给 1500 万行的 MySQL 表加唯一索引：一次生产事故实录

每个 DBA 都有一段关于 ALTER TABLE 翻车的故事。这是我的。一张 1500 万行的生产 MySQL 表，一个必须存在的唯一索引，一批与之相悖的重复数据，以及一个在操作顺序上犯下的致命错误——它让整个应用瘫痪了 36 分钟。

本文先讲这段惨痛经历，然后远远超出这个故事本身——介绍那些本可以避免这场故障、让每一分钟停机都不发生的现代工具与技术。

---

问题：微信平台的批量用户同步

我们运营着一套微信公众号管理系统。user_info 表保存着所有托管账号下每一位关注者的资料数据——1500 万行，并且还在持续增长。核心操作是用户同步：从微信 API 拉取关注者数据，并保持数据库同步。

最初的同步逻辑简单粗暴到令人心痛：

For each user:
  1. Call WeChat API to get user info        (~200ms)
  2. SELECT * FROM user_info WHERE openid = ? (~1ms)
  3. If exists → UPDATE. If not → INSERT.     (~1ms)

对于一个拥有 30 万关注者的公众号，这意味着 30 万次串行 API 调用外加 60 万次数据库查询。总耗时：大约 14 小时。完全没法用。

优化方向显而易见：使用微信的批量 API（每次调用 100 个用户）以及 MySQL 的批量 upsert：

INSERT INTO user_info (openid, nickname, avatar_url, subscribe_time)
VALUES
  ('oX1...abc', 'Alice', 'https://...', '2023-06-01'),
  ('oX1...def', 'Bob',   'https://...', '2023-06-15'),
  ('oX1...ghi', 'Carol', 'https://...', '2023-07-20')
ON DUPLICATE KEY UPDATE
  nickname = VALUES(nickname),
  avatar_url = VALUES(avatar_url),
  subscribe_time = VALUES(subscribe_time);

这个模式——INSERT ... ON DUPLICATE KEY UPDATE——就是 MySQL 原生的 upsert。它在一条原子语句里插入新行、更新已有行。但有一个关键前提：表必须在用于判定重复的列上有唯一索引（或主键）。

我们的 user_info 表在 openid 上只有一个普通（非唯一）索引。这对 SELECT 查询没问题，但对 ON DUPLICATE KEY UPDATE 毫无用处——后者需要 UNIQUE 或 PRIMARY KEY 约束来确定该更新哪一行。

我们需要把那个普通索引改造成唯一索引。在一张 1500 万行的生产表上。而且表里已经有重复数据了。

---

为什么给大表加唯一索引令人胆寒

给小表加索引轻而易举。给一张几千万行的表加唯一索引则是完全不同的操作，而且它可能以多种方式灾难性地出错。

锁的问题

在 MySQL 5.6 及更早版本中，ALTER TABLE ... ADD INDEX 默认使用 COPY 算法：它会创建整张表的一份带新索引的副本，然后再切换过去。在这个过程中，表会被锁定写入，直到操作结束。在一张 1500 万行的表上，这可能意味着 30 到 60 分钟完全无法写入。

MySQL 5.6+ 引入了带 ALGORITHM=INPLACE 的 Online DDL，这好得多——但即便是 inplace 操作，也会在开始和结束时各获取一次短暂的元数据锁，而且仍然会消耗大量服务器资源。

排序缓冲区与临时空间

构建一个唯一索引需要 MySQL：

1. 读取表中每一行
2. 提取被索引列的值
3. 对它们排序（用于检测重复并构建 B 树）
4. 将索引页写入磁盘

对于 1500 万行，这个排序操作可能消耗数 GB 的临时磁盘空间，并跑满 CPU 核心。在我们那台 8 核、16GB 内存、300GB SSD 的服务器上，仅排序阶段就是一个显著的瓶颈。

唯一性校验

与普通索引不同，唯一索引必须验证没有任何两行共享相同的值。如果 MySQL 在 ALTER TABLE 过程中发现重复，整个操作会失败并回滚——而这可能已经跑了 30 多分钟。你必须在执行 ALTER 之前清理好数据。

业务影响

在 ALTER 运行期间，每一条触及该表的查询都要争抢资源。即便使用 Online DDL，读查询也会变慢，因为服务器正忙于排序和写入索引页。写密集型负载则受影响更甚。

---

现代做法（我们本该采用的）

