学会MySQL索引

就一起来聊一聊MySQL索引。

什么是索引？

百度百科是这样描述的:

索引是为来加速对表中数据行中的检索而创建的一种分散的数据结果,时针对表而建立的，它是由数据页面以外的索引页面组成,每个索引页中的行都含有逻辑指针,以便加速检索物理数据

其实，索引的概念大家都很清楚，也知道索引能够提升查询效率，但大部分童鞋在怎么建索引，建在哪些字段上有以下常见误解:

• 新建表时不需要建索引,后续才添加索引

• where条件后的字段均建索引

• 简单SQL不需要索引,联合查询才需要索引

• 联合索引的顺序是where条件后字段的先后顺序

• 对于区分度小的字段上也新建索引,如状态,性别等字段等。

索引区分度

在说上述问题之前,我们先来看看另一个概念,就是区分度。

区分度: 指字段在数据库中的不重复比

区分度在新建索引时有着非常重要的参考价值,在MySQL中,区分度的计算规则如下:

字段去重后的总数与全表总记录数的商。

例如:

select count(distinct(name))/count(*) from t_base_user;

结果如下:

其中区分度最大值为1.000,最小为0.0000,区分度的值越大,也就是数据不重复率越大，新建索引效果也越好,在主键以及唯一键上面的区分度是最高的,为1.0000。在状态,性别等字段上面的区分度值是最小的。 (这个就要看数据量了,如果只有几条数据,这时区分度还挺高的,如果数据量多,区分度基本为0.0000。也就是在这些字段上添加索引后,效果也不佳的原因。)

值得注意的是:    如果表中没有任何记录时,计算区分度的结果是为空值，其他情况下,区分度值均分布在0.0000-1.0000之间。

如何建索引

   (一)  : 区分度
个人强烈建议, 建索引时,一定要先计算该字段的区分度,原因如下:

1. 单列索引
可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效，以及效果如何。区分度越大,索引效果越明显。

2.多列索引(联合索引)
多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效,例如:

select * from t_base_user where name="" and status=1;

像上述语句,如果建联合索引的话,就应该是:

alter table t_base_user add index idx_name_status(name,status);

而不是:

alter table t_base_user add index idx_status_name(status,name)；

(二) 最左前缀匹配原则
  MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (该语句仅作为演示)

在上述语句中,status就不会走索引,因为遇到<时,MySQL已经停止匹配,此时走的索引为:(type,created_at),其先后顺序是可以调整的,而走不到status索引,此时需要修改语句为:

select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"

即可走status索引。

(三) 函数运算
  不要在索引列上,进行函数运算,否则索引会失效。因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

(四) 扩展优先
  扩展优先,不要新建索引,尽量在已有索引中修改。如下:

select * from t_base_user where name="andyqian" and email="andytohome"

在表t_base_user表中已经存在idx_name索引,如果需要加入idx_name_email的索引,应该是修改idx_name索引,而不是新建一个索引。

误解纠正

  上面说了,如何新建索引,现在我们就可以来回答,在第一步中存在的误解了。

误解一:   新建表时不需要建立索引,后续才加索引

答: 一个好的数据表设计,在一开始就要考虑索引的创建,而不是等到后续出问题了,影响业务使用了,才新建索引来救场,而且后续创建索引的成本也相对高很多。(这就是给生产事故留下生根发芽的机会呀)

误解二:   where条件后的字段均建索引

答: 这个误解比较常见,但where条件后的字段不需要全部建立索引,过多的索引,也会导致索引文件剧增,也还达不到期望中的效果。详细请参考上述新建索引的小节。

误解三:   简单SQL不需要建立索引,联合查询采建立索引

答: 这个误解就得好好说说了,现在互联网公司特别是B/S架构下,业务逻辑均剥离在代码逻辑层,到最后SQL层面,其实都是一些简单的SQL,只有些许连接查询,更多的还是单表操作,(C/S架构中有很多在SQL层面的写逻辑的)，你说这些语句简不简单。

误解四:    联合索引的顺序是where条件后字段的先后顺序

答: 我们刚才说过,联合索引的顺序,是根据最左前缀原则,以及区分度来区分的，和where条件后字段的先后顺序无关。

误解五： 对于区分度较小的字段新建索引

答: 在区分度较小的字段上新建索引,基本无效,还会增加大量的索引文件,你说是不是得不偿失。

索引重不重要？

  上面介绍了MySQL索引的概念，新建索引时的一些技巧。这么理论的东西，对于平时没有使用或使用比较少的童鞋，此时对索引的重要性可能还没那么直观，那么，我就来说说我在索引上吃过的亏，踩过的坑！同时也是未建索引常见问题！

0. 导致慢查询
这个问题可是未建索引的常客哦，（这里也还有很多细节呢，如: 隐式类型转换等等）

1. 导致服务超时

场景 :
  在某次上线时，作为服务提供者，提供服务给业务方使用。一开始以为就提供一个简单的服务，也已经测试完成，心里还在窃喜，今天总算可以早早回家了！

描述 :
  实际一上线，在生产环境中导致业务方请求调用时，而且每次请求均超时，数据也已落地，此时只能review代码，最后发现生产中有个慢查询导致，活活的花费了10多秒，这个语句有多简单呢，你绝对想不到，其实就是一个单表的where条件查询语句。你说这种原因导致服务不可用，你说冤不冤，气不气！（这也是我为什么说，一个好的数据表设计，从一开始就要考虑新建索引了）。

2. 数据库服务器CPU 100%

在查询频率比较高的SQL上，如果由于未建索引，导致慢查询的话，那可是会导致数据库服务器CPU 100%，影响可是整个系统哦。

小结
上面说了好几类，由于没建立索引而导致的问题，轻则导致慢查询，影响系统效率，重则，导致CPU 100%，影响整个系统的使用，看到这里，你说索引重不重要？

最后

上面简单说了，索引是什么？有什么用,以及建立索引时的一些技巧，还着重说了，索引的重要性。那么索引这么重要,在平时编码时如何避免呢？以下是我个人的建议:

1. 在建表时就应该考虑添加索引，如：外键字段,等等。

2. 在写完SQL后,一定要查看执行计划。尽量避免全表扫描。

3. 如果是已有表中添加索引,一定要先计算该字段的区分度。

4. 联合索引，将区分度大放在前面。

5. 遵从MySQL左列前缀优先原则\