深入理解MySQL数据库索引

深入理解MySQL数据库索引

个人主页：顾漂亮

1. 索引简介

1.1 索引是什么？

MySQL的索引是一种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。

MySQL索引类似于书籍的目录，通过指向数据行的位置，**可以快速定位和访问表中的数据。**就像使用汉语字典的目录（索引）页，可以通过笔画、偏旁部首、拼音等排序的目录快速查到所需要的字。

1.2 为什么要使用索引？

• 在工作或者学习中，如果对索引比较了解，可以写出更高效率的SQL语句。
• 索引可以提升数据检索的效率，在应用程序的运行过程中，查询操作的频率远远高于增删改的频率。

2. 索引需要采用什么样的数据结构？

2.1 Hash – 哈希表

查询和删除数据的时间复杂度都是O(1)，查询速度非常快，但MySQL并没有选择其作为索引的原因是Hash不支持范围查找。

2.2 二叉搜索树

落选原因：

• 最坏情况下，查询时间复杂度为O(N)。
• 节点个数过多无法保证数的高度。
• AVL和红黑树虽然是平衡或者近似平衡，但毕竟是二叉树。
• 由于数据都是在磁盘上保存的，在检索数据时，每次访问某个节点的子节点时都会发生一次磁盘IO，而在整个数据库系统中，IO是性能的瓶颈，减少IO次数可以有效提升性能。

2.3 N叉树

为了解决树高的问题，可以使用N叉树。

Tips：推荐一个数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html。

由上图可知，相同数据量的情况下，N叉树的树高可以得到有效控制，也就意味着在相同的情况下可以减少IO的次数，从而提升效率。但是MySQL认为N叉树作为索引的数据结构不是最理想的。

2.4 B+树

• MySQL使用的是B+树的升级版，一般B+树的叶子节点可以组织成一个单链表，MySQL则将其升级为双向链表结构，更大提升查询和修改的效率。

• B+树是一种经常用于数据库和文件系统等场合的平衡查找树，MySQL索引采用的数据结构，以4阶B+树为例：
  

B+树的特点：

• 能够保持数据稳定有序，插入和修改有较为稳定的时间复杂度。
• 非叶子节点仅具有索引作用，不存储数据，所有叶子节点保存真实数据。
• 所有的叶子节点构成一个有序链表。

B+树和B树的对比：

• B+树的叶子节点中的数据是连续的，且相互链接，便于区间查找和搜索。
• 非叶子节点的值都包含在叶子节点中。
• 对于B+树而言，在和B树相同树高的情况下，查找任意元素的时间复杂度都为log(N)，性能更加均衡。

3. MySQL中的页

3.1 为什么要使用页？

在.ibd文件中最重要的结构体就是Page（页），页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存和磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的。根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是邻近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中就可以直接从内存中直接读取，从而减少磁盘IO，提高性能。

每一页中即使没有数据也会使用16KB的存储空间，同时与索引的B+树中的节点对应，查看页的大小，可以通过系统变量innodb_page_size查看：

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 | # 16384表示的是字节数 16*1024 = 16384
+------------------+-------+
1 row in set, 6 warnings