这次事故发生在 2017 年。如今，应对这类场景有远为出色的工具。如果你正面临类似情况，不要不加考虑就在一张大生产表上直接跑 ALTER TABLE，先掂量一下这些替代方案。

pt-online-schema-change（Percona Toolkit）

Percona 的 pt-online-schema-change（pt-osc）是 MySQL 上零停机变更表结构的黄金标准。它在拥有数十亿行表的公司里经受过实战考验。

它的工作方式如下：

1. 创建影子表——一张原表结构的空副本：CREATE TABLE _user_info_new LIKE user_info
2. 修改影子表——在空的影子表上应用结构变更（瞬间完成，因为它没有数据）：ALTER TABLE _user_info_new ADD UNIQUE INDEX openid_u_index(openid)
3. 给原表添加触发器——INSERT、UPDATE、DELETE 触发器，把所有变更实时回放到影子表
4. 分块复制行——把数据从原表小批次地复制到影子表（默认：1000 行/批）。在两批之间，pt-osc 会检查复制延迟和服务器负载，如果服务器压力过大就自我限流
5. 原子重命名——所有行复制完成后：RENAME TABLE user_info TO _user_info_old, _user_info_new TO user_info
6. 删除旧表和触发器

关键洞见：影子表是在空的时候被修改的（瞬间完成），而重命名是原子的（一个仅涉及元数据、耗时毫秒级的操作）。应用永远不会遇到没有有效 user_info 表的那一刻。

pt-online-schema-change \
  --alter "ADD UNIQUE INDEX openid_u_index(openid)" \
  --user=dba --ask-pass \
  --chunk-size=1000 \
  --max-lag=1s \
  --check-interval=5 \
  --critical-load="Threads_running=100" \
  --set-vars="innodb_lock_wait_timeout=2" \
  D=mydb,t=user_info \
  --execute

对我们这个场景的提醒： pt-osc 会在复制阶段发现重复数据，并以重复键错误告终。我们仍然需要先去重。但 ALTER 本身是不会阻塞的。

gh-ost（GitHub Online Schema Transmogrifier）

GitHub 的 gh-ost 采取了不同思路：它不用触发器，而是读取 MySQL 二进制日志来捕获变更。

gh-ost \
  --alter="ADD UNIQUE INDEX openid_u_index(openid)" \
  --database=mydb \
  --table=user_info \
  --user=dba --ask-pass \
  --chunk-size=1000 \
  --max-lag-millis=1500 \
  --throttle-query="SELECT GREATEST(0, COUNT(*)-100) FROM information_schema.processlist WHERE command='Query'" \
  --initially-drop-ghost-table \
  --execute

相比 pt-osc 的关键优势：

• 无触发器——触发器在写密集型表上可能引发性能问题，而且某些 MySQL 配置会限制触发器的使用
• 可暂停——你随时可以通过一个 Unix socket 暂停和恢复迁移：echo throttle | nc -U /tmp/gh-ost.mydb.user_info.sock
• 可测试——用 --test-on-replica 先在从库上完整跑一遍迁移，验证结果无误后再在主库执行
• 可观测——丰富的状态输出，实时展示进度、预计完成时间和服务器负载

MySQL 8.0+ 的 Online DDL（ALGORITHM=INPLACE）

MySQL 8.0 大幅改进了原生 Online DDL 能力。对于二级索引（包括唯一索引），ALGORITHM=INPLACE 现在已是默认：

ALTER TABLE user_info
  ADD UNIQUE INDEX openid_u_index(openid),
  ALGORITHM=INPLACE,
  LOCK=NONE;

使用 ALGORITHM=INPLACE, LOCK=NONE 时：

• 整个操作期间，表始终可读可写
• 不会创建表副本——索引就地构建
• 只在最开始和最末尾各获取一次短暂的元数据锁

这比旧的 COPY 算法要快得多，干扰也小得多。不过有几点重要提醒：

• 它仍会消耗大量 I/O 与 CPU——服务器必须读完全部 1500 万行才能构建索引
• 它无法暂停——与 gh-ost 不同，inplace ALTER 一旦启动，你要么等它跑完，要么杀掉它（丢失全部进度）
• 它仍会校验唯一性——重复数据会导致整个操作失败

何时选用哪种方案

