好东西该如何用——MySQL索引使用的几条建议

1、索引

索引是一种数据结构，用来在大量的数据中快速的查找指定的内容。我们在学习索引的时候想必接触最多的例子就是书的目录，基本上每一个讲索引的文章都会用书或者字典的目录来引出索引的概念，索引确实如目录一样，本质上索引就是一种快速检索数据的一种数据结构。

我们说索引是一种数据结构，那么MySQL中索引是一种什么结构呢？现在MySQL的存储引擎默认为Innodb,其选择使用B+树来实现索引（关于MySQL为什么使用B+树实现索引，我觉得这篇文章总结的挺不错
cloud.tencent.com/developer/a…
，我们每建立一个索引（主键或者唯一索引也好，二级索引也罢），Innodb都会为其建立一颗B+树来实现上面这种“目录”的功能，同时Innodb为其建立的B+树索引大概长这个样子。

2、索引的代价

我们说索引是个好东西，数据库中的数据一般比较多，而我们从数据库中查询数据的场景太多了，如果直接扫描整个表，在表中数据很少的情况下还是可以的，但是当表数据量比较大的时候，直接扫描全表就太耗时了，严重影响用户体验，而索引使得我们在大量数据的表中检索数据的速度提高了很多，在大型的互联网公司中，表数据通常量级比较大，在一般情况下要求查询操作必须命中索引，否则可能产生大量的慢查询语句。在《阿里巴巴Java开发手册》中，明确索引至少要优化到Range级别，如下所示：

关于索引的类型级别不太了解的可以查阅相关文章，这里不再展开。

但是好东西也不能随便乱用，用的好了提高效率，用的不好拖后腿。并不是说我们每个查询都为其建立一个索引。主要原因有如下两点。

1、时间代价

我们一旦创建索引，那么以后要想在查询的时候靠索引用的爽，那么平时的数据变更操作（新增、删除、修改）必须要维护好涉及到的所有的索引，我们往表中插入、删除、修改一条数据的时候就要到对应的聚簇索引与所有的二级索引进行修改。所以索引如果过多，那么我们的DML操作语句需要改动的索引也就越多，相应的执行时间也会变长，所以索引在提高查询速度的同时也加剧了DML操作的负担或者代价，从这个角度来说，索引并不能随便创建。

2、空间代价

我们每建立一个索引，MySQL就需要为我们创建一颗B+树，而这颗B+树可不是凭空存在的，他可是真真实实的占用着物理空间呢。所以，我们为一张表建立的索引越多，索引所占据的存储空间越大，大家可不要小瞧这些空间占用，当表的数据量非常大的时候，每一颗B+树由于保存了表中的某些列或者全部列数据，所以每一颗B+树占用的空间都不容小觑。从这个角度上来说，索引并不能随便创建。

3、索引的使用

前面我们提到，索引一方面能够极大的提升数据查询的速度，另一方面又会存在时间和空间的代价，所以也不能随便的创建索引。同时，即便我们创建了索引，如果不能够很好地利用索引，可能导致索引虽然被创建了但是并没有用到，所以对于在日常工作中需要写sql的开发人员，应该学习一下该如何更好的使用索引，前段时间我在面试猿辅导时面试官也曾问我对索引的使用有什么看法？结合我当时的回答以及后面对索引的使用总结，接下来我们一起探讨一下在使用索引的过程中应该注意的一些问题，帮助我们在日常开发中更好的设计表索引，编写出更加合理有效的sql。

1、只为出现在搜索条件、排序、分组的列创建索引

我们日常开发中的数据表中可能存在很多的列，比如student表可能存在id,name,sex,class,score这些列，我们想要查询分数在九十分以上的学生。

select id,name,sex,class,score from student where score>90;
复制代码

这个时候我们只需要为score建立索引即可，不必为select中的其他列建立索引。

为什么只为必要的列创建索引呢？

