熟悉MySQL的都知道MySQL服务端实现主要分为Server层和存储引擎层。Server层负责接收和管理客户端连接、管理缓存、解析SQL、优化SQL、调用存储引擎执行SQL;存储引擎层主要负责存储、查询数据。
(图片来自于网络)
1、连接管理连接器负责跟客户端建立连接、获取权限、维持和管理连接;
建立连接之后会验证用户名+密码,获取权限列表,连接完成;
连接建立后,无其他动作,则此连接将处于空闲状态;若连接后客户端长时间不发送命令到服务端,连接器会自动断开(由wait_timeout控制)
2、查询缓存之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。
MySQL 收到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。如果当前查询语句能够直接在缓存中找到 key,那么这个 value 就会被直接返回给客户端;
如果不存在缓存,就继续执行直到完成后把结果存入缓存。(MySQL8.0后取消该功能 缓存极易失效)
分析器先会做【词法分析】。查询语句是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是 什么,代表什么,比如将 select 识别为查询语句,from 之后的字符串识别为表……
然后进行【语法分析】,判断是否符合MySQL的语法,根据SQL语法生成一个数据结构(解析树)。
4、优化SQL经过分析器,MySQL 就知道具体要做什么 *** 作。在开始执行之前,还要先经过优化器的处理决定选择使用哪一个方案。
比如在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接 顺序。
优化器最终会把解析树变成一个查询执行计划。
5、调用存储引擎根据表的引擎定义,执行器选择具体的存储引擎,调引擎的接口执行查询
查询到的数据放入内存中,放入结果集里.
查询完毕后,将结果集返回给客户端
根据Server层生成的执行计划,查询并返回对应数据,不同的存储引擎执行查询的实现不一样。
一条更新SQL的执行流程
更新SQL执行流程,在Server层和查询SQL差不多,也会经过连接、查询缓存、分析、优化、执行的过程。只是查询缓存阶段,查询SQL是从缓存中查询是否存在和查询sql对应的缓存,而更新SQL是删除对应表的缓存;执行阶段,查询SQL是把磁盘或存储引擎缓存中的数据查询出来,而更新SQL是把新的数据更新到存储引擎缓存和磁盘中。
假设t_user表的存储引擎为InnoDB,一条更新SQL的执行过程如下:
【执行事务阶段】
1、客户端向MySQL发送执行 update t_user set name='小王' where id=1;
的命令。
2、删除 t_user 表的所有缓存(如果开启了缓存的话)。
3-4、执行器调用InnoDB存储引擎查询接口,InnoDB在t_user表中查询id=1的记录,先从 Buffer Pool 中查询,如果存在直接返回,否则去磁盘中查询(如果id为主键,就会在聚簇索引上查询数据)。
5、在对查询到的记录修改前会先把旧值写入undo page(undo log的缓存)。
6、执行器查询到记录后,把name的值改为“小王”,调InnoDB的接口把新值写入Buffer Pool中的data page,这里注意下,MySQL执行增删改查,都是直接 *** 作 Buffer Pool,查数据都是先从 Buffer Pool 中查,修改数据时页先写入 Buffer Pool。
7、把对Buffer Pool中data page和undo page的修改记录到 Log Buffer中(redo log的缓存)。至于Log Buffer中的内容何时持久化到磁盘,有不同的策略:
(1)根据刷盘策略执行(innodb_flush_log_at_trx_commit)
默认值为1,每次提交事务都会调用write()将log buffer中的数据写入 os buffer,并调用fsync()刷到磁盘;
值为0时,每次提交事务不 *** 作,后台线程每秒调用write()将log buffer中的数据写入 os buffer,并调用fsync()刷到磁盘;
值为2时,每次提交事务都会调用write()将log buffer中的数据写入 os buffer,后台线程每秒调用fsync()将数据从os buffer刷到磁盘;
(2)Log Buffer空间不足时
(3)正常关闭服务器时
8、为了提升性能,事务执行过程中会把update *** 作记录到binlog cache,具体binlog cache的内容什么时候刷盘,也有不同的策略,根据sync_binlog来设置:
默认sync_binlog=0,表示每次提交事务都只调用write()把数据写入 os buffer,不调用fsync(),由文件系统去控制刷盘,性能最好。但如果此时宕机,会丢失未调用fsync() 的binlog日志;
sync_binlog=1的时候,表示每次提交事务都会调用fsync(),安全性最高,性能最差。
sync_binlog=N(N>1)的时候,表示每次提交事务都调用write(),但累积N个事务后才调用fsync()。
【提交事务阶段】
9、客户端向MySQL发送提交事务请求。
10、根据binlog刷盘策略把 binlog cache 刷盘到binlog文件。
11、调用InnoDB存储引擎提交事务接口,修改redo log 状态为commit,此时整个事务完成。
Buffer Pool中的脏页(修改但没有刷新到磁盘的新数据、undo log)由Master Thread 或 Purge Thread 负责根据一定策略刷新到磁盘中。
转载请注明出处——胡玉洋 《根据一条Sql来分析MySQL执行的全流程(连接、分析、优化、执行、Undo Log、Binlog、Redo Log)》
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)