Innodb架构图
内存结构
Buffer Pool
在对数据进行**查询**
时,会先将数据页从磁盘中查询出来,先放到**Buffer Pool**
中,不同页之间用链表进行存储。
Change Buffer
在对数据进行**修改**
时,如果数据不在缓冲池中,需要先将数据从磁盘中查询出来,此时因为数据需要被修改,不会直接放到Buffer Pool中,而是会放到**Change Buffer**
中。等到读取数据时,才会将Change Buffer中的内容合并到Buffer Pool
自适应哈希索引
根据Buffer Pool中页的访问频次,建立自适应哈希索引
Log Buffer
在进行Redolog 和 UndoLog的写操作时,不会直接写入磁盘,会先写入到Log Buffer中,顺序写的效率远高于随机写,并且批量写入磁盘可以减少IO次数提高效率。
磁盘结构
DoubleWrite Buffer
https://www.yuque.com/yuqueyonghudhnost/ckqh1t/nu6mhy0h3ao9hgw8
Innodb Data Dictionary
Update语句的执行流程
连接器
- MySQL本质上属于
**网络服务**
,外部与MySQL服务器建立**TCP连接**
首先要进行**三次握手**
。 - 在建立连接后,MySQL会对
**身份信息进行校验**
,如果身份信息符合要求后,则会保存该用户的权限信息,在后续进行语句执行时会根据这里保存的权限信息进行校验。 - 在MySQL服务器与外部应用建立连接后,从数据库连接池中分配一个线程,防止线程的重复建立和销毁。此层也被称作为
**连接层**
。
查询缓存
- 在MySQL8.0后,查询缓存部分被移除
- 此处查询缓存的本质是将查询的
**SQL语句**
和对应的**查询值**
进行保存,即使是语义相等的SQL语句,如果格式不相等也不会查询到缓存的数据。 - 并且如果该
**表执行过修改语句时**
,**缓存的数据会被清空**
,因此该缓存的缓存命中率很低。
解析器
**词法分析**
,分析SQL语句中的单词,区分为一个个关键字,例如 where、from等关键词**语法分析**
,判断单词和条件之间的联系是否正确,如果正确则会构建语法树
优化器
prepare阶段
- 验证SQL语句中的表和字段是否存在
- 将SELECT * 转换为表的所有字段
优化器
负责将SQL语句的执行方式确定下来,例如在表中有多个索引时,需要**确定使用哪个索引或者是否使用索引**
执行器
- 具体执行SQL语句
- 索引下推(ICP)是在该部分进行优化
SQL执行
- 进入到执行器进行SQL语句的执行,MySQL对数据进行操作需要在
**Buffer Pool**
中进行,如果Buffer Pool中**没有**
对应的数据,需要先将数据从**磁盘**
中读取到Buffer Pool中。MySQL中数据的**操作单位是页**
,因此不会只读取几条数据,而是将**相关联的数据页**
进行加载到**Buffer Pool**
中。 - 在数据页读取到Buffer Pool中后,此时开始进行数据的操作,再真正操作数据页前,需要先记录
**UndoLog**
,用于保证数据能够正常回滚。 - UndoLog记录的是
**逻辑日志**
,**即记录的是逻辑相反的SQL语句**
**,**如果事务需要**回滚**
时,则需要执行UndoLog的语句来保证数据的回滚,在UndoLog**持久化到共享表空间 ibdata**
后,UndoLog才持久化完毕。 - 在保存完毕UndoLog后,开始执行Buffer Pool中数据页的数据,修改后的数据页被称为
**脏页**
,脏页不会马上刷入到磁盘中,会有**单独的线程**
通过**Double Write Buffer**
机制保证脏页刷盘的准确性。 - 在操作完毕数据页后,此时会记录Redolog保证事务的
**原子性**
,Redolog记录的是**物理日志**
,在事务提交后能通过Redolog保证语句一定被执行完毕。 - Redolog的写入需要先写入到
**Redolog Buffer**
中,RedoLog需要从RedoLog Buffer中通过**write**
写入到**Page Cache**
(文件系统缓存)中,在Page Cache中发生宕机仍然会丢失数据,然后再通过**fsync**
刷盘真正将数据保存到磁盘中,此时的RedoLog称为**prepare**
阶段。 - UndoLog和RedoLog都是在
**存储引擎层**
做的事情,此时到事务提交后,需要由**Server层**
保存**Binlog日志**
,用于复制和恢复数据。 - Binlog日志需要先保存到
**Binlog Buffer**
中,需要先通过**write**
命令写入到**Page Cache**
中,然后再通过**fsync**
刷盘将数据真正保存到磁盘中。 - 此时需要进行RedoLog的
**commit**
阶段,也要进行**write**
和**fsync**
阶段,当事务失败时,如果判断到RedoLog已经为**commit**
状态,此时则认为事务执行完毕,直接提交。 - 如果RedoLog中只有
**prepare**
阶段的日志,则需要**判断binlog日志是否完整**
,如果binlog日志是完整则,则认为事务已经执行完毕,可以提交,否则需要进行回滚操作。 - 当写入RedoLog和Binlog都写入完毕后,需要将Buffer Pool中的脏页数据写入到磁盘中,会有单独的线程在
**CheckPoint**
会定期将脏页写入到磁盘中。 - 会先将脏页写入到
**Double Write Buffer**
中,此时该Buffer仍然处于内存中,如果此时机器宕机,需要通过RedoLog进行数据的恢复。 - 在脏页写入到
**Double Write Buffer**
中后,此时会将数据**一次性写入**
到共享表空间**ibdata**
中,然后在通过多次写入将数据写到**磁盘表文件idb**
中,如果在写入idb文件时发生宕机,可以通过ibdata中的数据**覆盖到idb文件**
中。
MySQL中的缓冲池
- 在进行查询时,会先从缓冲池中进行查询,如果缓冲池中没有,则会先从磁盘中查询,将查询的数据页放入缓冲池中,然后再从缓冲池中读取数据进行返回,这个操作称之为预读。
- 为避免大量的查询将缓冲池中真正的热数据删除,引入新生代老年代机制。在大量查询后,只会将数据加入到老年代中,当数据在老年代中仍然有继续被查询,停留了一段时间,这些数据才会被从老年代转移到新生代中。
- 在进行更新时,会先从缓冲池中进行查询,如果缓冲池中没有则将磁盘中信息加入到缓冲池中。数据更新时会进行快速写,即操作完缓冲区的数据后,并且记录下相关的数据即完成。
- 会先记录Undo Log,用于事务的回滚操作,Undo Log记录为逻辑日志,即相反操作
- 在数据更新过后,缓冲池中的数据称之为脏页,这些脏数据不会在每次更新后都同步到磁盘中,因为全量同步的耗时较久,每次都只会同步一部分脏页数据,在缓冲池不够用时,会将一部分脏页刷入磁盘中,进而可以删除缓冲池中的空间。
- 如果包含唯一索引,则不能使用缓冲池,因为需要对磁盘中的所有数据进行唯一性检查。
- 缓冲池可以设置大小已经缓冲池的个数以及大小
MySQL的执行状态
- 可以打开profiling选项,打开后MySQL会对SQL语句的每一个节点的耗时以及系统的状态进行记录,可以这些信息进行研究。