mysql重学（一）mysql语句执行流程

思考

1. 一条查询语句如何执行？
2. mysql语句中若列不存在，则在哪个阶段报错
3. 一条更新语句如何执行？
4. redolog和binlog的区别？
5. 为什么要引入WAL
6. 什么是Changbuf？如何工作
7. 写缓冲一定好吗？
8. 什么情况会引发刷脏页
9. 删除语句会造成什么后果？会改变磁盘文件大小吗
10. 如何收缩空间

1.查询语句

关于一条mysql查询语句在mysql中的执行流程

image.png

如select name from test where id=10;

1. 连接器---先与mysql服务端连接器建立连接，若查询缓存命中则直接返回 （查询缓存的弊端：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。还容易造成内存泄漏，8.0版本以后删除，是个被抛弃的功能）

查看查询缓存设置
show variables like 'query_cache%';
//my.cnf设置关闭查询缓存
query_cache_type=0

连接完成后，若长时间处于空闲状态，则会自动断开

查看当前mysql的连接状态
show processlist;
查看wait_timeout连接最长闲置时间
show variables like 'wait%';

mysql异常重启现象：使用长连接后，mysql内存可能涨的很快(mysql执行过程使用的内存管理在连接对象中，这些资源断开连接才会被释放)，长连接多了后，内存占用太大，被系统强行杀掉。因此我们需要考虑：1.定期断开长连接（比如对客户端的连接池中的连接设置一个过期时间<wait_timeout）；2.执行较大操作后，再执行mysql_reset_connection来初始化连接资源(5.7以上,当然这是c函数)

int mysql_reset_connection(MYSQL *mysql)

2. 分析器---词法分析告诉服务端你要干什么（我要找 test表中id为10的名字） ，也就是解析客户端命令的token，生成一颗对应的解析树，然后分析器再进一步检查解析树的合法性，比如表和列是否存在等(其中sql语法错误在这块暴露)

3. 优化器---服务端会思考该怎么执行最优(比如索引的选择、表的连接顺序)，生成执行计划。存储引擎的各种数据获取方法都是已经定好的静态方法，优化器能决定执行器选择存储引擎的哪个方法去获取数据

4. 执行器---先检查用户对库对表的权限，再继续执行执行计划

执行器执行子语句的逻辑
1.from字句组装来自不同数据源的数据(先join再on)；
2.where字句基于指定条件对记录进行筛选
3.group by字句将数据划分为多个分组
4.使用聚合函数进行计算
5.使用having字句筛选分组

6.计算所有表达式
7.select的字段
8.使用order by排序结果集
以上每一个步骤都会产生一个虚拟表该虚拟表被用作下一步的输入，只有最后一个表才会返回给调用者

执行器的查询数据逻辑
select * from T where ID=10;
比如我们这个例子中的表 T (innodb引擎)中，ID 字段没有索引（有索引就直接跳到那行），那么执行器的执行流程是这样的：
1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。 也就是说没有索引的话，会进行全表扫描 慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

5. 存储引擎--存储数据，提供读写接口，对于执行器来说，是个黑箱

1.2源码角度

源码分析sql执行过程

image.png

---

2.更新语句

以update a set name=1 where id=1;
主要区别在于在查询到数据之后(select name from a where id=1)，如果是innodb引擎它会进行日志的两阶段提交（WAL技术Write-Ahead Logging先写日志再写磁盘）:

1. 开启事务，写入redolog（innodb引擎特有），并更新内存
2. 写入binlog，提交事务，commit

update T set c=c+1 where ID=2;

image.png

需要注意的是上方提到的先写日志也是先写磁盘，只是写日志是顺序写，这也就引出的redolog和binlog的区别:

1. redolog是Innodb引擎独有的，文件是固定大小的(默认情况下，ib_logfile0 and ib_logfile1两个文件表示)，是循环写的，写满了从头写(记录了某个数据页上做了什么修改,是innodb独有的。)。这种不停的在日志文件末尾追加日志，是磁盘顺序读写，磁盘顺序读写性能很高。

mysqld查看redolog文件大小show variables like 'innodb_log_file_size'
my.cnf设置文件大小
innodb_log_file_size=xxxxx
innodb_log_files_in_group

默认在/var/lib/mysql文件夹下

image.png

2. binlog 是追加写的，写满了再新建文件接着写。

