深入探索数据库索引的奥秘：加速数据检索的利器！

索引是必须掌握的数据库知识，下面我们一起来研究下数据库索引吧。

1

索引的几个概念

单列索引：索引节点中的关键字是一个列的内容，也叫普通索引，比如：用身份证号做索引，用手机号做索引。

联合索引：索引节点中的关键字是多个列的内容，经常在需要用多个字段查询的时候用到。比如：用手机号+时间做索引，或者手机号+时间+状态做索引；也就是说单列索引是特殊的联合索引。

唯一索引：索引列的值必须唯一，但允许有空值；如果是联合索引，则列值的组合必须唯一。

主键索引：当给这一张表指定主键以后，那么这个主键就会变成一个主键索引，主键索引是唯一性索引，唯一性索引并不一定就是主键。

聚集索引（聚簇索引）：聚集索引的 B+Tree 中的叶子节点存放的是整张表的行记录数据，而且索引键值的逻辑顺序跟数据库表中的数据行内容存储顺序是一致，辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键的值。

辅助索引：辅助索引也叫非聚集索引，也就是非主键索引。

回表扫描：在 InnoDB 中辅助索引里存放的是主键值，假设查询条件不是主键而是辅助索引，那么首先需要从辅助索引里找到对应的主键值，然后从主键索引里找到该主键值对应的数据记录行，这个过程就叫做回表扫描，因为这个过程比基于主键索引的查询多扫描了一棵辅助索引树，这个过程就叫回表扫描，意思就是从辅助索引树里找一圈，然后再回到主键索引里找一圈。

覆盖索引：在辅助索引里面，不管是单列索引还是联合索引，如果查询的数据列只用从索引节点中的关键字就能够取得，不必从数据区中读取主键值，再回到主键索引查询记录行，这时候使用的索引就叫做覆盖索引，这样就避免了回表扫描，所以说，用到覆盖索引的查询效率会高一些，原因就是不用进行回表扫描操作，IO 操作次数减少了。同时覆盖索引也可以减少数据库 IO，将随机 IO 变为顺序 IO，因此提高数据的查询效率。

了解了覆盖索引的概念，我们就能知道为什么数据库开发一般要求不要用 select * 查询，除了select * 查询需要返回的字段值多，占用空间以外，更重要的原因就是，如果业务需求只需要查询索引列的时候，直接查索引字段会直接从索引文件返回内容，而不需要回表扫描，这样就大大提升了查询的性能。

2

索引的创建原则

在实际开发过程中，一般人们存在一个误区，就是查询效率低，我们创建索引解决就行了，也有人把“在经常使用的查询条件上都建立索引“作为解决查询缓慢的法则，其实通过索引数据结构的学习，大家应该能有体会，索引不是越多越好，太多的索引反而会影响数据库的性能。

下面介绍索引创建的 3 个原则：

1、最少空间原则

最少空间指的是列的长度小，长度越小的列，B+Tree的路数就会越多，IO 检索的次数就会越少，查询的效率就会越高，也就是说索引的效果越好。如果不理解，请再看一下索引的数据结构部分的内容。

2、高离散度列原则

数据表中列的离散度公式：count(distinct(列名称)) / count(*)，也就是列的所有不相同的值和所有数据记录总行数的比，如果列的所有不相同的值和数据记录总行数越接近，那么列的离散度越好，简单来说，如果列的重复值越多，离散度越低，重复值越少，离散度越就越高。

这也就是大家都了解的不要在性别字段上建立索引的主要原因，因为性别字段列的值除了男就是女，列的散型太差，建立索引起不到效果，查询优化器选择执行计划可能觉得走这样的索引还不如走全表扫描。

因此我们在创建索引的时候尽量选择离散度高的列，也就是选择列的值几乎没有重复的列做索引效果最好。

3、最左匹配原则

最左匹配原则一般用于联合索引中，联合索引在 B+Tree 中是复合的数据结构，最左匹配原则就是联合索引的检索比对是从左往右匹配的，它是按照从左到右的顺序来建立搜索树的。

假如一张表用手机号和时间联合索引【phone，time】，没有建立 time 的单列索引。

那么联合索引【phone，time】（phone 在左边，time 在右边），就表示：phone 是有序的，time 是无序的；当 phone 相等的时候，time 才是有序的。

这个时候我们使用 phone 和 time 作为查询条件的时候，B+Tree 会优先比较 phone 来确定下一步应该搜索的方向，往左还是往右；如果 phone 相同的时候再比较 time ；但是如果查询条件只有 time，那么就不知道第一步应该查哪个节点，因为建立搜索树的时候 phone 是在左边，所以用 time 查询就不会用到索引。

如果用 phone 作为查询条件，是否可以用到索引呢，答案是肯定的，因为 phone 在联合索引的左边，所以用phone 独立查询，按照最左匹配原则是可以用到索引的。

从最左匹配原则我们就能发现，如果创建了联合索引【phone，time】，那么就没有必要再去创建单列索引【phone】了，但是如果有用time作为查询条件的情况，就需要创建 time 列的索引【time】。

3

索引使用的几个注意事项

1、在 where判断 order 排序和 join（on）的字段上创建索引

2、索引字段最好不要为 null 值

3、索引列的长度能少则少

4、索引不是查询字段都要建立索引，也不是越多越好

5、注意联合索引的使用最左匹配原则

6、选择索引要选离散度高的列

7、联合索引里最左的列，就不要创建单列索引了，浪费空间不说，还增加了更新操作的索引维护成本 

8、频繁更新的值，不要作为主键或者索引

9、索引列上避免使用函数（replace、substr、concat、sum）和运算

10、字符串查询一定要加引号

11、like 条件中前面带 %，同时要保证 % 前面的内容离散度要高

12、避免使用 NOT IN 、<>、NOT LIKE 等负向判断

13、可以使用 limit 关键字进行查询记录限制

后面将为大家介绍数据库的事务。