当前位置：首页 > news >正文

【大白话说Java面试题第73题】【Mysql篇】第3题：说说索引的设计原则？

news 2026/5/24 13:06:14

第3题：索引的设计原则

📚回答：

核心考点：
大厂面试中，索引设计原则不仅是“知道什么该建”，更要懂“什么不该建”以及“为什么”。面试官期望你从查询模式、数据分布、写入代价三个维度综合权衡，给出可落地的设计规范。

1. 索引设计的核心目标

索引的本质是用空间换时间，但代价是写入性能下降和存储成本增加。设计原则的核心是在查询效率和写入/存储成本之间找到平衡点。

大厂口诀：读多写少加索引，写多读少要克制，覆盖索引是王道，最左前缀不能忘。

2. 六大核心设计原则（面试必背）

原则	核心要点	反例（不要这样建）	大厂最佳实践
① 为高频查询字段建索引	`WHERE`/`JOIN`/`ORDER BY`/`GROUP BY`字段优先	给从不查的字段加索引	用慢查询日志识别高频查询，针对性建索引
② 高区分度字段优先	区分度 =`COUNT(DISTINCT col)/COUNT(*)`，越接近1越好	给性别字段建单独索引（除非查询极少数值）	区分度 > 0.1 才考虑建索引
③ 长字段用前缀索引	只索引前N个字符，减少存储和I/O	给`TEXT/BLOB`全字段建索引（必须指定前缀）	前缀长度选择：以区分度损失<5%为准
④ 更新频繁字段谨慎建	索引列每更新一次，B+树就要调整一次	给`last_login_time`建索引	更新频繁但查得少的字段，牺牲查询换写入
⑤ 联合索引遵循最左前缀	查询条件必须从索引最左列开始匹配	只查第2列却不查第1列	等值查询列放左边，范围查询列放右边
⑥ 用覆盖索引避免回表	索引包含查询所需的所有字段	`SELECT *`配合二级索引（必回表）	只查索引中的列，`SELECT`只写必要字段

3. 原则详解 + 大厂面试追问

原则①：为高频查询字段建立索引

为什么：索引的目的是快速缩小扫描范围。如果某字段从不作为查询条件，建索引只会浪费空间并拖慢写入。

大厂追问：“怎么识别高频查询字段？”

回答：

开启慢查询日志（slow_query_log=ON，long_query_time=1）
用pt-query-digest分析慢日志，按查询频率排序
对TOP 10的慢查询，通过EXPLAIN看是否缺索引

原则②：高区分度字段优先，低区分度需谨慎

核心公式：

区分度 = COUNT(DISTINCT col) / COUNT(*)

区分度 ≈ 1（如手机号、身份证）→非常适合建索引
区分度 ≈ 0（如性别、状态码）→通常不适合单独建索引，除非数据分布极不均匀

大厂追问：“性别字段什么时候适合建索引？”

回答：
当数据分布极度不均时，查询少数值可以用索引。
举例：订单表status有'pending'(10条) 和'done'(100万条)，查询WHERE status='pending'时，索引能快速定位10条记录，虽然基数低但有用。

量化经验：

区分度 > 0.1 → 可以考虑
区分度 < 0.01 → 除非覆盖索引或联合索引，否则不建议单独建

原则③：长字段使用前缀索引

语法：

-- 只索引 content 字段的前50个字符ALTERTABLEarticlesADDINDEXidx_content(content(50));

为什么：VARCHAR(255)全字段索引，每个索引项占255字节，页内能存的索引项少 → 树高增加 → I/O增多。

大厂追问：“前缀长度怎么选？”

回答：
目标是在空间和区分度间平衡。通常测试不同长度：

-- 计算不同前缀长度的区分度SELECTCOUNT(DISTINCTLEFT(content,10))/COUNT(*)ASsel_10,COUNT(DISTINCTLEFT(content,20))/COUNT(*)ASsel_20,COUNT(DISTINCTcontent)/COUNT(*)ASsel_fullFROMarticles;

选择区分度接近全字段、但长度较短的值。例如全字段区分度0.95，前缀20字符已达0.94，则选20。

MySQL限制：

InnoDB（REDUNDANT/COMPACT格式）：前缀最大767字节
InnoDB（DYNAMIC/COMPRESSED格式）：前缀最大3072字节
多字节字符集（UTF8MB4）需注意：1个字符可能占4字节

原则④：更新频繁字段谨慎建索引

为什么：

索引是有序数据结构，字段值更新可能导致B+树页分裂/页合并，代价高
写入放大：更新一行数据，需要维护该行所有索引

大厂追问：“last_login_time每次登录都更新，要不要建索引？”

回答：
如果查询场景是“统计近7天登录用户”，该字段既要更新又要查询，解决方案：

方案1：接受写入代价，建索引（读多写少时）
方案2：用异步统计表+ 定时任务，避免高频更新字段被索引（写多读少时）
方案3：用覆盖索引减少回表代价

经验法则：

读写比 > 10:1 → 可以建索引
读写比 < 3:1 → 慎重，考虑是否必要

原则⑤：联合索引遵循最左前缀

联合索引(a, b, c)的命中规则：

WHERE条件	是否命中	使用哪些列	说明
`WHERE a=1`	✅	a	最左列命中
`WHERE a=1 AND b=2`	✅	a, b	连续两列
`WHERE a=1 AND b=2 AND c=3`	✅	a, b, c	全命中
`WHERE a=1 AND c=3`	✅（部分）	a（c用ICP）	跳过b，c无法用于索引查找，但可用索引下推过滤
`WHERE b=2`	❌	无	缺少最左列，全表扫描
`WHERE a=1 AND b>2 AND c=3`	✅（部分）	a, b（c无效）	范围查询`b>`会中断后续列

大厂追问：“为什么要遵循最左前缀？底层原理是什么？”

回答：
B+树索引的排序规则是先按第一列排序，第一列相同再按第二列排序。

(a, b)联合索引：数据先按a排序，a相同时按b排序
只给b条件时，b在整个树中不是全局有序的，无法利用索引

设计技巧：

等值查询列放左边，范围查询列放右边：(a, b)中，WHERE a=1 AND b>2能用完(a,b)；反过来(b, a)无法使用
区分度高的列放左边：能更快缩小范围
频繁查询的列放左边：让更多查询能用上索引

原则⑥：用覆盖索引避免回表

什么是回表：
二级索引叶子节点存的是主键值，不是完整行数据。查询非索引列时，需要先用二级索引找到主键，再用主键查聚簇索引，两次B+树查找。

覆盖索引：
索引包含了查询所需的所有字段，无需回表，直接在索引上返回结果。

示例：

-- 联合索引：idx_name_age (name, age)-- 覆盖索引查询SELECTname,ageFROMusersWHEREname='张三';-- 不回表，Using index-- 非覆盖索引查询SELECTname,age,cityFROMusersWHEREname='张三';-- 回表，Using index condition

大厂追问：“如何判断一条SQL是否用了覆盖索引？”

回答：
用EXPLAIN，看Extra列：

Using index→ 使用了覆盖索引，不回表
Using index condition→ 用了索引+索引下推，但仍需回表
没有Using index→ 需要回表

优化技巧：

避免SELECT *，只查必要的字段
把高频查询字段放入联合索引，变成覆盖索引

4. 索引命名规范（大厂加分项）

大厂通常有索引命名规范，面试中提到能体现工程素养：

索引类型	命名规范	示例
普通索引	`idx_表名_字段名`	`idx_user_name`
联合索引	`idx_表名_字段1_字段2`	`idx_order_user_time`
唯一索引	`udx_表名_字段名`	`udx_user_email`
前缀索引	`idx_表名_字段名_prefix`	`idx_article_content_prefix`

5. 实战案例：电商订单表索引设计

业务场景：
订单表（orders），字段：order_id(主键)、user_id、status、create_time、amount

高频查询：

SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC LIMIT 10（查询某用户最近订单）
SELECT COUNT(*) FROM orders WHERE status = 'pending'（统计待处理订单）
SELECT SUM(amount) FROM orders WHERE create_time BETWEEN ? AND ?（日报统计）

索引设计方案：

索引	设计理由	潜在问题及应对
主键索引：`PRIMARY KEY (order_id)`	自增整型，避免页分裂	UUID主键会导致二级索引膨胀
联合索引：`idx_user_create (user_id, create_time)`	覆盖排序字段，避免`Using filesort`	`user_id`需等值查询
覆盖索引：`idx_status (status)`+ 考虑使用计数表	status区分度低	若99%订单为`done`，查`pending`用索引有效；否则用异步计数表替代
覆盖索引：`idx_create_time (create_time, amount)`	日报统计时覆盖`amount`，避免回表	若create_time范围太大，考虑按月分表

反例（不要这样做）：

❌ 给status、create_time、user_id分别建三个单列索引 → 浪费空间，写入慢
❌ 主键用UUID → 页分裂频繁，性能差
❌ 给amount单独建索引（不单独作为查询条件）

6. 索引失效场景速查（面试必考）

失效场景	示例	解决方案
函数操作	`WHERE DATE(create_time) = '2024-01-01'`	改用范围查询：`create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'`
隐式类型转换	`WHERE phone = 13800138000`（phone是VARCHAR）	保持类型一致：`phone = '13800138000'`
联合索引不满足最左前缀	索引`(a,b)`，只查`b=1`	要么调顺序，要么新建索引
使用`!=`或`<>`	`WHERE status != 'done'`	考虑用`IN`包含需要的值
`LIKE`以通配符开头	`WHERE name LIKE '%张'`	改用`name LIKE '张%'`或全文索引
`OR`中包含非索引列	`WHERE name='a' OR age=30`（age无索引）	拆成`UNION`或给age加索引
字符集/排序规则不一致	两表JOIN，字段字符集不同（utf8 vs utf8mb4）	统一字符集和排序规则

7. 总结对比表

原则	核心要点	适用场景	不适用场景
高频字段建索引	WHERE/JOIN/ORDER BY字段	读多写少	写多读少
高区分度优先	区分度 > 0.1	唯一性字段	性别、状态等低基数字段
前缀索引	长字符串只索引前N字符	VARCHAR/TEXT/BLOB	短字段
更新频繁谨慎建	写入代价高	几乎不变的字段	频繁UPDATE的字段
最左前缀	从最左列开始匹配	多条件查询	跳列查询
覆盖索引	索引包含所有查询列	高频小查询	SELECT * 场景

💡面试官想要的满分总结：

“索引设计核心原则是从查询模式出发，在查询效率和写入代价间权衡。
六大原则：
① 高频WHERE/JOIN/ORDER BY字段优先建索引；
② 高区分度字段优先，区分度<0.01的除非联合索引否则慎重；
③ 长VARCHAR/TEXT用前缀索引，以区分度损失<5%为界；
④ 更新频繁字段谨慎建，读写比<3:1时不建；
⑤ 联合索引遵循最左前缀，等值列放左、范围列放右；
⑥ 尽量用覆盖索引避免回表，SELECT时只查必要字段。
落地规范：用EXPLAIN验证每个索引是否生效；定期通过慢查询日志和pt-query-digest识别慢SQL；单个表索引数建议不超过5-6个；主键用自增整型，避免UUID。
面试常考的反例：性别建单列索引、联合索引跳列、长字符串全字段索引、SELECT *导致回表——这些都要能解释清楚为什么错。”