我们一起聊聊MySQL 索引的底层逻辑

秒速五厘米 2024-02-05 21:39 44阅读 0赞

### **数据结构以及算法** ###

索引的本质其实就是一种数据结构。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找，这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找、二叉树查找等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

![图片][e1d68c07beb81f125e58e5414eafa152.png]

#### **1.1 B-Tree** ####

为了描述 B-Tree ，首先定义一条数据记录为一个二元组 \[key, data\] ， key 为记录的键值，对于不同数据记录， key 是互不相同的；data 为数据记录除 key 外的数据。那么 B-Tree 是满足下列条件的数据结构：

1.  d 为大于 1 的一个正整数，称为 B-Tree 的度。
2.  h 为一个正整数，称为 B-Tree 的高度。
3.  每个非叶子节点由 n-1 个 key 和 n 个指针组成，其中 d<=n<=2d 。
4.  每个叶子节点最少包含一个 key 和两个指针，最多包含 2d-1 个 key 和 2d 个指针，叶节点的指针均为 null 。
5.  所有叶节点具有相同的深度，等于树高 h 。
6.  key 和指针互相间隔，节点两端是指针。
7.  一个节点中的 key 从左到右非递减排列。
8.  所有节点组成树结构。
9.  每个指针要么为 null ，要么指向另外一个节点。
10. 如果某个指针在节点 node 最左边且不为 null ，则其指向节点的所有 key 小于 v(key\_1)，其中 v(key\_1) 为 node 的第一个 key 的值。
11. 如果某个指针在节点 node 最右边且不为 null ，则其指向节点的所有 key 大于 v(key\_m) ，其中 v(key\_m) 为 node 的最后一个 key 的值。
12. 如果某个指针在节点 node 的左右相邻 key 分别是 key\_i 和 key\{i+1\} 且不为 null ，则其指向节点的所有 key 小于 v(key\{i+1\}) 且大于 v(key\_i) 。

如下是一个 d = 2 的 B-Tree 示意图。

![图片][5f51ed0ff646e52d63aebb6758dbda25.png]

由于 B-Tree 的特性，在 B-Tree 中按 key 检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的 data ，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到 null 指针，前者查找成功，后者查找失败。B-Tree 上查找算法的伪代码如下：

BTree_Search(node, key) {
        if(node == null) return null;
        foreach(node.key)
        {
            if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
        }
        return BTree_Search(point[i+1]->node);
    }
    data = BTree_Search(root, my_key);

关于 B-Tree 有一系列有趣的性质，例如一个度为 d 的 B-Tree ，设其索引 N 个 key ，则其树高 h 的上限为 log\_d((N+1)/2) ，检索一个 key ，其查找节点个数的渐进复杂度为 O(log\_dN) 。从这点可以看出， B-Tree 是一个非常有效率的索引数据结构。

#### **1.2 B+Tree** ####

B-Tree 有许多变种，其中最常见的是 B+Tree ，例如 MySQL 就普遍使用 B+Tree 实现其索引结构。与 B-Tree 相比， B+Tree 有以下不同点：

1.  每个节点的指针上限为 2d 而不是 2d+1 。
2.  内节点不存储 data ，只存储 key ；叶子节点不存储指针。

如下是一个简单的 B+Tree 示意。

![图片][e6ee6940db1fb644ee33001c1612a3ef.png]

由于并不是所有节点都具有相同的域，因此 B+Tree 中叶节点和内节点一般大小不同。这点与 B-Tree 不同，虽然 B-Tree 中不同节点存放的 key 和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中 B-Tree 往往对每个节点申请同等大小的空间。一般来说， B+Tree 比 B-Tree 更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论。

#### **1.3 带有顺序访问指针的 B+Tree** ####

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 的基础上进行了优化，增加了顺序访问指针。

![图片][122dcbff125efde7d263b178c5d398d6.png]

如图所示，在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree 。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

#### **1.4 为什么使用 B-Tree / B+Tree** ####

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用 B-/+Tree 作为索引结构，这一节将结合计算机组成原理相关知识讨论 B-/+Tree 作为索引的理论基础。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取， I/O 存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘 I/O 操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析 B-/+Tree 作为索引的效率。

##### **主存存取原理** #####

目前计算机使用的主存基本都是随机读写存储器 （ RAM ） ，现代 RAM 的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明 RAM 的工作原理。

![图片][b791a08feb89dd596b8badcfbe32c775.png]

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。上图展示了一个 4 x 4 的主存模型。主存的存取过程如下：当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取 A0 再取 A1 和先取 A0 再取 D3 的时间消耗是一样的。

