从新华字典谈索引

2013年，那时候有个“2000万开房数据”泄露事件，我和单位上的小伙伴，下载数据导入SQL SERVER，发现查询一次要1分多钟，果断加上索引，查询只需几毫秒。那是我第一次意识到索引对查询效率的提升有这么大，随后几年的工作学习中，也积累了一些对索引的见解。同时我在和大家的交流中发现，很多初学者学习索引很困难，老是觉得这是一个很抽象的东西，所以我拿《新华字典》类比，希望能更明确地讲一讲索引。

为了把重点聚焦到索引，这里简单的把《新华字典》看成一个表，表里面有“声母、韵母、音调、汉字、偏旁部首的笔画、偏旁部首、其余部分的笔画、释义”等字段；另外《新华字典》有两种查询目录可以看成是两个索引，一种是根据拼音查字（索引1：声母、韵母、音调、汉字）、另一种是根据偏旁部首查字（索引2：偏旁部首笔画、偏旁部首、其余部分的笔画、汉字）。

1 索引的分类，这里从三个比较有代表性的角度来对索引分类

1.1 聚集索引和非聚集索引

聚集（clustered）索引，也叫聚簇索引。

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

只看定义确实是有点抽象，那么结合《新华字典》的例子，正文与索引1的顺序完全相同，那么索引1就是聚集索引。那么聚集索引有什么特征呢？

查询方面：在查询连续的信息时，聚集索引可能会更加快速，比如要求你把声母为K的汉字释义查询出来，那么我只需要根据索引1查询到第一个声母为K的汉字在哪一页，然后翻到正文，一个一个看下去，到声母不是K的时候就已经查全了。对比非聚集索引：要求把“忄（竖心旁）”的汉字释义查询出来，那么我需要不断的重复查询索引2，翻到正文，直到索引2中所有竖心旁的汉字都查完了，才能说查全。结合数据库来表述：在通过聚集索引查询连续的信息时（>、>=、<、<=、between...and...、LIKE 'K%'），只需要读取一次索引；而通过非聚集索引查询连续的信息时，需要反复读取索引。

插入、删除方面：假设修订字典，需要增加一个汉字（啊ā），因为这个字在索引1中排序比较靠前，所以不仅仅是索引1需要重新排版，几乎整个正文都需要重新排版；如果需要增加一个汉字（做zuò），显而易见，正文只有一小部分需要重新排版。所得得出一个结论：按聚集索引的顺序插入数据，效率最高。

1.2 覆盖索引和非覆盖索引

覆盖索引（covering index），也有称索引覆盖
如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。

比如说：老师要求你把声母为K的汉字抄写一遍，只要求抄写拼音和汉字、不抄写释义，那么翻开字典直接抄写目录1就可以，不需要翻到正文。

1.3 复合索引和单一索引

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)

举例说明的两种索引都是复合索引。与之相对应的概念是：单一索引。另外有一对概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引。字典例子中的两个索引都是宽索引。

2 索引对查询的影响

开头已经写过了，一个需要1分多的查询，通过建立索引优化到了毫秒级，说明索引对查询是有正面影响的。但是不合适的索引并不能对查询造成影响。那么哪些情况下，索引没有起到应有的作用：

2.1 查询语句中where字段的顺序与索引顺序不一致

还是《新华字典》 的例子，要求找出韵母为“uai”且声母为“k”的字， 如果是我们翻字典，看到这个需求，肯定就会先查声母再查韵母，但是数据库引擎有时候就没有这么聪明，这时候可能就不通过索引，而是全表扫描。

2.2 以通配符开头的LIKE查询

如果要求查询声母为zh、ch、sh的汉字，你可能会通过LIKE '_h'来查询，但是这些查询是无法利用索引的，我们结合拼音的相关知识，可以肯定h结尾且字符长度为2的声母只有zh、ch、sh，但是数据库引擎并不能这么智能，多以全表扫描。同理对于组合索引（A、B），只查询第二个字段（B = 1），也可以看成是这一类原因导致的索引不起作用。

2.3 取值范围较小的情况

比如说音调，拼音的音调只有五种（包括轻声），假如这本字典专门有一个目录，是按音调来排序，现在要求你找出所有第一声的汉字的释义，那么相对而言，直接翻正文（全表扫描）可能都比重复的查询目录再跳到正文要来得快。在数据库引擎中，对取值范围较小的字段建立索引是毫无正面意义的。

3 索引对插入、修改、删除的影响

一本《新华字典》只有两个目录，如果再加上一个目录（韵母、声母、音调、汉字）是不是就可以完美解决2.1中的问题呢？对于查询需求来说，确实如此，这样无论" 声母='k' "在前，还是“ 韵母='uai' ”在前，都有索引可用。但是又会有新的问题：首先字典变厚了，印刷的成本上升；其次需要多制定一个不常用的目录，增加了不必要的排版成本。在数据库设计中就是：增加了硬盘空间消耗、降低了增、改、删效率。

4 总结

数据库的一些特性，其实可以从生活中找到类似的“参照物”，因为一切工具（包括数据库）设计出来都是为了解决现实问题，有些问题不仅仅是在程序设计的时候遇到过、解决过。在线下环境，我们各行各业的前辈都已经遇到并很好的解决了，学习过程中遇到一个抽象的概念，可以寻找“参照物”帮助理解；生产环境下遇到一个棘手的问题，也不能只从程序设计的角度来思考，三人行必有我师，增加人生阅历对编程是很有帮助的。