mysql联表查询

Question

目前两张表
表一：sms_ticket（一次8000条数据左右）
`msgtype` VARCHAR(10) NULL DEFAULT NULL,
`provindex` BIGINT(20) NULL DEFAULT NULL,INDEX `provindex` (`provindex`)
表二：sms_phonesegment（目前数据22000左右）
`startprefix` BIGINT(20) NOT NULL,
`endprefix` BIGINT(20) NOT NULL,
`provindex` INT(10) NOT NULL DEFAULT '0',
`cityindex` INT(10) NOT NULL DEFAULT '0',INDEX `startprefix_endprefix` (`startprefix`, `endprefix`)
sql：
SELECT r.provIndex provIndex,SUM(CASE TYPE WHEN '00' THEN msgCount ELSE 0 END) mo_count,SUM(CASE TYPE WHEN '01' THEN msgCount ELSE 0 END) mt_count
FROM
(SELECT COUNT(1) msgCount,mph.provindex provIndex,ct.msgtype TYPEFROM sms_ticket ct
INNER JOIN sms_phonesegment mph ON ct.provindex BETWEEN mph.startprefix AND mph.endprefix
GROUP BY TYPE,provIndexORDER BY provIndex) r
GROUP BY r.provIndex;
在 正式库上 查询一次 用了 300+秒 看图
求大神呀 给出优化方案 我简直 头都要大了，在我们公司租用的服务器上相同数据 相同sql 执行的情况 1秒都不到

Answer 1

用explain 贴出查询计划

Answer 2

这个rows 高得离谱  这个数据量是 ticket表 10000条 segment表 22000条 的数据

Answer 3

然而 我们公司租用的服务器上 ticket表20万条 segment表22000条  执行的情况是这样 

Answer 4

ms_ticket ct
INNER JOIN sms_phonesegment mph 你这两个表的连接后数量有2亿多行

Answer 5

SELECT COUNT(1) msgCount,mph.provindex provIndex,ct.msgtype TYPEFROM sms_ticket ct
INNER JOIN sms_phonesegment mph ON ct.provindex BETWEEN mph.startprefix AND mph.endprefix
GROUP BY TYPE,provIndexORDER BY provIndex问题是这个连出来的结果太大

Answer 6

嗯 现在问题是 公司和正式环境 表结构 索引都是一样的 只是mysql版本不一样 公司是5.1  正式环境5.6  跟版本是否有关

Answer 7

 你用两种环境跑一下SELECT COUNT(1) msgCount,mph.provindex provIndex,ct.msgtype TYPEFROM sms_ticket ct
INNER JOIN sms_phonesegment mph ON ct.provindex BETWEEN mph.startprefix AND mph.endprefix
GROUP BY TYPE,provIndexORDER BY provIndex
这句话看看效率

Answer 8

公司上 ticket表数据20万 phonesegment表数据22000 跑的结果
正式环境 ticket表数据650条 phonesegment表数据22000 跑的结果区别太大了  公司上数据量时间反而短  正式环境数据量小时间反而长 

Answer 9

贴出你的 show index from ...
explain select ...这样大家可以进行分析有的放矢。

Answer 10

正式库上面的show index
正式库上的plain
公司库的 show index
公司库上plain
plain后发现 公司库上会用索引，而正式库上不会用索引