MySQL 索引设计有哪些原则？

简化版

MySQL 索引设计要围绕高频 SQL，而不是围绕字段本身。常见原则是：高频过滤列建索引，联合索引遵循最左前缀，等值列在前、范围和排序列在后，尽量覆盖查询，同时避免冗余索引和过宽索引。

详细版

常用索引设计原则：

为高频 where、join、order by、group by 建访问路径。
联合索引按真实查询条件排序，不是字段越多越好。
等值过滤列通常优先，范围列和排序列靠后。
区分度太低的单列索引价值有限，但组合后可能有价值。
能覆盖高频查询时，可以减少回表。
避免重复索引，如有 (a,b) 时很多场景不需要再建 (a)。
索引会增加写入、更新和存储成本，不能无限加。

例子：

where user_id = ?
  and status = ?
order by create_time desc
limit 20

可以优先考虑 (user_id, status, create_time)。

完整版教学

一、索引是访问路径，不是字段标签

很多人设计索引时会说“这个字段经常查，给它建个索引”。这还不够。数据库执行的是完整 SQL，索引应该服务一条访问路径：先过滤哪些数据，再按什么顺序读，是否需要回表。

同样是 create_time 字段，如果 SQL 是按用户查订单再按时间排序，索引可能是 (user_id, create_time)；如果 SQL 是按状态查最近订单，可能是 (status, create_time)。

设计索引时可以把 SQL 拆成四个问题：谁负责等值过滤，谁负责范围过滤，谁负责排序或分组，返回列是否需要回表。比如 where tenant_id=? and status=? order by create_time desc limit 20，索引不是给 tenant_id、status、create_time 各建一个就完事，而是要让一条联合索引形成连续路径。

访问路径：
tenant_id 等值定位租户
 -> status 等值缩小状态
 -> create_time 按顺序取最近 20 条
 -> 返回列少时尝试覆盖

二、联合索引顺序怎么排

联合索引顺序通常考虑四个因素：

等值条件：如 user_id = ?、status = ?；
范围条件：如 create_time between ? and ?；
排序分组：如 order by create_time desc；
区分度和过滤效果。

一般可以把等值过滤列放前面，范围和排序列放后面。这样前面的等值条件先缩小范围，后面的有序性还能服务范围扫描或排序。

例如订单查询：

select id, amount, create_time
from orders
where user_id = 1001
  and status = 'PAID'
  and create_time >= '2026-07-01'
order by create_time desc
limit 20;

(user_id,status,create_time) 通常比三个单列索引更贴合，因为它能先定位某个用户的某种状态，再沿时间范围扫描并满足排序。若建成 (create_time,user_id,status)，时间范围可能很大，后续用户和状态更多只是过滤，效果可能差很多。

SQL 特征	索引顺序倾向	原因
等值 + 排序	等值列在前，排序列在后	固定分组后利用后续有序性
等值 + 范围	等值列在前，范围列在后	先缩小范围，再扫描连续区间
JOIN	被驱动表关联列建索引	避免被驱动表反复全扫
覆盖查询	返回小字段可放后面	减少回表但不破坏访问路径

记忆钩子：联合索引的顺序先服务“怎么找到数据”，再服务“怎么少回表”，不要为了覆盖把访问路径排乱。

三、区分度不是唯一标准

高区分度列通常更适合做索引，但不能机械套用。比如 status 只有几个值，单独建索引可能价值不大；但在 (user_id, status, create_time) 中，status 可以进一步缩小某个用户下的订单范围，还能配合时间排序。

索引设计要看组合后的访问效果，而不是只看单列基数。

假设 status 只有 4 个值，单独 status='PAID' 可能命中 60% 数据，价值不高；但对某个用户来说，user_id=1001 有 1000 条订单，再加 status='PAID' 可能剩 300 条，最后按 create_time 取 20 条。低区分度列在联合索引中仍可能有价值，因为它在已经缩小的局部范围内继续过滤。

四、覆盖索引要控制宽度

覆盖索引能避免回表，但把很多列都塞进索引会带来成本：

索引页更大，缓存命中下降；
写入和更新维护更多索引；
变更字段时索引维护成本增加；
太多索引让优化器选择更复杂。

所以覆盖索引适合返回列少、访问频率高、性能收益明显的查询，比如列表页、排行榜、状态页。

例如首页订单列表只展示 id,status,amount,create_time，如果查询频率很高，可以评估 (user_id,status,create_time,id,amount) 这类覆盖索引。但如果返回列包括 remark 大文本、JSON 扩展字段，把它们也放进索引会让索引页变胖，减少单页可容纳记录数，写入维护也更重。覆盖索引的边界是“刚好覆盖高频轻量查询”，不是把表复制一份到索引里。

五、冗余索引要定期清理

如果已经有索引 (a, b)，再建 (a) 很多时候是冗余的，因为 (a, b) 可以按最左前缀服务 a 条件。冗余索引会浪费空间，拖慢写入，还可能干扰优化器选择。

但反过来不成立：有 (a) 不代表可以替代 (a, b)，因为后者能继续利用 b 的有序性。

冗余索引的成本常被低估。假设一张表有 5 个二级索引，每秒写入 2000 行，每次插入都要维护 5 棵 B+ 树；如果其中 2 个索引长期不用或被更长左前缀覆盖，就等于每秒做大量无意义维护。清理前要结合线上索引使用情况、慢日志和回滚方案，不能凭名字删除。

六、设计索引要会验证

索引设计不是写完 DDL 就结束。上线前后要用 EXPLAIN 看 key 是否命中、rows 是否下降、Extra 是否减少 filesort/temporary/回表，再用接近线上数据量验证真实耗时。小数据集下所有索引看起来都快，数据量上来后访问路径差异才会暴露。

验证闭环：
梳理高频 SQL -> 设计联合索引 -> EXPLAIN 验证计划
             -> 压测/灰度观察耗时 -> 清理冗余索引

七、常见误区与追问

误区：哪个字段经常查就给哪个字段建单列索引。 MySQL 执行的是完整 SQL，索引应服务过滤、排序、分组和返回列组成的访问路径。
误区：区分度最高的列永远放联合索引第一。 区分度要结合等值、范围、排序和高频查询模式判断，不能脱离 SQL。
误区：索引越多查询越快。 索引会增加写入、更新、删除和存储成本，过多索引还可能干扰优化器选择。
误区：覆盖索引应该覆盖所有列。 覆盖索引适合高频、返回列少且稳定的查询，过宽索引会拖累写入和缓存。
追问：有 (a,b,c) 还需要 (a) 吗？ 多数 where a=? 场景可被 (a,b,c) 左前缀覆盖，但是否保留要看索引宽度、覆盖需求和优化器实际选择。
追问：索引设计上线后怎么确认有效？ 用 Explain、慢日志和真实数据量验证扫描行数、排序、回表和耗时是否下降，并持续观察写入成本。

八、加强记忆

索引设计的核心不是给字段贴标签，而是为高频 SQL 设计访问路径。先看等值过滤如何缩小范围，再看范围、排序、分组能否继续利用有序性，最后才考虑是否用覆盖索引减少回表。收益来自少扫描、少排序、少回表，代价是更多存储和写入维护；所以索引要设计、验证、观察、清理，不能只加不管。

简化版

详细版