第 10 章：索引设计实战

zjc 于 2026-01-10 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。索引设计不是把所有 WHERE 字段都建一遍，而是从查询路径、数据分布、写入成本和业务演进出发，选择一组能覆盖核心访问模式的索引。

10.1 索引设计原则

核心原则：

先明确高频 SQL，再设计索引；
联合索引优先于多个单列索引；
等值条件放前面，范围条件放后面；
尽量让排序和分组复用索引顺序；
高频轻查询考虑覆盖索引；
唯一性交给唯一索引，不靠代码约定；
控制索引总数；
为索引建立负责人和验证记录；
上线前后对比执行计划；
定期清理无效索引。

10.2 选择性评估

查看表规模：

SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'orders';

查看单列选择性：

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT user_id) AS distinct_user,
  COUNT(DISTINCT status) AS distinct_status,
  COUNT(DISTINCT merchant_id) AS distinct_merchant,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity,
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;

查看组合选择性：

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT user_id, status) AS distinct_user_status
FROM orders;

选择性的常见判断：

选择性	说明
接近 1	大部分值唯一，适合索引
中等	可能适合与其他列组合
很低	单独索引未必有效
严重倾斜	平均值误导，需要按热门值分析

状态列基数低，但 (status, created_at) 对处理任务可能有效，因为任务只需扫描少量待处理状态。

10.3 联合索引顺序

假设订单表有三个高频查询：

-- Q1
SELECT *
FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;

-- Q2
SELECT *
FROM orders
WHERE user_id = 1001
  AND status IN ('CREATED', 'PAID')
ORDER BY created_at DESC;

-- Q3
SELECT *
FROM orders
WHERE status = 'CREATED'
  AND created_at >= '2026-08-01'
ORDER BY created_at;

推荐索引：

KEY idx_user_created (user_id, created_at),
KEY idx_status_created (status, created_at)

不建议一开始就设计：

KEY idx_user_status_created (user_id, status, created_at)

原因是 Q1 不能使用该索引完成 ORDER BY created_at，因为中间隔了 status。

联合索引设计口诀：

固定过滤 -> 可选过滤 -> 范围过滤 -> 排序字段

真实业务要结合查询频率和结果集大小调整，不能机械套用。

10.4 覆盖索引设计

订单列表接口只展示：

order_no、user_id、status、total_amount、created_at

创建索引：

ALTER TABLE orders
  ADD INDEX idx_user_created_cover (
    user_id,
    created_at,
    order_no,
    status,
    total_amount
  );

查询：

SELECT
  id,
  order_no,
  status,
  total_amount,
  created_at
FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;

验证：

EXPLAIN
SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC;

如果 Extra 出现 Using index，说明无需回表。

覆盖索引代价：

索引变宽；
写入成本增加；
磁盘和内存占用增加；
索引列更新更频繁时收益下降。

适合核心高频接口，不适合盲目给所有查询加宽。

10.5 前缀索引

长字符串可以只索引前缀：

ALTER TABLE users
  ADD INDEX idx_nickname_prefix (nickname(20));

评估前缀选择性：

SELECT
  COUNT(DISTINCT LEFT(nickname, 10)) / COUNT(DISTINCT nickname) AS p10,
  COUNT(DISTINCT LEFT(nickname, 20)) / COUNT(DISTINCT nickname) AS p20,
  COUNT(DISTINCT LEFT(nickname, 30)) / COUNT(DISTINCT nickname) AS p30
FROM users;

限制：

不能用于 ORDER BY 的完整排序优化；
不能作为覆盖索引；
唯一前缀可能误判，唯一约束应使用完整列或业务散列；
前缀过短会导致大量回表。

长文本搜索应考虑全文索引、外部搜索系统或单独散列列。

10.6 唯一索引

唯一索引负责数据库层一致性：

ALTER TABLE users
  ADD UNIQUE KEY uk_mobile (mobile);

插入冲突：

INSERT INTO users (username, nickname, mobile)
VALUES ('dave', 'Dave', '13800000001');

报错 Duplicate entry 是数据库在保护业务约束。

MySQL 允许唯一索引中多个 NULL 值。业务上如果不允许多个空手机号，应把列定义为 NOT NULL，或使用默认值加唯一约束。

逻辑删除示例：

ALTER TABLE users
  DROP INDEX uk_username,
  ADD UNIQUE KEY uk_username_deleted (username, deleted);

该方案只能区分“未删”和“一次删除”，多次删除同值仍会冲突。更稳妥的是使用删除版本号：

ALTER TABLE users
  ADD COLUMN delete_version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  DROP INDEX uk_username_deleted,
  ADD UNIQUE KEY uk_username_delver (username, delete_version);

未删除行 delete_version=0，删除时更新为自增值。

10.7 函数索引与表达式索引

MySQL 8.0.13 支持函数索引：

ALTER TABLE users
  ADD INDEX idx_upper_username ((UPPER(username)));

查询必须使用相同表达式：

SELECT *
FROM users
WHERE UPPER(username) = 'ALICE';

也可以为 JSON 字段建立表达式索引：

