一、SQL 执行过程
MySQL 中是如何执行 SQL 的，过程如下：
连接器->(查询缓存)->分析器->优化器->执行器->存储引擎
查询缓存模块，8.0 中已做废此功能。
连接器--客户端与 MySQL 服务器的连接
配置文件中，max_connections 用于控制最大连接数，默认值151。
需要注意的是，连接操作比较耗资源，所以一般使用长连接，不过长连接有内存占用的问题。解决方案定时断开或者在大查询之后断开重连（5.7之后可以调用 MySQL_reset_connection()，在不断开的情况下释放资源）
sql
体验AI代码助手
代码解读
复制代码
mysql_reset_connection()
分析器--词法和语法解析
略
优化器--基于成本的优化方式选择索引，生成执行计划
优化器选择索引的依据
- 是否排序
- 是否使用临时表
- 回表开消
- 扫描行数（估计值，采样，索引的区分度（不同的值））
MySQL 的优化器不是很完美，有时候需要优化 SQL，需要用 explain 分析索引使用情况
优化方向：
- 修改 SQL
- 加索引
- 删索引
- force index
sql
体验AI代码助手
代码解读
复制代码
-- 强制指定使用索引a
select * from t force index(a) where a between 10000 and 20000;
执行器--执行器检查权限，操作存储引擎，返回结果
略
存储引擎
MySQL支持多种存储引擎，同一个数据库不同的表可以设置不同的引擎
InnoDB：5.5 后默认，支持事务，外键，适合大部分场景
MyISAM：5.5 之前默认
MEMORY：内存，速度快，不能持久化，主从同步的有问题
二、crash-safe
实现 crash-safe：MySQL 异常重启，数据不会丢失
实现方式：WAL（预写式日志） 技术，先写日志再把数据写磁盘，保证数据操作的原子性和持久性
redolog
InnoDB 存储引擎功能，记录的是物理日志，记录的是数据页的物理修改（指 InooDB 中同一数据页在磁盘上和内存中的差异，因为是顺序IO，性能会比随机IO快，这种数据页叫脏页）
记录方式，循环覆写，固定的大小，不能用于归档。
binlog
MySQL 基本功能，不限存储引擎，只记录数据的变化，是逻辑日志
归档日志，可用于服务器之间主从同步，备份恢复
两阶段提交
redolog 保证了 MySQL 数据不丢失
binlog 用于归档，保证了数据库可以备份每一次事务提交
如何保证归档的数据和当前的数据一致，也就是如果让 “当前数据” 与 “备份数据” 一致或主机与从机数据一致。
MySQL 的实现方案，过程如下：
执行SQL->更新内存中的数据页（如果没有从磁盘中加载到内存）-> 写入redolog(redolog prepare 状态) -> 写binlog->提交事务(redolog commit 状态)
写日志执行过程为两阶段提交 prepare->commit ，保证了数据逻辑上的一致性
三、性能
日志与磁盘IO的关系
数据库的主要性能瓶颈就是磁盘IO，相对于内存来说，磁盘太慢了。要提高性能就需要减少刷盘次数。
配置文件中，innodb_flush_log_at_trx_commit 用于控制 redolog 日志持久化策略
- 设置为1，redolog 每次事务提交都直接持久化到磁盘
- 设置为2，每次只写到 page cache
后台有一个线程每秒写盘（write + fsync），刷盘的时候可以顺带把其它未提交的事务数据刷新，有数据丢失的风险，最多丢失1秒的数据，但是IO性能会更好。
将某个脏页刷新到磁盘前，会先保证该脏页对应的 redolog 刷新到磁盘中
此外，如果遇到大事务 redolog 缓存占用到了 innodb_log_buffer_size 的一半，会主动写盘（只write）
配置文件中，sync_binlog 用于控制 binlog 日志持久化策略
- 设置为1，binlog 在每次事务提交都直接持久化到磁盘
- 设置为N，表示每次只 write 到文件系统的 page cache，N个事务后 fsync。MySQL 崩溃不会丢数据，但是系统断电会丢最多N个数据，一般设置为100-1000
上面两阶段提交，需要注意的是，redolog 刷盘时机是在两阶段中的 prepare，commit 其实不需要刷盘(重启后校验 binlog 完整性)
正常情况下，数据库需要保证crash-safe, 必须配置成配置双1(innodb_flush_log_at_trx_commit=1和sync_binlog=1)，此时一个事务的提交会刷盘两次（redolog 和 binlog）
配置文件中，innodb_log_buffer_size redolog 缓存大小，如果有大事务，可以设置大一些，默认16M
有时候为了提高性能，MySQL 会设置为非双1，比如：
- 业务高峰
- 备库延迟追主库
- 恢复备份
- 批量导入
组提交
在 redolog 中有 LSN 日志逻辑序列号，记录 redolog 写入点，每次递增日志的写入长度。通过 LSN 可以判断，在事务提交的时候可能其它事务已经帮你刷盘了，不需要重复刷盘。 让 redolog 的刷盘尽量靠后，可以更好的利用组提交。
InnoDB 做了一个两阶段提交优化，redolog 的 fsync 放在 binlog write 之后
binlog 也有组提交，不过一般用不上，因为 redolog 的 fsync 步骤比较快，达不到积累binlog日志的效果。
在 sync_binlog 设置为1时如果你想提升 binlog 组提交的效果，可以修改如下配置：
配置文件中 binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
配置文件中 binlog_group_commit_sync_no_delay_count 参数，表示累积多少次事务以后才调用 fsync。
以上两个参数是或的关系，满足一个就会 fsync。
这两个参数原理是延迟 commit success 的返回，最长延迟 binlog_group_commit_sync_delay 微秒，所以上面两个参数是无损的（对 crash-safe 没有影响），但是会增加语句的响应时间
需要注意 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 的逻辑先走。等到满足了这两个条件之一，再进入 sync_binlog 参数控制的阶段。
脏页刷盘（InnoDB Buffer Pool)
配置文件中，innodb_buffer_pool_size innodb 缓存大小，建议设置为设置为主机内存的60-80%
配置文件中，innodb_change_buffer_max_size 设置 change buffer 占用 buffer pool 的比例，默认是50%
InnoDB 并没有使用操作系统中文件系统自带的缓存，而是自己实现缓存，InnoDB 中数据以页为单位从磁盘中加载到内存，页的大小一般为 16 KB，为减少磁盘 IO 负担，当内存数据页数据有修改时，并不会立即更新到磁盘中，而是等待特定的时机刷新。
记录内存数据页和磁盘数据页之间物理差异就是 redolog 的工作, 脏页的刷新和 redolog 息息相关。
刷脏页（这里指内存数据页与磁盘不一致，相反的叫干净页）的过程叫 flush，flush 时机有以下几种情况：
- redolog 满了, 此时 MySQL 不能处理任何写操作，这也是 MySQL 卡死的主要原因之一
- 系统内存不足，干净页/脏页都可能释放，如果是脏页，会触发 flush
- 空闲时
- 正常开闭程序时
配置文件中，innodb_io_capacity 这个参数告诉 InnoDB 磁盘的性能，控制全力刷新磁盘时的性能，默认200
可以使用用 fio 测试磁盘的 IOPS，机械硬盘建议100，SSD 建议200或更高，比如1000，最高不建议超过20000
bash
体验AI代码助手
代码解读
复制代码
touch /tmp/test_io
fio -filename=/tmp/test_io -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
实际使用时，不可能让磁盘一直全力去刷新，所以需要控制不同程度下动态处理使用不同的速度百分比
动态处理由两个因素决定，两个维度取更大的值刷新
- 脏页比例
- redolog 生成速度（checkpoint 和 write pos差值）
配置文件中，innodb_max_dirty_pages_pct 用于设置脏页百分比上限，默认90，意思是 InnoDB 会避免达到90这个值，如果达到了些值 InnoDB 会全力刷新
sql
体验AI代码助手
代码解读
复制代码
-- 查询脏页比例
select VARIABLE_VALUE into @a from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_total';
select @a/@b;
checkpoint 是 redolog 中记录要擦除的LSN序号 InnoDB 每次写入的日志都有一个LSN序号 write pos 直接刷脏页是不会动 redolog 的，等后续应用 redolog 的时候，会根据LSN 的大小来判断这个页有没有应用到这条 log
配置文件中，innodb_flush_neighbors 表示如果脏页旁边也是脏页，是否一起flush，用于机械盘设置为1，用于 ssd 建议设置为0（MySQL8的默认值）
