今早过来,线上出现了大量的超时日志,昨晚上线了一个项目,涉及到很大的数据量,涉及到的几个表有千万级别,个别表达到亿级别,上线初自己手动测试,没有什么问题,但是今早一过来,监控显示2分钟内爆了3000+个超时,并且影响到其他的业务系统。运维老大帮我定位了系统前几的慢查询,全是新上线的数据表的问题。
出现事故之后才知道自己在这方面的不足,根据这次事故来补充在mysql查询优化,顺便做下记录。
定位
cat监控系统定位看了是我新增的服务超时导致,在具体到DAO层,锁定了几个DAO查询SQL。
第一反应是加索引。查看到关联的表,有2张表的SQL查询漏了2个字段的索引,二话不说,赶紧让运维 加上去,然后超时的错误以及数据库负载立马降下来,妥妥的。
一个小时过后,又有超时瞬间爆发,这次更厉害,3分钟内容,14405次超时错误。这次没办法,只能通过修改配置来临时取消该有关该SQL操作的调用。(失落脸)
这次有足够的时间去定位问题 以及 解决问题,在监控系统查看,还是找到了几个问题,有索引建立缺失 以及 索引失效,服务器优化效果不佳的问题。
解决后,做个问题总结。
问题解释
备注:本次问题全是涉及到单表的,多表的联合查询优化后面再详细谈。
1.1 单列索引和联合索引
描述:这次事故中,最大的问题就是建了过多的单列索引,没有建任何的联合索引,导致效率很低。
单列索引,简单讲就是一个索引对应一个字段。
联合索引, 简单讲就是一个索引多对应多个字段。
问题: 什么时候应该建单列索引,什么时候应该建联合索引?两者的效率如何,什么情况下哪个效率更高。
例子: 这次系统事故中,遇到一张千万级数量的表的查询效率很低,然后SQL语句是类似这样,select * from tableName where a = '1990' and b = '123' and c like 'X%',然后索引有3个,a , b ,c三个字段 各有一个对应的索引。
还原 :
SQL : SELECT *
FROM test_data WHERE line1=’line14999995’ AND line2=’07004999995’ AND line3 like ‘000AA%’;
(1)在本机建了一张表进行数据还原,500W数据,先给3个字段建 3个索引,进行查询。
结果: 70W 数据,耗时:2s 43ms
(2) 把之前的三条索引删除,改为建一个 (a,b,c)的联合索引,进行查询。
结果:70W 数据,耗时:345ms ,性能提升了6倍左右
分析:
explain下2次的结果:
(1)第一次
单表查询下的explain 的结果主要看 key(使用到的索引) rows(扫描的行数)
这里可以看到是 3个索引,只用到了 其中1个,没有用到全部,扫描行数达到242W行。
(2)第二次
用到了一个联合索引,扫描行数达到 120W,比之前的少了一半。
结论:
出现多个条件 并且是 “=” 过滤的时候,联合索引的效率远比单列索引高效;在多个单列索引以及多个条件查询的时候,只有其中一个索引被用到,其他的都失效。
具体的底层原理来分析,后面再详谈。
1.2 最左前缀匹配,索引顺序
本次事故没有遇到过这个问题,但是学习了,也非常值得说下。
建联合索引的时候,mysql会从左到右进行逐步匹配,遇到模糊匹配(> < like between)就会停止匹配,所以索引在多个字段的顺序会影响到查询的效果。
比如上面的 select * from tableName where a = '1990' and b = '123' and c like 'X%' ,联合索引 index1(a,b,c) 和 index2(a,c,b)的查询效果是完全不一样的。
index1 可以完全匹配到 3个字段的范围,扫描的行数会比较小;而 index2 从a匹配到c的时候就停止了,导致扫描的行数比较大,效率自然也就低。
1.3 区分度
区分度:字段内容不重复的概率,计算公式: count(distinct status)/count(*) ,创建索引的时候,要选择区分比较高的字段,比如orderNo (订单号)这种区分度接近1的,就很适合,像 status,XXType 这种区分度接近0的字段,大部分情况下是没有必要创建,以免浪费空间以及降低update insert 等操作的性能。
1.4 减少重复没必要索引
联合索引创建之后,联合索引的第一个字段的单列索引就没有必要去创建了,无论查询条件里面是否有该字段的查询操作。
在上面提到创建联合索引,比如 index1(a,b,c) , 那么 字段a就没有必要再去创建索引了。两者效果是一样的。
结论
不要随便单纯根据SQL的字段加索引,要综合考虑所有关于该表的SQL语句进行设计索引
优先创建联合索引 再考虑加单独的字段索引
同一张表,不同数据量,同样的SQL语句可能的执行顺序不一致,索引使用可能不一致
多个条件一起查询的时候 ,建联合索引一定要 模糊匹配的字段索引建在后面,比如c字段是模糊查询,建 (abcd)和(abdc)两种索引,前者只会有 abc3个索引生效,而后者是4个都会生效
联合索引建立之后,联合索引的首个字段 不需要再建索引,免得浪费空间
建索引时,优先采用区分度搞的字段,比如 status这种字段就没必要建索引
备注:上面说的总结、分析都没有深入说明原理,接下来会一篇关于mysql查询原理(B-tree)简单的讲解。