关于buffer:故障分析-MySQL唯一键约束失效

作者：胡呈清

爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

最近遇到一个故障：单主模式 5 节点 MGR 集群，在应用 mysqlshell 导入数据时，所有 secondary 节点报错惟一键抵触退出集群。最终失去的论断是：mysqlshell importTable() 导入数据时会做 SET unique_checks=0 操作，在满足肯定条件时会导致惟一键束缚生效，主库插入了反复的数据，但从库会插入失败从而报错。上面咱们就来聊聊这是怎么回事。

unique_checks=0 的原理（会导致惟一键束缚生效吗？）

看看官网文档是怎么形容的：

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:
SET unique_checks=0;
… SQL import statements …
SET unique_checks=1;
For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

对大数据量的导入，无论是 sql 文件还是 csv 文件，如果表中有惟一索引，咱们能够通过 SET unique_checks=1; 来放慢导入速度。这里用到的是 change buffer 的原理：

change buffer 的作用对象是一般二级索引，当插入、批改、删除二级索引记录时，即便数据不在 innodb buffer pool 中，也不须要先把数据从磁盘读取到内存。只须要在 change buffer 中实现 DML 操作，下次读取时才会从磁盘读取到内存，并与 change buffer 进行 merge，从而失去正确的数据。这个性能缩小了 DML 时的随机 IO。

这里要留神对象是一般二级索引，惟一索引为什么不行？当执行 DML 时，数据不在内存中，为了保障唯一性，必须先从磁盘读取数据，这个随机 IO 是无奈防止的，change buffer 失去了其作用，因而对惟一索引有效。

然而 unique_checks=0 让 change buffer 从新对惟一索引无效了。文档还有句暧昧的形容：“Be certain that the data contains no duplicate keys.”（你须要本人确保数据不蕴含反复值）。这会让惟一键束缚生效吗？再让咱们看看文档对于 unique_checks 零碎变量的形容：

If set to 0, storage engines are permitted to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0 to speed up large table imports to InnoDB.
Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.

至始至终没有呈现“不进行唯一性查看”的字眼，写文档的人真是刁滑。要搞清楚这个问题，咱们还得回到 change buffer 的原理：change buffer 无奈反对惟一索引，然而 unique_checks=0 让 change buffer 从新对惟一索引无效。联合文档两个形容，咱们作出如下假如：

• 如果 set unique_checks=0; DML 操作的数据，如果主键和惟一索引在 innodb buffer pool 中，则能够通过内存中的数据进行唯一性检测，这也是咱们在测试中都会遇到即便 set unique_checks=0 也不能插入反复数据；
• 但如果 DML 操作的数据如果不在 innodb buffer pool 中，则不会通过读取磁盘上的主键、惟一索引进行惟一检测，此时就和一般的二级索引一样，间接在 change buffer 实现数据写入，缩小磁盘 IO。

验证

1. 用 sysbench 造点数据

sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 \
--mysql-port=3309 --mysql-user=root --mysql-password=root \
--mysql-db=sbtest --tables=4 --table-size=1000000 --report-interval=3 --threads=8 --time=300 prepare

2. 再创立一张表，并导入一些数据

CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  unique key `a` (`a`)
);
insert into t(a,k,c,pad) select * from sbtest1 where id <= 5000;

3. 重启 mysqld

这一步目标是为了保证数据不在 innodb buffer pool 中，留神参数：

innodb_buffer_pool_size = 64M
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_dump_pct = 0

4. 插入反复数据

mysql> set session unique_checks=0;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t(a,k,c,pad) values(1,439502,'59488447928-69619891934-66520244141-26804756589-33623161608-43445073345-17979281186-83118017446-98119924781-27868548476','0000');
Query OK, 1 row affected (0.01 sec)

能够看到胜利插入了反复数据（这里用 where a=1 等值查问只会输入一行后果，因而用 where a<2）：

mysql> select id,a from t where a<2;
+------+---+
| id   | a |
+------+---+
|    1 | 1 |
| 5022 | 1 |
| 5026 | 1 |
| 5035 | 1 |
+------+---+
4 rows in set (0.01 sec)

如果想察看的更分明，能够继续运行 sysbench run，用 sbtest* 表把 innodb buffer pool 占满，这样能够重复插入同一行数据：

5. 一些烦扰验证的景象

有些记录无奈顺利插入进去，这个对我困扰十分大，一度让我认为推论是谬误的 … 这个就留给官网解释吧：

如果是 char 类型的惟一键，则不会有相似问题：

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB;

论断

如果 set unique_checks=0; DML 操作的数据，如果主键和惟一索引在 innodb buffer pool 中，则能够通过内存中的数据进行唯一性检测，这也是咱们在测试中通过都会遇到即便 set unique_checks=0 也不能插入反复数据；

但如果 DML 操作的数据如果不在 innodb buffer pool 中，则不会通过读取磁盘上的主键、惟一索引进行惟一检测，此时就和一般的二级索引一样，间接在 change buffer 实现数据写入，缩小磁盘 IO。

须要留神的是 mysqldump 导出的 sql 文件头部会主动携带 set unique_checks=0 信息，包含结尾咱们提到的 mysqlshell importTable() 也会做同样的设置。

对此我提了个 bug：https://bugs.mysql.com/bug.ph…

尽管文档很狡猾的写了：“Be certain that the data contains no duplicate keys.”，咱们也晓得了 set unique_checks=0 的原理就是通过 change buffer 减速惟一键的插入，这仿佛就不能齐全保障唯一性。如果这的确不算 bug，也心愿官网能把文档写的更分明明确些，而不是形容的暧昧不清。

解决方案

如果不能保障导入的数据惟一，在 mysqlshell importTable() 导入前敞开 change buffer：set global innodb_change_buffering=none;，如果是导入 sql 文件，则留神将 set unique_checks=0 正文。