mysql 一条语句的执行流程

一条查询语句的执行流程

连接器

连接器：管理连接、权限校验

管理连接

管理连接：由于连接成本高，连接池会复用连接。

成本高：TCP三次握手；发起系统调用；高并发场景可能耗尽文件资源描述符；

复用连接的问题（长连接问题）：连接在断开时才会释放占用资源，而不是用完就释放；长连接可能导致占用内存变大，比如大的查询；长时间积累会导致mysql被系统杀掉OOM // 现象：mysql重启

长连接问题解决方案：

• 定期断开连接
• mysql_reset_connection 重置

空闲连接最大空闲时间：wait_timeout=default 8h

权限校验

到权限表中查找拥有的权限；之后这个连接验证的全局权限用的都是此时的权限快照；即使后续修改权限，也只会在新会话中生效，不会改变当前会话; // 但db权限修改了，就会生效，但如果进入了use db1；那修改了也不会改变use db1里的会话。

分析器

分析器：词法分析、语法分析

词法分析：检查表、列是否存在；若列不存在，则返回报错

语法分析：比如 elect * from t; 会报语法错误；错误会在use near后面

优化器

优化器：多条执行计划成本对比，智能选择索引

成本对比：对比CPU计算、内存消耗、大概扫描行数(采样统计)、是否排序、是否使用临时表等

采样统计

一个索引上不同值（基数）越多，区分度越高；mysql通过采样统计得到索引的基数；

innodb_status_persistent：

on 持久化 N=20（采样页数）M=10（1/10个页数变动就重新采样）

off 仅存在内存 N=8 M=16

优化器选错索引改正

• 扫描行数不准：analyze table t修正
• force index 强制修改索引
  • 问题：不优雅、维护字段变动就需要手动修改、迁移数据库可能语句不兼容 // 主要就是关注变更的及时性
• 修改sql语义
• 业务思考，删掉有影响的无效索引

执行器

执行前会判断有无操作表的权限；在进优化器前会先precheck(粗检查)，执行器进行细检查，比如视图、存储过程等复杂对象在precheck检查不了。

存储过程举例：

delimiter ;;
create procedure idata()
begin
	...
end ;;
delimiter ;

走查询缓存时，也会先查权限；

查询缓存

失效频繁：表更新时，所有查询缓存都会被清空；更新压力大的数据库缓存命中率低；

查询缓存适合静态表，比如系统配置表；

存储引擎

比如innodb，存储引擎以插件的方式加入

一条update语句的执行流程

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

server层执行流程与select相同，下面主要介绍引擎层的执行流程 // 部分流程节点不在引擎中，比如binlog

redo log

redo log buffer结构

redo log buffer类似go ring buffer, 是固定大小的环状结构。write_pos是当前记录的位置，write_pos到check_point的绿色部分还能写入，其余位置是新的写入。如果write_pos追上check_point，就需要先落盘，更新check_point的位置。// 此时落盘是prepare状态的redo log

redo log一般有4GB，由4个1GB的文件组成。如果redo log设的太小，会出现磁盘压力小，但数据库出现间歇性的性能下跌，因为系统频繁的中断业务刷脏，更新check point位置

redo log日志类型

redo log是物理日志，记录了数据页的具体修改，比如哪一行的那个字段由啥改成啥；

redo log记录的是操作，而不是数据本身，数据存在内存（buffer pool）和磁盘上；

写入时机

redo log在修改数据前顺序写入，是WAL（Write Ahead Log），是崩溃恢复的重要保证机制；

redo log是顺序写入，比直接写入磁盘更快（磁盘I/O慢、写B+树），降低了服务崩溃，数据丢失的风险。

配置innodb_flush_log_at_trx_commit

0：只写到buffer中（内存缓存)，等待定时刷新

1：事务提交时持久化到磁盘 // 推荐

2：会推到page cache中（os缓存），定时持久化

binlog

redo log怎么找到对应的binlog：有个xid，关联他们。

mysql有全局变量global_query_id，每次执行语句会给它发一个query_id，然后把这个变量+1。如果这个语句是事务的第一条语句，就会把这个query_id给xid。每次sql重启都会清空global_query_id 。

redo log和binlog 对比

redo log	binlog
物理日志	逻辑日志，有三种格式，比如statement记录的就是sql语句
innodb引擎特有，用于崩溃恢复	mysql上的归档日志，主要用于主从复制
循环写入	顺序追加记录，追加写不会覆盖

配置

sync_binlog:

0：就写到binlog buffer中，等待定时刷新

1：事务提交立即刷新 // 推荐

N：提交累积N个后刷新

两阶段提交协议

两阶段提交协议保证了redo log和binlog的一致性；

崩溃时机

在流程图的时机A崩溃：redo log处于prepare状态，未写入binlog: 服务重新启动时，认为事务提交失败，回滚事务

在流程图的时机B崩溃：redo log处于prepare状态，写入binlog完成：服务重新启动时，认为事务提交成功，回放事务，将redo log prepare状态改为commit

在流程图的时机C崩溃: redo log若处于prepare状态，同时机B；若处于commit状态，则完成事务；

WAL保证了崩溃数据不丢失，prepare状态的引入，保证了事务提交的一致性。