怎么跳出MySQL的10个大坑

MySQL · 性能优化· Group Commit优化

背景

关于Group Commit网上的资料其实已经足够多了，我这里只简单的介绍一下。

众所周知，在MySQL5.6之前的版本，由于引入了Binlog/InnoDB的XA，Binlog的写入和InnoDB commit完全串行化执行，大概的执行序列如下：

InnoDB prepare （持有prepare_commit_mutex）；

write/sync Binlog；

InnoDB commit (写入COMMIT标记后释放prepare_commit_mutex)。

当sync_binlog=1时，很明显上述的第二步会成为瓶颈，而且还是持有全局大锁，这也是为什么性能会急剧下降。

很快Mariadb就提出了一个Binlog Group Commit方案，即在准备写入Binlog时，维持一个队列，最早进入队列的是leader，后来的是follower，leader为搜集到的队列中的线程依次写Binlog文件,

并commit事务。Percona 的Group Commit实现也是Port自Mariadb。不过仍在使用Percona Server5.5的朋友需要注意，该Group

Commit实现可能破坏掉Semisync的行为，感兴趣的点击

bug#1254571

Oracle MySQL 在5.6版本开始也支持Binlog Group Commit，使用了和Mariadb类似的思路，但将Group Commit的过程拆分成了三个阶段：flush

stage 将各个线程的binlog从cache写到文件中sync stage 对binlog做fsync *** 作（如果需要的话）；commit

stage 为各个线程做引擎层的事务commit。每个stage同时只有一个线程在 *** 作。

Tips：当引入Group Commit后，sync_binlog的含义就变了，假定设为1000，表示的不是1000个事务后做一次fsync，而是1000个事务组。

Oracle MySQL的实现的优势在于三个阶段可以并发执行，从而提升效率。

XA Recover

在Binlog打开的情况下，MySQL默认使用MySQL_BIN_LOG来做XA协调者，大致流程为：

1.扫描最后一个Binlog文件，提取其中的xid；

2.InnoDB维持了状态为Prepare的事务链表，将这些事务的xid和Binlog中记录的xid做比较，如果在Binlog中存在，则提交，否则回滚事务。

通过这种方式，可以让InnoDB和Binlog中的事务状态保持一致。显然只要事务在InnoDB层完成了Prepare，并且写入了Binlog，就可以从崩溃中恢复事务，这意味着我们无需在InnoDB

commit时显式的write/fsync redo log。

Tips：MySQL为何只需要扫描最后一个Binlog文件呢 ？ 原因是每次在rotate到新的Binlog文件时，总是保证没有正在提交的事务，然后fsync一次InnoDB的redo

log。这样就可以保证老的Binlog文件中的事务在InnoDB总是提交的。

问题

其实问题很简单：每个事务都要保证其Prepare的事务被write/fsync到redo log文件。尽管某个事务可能会帮助其他事务完成redo

写入，但这种行为是随机的，并且依然会产生明显的log_sys->mutex开销。

优化

从XA恢复的逻辑我们可以知道，只要保证InnoDB Prepare的redo日志在写Binlog前完成write/sync即可。因此我们对Group

Commit的第一个stage的逻辑做了些许修改，大概描述如下：

Step1. InnoDB Prepare，记录当前的LSN到thd中；

Step2. 进入Group Commit的flush stage；Leader搜集队列，同时算出队列中最大的LSN。

Step3. 将InnoDB的redo log write/fsync到指定的LSN

Step4. 写Binlog并进行随后的工作(sync Binlog, InnoDB commit , etc)

通过延迟写redo log的方式，显式的为redo log做了一次组写入，并减少了log_sys->mutex的竞争。

目前官方MySQL已经根据我们report的bug#73202锁提供的思路，对5.7.6的代码进行了优化，对应的Release Note如下：

When using InnoDB with binary logging enabled, concurrent transactions

written in the InnoDB redo log are now grouped together before synchronizing

to disk when innodb_flush_log_at_trx_commit is set to 1, which reduces

the amount of synchronization operations. This can lead to improved performance.

性能数据

简单测试了下，使用sysbench, update_non_index.lua, 100张表，每张10w行记录，innodb_flush_log_at_trx_commit=2,

sync_binlog=1000，关闭Gtid

表结构如下：

业务需要将表中重复数据删除，所以需要按照 组合唯一索引键筛选出重复的数据进行删除。SQL如下：

表中有符合索引 KEY column1_column2_index ( column1 , column2 )

sql语句 Group by 也是按照最左匹配原则顺序写的 group by 的字段，但是每次执行SQL耗时都是好几十秒

explain 该 sql 发现，并没有走表中存在的复合索引，而是直接走的 File sorted（文件排序）；group by 语句其实是有要先排序再分组的；

问题的关键定位到没有没有命中表中的复合索引，那为何 group by 字段前两个就是复合索引，只是最后两个不是，为何没有走索引呢？不是索引只要满足最左匹配原则就可以命中吗？

分析后发现，索引可以用在两个地方，1 被用于提高WHERE条件的数据行匹配或者执行联结 *** 作时匹配其它表的数据行的搜索速度。2 快速地执行ORDER BY和GROUP BY语句的排序和分组 *** 作。

本处就是可以使用索引做排序使用，而避免文件排序；此处要命中索引，走索引排序，必须要表中有一个复合索引包含 group by 的所有字段且顺序一致；

网上有部分博客说 group by 自带的排序和 order by 排序，走不走索引的规则是一样的，这里本人测试了一下，添加 group by 后面所有顺序字段的复合索引对 group by 的查询时间有直接的影响，从 30多秒 优化到 3秒；

但是对如下SQL 的执行时间也有影响，但是远远没有对group by 的影响大，如下sql，添加 order by 的全索引后 只能从30多秒优化到 10 多秒

select column2 from am_cm_relationship order by column1,column2,column3,column4

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert *** 作：

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

delete *** 作：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除 *** 作

---