技术分享 | MySQL 大表添加唯一索引的总结

作者：莫善

某互联网公司高级 DBA。

本文来源：原创投稿

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

一、前言

本文对 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,'北京');

3.2 风险规避

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

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

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

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

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

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

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

四、添加唯一索引的测试

4.1 hook 功能

具体使用请看官方文档：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

echo "时间: $(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 ]];then
  echo '拷贝总行数不匹配，修改失败，退出.'
  sleep 5
  exit -1
fi

（4）添加对应权限

chmod +x /tmp/hook/*

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

--hooks-path=/tmp/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_CO
  PIED_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)

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

4.3 加强版 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

log_addr='${BASH_SOURCE}:${FUNCNAME}:${LINENO}' #eval 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}' <<< "${count_info}")"
    
    f_logging "$(eval echo ${log_addr}):INFO" "库表: ${GH_OST_DATABASE_NAME}.${GH_OST_TABLE_NAME} 原表预计总行数: ${GH_OST_ESTIMATED_ROWS}, 实际拷贝总行数: ${GH_OST_COPIED_ROWS}"

    if [ -z "${count_total}" ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "唯一索引字段数据唯一性检查异常, 终止改表操作"
        exit -1
    fi

    mark=""

    for count in $(echo "${count_info}"|tr ":" " ")
    do
        if [ -n "${count}" ] && [ "${count}x" == "${count_total}x" ]
        then
            [ "${mark}x" == "x" ] && mark="true"
        else 
            mark="false"
        fi
    done
    
    if [ "${mark}x" == "truex" ]
    then
        f_logging "$(eval echo ${log_addr}):INFO" "唯一索引字段数据唯一性正常, 允许切表"
    else 
        f_logging "$(eval echo ${log_addr}):ERROR" "唯一索引字段数据唯一性检测到可能丢失数据, 终止改表操作"
        exit -1
    fi
    exit 0
}

f_main

该脚本非通用版，仅供参考。

mysql_comm='mysql -h xxxx -P xxxx -u xxxx -pxxxx db_name'   #这里是从库的地址
mysql_sql="select concat(count(distinct rshost,a_time),':',count(*)) from db.table"

其中检查唯一性的 SQL 可以使用如下的命令生成，仅供参考。

alter="alter table t add unique key uk_name(name,name2),add unique key uk_age(age);"
echo "${alter}"|awk 'BEGIN{ FS="(" ; RS=")";print "select concat(" } 
    NF>1 { print "count(distinct "$NF"),'\'':'\''," }
    END{print "count(*)) from t;"}'|tr -d '\n'

执行上面的命令会根据业务提交的添加唯一索引的 SQL 得到一条检查字段数据唯一性的 SQL 。

select concat(count(distinct name,name2),':',count(distinct age),':',count(*)) from t;

• 第
  一，如果是大表，在执行【gh-ost-on
  -before-cut-over】脚本过程中（大表执行这个脚本时间较长），新增的记录跟原来数据有重复，这个就没法规避了。
• 第二，
  在改表过程中，如果业务新增一条与原
  数据重复的记录，然后又删除，这种场景也会导致丢数据。
  
  
  
  

针对第二个场景可能有点抽象，所以举一个具体的例子，原表数据如下：

现在对 name 字段添加唯一索引。

假如现在正在使用 gh-ost 进行添加唯一索引，这时候业务做了下面几个操作：

（1）新增一条记录

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

这时候原表的数据就会变成像下面这样。

这时候新表的数据就会变成像下面这样。

id=1 和 id=4 是两条重复的记录，所以 id=1 会被覆盖掉。

（2）删除新增的记录

delete from t where id = 4;

这时候原表的数据就会变成像下面这样。

这时候新表的数据就会变成像下面这样。

可以发现，这时候如果发生切表，原表 id=1 的记录将会丢失，而且这种场景 hook 的脚本没法发现，它检查原表的 name 字段的数据唯一性是正常的。

针对上述两种极端场景，发生的概率应该是极低的，目前我也没想到什么方案解决这两个场景。

gh-ost 官方文档上说 --test-on-replica 参数可以确保不会丢失数据，这个参数的做法是在切表前停掉从库的复制，然后在从库上校验数据。

gh-ost comes with built-in support for testing via --test-on-replica: 
it allows you to run a migration on a replica, such that at the end of the migration gh-ost would stop the replica, swap tables, reverse the swap, and leave you with both tables in place and in sync, replication stopped.
This allows you to examine and compare the two tables at your leisure.

https://github.blog/2016-08-01-gh-ost-github-s-online-migration-tool-for-mysql/#testable Testable部分（Testable不是书写错误）

很明显，这个方式还是没法解决在实际切表那一刻保证数据不会丢，就是说切表和校验之间一定是存在时间差，这个时间差内出现新写入重复数据是没法发现的，而且大表的这个时间差只会更大。

五、总结

• 如果业务能接受，可以不使用唯一索引。将添加唯一索引的需求改成添加普通二级索引，这样就可以避免加索
  引导致数据丢失。
  
  

存储引擎读写磁盘，是以页为最小单位进行。唯一索引较于普通二级索引，在性能上并没有多大优势。相反，可能还不如普通二级索引。

• 在读请求上，唯一索引和普通二级索引的性能差异几乎可以忽略不计了。
• 在写请求上，普通二级索引可以使用到【Change Buffer】，而唯一索引没法用到【Change Buffer】，所以唯一索引会差于普通二级索引。
  
  

• 一定要加
  唯一索引的话，可以
  跟业务沟通确认是否能接受从库长时间延迟。如果能接受长时间延迟，可以优先使用 ONLINE DDL 进行添加唯一索引（小表直接用 ONLINE DDL即可）。
• 如果使
  用第三方工具添加唯一索引
  ，要优先使用 gh-ost（配上hook），添加之前一定要先检查待加唯一索引字段的唯一性，避免因为原表存在重复值而导致丢数据。
  
  

强烈建议不要马上删除【old】表，万一碰到极端场景导致丢数据了，还可以通过【old】表补救一下。

• pt-osc 建议添加【--no-drop-old-table】参数
• gh-ost 不建议添加【--ok-to-drop-table】参数
  
  

六、写在最后

相关推荐：

更多关于 SQLE 的信息和交流，请加入官方QQ交流群：637150065...