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

目录一、SQL 执行过程连接器--客户端与 MySQL 服务器的连接优化器--基于成本的优化方式选择索引，生成执行计划存储引擎二、crash-saferedologbinlog两阶段提交三、性能日志与磁盘IO的关系组提交脏页刷盘（InnoDB Buffer Pool)

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

一、SQL 执行过程

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

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

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

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

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

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

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

优化器选择索引的依据

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

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

优化方向：

修改 SQL加索引删索引force index-- 强制指定使用索引aselect * 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

 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 会全力刷新

 -- 查询脏页比例 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的默认值）

到此这篇关于MySQL高效可靠处理持久化数据的教程指南的文章就介绍到这了,更多相关MySQL处理持久化数据内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！