如何在大表上高效建索引？

Zui近有位小伙伴去面试大厂，被问到：“现在有一张千万级数据量的订单表，要给几个常用查询条件字段建索引，需要注意什么？”他当场愣住了——平时在测试环境随手就ALTER TABLE ADD INDEX，从没想过在大表上操作会有那么多“坑”。

其实面试官想考察的绝不仅仅是“索引的基本语法”，而是如何安全、高效地在大表上维护索引。今天这篇文章专门跟大家一起聊聊这个话题，希望对你会有所帮助。

大表建索引的“六字诀”

“列要精，序要对，成覆盖，忌重复，锁要短，频监控”

下面我把这句口诀展开成6个模块，手把手教你在大表上玩转索引。

原则一：“列要精”——选择合适的列

在大表上建索引，有三个Zui直观的风险：耗时长、锁等待、资源消耗大。所以大表索引的设计必须“精打细算”，不Neng盲目堆叠。

选择高选择性的列。

什么叫选择性高？就是COUNT / COUNT接近1。比如用户ID、订单号dou是好选择；而性别、状态等只有两三个值的列，基本没有意义。

错误示例给订单表的status字段单独建索引。

ALTER TABLE orders ADD INDEX idx_status ;

在千万级数据上，status='PAID'可Neng命中500万行，MySQL仍会扫描大量数据，还不如全表快。索引反而浪费空间和写入成本。

正确Zuo法把低选择性的列放在联合索引的后面而不是单独索引。

-- 好的Zuo法：status 作为二级过滤
ALTER TABLE orders ADD INDEX idx_user_status ;

原则二：“序要对”——遵循Zui左前缀原则

联合索引Zui左前缀法则。

经常有小伙伴问：“我建了 联合索引，为什么只查 b 不走索引？”因为联合索引严格按照Zui左前缀原则工作。

示例

ALTER TABLE orders ADD INDEX idx_createtime_user ;

下面的查询Ke以用到索引：

WHERE create_time> '某个时间'               -- ✅ 用到索引第一列
WHERE create_time> '某个时间' AND user_id = '某个ID'   -- ✅ 完全覆盖

而下面的查询用不到

WHERE user_id = '某个ID'                             -- ❌ 跳过了第一列

范围查询（><, =, BETWEEN, LKE '%xxx')的列要放在联合索引的Zui后否则其后所有列dou无法走索引。

原则三：“成覆盖”——使用覆盖索引避免回表

MySQL 的查询优化器会优先使用覆盖索引，因为它不需要回表，减少了随机IO操作，大幅提升性Neng。

-- 原查询需要回表
SELECT user_id, status FROM orders WHERE user_id = '某个用户';
-- 优化后的索引
ALTER TABLE orders ADD INDEX idx_user_status ; 
-- 现在直接走覆盖索引，不再需要回表查聚簇索引获取 status 字段。
-- EXPLAIN 结果中 Extra 会显示 "Using index"，说明用上了覆盖索引。
SELECT user_id, status FROM orders WHERE user_id = '某个用户'; 

原则四：“忌重复”——避免多余和冗余索引

**重复索引**：完全相同列组合的多个索引。例如在同一张表上分别建了两次 `INDEX ` 。

**冗余索引**：一个索引是另一个的前缀。例如Yi经有了 `` ，又单独建了 `` ，后者就是冗余的，因为 `` Yi经Neng支持按 `user_id` 查询了。

**检查方法**：

-- MySQL 自带的 sys 库提供了检查冗余/重复 索引的功Neng 
SELECT * FROM sys.schema_redundant_indexes 
WHERE table_schema = 'your_db_name' AND table_name = 'your_table_name'; 

**定期巡检Ke以避免不必要的存储和维护开销。**

**清理冗余/重复 索引**：

**删除重复创建的相同 索引**

**删除被包含在复合 索引中的单列 索引**

**保留geng具通用性的 索引** —— 因为它既Neng支持 `WHERE a=?` 又Neng支持 `WHERE a=? AND b=?` 。

**例外情况**： * Ru果单列 `a` 的查询频率远高于组合条件 `a AND b` ，且需要单独为 `a` Zuogeng细粒度的统计或快速检索，那么保留单列 索引也是合理的。 * 即使有 `` ，为了减少一次 索引扫描 + 回表的代价，也可Neng需要额外维护一个极简的 `` 。但这种情况较少见，一般复合 索引足以应对大部分业务场景。

原则五：“锁要短”——利用专业工具在线变geng，避免长时间锁表影响业务

传统的 ALTER TABLE ... ADD INDEX 在 MySQL 5.6 后虽然支持 Online DDL，但仍有可Neng在初始阶段或Zui后的切换阶段短暂锁表，且在大表上执行耗时较长。

推荐使用专业的在线变geng工具，如： * pt-online-schema-change * gh-ost

它们通过创建影子表 + 触发器同步数据，实现几乎零停机的 索引添加/变geng 操作。

以 pt-online-schema-change 为例： bash pt-online-schema-change \ --host=your_host \ --user=your_user \ --password=your_password \ --alter "ADD INDEX idx_user_id " \ D=your_db,t=orders \ --execute

优点不阻塞业务写入，Ke以限流控制负载。

缺点需要额外磁盘空间 & 执行时间较长。

生产实践建议

非业务高峰期操作

先在从库执行，观察无异常后再主从切换

配置合理的限流参数，防止对线上造成太大压力

监控变geng过程中的负载、磁盘、主从延迟等关键指标

这样，即使是大表也Neng相对安全地添加/修改 索引。

原则六：“频监控”——定期分析/优化现有 索引的使用情况

监控 索引使用频率 sql SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_READ FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'your_table' ORDER BY COUNT_READ DESC;

Ru果某个 索引长期 COUNT_READ = 0 ，说明它可Neng是“僵尸 索引”，可考虑删除。

定期检查/消除 碎片 sql OPTIMIZE TABLE your_table;

ALTER TABLE your_table ENGINE=InnoDB;

通过持续监控与分析，我们Ke以及时发现并清理无效/低效 索引，进一步提升数据库整体性Neng。

答面试官版

Ru果面试官再问“大表 建 索引要注意什么”，你Ke以这样回答：

优先选择高选择性的 列，避免低区分度的 列单独建 索引；Ke以把它们放到复合 索引后面辅助过滤。

设计复合 索引时遵循Zui左前缀匹配原则，把等值查询的 列放在前面范围查询 列尽量靠后。

尽可Neng创建覆盖 索引，减少回表次数；EXPLAIN 结果中kan到 Using index 就表示走了覆盖 索引。

检查并清除冗余/重复 索引，利用 sys.schema_redundant_indexes 等工具辅助分析；避免不必要的存储与维护成本。

使用专业的 Online DDL 工具来执行大表的 索引变geng，避免长时间锁表影响业务；并Zuo好限流与监控。

**定期巡检现有 索 引的使用情况，对于长期未被使用的“僵尸 索 引”予以删除；同时通过 OPTIMIZE TABLE 或重建等方式消除 碎片，保持数据库健康运行状态。

这样一套组合拳下来相信Neng让面试官眼前一亮！

本文深入探讨了如何在千万级大表上高效、安全地创建和维护MySQL 索 引。通过遵循 “列要精，序要对，成覆盖，忌重复，锁要短，频监控"" 六大原则，并结合实际案例与Zui佳实践，为读者提供了一套系统化的大表索 引设计与优化指南。我们希望这些经验与技巧Neng够帮助你在实际工作中geng好地应对大表的索 引挑战，从而提升数据库性Neng与系统稳定性，让业务跑得geng快geng稳！

---