精通mysql索引机制，你就不用再背sql优化口诀了！！（万字长文）

也许很多人都背过 MySQL 调优的口诀，但是从来不理解为什么这样子写出的 sql 语句，可以有更高的性能。
而要理解其中的原由，就必须对 MySQL 底层的做一定的了解。

同时，为了进大厂，你也必须学会，才能去和面试官喷。。

下面我给出几道题目，你可以做一个自我检测：

1. 什么叫 4K 对齐
2. 如何存储空值数据
3. 如何存储可变长数据
4. 大 value 如何存储
5. 什么是聚簇索引
6. InnoDB 没有定义主键会怎样
7. 为什么推荐自增 id
8. 什么是页目录
9. 查询起始页是否会改变
10. 什么是覆盖索引
11. 为什么要符合最左前缀原则

熟悉 MySQL 的都知道 MySQL 有各种各样的存储引擎（InnoDB、MyISAM 等等）。
不过由于 InnoDB 支持事务，支持行锁，这样的特性十分适合我们生产环境中去使用，因而我们大部分情况下使用的都是 InnoDB 存储引擎。
我下面也是对 InnoDB 做详细地描述。

数据页

我们都知道，我们一般要用到 MySQL 数据库，一般都会将数据持久化到磁盘，而不是存储在内存中。
因为内存断电易失，此外，就是容量有限。

比如我们要在计算机磁盘上存储一段数据，那就会把它存在文件里，比如叫 “我很帅.txt”；
那我我们要查找数据的时候，要怎么去找，比如说找到里面的 “风流倜傥、玉树临风……”；
Linux 当中有 grep，awk 等等等等的命令，你也可以用 java 语言写一个程序，去查找。

不过这就涉及到一个概念，叫寻址，我们的磁盘寻址，一般是毫秒级别的；
然后，又涉及到带宽，一般几百兆，或者能达到一两个G。

然后做个简单的对比，对于内存，寻址则是纳秒级别的。
我们都知道 纳秒 <微秒 < 毫秒，1 毫秒 = 10^6 纳秒。
而且内存的带宽也要高于硬盘。

所以到目前为止，我们计算机的 I/O 都是瓶颈。
所以对于我们的硬盘来说，一次读取数据，绝对不会是一个字节一个字节去读的，因为磁盘的 I/O 是一个极慢的过程。

所以有这么一个概念，叫 4K 对齐，我们格式化磁盘的时候，也会看到有个东西叫 4K 对齐，那什么叫 4K 对齐？
就是我们读磁盘当中的数据的时候，假设我们就读一小点数据，几个字节；
但是真正从磁盘当中读出的数据，至少要有 4K，不管你用不用得到，都会存储到内存空间中（所以读出的数据永远是 4K 的倍数）。
假设你要读边上的数据了，那么就不用再去磁盘中去找，就可以节省时间。

这就是因为 I/O 这一瓶颈而不得不这么做。

而在我们的 MySQL 数据库中，对于我们的 InnoDB 存储引擎，存储的基本单位就是数据页；
存储的时候，默认的页要有 16KB 的大小。
所以我们在读取数据的时候，每次不是指读出你想要的那几条，一次至少读出的数据，就是一页，16KB。
而存储的时候，一次也最少要写入一页的数据。

那么我们接下来就要考虑数据页中是如何存放我们的数据的了。
（我不会把页里面所有的内容都列举出来，我们只需要分析有关的内容，便于理解）

首先，对于我们大量数据的存储，既然要分页，那么必须给每一个页标上一个唯一的号码，那么我们才能确认哪一页是哪一页；
我们可以将数据存储比作一本书，书有很多很多的页码，假设，一本书的页码全是一个数字，那你按页去分辨内容的时候一定会非常头疼。

行格式

不要急，我们先来看最主要的内容，既然是数据库，那么最重要的，就是要存放我们的数据吧。
废话不多说，先建表：

为了方便使用数据库，我使用了 Navicat。
我们可以看到，新建的表，默认使用了 InnoDB 存储引擎（我的是 MySQL 5.7 版本）。
同时，字符集为 latin1

我们知道，我们的关系型数据库，数据是按行来存储的。
我们在创建表的时候，就会先定义表的行格式，也就是每一列分别存储什么类型的数据，数据占用空间是多少。
我们可以看一下，目前默认的行格式是什么：

我先不说一行里面到底存储了多少内容，我们一点一点来分析，最后就能明白。

首先，我们看一下我们的表结构：

我们的一行肯定要存储数据，所以至少要包含：每个字段所占用的大小。

