MySQL高效可靠处理持久化数据的教程指南

本文阅读前提：需要比较熟悉 MySQL 的基本功能，有使用 MySQL 的相关经验

一、SQL 执行过程

MySQL 中是如何执行 SQL 的，过程如下：

连接器->(查询缓存)->分析器->优化器->执行器->存储引擎

查询缓存模块，8.0 中已做废此功能。

连接器--客户端与 MySQL 服务器的连接

配置文件中，max_connections 用于控制最大连接数，默认值151。

需要注意的是，连接操作比较耗资源，所以一般使用长连接，不过长连接有内存占用的问题。解决方案定时断开或者在大查询之后断开重连（5.7之后可以调用 MySQL_reset_connection()，在不断开的情况下释放资源）

优化器--基于成本的优化方式选择索引，生成执行计划

优化器选择索引的依据

• 是否排序
• 是否使用临时表
• 回表开消
• 扫描行数（估计值，采样，索引的区分度（不同的值））

MySQL 的优化器不是很完美，有时候需要优化 SQL，需要用 explain 分析索引使用情况

优化方向：

• 修改 SQL
• 加索引
• 删索引
• force index

存储引擎

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

实际使用时，不可能让磁盘一直全力去刷新，所以需要控制不同程度下动态处理使用不同的速度百分比

动态处理由两个因素决定，两个维度取更大的值刷新

• 脏页比例
• redolog 生成速度（checkpoint 和 write pos差值）

配置文件中，innodb_max_dirty_pages_pct 用于设置脏页百分比上限，默认90，意思是 InnoDB 会避免达到90这个值，如果达到了些值 InnoDB 会全力刷新

checkpoint 是 redolog 中记录要擦除的LSN序号 InnoDB 每次写入的日志都有一个LSN序号 write pos 直接刷脏页是不会动 redolog 的，等后续应用 redolog 的时候，会根据LSN 的大小来判断这个页有没有应用到这条 log

配置文件中，innodb_flush_neighbors 表示如果脏页旁边也是脏页，是否一起flush，用于机械盘设置为1，用于 ssd 建议设置为0（MySQL8的默认值）