关于mysql:技术分享-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

首先咱们看一下官网对增加索引的介绍：

惟一索引属于非凡的二级索引，将援用官网介绍增加二级索引的内容做例子。

能够看到 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 小总结

由上述介绍可知，各计划都有优缺点

3 增加惟一索引的危险

依据下面的介绍能够得悉 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 增加惟一索引的测试

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

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

这个 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

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

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

hook_conf 变量的值是这样的，由改表平台依据业务的 SQL 语句主动生成。

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;

须要留神的是，这个加强版的 hook 也不能 100% 保障肯定不会丢数据，有两种极其状况还是会丢数据。

• 第一，如果是大表，在执行【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-migrati… Testable 局部（Testable 不是书写谬误）

很显著，这个形式还是没法解决在理论切表那一刻保证数据不会丢，就是说切表和校验之间肯定是存在时间差，这个时间差内呈现新写入反复数据是没法发现的，而且大表的这个时间差只会更大。

另外停掉从库的复制很可能也存在危险，很多业务场景是依赖从库进行读申请的，所以要慎用这个性能。

5 总结

• 如果业务能承受，能够不应用惟一索引。将增加惟一索引的需要改成增加一般二级索引，这样就能够防止加索引导致数据失落。

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

• 在读申请上，惟一索引和一般二级索引的性能差别简直能够忽略不计了。
• 在写申请上，一般二级索引能够应用到【Change Buffer】，而惟一索引没法用到【Change Buffer】，所以惟一索引会差于一般二级索引。

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

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

• pt-osc 倡议增加【–no-drop-old-table】参数
• gh-ost <font color=’red’> 不倡议增加 </font>【–ok-to-drop-table】参数

6 写在最初

本文对 MySQL 大表增加惟一索引做了一下总结，分享了一些案例和教训。

总体来说增加惟一索引是存在肯定的危险的，各公司的业务场景也不一样，需要也不同，还可能碰上其余未知的问题，<font color=’red’> 本文所有内容仅供参考。</font>