首先查询语句不是根据select条件进行过滤的，而是根据搜索、排序、分组等条件进行过滤的。

其次，我们知道Innodb的B+树以页为单位，一页默认情况下为16KB,那么在页容量大小固定的情况下，是不是当索引所包含的列越少，一个页中所存放的索引项就越多呢？那么这个时候索引所占据的存储空间就越少。同时由于每个页存放的索引项变多了，导致B+树更矮更胖了，所以索引查询时需要的IO操作也更少了，索引查询的速度也提升了。

2、主键趋势递增

这里我们最好为主键设立auto_increment属性使得主键自增，开发中可能有些使用数据库中间件生成主键的方式也最好采用主键不断递增的方式生成主键。为什么要这样呢？

首先我们知道Innodb会自动为主键创建一个B+树，这颗B+树所代表的索引被称为聚簇索引。如果我们创建了主键，那么Innodb会为该主键生成一颗B+树，如果没有主键，Innodb会选择一个非空的Unique键，如果也没有，Innodb会为我们隐式的生成一个6字节的RowId作为主键，然后为其创建一个B+树索引，所以可以理解为Innodb会为每一张表创建一个唯一的聚簇索引。聚簇索引的B+树是按照主键进行排序的，并且所有的数据均存放在B+的叶子节点中，就像这样：

这张图选自《MySQL是怎样运行的：从根上理解MySQL》，我觉得这个图很好，在此引用这个图一下。

其中内节点中只存放主键值，叶子节点中存放整行数据，每个页面中所有的数据都是根据主键递增进行排序的。

如果我们插入记录的时候采用的主键递增的方式，那么当一个数据页用完的时候，只需要在最后面新增一个数据页，然后把新添加的记录放到新的数据页中，然后在上层的页面中新增一个指向新创建页面的目录项即可。

而如果我们的主键不是递增的，这个时候就要根据排序的位置找到应该插入的位置保持B+树中的主键递增的顺序，假设需要插到页9中，而碰巧页9中的存储空间已经不足以插入一条新纪录，那么这时候就需要新增一个数据页，把页9中一半的数据转移到新的页面中，同时在上层目录页中插入一条目录项纪录，而如果此时上层页也满了，那就需要继续的分裂，移位。而页面的分裂、纪录的移位就意味着性能损耗，所以从这个角度上来说我们最好让主键保持趋势自增。

同样二级索引的记录在索引列都相同的情况下也是按照主键来排序的，这个时候再插入记录的时候也会涉及到记录移位和页面分裂等操作。

3、索引的区分度要高

索引的区分度是指某个列中不重复的比例，还是拿上面student表为例，假如我们为sex建立索引。性别除了男就是女，这个索引的区分度就很低，当我们指定sex=’男’的时候，可能会命中非常多的数据行。

而MySQL有个查询优化器的东东，在sex上建立的索引是二级索引（除了聚簇索引其他的都属于二级索引），这里就有一个回表的概念。回表是指，二级索引存储的是二级索引的索引列以及主键值，索引先从二级索引所对应的B+树中定位到记录所对应的主键值，然后根据这个主键值去聚簇索引中查询完整的记录，所以这里就需要两次查询B+树。而前面提高的查询优化器，在二级索引命中了大量记录的情况下，需要回表查询的记录就有很多，查询优化器可能认为这样还不如直接对全表进行扫描来的快呢！所以查询优化器可能会选择放弃使用索引，而走全表扫描。

因此我们要尽量在区分度高，通过索引可以过滤大量记录的列上建立索引，这样能够使查询优化器更加倾向于使用二级索引+回表的方式查询。

4、索引的类型尽可能小

这里得小一定要是在满足业务需求的情况下，先满足需求，再考虑优化。

