数据库索引、河神、樵夫与《新华字典》

0、结论

1、回顾数据库设计与优化的基本原则：减少磁盘IO次数；

2、数据库的设计从数据的存储组织结构，就开始考虑如何在后续读取、写入过程中减少磁盘IO次数，即数据本身先有序，才能提高检索的效率，比如折半查找（二分查找），数据库实际按照主键顺序存储组织数据；

3、数据只能按照一种顺序存储，但是实际有多种检索的场景，所以索引分为聚簇索引（主键索引）和非聚簇索引（辅助索引）；

4、对数据库索引结构可以类比《新华字典》的使用，但是并不完全一样

5、分布式数据库一般不支持索引，一般通过分区、分桶等其他技术减低磁盘IO次数，实现数据的快速检索

1、樵夫与河神的故事

诚实的樵夫

首先，我们先抛开数据库，从一个熟悉的场景切入，看我们是怎么找到自己想要的事物。

粗心的樵夫，路过一条小河时，不小心把自己吃饭的家伙，也就是斧子，掉进了河里……就好像程序员把电脑丢进了河里、学生把书丢进了河里（高考完了可能是故意的……）。

图1 樵夫与河神

樵夫很幸运，河里有一个好心的河神，而且河底只有三把斧子，所以河神只捞了三次，终于把樵夫的那把铁斧子给捞到了。

如果樵夫没有那么幸运，河底有100把金斧子，100把银斧子，100把铁斧子呢……又或者金、银、铜、铝、铁、锡各来100把呢……

稍微思考一下，会发现这里存在一定的空间：

• 樵夫要想更快速地拿到自己的那把斧子，可以先告诉河神自己的斧子是把铁斧子，而不是捞上来再排除

• 河神应该一次多捞几把斧子，然后再考验樵夫，是这把金斧子、这把银斧子，还是那把铁斧子……

• 斧子掉进河里“很有规律”，按照材质的不同，整整齐齐排在河底，等着河神来找

上面三种优化策略，对应了数据库快速检索的相应的底层策略：

• 按照索引/键去查找，而不是把磁盘上的所有数据都遍历一遍

• 数据不是一行行地单独加载，而是以页为单位，每次加载一个完整的数据页，数据页里有多条数据，之后再在内存中进行下一步检索就行了

• 如果索引/键不能唯一标识出目标数据，符合条件的可能有多条，怎么确保符合条件的都被找到呢，所以数据必须以有序的方式进行存储，从第一个符合条件的开始，直到找到第一个不符合条件的，就可以停止查找了

查找与排序

曾经，我们学过很多种排序算法，快速排序、堆排序、希尔排序、归并排序等，当年不知道有啥用，反正各种面试都让徒手写至少三种排序算法……似乎在实际工作中很少用到，毕竟sort()或者order by自然就得到了有序的结果……其实，排序算法就好像“百姓日用而不知”的“道”，我们每天都会用到，只是我们没有直接的感受到而已。

当年不知道排序有啥用，但现在，从数据库设计优化的角度看，排序是为了更快的查找！

数据库中确保数据有序存储是通过所谓的索引组织表来实现的，为了理解数据库的索引结构，可以对照同样聚焦于数据存储与快速检索的《新华字典》。

2、数据库索引与《新华字典》

数据库中的数据要排序存储起来，怎么排序要考虑清楚，毕竟只能按照一种排序规则进行排序，无法做到“成年的数据库不做选择题，既要、又要、还要”……

数据库中的数据是按照主键索引（你不定义主键也没用，数据库会找到你建表时的唯一非空索引字段，还找不到会帮你创建一个主键字段）进行排序存储的，主流的数据库中数据的存储都是按照B+树的结构存储的：

图2 B+树结构示意图

非叶子节点，存放主键值及相应的指针；叶子节点存放键值对应的数据页。一个数据页，就好像一页字典：

图3 字典示意数据页

通常从树根到叶子节点，一般2-3层也就够了，所以通过主键检索数据，从树根到叶子节点的数据页加载到内存中，只需要2-3次磁盘IO操作。这棵B+树，包含所有完整的数据，又称作聚簇索引、主键索引。

但是，我们实际在使用数据库时，索引不止一个，这不是跟只能有一种排序方式相矛盾了……

再回到《新华字典》，我们可以按照拼音检索、按照部首检索、按照笔画检索等。

图4 辅助索引字典示意图

对照到数据库中，我们可以根据实际检索的场景，创建多个索引，这些索引，叫做辅助索引、非聚簇索引。

这些辅助索引同样是采用B+树的结构在硬盘上存储，不同于聚簇索引的地方在于，叶子节点仅仅存放键值和对应的主键值。实际检索中，如果从辅助索引检索，则在不是覆盖索引（懂的都懂，以后再说）的情况下，定位到主键的键值后，还要到聚簇索引中找到真正数据页，这一步叫做回表。

按照数据库的主键索引组织表，再看一下《新华字典》的组织结构：

1. 新华字典按照拼音+汉字的形式创建了聚簇索引，并按照拼音+汉字的排序规则，将每个汉字有序存储在字典的每一页中，每一页字典，就是这个B+树的叶子结点；

2. 检字表前面，拼音检索索引，可以理解为这种聚簇索引B+树的非叶子节点；

3. 部首检索索引、难检字笔画索引，可以理解为辅助索引，这种索引的B+树，不管是叶子节点还是非叶子节点都只在当前索引目录中。笔画可以理解为非叶子节点，汉字+页码，可以理解为叶子节点。

3、有序的困境

弱水三千，只能取一瓢饮

数据库中的数据，只能按照一种排序方式进行存储，所以主键的设计一定要考虑清楚。选择了一种，就要放弃其他的可能。不是主动的“弱水三千，只取一瓢饮”，而是被动的“弱水三千，只能取一瓢饮”。

涉及到这种排他性的决策，需要合理地评估索引选择的机会成本。

熵增原理

毛主席说过，“一个人做好事不难，难的是一辈子做好事，不做坏事”。

数据库有序真正的困境在于，从数据库表创建之时起，数据的每一次增删改，都要保证数据库的有序，索引的个数越多，增删改维持数据有序的成本越大。

熵增原理（The Principle of Entropy Increase），也称为熵增定律，是热力学第二定律的一个重要内容。它指出，在一个孤立系统中，所有自发进行的过程都会使系统的总熵增加。熵是一个衡量系统无序程度或混乱程度的物理量，因此熵增原理表明，自然过程总是朝着无序或混乱增加的方向进行。

如同熵增原理告诉我们的，自然界的过程总是朝着熵增加的方向发展，即无序和混乱程度增加。数据库的有序是一种有始无终的宿命，一旦开始有序，必须不断努力付出代价，确保其一直有序！

图5 熵增原理

4、参考文献

1、《伊索寓言》

2、《数据库系统实现(第2版)》

3、《MySQL技术内幕 InnoDB存储引擎(第2版)》