##### **磁盘存取原理** #####

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘 I/O 操作。与主存不同，磁盘 I/O 存在机械运动耗费，因此磁盘 I/O 的时间消耗是巨大的。下图是磁盘的整体结构示意图。

![图片][c20e6833efe452debda7cd08d4112d5c.png]

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。下图是磁盘结构的示意图。

![图片][ad6d386c8be710378c0b5a672c6e970b.png]

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

##### **局部性原理与磁盘预读** #####

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O 。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。预读的长度一般为页 （ page ） 的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页 （在许多操作系统中，页得大小通常为 4k ） ，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

##### **B-/+Tree 索引的性能分析** #####

到这里终于可以分析 B-/+Tree 索引的性能了。上面说过一般使用磁盘 I/O 次数评价索引结构的优劣。先从 B-Tree 分析，根据 B-Tree 的定义，可知检索一次最多需要访问 h 个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现 B-Tree 还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O 。B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h) = O(log\_dN) 。一般实际应用中，出度d是非常大的数字，通常超过 100 ，因此 h 非常小（通常不超过 3 ）。综上所述，用 B-Tree 作为索引结构效率是非常高的。而红黑树这种结构， h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为 O(h) ，效率明显比 B-Tree 差很多。上面还说过， B+Tree 更适合外存索引，原因和内节点出度 d 有关。从上面分析可以看到， d 越大索引的性能越好，而出度的上限取决于节点内 key 和 data 的大小：d\_\{max\} = floor(pagesize / (keysize + datasize + pointsize)) 。floor 表示向下取整。由于 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，拥有更好的性能。

### **MySQL 的实现** ###

在 MySQL 中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论 MyISAM 和 InnoDB 两个存储引擎的索引实现方式。

#### **2.1 MyISAM 索引实现** ####

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图：

![图片][b8567a7c563928d4ef06c216ed1f0a37.png]

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引 （ Primary key ） 示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引 （ Secondary key ） 在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

![图片][567b53da0a8845d1976ea82ba62b42e0.png]

同样也是一颗 B+Tree ， data 域保存数据记录的地址。因此， MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其data域的值，然后以 data 域的值为地址，读取相应数据记录。MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。

#### **2.2 InnoDB 索引实现** ####

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道， MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

![图片][368ad4466f19bb176e734fcd340a06d9.png]

上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（ MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说， InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

![图片][f45e95a234d29fc4fbd082774aabd4f9.png]

这里以英文字符的 ASCII 码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree ，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

### **总结** ###

本文以 MySQL 数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是， MySQL 支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此 MySQL 数据库支持多种索引类型，如 B-Tree 索引，哈希索引，全文索引等等。为了避免混乱，将只关注于 B-Tree 索引，因为这是平常使用 MySQL 时主要打交道的索引。

### **参考文献** ###

\[1\] Baron Scbwartz 等 著，王小东等 译；高性能 MySQL（High Performance MySQL）；电子工业出版社，2010 \[2\] Michael Kofler 著，杨晓云等 译；MySQL5权威指南（The Definitive Guide to MySQL5）；人民邮电出版社，2006 \[3\] 姜承尧 著；MySQL 技术内幕-InnoDB 存储引擎；机械工业出版社，2011

[e1d68c07beb81f125e58e5414eafa152.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c533e42b23f741f398c9a628569d98f2.png
[5f51ed0ff646e52d63aebb6758dbda25.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b44acbab2bca407aa2e24f7cbbdd6b21.png
[e6ee6940db1fb644ee33001c1612a3ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bf660187becb47b7a0c03f4c154d507f.png
[122dcbff125efde7d263b178c5d398d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/21d58e7946b14c258ebd498fc89f7f9a.png
[b791a08feb89dd596b8badcfbe32c775.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b12b70cac00c48cb8d8a47fe73f16e65.png
[c20e6833efe452debda7cd08d4112d5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f594c16c8b14447684f9d5e065c638ef.png
[ad6d386c8be710378c0b5a672c6e970b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0ef55a77f66944ed878f821e95e0de57.png
[b8567a7c563928d4ef06c216ed1f0a37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/4bcf435a4766426e955d3b19450e97cb.png
[567b53da0a8845d1976ea82ba62b42e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f1ac6f47880849fe9def60cb9c2891ba.png
[368ad4466f19bb176e734fcd340a06d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8fc6f7d32b5f4c7a92795ad5985f99be.png
[f45e95a234d29fc4fbd082774aabd4f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/06cdd66fafc74021bf04d372a26ef70e.png