MySQL大表增加唯一索引操作

Oracle中给表增加索引可以用online的模式，不影响正常业务的情况下执行创建操作，MySQL数据库好像对这种在线操作是比较头疼的，技术社群的这篇文章《技术分享 | MySQL 大表添加唯一索引的总结》了解一下。

一、前言

在数据库的运维工作中经常会遇到业务的改表需求，这可能是 DBA 比较头疼的需求，其中添加唯一索引可能又是最头疼的需求之一了。MySQL 5.6 开始支持 Online DDL ，添加[唯一]索引虽然不需要重建表，也不阻塞 DML ，但是大表场景下还是不会直接使用 Alter Table 进行添加，而是使用第三方工具进行操作，比较常见的就属 pt-osc 和 gh-ost 了。本文就来总结梳理一下添加唯一索引的相关内容。

本文对 ONLINE DDL 讨论的也是基于 MySQL 5.6 及以后的版本。

二、添加唯一索引的方案简介

这部分内容仅介绍 ONLINE DDL 、pt-osc 和 gh-ost 三种方案，且仅做简单介绍，更加详细的内容请参考官方文档。

2.1 ONLINE DDL

首先我们看一下官方对添加索引的介绍：

唯一索引属于特殊的二级索引，将引用官方介绍添加二级索引的内容做例子。

可以看到 ONLINE DDL 采用 In Place 算法创建索引，添加索引是不阻塞 DML ，大致流程如下：

• 同步全量数据。遍历主键索引，将对应的字段（多字段）值，写到新索引。
• 同步增量数据。遍历期间将修改记录保存到 Row Log ，等待主键索引遍历完毕后回放 Row Log 。

也不是完全不阻塞 DML ，在Prepare 和 Commit 阶段需要获取表的 MDL 锁，但Execute 阶段开始前就已经释放了 MDL 锁，所以不会阻塞 DML 。在没有大查询的情况下，持锁时间很短，基本可以忽略不计，所以强烈建议改表操作时避免出现大查询。

由此可见，表记录大小影响着加索引的耗时。如果是大表，将严重影响从库的同步延迟。好处就是能发现重复数据，不会丢数据。

2.2 pt-osc

# ./pt-online-schema-change --version
pt-online-schema-change 3.0.13
# 


• 创建一张与原表结构一致的新表，然后添加唯一索引。
• 同步全量数据。遍历原表，通过【INSERT IGNORE INTO】将数据拷贝到新表。
• 同步增量数据。通过触发器同步增量数据。

由此可见，这个方式不会校验数据的重复值，遇到重复的数据后，如果是同步全量数据就直接忽略，如果是同步增量数据就覆盖。

这个工具暂时也没有相关辅助功能保证不丢数据或者在丢数据的场景下终止添加唯一索引操作。

pt-osc 有个参数【--check-unique-key-change】可以禁止使用该工具添加唯一索引，如果不使用这个参数就表示允许使用 pt-osc 进行添加索引，当遇到有重复值的场景，好好谋划一下怎么跑路吧。

2.3 gh-ost

# ./bin/gh-ost --version
1.1.5
# 


• 创建一张与原表结构一致的新表，然后添加唯一索引。
• 同步全量数据。遍历原表，通过【INSERT IGNORE INTO】将数据拷贝到新表。
• 同步增量数据。通过应用原表 DML 产生的 binlog 同步增量数据。

由此可见，这个方式也不会校验数据的重复值，遇到重复的数据后，如果是同步全量数据就直接忽略，如果是同步增量数据就覆盖。

值得一提的是，这个工具可以通过 hook 功能进行辅助，以此保证在丢数据的场景下可以直接终止添加唯一索引操作。

hook 功能后文会着重介绍。

2.4 小总结

由上述介绍可知，各方案都有优缺点

三、添加唯一索引的风险

根据上面的介绍可以得知 gh-ost 是比较适合大表加唯一索引，所以这部分就着重介绍一下 gh-ost 添加唯一索引的相关内容，主要是希望能帮助大家避坑。

如果业务能接受从库长时间延迟，也推荐 ONLINE DDL 的方案。

3.1 风险介绍

我们都知道使用第三方改表工具添加唯一索引存在丢数据的风险，总结起来大致可以分如下三种：

文中出现的示例表的 id 字段默认是主键。

• 第一，新加字段，并对该字段添加唯一索引。

alter table t add addr varchar(20) not null default '北京',add unique key uk_addr(addr); #注意这里是不允许为空


如果这时候使用gh-ost执行上述需求，最后只会剩下一条记录，变成下面这样。

• 第二，原表存在重复值，如下数据表。

alter table t add unique key uk_name(name);


如果这时候使用 gh-ost 执行上述需求，id=3 这行记录就会被丢弃，变成下面这样。

• 第三，改表过程中新写（包含更新）的数据出现重复值。

alter table t add unique key uk_name(name);


如果这时候使用 gh-ost 执行上述需求，在拷贝原表数据期间，业务端新增一条如下面 INSERT 语句的记录。

insert into t(name,age,addr) values('张三',22,'北京');


这时候，id=1这行记录就会被新增的记录覆盖，变成下面这样

3.2 风险规避

• 新加字段，并对该字段添加唯一索引的风险规避

针对这类场景，规避方式可以禁止【添加唯一索引与其他改表动作】同时使用。最终，将风险转移到了上述的第二种场景（原表存在重复值）。