id：int
首先 int 是一个固定长度的数字，占 32 位，也就是 4 个字节
并且是非空的，也就是无论哪一行，有多少行，这里肯定是要花费 4 个字节的空间来存储这个 int

a：int，不同的是，可以为空。

• 现在我们就要思考一下了，首先，假如有数据，那么占了 4 个字节没错；
  可是要是没有数据呢，为空的情况怎么办？？？
• 我们可以想到，用一种特殊的字符来占据这个位置，来表示，这个位置是空的。
  这么做有点就是，十分简单，而且行的大小不会动态改变；
  但是，缺点显而易见，这里明明不存储数据，但是，就是要占用空间，假设空的字段很多，那岂不是要浪费很多很多的空间？？？
• 于是，我们还可以继续思考，为了节省空间，我们可以选择，不往这里存储数据，但是，这样就会产生问题：
  这里没有数据了，那么空出来的位置，就要去存储下一列的数据，才能不浪费空间，那么数据的位置就不固定，就会出现错乱，我们怎么读到正确的数据呢？

所以，为了解决这个问题，我们来看 COMPACT 行格式（与 DYNAMIC 类似）：
在 COMPACT 行格式中，有一个字段叫 NULL标志位，用来记录，这一行中，哪些字段为空；

现在我们已经了解了 COMPACT 行格式中的一个额外的字段，
也就是说，我们的一行数据，真正的大小，是要大于一行中数据所占的大小的，
因为会有额外字段，去占用存储的空间。
（不过，要是我们设计表的时候，所有的字段都不为空，那么我们就可以节省这一个用来记录空字段的空间了）

我们继续看：

• c：varchar（20）：意味着 c 这个字段，只能存储最多 20 个字符。
  （在老版本中，这个长度表示的单位则是字节，不过现在已经表示字符了；
  因为如果要用字节来表示，很多字符占用的字节数不是固定的，因此很难把控这个字节数到底有多大）
• 我们先想，假如是定长字符串，那么存储空间就很容易把控，因为长度固定，根本不会变
• 那么，像这样，如果是可变长度的字符串呢？
  我们就得去额外的开辟字节，去存储字符的长度；
  这样，我们在读取的时候，才知道应该要读到哪里。

所以，现在我们又知道了，在 COMPACT 行格式中，还有一个额外的字段，是用来存储可变字符长度的；
这样，又再一次说明了，实际上一行记录的内容，要大于实际存储数据的内容。

所以，我们现在做一个测试：
我们建一个表，给出一个 varchar 65535 长度的字段；
不过惊喜地发现报错了：
上面说，最大的行大小为 65535，不过我现在给出的字段就是 65535，按道理应该正好对上最大值对吧。

不过我们上面已经分析过，因为一个行之中，还要额外存储其他的字段，来保证一些特殊情况的处理，如可变长度字符串，空字段；
所以这会额外占用存储空间，就使得行大小会大于 65535，就会存不下了。
这时候，我把它改为 65532，就能成功了（65533 也不行的）。

不过，你们还记不记得，一页的大小是多少？？
16K 对吧，也就是 16 * 1024 只有 16384 个字节的大小；
也就是还存不下我们的这一行数据。

那么，这样的数据该怎么办？
我们想一想，一页存不下，那就两页，三页……
把它分开，在各个页中分别存储每一小段数据。

想法很完美，那么，要实现这样一个目标，就要需要按顺序找出所有的页，
我们知道，数据结构有一种叫链表，
我们要实现这样一种存储方式，就可以在一个页中，存储一部分数据的值，然后再存储下一个页的地址，这样，便可以实现分页存储大段的数据了。

还有一种方式，就是将数据和指针分离，一个页专门存放数据的地址，其他页专门存放数据：

了解了行格式，我们继续往下探究。

页目录

我们现在往数据库里插入 8 条记录：

我们尝试一下把它们全部查找出来，
一看，确实都有了；
不过，有个小细节，就是查出来的数据，默认是按照 id 排好序的。
也就是说，我们的 InnoDB，已经帮我们做好了排序的工作。

之前我们建表的时候，明确指定了，id 就是这张表的主键，然后，存储的数据，会默认按照主键进行排序。
要是我们没有指定主键会怎么样？？？

如果没有的话，那么 InnoDB 会默认挑选一个唯一非空的字段来作为表的主键。
如果连一个唯一非空的字段都没有，那么就会自动生成一个 row_id，但是这个 row_id 对于我们用户来说是不可见的，也不可以被查找。

现在假设，我采用了 MyISAM 存储引擎，设置 id 主键，然后我们往其中插入数据

我们查询一下：
可以发现，我们的查询结果并不是按照 id 主键排好序的。

