mysql索引系统表_mysql索引的创建以及优化（单表）

今早过来，线上出现了大量的超时日志，昨晚上线了一个项目，涉及到很大的数据量，涉及到的几个表有千万级别，个别表达到亿级别，上线初自己手动测试，没有什么问题，但是今早一过来，监控显示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)简单的讲解。