场景	推荐工具
MySQL 8.0+、写流量较低、可容忍一定性能下降	原生 ALGORITHM=INPLACE
MySQL 5.6/5.7，或写流量高，或需要零停机保证	pt-online-schema-change
写密集型表，触发器会带来麻烦	gh-ost
无法安装外部工具的托管数据库（RDS、Aurora）	原生 Online DDL（往往是唯一选择）
需要先在从库上测试迁移	带 --test-on-replica 的 gh-ost

---

去重的挑战

在添加唯一索引之前，你必须确保不存在重复值。这听起来简单，实则布满了微妙的陷阱。

找出重复

-- How many duplicate openid values exist?
SELECT openid, COUNT(*) AS cnt
FROM user_info
GROUP BY openid
HAVING cnt > 1
ORDER BY cnt DESC
LIMIT 20;

在我们的表上，这条查询耗时约 45 秒（带排序的全表扫描）。我们发现了几百个 openid 值各自有 2 到 5 条重复行，另外还有相当数量的行 openid 为 NULL。

决定保留哪些行

这是最难的部分。对每一组重复，你都需要一条确定性的规则：

策略 1：保留最新的行（最常见）

-- Delete all but the most recently updated row for each duplicate openid
DELETE u1 FROM user_info u1
INNER JOIN user_info u2
  ON u1.openid = u2.openid
  AND u1.id < u2.id
WHERE u1.openid IN (
  SELECT openid FROM (
    SELECT openid FROM user_info
    GROUP BY openid HAVING COUNT(*) > 1
  ) AS dupes
);

策略 2：保留数据最完整的行

-- Score each row by data completeness, keep the highest-scored
DELETE u1 FROM user_info u1
INNER JOIN (
  SELECT openid, MAX(id) AS keep_id FROM (
    SELECT id, openid,
      (CASE WHEN nickname IS NOT NULL THEN 1 ELSE 0 END +
       CASE WHEN avatar_url IS NOT NULL THEN 1 ELSE 0 END +
       CASE WHEN city IS NOT NULL THEN 1 ELSE 0 END) AS completeness,
      ROW_NUMBER() OVER (PARTITION BY openid ORDER BY
        (CASE WHEN nickname IS NOT NULL THEN 1 ELSE 0 END +
         CASE WHEN avatar_url IS NOT NULL THEN 1 ELSE 0 END +
         CASE WHEN city IS NOT NULL THEN 1 ELSE 0 END) DESC, id DESC
      ) AS rn
    FROM user_info
    WHERE openid IN (SELECT openid FROM user_info GROUP BY openid HAVING COUNT(*) > 1)
  ) ranked WHERE rn = 1
  GROUP BY openid
) keepers ON u1.openid = keepers.openid AND u1.id != keepers.keep_id
WHERE u1.openid IN (SELECT openid FROM (SELECT openid FROM user_info GROUP BY openid HAVING COUNT(*) > 1) d);

策略 3：去重前先合并记录——如果不同的重复行各自填有不同的非 NULL 字段，就在删除其余行之前，把它们合并进你要保留的那一行。

处理 NULL 值

NULL 值值得特别注意。在 MySQL 中，唯一索引允许多个 NULL 值——两行 openid = NULL 并不违反唯一约束（因为在 SQL 里 NULL != NULL）。不过，一张用户表里出现 NULL 的 openid，几乎可以肯定是数据质量问题。

-- How many NULL openids do we have?
SELECT COUNT(*) FROM user_info WHERE openid IS NULL;

-- Delete them (or move to a quarantine table first)
DELETE FROM user_info WHERE openid IS NULL;

稳妥的做法：去重前先备份

我们在删除重复行之前，先为它们建了一张备份表。事实证明这至关重要：

-- Create a backup table with the same structure
CREATE TABLE user_info_duplicate LIKE user_info;

-- Copy all rows that have duplicates
INSERT INTO user_info_duplicate
SELECT * FROM user_info
WHERE openid IN (
  SELECT openid FROM (
    SELECT openid FROM user_info
    GROUP BY openid HAVING COUNT(*) > 1
  ) AS dupes
);

这张备份表成了我们的安全网。唯一索引建好之后，我们可以用 INSERT IGNORE 恢复被删除的行——IGNORE 关键字会静默跳过任何会违反唯一约束的行。

---

事故实录：我们是如何搞垮应用的

准备工作做完后，我们有了一个计划：

1. 把重复行备份到 user_info_duplicate
2. 从 user_info 中删除重复行
3. 删除 openid 为 NULL 的行
4. 删除 openid 上旧的普通索引
5. 在 openid 上创建新的唯一索引
6. 用 INSERT IGNORE 恢复去重后的备份数据

