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

近来有个一情况非常奇怪 查询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
1
情况分析，
时间都花费在 Creating sort index，这是和order有关的语句, 
把order 给去掉，或者order by id desc 则还是很快,说明貌似是 和update_time有关

解决办法：
一，修改mysql的部分参数:

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

下面是参数详解

max_connections = 1000 
#指定MySQL允许的最大连接进程数。如果在访问数据库时经常出现"Too Many Connections"的错误提 示，则需要增大该参数值。
query_cache_size = 64M  
#指定 MySQL 查询缓冲区的大小。可以通过在 MySQL 控制台执行以下命令观察：
代码:
> SHOW VARIABLES LIKE '%query_cache%';
> SHOW STATUS LIKE 'Qcache%';如果 Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况；
如果 Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲； Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
memlock # 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的 mysql 在在内存高度
紧张的时候,数据在内存中保持锁定并且防止可能被 swapping out,此选项对于性能有益


tmp_table_size = 512M 
#临时表的最大大小，如果超过该值，则结果放到磁盘中,此限制是针对单个表的,而不是总和.
thread_cache_size = 20 
# 我们在 cache 中保留多少线程用于重用.当一个客户端断开连接后,如果 cache 中的线程还少于 thread_cache_size,则客户端线程被放入 cache 中.这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,
这不会有明显的性能提升.)服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,
如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用
根据物理内存设置规则如下：
1G  —> 8
2G  —> 16
3G  —> 32
大于3G  —> 64
myisam_max_sort_file_size=100G

重建索引时允许使用的临时文件最大大小(myisam_max_sort_file_size)

myisam_sort_buffer_size=175M
MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE 或用 CREATE INDEX 创建索引或 ALTER TABLE 过程中排序 MyISAM 索引分配的缓冲区(myisam_sort_buffer_size)
key_buffer_size = 256M 
#指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够  如果是以MyISAM引擎为主，可设置较大，但不能超过4G. 在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎.注意：该参数值设置的过大反而会是服务器整体效率降低！
read_buffer_size = 2M 
#读查询操作所能使用的缓冲区大小。和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享！用来做 MyISAM 表全表扫描的缓冲大小.当全表扫描需要时,在对应线程中分配.
read_rnd_buffer_size = 8M 
#MyISAM 以索引扫描(Random Scan)方式扫描数据的 buffer大小 

mysql配置对应,这一步修改配置 打开 https://blog.csdn.net/qq_27229113/article/details/91866071

二，增加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 ;

 

附录
表结构

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;

结尾记录下,复制大佬的,添加自己的,保证自己每一篇博客都测试过,原文地址:https://blog.csdn.net/mingover/article/details/79066064#commentsedit