人人都会AI编程

12.1 索引设计原则与最佳实践

更新时间：2026-07-10

索引是数据库性能优化中最重要也最容易被滥用的工具。一个好的索引能让查询从全表扫描变成瞬间返回，而一个糟糕的索引不仅浪费磁盘空间，还会拖慢写入性能。本节从实战角度出发，给出索引设计的核心原则和可以直接落地的实践建议。

12.1.1 索引设计的核心原则

设计索引时，有五个必须时刻记在脑子里的原则，它们直接决定了索引是“加速器”还是“累赘”。

原则一：只为高频查询服务，不为低频查询买单

索引不是免费的。每次对表进行 INSERT、UPDATE、DELETE 时，所有相关的索引都需要同步维护。一张表上有五六个索引，写入性能可能会下降数倍。因此在建索引之前，一定先问自己一个问题：这个查询的频率是否足够高，值得用写入性能的损失来换取读取性能的提升？

  • 每天只跑一次的报表查询，完全没必要专门建索引，靠夜间全表扫描即可。
  • 每秒钟都有上千次请求的用户登录查询，必须在用户名或手机号字段上建唯一索引。

原则就是：将有限的索引预算，投给最高频、最核心的查询。

原则二：高选择性的列优先，低选择性的列靠后

“选择性”是指列中不同值的数量与总行数的比值。选择性越高，索引过滤掉的无效数据就越多，查询效率就越高。比如：

  • 主键的选择性是 1（每个值都唯一），是完美的索引候选。
  • 性别字段只有“男”“女”两种值，选择性极低（约 2/总行数），单独建索引几乎没用，因为数据库一看过滤不掉多少数据，可能直接放弃索引全表扫描。

在联合索引中，应该把选择性高的列放在前面。比如 status 有 3 种值但 user_id 有几十万种值，联合索引应该建 (user_id, status) 而不是 (status, user_id)，这样第一级就能排除绝大多数无关行。

原则三：联合索引的列顺序取决于查询模式，而不是列的自身属性

很多人在设计联合索引时，会习惯性地把等值查询的列放在前面，范围查询的列放在后面。这个经验是对的，但更准确的描述是：索引的列顺序应该尽量匹配查询中 WHERE 子句的列顺序，并且让最左侧的列过滤掉最多的数据。

因为 B+ 树索引按照定义好的列顺序组织数据，对于联合索引 (A, B, C)：

  • 查询条件包含 A 能用索引
  • 查询条件包含 A 和 B 能用索引
  • 查询条件只包含 B 或只包含 C 不能有效利用索引（除非有索引条件下推等优化）
  • 如果 A 是范围查询，则 B 列的索引将无法继续有序使用

所以实际做法是：

  1. 列出所有会使用该索引的查询。
  2. 找出这些查询在 WHERE 子句中都包含的公共列，将它们作为索引的前缀。
  3. 对于剩下的列，优先把等值查询的列放在范围查询列的前面。
  4. 如果有排序需求（ORDER BY），也可以考虑让索引顺序与排序顺序一致，以消除文件排序。

原则四：尽量让索引“覆盖”查询，避免回表

如果一个索引包含了查询所需的所有列（包括 SELECT 后面的列和 WHERE 条件中的列），那么数据库引擎只需要扫描该索引即可返回结果，不需要再去聚簇索引中读取完整的行数据，这个过程叫覆盖索引。它消除了回表的随机 I/O，性能提升通常非常明显。

例如，有表 orders(id, user_id, order_time, amount)，经常有这个查询：

SELECT user_id, order_time FROM orders WHERE user_id = 123;

如果只建 idx_user_id (user_id)，那么通过二级索引找到主键后还需要回表取 order_time。
但如果建 idx_user_id_time (user_id, order_time)，那么 user_id 和 order_time 都在索引里，查询完全在索引上完成，无需回表，效率更高。

代价是索引会更大一些，需要权衡查询频率与空间占用。

原则五：索引数量要克制，冗余索引要清理

MySQL 一张表可以有多个索引，但每增加一个索引就会增加存储空间和写入消耗。同时，存在冗余索引时，优化器可能选择错误的执行计划，反而拖累性能。例如：

  • 已经有了联合索引 idx_ab (A, B)，再建一个单独的 idx_a (A) 就是冗余的，因为 idx_ab 的左前缀完全可以替代 idx_a 的功能。
  • 已有主键（聚簇索引），再建一个唯一索引 UNIQUE (id) 毫无必要。

