为什么索引可以提升查询速度？

官方定义：索引是帮助存储引擎高效获取数据的一种有序的数据结构。

提取句子主干：索引是有序的数据结构。

基于快速查找的数据结构有很多，MySQL采用的是 B+Tree。

为什么采用B+Tree而不是其他的呢？
B+Tree相比其他数据结构有什么优点呢？

其他数据结构

了解B+Tree之前，先了解一下其他的数据结构，看看它们有什么问题，再看B+Tree是如何解决这些问题的。

二叉树

二叉树的特点：任何一个节点，左边的节点都比它小，右边的节点都比它大。

但是普通的二叉树有一个弊端：不适用于单边增长。

如上图，对于单边增长的数据，二叉树会完全失去平衡。
这意味着，对于自增型的主键来说，这种索引完全没用，相当于全表扫描。

红黑树

红黑树是一种平衡二叉树。
它解决了普通二叉树单边增长的问题，会自旋进行自动平衡。

对于同样的6个数据，看看红黑树是如何存放的。

高度比普通二叉树要好很多，但是依然不理想。

H = log₂^(S-1)

如公式所示，可计算出：
理想状态下，1000万的数据量，树的高度为24层，
意味着最差的查询，需要24次磁盘I/O，速度还是没法接受的。

哈希

根据索引列计算哈希码，再根据一一映射关系，只需一次(理想状态)就可以查找到数据。

哈希查找速度虽然很快，但是有一个严重的弊端：不支持范围查询！
这意味着大于、小于这种常用的条件无法使用。

而且，在数据量大的情况下，会存在较多的哈希冲突，速度也会受到一定的影响。

InnoDB是支持哈希索引的，如果不考虑范围查询，也可以使用，MySQL提供了很多选择。

B-Tree

B-Tree解决了范围查询的问题，且在一定程度上解决了高度的问题。

为什么要说“一定程度上”呢？
因为高度问题优化的还不是最好。

可以看到，对于20个数据，高度也仅为4，而且是有序的。

B-Tree和B+Tree的区别就是：B-Tree会在所有节点中存放数据，而B+Tree只在叶子节点中存放数据。
这使得，B+Tree的非叶子节点可以存放更多的索引，可以最大限度的降低树的高度。

B+Tree

首先直接看一下B+Tree的结构是怎样的。

B+Tree叶子节点拥有所有的元素，且是有序的。
非叶子节点不存储数据，来存放更多的索引，部分索引做冗余。

借用两张图，可以更好的理解B-Tree和B+Tree的区别。

B-Tree

B+Tree

Innodb数据存放的结构就是B+Tree，如果有主键，则根据主键来构建树，没有主键但是有唯一索引，则根据唯一索引来构建树，如果主键和唯一索引都没有，Innodb会自动为每行数据生成一个“RowID”隐藏列来构建树。

Innodb设计的数据存放结构就是这样的。
数据的存放结构一定是B树，别无选择。
二级索引我们可以选择使用B+Tree或Hash来构建。

页的概念

InnoDB中，有“数据页”的概念，它是InnoDB磁盘管理的最小单元。
类似于操作系统中“页”单位。

每次InnoDB读取数据，最少读取“一页”的数据，即使你只需要一条数据。

B+Tree的每一个节点都是一个数据页，在InnoDB中，默认的数据页大小为：16KB。
可以通过如下命令查看：

SHOW VARIABLES LIKE 'innodb_page_size';-- 16384字节 = 16KB

MySQL通过横向的扩展节点，和只在叶子节点存放数据，来最大程度的降低树的高度，使得可以通过最少的磁盘I/O来查找数据。

粗略计算

通过InnoDB的数据页大小，可以粗略计算一下，在树的高度为3的情况下，InnoDB可以存放多少数据。

计算的假设条件：

• 主键为INT递增，INT占用4个字节(指针占用6字节)
• 每行数据占用1KB磁盘空间

通过以上两个条件，可以粗略计算出，在理想条件下：

• 第一层可以存放的索引数量：1600。
• 第二层可以存放的索引数量：2500000。
• 第三层可以存放的索引+数据 数量：25000000。

InnoDB可以做到：在树的高度仅为3的情况下，存放2500万的数据。
效率还是非常高的。

执行流程