为什么偏偏 InnoDB 会产生这样的结果？？
那么，我们就要从它的存储结构谈起了。

首先，通过我们的观察，我们会发现，对于 InnoDB 存储引擎，我们插入的数据，会被自动排序。
我们知道，对于线性表来说，随机插入操作的时间复杂度为 O(n)，加入我们的 InnoDB 存储引擎采用顺序线性表来存储我们的数据，那么就很容易造成数据插入效率低下的为问题。
所以，实际上，行与行之间，采用链表的方式来进行相连。

不过我们仔细一想，能这么简单吗？？
我们知道，链表虽然纯插入的操作复杂度低，但是，光要找出指定的数据，就需要从头至尾遍历，查找的时间复杂度为 O(n)，所以效率还是不够高的。
只要一个页个数据量开始变多，那么查询所花费的时间就可能会很长。

为了解决这个问题，在页中还有一部分存储空间，叫页目录（Page Director）。
我们存储在页中的数据，会被分组，其中，用一个页目录来专门存放每一组的起始 id。
（语言表述的话会难以理解，我放一张图）

通过页目录，我们将大量的数据分为少量的组，然后先通过页目录，去查找属于哪一个组；
然后，从那一组的开头，去依次往后查询遍历。

比如，我们要找 id=2 的行记录，我们便可以先通过页目录，找到 1，然后是 3；
因为 2 比 1 大，然后又比 3 小，
这样，我们就可以确定，2 在第一组；
所以，我们从页里面的 1 的行，开始往后寻找，就可以找到 2。

这样，在数据量上升之后，查询的效率就会明显得到提高。

不过，在页目录中，并不需要用遍历的方式去查询，
首先，在页的目录中，我们只需要存储 id 主键，不用存储其它的字段数据；
而主键通常很小，所以页目录也很小；
所以我们完全可以，将其作为一个数组，然后采取二分查找的方式，去进行查找，
这样就可以进一步提升查询效率。

聚集索引

到这里，我们已经探讨了，在一个页的情况下，我们要怎么存储数据。
由于一个页，大小只有 16KB，那么存储的数据总是有限的，所以，我们真正的生产数据，肯定会存到很多很多的页中去。
那么，当数据分页之后，又是什么样子的呢。

如果你还不知道，先别急着看答案，
按照道理，你想一想，数据应该用什么方式去存储。

首先，既然分页了，那么我们必须有一种结构，能把所有的页都找到。
仿照着之前行的存储方式，我们也可以将其存储成链表的样子。
在第一页上，记录下第二页的地址，我们就可以一页一页去查找。

但是，如果只是这样，那可不行。
就如同之前所描述的，一页中的多行记录一样，页中的行都要有页目录，更何况可以产生更多的页的数目呢。
所以，类似于页目录，我们还需要额外的一个页，去存储我们的页信息。

就像这样，假如这时候我们需要去查找 id=8 的数据，
我们就会先通过目录页，找到记录所在的那一个页，就是 5 的所指向的那一页；
然后在那一个页中，我们通过页中的目录，去查找到数据行所在的那一组，就会找到 7；
最后，从 id=7 的那一行，往后遍历，就会找到 8 这一行记录。

假设数据量变大，那么查找的效率就会比从头开始查找高处许多：
先从起始页查找，很快就能定位到下一个页，然后再定位到下一个页；
这样最多 3、4 次，就能查找到指定的页；
然后在数据页中，只要按照页目录，迅速检索出行所在的组，然后就能找到。

假设从头往后遍历扫描，那么就会遍历成千上万个页，可见这样的存储结构确实十分高效。

然后看到这里，不知道你有没什么发现，这样一个存储结构像什么？
假设我把数据变多：

这看起来不就是一棵树嘛。
实际上，我们的 InnoDB 底层采用的是 b+ 树。
这里推荐一个演示 b+ 树的网站：

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

可以自己快速的体验和理解 b+ 树，
就像这样子：

而我们之前描述的，迅速查找的页，就叫做索引。
我们上面也已经探讨过了，在 InnoDB 中，必须是有主键的，而有了主键，就一定会有主键索引，
我们刚才演示确实是记录按照 id 排好顺序的。

而主键索引又叫聚簇索引、聚集索引。
意思就是，所有的数据都存储在叶子节点中，
就像这样：

不过虽然采用了 b+ 树的形式确实是大大提高了查询速率。
不过这里又有一个小细节，就是当一个页存不下之后，新增一个页，创建出一个页表，那么查询的起始页就会改变位置。

如果我们的表，它的起始页一直在变化，那么肯定是不好的。
InnoDB 它是怎么做的呢？
首先，它会将起始页复制出一个；
然后，将原先的起始页，修改成为新的页目录。

