前言

最近有位小伙伴去面大厂，被问到：“现在有一张千万级数据量的订单表，要给几个常用查询条件字段建索引，需要注意什么？”

他当场愣住了——平时在测试环境随手就ALTER TABLE ADD INDEX，从没想过在大表上操作会有那么多“坑”。

其实，面试官想考察的绝不仅仅是“索引的基本语法”，而是如何安全、高效地在大表上维护索引。

今天这篇文章专门跟大家一起聊聊这个话题，希望对你会有所帮助。

更多项目实战在Java突击队网：susan.net.cn/project

一、先上一句“心法口诀”

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

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

二、千万级大表和普通表不一样？

在大表（千万级）上建索引，有三个最直观的风险：

1. 锁表时间超长：InnoDB在MySQL 5.6+ 支持Online DDL（大多数操作不锁全表），但仍有短暂的独占元数据锁，且创建过程会消耗大量IO和CPU，拖垮业务。
2. 磁盘空间井喷：每加一个索引，相当于重建一张“影子表”，千万级数据可能占用几十GB临时空间，导致磁盘打满。
3. 写入性能骤降：每个新索引都会拖累INSERT、UPDATE、DELETE的速度。在大表上，这点尤其致命。

所以，大表索引的设计必须“精打细算”，不能盲目堆叠。

三、6 个必须注意的要点

3.1 原则一：“列要精”

选择高选择性的列。

什么叫选择性高？

就是COUNT(DISTINCT column) / COUNT(*)接近1。

比如用户ID、订单号都是好选择；

而性别、状态等只有两三个值的列，基本没有意义。

错误示例：给订单表的status字段（只有“已支付”“未支付”“已取消”）单独建索引。

ALTER TABLE orders ADD INDEX idx_status (status);

在千万级数据上，status='PAID'可能命中500万行，MySQL仍会扫描大量数据，还不如全表快。

索引反而浪费空间和写入成本。

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

-- 好的做法：status 作为二级过滤
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

例外：当status配合其他条件时，联合索引才可能被用到。

3.2 原则二：“序要对”

联合索引最左前缀法则。

经常有小伙伴问：“我建了 (a,b,c) 联合索引，为什么只查 b 不走索引？”

因为联合索引严格按照最左前缀原则工作。

示例：

ALTER TABLE orders ADD INDEX idx_createtime_user (create_time, user_id);

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

WHERE create_time > '2026-01-01'               -- ✅ 用到索引第一列
WHERE create_time > '2026-01-01' AND user_id = 123  -- ✅ 完全覆盖

而下面的查询用不到：

WHERE user_id = 123                            -- ❌ 跳过了第一列

所以，范围查询（><）的列要放在联合索引的最后，否则其后所有列都无法走索引。

-- 推荐：等值查询在前，范围查询在后
INDEX (user_id, create_time)
-- 不推荐
INDEX (create_time, user_id)

3.3 原则三：“成覆盖”

使用覆盖索引避免回表。

覆盖索引是指索引中已经包含了查询需要的所有字段，不需要再回表查聚簇索引，极大减少随机IO。

示例：

-- 原查询需要回表
SELECT user_id, status FROM orders WHERE user_id = 123;

如果索引只建了(user_id)，还需要通过主键回表取status。更优的方案：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

现在查询所需字段都在索引中，Extra列会显示Using index，性能提升几倍。

使用场景：高频查询的字段组合，且数据量较大时。

3.4 原则四：“忌重复”

避免多余和冗余索引。

重复索引：完全相同列组合的多个索引。

冗余索引：一个索引是另一个索引的前缀。例如有了(a,b)，又建(a)就是冗余。

检查方法：

SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db' AND table_name = 'orders';

示例：

-- 已经有一个索引
ALTER TABLE orders ADD INDEX idx_user (user_id);
-- 又加了这个，idx_user 就成了冗余
ALTER TABLE orders ADD INDEX idx_user_createtime (user_id, create_time);

冗余索引会浪费空间，建议删除前一个。

3.5 原则五：“锁要短”

用工具在线建索引。

传统的ALTER TABLE在大表上可能会长时间阻塞写入（虽然Online DDL支持，但仍有短暂锁风险，且消耗资源）。

推荐工具：pt-online-schema-change (Percona Toolkit) 或 gh-ost，它们通过创建影子表、触发器，实现几乎零锁表的索引变更。

使用示例（pt-osc）：

pt-online-schema-change --alter "ADD INDEX idx_user (user_id)" \
  D=test,t=orders --execute

原理：

graph LR A[原表 orders] -->|复制结构| B[影子表 _orders_new] B -->|加索引| B A -->|触发器同步增量| B A -->|最后rename替换| C[新表 orders_new]

优点：不阻塞业务读写，可以限流控制负载。
缺点：需要额外的磁盘空间，执行时间长（千万级可能需要几小时）。

生产强烈建议：永远不要在高峰期对大表直接ALTER，用专业工具或维护窗口。

3.6 原则六：“频监控”

定期分析索引使用情况。

大表索引不是建完就万事大吉，随着业务变化，有些索引可能变成“僵尸索引”，只消耗资源从不使用。

监控手段：

-- 查看索引使用统计（需开启 performance_schema）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema='your_db' AND object_name='orders';

如果某个索引的COUNT_READ长期为0，可以考虑删除。

另外，定期执行OPTIMIZE TABLE或重建索引可以消除索引碎片，提升效率。

更多项目实战在Java突击队网：susan.net.cn/project

四、一张图看懂大表建索引流程

五、大表索引的优缺点与适用场景

优点

• 大幅提升查询速度（尤其是高选择性列）
• 覆盖索引可避免回表，减少IO
• 合理设计能让千万级表的复杂查询从分钟级降到毫秒级

缺点

• 占用额外磁盘空间（一般占数据量的30%～100%）
• 降低写入（INSERT/UPDATE/DELETE）性能
• 管理复杂度高，DDL操作风险大

最佳适用场景

• 只读或读多写少的业务（如订单历史查询、报表）
• 查询条件稳定，有明确的高频过滤字段
• 核心高频查询可以使用覆盖索引

不适用场景

• 写入极频繁的日志表（如埋点数据）——宁可降低索引量
• 数据量极小（<10万行）——全表扫描也很快
• 绝大多数查询不走索引（例如没有where条件）

六、总结

面试官如果再问“大表建索引要注意什么”，你可以从容回答：

1. 先评估必要性：是否真的需要？能否通过分区、归档冷数据减少表大小？
2. 列选择：只在高选择性、高频查询的列上建索引，避免低选择性列单独索引。
3. 联合索引顺序：等值查询在前，范围查询在后；遵循最左前缀。
4. 覆盖索引：尽量让索引包含查询所需字段，避免回表。
5. 去冗余：用sys.schema_redundant_indexes检查并清理无用索引。
6. 安全变更：用pt-online-schema-change或gh-ost控制锁，避免业务抖动。
7. 持续监控：观察索引使用频率、碎片率，及时优化。

最后，送大家一句话：索引是把双刃剑，在大表上尤其锋利。

用得好，性能直冲云霄；用不好，磁盘爆满、写入崩溃。

每一次加索引，都应当像做手术一样，事先设计、事中安全、事后复盘。