MySQL 大表添加唯一索引
在数据库运维工作中，表结构变更尤其是大表添加唯一索引，是 DBA 常常面临的棘手任务。本文将深入剖析 MySQL 大表添加唯一索引的多种方案，详细阐述其中的风险，并提供实用的规避策略与测试方法，助力 DBA 更安全、高效地完成这一操作。
一、添加唯一索引的主流方案解析
（一）ONLINE DDL 方案
MySQL 5.6 及后续版本引入的 ONLINE DDL 功能，为添加索引提供了新的途径。它采用 In Place 算法创建索引，在添加过程中基本不阻塞 DML 操作。其大致流程如下：
- 全量数据同步：遍历主键索引，将对应字段（多字段）值写入新索引。
- 增量数据同步：遍历期间将修改记录保存到 Row Log，待主键索引遍历完成后回放 Row Log。
不过，ONLINE DDL 在 Prepare 和 Commit 阶段仍需获取表的 MDL 锁，虽然在没有大查询的情况下持锁时间很短，但大表场景下仍会对从库同步延迟产生显著影响。其优势在于能发现重复数据，确保数据不丢失，因此更适合小表及对从库延迟要求不高的场景。
（二）pt-osc 方案
pt-osc（Percona Toolkit Online Schema Change）是常用的第三方改表工具，它通过创建与原表结构一致的新表来添加唯一索引，具体流程为：
- 全量数据同步：遍历原表，使用【INSERT IGNORE INTO】将数据拷贝到新表。
- 增量数据同步：借助触发器实现，INSERT 触发器对应 REPLACE INTO，UPDATE 触发器对应 DELETE IGNORE + REPLACE INTO，DELETE 触发器对应 DELETE IGNORE。
该方案的缺陷在于不会校验数据重复值，遇到重复数据时，全量同步会直接忽略，增量同步则会覆盖，且缺乏保证不丢数据或在丢数据时终止操作的辅助功能。因此，pt-osc 不太适合用于添加唯一索引。
（三）gh-ost 方案
gh-ost（GitHub's Online Schema Migration for MySQL）同样通过创建新表添加唯一索引，流程如下：
- 全量数据同步：遍历原表，通过【INSERT IGNORE INTO】将数据拷贝到新表。
- 增量数据同步：应用原表 DML 产生的 binlog 进行同步，INSERT 对应 REPLACE INTO，UPDATE 对应 UPDATE，DELETE 对应 DELETE。
gh-ost 的亮点在于支持 hook 功能，可通过该功能在丢数据场景下终止添加唯一索引操作，相对更适合大表添加唯一索引。
（四）方案对比与小结
| 方案 | 是否丢数据 | 适用场景 |
|---|---|---|
| ONLINE DDL | 不丢数据 | 小表、对从库延迟无要求的场景 |
| pt-osc | 可能丢数据，无辅助功能 | 不适合添加唯一索引 |
| gh-ost | 可能丢数据，有辅助功能 | 适合添加唯一索引 |
二、gh-ost 添加唯一索引的风险与规避
（一）主要风险类型
- 新增字段并添加唯一索引：若新字段不允许为空，使用 gh-ost 可能导致多条记录仅保留一条。
- 原表存在重复值：添加唯一索引时，重复记录会被丢弃。
- 改表过程中新写数据出现重复值：新写入的重复数据会覆盖原记录，造成数据丢失。
（二）风险规避策略
- 针对新增字段并添加唯一索引：禁止添加唯一索引与其他改表动作同时进行，可通过工单系统前端审核实现。
- 针对原表存在重复值：利用 gh-ost 的 hook 功能，在改表前校验待添加唯一索引字段的数据唯一性。
- 针对改表过程中新写数据重复：同样借助 hook 功能，在全量拷贝完成切表前再次校验字段数据唯一性。
三、gh-ost hook 功能的实践应用
（一）hook 功能基础介绍
hook 是 gh-ost 工具与外部脚本的交互接口，使用时只需按要求命名脚本并添加执行权限。
（二）基础 hook 样例
- 创建 hook 目录：
mkdir /tmp/hook && cd /tmp/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
- 全量拷贝完成后执行的脚本：比较预计行数与实际拷贝行数，若不一致则终止改表。
#!/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
- 添加权限并使用：
chmod +x /tmp/hook/*，在 gh-ost 命令中添加--hooks-path=/tmp/hook参数。
（三）加强版 hook 样例
该版本在切表前检查唯一索引字段数据的唯一性，避免改表过程中新增数据与原数据重复导致的问题。脚本通过配置文件获取从库连接信息和唯一性校验 SQL，若检测到数据不唯一则终止改表。
四、添加唯一索引的优化建议
（一）优先考虑普通索引
若业务允许，可将唯一索引需求改为普通二级索引。从性能角度看，唯一索引在读请求上与普通二级索引差异微乎其微，但在写请求上，普通二级索引可使用 Change Buffer，而唯一索引无法使用，因此唯一索引性能更差。
（二）合理选择方案
- 若业务能接受从库长时间延迟，小表可直接使用 ONLINE DDL，大表也可优先考虑该方案。
- 使用第三方工具时，优先选择 gh-ost 并搭配 hook 功能，且添加前务必检查待加唯一索引字段的唯一性。
（三）善后处理
改表完成后，不要立即删除 old 表，以便在极端场景导致丢数据时进行补救。pt-osc 建议添加【--no-drop-old-table】参数，gh-ost 不建议添加【--ok-to-drop-table】参数。
五、总结
MySQL 大表添加唯一索引是一项具有挑战性的任务，各方案均有优缺点。ONLINE DDL 适合特定场景，第三方工具中 gh-ost 搭配 hook 功能更具优势，但仍需注意风险。在实际操作中，DBA 应根据业务需求和场景，谨慎选择方案，做好风险评估与规避，确保数据库操作的安全与稳定。本文内容仅供参考，实际应用中需结合具体情况灵活处理。
浙公网安备 33010602011771号