面经策

MySQL 数据库一亿条数据，怎么快速加索引？ ​

  • 标签：后端、场景题

🏛 业务架构师 ​

一亿条数据的表加索引，核心是Online DDL + 变更管理。我先讲技术方案，再讲大表 DDL 的纪律——这个场景是变更事故的高发区，业务视角的重点在后半段。

技术方案

MySQL 8.0（首选体验）

sql
ALTER TABLE big_table ADD INDEX idx_create_time(create_time),
ALGORITHM=INPLACE, LOCK=NONE;
  • 加索引走 INPLACE：允许并发读写，在线完成
  • 期间消耗：额外磁盘（排序中间文件 + 索引文件）、IO/CPU 资源、row log 回放

MySQL 5.7 或需要更强控制力：gh-ost / pt-osc

  • gh-ost：binlog 回放建影子表，支持动态限速（--max-load 按复制延迟自适应暂停）、可随时终止——亿级表生杀大权在握
  • 我选 gh-ost 而不是原生 DDL 的理由：大表 DDL 中途发现影响业务，原生 DDL 只能干等，gh-ost 能 throttle 甚至放弃

执行纪律（事故预防的关键）

  1. 演练计时：先在从库跑一遍，拿到真实耗时（1 亿行约 30 分钟~2 小时，取决于硬件），安排在凌晨窗口
  2. 磁盘检查：索引构建需要约等于索引大小的额外空间，磁盘不足直接失败
  3. 主从延迟监控：DDL 在主库执行完，从库回放会产生延迟——延迟期间读写分离的读会读到旧结构，业务侧要评估影响
  4. 长事务检查：information_schema.innodb_trx 先清长事务，否则 MDL 锁等待阻塞后续所有查询（表级读写全卡，经典事故）
  5. 大促冻结期：高峰前一周冻结 DDL 变更

业务收尾：一亿行加索引本质是容量管理问题——索引加速读但拖慢写、占空间。加之前先确认优化器真的会用它（查询模式匹配最左前缀），别为"感觉需要"加索引：每个多余索引都在给每笔写入放血。

🏗 系统架构师 ​

一亿行数据加索引，核心矛盾是DDL 期间的锁、IO 冲击与主从延迟。我按版本与工具分层给方案。

1. MySQL 8.0 原生 ONLINE DDL（常规首选） 8.0 加二级索引走 INPLACE，LOCK=NONE 允许并发 DML：

sql
ALTER TABLE big_table ADD INDEX idx_create_time(create_time),
  ALGORITHM=INPLACE, LOCK=NONE;

但一亿行 INPLACE 也要 10 分钟~1 小时（取决于 IO 与 buffer pool），期间风险点：

  • 主从延迟：从库单线程回放 DDL 造成延迟放大——低峰执行 + 监控延迟
  • online DDL 日志：构建期间增量 DML 记录在日志里最后应用，写入密集时日志膨胀
  • 长事务阻塞：DDL 开始要拿 metadata lock，任何长事务/慢查询没结束就卡住，后面的请求全堵——执行前查 information_schema.innodb_trx 清理长事务

2. gh-ost / pt-osc（生产规范级） 大表（亿级）或高峰期，我规范走 gh-ost：

  • 原理：建影子表 → 全量按主键分批拷贝（chunk 复制）→ binlog 异步回放增量 → RENAME TABLE 原子切换
  • 关键优势：可限速（--max-load Threads_running=50 --critical-load 超阈值自动节流甚至暂停）、可随时终止（进度存盘）、binlog 回放（不占触发器，pt-osc 用触发器有侵入）
  • 切换瞬间需要短暂的 metadata lock，同样要避开长事务

3. 一致性校验与收尾

  • 切换后 ANALYZE TABLE 更新统计信息，执行计划立刻正确
  • 对比新旧表行数、抽样 checksum
  • 从库先做的"灰度式"变体：先在从库执行（或不切流量），确认无误后倒换——适合超保守场景

4. 如果是主键/唯一索引变更：更重，必须 gh-ost 且评估回滚窗口（旧表保留时间）。

流程红线：预生产演练、低峰执行、限速参数就位、监控延迟与 threads_running、DBA 双人在岗。亿级表的 DDL 从来不是一条 SQL 的事，是一次小型变更工程。

💻 全栈开发专家 ​

一亿行加索引，MySQL 8.0 直接 ALTER TABLE ADD INDEX 是 Online DDL（INPLACE），不锁 DML 但构建慢、主从延迟大。生产上我按表的大小和写入压力选方案。

方案一：gh-ost（大表首选）

bash
gh-ost --host=master --database=shop --table=orders \
  --alter="ADD INDEX idx_user_created(user_id, created_at)" \
  --chunk-size=5000 --max-load=Threads_running=50 \
  --cut-over=default --allow-on-master

原理：建影子表拷贝数据 + 消费 binlog 回放增量，完成后原子 cut-over。优点是可暂停、可动态限流、随时终止不留半成品，主从延迟可控；要求 binlog 格式为 ROW。

方案二：pt-online-schema-change

触发器方案，成熟稳定，但触发器写放大明显，高写入表慎用；gh-ost 不可用（无 binlog 权限）时兜底。

方案三：8.0 原生并行 DDL

8.0.27+ 用 innodb_ddl_threads=8、innodb_parallel_read_threads 加速索引构建，千万级中等表直接搞定，省一套工具链。注意加索引不支持 INSTANT（INSTANT 只覆盖加列等），显式写 ALGORITHM=INPLACE, LOCK=NONE 防止意外锁表。

方案四：主从轮转

从库逐个 DDL → 主从切换 → 原主库执行，全程业务无感；缺点是切换操作重，要处理 GTID。

必讲的坑

  • 磁盘预留 1.5 倍表空间（影子表 + 期间增量）。
  • 低峰执行，盯 Threads_running 和主从延迟，超阈值就节流。
  • 长事务会卡住 cut-over 的元数据锁，执行前查 information_schema.innodb_trx 清理。