比如说我们之前用bigint的某一列，我们确定该列不会特别大，那么可以改用int。因为在同一类型中，所存储的范围越大，占用的存储空间也就越大，比如性别，我们只需要一位就可以表示了，不要小瞧在某一个列上存储空间的节省，当表的数据量很大的时候，这种空间的节省是惊人的。而如果索引所占用的存储空间越小，那么一个页面中就可以放下更多的索引项，这个时候查询的IO操作的效率也就会越高。

5、索引字符串的前缀

我们知道长字符串一般是比较占用存储空间的，当我们需要根据某一字符串作为搜索条件进行搜索的时候，我们可以选择截取字符串的某一长度的前缀来建立索引，这样可以大大节省索引项占用的存储空间，提升IO操作效率。比如person_info表有一个字段description,平均长度在200个字符左右，然后通过比较前20个字符我们就可以区分90%的记录，这个时候索引可以达到大致相当的效果却可以大大减少索引占用的存储空间，这个时候我们可以这样定义：

create index idx_description on person_info(description(20))
复制代码

6、索引列单独出现在表达式中

我们在使用索引的时候，必须保证索引单独出现在表达式中，否则会导致查询不能命中索引的情况。比如student表中我们要查询分数为90分的学生。

select * from student where score=90;
复制代码

select * from student where score+10=100;
复制代码

这两种写法看似一样，上面可以使用到为score建立的二级索引，但是下面只能走全表扫描。类似的还有对索引使用函数比如upper(name)等都会使得索引无法得到有效的使用，所以在使用索引的时候请让索引单独出现在表达式中。

7、尽量使用覆盖索引查询

前面我们说过，通过二级索引查询记录的时候需要查找到记录对应的主键，然后根据主键查找完整的记录。是因为二级索引中存储的信息并不完整，其B+树中存储的就是二级索引的索引列+主键。但是如果我们并不需要查找出一条记录的所有的列，而只需要查询某些列那么可以使用覆盖索引，所谓的覆盖索引是select中查询的列都在二级索引所对应的B+树中存在，那么就不再需要回表进行查询。

比如说刚才的student表，我们为score建立一个二级索引，那么该索引列中存放的应该是score+主键（id）。如果我们的查询语句是这样的：

select id,score from student where score=80
复制代码

select查询列全部都在二级索引中，此时我们就不需要再回表通过主键进行查询了，直接从二级索引中过滤出相关记录返回即可。如果我们想也查询出学生姓名，并且也不想回表操作怎么办呢？这个使用我们可以使用联合索引。

create index idx_score_name on student(score,name);
复制代码

这个时候二级索引中的记录，是先按照score进行排序，其中score相同的按照name进行排序，name相同的会按照主键id进行排序。这个时候我们再查询：

select id,score,name from student where score=80;
复制代码

这时候这条查询语句中的查询的列都在score所对应的B+树中，我们就不需要回表继续查询，只需要查询出所有score=80的记录返回即可。

回表操作由于需要两次查询B+树，如果我们可以通过覆盖索引来避免回表操作的话，那么查询效率可以提升很多。

8、删除重复的索引

比如说刚才我们建立一个（score,name）的联合索引，那么如果我们单独查询score的话，需不需要再建立一个针对score的二级索引呢？答案是不需要！因为针对索引的查询遵循最左匹配原则，我们可以直接使用为score和name建立的联合索引，二级索引中是先按照score进行排序，在score相同的情况下再按照name进行排序，在name也相同的情况下按照主键进行排序，当查询条件进行匹配的时候，是先匹配左边的列，在左边的列相同时再去匹配右边的列。所以假如你为（score,name）建立了一个联合索引idx_score_name,又为score建立了一个二级索引idx_score，那么这个idx_score其实是多余的，因为idx_score_name完全可以满足你的需求，所以删掉重复的索引吧，至于为什么？回去看看索引的时间空间代价吧！

本文总结了几条我们在使用索引的时候需要注意的一些小问题，觉得不错请点个赞吧，您的支持与鼓励是我创作的源动力！