专栏首页腾讯云数据库(TencentDB)大咖丁奇:加唯一索引怎么会导致丢数据?

大咖丁奇:加唯一索引怎么会导致丢数据?

 点击上方蓝字每天学习数据库

一起构建MySQL知识网络,我是林晓斌,今天的文章我们从索引说起。

林晓斌

林晓斌,网名丁奇,腾讯云数据库负责人,数据库领域资深技术专家。作为活跃的MySQL社区贡献者,丁奇专注于数据存储系统、MySQL源码研究和改进、MySQL性能优化和功能改进,在业务场景分析、系统瓶颈分析、性能优化方面拥有丰富的经验。其创作的《MySQL实战45讲》专栏受众已逾2万人。

你一定知道了,索引的作用是加快查询速度。比如有一个人口信息表,如果没有加索引,你要按照身份证号查找一个人,就得全表扫描,表数据量大的时候,这个语句的执行速度就会很慢。

给身份证号字段加上索引,再用身份证号来查就快了。由于身份证号是不会重复的,也有的DBA会创建唯一索引。唯一索引当然也可以用来加速查询,它跟普通索引的区别是什么呢?

唯一索引额外功能 

为了便于说明,我们创建一个简单的表t,如下:

CREATE TABLE `t` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL,  UNIQUE KEY `a` (`a`)) ENGINE=InnoDB;insert into t values(1,1),(2,1);

我在这个表的字段a上创建了唯一索引,字段b上面没有索引,表里初始化两行数据,分别是(1, 1)和(2, 1)。假设我现在要执行一个新的插入语句:

insert into t values(2,1);

就会报错:

ERROR 1062 (23000): Duplicate entry '2' for key'a';

如果我现在要给字段b上加上一个唯一索引,可以这么执行:

alter table t add unique index (b);

同样会报错:

ERROR 1062 (23000): Duplicate entry '1' for key 'b'

也就是说,MySQL的唯一索引,同时起到了唯一约束的作用,这个作用体现在两个方面:

1. 在有唯一索引的字段,不能插入跟已经存在的数据重复的行;

2. 在有重复行的字段上,不能创建唯一索引。

这个还是比较好理解的,然后你就会对这篇文章的标题表示疑问:既然是约束,那大不了在创建唯一索引的时候就失败好了,怎么会导致丢数据呢?

这个问题要从加索引的代价说起。

加索引的代价

在MySQL的老版本里,由于加索引和加字段是会锁表的,这个期间表上不能更新,因此不能直接在业务库上操作。不过从5.6版本开始,MySQL已经支持了online DDL特性,也就是在加索引和加字段的过程中,这个表是可以正常读写的。

虽然不会影响读写业务,但是在大表是加索引还存在另一个问题,就是会导致主备延迟。如果一个加索引命令在主库上执行30分钟,那么这个命令传到从库,也要执行30分钟。这样就会导致从库有30分钟的延迟。

如果是用的MySQL5.6及之后的版本,并且操作的是小表,比如加索引的执行时间不超过5分钟,由于不会导致明显的延迟,是可以直接在线执行的。当然我还是会建议你在低峰期执行这个操作。

pt-ost加索引

那你会问了,如果是大表加索引,怎么解决这个延迟的问题呢?这时候就要介绍到在线加字段的工具了,比如percona的工具pt-ost,这里的ost就是onlineschema change的简称。(其实更常用的还有gh-ost,今天我们先不展开)

先介绍一下 pt-ost的流程,假设我要给前面的表t的字段b上加一个唯一索引,执行流程是这样的:

1. 创建一个临时表,记为 t’,t’跟t的表结构一样,只是在字段b上多了一个唯一索引;这时候t’是一个空表,因此字段b上的唯一索引是肯定可以创建成功的;

2. 从表t中一行行读出数据,写入到t’中,这个过程称为全量阶段;

3. 如果在全量阶段中,表t上有更新数据,会用触发器写入到t’中,这个过程称为增量阶段。

4. 增量阶段完成后,表t和t'交换表名,用户看到的表t就是加完索引的表,然后再把现在的t'表删掉。

这个工具把一个加索引的大操作,改成了几个小操作来执行,可以解决主备延迟的问题。

不过你一定发现了,全量阶段有问题。t’上字段b已经有了唯一索引,在插入第一行数据(1,1)的时候是没问题的,但是插入第二行数据(2,1)的时候,就违反了唯一索引的约束,插入语句会报错。

下图的状态2表示了这个过程。

图1 表t的字段b加唯一索引的执行流程

但是,pt工具会忽略这个错误,继续执行后面的流程,这样全量阶段结束后,表t'里就少了一行,交换表名后,用户就会看到,丢了数据。

pt-ost的check-unique-key-change参数

这么严重的一个bug,官方当然还是要处理的,在pt-ost3.0版本里面,增加了一个参数check-unique-key-change。

看这个参数名的字面意思,好像是在加唯一索引之前会判断一下这个操作是否会丢数据。

实际上是不是这样呢?我们来看一个例子。假设我现在在一个空表上用pt-ost工具加一个唯一索引,执行的命令如下:

./pt-online-schema-change  D=test,t=lewis --execute --alter "ADDUNIQUE uniq_case_id(c1)"

这个命令的意思是,在test库的lewis表的字段c1上,创建一个唯一索引,这里我省略了连接信息。

虽然现在这个表是空表,但执行这个命令仍然会报错,报错的截图如下

图2 空表上用pt-ost工具加唯一索引报错截图

输出的提示里说明,发现你要创建一个唯一索引,所以工具退出。

