设计索引时应遵循哪些原则？

结论：索引设计只有三个目标——让高频查询少扫行、让写入别被索引拖垮、让空间别被索引吃光。落到具体动作上就是：按查询建而不是按列建、联合索引列序匹配最左前缀、尽量覆盖、控制数量、优先高区分度的短列。

一、原则清单

二、联合索引的列顺序怎么排

查询：WHERE tenant_id = ? AND status = ? AND ctime > ? ORDER BY ctime

候选 A：(tenant_id, status, ctime)
  ├─ 等值列 tenant_id、status 在前
  ├─ 范围列 ctime 在后
  └─ ORDER BY ctime 可复用索引顺序，去掉 filesort   ✅

候选 B：(ctime, tenant_id, status)
  ├─ 范围列在最左
  └─ 只能用到 ctime，tenant_id 与 status 退化为过滤   ❌

排序列的三条经验：

1. 等值条件列放最左，范围条件列放最后。
2. 区分度高的列优先，但要让位于「等值/范围」这条更硬的规则。
3. 如果一条索引同时服务多个查询，优先满足最高频那条，其余靠覆盖或回表。

三、验证与度量

-- 1. 列的区分度：越接近 1 越适合单独建索引
SELECT
  COUNT(DISTINCT tenant_id) / COUNT(*) AS sel_tenant,
  COUNT(DISTINCT status)    / COUNT(*) AS sel_status,
  COUNT(DISTINCT ctime)     / COUNT(*) AS sel_ctime
FROM t_order;

-- 2. 建索引
ALTER TABLE t_order
  ADD KEY idx_tenant_status_ctime (tenant_id, status, ctime);

-- 3. 验证是否命中、是否覆盖
EXPLAIN SELECT id, ctime FROM t_order
WHERE tenant_id = 7 AND status = 1 AND ctime > '2026-01-01'
ORDER BY ctime
LIMIT 50;
-- 期望：key=idx_tenant_status_ctime，Extra 含 Using index，无 Using filesort

-- 4. 找出冗余索引（sys 库，需 5.7+）
SELECT * FROM sys.schema_redundant_indexes;

-- 5. 找出从未被使用的索引（依赖 performance_schema）
SELECT * FROM sys.schema_unused_indexes;

前缀索引的区分度测试：

SELECT
  COUNT(DISTINCT LEFT(title, 8))  / COUNT(*) AS sel_8,
  COUNT(DISTINCT LEFT(title, 16)) / COUNT(*) AS sel_16,
  COUNT(DISTINCT title)           / COUNT(*) AS sel_full
FROM t_article;
-- 选一个接近 sel_full 的最小前缀长度

四、数量与写放大的权衡

每增加一个索引，插入要维护一棵 B+Tree，更新要改所有含该列的索引，删除要打删除标记。经验值：写多读少的表索引控制在 3 个以内；读多写少的报表表可以放宽。索引不是免费的缓存，它是用写性能换读性能的契约。

五、几个容易踩的细节

• NULL 多的列：IS NULL 走索引，但区分度差；把「是否删除」这类标志位放进联合索引尾部更划算。
• 字符集不一致：utf8mb4 与 utf8 的列做 JOIN 会隐式转换，索引直接失效。
• 隐式类型转换：字符串列与数字比较同样失效，参数类型必须与列定义一致。
• 更新频繁的列：如 update_time，建索引会让每次更新多维护一棵树，慎建。
• 超长文本：TEXT 列只能建前缀索引，且无法覆盖；关键词检索交给全文索引或 ES。

常见追问

• 区分度低于多少就不该单独建索引？ 业内常以 5% 为参考线，但更准确的标准是「优化器用它能过滤掉多少行」，最终要靠 EXPLAIN 验证。
• 联合索引能替代多个单列索引吗？ 只能在最左前缀覆盖的范围内替代，(a, b) 不能服务只查 b 的语句。
• 索引一定越多越好吗？ 不是，写放大、空间占用、优化器选择成本都会上升。
• 为什么建议主键用自增？ 顺序写入让 B+Tree 页填充率高、分裂少，回表访问也集中在少数热页。
• 如何验证索引真的被用上？ EXPLAIN 的 key、rows_examined/rows_sent 比值、Handler_read% 三个指标一起看。
• 分区表上索引有什么不同？ 分区键必须进每个唯一索引，否则无法建唯一约束；跨分区查询会放大扫描。

小结

索引设计是「查询 → 列组合 → 顺序 → 覆盖 → 数量」的五步推导，而不是给每个 WHERE 列都挂一条索引。落地时用区分度 SQL 选列、用 EXPLAIN 验顺序、用 sys 库清冗余，最后接受「索引必然有写代价」这个前提。