如果是工单系统，在前端审核业务提交的 SQL 是否只有添加唯一索引操作，不满足条件的 SQL 工单不允许提交。

• 原表存在重复值的风险规避

针对这类场景，规避方式可以采用 hook 功能辅助添加唯一索引，在改表前先校验待添加唯一索引的字段的数据唯一性。

• 改表过程中新写（包含更新）的数据出现重复值的风险规避

针对这类场景，规避方式可以采用 hook 功能添加唯一索引，在全量拷完切表前校验待添加唯一索引的字段的数据唯一性。

四、添加唯一索引的测试

4.1 hook 功能

gh-ost 支持 hook 功能。简单来理解，hook 是 gh-ost 工具跟外部脚本的交互接口。使用起来也很方便，根据要求命名脚本名且添加执行权限即可。

具体使用请看官方文档：https://github.com/github/gh-ost/blob/f334dbde5ebbe85589363d369ee530e3aa1c36bc/doc/hooks.md

4.2 hook 使用样例

这个样例是网上找的，可能很多小伙伴都在用。

（1）创建 hook 目录

mkdir /tmp/hook
cd /tmp/hook


（2）改表前执行的 hook 脚本

vim gh-ost-on-rowcount-complete-hook


#!/bin/bash

echo "$(date '+%F %T') rowcount-complete schema:$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME before_row:$GH_OST_ESTIMATED_ROWS"
echo "$GH_OST_ESTIMATED_ROWS" > /tmp/$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME.txt


（3）全量拷贝完成后执行的 hook 脚本

vim gh-ost-on-row-copy-complete-hook


#!/bin/bash

if [[ `cat /tmp/$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME.txt` -gt $GH_OST_COPIED_ROWS ]];then
  echo '拷贝总行数不匹配，修改失败，退出.'
  sleep 5
  exit -1
fi


（4）添加对应权限

chmod +x /tmp/hook/*


（5）使用 在 gh-ost 命令添加如下参数即可。

--hooks-path=/tmp/hook


这个 hook 的工作流程大概如下：

• 改表前先执行【gh-ost-on-rowcount-complete-hook】脚本获取当前表的记录数【GH_OST_ESTIMATED_ROWS】，并保存到【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】文件
• 原表全量数据拷贝完成后执行【gh-ost-on-row-copy-complete-hook】脚本，获取实际拷贝的记录数【GH_OST_COPIED_ROWS】，然后和【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】文件存的值做比较，如果实际拷贝的记录数小，就视为丢数据了，然后就终止改表操作。反之就视为没有丢数据，可以完成改表。

其实这个 hook 是存在风险的：

• 第一，如果改表过程中原表有删除操作，那么实际拷贝的行数势必会比【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】文件保存的值小，所以会导致改表失败。这种场景对我们来说体验十分不友好，只要改表过程中目标表存在【DELETE】操作，就会导致添加唯一索引操作失败。

关于这个问题，之前跟这个 hook 用例的原作者沟通过，他是知晓这个问题的，并表示他们的业务逻辑是没有删除【DELETE】操作，所以不会有影响。

• 第二，如果改表过程中，新加一条与原表的记录重复的数据，那么这个操作不会影响【GH_OST_COPIED_ROWS】的值，最终会改表成功，但是实际会丢失数据。

有小伙伴可能会疑问，上述【gh-ost-on-row-copy-complete-hook】脚本中，为什么不用【GH_OST_ESTIMATED_ROWS】的值与【GH_OST_COPIED_ROWS】比较？

首先我们看一下【GH_OST_ESTIMATED_ROWS】的值是怎么来的。

GH_OST_ESTIMATED_ROWS := atomic.LoadInt64(&this.migrationContext.RowsEstimate) + atomic.LoadInt64(&this.migrationContext.RowsDeltaEstimate)


可以看到【GH_OST_ESTIMATED_ROWS】是预估值，只要原表在改表过程中有 DML 操作，该值就会变化，所以不能用来和【GH_OST_COPIED_ROWS】作比较。

hook 实现逻辑请参考：https://github.com/github/gh-ost/blob/master/go/logic/hooks.go

4.3 加强版 hook 样例

上面的 hook 样例虽然存在一定的不足，但是也给我提供了一个思路，知道有这么个辅助功能可以规避添加唯一索引引发丢数据的风险。受这个启发，并查阅了官方文档后，我整理了个加强版的 hook 脚本，只需要一个脚本就能避免上述存在的几种问题。

按说应该是两个脚本，且代码一致即可。

• 改表前先校验一次原表是否存在待添加唯一索引的字段的数据是否是唯一的，如果不满足唯一性就直接退出添加唯一索引。
• 切表前再校验一次，但是我们环境是在代码里面做了校验，在业务提交工单后直接先判断唯一性，然后再处理后续的逻辑，所以第一个校验就省略了（改表工单代码代替hook校验）。

vim gh-ost-on-before-cut-over


这表示在切表前需要执行的 hook 脚本，即：切表前检查一下唯一索引字段的数据是否有重复值，这样避免改表过程中新增的数据跟原来的有重复。

#!/bin/bash
work_dir="/opt/soft/zzonlineddl"                                  #工作目录
. ${work_dir}/function/log/f_logging.sh                           #日志模块
if [ -f "${work_dir}/conf/zzonlineddl.conf" ]
then
    . ${work_dir}/conf/zzonlineddl.conf                           #改表项目的配置文件
fi