sql语句执行过程

系列文章目录

第一章：sql_mode模式
第二章：optimize table、analyze table、alter table、gh-ost
第三章：InnoDB MVCC原理
第四章：sql语句执行过程

---

---

一、整体流程

update t set c = 1 where id = 1;

下面是该sql执行的大概流程，若有不实之处，希望大家指正。

一、mysql-server

1. 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。一般可以通过mysql client 执行如下命令连接mysql server：

mysql -h$ip -P$port -u$user -p

注意点：

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
• 连接器在客户端wait_timeout（默认8个小时）主动断开连接
• MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2. 缓存

mysql收到查询语句后，会先查询缓存。缓存是以key-value的形式保存的，key是之前查询的语句，value是查询的结果。若是当前查询的语句，可以在缓存中直接命中，则直接返回。但是一般情况下，缓存的命中率比较低，因为只要对一个表有更新操作，该表所有的查询缓存都会失效。此外，目前mysql8.0已经取消了缓存。
注意点
mysql 8.0以下版本可以设置缓存按需使用，将query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定。

select SQL_CACHE * from T where ID=10；

3.分析器

分析器是对需要执行的sql语句进行词法分析，语法分析。一般若是sql语句出现错误，就是分析器报的错误。

4.优化器

一般都会通过索引来优化查询的速度，但是若是一条sql语句，有多个选择，需要通过优化器进行采样分析，决定使用最优的方式，可能不选择使用索引。

5.执行器

执行器，顾名思义，就是执行sql语句。
若过滤条件不是索引：

• 调用 InnoDB 引擎接口取这个表的第一行，判断 过滤条件是否满足，如果不满足则跳过，如果是则将这行存在结果集中；
• 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

若过滤条件是索引：

• 调用 InnoDB 引擎接口取这个表满足条件的第一行，并将这行存在结果集中；
• 循环调用引擎接口取“下一行”，直到不满足条件为止。

二、mysql-engine

1.简介

mysql引擎是插件式，可以供选择，目前oltp一般都是选择innodb，主要是innodb，支持事务，支持行锁，并发性能较好。常用的mysql引擎对比如下：

三、三大重要日志

1.bin log

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

• 逻辑日志：可以简单理解为记录的就是sql语句。
• 物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。
  binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。
  使用场景：
• 主动复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
• 数据恢复：通过使用mysqlbinlog工具来恢复数据。
• 保证innodb事务 crash-safe 能力
  binlog日志有三种格式，分别为STATMENT、ROW和MIXED。
• STATMENT：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能。在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。
• ROW：基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。会产生大量的日志，尤其是alter table的时候会让日志暴涨。
• MIXED：基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

2.undo log

原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log也是MVCC(多版本并发控制)实现的关键，可以参考：InnoDB MVCC原理

3.redo log

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。
bin log 与 redo log 的区别：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。