近来有个一情况非常奇怪查询limit 10 竟然非常慢，而且都where中的两个字段和order by 的一个字段都有索引，这个表有1200W的数据，update比较多,但压力还OK，

profiles 查看使用的时间

profile 能查看到执行中的时间长短

SET profiling = 1;

select   t.*   from t_user t  where 1=1
and t.logintype= 'qq'
and t.channel= 'scb_weixin'
order by t.update_time DESC limit 10 ;

show PROFILES;

结果:

169    0.00231675    SHOW STATUS
170    0.00251925    SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
171    0.00085025    SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.001298*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=168 GROUP BY STATE ORDER BY SEQ
172    0.000857    SELECT * FROM `t_pdb`.`t_user` LIMIT 0
173    0.00316375    SHOW COLUMNS FROM `t_pdb`.`t_user`

show PROFILE for QUERY <从上面一个语句中找到,如 172> 结果:

starting    0.000135
checking permissions    6.7E-5
Opening tables    7.4E-5
init    0.000105
System lock    7.1E-5
optimizing    7.8E-5
statistics    0.000209
preparing    7.9E-5
Sorting result    6.6E-5
executing    6.5E-5
Sending data    8.5E-5
Creating sort index    33.105645
end    0.000179
query end    9.1E-5
closing tables    7.3E-5
freeing items    9.3E-5
logging slow query    0.000112
cleaning up    7.4E-5

show status 查看

FLUSH STATUS;
select   t.*   from t_user t  where 1=1
and t.logintype= 'qq'
and t.channel= 'scb_weixin'
order by t.update_time DESC limit 10 ;
show status where variable_name like 'handler%' or variable_name like 'created%';

结果:

Created_tmp_disk_tables    0
Created_tmp_files    0
Created_tmp_tables    0
Handler_commit    1
Handler_delete    0
Handler_discover    0
Handler_external_lock    6
Handler_mrr_init    0
Handler_prepare    0
Handler_read_first    0
Handler_read_key    4583930
Handler_read_last    0
Handler_read_next    15239952
Handler_read_prev    0
Handler_read_rnd    4583928
Handler_read_rnd_next    0
Handler_rollback    0
Handler_savepoint    0
Handler_savepoint_rollback    0
Handler_update    0
Handler_write    0

上面的几个count很大，网上找了下，应该是和order有关

执行计划

explain
select   t.*   from t_user t  where 1=1
and t.logintype= 'qq'
and t.channel= 'scb_weixin'
order by t.update_time DESC limit 10 ;

结果:

1    SIMPLE    t        index_merge    idx_channel,idx_logintype    idx_channel,idx_logintype    99,99        3083430    100    Using intersect(idx_channel,idx_logintype); Using where; Using filesort

情况分析，时间都花费在 Creating sort index，这是和order有关的语句, 把order 给去掉，或者order by id desc 则还是很快,说明貌似是和update_time有关

解决办法：一，修改mysql的部分参数，用于 https://forums.mysql.com/read.php?24,609971,609971

max_connections=10000
query_cache_size=0
table_open_cache=10000
tmp_table_size=150M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=175M
key_buffer_size=128M
read_buffer_size=1M
read_rnd_buffer_size=4M

二，增加order by 的参数的 where xxx is not null 这个立杆见影，把条件is not null加到where中去

select   t.*   from t_user t  where 1=1
and t.logintype= 'qq'
and t.channel= 'scb_weixin'
and t.update_time is not null
order by t.update_time DESC limit 10 ;

后续

相关情况还是没搞找到相关资料，但条件is not null加到where中确实效果很明显.如果各位看观有相关的信息，求留下言告知下 ^_^

附录

表结构

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scb_user_id` varchar(256) DEFAULT NULL COMMENT '',
  。。。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `ukey_login_name_type` (`login_name`,`logintype`,`channel`),
  KEY `idx_device_guid` (`device_guid`),
  KEY `idx_update_time` (`update_time`),
  KEY `idx_channel` (`channel`),
  KEY `idx_logintype` (`logintype`),
  KEY `idx_scb_user_id` (`scb_user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=12915154 DEFAULT CHARSET=utf8;

本文由创作，采用知识共享署名4.0 国际许可协议进行许可。本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名。最后编辑时间为: 2020/05/15 01:19

mysql查询均有索引但速度慢问题排查

profiles 查看使用的时间

show status 查看

执行计划

后续

附录