最近遇到类似案例，这里将可能出现这种情况的2个场景描述一下，其中一种情况在翻看老叶的公众号有类似文章，如下，
- 故障案例：MySQL唯一索引有重复值，官方却说This is not a bug
https://mp.weixin.qq.com/s/s7aHwtZXYBEqOIEO6F7O2w
我们分别描述。
场景1 unique_checks = 0
原理
当我们进行数据插入的时候，对于唯一索引，实际上大概会经历数据查找，唯一性检查、数据插入 3个阶段。而对于普通索引来讲如果page不在buffer pool中则可能在数据查找阶段就会写入到ibuff，这种情况就等待后续的ibuff合并即可。
但是我们一旦设置了unique_checks=0，对于唯一索引（非主键）而言也可能走普通索引的方式，我们大概看看是如何改变的，首先根据设置，事务检查唯一索引的标记会设置为如下，
trx->check_unique_secondary =
!thd_test_options(thd, OPTION_RELAXED_UNIQUE_CHECKS);
然后在插入阶段，row_ins_sec_index_entry_low函数首先会根据是否检查唯一性将search_mode 设置上BTR_IGNORE_SEC_UNIQUE标记，search_mode 的值很多，主要包含2类，A：读写锁模式/B：操作方式，他们各自占用不同的bit位。
if (!thr_get_trx(thr)->check_unique_secondary) {search_mode |= BTR_IGNORE_SEC_UNIQUE;}
接下来就是查找数据调用btr_cur_search_to_nth_level上层函数，进行数据定位，然后在其中判定，
case BTR_INSERT:
btr_op = (latch_mode & BTR_IGNORE_SEC_UNIQUE)
? BTR_INSERT_IGNORE_UNIQUE_OP
: BTR_INSERT_OP;
break;
如果为insert且latch_mode带有BTR_IGNORE_SEC_UNIQUE，设置btr_op为BTR_INSERT_IGNORE_UNIQUE_OP。
最后在判定是否能够使用ibuf上，我们看到如下，
if (btr_op != BTR_NO_OP &&
ibuf_should_try(index, btr_op != BTR_INSERT_OP)) { //是否进入 ibuf
/* Try to buffer the operation if the leaf
page is not in the buffer pool. */
fetch = btr_op == BTR_DELETE_OP ? Page_fetch::IF_IN_POOL_OR_WATCH //这里和 WATCH和purge线程有光
: Page_fetch::IF_IN_POOL; //bug page get gen 只看是否在buffer中
}
而函数ibuf_should_try就是判定是否使用ibuf，一旦使用ibuf，当然修改的相关page就不一定非要在buffer pool中，因此对于insert操作定义为Page_fetch::IF_IN_POOL，而函数ibuf_should_try主要包含如下判定：
- A:开启了change buffer
- B:不等于系统表空间
- C:不能是聚集索引
- D:不能处于export状态下
- E:insert操作不能是唯一索引
- F:其他操作，唯一索引也可以使用ibuf，这里实际上就只剩下delete和ignore唯一性的insert了
而在底层修改操作实际上只有insert和delete操作，而这里满足的是F条件因此insert操作的查找page动作被标记为Page_fetch::IF_IN_POOL，接下来buf_page_get_gen函数就不会再去访问物理磁盘了，这个时候可能返回的page为NULL，那就要走这个逻辑了，
if (block == nullptr) { //如果block没有在innodb buffer中进行操作
...
switch (btr_op) {
case BTR_INSERT_OP:
case BTR_INSERT_IGNORE_UNIQUE_OP: //注意这里
...
if (ibuf_insert(IBUF_OP_INSERT, tuple, index, page_id, page_size,
cursor->thr)) {
cursor->flag = BTR_CUR_INSERT_TO_IBUF;
goto func_exit;
}
也就是插入到ibuf中，那么我们可以想象，如果设置了unique_checks=0，这个时候如果重复的数据在磁盘上（因为innodb buffer查询不到page返回NULL），则会将接下来的数据本该重复的数据插入到ibuf，而不会去检测重复值。然后等到需要读取这个page到buffer pool的时候比如select，那就需要做ibuf的合并，合并后重复的数据就出现了。
测试
测试可以根据老叶公众号的方式测试，主体思想就是做一个大一点的表，然后重启数据库，并且不要开启启动时加载page到buffer pool，下面是我测试的结果：
771102a0b8e615659fe13dd400d1844.png
这里b列是一个唯一索引，我们看到了第二查询出现了2个相同的值
其他和总结
当出现这种情况的时候可以看到，第一个查询只出现了一行，这看起来好像是对的，但是实际上索引上有2行不同的值，对于唯一索引来讲如果访问到一行值，访问就会停止，因此出现了这种情况，看起来也是比较奇特。
因此我们在考虑使用unique_checks=0加速导入数据的时候需要特别注意一下这个问题，除非能够保证数据都是唯一的否则不建议设置，现在我们知道实际上加速就是让唯一索引也能够使用ibuf这个特性，这里我们再来会看一下官方的这句话
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.
很显然和我们分析一致。
场景2 RR隔离级别相关
这个地方主要和隔离级别有关了，虽然提出这个BUG的时间有点久了，但是这不是BUG，并且8.0也能重现，如下，
https://bugs.mysql.com/bug.php?id=69979
重现如下：
建表和插入数据
create table testuniq(id int primary key,a int unique key);
insert into testuniq values (10, 100), (20, 200);
mysql> select * from testuniq;
+----+------+
| id | a |
+----+------+
| 10 | 100 |
| 20 | 200 |
+----+------+
| TRX1 | TRX2 |
|---|---|
| 1.begin; | |
| 2.select * from testuniq; | |
| 3.update testuniq set a=300 where id=10; | |
| 4.update testuniq set a=100 where id=20; | |
| 5.select * from testuniq; |
完成第四步的时候数据就是，
mysql> select * from testuniq;
+----+------+
| id | a |
+----+------+
| 10 | 100 |
| 20 | 100 |
+----+------+
可以看到唯一索引出现了重复值，对于这个问题，只要不阻止第4步的update testuniq set a=100 where id=20操作按照原理上来讲就会出现重复值，因为RR有一个read view在begin开始后第一个select语句后一直存在，而update属于当前读访问的当前记录已经被修改了，因此第4步并没有访问历史记录，因此update通过，最终出现这种现象。同时在BUG中也详细描述了这是符合设计的PG也是类似的结果，可以自行参考。
网友评论