ALTER TABLE orders
  ADD INDEX idx_extra_channel ((CAST(extra->>'$.channel' AS CHAR(32))));

原则：

优先改写 SQL 为普通索引可用的形式；
函数索引必须与应用表达式完全一致；
隐式转换和字符集差异会导致失效；
复杂表达式增加维护成本；
用注释记录用途。

10.8 不可见索引

MySQL 8.0 支持不可见索引：

ALTER TABLE orders
  ALTER INDEX idx_status_created INVISIBLE;

优化器会忽略不可见索引，但写入仍维护它。适合删除索引前的灰度验证：

将索引设为 INVISIBLE；
观察慢日志和业务指标；
确认无影响后 DROP；
如出现劣化，立即 VISIBLE 恢复。

恢复：

ALTER TABLE orders
  ALTER INDEX idx_status_created VISIBLE;

注意：

不可见索引仍占用空间和写入成本；
主键不能设为不可见；
需要监控确认没有强制使用该索引的 SQL；
灰度周期要覆盖业务高峰和周期任务。

10.9 冗余与重复索引

以下索引存在冗余：

KEY idx_user (user_id),
KEY idx_user_created (user_id, created_at)

通常 idx_user 可以删除，因为 idx_user_created 可支持按 user_id 前缀查询。

以下不是简单冗余：

KEY idx_user_created (user_id, created_at),
KEY idx_user_status (user_id, status)

两者支持不同排序和过滤路径，需要结合执行计划判断。

查找重复和未使用索引：

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  COUNT_READ,
  COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'shop'
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ ASC, COUNT_WRITE DESC;

判断索引是否可删除：

观察一个完整业务周期；
覆盖日常、周报、月报、活动、批处理；
搜索代码和配置中的 FORCE INDEX；
在预发或影子库验证；
先设为 INVISIBLE；
删除后保留恢复脚本。

10.10 在线创建索引

MySQL 8.0 InnoDB 支持 Online DDL 创建索引：

ALTER TABLE orders
  ADD INDEX idx_merchant_created (merchant_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

Online DDL 不等于零影响：

需要扫描原表；
需要临时磁盘空间；
记录增量变更；
占用 IO 和 CPU；
末尾可能短暂持有元数据锁；
长事务会阻塞 DDL；
主从延迟可能上升。

上线前检查长事务：

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

避免在长事务、大流量和备份任务重叠时执行。

10.11 索引设计案例

案例一：订单查询

原始 SQL：

SELECT *
FROM orders
WHERE merchant_id = 10
  AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC
LIMIT 20;

问题：

merchant_id 有索引但缺少时间排序；
SELECT * 无法覆盖；
回表后可能再排序。

索引：

ALTER TABLE orders
  ADD INDEX idx_merchant_created (merchant_id, created_at);

如果 IN 值较多，ORDER BY created_at 是否能完全避免排序取决于优化器执行方式，需用执行计划验证。

案例二：后台任务扫描

SELECT *
FROM orders
WHERE status = 'TIMEOUT_PENDING'
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
LIMIT 100;

索引：

ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at);

该索引虽然状态基数低，但待处理状态占比很小，扫描量极低，是典型有效设计。

案例三：多租户表

SELECT *
FROM tenant_documents
WHERE tenant_id = 100
  AND folder_id = 20
  AND deleted = 0
ORDER BY updated_at DESC
LIMIT 50;

索引：

ALTER TABLE tenant_documents
  ADD INDEX idx_tenant_folder_updated (
    tenant_id,
    folder_id,
    deleted,
    updated_at
  );

如果 deleted 只有 0/1 且必传，可以放在排序前。若多数查询不传 folder_id，应保留或设计另一条索引。

案例四：活动库存扣减

UPDATE activity_stock
SET stock = stock - 1
WHERE activity_id = 1001
  AND sku_id = 2002
  AND stock > 0;

索引：

ALTER TABLE activity_stock
  ADD UNIQUE KEY uk_activity_sku (activity_id, sku_id);

唯一索引精确定位行，避免扫描大量库存行并降低锁范围。

10.12 索引规范模板

命名：

类型	前缀
普通索引	`idx_`
唯一索引	`uk_`
全文索引	`ft_`
表达式索引	`idx_表达式含义_`

上线评审表：

项目	内容
SQL	原始 SQL 和参数分布
表规模	当前行数、月增量
问题	扫描行、耗时、临时表、排序
新索引	完整 DDL
预期	新执行计划和扫描行变化
写入影响	预估 DML 延迟变化
验证	功能、性能、复制、监控
回滚	删除或不可见索引 DDL

本章小结

索引设计要从真实查询出发。等值条件前置、范围条件后置、排序字段尽量复用索引顺序；高频接口可评估覆盖索引，唯一性必须由唯一索引保证。索引会增加写入、存储、内存和 DDL 成本，因此每个索引都应有明确用途、验证数据和下线机制。

思考题

为什么 (a,b) 索引通常可以让 (a) 查询使用，反之不行？
覆盖索引适合什么场景？有哪些代价？
低基数索引在什么情况下仍然有效？
不可见索引如何用于安全删除索引？
为你们系统的一条核心慢 SQL 设计索引，并写出上线验证清单。