Mysql复合索引的坑！！！

 

问题发生！

上周五公司年会，一早运维电话说UIOC，IMS应用的CPU持续飙高，立马启动UIOC。迅速赶到公司情况属实，各种查最终发现DB并发连接数超高，DB负载超高，kafka积压严重，并且持续时间已经有一两个小时了。

 

 

解决方案第一波！

查看IMS应用线程堆栈（ThreadDump）信息发现200个运行线程有167个都在做同一个操作，这个操作是每个用户登录时都要加载自己当天所有的任务并缓存到redis，加载自己所有任务的mysql语句基本如下：

select * from A left outer join B on A.taskid=B.taskid where A.resourceid = '123456' ，其中我们已经有了一个复合索引 （A.modifytime ， A.resouceid），复合索引使用方式是从左到右，所以A.resourceid = '123456'根本用不上复合索引，结果是全表扫描。很明显我们sql语句忘记加时间限制，所以当晚改成如下：

select * from A left outer join B on A.taskid=B.taskid where  A.modifytime > 2017-01-09  and A.resourceid = '123456'

 

 

问题第二次发生（+原理解析）！

本以为改成这样，索引一定可以生效运行正常，谁知道过来两天DB又报警，一查还是那条语句的问题。explain执行计划发现会跑偏索引、未命中索引或者只用到复合索引的一半（A.modifytime）。复合索引有两个字段，并且where条件都有，为什么只用到一半。后来了解到mysql复合索引从左到右，遇到范围查询的条件（A.modifytime）时，索引回表后不会再使用复合索引后面的字段。

还有一个问题为什么会跑偏索引或未命中索引？

通过DBA了解到，mysql执行时使用哪个索引是运行时决定的（和oracle不一样），如果SQL引擎认为用索引后回表再进行扫描查询，还不如直接全表扫描时，他可能会不用索引或者用另外一个他认为更合适的索引。

 补：按道理来说用一半索引也可以了，为什么还会报警？主要是因为有些比较大的城市，每天所有用户所有任务量也会很大，命中一半索引，回表后全表扫描DB压力还是很大。特别是早高峰时段压力更为明显！

 

 

解决方案终极波！

问题描述到这里，到底怎么解决呢？调整复合索引顺序！

结合业务我们知道，主表中存储此城市所有用户15天的所有任务数据，先用A.resourceid做过滤效果会好很多，再加上时间条件（A.modifytime > 2017-01-09）几乎可以锁定数据到很小一个范围。所以我们调整了复合索引的顺序，即（A.resouceid, A.modifytime），这样那条SQL语句每次都会命中这个索引。上线后第二天高峰时段监控发现，DB的CPU使用率由之前80%降到12%。

 

 参考链接：MYSQL是这么走索引的

http://blog.chinaunix.net/uid-29578485-id-5241735.html