第 1 到 3 步都很顺利。然后我们犯下了那个致命错误。

错误

我们在第 5 步之前，就执行了第 4 步——删除旧的普通索引：

DROP INDEX openid_index ON user_info;
-- At this moment, user_info has NO index on openid
-- Every query filtering by openid now does a full table scan of 15M rows

接着我们启动了第 5 步：

ALTER TABLE user_info ADD UNIQUE openid_u_index(openid);
-- This took 36 minutes and 13 seconds

在那 36 分钟里，user_info 表的 openid 列上没有任何索引。应用中每一条按 openid 查找用户的查询——几乎就是全部查询——都从亚毫秒级的索引查找，变成了对 1500 万行的全表扫描。

影响立竿见影，而且极为严重：

• 查询响应时间从约 1 毫秒飙升到 30 多秒
• 应用的连接池在几秒内被打满
• API 端点开始超时
• 微信回调 URL 停止响应，这意味着微信不再向我们推送事件通知
• 各个监控渠道的错误告警接连炸开

我们没法杀掉这个 ALTER TABLE，否则会丢失全部进度、被迫从头再来。我们也没法把旧索引加回去，因为 ALTER 在表上持有一把元数据锁。我们被卡住了，只能眼睁睁看着进度计数器一点点往前爬，煎熬了整整 36 分钟。

