SQL进阶理论篇（三）：什么是索引

简介

索引在SQL优化中占了很大的比重，甚至可以说，对SQL的优化，其实就是对索引的优化。

但是索引并不是万能的，用好了索引，确实可以提升SQL的查询效率，甚至提升十倍以上。但是用错了索引，也可能会降低查询的效率。

索引是万能的吗

什么是索引（index）呢？

数据库中的索引，就好比是一本书的目录，可以帮我们快速进行特定值的定位查找，从而提高整体查询的效率。

所以，索引就是帮助数据库管理系统高效获取指定数据的一种数据结构。

如果不使用索引的话，我要找一个特定值，就必须从第一条数据开始，一条一条扫描，直到找到我需要的数据。可想而知的慢。

那既然如此，我把所有字段都建上索引不就好了，那整个表岂不是查啥都会很快？

倒也不是。

首先，如果你的数据行数比较少，比如说还不到1000行，这就不需要创建索引了，可能直接遍历的速度还更快，因为启用索引也是有时间开销的。

另外，当数据重复度大的时候，比如高于10%，也可以不对这个字段进行索引。就像我们之前说过的性别字段，如果需要在100w行数据里查找出那50w行性别为男的数据，加上索引也不会变快，因为索引里一般是只保存了主键，即使快速定位到了这50w主键，下一步还是要回表，依次取出这50w数据。

那性别字段真的不适合创建索引吗？

也不绝对。

假设有一个女儿国，100w人里只有10个男性，那么当我们要通过select * from user where gender=1;来筛选出男性的记录时，针对性别建了索引，要比不建索引的效率快的多。

因此我们可以得出结论，索引的价值在于快速定位少量数据。如果需要定位的数据有很多，那么索引就失去了它的使用价值，比如通常情况下的性别字段（通常情况下的男or女 ，不是指老美的近百种性别）。

我们不仅要看字段中的数值个数，还要根据数值的分布情况来考虑是否创建索引。

索引的种类有哪些？

从功能逻辑上来讲，索引主要有4类，分别是普通索引、唯一索引、主键索引和全文索引。

从物理实现方式上来区分，索引可以分为2种：聚集索引和非聚集索引，其中，非聚集索引也被称为是二级索引或者辅助索引。

如果是按照字段个数进行划分，则可以分为两类：单一索引和联合索引。

普通/唯一/主键/全文索引

普通索引是基础索引，没有任何约束，主要是用来提升查询效率的；

唯一索引，是在普通索引的基础上，对字段添加了唯一性约束；

主键索引，是在唯一索引的基础上，对字段添加了非空约束，也就是NOT NULL + UNIQUE，一张表里最多只能有一个主键索引。

全文索引，用的不多，MySQL自带的全文索引只支持英文。

前三种索引其实都是一类索引，只不过是对数据的约束性逐渐提升。不过要注意，单表中，只能有一个主键索引，因为数据存储在文件中只能按照一种顺序进行。

聚集索引与非聚集索引

聚集索引可以按照主键来排序存储数据，主键索引后面就是数据（类似数组，可以简单理解成，索引位置存储的就是对应的数据行），这样子，找到了索引值所在的位置，就相当于找到了数据的位置，在查找行的时候非常有效。

只有当表包含聚集索引时，表内的数据行才会按照索引值在磁盘上进行物理排序和存储。每个表只能有一个聚集索引，因为数据行本身只能按照一个顺序存储。

一般来讲，主键索引或者唯一非空索引，都可以作为聚集索引。

在MySQL的InnoDB里，

• 如果一张表定义了主键索引，那么这个主键索引就作为聚集索引。
• 如果没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索引。
• 如果也没有唯一非空索引，那么InnoDB会生成一个隐藏的自增主键作为聚集索引。

总之InnoDB一定会给自己搞出一个聚集索引，用来指导自身在磁盘上的排序。

非聚集索引是什么呢？

在数据库管理系统中，会有单独的存储空间来存放非聚集索引。这些索引是按照顺序存储的，但是索引里存储的并不是对应的数据行，而是数据行存储的地址。

也就是说，在正式使用的时候，系统会进行两次查找，第一次先找到索引，第二次去索引对应的位置取出数据行。

这不同于聚集索引，聚集索引相当于是索引里存储的就是对应的数据行，索引排序后相当于是对所有数据行排序了。非聚集索引是维护有单独的索引表，它只保证索引表是有序的，而数据行仍然是随机存储的。

复制一下教程里对聚集索引和非聚集索引的总结：

• 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。
• 一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
• 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

前两项总结好理解，该如何理解第三项总结里的"插入/删除/更新时，聚集索引会更慢"呢？

主要是因为聚集索引，其实是面向读取的设计。

由于我们的数据，会按照聚集索引的大小顺序依次写入磁盘，所以当我们更新或者插入一条随机的数据时，所有的记录需要重新排序并按照新顺序重写进磁盘。

而非聚集索引由于维护的只是个索引表，只需要更新下索引表就可以，数据行还是随机存。

所以在数据量很大的情况下，这个效率的差距是很明显的。

单一索引与联合索引

索引列为一列时，是单一索引；

多个列组合在一起创建的索引，叫做联合索引。

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候效率可能会存在差别。

这是因为联合索引的 最左匹配原则 。就是按照最左优先的方式进行索引的匹配。

比如以（x, y, z）的顺序创建联合索引，如果查询条件是where x=1 and y=2 and z=3，就可以匹配上索引；如果查询条件是where y=2，就匹配不上联合索引。

其实就是说，如果联合索引最左边的字段没有进入查询条件里，那么联合索引就无法发挥作用。

总结

对where字句的字段建立索引，可以大幅度提升查询的效率。

采用聚集索引进行数据查询，比使用非聚集索引的查询效率略高。

索引其实还存在一些不足，比如说占用额外的存储空间、会降低数据库的写操作性能等，如果有多个索引的话，还会增加索引选择的时间。因此在使用索引的时候，需要在效率和维护代价之间做平衡。

参考文献

1. 23丨索引的概览：用还是不用索引，这是一个问题