理清了存储的数据结构，我们现在再来一个问题，
就是为什么我们存储的主键字段，要尽可能的小，并且是递增的？
也就是为什么推荐我们用自增 int 主键？

通过上面的学习，想来你们应该能够明白。
因为我们的页目录，包括目录页，里面都不存储行里面所有的记录，只包含一个 id 索引，和目标地址的指针。
那么，现在，假设我们用 8 字节的 id，加上 6 字节的指针。
那么，一个页，16KB，就可以存放大约 1170 条记录，这样我们的索引目录的数量就会比较少，我们的树的层数、高度就会比较低，就可以减少磁盘 I/O 带来的开销。

这样，假设树的高度为 3，我们的 3 层树，就可以承载 1170 * 1170 * 16KB 的数据量（大约 21G）；
要是树再高一层，那就再乘上 1170，这样，数据量就可以有二十多 TB。
而我们的 I/O 开销，就只有树的高度，这几次。
也就是，假设树有 3 层，我们找到数据行，只需要找 3 页，就能找到，所以，索引是十分高效的。

而且，如果是递增的，那么当数据页不够时，增加新的数据页即可。
否则，如果插在中间，那就得把当前数据页的排在后面的数据，挤到下一页去，产生页分裂，这时非常低效的。

非主键索引

之前我都是再说主键索引，不过 InnoDB 也可以建立非主键索引。
它们唯一的区别就是，聚集索引存储了每一行的所有数据，但是其它的索引，就不会存储行数据，而只是在存储了主键的值。
这样的话，当我们通过某个字段，查找到了主键 id，但是我们不能直接获得其它列的数据，我们还必须通过这个 id，回到主键索引，去查取整个行的记录。

不过我们知道，对于表字段建立索引，可以指定多个字段，共同建立起一个索引，叫联合索引。
我们现在明白，索引是一种 b+ 树的结构，也就是会对字段的值进行排好序，方便我们的查找。

看到这里，你也就应该要能思考出，为什么覆盖索引会有查询优势，
因为，查询的字段，就已经是非主键索引的排序字段了，我们在这棵索引树上可以直接获取到字段的值，
而不用去回表扫描，增大 I/O 的开销。

不过对于联合索引，多个字段的情况，那么它是如何排序的？

现在我先对字段 a、b、c 建立了一个联合索引：

然后我们查询一下索引中的字段，
我们可以发现，确实是排序过的：

• 首先按照 a 排序，
• 其次如果 a 相等，就按照 b 排序
• 再然后，如果 a、b 都相等了，就按照 c 排序。
  
  然后就可以很容易解释，为什么对于联合索引的查询，不按索引顺序来写条件，就会导致索引失效了。
  因为假设，没有 a 字段的明确指定，那么 b 的排序就不是完全按照次序的，a 每发生变化，b 的排序就会错乱；
  只有 a 确定了，那么查询到的 b 就是排好序的，那么就可以进行检索。

就比如：

select * from test where a=1 and b=1 and c=1;

• 这样查找的时候，因为 a 的索引是有序的，就会先找到 a；
• 找到 a 之后，b 就是有序的，就可以通过索引迅速找到 b；
• 找到 b 之后，那么 c 就是有序的，就可以通过索引快速找到 c。

那么假设 sql 是这样：

select * from test where a=1 and c=1;

• 这样查找的时候，因为 a 的索引是有序的，就会先找到 a；
• 但是由于 b 没有确定，所以 c 无法保证有序，就无法通过索引再进行快速分治查找。
• 因而 c 索引失效

那么再假设 sql 是这样：

select * from test where and a=1 and b>1 and c=1;

• 这样查找的时候，因为 a 的索引是有序的，就会先找到 a；
• 找到 a 之后，b 就是有序的，就可以迅速查找到 b=1 的位置，然后，依次往后遍历出所有的 b 都是大于 1 的；
• 但是，由于 b 有很多很多个符合要求，所以 b 也不是固定的，那么就导致 c 无法保证有序，因而 c 就无法通过索引分治快速查找
• 所以，c 索引再次失效。

作者的话

学到这里，那你应该能大致理解，对于 InnoDB，是如何存储数据的，索引又是一种什么东西；
那么，在对于数据库 sql 语句的编写，你也能大致明白，底层的逻辑是什么样子的，也就能因此避免掉一些性能低效的 sql 语句；
这样，你也就不用去刻意背诵 sql 优化口诀，而是能真正明白，怎样的 sql 是高效的。

不过，我这里也只是分析了底层的存储结构，关于 InnoDB 还有很多知识需要学习，所以要做到真正精通，还需要大家不断努力。