Mysql复合索引的坑!!!

这篇文章主要介绍了Mysql复合索引的坑!!!,包括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.resourceid = '123456' and A.modifytime > 2017-01-09

问题第二次发生(+原理解析)!

本以为改成这样,索引一定可以生效运行正常,谁知道过来两天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,索引,复合,所有方面的内容对大家有所帮助,web开发乐园整理,

您可以选择一种方式赞助本站

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: