设计索引的时候有哪些原则（考虑哪些因素）？

面试记录 2026-09-17

题目：设计索引的时候有哪些原则（考虑哪些因素）？

难度：high

标签：db/mysql/index, mysql/index, difficulty/high

评分：⭐⭐⭐☆（3/5）

我的回答

设计索引，MySQL 设计索引首先要考虑数据量情况，然后另外要根据实际业务看业务是怎么划分的，业务怎么查询的，然后以及业务的排序规则去考虑使用哪些索引。要创建索引，毕竟要根据实际业务来，有业务才去建索引，没业务没有必要建这些索引，因为建立索引是要耗费资源。要说原则吧，首先是要在区分度比较大的数据上，字段上进行索引创建，而不要在区分度特别小的字段上建索引。区分小的字段上建索引有意义，但意义不大。另外的话是，我要考虑联合索引，也就是多个字段建立索引。这时候要考虑的话，可能一个要考虑回表，一个是查询规则，还有是排序规则。依据目标去创建对应的联合索引，达到快速查询、快速返回的目的。然后索引的原则，另外如果有匹配的模糊查询之类的，可能会加一些特殊的全文索引，这时候如果不用 eS 类的搜索引擎的话，就加全文索引。基本上索引的创建原则大概就是这些。另外创建索引时候要考虑索引是本身是有长度限制的，不能在太长。这个不能太长指的是单个字段上的字段值不能太长。比如你在一个 Text 字段上加索引，最好截取前面多少，而不是说整个 Text 那么大的一个字段去建索引。Text 不是 test。

点评

优点：

• 强调业务驱动：先看业务查询和排序再建索引，"没业务不建"是对的
• 提到高基数优先：低基数字段单独建索引意义不大
• 提到联合索引 / 回表 / 排序几个关键点
• 提到 TEXT 前缀索引 与 全文索引
• 提到索引本身有长度限制

不足：

• 未讲读写权衡：索引越多，INSERT/UPDATE/DELETE 越慢（每个索引都要维护）
• 未具体量化基数：distinct_count / total_rows，越接近 1 越适合建索引
• 未讲联合索引列顺序：等值条件列放前、范围列放后、排序列放最后
• 未讲覆盖索引优先：能覆盖则不建更宽索引，避免回表
• 未讲冗余索引治理：(a,b,c) 存在时 (a,b)、(a) 就是冗余
• 未讲 MySQL 8.0 特性：INVISIBLE INDEX（灰度下线）、descending index、functional index
• 未讲索引键长度限制：InnoDB 默认 3072 字节（innodb_large_prefix=ON），旧版本 767 字节
• 未讲分库分表场景：索引设计与 shard key 的联动

补充：

• 索引设计 5 问：谁查、查多少、多频繁、要不要排序、写多还是读多
• 治理工具：sys.schema_unused_indexes、performance_schema.table_io_waits_summary_by_index_usage.count_star=0
• 灰度下线：MySQL 8.0 ALTER TABLE t ALTER INDEX idx INVISIBLE 观察再删
• 唯一索引 vs 普通索引：唯一索引更省空间（记录更紧凑），还能加速优化器选择

完整讲解

第一部分：知识重点剖析

核心考点定位：以业务查询模式为锚点，系统掌握索引设计的全维度决策框架，覆盖数据、访问、性能、治理四条主线。

知识点拆解：

1. 业务维度：先看查询，再定索引

• 查询频率：一个索引每月只被用几次，收益远不如维护成本
• 查询模式：等值 / 范围 / 排序 / 分组 / JOIN，每个模式对索引要求不同
• 数据量：小表（< 1 万行）优化器倾向全表；大表必须靠索引
• 读写比：读多写少 → 多建索引；写多读少 → 少建但建好

1. 基数（Cardinality）与选择率

• 基数 = distinct_count / total_rows
• 基数接近 1（如 ID、订单号）→ 单列索引最有效
• 基数很低（如 gender、status 只有 2-3 值）→ 单列索引几乎无用，考虑放联合索引前缀或 Skip Scan
• 中基数（如 city、user_id）→ 单列索引常见
• SHOW INDEX FROM t 里的 Cardinality 是采样估算，用 SELECT COUNT(DISTINCT col), COUNT(*) FROM t 拿真实值

1. 联合索引的列顺序原则（关键）

• 等值条件列放前：WHERE a=? AND b>? → 索引顺序 (a,b) 才对
• 范围条件列放后：范围列后面的列无法用索引定位（跨范围无序）
• 排序列放最后：ORDER BY 的列如果放在联合索引最后，且前面都是等值，可以省 filesort
• 高基数列尽量靠前：能更快收敛匹配行
• 避免低基数列开头：如 (status, user_id)，status 基数 3 会导致扫描大量重复行

1. 覆盖索引优先

• SELECT 的列全部命中某个索引 → 走覆盖索引，Extra=Using index
• 覆盖索引能省掉回表，性能提升数倍
• 权衡：索引宽度 vs 覆盖收益。避免为了覆盖把所有列都塞进一个索引，那样索引本身会膨胀

1. 回表数量控制

• 回表次数 = 索引扫描到的行数，不是最终 LIMIT 数量
• LIMIT 10 的查询可能回表几百万次
• 优化：延迟关联、覆盖索引、ICP

1. 索引键长度限制

• InnoDB 默认：innodb_large_prefix=ON 时 3072 字节（COMPRESSED/DYNAMIC 行格式）
• 旧版本 767 字节（COMPACT/REDUNDANT 行格式）
• TEXT/BLOB 不能整列建索引，必须用前缀索引 INDEX(name(20))
• 前缀长度选择：SELECT COUNT(DISTINCT LEFT(name, N)) / COUNT(DISTINCT name) FROM t，达到 0.99+ 停止

1. 索引类型选择

• B+ 树索引（默认）：等值、范围、排序都能用
• 全文索引：中文模糊搜索场景，需要 ngram 分词器
• 空间索引：GIS 场景
• HASH 索引：仅 Memory 引擎支持；InnoDB 的 adaptive hash index 是自动的，无法手动控制
• MySQL 8.0 新增：FUNCTIONAL INDEX（表达式索引），DESCENDING INDEX（降序索引）

1. 冗余索引治理

• 前缀冗余：(a,b,c) 已存在时 (a,b)、(a) 是冗余
• 完全冗余：(a) 和 (a) 重复
• 反向冗余：(a,b) 和 (b,a) 都能用，但用途不同，通常保留一个
• 治理手段：
• sys.schema_unused_indexes 找 count_star=0 的索引
• performance_schema.table_io_waits_summary_by_index_usage 精确统计
• MySQL 8.0 ALTER TABLE t ALTER INDEX idx INVISIBLE 灰度下线，观察 1-2 周再 DELETE

1. 特殊场景

• JOIN 场景：外键关联字段必须建索引，否则 join 时右表全表扫描
• 分库分表：索引设计要考虑 shard key 是否在索引里，跨分片查询尤其要谨慎
• 唯一约束：业务上唯一的字段必须建唯一索引，同时能防止重复插入，还能加速等值查询
• 主键选择：优先自增 bigint，避免 UUID（无序、体积大、页分裂）
• NULL 值：索引列允许 NULL 会增加 NULL bitmap 开销，优先 NOT NULL + 默认值

1. 工程化治理

• 索引变更走 CR：谁能加、谁能删要有流程
• FORCE INDEX 硬编码是雷区：索引被删业务立即崩
• 定期审计：每月扫一次 schema_unused_indexes 和慢查询
• 生产变更前必跑 EXPLAIN ANALYZE（8.0）看真实代价

第二部分：考察方向与延伸递进

考察方向：

• 深度：能否讲清联合索引列顺序、覆盖索引、基数选择背后的原理
• 广度：能否覆盖索引类型、长度限制、8.0 新特性、分库分表
• 应用：能否针对真实业务给出索引设计 + 治理的完整方案

延伸递进面试题：

1. (status, user_id, create_time) 是常见设计，如果查询是 WHERE status=1 ORDER BY create_time DESC，这个索引能用吗？

• 解答要点：能。status 等值命中前缀，user_id 未知但因为是联合索引的中间列——不对，中间列未知则 create_time 无法走排序。所以要改成 (status, create_time) 或直接建 (status, create_time DESC)（8.0 支持）。

1. TEXT 字段做前缀索引怎么选长度？

• 解答要点：跑 SELECT COUNT(DISTINCT LEFT(col, N)), COUNT(DISTINCT col) FROM t 让比值达到 0.99 或以上，同时兼顾空间。经验值：邮箱 20、手机号 11、用户名 10-15。

1. MySQL 8.0 的 INVISIBLE INDEX 相比直接删除有什么好处？

• 解答要点：可以先设置为不可见观察 1-2 周，确认没有 SQL 走这个索引后，再执行真正的 DROP INDEX。避免"删了才发现在用"的事故。DDL 也是在线的。

第三部分：面试者参考回答（口语化）

面试官您好，索引设计我按四条主线来讲：业务、数据、性能、治理。

第一条，业务维度。索引不是越多越好，一定要业务驱动——有查询模式才建索引。我会先摸清几个问题：谁在查、查多频繁、单条查询返回多少行、要不要排序、表是读多还是写多。小表几千行不用建，优化器自己全表扫；大表必须靠索引。写多读少的表要克制，每个索引都是 INSERT/UPDATE/DELETE 的额外维护成本。

第二条，数据维度，重点是基数和联合索引列顺序。基数就是 distinct_count / total_rows，越接近 1 越适合建单列索引，比如 ID、订单号；基数很低（如 status 只有 2-3 值）单独建索引基本没用，但可以放联合索引前面做前缀。联合索引的列顺序有讲究：等值条件列放前面、范围条件列放后面、ORDER BY 列放最后——这样能省 filesort。反过来如果排序列前面还有个未知列，排序就废了。

第三条，性能维度，重点是覆盖索引和回表控制。如果能把 SELECT 列全部塞进一个索引里，就走覆盖索引，EXPLAIN 显示 Using index，性能能提升好几倍。但索引宽度也有代价，不能什么都往里塞。回表次数取决于索引扫描行数而不是 LIMIT，所以要用延迟关联或 ICP 减少。索引键长度也有硬限制，InnoDB 默认 3072 字节，TEXT 字段必须做前缀索引，用 SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(DISTINCT col) 找最优长度。

第四条，索引治理。这块特别重要，很多线上事故出在这里。三个点：一是冗余治理，(a,b,c) 存在时 (a,b)、(a) 就是冗余，应该删掉；二是无索引治理，用 sys.schema_unused_indexes 或者 performance_schema.table_io_waits_summary_by_index_usage.count_star=0 找长期没人用的索引；三是灰度下线，MySQL 8.0 有 ALTER TABLE t ALTER INDEX idx INVISIBLE，可以先设为不可见观察一两周，确认没有 SQL 依赖后再真删。

特殊场景还有几个：JOIN 关联字段必须建索引；分库分表要考虑 shard key 是否在索引里；唯一约束优先用唯一索引既能防重又能加速；主键选自增 bigint 避免 UUID 打散 B+ 树导致页分裂和随机 IO；索引列最好 NOT NULL，避免 NULL bitmap 的额外开销。

最后工程化流程：任何索引变更走 CR，生产变更前跑 EXPLAIN ANALYZE 看真实代价，每月审计一次 schema_unused_indexes。特别警惕 FORCE INDEX 硬编码——团队有人删了这个索引而代码里还强推，业务直接生产报错，我之前踩过这个坑。

