1. 前言
前几天在做一个 MySQL 的测试实验，通过一个存储过程写入 10W 数据，发觉用了 1 小时多。前面学习来 InnoDB 的原理，觉得可以对此进行一次分析和调优。
2. 环境
- Docker
- Linux
- 机械硬盘
- MySQL 5.7
- SQL如下
-- 表结构
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 插入存储过程
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
-- 执行结果
mysql> call idata();
Query OK, 1 row affected (1 hour 12 min 46.07 sec)
3. 分析
3.1 数据插入时，InnoDB 都要怎么做？
数据在写入的数据库时，需要写一份 binlog 和 一份 redolog。
- 把数据写到新行里；
- 写入 redolog
- 写入 binlog(完成)
- commit(redolog 完成)
表中不存在唯一辅助索引，因此在写入过程中，可以使用 Change Buffer，再由 Master 线程逐步合并到 簇拥索引(表) 上。那么，性能瓶颈就集中在日志的写入上了。
3.2 日志写入的配置
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
由参数可以看到，两个日志的配置都是最安全的，同时 IO 也是最慢的。
innodb_flush_log_at_trx_commit 当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
sync_binlog 如果 autocommit 开启，每个语句都写一次 binary log，否则每次事务写一次。默认值是 0，不主动同步，而依赖操作系统本身不定期把文件内容 flush 到磁盘。设为 1 最安全，在每个语句或事务后同步一次 binary log，即使在崩溃时也最多丢失一个语句或事务的日志，但因此也最慢。
4. 调优实验一
- 打开上述两个配置，设置为 0；
- 新建表 t2
- 新建存储过程
- 执行存储过程
- 观察数据
4.1 实验语句
set global sync_binlog=0;
set globle innodb_flush_log_at_trx_commit=0;
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 插入存储过程
delimiter ;;
create procedure idata3()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata3();
4.2 实验结果
mysql> call idata3();
Query OK, 1 row affected (8.68 sec)
** 仅需8s就可以完成10w数据的写入 **，插入性能提高约 540 倍。
4.3 调优实验二
上面通过从原理上分析，其实是牺牲数据的安全性来换取性能的提升。在 autocommit 的配置下，idata3() 的存储过程是每插入一次，就commit 一次。这个是触发大量磁盘IO的原因，因为每次都需要写两次磁盘，一次是 redo log，一次是 binlog，导致写性能一直很差。
所以不断提交事务对批量写数据来说是一个灾难的操作。考虑改善一下存储过程，让所有的提交集合成一个 commit。
- 编写存储过程 idata4
- 清空表 t2
- 执行 idata4()
- 观察执行结果
CREATE DEFINER=`root`@`%` PROCEDURE `idata4`()
begin
declare i int;
START TRANSACTION; -- 开启事务
set i=1;
while(i<=100000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
COMMIT; -- 提交事务
end
4.4 实验结果
set global sync_binlog=1;
set globle innodb_flush_log_at_trx_commit=1;
mysql> call idata4();
Query OK, 0 rows affected (1.21 sec) // 不同主机执行的测试
执行效果非常好。但从业务绝对上看，已经是把多个事务集合成 1 个事务了。
5 小结
InnoDB 的 Change Buffer 是提供 TPS 的一个非常重要的设计。但假如在使用的过程中不注意其他机制对磁盘IO的发起，那么很难用气 Change Buffer 的最大的效率。
除了频繁事务，唯一性辅助索引 也有可能让 Change Buffer 失去作用。
