SQL慢查询优化2

背景

场景：慢查询报警，每分钟60条
目标：每分钟慢查询报警小于10次
方案：分析慢SQL原因，指定方案
结果：每分钟慢查询报警小于10次

通过1个案例，来说明慢SQL优化。

案例

慢SQL

慢查询当中，最多的一条SQL语句，就是SELECT * FROM user_message WHERE user_id = 1234567890 and msg_type = 2 ORDER BY CREATE_TIME DESC LIMIT 880, 10;

表结构

执行计划

第一个看的字段是possible_keys，含义就是理论上用到的索引，现在为idx_user_pushtime, idx_user_state, idx_msg_type_user_id_CREATE_TIME。

第二个看的字段是key，含义就是实际上用到的索引，现在为idx_user_pushtime。

第三个看的字段是extra，含义就是额外的动作，我们特别关注一下using filesort，这是一个危险的信号。

疑问

key实际上用到的索引竟然是idx_user_pustime?

这个SQL，难道不是应该使用索引idx_msg_type_user_id_CREATE_TIME(msg_type,user_id,create_time)更合适吗？

这里可能有网友会说：因为SQL语句中where条件为：use_id = 1234567890 and msg_type = 2 ORDER BY CRATE_TIME DESC LIMIT DESC LIMIT 880, 10;

如果msg_type和user_id的位置置换一下，比如这样子：

会不会用到索引：
idx_msg_type_user_id_CREATE_TIME

执行计划

我们再次看一下SQL的执行计划：

发现是一样的。都是用了索引：idx_user_pushtime

思考

可能是因为msg_type的区分度太小了。msg_type只有2种值，放在索引的第一位，实在不合适。因此，mysql的优化器，用了区分度大的idx_user_pushtime。但用了这个idx_user_pushtime索引，因为order by，造成了using filesort。

验证

强制使用一下索引：
idx_msg_type_user_id_CREATE_TIME

执行计划

首先，用的索引key，的确为：
idx_msg_type_user_id_CREATE_TIME。且extra已经没using filesort。

消耗的时间：

分析

主责

主要责任还是人建错了索引。

idx_msg_type_id_CREATE_TIME索引，这个联合索引，第一个索引竟然是msg_type，区分度太小了，几乎为0。

msg_type只有2个取值，这个和sex性别字段上建索引是一样的。建索引应该遵守：(where 条件顺序可互换的前提下）区分度从大到小。比如这索引应该建为：idx_user_id_msg_type_create_time。

次责

Mysql用错索引了。

补充一下知识，mysqk用哪个索引，是通过样本筛选来选的。

样本筛选，就说明有误差，这个误差往往会导致用错索引。现在呢，我们已经清楚问题所在了。mysql用错索引，这个是我们没法改的。我们能做的就是“建对索引”。

因此，我们建一个idx_user_id_msg_type_create_time的索引。

建索引

alter table user_message add index idx_user_id_msg_type_create_time(user_id, msg_type, create_time);

表结构

执行计划

这个时候，已经没using filesort，也没满查询了。同时也验证了猜想是正确的。

实时慢SQL统计

总结

其实就是人为建的索引，建错了，这个错。不是指字段，是指字段的顺序建错了。可见，键索引，学问是很多的。

强制使用索引force index(索引名)

参考资料：《大厂面试题》为什么SQL使用了索引，却还是慢查询(续集)