也许很多人都背过 MySQL 调优的口诀,但是从来不理解为什么这样子写出的 sql 语句,可以有更高的性能。
而要理解其中的原由,就必须对 MySQL 底层的做一定的了解。
同时,为了进大厂,你也必须学会,才能去和面试官喷。。
下面我给出几道题目,你可以做一个自我检测:
- 什么叫 4K 对齐
- 如何存储空值数据
- 如何存储可变长数据
- 大 value 如何存储
- 什么是聚簇索引
- InnoDB 没有定义主键会怎样
- 为什么推荐自增 id
- 什么是页目录
- 查询起始页是否会改变
- 什么是覆盖索引
- 为什么要符合最左前缀原则
熟悉 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 还有很多知识需要学习,所以要做到真正精通,还需要大家不断努力。