my.cnf设置binlog过期时间，过期删除
expire_logs_days = 5
开启binlog日志
log-bin=mysql-bin

2.1WAL的设计理念来源

关于WAL的设计(摘抄自别人评论，觉得挺有道理)
并不是仅仅为了提升IO性能才设计的WAL。如果仅仅是为了提升性能，那为了WAL所采取的一系列措施也太得不偿失了。 WAL的出现是为了实现关系型数据库的原子性和持久性。实现原子性和持久性的最大困难是“写入磁盘”这个操作并不是原子性的，不仅有“写入”与“未写入”状态，还客观存在“正在写”的中间状态。 由于写入中间状态与崩溃都不可能消除，所以如果不做额外保障的话，将内存中的数据写入磁盘，并不能保证原子性与持久性。所以可能出现以下情形： 1：未提交事务，写入后崩溃（比如修改三个数据，程序还没修改完，但数据库已经将其中一个或两个数据的变动写入磁盘，此时出现崩溃） 2：已提交事务，写入前崩溃（程序已经修改完三个数据，但数据库还未将全部三个数据的变动都写入磁盘，此时出现崩溃） 由于写入中间状态与崩溃都是无法避免的，为了保证原子性和持久性，只能在崩溃恢复后采取补救措施，这种能力就被称为“崩溃恢复”。 为了能够实现崩溃恢复，采取了写日志的方式，写日志成功后再去写磁盘，这种事务实现方式被称为“提交日志（CommitLogging），目前阿里的OceanBase就采用这种方式，但是Commit Logging存在一个巨大缺陷：所有对数据的真实修改都必须发生在事务提交之后，即成功写入日志之后。在此之前，即使磁盘IO有足够的空闲，即使某个事务修改的数据量非常庞大，占用了大量的内存缓冲区，都不允许在事务提交前写入磁盘，因此这种方式对数据库性能的提升十分不利。 基于Commit Logging的问题就，提出了“提前写入日志”（Write-Ahead Logging）的日志改进方案，“提前写入”就是允许在事务提交之前写入变动数据的意思。而对于提前写入磁盘，在数据库崩溃后需要回滚的数据，给出的解决办法是增加另外一种被称为Undo Log的日志类型，当变动数据写入磁盘前，必须先记录Undo Log，以便在事务回滚或者崩溃恢复时根据Undo Log对提前写入的数据变动进行擦除。

mysql的ref也能验证上面这个说法

The redo log is a disk-based data structure used 
during crash recovery to correct data 
written by incomplete transactions. 

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。

show variables like 'innodb_flush%';
//my.cnf配置
innodb_flush_log_at_trx_commit = 1

这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

2.2写缓冲ChangeBuffer

我们知道mysql数据存储包含内存与磁盘两个部分，innodb是按数据页(通常为16k)从磁盘读取到内存中的(剩余操作在内存中执行)，当要更新数据时，若目标数据的数据页刚好在内存中，则直接更新。不在呢？
将这个更新操作(也可能是插入)缓存在change buffer中(redolog也会记录这个change buffer操作)等到下一次查询要用到这些数据时，再执行这些操作，改变数据(称为合并操作记录称为merge)。

写缓冲的目的是降低写操作的磁盘IO，提升数据库性能(一次内存操作，一次redolog写盘操作)。
写缓冲除了上面这个情况，也会定期被刷盘的，数据库正常关闭和redo log写满也会进行merge操作

小实验

查看mysql的change_buffer配置

image.png

innodb_change_buffer_max_size

介绍了写缓冲的大小，占整个缓存池的比例，默认25%

innodb_change_buffering

配置哪些写操作启用写缓冲，可以设置all/none/inserts/deletes等。

2.3redolog写满了怎么办？

在上文的changebuffer中我们提到了，mysql是按页读取数据到内存中的，无论要更新的数据是否在内存中，只要是更新操作就一定是在内存中执行。当内存数据页和磁盘数据页内容不一致时，我们称这个内存页为脏页，内存写入磁盘（称为flush操作），两者一致则为干净页

因为redolog是环形日志，当redolog写满时，就需要“擦掉”开头的一部分数据来达到循环写，这里的擦掉指，指将redolog日志的checkpoint位置从 CP推进到CP‘ ，同时将两点之间的脏页刷到磁盘上(flush操作)，此时系统要停止所有的更新操作（防止更新操作丢失）