MySQL根据主键查找数据时，首先将第一层节点加载到内存，在CPU中计算，然后根据计算区间去加载第二层的节点，因为已经可以得到明确区间，第二层也只需要加载一个节点，根据第二层的节点再去找第三层的节点。

第三层存放着索引和数据行记录，找到索引就找到数据行了。

在InnoDB中，树的高度为3的情况下，即使是千万的数据，也只需要3次磁盘I/O即可找到数据。

而且通常情况下，InnoDB会将第一层节点缓存起来，意味着只需2次磁盘I/O即可。

聚集索引和非聚集索引

聚集索引：索引和数据行存放在一起，找到索引就找到数据行了。
非聚集索引：索引和数据分开存放，通过索引需要再单独去找数据行。

在InnoDB中，主键就是一种聚集索引，一张表聚集索引只能有一个，因为数据不能分开重复存储。

用户自己创建的索引就是二级索引，也就是“非聚集索引”。
InnoDB的非聚集索引中，存放的是索引和主键的值。
通过索引找到主键，再根据主键去查找数据行，也就是我们常说的“回表”。

在MyISAM中，索引存放的就不是数据行或主键值了，而是数据行所在磁盘的地址指针。
通过索引找到地址指针，再通过指针去找到数据行。

回表

不能通过索引直接获取数据，需要根据索引存放的主键值重新获取数据行的行为称为：回表。

应该尽可能的避免“回表”。
大多数情况下，回表查询的数据是随机分布的，意味着数据分布在不同的数据页中，InnoDB要加载大量的数据页，也就意味着需要进行大量的随机I/O，随机I/O性能是非常低的，特别是机械硬盘，针头需要重新寻道。

根据主键获取数据行的效率是最好的，如果只能使用二级索引，那么尽可能的使用“覆盖索引查询”。

覆盖索引查询

当查询的列能从索引中全部获取时，就无需 回表查询，这样的查询称为：覆盖索引查询。

“无需回表查询”可以极大地提升性能，因为数据可以全部从索引中获取，减少了磁盘I/O。

要满足覆盖索引查询，必须创建多列索引，且查询的条件要遵循“最左前缀”原则。

有序的索引

有序的索引可以让数据快速的被检索到，但是为了维护索引的顺序，InnoDB花了不少功夫。
糟糕的设计会使得InnoDB维护索引特别吃力和艰难。

页分裂

InnoDB的默认数据页大小为16KB，有序的存放着索引和数据行。

当我们使用UUID作为主键时，由于UUID没有规则和顺序，会导致新插入的数据被随机的分散到各个数据页中，一旦分配的数据页是满的，InnoDB就不得不进行“页分裂”。

“页分裂”会额外消耗系统的开销，还会使得索引数据变得稀疏，形成空洞，增大索引文件的大小，占用磁盘空间，降低性能。

除了新增，修改主键的值也会造成“页分裂”，道理是一样的。

应该尽可能的避免“页分裂”，主键最好使用有序的，自增主键就是一个不错的选择。

局部性原理

局部性原理是指CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中。

当程序需要从磁盘中加载数据时，CPU会自动的把我们需要数据的相邻数据也一并加载到内存。
因为操作系统认为：程序接下来很可能会访问相邻的数据，多加载一些相邻数据，可以减少磁盘I/O。

在MySQL中，即使我们只需要查询一条数据，MySQL也会加载一个“页”的数据。
将相邻的数据缓存起来，下一次访问时，就不需要从磁盘中读取了。

而对于使用UUID作为主键/索引的列，由于没有规律和顺序，会导致局部性原理失效。
加载数据缓存几乎不起作用，降低了性能。

页结构

Innodb将“页”作为基本单位，索引和数据都保存在一个个的数据页中。

• Infimum+Supremum：页中最小值和最大值。
• User Records：数据行记录
• Free Space：空闲空间。
• Page Directory：页目录。

数据页中的页目录（Page Directory），可以理解为索引中的索引。
同一个数据页中，可能存在上千个索引，索引之间通过指针连接，使得插入很快，但是查询较慢。
Page Directory的作用是为了在同一个数据页中快速查找。

数据页中存放的数据大致如下图所示：

行格式

MySQL中，数据是按照一行一行来保存的，一个数据行代表一条数据记录。

数据行中，除了保存行数据外，还记录了很多其他的东西，和具体的“行格式”有关。

可以通过如下命令查看表的“行格式”：

SHOW TABLE STATUS LIKE '表名';
-- Row_format Dynamic