Query OK, 0 rows affected (36 min 13.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

我们本该怎么做

正确的顺序是先创建唯一索引，此时旧的普通索引仍然在位：

-- Step 1: Create the unique index (old index still active, queries still fast)
ALTER TABLE user_info ADD UNIQUE openid_u_index(openid);
-- 36 minutes, but the old openid_index is still serving queries

-- Step 2: Verify the new index works
SHOW INDEX FROM user_info;
EXPLAIN SELECT * FROM user_info WHERE openid = 'oX1...abc';
-- Confirm the optimizer is using openid_u_index

-- Step 3: NOW drop the old index (the unique index has taken over)
DROP INDEX openid_index ON user_info;

按这个顺序，表上始终至少有一个可用的 openid 索引。ALTER TABLE 仍然要跑 36 分钟，但整个过程中查询继续使用旧索引。没有故障，没有超时，没有惊慌失措的 Slack 消息。

---

分步安全操作流程

下面是给一张存在重复数据的大生产表添加唯一索引的完整、正确流程。请严格按此顺序执行。

阶段 1：准备（非高峰时段、低流量）

-- 1. Create a backup table for duplicate rows
CREATE TABLE user_info_duplicate LIKE user_info;

-- 2. Identify and backup duplicate data
INSERT INTO user_info_duplicate
SELECT * FROM user_info
WHERE openid IN (
  SELECT openid FROM (
    SELECT openid FROM user_info
    GROUP BY openid HAVING COUNT(*) > 1
  ) AS dupes
);

-- 3. Verify the backup
SELECT COUNT(*) FROM user_info_duplicate;
-- Should match the total number of rows involved in duplicates

阶段 2：去重

-- 4. Delete duplicate rows (keep the one with the highest ID)
DELETE u1 FROM user_info u1
INNER JOIN user_info u2
  ON u1.openid = u2.openid AND u1.id < u2.id;

-- 5. Delete NULL openid rows
DELETE FROM user_info WHERE openid IS NULL;
DELETE FROM user_info_duplicate WHERE openid IS NULL;

-- 6. Verify no duplicates remain
SELECT openid, COUNT(*) AS cnt
FROM user_info
GROUP BY openid
HAVING cnt > 1;
-- Should return 0 rows

阶段 3：创建索引（关键环节）

-- 7. Create the unique index while the old regular index is STILL IN PLACE
ALTER TABLE user_info ADD UNIQUE openid_u_index(openid);
-- This will take a long time. The old index keeps queries fast.

-- 8. Verify the new index
SHOW INDEX FROM user_info WHERE Key_name = 'openid_u_index';
EXPLAIN SELECT * FROM user_info WHERE openid = 'oX1...test';

-- 9. Only NOW drop the old regular index
ALTER TABLE user_info DROP INDEX openid_index;

阶段 4：数据恢复

-- 10. Restore backup data (INSERT IGNORE skips duplicates)
INSERT IGNORE INTO user_info SELECT * FROM user_info_duplicate;

-- 11. Verify row counts make sense
SELECT COUNT(*) FROM user_info;

-- 12. Clean up (after a few days, once you are confident)
-- DROP TABLE user_info_duplicate;

回滚方案

如果在阶段 3 出了任何问题：

-- If the ALTER fails (duplicate found that we missed):
-- The old index is still there, no harm done.
-- Find the remaining duplicates and fix them:
SELECT openid, COUNT(*) FROM user_info GROUP BY openid HAVING COUNT(*) > 1;

-- If you need to abort and restore all data:
INSERT IGNORE INTO user_info SELECT * FROM user_info_duplicate;

---

DDL 操作期间的监控

在跑一个耗时很长的 ALTER TABLE 时，你需要看清正在发生什么。下面是几条必备的监控命令。

观察 ALTER 进度

-- MySQL 8.0+: Monitor ALTER TABLE progress via performance_schema
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
  ROUND(WORK_COMPLETED / WORK_ESTIMATED * 100, 1) AS pct_complete
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

监控活跃查询

-- Check for blocked queries
SHOW PROCESSLIST;

-- More detailed view (MySQL 5.7+)
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Find queries waiting on metadata locks
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

追踪服务器负载

-- InnoDB status (buffer pool, I/O, locks)
SHOW ENGINE INNODB STATUS\G

-- Key metrics to watch during ALTER
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

迁移后检查索引使用情况

-- MySQL 8.0+: Find unused indexes (check after a few days)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb'
  AND object_name = 'user_info';

-- Verify query plans use the new index
EXPLAIN FORMAT=JSON
SELECT * FROM user_info WHERE openid = 'oX1...abc';

设置告警

在启动 ALTER 之前，先把监控阈值配好：

# Watch for slow queries during the migration
tail -f /var/log/mysql/slow-query.log

# Monitor server load (should stay below 80% CPU)
mysqladmin -u root -p extended-status --sleep=5 | grep -E "Threads_running|Slow_queries"

---

事后复盘：这一切值得吗？

在唯一索引就位、应用代码改为使用批量 upsert 之后，数据自己说明了一切：

指标	之前	之后
同步方式	串行（每次 API 调用 1 个用户）	批量（每次 API 调用 100 个用户）
数据库操作	SELECT + 条件式 INSERT/UPDATE	INSERT ... ON DUPLICATE KEY UPDATE
30 万用户耗时	约 14 小时	约 1 小时
单用户成本	约 300ms（API 调用 + 2 次 DB 查询）	约 1ms（批处理）
速度提升	基准	约快 14 倍

每批 100 个用户端到端大约耗时 1 秒：微信 API 调用约 500ms，批量数据库 upsert 约 500ms。同步从一个通宵任务，变成了午休时间就能跑完的活儿。

---

经验教训

在替代品到位之前，永远不要移除安全网。 这适用于索引、负载均衡器、功能开关，以及生产环境中的一切。旧索引就是我们的安全网。我们在唯一索引尚未就绪时就把它移除了，应用随之崩溃。

操作的顺序比操作本身更重要。 我们计划里的每一个单独步骤都是对的。计划失败在于顺序，而非内容。

先估算，再乘以 3。 我们的 DBA 估计”几分钟”。实际跑了 36 分钟。永远按最坏情况规划，尤其是那些无法暂停、无法回滚的操作。

用对工具。 2017 年，我们在一张线上生产表上直接跑了 ALTER TABLE。如今，pt-online-schema-change 和 gh-ost 就是专门为解决这个问题而生的。用它们吧。它们免费、久经实战考验，能让你免于经历我们那样的故障。

在添加唯一约束之前先去重。 如果 ALTER 跑到第 35 分钟因为一条残留的重复行而失败，你会损失掉所有这些时间。先跑去重查询，用 GROUP BY ... HAVING COUNT(*) > 1 验证无误，然后才启动 ALTER。

监控一切。 如果我们在删除旧索引时正盯着 SHOW PROCESSLIST，就会立刻看到全表扫描开始出现，从而能更快做出反应。请在开始之前就把监控配好，而不是等事情出错之后。

---

那次 36 分钟的故障是一段刻骨铭心的经历。它教给我们团队的生产数据库运维知识，比任何文档篇幅或大会演讲都要多。有时最好的教训恰恰来自最糟糕的错误。

如果你正准备给一张大表添加唯一索引——深吸一口气，反复核对操作顺序，并想一想 pt-osc 或 gh-ost 是否能让你免于以惨痛方式学到这一课。