MySQL结构流程，看这一篇就够了！

结构流程篇

前言--参考资料

对于MySQL的学习资料已经看过不少了，其中每次系统的学习还整理了思维导图，但是有些知识点在心中总是模模糊糊的，感觉知识总是东一块西一块，没有全部整合在一起

于是我想到了自己开一个MySQL的专栏来将自己学习到的知识点全部串起来

由点到线、由线到面、构建MySQL知识体系

• 参考资料（文中可能使用以下资料的图片）

• 《MySQL是怎样运行的 从根儿上理解 MySQL》
• 《MySQL实战45讲》
• 《MySQL技术内幕 InnoDB存储引擎》

结构

服务端架构

MySQL的服务端主要分为server层和存储引擎层

• server层存在各种各样服务客户端的组件

• 管理连接的连接器
• 分析SQL语法的分析器
• 优化SQL，根据索引生成多套执行计划的优化器
• 调用存储引擎层接口的执行器

• 存储引擎层则是各种各样插件式的存储引擎，作用是与磁盘交互存取数据，文中内容基本上都是innodb存储引擎
  

存储结构

MySQL中的表可以分为俩个文件，分别是表结构文件和表空间文件

表结构文件用于存储定义的表结构信息，以frm结尾

表空间文件用于存储数据，表空间又可以分为共享表空间和独立表空间

独立表空间是每个表独有的，用于存储数据相关信息；共享表空间是所有表共享的，用于存储其他信息，如：回滚信息、二次写信息等

表空间由逻辑段构成，比如存储数据的独立表空间则由叶子节点段、非叶子节点段构成，熟悉B+树的同学会知道叶子节点实际上就是存储真正数据的页；而共享表空间由回滚段等构成

段是由区构成的，区则由连续的页构成，默认页大小为16KB，区大小为1MB，每页上有多条记录，而记录都有多种种类的行格式

为表分配空间时，先以碎片页为单位申请空间，当满足32个碎片页时以区为单位申请空间；这样就避免小表直接用区申请空间会浪费空间

• 存储结构：表结构文件| 表空间文件 -> 段 -> 区 -> 页 -> 记录

• B+树中的数据页（只说一些重点参数，所有参数看了也不一定记得住，有兴趣自己去看）

• file header与page header 记录了一些页的信息，file head中有两个参数是首尾指针指向上一页和下一页，说明页与页之间是双向链表
• infimum和supremum 分别代表该页中最小和最大的记录，数据页是按照某个索引列排序构成的，比如聚簇索引就是按照主键值排序，页中的记录会被分为多个组，每个组是按照顺序形成的，其中每个组的最大值记录到这个组的槽中，而infimum和supremum单独占一个组，是页中最小、最大的组，这些槽维护形成了一个有序列表，当查找记录时可以通过二分法查找
• user records 和 free space 用于存储记录和剩下剩余空间
• page directory 页目录中存放每个组中的槽
• file trailer 与 file head参数对比 用于判断该页是否刷盘成功

• 记录行格式compact

• 变长字段长度列表（逆序的，因为指针指向记录中间从左右两边寻找数据）+ NULL标志位（第几位列是NULL则表示第几位为1）+ 头信息（有next指针，标识了记录之间是单向链表） + 隐藏列（回滚指针、rowid、事务id） + 数据列

缓冲池结构

处理数据需要在缓冲池，比如读某条记录要先判断该记录的数据页是否在缓冲池，不在则要先从磁盘读到缓冲池

访问缓冲池的线程在某些情况下会加锁，如果一个线程锁住整个缓冲池，那么并发能力并不好；利用分段锁的思想，将缓冲池分为多个缓冲池实例，读取数据页时通过页hash到对应实例去处理

缓冲池需要扩容向OS申请更大空间时，要把旧空间数据全复制到新空间；为了避免这种情况，每个缓冲池实例中以chunk为单位申请空间

每个chunk中以缓存页、控制块、碎片组成，缓存页就是缓存的数据页，控制块对应一个缓存页表示它是空闲的free还是脏页flush（需要刷进磁盘的），剩下的空间就是碎片 【图中相同颜色的控制块、缓存页对应】

为了方便管理数据页会使用很多链表，比如free链表用来管理空闲页，flush链表用来管理脏页，lru链表用来管理缓冲池中保存的数据页

innodb支持预读，它可能会将它认为后续用得上的页也预读进缓冲池，还有全表扫描时也会将很多只访问一次的页刷入缓冲池，如果使用普通的LRU算法则会导致缓存命中率大大降低

