三、幻读

InnoDB 的默认事务隔离级别是可重复读，可重复读的条件下，一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没看到的行，称之为幻读。

幻读仅在当前读下才会出现，且仅专指“新插入的行”。

注 ：在可重复读的隔离级别下，普通的查询是快照读，不会看到别的事务插入的数据。当前读，读取的是数据的最新版本，需要获取到对应记录的锁。

3.1 幻读带来的问题

1. 破坏锁的语义。
2. 破坏数据的一致性。
3. 会影响新插入的记录。

3.2 如何解决幻读？

为了解决幻读，InnoDB引入了间隙锁。

间隙锁锁的是两个值之间的空隙，通过锁索引叶子节点的 next 指针实现，间隙锁间无冲突，即可以对一个间隙上多把间隙锁，但往间隙中插入记录会冲突。

执行当前读的时候，不仅会给数据库中的行加上行锁，并且给行两边的间隙加上间隙锁，这样就确保了无法插入新的记录。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

间隙锁的问题：

1. 间隙锁的引入会导致同样的语句锁住更大的范围，会影响并发度。
2. 两个 session 同时在同一个区间锁上间隙锁，且两个 session 同时往这个区间内插入值会导致互相等待，进入思索。

注 1：间隙锁只有在可重复读隔离级别下才生效。

注 2：在 InnoDB 的基础设定中，锁是加在索引上的。

3.3 加锁规则

可重复读下的加锁规则：

可分为两个原则、两个优化、一个 bug

两原则：

1. 加锁的基本单位是 next-key lock。
2. 查找过程中访问到的对象才会加锁。

两优化：

1. 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
2. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

在当前读带 limit 时，满足条件后，不会再查找下一个对象，没有查找到对象，后面的数据就不会上锁。

建议：在删除数据时尽量加 limit，不仅可以控制删除数据的条数，还可以减小锁的范围。

读提交下的加锁规则：

语句执行过程中加上的行锁，在语句执行完成后，就把“不满足条件的行”上的行锁释放掉，不需要等到事务提交。

也就是说，读提交隔离级别下，锁的范围更小、时间更短。

四、临时提高 MySQL 的性能

4.1 短连接风暴

问题：采用短连接的方式连接数据库，业务高峰期，连接数暴涨。

场景：

1. 数据库有 max_connections 参数，控制 MySQL 实例同时存在的连接数上限，超过这个值就会拒绝连接，返回报错，从业务角度来看就是数据库不可用了。
2. 假如调大这个参数，系统的负载会增大，大量的资源耗费在权限验证等逻辑上，反而已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

解决办法：

1. 先处理掉占着连接不工作的线程。 实现方法：可以通过 kill connection 或者设置 wait_timeout 实现。 问题：这个方法会存在误删有用线程的问题，导致应用侧延迟报错。
2. 减少连接过程中的消耗。 实现方法：让数据库跳过权限验证阶段，重启数据库，使用 -skip-grant-tables 参数启动，整个 MySQL 会跳过所有的权限验证阶段。 问题：数据库有很大的被入侵风险。

注：在 MySQL 8.0 版本里，启动 -skip-grant-tables 参数会默认同时打开 –skip-networking 参数，表示只能被本地的客户端连接。

4.2 慢查询性能问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

1. 索引没设计好。
2. SQL 语句没写好。
3. MySQL 选择错了索引。

问题一：索引没设计好。

解决办法：紧急创建索引。MySQL 5.6 版本以后，创建索引支持 Online DDL，最高效的版本是直接执行 alter table 语句。

紧急情况下，最高效的方案是能够在备库先执行，假如现在一主一备，大概流程如下：

1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引。
2. 执行主备切换。
3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

平时做变更时，应该考虑类似 gh-ost 这样的方案。

问题二：SQL 语句没写正确。

解决办法：

# 例子：假如语句错误写成了 select * from t where id + 1 = 10000，可以通过语句重写查询
insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
 
# 这个存储过程让插入的新规则生效，也就是“查询重写”
call query_rewrite.flush_rewrite_rules();

问题三：MySQL 选错了索引。

解决方案：使用查询重写功能给原来的语句加上 force index。

慢查询的问题实际上都应该在上线前发现问题。

1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
2. 在测试表里插入模拟线上的数据，做一遍回归测试；
3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。

如果新增的SQL语句不多，手动跑一下就可以。

而如果是新项目的话，或者是修改了原有项目的表结构设计，全量回归测试都是必要的，这时候，需要工具帮助检查所有的SQL语句的返回结果。

