人人都会AI编程

24.2 索引设计规范与禁止项

更新时间：2026-07-11

索引是数据库性能优化的核心手段，设计得当能显著提升查询效率，设计不当则不仅拖累查询性能，还会增加写入开销、占用大量磁盘空间。以下规范基于 InnoDB 引擎特性整理，可直接用于日常开发和评审。

24.2.1 索引设计的基本流程

不推荐凭感觉建索引，建议遵循以下步骤：

  1. 分析查询语句：梳理业务中的核心 SQL，尤其是高频查询、复杂关联和慢查询。
  2. 使用 EXPLAIN 验证：对每条关键 SQL 执行 EXPLAIN，确认是否使用了合适的索引，关注 type、key、rows、Extra。
  3. 优先满足过滤与排序：索引需要覆盖 WHERE、JOIN ON、ORDER BY、GROUP BY 中的列。
  4. 从单列索引开始，逐步优化为联合索引：避免一开始就建大量单列索引，应优先设计联合索引覆盖多个查询。
  5. 评估写入负载：了解该表的插入、更新频率，避免过度索引拖累写入性能。
  6. 上线后监控：利用 pt-index-usage 或通过慢查询日志定期分析索引使用情况，清理无用索引。

24.2.2 联合索引设计规范

联合索引是索引设计中最容易出错的区域，牢记以下规则：

  • 最左前缀原则是铁律：联合索引 (a, b, c) 能加速查询条件中包含 a、a,b、a,b,c 的过滤，但无法替代仅对 b 或 c 的查询。设计时要确保查询条件一定会带有索引最左列。
  • 等值查询列放在前面，范围查询列放在最后：例如查询 WHERE status = 1 AND create_time > '2024-01-01'，应建立 (status, create_time) 索引，而不是反过来。因为范围查询后面的列无法继续使用索引排序。
  • 覆盖索引优先：如果联合索引已经包含了 SELECT 需要的所有列，查询就无需回表。在设计时尽量将 SELECT 的列纳入索引尾部（在过滤列之后），以形成覆盖索引，但要注意索引长度不宜过长。
  • 区分度高的列靠左：将选择性高（不同值多）的列放在联合索引的靠前位置，有助于快速缩小扫描范围。例如 user_id 比 gender 更适合做前缀。
  • 联合索引数量不宜过多：一个表通常建议不超过 5~6 个索引，单表索引字段总数不宜超过 10~15 个。每个联合索引的列数一般不超过 3~4 列，最宽泛不超过 5 列。

24.2.3 主键设计规范

InnoDB 表必须有主键，且主键设计直接影响聚簇索引的性能和空间：

  • 主键必须存在且尽量短小：如果没有显式定义主键，InnoDB 会选择一个唯一非空索引或内部生成 6 字节的 row_id。后者不可控且额外开销，应显式指定。
  • 推荐使用自增主键：自增整数（BIGINT UNSIGNED）能让数据按顺序插入，减少页分裂和磁盘碎片，聚簇索引的叶子节点可以填得更满。

为什么不用 UUID 字符串作为主键？
UUID 是无序随机值，插入时会导致频繁的页分裂和索引碎片，造成磁盘空间浪费和性能下降。如果业务需要全局唯一标识，可以将 UUID 作为唯一二级索引，主键仍用自增整数。

  • 主键尽量用整型并避免过长：INT 和 BIGINT 比较及排序的效率远高于字符串。主键值会附加到每个二级索引的叶子节点中，主键过长会导致所有二级索引体积膨胀。
  • 避免使用业务字段作为主键：业务字段可能变更（如手机号、身份证号），主键变更会触发所有二级索引的级联更新，代价极高。主键应当是无物理意义的代理键。

24.2.4 索引列顺序的优化

对于单列索引和联合索引，列的顺序设计要注意：

  • 将 WHERE 条件中最“限定范围”的列置前：例如查询 WHERE org_id = 10 AND user_id = 100，如果 org_id 唯一性差而 user_id 区分度高，优先考虑 (user_id, org_id)，但也要考虑是否存在其他查询只以 org_id 为条件，这时可能需要两个索引或不同的排序。
  • 将 GROUP BY / ORDER BY 的列加入索引：利用索引有序的特点避免额外的文件排序（filesort）。例如 ORDER BY create_time DESC，联合索引可设计为 (user_id, create_time)，使排序直接通过索引完成。
  • 避免在排序或分组中包含多个不同方向的排序列：如 ORDER BY a ASC, b DESC，即使有联合索引 (a, b)，MySQL 8.0 之前的版本无法利用索引直接排序。8.0 支持降序索引，可适当使用。

24.2.5 索引禁止项（红线）

在评审和开发中，遇到以下情况应要求修改：

  1. 禁止在频繁更新的列上建立过多索引

每次 UPDATE 若涉及索引列，都需要同步维护索引树。对高频变更的字段（如订单状态、计数）建立索引要慎重，确保利大于弊。

  1. 禁止在索引列上使用函数或运算

WHERE DATE(create_time) = '2024-01-01' 会导致索引失效。应改写为范围查询 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。

  1. 禁止隐式类型转换

索引列为字符串时，查询条件中不要传入数值。例如 WHERE phone = 13800138000 会触发类型转换导致索引失效，必须写成 WHERE phone = '13800138000'。

  1. 禁止滥用 SELECT *

联合索引有机会做覆盖索引，但若总是 SELECT *，则无法使用覆盖索引，必须回表。应只查询需要的列。

  1. 禁止在区分度极低的列上单独建立索引

例如性别、布尔字段、状态值等仅几个值且分布均匀的列，单独建立索引无法有效过滤数据，优化器往往选择全表扫描。如果实在需要，可以放在联合索引最前面作为辅助过滤，但要先评估效果。

  1. 禁止冗余和重复索引

INDEX(a) 和 INDEX(a, b) 并存时，前者是冗余的，因为联合索引的前缀 (a) 已经可以替代它。多余的索引只会浪费空间和写入开销，必须清理。

  1. 禁止使用外键在生产环境

虽然 MySQL 支持外键，但外键在并发写入、数据迁移、分库分表场景会带来锁竞争和维护困难。应在应用层保证数据一致性，而非依赖数据库外键。

  1. 禁止在长字符串上直接建立完整索引

对于 CHAR(255) 或 TEXT 字段，应使用前缀索引（INDEX(col(20))）或倒排索引方案（如 Elasticsearch），避免索引体积过大。

  1. 禁止未经 EXPLAIN 验证的索引上线

任何新建索引都必须经过执行计划确认，同时评估对写入性能的影响。可在非生产环境或利用 --innodb-index-stats 测试。

  1. 禁止忘记监控索引使用率

定期通过 sys.schema_unused_indexes（MySQL 8.0）或 pt-index-usage 识别长期未使用的索引，并予以删除。避免“建了不删”造成索引膨胀。

24.2.6 索引维护与持续优化

  • 统计信息更新：执行 ANALYZE TABLE tbl; 可使索引统计信息更准确，优化器会选择更优计划。
  • 定期重建索引：对于被大量删除和插入的表，可以通过 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建表空间和索引，回收碎片。
  • 不可见索引测试：MySQL 8.0 支持 ALTER TABLE ... ALTER INDEX idx_name INVISIBLE，可将索引设为不可见，观察性能影响后再确定是否删除，是安全删除索引的最佳实践。

索引设计是一项平衡艺术：既要加速查询，又要控制写入开销；既要减少回表，又要避免索引体积过大。遵循以上规范和禁止项，可以让索引真正成为性能的“助推器”而非“绊脚石”。