联合索引与 EXPLAIN ANALYZE

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

EXPLAIN FORMAT=TREE
SELECT order_id, total_amount
FROM sales_order
WHERE customer_id = 1
  AND status = 'paid'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

CREATE INDEX idx_order_customer_status_created
  ON sales_order(customer_id, status, created_at DESC, order_id DESC);

EXPLAIN ANALYZE
SELECT order_id, total_amount
FROM sales_order
WHERE customer_id = 1
  AND status = 'paid'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

ALTER TABLE sales_order
  ALTER INDEX idx_order_customer_status_created INVISIBLE;

实验结束可将索引改回 VISIBLE 或删除。

机制解释

InnoDB 聚簇索引叶子页保存整行。二级索引按索引列排列并携带主键；查询其他列可能回表。联合索引顺序要同时服务等值条件、范围、排序和选择列，不能只按单列选择性排序。

EXPLAIN 的估算行数来自统计信息；EXPLAIN ANALYZE 提供执行后的实际循环、行数和时间。两者差距大时，应先检查数据分布、谓词相关性和统计信息。

不可见索引让优化器默认忽略索引但继续维护它，适合评估删除风险；它仍消耗写入和空间。

真实边界

• 小表全扫可能比走索引合理。
• 低选择性索引并非永远无效，联合索引和覆盖场景仍可能有价值。
• 索引增加 DML、页分裂、buffer pool 和备份成本。
• 示例数据太少不能证明生产计划，应使用脱敏后的分布或合成等价分布。

练习

1. 比较只索引 customer_id 与联合索引的计划。
2. 将查询改成 SELECT *，观察覆盖性和回表差异。
3. 让索引不可见后验证计划，再说明安全删除还需要哪些证据。

验收清单

• 保存建索引前后 EXPLAIN ANALYZE 的实际 rows、loops 和时间。
• 能解释联合索引列序如何服务过滤与排序。
• 索引删除方案包含不可见观察期、写入成本和回滚。

完成后核对答案

深度加餐：索引是写入成本换读取证据

先写查询谓词、排序和返回列，再设计索引；不要把每个字段都单独建一棵树。

先做一个可观察实验

CREATE INDEX idx_orders_status_time
  ON orders(status, placed_at DESC, id DESC);

EXPLAIN ANALYZE
SELECT id, order_no, placed_at
FROM orders
WHERE status = 'PENDING'
ORDER BY placed_at DESC, id DESC
LIMIT 20;

预期：计划应显示使用联合索引并减少回表/排序；如果 rows 仍很大，检查选择性与统计信息而不是继续堆索引。

原理下潜与工程边界

B+Tree 索引按左前缀组织；覆盖索引可直接返回所需列；EXPLAIN 的 estimated rows 与 ANALYZE 的实际耗时/行数可以暴露估算偏差。

函数包裹列、隐式类型转换、前导通配符会让索引难以使用；过宽索引增加写放大与 buffer pool 压力；DESC 语法与实际计划要以版本为准。

反例排错路线

保存变更前后的 EXPLAIN JSON、rows_examined、p95 延迟和写入吞吐；用 invisible index 先做撤销演练。

练习加码（仍不提供答案）

加码：为“租户 + 状态 + 时间”查询设计索引，比较左前缀不同排列在三种数据分布下的计划。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）