目录
一、执行一条SQL语句后发生了什么？
1、连接器：建立连接，管理连接、校验用户登陆身份
2、查询缓存：查询语句如果命中缓存则直接返回，否则继续往下执行。但是MySQL8.0中已删除该模块。
3、解析SQL语句：通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型。
4、执行SQL语句（其中分为三个阶段）：1.预处理阶段：检查表或字段是否存在，将select * 中的* 符号扩展为表上的所有列。2.优化阶段：基于查询成本的考虑，选择查询最小的执行计划。3.执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端。
二、MySQL的数据是如何存放的？
2.1、MySQL中表空间文件的结构
表空间由段（segment）、区（extent）、页（page）、行（row）组成，在InnoDB存储引擎中结构如下：
段（Segment）：表空间是由各个段所组成的，而段又是由多个区组成。段一般分为数据段、索引段和回滚段等。
索引段：存放B+树的非叶子节点的区的集合
数据段：存放B+树的叶子结点的区的集合
回滚段：存放的是回滚数据的区的集合，用于实现MVCC实现多版本查询机制
区（Extent）：在 InnoDB 存储引擎中是用B+树来进行数据处理的，B + 树中么一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，那么磁盘查询时就会有大量的随机 I/O ，随机 I/O 是非常慢的。
解决办法：让链表中的相邻页的物理位置也相邻，这样就可以使用顺序 I / O 了，在进行范围查询时性能就会很高。解决办法就是：在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配，而是按照区（Extent）为单位分配。每个区的大小为 1 MB，对于 16 KB的页来说，连续的 64 个页就会被划为一个区，就可以使得 B + 树中链表相邻的物理位置也相邻，就能够使用顺序 I / O 了。
页（Page）：记录是按照【行】来存储的，但是数据库的读取并不是以【行】为单位，否则每提取一次就会进行一次 I/O 操作，且只能处理一行数据，效率很低。在 InnoDB中，数据是按照【页】为单位来读写的，每当需要读取一条记录时，并不是仅仅将所需要的【行】读取出来，而是以【页】为单位，将其整体读入内存中。默认每个页的大小为 16 KB，也就是最多保证 16 KB 的连续存储空间。【页】也是InnoBD存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16 KB 为单位的，一次最少从磁盘中读取 16 KB 的内容到内存中，一次最少把内存中的 16 KB 内容刷新到磁盘中。而表中的记录存储在【数据页】中。
2.2、InnoDB中的行格式
行格式（row_format），就是记录的存储结构。
在 InnoBD 中提供了四种行格式，分别是 Redundant 、 Compact 、 Dynamic 和 Compressed 行格式。
Redundant 是一种比较老的行格式，现在很少使用，是一种不紧凑的行格式。而其它三种都是紧凑的行格式，目的是为了在一个数据页中能够存放更多的行记录， Dynamic 和 Compressed都是基于Compact改进而来的，在 MySQL 5.7 版本之后默认使用 Dynamic 行列式。
此次用来说明的就是以 Compact 行列式为例，它的结构长这样子：
接下来我将逐个说明各个字段的作用：
变长字段长度列表：大家都知道在MySQL中的字符变量有两种，分别是 varchar( n ) 和 char( n )，其中 char 是定长的， varchar 是变长的，变长字段实际存储的数据长度是不固定的，所以在进行数据存储的时候就需要将数据占用的大小存起来，放到【变长字段列表】中去。而【变长字段长度列表】所占用的字节数 = 所有的【变长字节长度】占用之和，具体情况为：如果变长字节字段存储的最大字节数小于等于 255 字节，就会用 1 字节表示【变长字段长度】，大于255则会用 2 字节表示。当数据表中没有变长字段的时候，这时候表里的行列式中就不会有【变长字段列表】以节省空间，在【变长字段列表】中各个字段的长度是逆序排放的，并且其中不记录字段中值为 NULL 的数据。下面举个🌰
记录 A ：name 列值为 abc（占用 3 字节），num 列值为 1564（占用四字节），game 列值为 cfhdx （占用5字节） addr 列值为 NULL，flag 和 vis字段不是变长字段，不用理会。它在行列式中的【变长字段长度列表】中的顺序就是：【05 04 03】，其中记录的数字与行列式中字段的排列顺序相反，并且其中不记录值为 NULL 的字段的长度。
为什么要这么设计成逆序呢？
答：在行列式格式中的【记录头信息】中指向下一个记录的指针，指向的不是下一条记录的记录的首部位置，而是在【记录的额外信息】和【记录的真实数据】之间（也就是上图红色箭头的位置）。这样的好处就是向左就是记录头信息，向右就是真实数据，并且在向左向右访问的时候各自访问的数据都是对应的，提高了访问速度。还有一个原因是这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，可以提高 CPU Cache 的命中率。同理， 【NULL 值列表】中的信息也需要逆序存放。
NULL 值列表：在表中的某些列可能会存储 NULL ，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，此时 Compact 行列式把这些 NULL 值都存储在【NULL 值列表】中 。如果数据表中的字段都定义为 NOT NULL的时候，这时候表里的行列式中就不会有【NULL值列表】了，所以通常建议将字段设置为 NOT NULL，这样就可以节省一个字节的空间（【NULL值列表】至少占用一个字节）。
存放规则：如果允许 NULL 值的存在，则每个列都对应一个二进制位（bit），按照列的逆序排列，如果二进制位为 1 代表该值为 NULL，为 0 则不为 NULL。举个🌰：
此时有一条记录A，其中字段有 a b c 三个字段，a字段为 NULL，bc不为 NULL，此时的【NULL值列表】为：00000001，不足八位的在前面补 0。
记录B，其中有 9 个字段a b c d e f g h i，abc字段为NULL，其余不为 NULL，此时【NULL值列表】为：00000001 11000000，超过了 8 位的继续补 8 位，然后与记录A一样的规则。
记录头信息：其中有很多内容，但是有三个比较重要：
delete_mask ：标识此条记录是否被删除。在执行 delete 语句的时候，不会将记录真正删除而是将这个记录的【delete_mask】标记为 1 。
next_record：下一条记录的位置。记录与记录之间只通过链表组织的。
record_type：表示当前记录的类型，0 表示普通记录，1 表示 B + 树中非叶子节点记录， 2 表示最小记录 ， 3 表示最大记录。
row_id：如果我们在建表中没有指定主键或者唯一约束列，那么 InnoDB 将会为记录添加 【row_id】隐藏字段，它不是必需的占用 6 字节。如果有主键或者唯一约束列那么就没有此字段。
trx_id：事务的 id ，表示这个数据是由哪个事务生成的。【trx_id】是必需的，占用 6 个字节。
roll_pointer：记录上一个版本的指针，是必需的，占用 7 个字节。
在 MVCC 机制（多版本并发控制）中，【trx_id】 和 【row_id】起到了很大的作用。
三、字段长度规定
3.1、一行记录长度最大值
在MySQL中，除了TEXT、BLOBs这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度不能超过 65535 （2^16 - 1）个字节。也就是说除了上述条件之外的一行记录总长度不能超过 65535 个字节。下面使用 varchar 来举个🌰。
计算 varchar（n）中最大能存储的字节数，字符集采用ASCII字符集，一个字符占用 1 字节。
单字段情况：create table test1( 'Name' varchar (65535) NULL);
此时会创建失败，提示Row size too large，其中这一行的长度包括了【变长字段长度列表】和【NULL 值列表】所占用的字节数。利用前文的计算方式与总长相减即可：65535 - 2 - 1 = 65532创建成功！但是在UTF - 8 的字符集下一个字符最多需要三个字节，其中 varchar( n )中的 n 的最大值就是 65532 / 3 = 21844
3.2、行溢出后怎么办？
在MySQL中，磁盘与内存交互的基本单位是页，一个页的大小一般是 16 KB，在InnoDB中如果一个页中存不了一条记录，此时就会发生行溢出，多出的数据就会存放到【溢出页】中。
当发生行溢出的时候，InnoDB 会在记录真实数据出保留该列的一部分信息，把剩余的数据放在【溢出页】中，用真实数据处的 20 字节存储指向溢出页的地址，从而找到剩余数据所在的页。
但是 Compressed 和 Dynamic 这两个行列式处理溢出的方式就有点不同，在它俩的记录真实数据处不会存储该列的部分数据，只存储 20 个字节的指针来指向溢出页。
四、InnoDB 缓冲池中的 Buffer Pool
4.1、Buffer Pool 是什么？
MySQL的数据是存放在磁盘中的，如果每次都从磁盘中获取（众所周知磁盘读写速度相比于内存巨慢）性能会很弱鸡。所以 InnoDB 为提升查询性能设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。
有了缓冲池之后，每当读取数据时，如果存在 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，反之才会去磁盘中读取。每当修改数据时，首先是修改缓冲池中的数据，将其设置为 “ 脏页 ” ，最后交由后台程序写入到磁盘中。
它有多大呢？ Buffer Pool 在MySQL中启动的时候，向操作系统中申请了一片连续的内存空间，默认为 128 MB ，但是可以通过 innobd_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置为可用物理内存的 60% ~ 80%.。
4.2、Buffer Pool 缓存是什么？
InnoDB 会把存储的数据划分为若干个页，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16 KB。而 Buffer Pool 同样需要按页来进行划分。在申请一片连续的内存空间之后，按照默认的 16 KB的大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页，在数据库刚运行时，这些页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。所以在MySQL刚启动时，使用的虚拟内存空间很大，而使用的物理内存空间很小，这就是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，再将虚拟内存和物理地址建立映射。
Buffer Pool 中除了索引页和数据页之外还包括一些其他的页，如：插入缓存页、undo页、自适应哈希索引、锁信息。
4.3、如何管理 Buffer Pool ？
为了更好的管理这些在 Buffer Pool 中的缓存页， InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括了：缓存的表空间、页号、缓存页地址、链表节点等等。
其中控制块也占有内存空间，放在Buffer Pool 的最前面，接着才是缓存页。它长这样子：
图中的绿色区域被称为碎片空间。当分配到的每一对控制块和缓存页数量足够多的时候，剩余的空间不够一对的大小，子然后无法使用，也就被称为碎片吧，如果很碰巧，设置的刚刚好，也可能不产生碎片。
在 InnoDB 中，查询一条记录时，会将整个页的数据加载到 Buffer Pool 中，加载完成后再通过页里面的页目录去定位到具体的记录。
为了能够快速找到空闲的缓存页， InnoDB 中使用了链表结构，将空闲缓存页的控制块作为链表的节点，这个节点称为 Free 链表（空闲链表）。它长这样子：
Free 链表中有一个个的控制块，每个控制块包含着对应缓存页的地址，相当于 Free 链表节点都对应一个空闲的缓存页。除了有控制块之外，还有一个头结点，包含链表的头尾节点地址，以及当前链表中节点的数量信息等。有了 Free 链表之后，每当需要从磁盘加载一个页到 Buffer Pool 中时，就从 Free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块信息填上，然后将缓存页对应的控制块从 Free 链表中添加。
4.4、管理脏页
设计 Buffer Pool 不仅仅是为了读性能的提升也是为了写性能的提升，也就是在更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后交由后台线程将脏页写入到磁盘中，同样了为了快速找到哪些缓存页是脏的，于是设计出了 Flush 链表，与 Free链表类似，区别在于 Flush 链表中的都是脏页。有了此链表后后台线程就可以遍历它长这样子（是不是很像 Free 链表？）：
4.5、如何提高缓存命中率
Buffer Pool 的大小是有限的，我们当然希望对于一些频繁访问的数据可以一直留在 Buffer Pool 中，而一些很少被访问到的数据希望可以在某些时机被淘汰掉，从而确保 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还保证了常用数据留在 Buffer Pool中。此时，最容易想到的调度算法就是 LRU算法了啦~（操作系统中CPU的缓存也用到了只不过实现方式不太一样）。
简单的 LRU 算法并没有被运用到 MySQL 中，因为简单的 LRU 算法会导致两个问题，分别是预读失效和 Buffer Pool 污染。
MySQL的预读机制（操作系统中也有类似的）：程序是有空间局部性的，靠近当前被访问的数据，在未来很大概率会被访问到。
预读失效：由于预读机制， MySQL 在加载数据页的时候会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 I/O，但是这些被提前加载进来的数据页，并没有被访问，相当于是白做了。就是预读失效。如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾页淘汰掉，如果预读的页一直不会被访问到，就会出现很奇怪的问题，不会被访问的预读页却占用了 CPU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存的命中率。
为了尽量避免预读失效带来的影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能的长。
MySQL的做法：将 LRU 算法进行了改进，将 LRU链表分为了两个部分，一个是 young 区域，另一个是 old 区域。young 区域是在 LRU 链表的前部分，old 区域则是在后半部分，长这样子：
其中 old 区域占整个 LRU 链表的比例 可以通过 innodb_old_blocks_pct 参数来设置，默认是 37 ，代表整个链表中 young 区域和 old 区域比例是 63 ：37。划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页真正被访问的时候，才将页插入到 young 区域的头部，如果一直没被访问就会从 old 区域中移除。不会影响到 young 区域的热点数据。在 young 区的末尾数据会被挤入到 old 区域的头部，而不会有页被淘汰，old 区才会有页被淘汰。
Buffer Pool 污染问题：
当一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 中的所有页都替换出去，导致大量热数据被淘汰。等这些热数据再次被访问的时候，由于缓存未命中，就会产生大量的磁盘 I/O ，MySQL 的性能就会急剧下降，这个过程被称为 Buffer Pool 污染。这个问题并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小也会造成 Buffer Pool 污染问题。又到了举🌰时间啦：
比如在一个数据量非常大的表中，我执行了查询语句，没有走任何索引。（select * from people where name like "%TangYuan%";）这个查询出的结果可能就几个，但是会导致索引失效，整个查询过程是全表扫描的，就会有如下过程：
从磁盘读到的页加入到 LRU 链表中 old 区域头部
此时需要从页里面读取行记录，也就是页被访问的时候，就要将页放到 young 区域头部
接下来拿到行记录的 name 字段和字符串 TangYuan 进行模糊匹配，如果条件符合加入到结果集中
如此执行下去，直到表被扫描完毕，此时，原本 young 区域中的热点数据就会被替换掉，这就是 Buffer Pool 污染问题。
🌰完毕！
像上述这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换。
MySQL的解决方法：在进入到 young 区域条件增加了一个停留在 old 区域的时间判断。在对某个处在 old 区域的缓冲页进行第一次访问时，就在它对应的控制块记录下来这个访问的时间：如果后续的访问时间与第一次的访问时间在设定的时间间隔内，那么该缓存页就不会从 old 区域移动到 young 区域的头部。如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部。由参数（innodb_old_blocks_time）控制的，默认是 1000ms，只有同时满足这两个条件才解决了 Buffer Pool 污染的问题。
4.6、宝子！我来填一下坑~ 脏页什么时候刷新捏？
当引入了 Buffer Pool 后，在修改数据时，首先是修改 Buffer Pool 中数据所在的页，将其设置为脏页，但是磁盘中还是原数据。脏页需要被刷新到磁盘之中，保证缓存和磁盘数据的一致性，但是若每次修改数据都刷进磁盘中，性能会很差，此时需要在某一时间进行批量刷盘（此时就会有宝子有个疑惑，如果不及时刷新的话？数据库突然宕机，未来得及写入的数据不就丢失了嘛？其实不用担心的，InnoDB 的更新策略是 Write Ahead Log 策略，即先写入日志中，再写入磁盘，通过 redo log 日志让 MySQL 有了崩溃恢复能力）。以下几种情况会触发脏页的刷新：
当 Buffer Pool 空间不足时，需要将部分数据也淘汰掉，如果淘汰的是脏页则需要先同步到磁盘中
MySQL 认为空闲时，后台线程会定期将 Flush 链表中的脏页定期刷入到磁盘中
当 MySQL 正常关闭之前，会把所有的脏页刷新到磁盘中
当 redo log 日志满了之后，会主动触发脏页刷新到磁盘中
完结撒花~ 下篇文章是MySQL的索引，其次是事务，再然后是锁，最后是日志哟~
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