定期检查 sys.schema_redundant_indexes 视图（MySQL 8.0）或通过 INFORMATION_SCHEMA 分析索引使用情况，将未使用或冗余的索引果断删除，保持索引集精简高效。

12.1.2 索引设计最佳实践清单

以下是可以直接参考落地的实践建议，覆盖了选列、建索引、验证的全流程。

1. 先设计查询，后设计索引

不要在项目开始时凭空“猜测”索引，而是等到核心业务 SQL 基本稳定后，再根据实际的查询模式设计索引。这样索引才能真正对准需求，避免建了一堆无用的索引。

2. 主键尽量使用自增整型或有序 UUID

InnoDB 的聚簇索引按照主键顺序物理存储数据。如果主键是乱序的（比如随机 UUID），插入时会导致大量的页分裂和数据移动，严重影响写入性能。可以用自增整型作为主键，或者用有序的雪花算法 ID。如果业务必须用 UUID，考虑用一个自增整型做主键，UUID 做二级唯一索引。

3. 为经常作为查询条件、排序、连接的列建立索引

这三个操作对索引的依赖最强：

  • WHERE 条件列：idx_xxx
  • ORDER BY 列：尽量与 WHERE 索引整合，实现“索引排序”而非“文件排序”
  • JOIN 关联列：连接双方表的关联列都应该有索引，否则会出现全表扫描或块嵌套循环连接

4. 联合索引的列数不宜过多，一般 3-5 列足矣

索引列越多，B+ 树节点能存的键就越少，树高度会升高，而且修改的成本也越高。一般一个联合索引包含 3 到 5 列已经能覆盖绝大多数的业务查询，再多就要认真考虑是不是设计不合理。

5. 长字符串字段使用前缀索引

对于 VARCHAR(255) 甚至更长的大字段，全文索引创建和维护成本高，占用空间大。可以只对字段的前若干个字符建立前缀索引：

CREATE INDEX idx_name_prefix ON users(name(10));

这样能大幅减少索引大小，只要前缀的选择性足够高即可。但要明白，前缀索引无法用于 ORDER BY 和覆盖索引，需根据场景权衡。

6. 确认索引真正被用到：EXPLAIN 验证

索引建完之后不是就万事大吉了，需要实际用 EXPLAIN SELECT ... 查看执行计划，确保：

  • key 列显示了期望的索引名
  • type 至少是 range 级别，最好是 ref 或 const
  • Extra 中没有 Using filesort（除非预期有排序），没有 Using temporary 等

如果优化器没有选择你建的索引，可能是统计信息不准、选择性太低或者查询写法导致索引失效，需要进一步排查原因。

7. 大表加索引时要谨慎，使用在线 DDL 或低峰期操作

对于千万行级别的大表，直接执行 CREATE INDEX 会长时间锁表，阻塞业务。应使用 MySQL 5.6+ 支持的在线 DDL：ALGORITHM=INPLACE, LOCK=NONE，或者使用 pt-online-schema-change 等工具在低峰期无锁执行。

8. 不要迷信单列索引，联合索引往往更高效

很多新手习惯在查询的每个条件列上都单独建一个索引，认为“都建了总有一个能用上”。实际上，一个查询通常只会用到一个索引（除索引合并外），因此为多条件查询设计一个合适的联合索引，远比给每个列建一个单列索引有效。

9. 删除不用的索引，定期巡检

业务演变后，某些查询可能被废弃，相应的索引如果不再被任何查询用到，就成了纯负担。定期通过 sys.schema_unused_indexes 查询未被使用的索引，并清理掉无用的索引，让数据库轻装上阵。

10. 为索引设置容易识别的命名规范

索引命名混乱会导致后期维护困难。推荐使用 idx_表名缩写_列名 格式，如 idx_u_mobile、idx_o_uid_ctime。这样看到名字就能立刻知道它的作用，避免在修改或删除时误操作。

12.1.3 总结

索引设计不是一次性工作，而是随着业务变化持续优化的过程。核心思路永远是：用最少的索引代价，满足最重要的查询需求。遵循以上原则和清单，你可以设计出既高效又易维护的索引体系，为 MySQL 的良好性能打下坚实基础。