而如果在上面的命令中增加一个参数 --nocheck-unique-key-change,这个命令就能执行成功。

这时候你一定有些怀疑了,这个参数看来并没有真的去检查数据是否重复?你一定很容易设计一个实验,在test库的lewis表插入两行完全相同的数据,然后执行pt-ost工具,并且带上参数--nocheck-unique-key-change。这时候命令能够成功执行,并且丢了一行数据。

有了上面这三个对照实验,你现在知道了这个参数的设计逻辑:pt-ost工具知道它在创建唯一索引的时候,可能会导致数据丢失。因此默情况下,只要发现你要用这个工具来创建唯一索引,就直接报错返回。

而使用这个功能的正确姿势如下:

1. 首先你要先确保要加唯一索引的字段上没有重复值。这个判断逻辑,工具并不会帮你做,但是在提示中给了方法,就是用count(distinct(c1))的值来判断。

a. 如果值等于表的行数,表示没有重复值;

b. 如果值小于表的行数,表示有重复值;

2. 有重复值你就不要创建唯一索引了;

3. 如果确认没有重复值,就明确带上--nocheck-unique-key-change这个参数,来执行创建唯一索引的命令。

这个命令在判断是否创建唯一索引的时候,实现的逻辑上还有一个bug,我们会用另外一篇文章来说明。

小结 

接下来,我们来总结一下今天的主要内容:

1. MySQL的InnoDB表加普通索引和唯一索引的时候,支持在线加索引,不会阻塞线上的读写操作;

2. 如果表不大,可以在几分钟之内执行完成加索引操作,可以在低峰期直接加,效果更快;

3. 直接在表上加唯一索引是没有风险的,如果字段上存在重复值,加索引语句会报错;

4. 如果要用pt-ost工具加唯一索引,要主动先确认要加唯一索引的字段上没有重复值,否则可能会导致丢数据。

留一个问题给大家探讨

前面说,新版本的MySQL对一些DDL操作支持online DDL,而另外一些操作则不支持。MySQL的这个行为会影响我们的操作决策。如果是可以online 的,只需要在低峰期操作就可以了;如果是不能online的,就必须使用工具来做。那么:

你怎么判断你在使用的这个MySQL版本,对某一个你要执行DDL的语句类型,是否支持online DDL呢?

欢迎在评论区留下你对讨论题的想法,数据君将挑选最精彩的回答送上腾讯云数据库100元无门槛代金券一张。

往期推荐

《大咖丁奇:索引存储顺序和order by不一致?》

《鹅厂老司机教你学习Innodb》

年中薅羊毛,可省18040元

云数据库MySQL年中疯狂折扣中,买MySQL高可用版送6个月数据迁移服务,1核1G内存100G SSD盘低至96.8元/月。免费数据管理DMC,双节点架构,自动容灾,最高可省18040元!点击左下角“阅读原文”立即参与~

↓↓点“阅读原文”享年中福利

好文和朋友一起看!

本文分享自微信公众号 - 腾讯云数据库(TencentDB),作者:林晓斌

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL索引分类,90%的开发都不知道

    MySQL的索引分类问题一直让人头疼,几乎所有的资料都会给你列一个长长的清单,给你介绍什么主键索引、单值索引,覆盖索引,自适应哈希索引,全文索引,聚簇索引,非聚...

    腾讯云数据库 TencentDB
  • 微信大牛教你深入了解数据库索引

    | 作者 刘国斌,腾讯微信事业群研发工程师,目前从事企业微信的后台研发工作,已经参与企业微信消息系统、群聊、客户联系等企业微信多个核心功能的迭代。 ---- ...

    腾讯云数据库 TencentDB
  • 大咖丁奇:索引存储顺序和order by不一致,怎么办?

    点击上方蓝字每天学习数据库 我是林晓斌,今天作为【迪B课堂】的客串嘉宾来跟大家分享:当索引存储顺序和order by不一致,该怎么办? ? 林晓斌 林晓斌,...

    腾讯云数据库 TencentDB
  • 史上最全存储引擎、索引使用及SQL优化的实践

    整个MySQL Server由以下组成 : Connection Pool :连接池组件 Management Services & Utilities...

    海仔
  • 从根上理解SQL的like查询%在前为什么不走索引?

    其实每天都有人私信我,如果遇到一些好的问题,我会拿来单独写文章的。比如,昨天就有人问我,like 查询 % 在前为什么不走索引?不能人云亦云,我们应该从根上理解...

    业余草
  • Elasticsearch 创建索引前必须要了解的知识,提前避坑!

    在项目中后期,如果想调整索引的 Mapping 结构,比如将 ik_smart 修改为 ik_max_word 或者 增加分片数量 等,但 Elasticsea...

    create17
  • Python第十二章-多进程和多线程02-多线程

    MySQL被Sun收购后,搞了个过渡的6.0版本,没多久就下线了(有一次居然听说有人在线上用6.0版本,我惊得下巴都掉了)。被Oracle收购后,终于迎来了像样...

    不会飞的小鸟
  • Mysql索引知识详谈

    原文地址:https://www.t-io.org/1159984867644153856

    talent-tan
  • 详解 Linux中的关机和重启命令

    如有疑问请留言或者到本站社区交流讨论,以上就是Linux 中关机重启的命令详解,感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

    砸漏
  • 当一只程序员遇到了一道无聊的智力填数题!

    本猿在朋友圈和群里多次看到这样一道智力题(见下图),一看就是一道需要乱扯的无聊的题目。好吧,试试就试试。

    后端技术探索

扫码关注云+社区

领取腾讯云代金券