mysql添加索引

作者：莫善

某互联网公司高级 DBA。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1 前言

在数据库的运维工作中经常会遇到业务的改表需求，这可能是DBA比较头疼的需求，其中添加唯一索引可能又是最头疼的需求之一了。

MySQL 5.6 开始支持 Online DDL，添加[唯一]索引虽然不需要重建表，也不阻塞DML，但是大表场景下还是不会直接使用Alter Table进行添加，而是使用第三方工具进行操作，比较常见的就属pt-osc和gh-ost了。本文就来总结梳理一下添加唯一索引的相关内容。

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

2 添加唯一索引的方案简介

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

2.1 ONLINE DDL

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

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata Creating or adding a secondary index Yes No Yes No

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

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

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

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

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

2.2 pt-osc# ./pt-online-schema-change --versionpt-online-schema-change 3.0.13# 创建一张与原表结构一致的新表，然后添加唯一索引。同步全量数据。遍历原表，通过【ｉｎｓｅｒｔ IGNORE INTO】将数据拷贝到新表。同步增量数据。通过触发器同步增量数据。

触发器 映射的SQL语句 ｉｎｓｅｒｔ 触发器 ｒｅｐｌａｃｅ INTO ｕｐｄａｔｅ 触发器 ｄｅｌｅｔｅ IGNORE + ｒｅｐｌａｃｅ INTO ｄｅｌｅｔｅ 触发器 ｄｅｌｅｔｅ IGNORE

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

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

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

2.3 gh-ost# ./bin/gh-ost --version1.1.5# 创建一张与原表结构一致的新表，然后添加唯一索引。同步全量数据。遍历原表，通过【ｉｎｓｅｒｔ IGNORE INTO】将数据拷贝到新表。同步增量数据。通过应用原表DML产生的binlog同步增量数据。

binlog语句 映射的SQL语句 ｉｎｓｅｒｔ ｒｅｐｌａｃｅ INTO ｕｐｄａｔｅ ｕｐｄａｔｅ ｄｅｌｅｔｅ ｄｅｌｅｔｅ

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

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

hook功能后文会着重介绍。

2.4 小总结

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

方案 是否丢数据 建议 ONLINE DDL 不丢数据 适合小表，及对从库延迟没要求的场景 pt-osc 可能丢数据，无辅助功能可以避免丢数据的场景 不适合添加唯一索引 gh-ost 可能丢数据，有辅助功能可以避免部分丢数据的场景 适合添加唯一索引

3 添加唯一索引的风险

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

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

3.1 风险介绍

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

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

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

id name age 1 张三 22 2 李四 19 3 张三 20

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

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

id name age addr 1 张三 22 北京

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

id name age addr 1 张三 22 北京 2 李四 19 广州 3 张三 20 深圳

alter table t add unique key uk_name(name);

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

id name age addr 1 张三 22 北京 2 李四 19 广州

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

id name age addr 1 张三 22 北京 2 李四 19 广州 3 王五 20 深圳

alter table t add unique key uk_name(name);

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

ｉｎｓｅｒｔ into t(name,age,addr) values(‘张三‘,22,‘北京‘);

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

id name age addr 2 李四 19 广州 3 王五 20 深圳 4 张三 22 北京

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

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

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

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

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

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

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

4 添加唯一索引的测试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/hookcd /tmp/hook

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

vim gh-ost-on-rowcount-complete-hook#!/bin/bashecho "$(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/bashecho "时间: $(date ‘+%F %T‘) 库表: $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME 预计总行数: $GH_OST_ESTIMATED_ROWS 拷贝总行数: $GH_OST_COPIED_ROWS"if [[ `cat /tmp/$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME.txt` -gt $GH_OST_COPIED_ROWS ]];thenecho ‘拷贝总行数不匹配，修改失败，退出.‘sleep 5exit -1fi

（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】文件保存的值小，所以会导致改表失败。这种场景对我们来说体验十分不友好，只要改表过程中目标表存在【ｄｅｌｅｔｅ】操作，就会导致添加唯一索引操作失败。

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

第二，如果改表过程中，新加一条与原表的记录重复的数据，那么这个操作不会影响【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/bashwork_dir="/opt/soft/zzonlineddl"#工作目录. ${work_dir}/function/log/f_logging.sh#日志模块if [ -f "${work_dir}/conf/zzonlineddl.conf" ]then. ${work_dir}/conf/zzonlineddl.conf#改表项目的配置文件filog_addr=‘${BASH_SOURCE}:${FUNCNAME}:${LINENO}‘ #ｅｖａｌ echo ${log_addr}#针对该改表任务生成的配置文件#里面保存的是这个改表任务的目标库的从库连接信息【mysql_comm】变量的值#还有数据唯一性的校验SQL【mysql_sql】变量的值hook_conf="${work_dir}/hook/conf/--mysql_port--_${GH_OST_DATABASE_NAME}.${GH_OST_TABLE_NAME}". ${hook_conf}function f_main(){count_info="$(${mysql_comm} -NBe "${mysql_sql}")"count_total="$(awk -F: ‘{print $NF}‘