开源工具 pt-query-digest（https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html）。

4.3 QPS 突增

问题：由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，可能会导致 MySQL 压力过大，影响服务。

场景：

1. 由全新业务的 bug 导致。 解决办法：假设 DB 运维比较规范，白名单一个个加的。这种情况下如果能确定业务方会下掉这个功能，可以从数据库端把白名单处理掉。
2. 新功能使用的是单独的数据库用户。 解决办法：使用管理员账号把这个用户删掉，断开现有连接。
3. 新增功能与主题功能部署在一起的。 解决办法：使用查询重写功能，把压力最大的 SQL 语句直接重写成 select 1 返回。

方案 3 风险很高，是用来止血的，这个应该是优先级最低的方案，可能存在的问题：

1. 如果别的功能用到了这个 SQL 语句模板，可能会误伤。
2. 很多业务并不是靠一个语句完成逻辑的，如果单独把这个语句以此返回，可能会导致后面的业务逻辑一起失败。

方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。

五、MySQL 的数据持久化机制

5.1 binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

问题：一个事务的 binlog 是不能被拆开的，无论事务多大也要确保一次性写入， binlog cache 如何保存？

1. 系统给 binlog cache 分配了一片内存，每个线程一个。参数 binlog_cache_size 用于控制单个线程 binlog cache 所占内存的大小。
2. 如果超过了这个参数规定的大小，就暂存到磁盘。
3. 事务提交的时候，执行器把 binlog cache 里的完整事务写到 binlog 中，并清空 binlog cache。

每个线程都有自己的 binlog cache，但共用同一份 binlog 文件。

• 图中的 write 指的是把日志写到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
• 图中的 fsync 才是将数据持久化到磁盘的操作。一般情况下认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

1. sync_binlog=0，每次提交事务只 write。
2. sync_binlog=N，标识每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，出现 IO 瓶颈时，将 sync_binlog 设置成为一个比较大的值可以提升性能。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

5.2 redo log 的写入机制

redo log 的三种状态：

1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分。
2. 写到磁盘，但是没有持久化，物理上是在文件系统的 page cache 里面，也就是黄色部分。
3. 持久化到磁盘，对应的是 hard disk，也就是绿色部分。

日志写到 redo log buffer 和 page cache 是很快的，但是持久化到磁盘很慢。

redo log 的写入策略由 InnoDB 提供的 innodb_flush_log_at_trx_commit 参数控制，有以下几种策略：

1. 参数为 0 时，表示每次事务提交时都只是把 redo log 留在 redo log buffer。
2. 参数为 1 时，表示每次事务提交时都将 redo log 直接持久化到磁盘。
3. 参数为 2 时，表示每次事务提交时都只把 redo log 写到 page cache。

InnodDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志写到文件系统的 page cache，然后再持久化到磁盘。

注：事务执行过程中的 redo log 是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程持久化到磁盘。

让没提交事务的 redo loog 写入到磁盘的三个场景：

1. InnoDB 后台线程调用 redo log buffer，并将 redo log 持久化到磁盘。
2. redo log buffer 占用的空间即将达到 innodb_log_buffer_size 参数的一半时，后台线程会主动写盘。
3. 并行的事务提交时，顺带将这个事务的 redo log buffer 持久化到磁盘。

5.3 两阶段提交

时序上 redo log 先 prepare，再写 binlog ，最后再把 redo log commit。

实际上，写 redo log 和写 binglog 都是分两步的，先 write 再 fsync。

两阶段提交细化流程就变成了：

1. redo log prepare：write
2. binlog：write
3. redo log prepare：fsync
4. binlog：fsync
5. redo log commit：write

5.4 组提交机制

日志逻辑序列号（log sequence number，LSN）：LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入为 length 的 redo log，LSN 的值就会加上length。

LSN 也会写到 InnoDB 的数据页中，确保数据页不会被多次执行重复的 redo log。

有并发事务写盘的时候，leader 事务会将所有小于等于当前最大 LSN 值的 redo log 持久化到磁盘中，其它事务直接返回。

因此一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约磁盘 IOPS 的效果越好。

因为两阶段提交的机制，binlog 也是可以进行组提交的。不过通常情况下两阶段提交细化流程的第三步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间很多，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交效果通常并不如 redo log 好。

binlog 组提交可以通过参数调节：

1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync。
2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，只要满足一个条件就会调用 fsync。

5.5 使用持久化机制提高 MySQL 性能

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3. 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。