Compact行格式

• 对于列中有变长字段的，行的头部会逆序来记录变长的长度，因为对于变长来说，一旦存的数据增多，MySQL都要为其再额外分配磁盘空间。
• 对于列中有允许为NULL值的，“NULL标志位”会记录在这里。因为NULL是不占空间的，一旦赋值，MySQL也要为其分配磁盘空间。
• 记录头信息中包含指向下一条记录的指针。

MySQL之所以在数据行中记录这些数据，都是为了方便对数据行进行扩展。

除了自定义的列外，InnoDB还会在数据行中加入一些隐藏列：

• RowID(6字节)：如果表中没有主键和唯一索引，InnoDB会自动生成一个RowID来构建B+Tree。
• 事务ID(6字节)
• 回滚指针(7字节)

事务ID和回滚指针是InnoDB为了实现MVCC多版本控制而设计的列。
可以在实现事务的同时，尽量减少对数据行加锁。

Dynamic

Dynamic行格式和Compact类似，针对变长字段进行了优化。

对于数据的长度超过了一个“数据页”的大小称为：数据行溢出。

Compact页会保存部分数据，然后记录下一页的地址指针。
而Dynamic只会记录地址指针，数据全部放在其他数据页中，使得同一个数据页可以存放更多的索引记录。

数据文件

MySQL中绝大多数存储引擎都是将数据存储在磁盘中的，MEMORY引擎除外。

InnoDB将数据以二进制的形式保存，保存路径为datadir下以数据库命名的文件夹中，文件名为：表名.idb。

查看数据文件

由于是二进制的，无法直接查看，在Linux环境下，可以使用hexdump查看。

表名：mytest
列：col1、col2、col3、col4

数据记录：
1	aa	bb	cc
2	dd	ee	ff
3	gg	hh	NULL

$ hexdump -C -v mytest.ibd

0000c080  20 80 00 00 01 00 00 00  7b c3 42 e1 00 00 01 59  | .......{.B....Y|
0000c090  01 10 61 61 62 62 63 63  02 02 02 00 00 00 18 00  |..aabbcc........|
0000c0a0  1f 80 00 00 02 00 00 00  7b c3 43 e2 00 00 01 4f  |........{.C....O|
0000c0b0  01 10 64 64 65 65 66 66  02 02 01 00 00 20 ff b0  |..ddeeff..... ..|
0000c0c0  80 00 00 03 00 00 00 7b  c3 48 e5 00 00 01 58 01  |.......{.H....X.|
0000c0d0  10 67 67 68 68 00 00 00  00 00 00 00 00 00 00 00  |.gghh...........|

可以看到，值为NULL的列除了在“NULL标志位”占用一个标记外，不会再占用空间了。

索引失效

索引的目的是为了帮助存储引擎快速检索数据的。

一旦达不到这个目的，索引就会失效，因为即使走索引也没有意义。

什么时候会失效？

查询数据的方式有很多种，返回的结果都一样，但是不同的查询方式性能不一样。

如何找到性能最好的查询方式呢？
这就是MySQL“查询优化器”要干的活了。

不能带来更好的性能

MySQL在执行SQL语句时，内置的“查询优化器”会先分析SQL，基于表的统计信息来生成执行计划。
优化器基于执行成本的方式来判断哪些执行计划是最优的，然后再和存储引擎API去交互查询数据。

一旦优化器认为，走索引并不能带来更好的性能，就不会走索引了，索引就失效了。

例如：一个从1递增的索引，查询条件为：索引列>0。
如果走索引，还需要扫描全部的索引，然后再回表查询，优化器认为：还不如直接全表扫描来得快，这种情况下索引就失效了。

Tips：优化器生成的执行计划不一定都是最优的，涉及很多东西，可以参考笔者以前的笔记。

不能减少扫描范围

索引的目的就是为了使存储引擎通过减少扫描范围来提升检索速度。

一旦索引不能减少查询扫描数据的范围，存储引擎也不会走索引。

例如：多列索引中，不满足最左前缀原则，还是要进行全表扫描的，索引起不到作用。

还包括对索引列进行了计算判断，也是不会走索引的。
对索引列计算出来的值是无法判断的，索引本身就没有参考意义了，只能全表扫描。

Tips：该篇笔记主要记录索引原理，更多查询优化方面的东西需要另起篇幅。