---

面试记录 2026-10-13（第 2 次）

题目：设计索引的时候有哪些原则（考虑哪些因素）？

难度：high

标签：db/mysql/index, mysql/index, difficulty/high

评分：⭐⭐⭐⭐☆（4/5）

练习次数：2

我的回答

设计索引要考虑的核心机制，首先是业务第一。基于什么样的业务去设计什么样的索引，不能创建一个没有意义的索引。你创建了一个特别好的索引，但这个索引没有意义，创建的是没有用的。

首先创建索引要在区分度比较高的字段上进行创建。如果一个字段区分度特别低，各种数据一样占一半，或者占的比例都很多，没有什么区分度，那么这个索引的意义也不大。

要解决的核心目标是什么？是为了快速的找到我们目标的数据。前置说的区分度高的，就是为了这个；然后整个索引的过程会涉及整个搜索的过程会涉及几个方面。首先是通过索引找到数据，然后如果你要显示的数据在索引中不存在，就会涉及一个回表。这里边要考虑的就是你的索引是否要进行覆盖，覆盖索引的所有字段。如果能覆盖，就会减少一次搜索。

另外就是查询的时候会涉及排序，排序的字段是否要包含在索引中？尽可能的其实要把排序字段在索引中体现出来，这样可以更好的利用索引提前进行排序，而不是搜索出来之后再进行外部的文件排序，那样会浪费更多的时间和资源。

还有就是对于数据量的考量，数据量级具体在什么量级，使用什么样的索引，不同量级的话可能要考虑提前做分库分表，这里不仅仅是索引方面的问题，还有对数据量评估，以什么维度去进行数据库的拆分。

这就是我考虑的创建索引时的主要原则。

点评

优点：

• 业务第一：抓到了最本质的原则——有查询模式才建索引
• 区分度：明确说低区分度字段建索引意义不大
• 覆盖索引减少回表：能识别回表成本，理解覆盖索引的价值
• 排序字段放索引避免 filesort：抓到了 ORDER BY 优化要点
• 数据量级考量：意识到数据量大会走向分库分表

不足：

• 未讲联合索引列顺序：等值列放前、范围列放后、排序列放最后——这是索引设计最关键的细节
• 未讲写放大成本：每个索引都要维护 B+ 树，写多读少场景要克制
• 未讲唯一索引：既能防重又能加速，业务唯一字段必须建
• 未讲主键选择：自增 bigint vs UUID，UUID 会导致 B+ 树页分裂和随机 IO
• 未讲前缀索引：TEXT/BLOB 字段用 INDEX(col(20)) 而非整列
• 未讲索引长度限制：InnoDB 默认 3072 字节（8.0 COMPRESSED/DYNAMIC）
• 未讲 JOIN 场景：关联字段必须建索引，否则右表全表扫描
• 未讲 NOT NULL：索引列允许 NULL 会增加 NULL bitmap 开销
• 未讲冗余治理：(a,b,c) 已存在时 (a,b)、(a) 冗余；schema_unused_indexes、INVISIBLE INDEX 灰度下线
• 未讲 8.0 新特性：FUNCTIONAL INDEX（表达式索引）、DESCENDING INDEX（降序索引）
• 与首次（2026-09-17）相比，评分从 3/5 提升到 4/5，说明对"覆盖索引、排序、业务驱动"这三个点已经掌握到位

补充：

• 索引设计 5 问：谁查、查多少、多频繁、要不要排序、写多还是读多
• 联合索引列顺序速查：WHERE a=? AND b>? ORDER BY c → (a, b, c)
• 治理工具：sys.schema_unused_indexes、performance_schema.table_io_waits_summary_by_index_usage.count_star=0
• 灰度下线：MySQL 8.0 ALTER TABLE t ALTER INDEX idx INVISIBLE 观察再删

ℹ️ 完整讲解见上方首次记录（2026-09-17）