MySQL:Innodb 唯一索引出现重复值的场景分析

数据库
当我们进行数据插入的时候,对于唯一索引,实际上大概会经历数据查找,唯一性检查、数据插入三个阶段。

最近遇到类似案例,这里将可能出现这种情况的2个场景描述一下,其中一种情况在翻看老叶的公众号有类似文章,如下,

故障案例:MySQL唯一索引有重复值,官方却说This is not a bug

我们分别描述。

场景1 unique_checks = 0

1.原理

当我们进行数据插入的时候,对于唯一索引,实际上大概会经历数据查找,唯一性检查、数据插入 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的合并,合并后重复的数据就出现了。

2.测试

测试可以根据老叶公众号的方式测试,主体思想就是做一个大一点的表,然后重启数据库,并且不要开启启动时加载page到buffer pool,下面是我测试的结果:

这里b列是一个唯一索引,我们看到了第二查询出现了2个相同的值。

3.其他和总结

当出现这种情况的时候可以看到,第一个查询只出现了一行,这看起来好像是对的,但是实际上索引上有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也是类似的结果,可以自行参考。

责任编辑:赵宁宁 来源: MySQL学习
相关推荐

2023-02-26 00:00:06

MySQL索引故障

2016-08-05 14:33:19

MySQL索引数据库

2022-08-04 08:22:49

MySQL索引

2018-09-14 09:12:00

数据库MySQL索引约束

2018-09-16 23:14:18

MySQL索引约束主键

2011-08-18 11:18:25

Oracle唯一约束唯一索引

2023-01-03 07:44:53

MySQL查询重复

2015-05-20 13:48:26

MySQL索引

2021-04-12 10:52:10

InnoDB索引数据库

2023-02-10 10:14:59

普通索引唯一索引

2010-10-12 14:40:03

mysql索引

2022-01-27 11:02:04

索引数据存储

2010-11-25 14:40:27

MySQL查询

2024-04-19 13:57:30

索引数据库查询

2011-06-15 18:59:16

算法SEO

2020-12-09 10:10:24

MySQL数据库算法

2021-09-06 06:45:06

普通索引唯一

2010-03-02 16:50:34

WCF返回值

2021-09-01 08:58:15

项目 UTFailed

2024-01-05 14:20:55

MySQL索引优化器
点赞
收藏

51CTO技术栈公众号