image.png

除了redolog写满还有什么会引发flush操作？

1.系统内存不足。当要读取新的内存页时就要淘汰一些数据页，如果淘汰的正好是脏页，就要执行一次flush操作
2.Mysql认为系统处于“空闲状态”
3.正常关闭Mysql

flush操作对性能的影响

上述后两者场景(系统空闲和正常关闭)对于性能都没太大影响。
当为第一种redolog写满时，系统无法执行更新操作，所有操作都会堵塞
当为第二种内存不够用时，如果淘汰脏页太多，影响mysql响应时间

后两者刷脏页会影响性能，所以Mysql需要有刷脏页控制策略，可以从以下几个设置项考虑
1.设置innodb_io_capacity告诉innodb所在主机的IO能力

//利用fio工具来测试磁盘随机读写能力fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

2. innodb_max_dirty_pages_pct设置脏页比例上限 控制刷脏页速度
   3.innodb_flush_neighbors=1 来设置“连坐”

一旦一个查询请求在执行过程中需要刷掉一个脏页，可以利用Mysql的一个连坐机制，即在准备刷掉一个脏页时把邻居(前提邻居也是脏页)也拖下水，邻居也可以把他的邻居给拖下水

---

3.删除语句执行流程

delete from t where  a=300 //假设a为索引

如该命令，在通过分析器-优化器-执行器找到数据后，innodb引擎会把a=300这条记录标记为删除(空间仍存在)，当要再插入一个a为300的值时，可能会复用这个位置（磁盘文件并不会减少，这里指记录的复用）

delete from t where  a>300 and a<500;

如该命令为范围删除，我们知道mysql的数据是按数据页存储的(默认16kb)，万一刚好删掉了一个数据页的记录呢（如上）？答案是整个数据页可能会被复用（有新值插入时，如插入a=400可以直接复用，但是a=600则不能）
另外，如果两个相邻的数据页利用率很小，系统会把两个页上的数据合到其中一个上，另一个数据页被标记为可复用。

delete from t

所有数据页都被标记为可复用，但是磁盘上文件不会变小。
因此delete命令其实只是把记录的位置（或数据页)标记为可复用，但磁盘文件的大小不会变
这些可复用的但是没被使用的空间就成了碎片。
这可能也是业务上更推荐使用软删除的原因吧

3.1只有delelte命令会产生碎片吗

当插入数据时，如果数据是按照索引递增插入，那么索引是紧凑的，但是数据如果是随机插入的呢？
1.如果插入后数据页没满，ok插入
2.如果插入后数据页数据溢出了，那么再插入这些数据时，就不得不再申请一个新的数据页来保存数据

select * from t where id<500 and id>0;
insert into t(id) values(550),(551),(560).....

如上，那么记录id为550的数据就不得不保存到新数据页中，页分裂完成后，旧的数据页留下了500-550的碎片空间（插入数据造成的空洞）
更新索引上的值也会造成碎片空间的产生，更新索引即删除一个旧的索引，再插入一个新值，也就是说，经过大量增删改的表都是可能存在碎片空间的

通过重建表来收缩空间

即重新构建索引树，让索引之间更紧凑

alter table a engine=innodb

关于重建表时能不能进行写操作

5.7之后引入了Online DDL
1.建立一个临时文件，扫描表a主键的所有数据页
2.用数据页中表A的记录生成B+树，存储到临时文件中
3.生成临时文件的过程中，将所有对A表的操作记录到一个日志文件中
4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与a表相同的数据文件
5.用临时文件替换a的数据文件
可以看到因为第三步的日志记录，使得重建表时也能进行写操作（alter 语句在启动时需要获取MDL写锁，但是这个写锁在真正拷贝数据之前旧退化成读锁）

重建方法都会扫描原表数据和构建临时文件，当表很大时很消耗IO和CPU资源，推荐使用github开源的gh-ost来做
重建表因为要创建临时文件（额外空间用于拷贝数据），如果表太大，磁盘太小可能会适得其反

参考

1.mysql实战45讲
2.源码角度分析mysql查询语句

最后编辑于：2025-01-18 16:59:15

© 著作权归作者所有,转载或内容合作请联系作者

喜欢的朋友记得点赞、收藏、关注哦！！！