因此innodb将LRU链表分为热冷数据区，初次访问的页先放在冷数据区，多次访问才放到热数据区，并且控制一段时间内多次访问页不会放到热数据区（这是为了防止全表扫描多次访问页）

进一步的优化：如果访问的页在LRU热数据区头部1/4处则不移动到头部

流程

查询流程

经典的查询流程图

0. 与服务端建立TCP连接，连接池管理由连接器组件处理
   
1. 分析器分析SQL，判断语法等（维护缓存代价高，在高版本移除）
   
2. 优化器优化SQL，根据不同的索引生成多套执行计划，大概计算成本，选择最优的执行计划
   
3. 执行器调用存储引擎层接口获取数据
   
4. 如果数据页不在缓冲池中则需要先从磁盘中加载到缓冲池
   
   从聚簇索引根节点（以innodb 聚簇索引举例），非叶子节点中记录的是对应页的最小值以及对应页位置（如图中根节点），叶子节点中存放记录；无论是叶子节点还是非叶子节点，都会维护页中每组的最大值槽，这是用来使用二分法快速定位的（只是图中根节点没画出来）
   
5. 查找数据5时，在根节点二分法定位到1所在页（具体介绍在叶子节点展开），来到页后查看槽中记录（无穷小、10、80、无穷大）根据二分法定位到无穷小所在组也就是infimum沿着单向链表遍历直到找到5
   

修改流程

以下修改流程不考虑change buffer的情况，change buffer 情况后续讨论；并且redolog、binlog刷盘策略是默认策略

bin log是MySQL层面的逻辑日志，用于恢复、备份数据；而redo log是innodb层面的物理日志，记录页中记录怎么操作的变化，用于恢复数据

1. 先判断数据页是否在缓冲池中，不存在则要先从磁盘中加载到缓冲池
   
2. 找到数据页二分法定位槽遍历到对应记录（查询流程），再对记录进行修改（修改的过程也有可能是先删除再新增）
   
3. 俩阶段提交

• redo log prepare write : 将redo log 从redo log buffer写入文件系统page buffer
• bin log write : 将binlog 从binlog buffer写入文件系统page buffer
• redo log prepare fsync ：将page buffer的redo log刷入磁盘
• binlog fsync：将page buffer的binlog 刷入磁盘
• redo log commit write : 提交事务成功

binlog 与 redolog的两阶段提交是为了数据一致性

如果写完redolog断电，那么binlog没记录，下次恢复时，会导致少更新一次（虽然redolog记录了，但是要按照binlog记录的来恢复）

如果写完binlog断电，redolog没记录，下次恢复时，会导致无法恢复（按照binlog记录的恢复，但是redoLog没记录到如何恢复）

如果每次修改一行记录就将页从磁盘加载到缓冲池中，那么就会出现随机读写的情况

change buffer 使用延迟更新的思想，来优化这种情况，当发生修改时先保存下这个记录（不立即写回页上），等到合适的时机（后台线程主动合并、页正好被加载到缓冲池、索引中空间满了强制合并）更新页中的记录

change buffer是insert buffer的优化，同时对update和delete也做了类似优化

如果使用的是唯一索引则不能使用change buffer，为了保证唯一性，需要先去读记录判断该记录是否存在

总结

结构的铺垫就是为了描述流程

页与页之间使用双向链表，这方便了范围查询，查完该页继续查下一页

页中的记录使用单向链表，其中页中分组，记录每组最大值，维护槽，槽是升序列表，可以使用二分法加速查询，时间复杂度从O(n)提升到O(log n)

非叶子节点中的记录是对应下层页中最小值记录，以及对应页所在位置，同样也会分组维护槽，使用二分法定位页

缓冲池由多个缓冲池实例组成，实例又以chunk来申请空间单位，chunk由控制块、缓存页、碎片组成；缓冲池中使用各种各样的链表来管理页

LRU链表为了避免预读、全表扫描来降低命中率，以热冷数据分区、一定时间内访问多次不放到热数据区等策略来解决降低命中率的问题

binlog是MySQL层面用于恢复，复制的逻辑日志，redolog是innodb层面记录在页上操作的物理恢复日志；binlog、redolog两阶段提交为了数据一致性

change buffer使用延迟更新的思想，避免了随机读写；使用唯一索引时，为了判断记录是否唯一要去读所以不能使用change buffer