MySQL 设计索引时应遵循哪些原则？

2026年03月24日

面试考察点

实战经验：面试官不仅仅想知道你会不会写 CREATE INDEX，更想考察你是否具备在生产环境中设计合理索引的能力，能否平衡查询性能与写入成本。
原理理解：是否理解 B+ 树结构、最左前缀原则、索引下推等底层原理，而非只会死记硬背几条规则。
权衡意识：索引不是越多越好，考察你是否懂得在查询性能、存储空间、写入性能之间做取舍。

核心答案

设计索引应遵循以下 7 大核心原则：

原则	核心要点	关键词
选择合适的列	WHERE、JOIN、ORDER BY、GROUP BY 列优先	高频查询列
区分度高优先	选择性 = 唯一值/总行数，越高越好	基数大、离散度高
最左前缀原则	联合索引按顺序匹配，从左到右连续使用	范围查询断后
覆盖索引	索引包含查询所需全部字段，避免回表	`SELECT` 列也在索引中
控制索引数量	单表索引不超过 5 个，单个索引字段不超过 5 个	写入成本、空间占用
前缀索引	长字符串只索引前 N 个字符，节省空间	牺牲部分选择性
索引下推	MySQL 5.6+ 特性，在索引层过滤减少回表	`Using index condition`

一句话总结：索引设计要 "选对列、区分度高、遵循最左前缀、善用覆盖索引、控制数量"。

深度解析

一、选择合适的列建立索引

上图总结了适合与不适合建立索引的列。具体说明如下：

✅ 适合建索引的场景：

WHERE 条件列：查询条件中频繁出现的列，能大幅减少扫描行数。
JOIN 关联列：外键关联列，能加速多表关联查询。
ORDER BY / GROUP BY 列：排序和分组操作非常消耗性能，索引可以利用 B+ 树的有序性直接获取结果。
高基数列：唯一值越多，索引的过滤效果越好。

❌ 不适合建索引的场景：

区分度低的列：如性别（只有 "男/女"）、状态（只有 "启用/禁用"），索引过滤效果差，优化器可能直接选择全表扫描。
频繁更新的列：每次更新都会导致索引重建，增加写入成本。
小表：数据量小于 1000 行时，全表扫描可能比走索引更快。

二、区分度（选择性）原则

区分度 = 唯一值数量 / 总行数，值越接近 1 越好。

-- 查看列的区分度
SELECT
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;

-- 结果示例：
-- user_id_selectivity: 0.85  ✅ 区分度高，适合建索引
-- status_selectivity: 0.01   ❌ 区分度低，不适合单独建索引

联合索引的列顺序：区分度高的列放前面。

-- 假设 status 只有 3 个值，user_id 有 10 万个值
-- ❌ 错误：区分度低的列在前
CREATE INDEX idx_wrong ON orders(status, user_id);

-- ✅ 正确：区分度高的列在前
CREATE INDEX idx_right ON orders(user_id, status);

三、最左前缀原则

联合索引按照定义顺序构建 B+ 树，查询时必须从最左列开始连续匹配。

上图展示了最左前缀原则的应用场景。关键点说明：

从左到右连续匹配：必须从索引的第一列开始，中间不能跳过任何一列。
范围查询会"断后"：一旦出现范围查询（>、<、BETWEEN、LIKE 前缀模糊），该列之后的索引列无法被使用。
优化建议：把范围查询列放在联合索引的最后。

-- 场景：经常查询某用户的已支付订单
-- ✅ 推荐：等值条件在前，范围条件在后
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 查询能完整利用索引
SELECT * FROM orders
WHERE user_id = 100 AND status = 'PAID' AND create_time > '2024-01-01';

四、覆盖索引

如果索引包含了查询所需的所有字段，MySQL 可以直接从索引中获取数据，无需回表查询聚簇索引。

-- 假设有索引：INDEX(user_id, status)

-- ❌ 需要回表：SELECT 中有索引不包含的字段
SELECT user_id, status, amount FROM orders WHERE user_id = 100;

-- ✅ 覆盖索引：SELECT 字段都在索引中
SELECT user_id, status FROM orders WHERE user_id = 100;

-- ✅ 更好的方案：创建包含 amount 的联合索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

EXPLAIN 中显示 Using index 表示使用了覆盖索引。

五、控制索引数量

索引不是越多越好，每个索引都有成本：

上图分析了索引带来的各项成本。具体说明：

空间成本：每个索引都是一棵独立的 B+ 树，会占用额外的磁盘空间。联合索引字段越多，空间占用越大。
时间成本（写入时）：
- INSERT 操作需要同时更新所有索引
- UPDATE 操作如果修改了索引列，需要先删除旧的索引项，再插入新的索引项
- DELETE 操作需要从所有索引中删除对应的数据
优化器成本：索引越多，MySQL 优化器在生成执行计划时需要考虑的选项越多，可能导致选择时间变长，甚至选错索引。

经验法则：

单表索引数量：不超过 5 个
单个索引字段数：不超过 5 个
定期审查：删除 unused 索引

-- 查看索引使用情况（MySQL 5.7+）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';

六、前缀索引

对于 VARCHAR、TEXT 等长字符串列，可以使用前缀索引只索引前 N 个字符，节省空间。

-- 创建前缀索引，只索引 email 前 10 个字符
CREATE INDEX idx_email_prefix ON users(email(10));

-- 查询时正常使用
SELECT * FROM users WHERE email = 'test@example.com';

权衡：

✅ 节省索引空间
❌ 可能降低区分度（前 N 个字符相同的情况）
❌ 无法用于覆盖索引（必须回表验证完整值）

如何确定前缀长度：

-- 计算不同前缀长度的区分度
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15,
    COUNT(DISTINCT email) / COUNT(*) AS full_column
FROM users;

-- 选择区分度接近完整列的最短前缀

七、索引下推（Index Condition Pushdown，ICP）

MySQL 5.6+ 引入的优化特性，将部分 WHERE 条件的过滤下推到存储引擎层，减少回表次数。

-- 假设有索引：INDEX(name, age)

-- 无 ICP：存储引擎返回所有 name 匹配的记录，Server 层再过滤 age
-- 有 ICP：存储引擎直接过滤 name AND age，只返回符合条件的记录
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;

EXPLAIN 中显示 Using index condition 表示使用了索引下推。

面试高频追问

联合索引的列顺序如何确定？
- 区分度高的列放前面、等值条件列放前面、范围查询列放后面、考虑排序分组需求。
什么情况下索引会失效？
- 对索引列使用函数或计算、隐式类型转换、LIKE 以 % 开头、OR 条件中有列无索引、联合索引不满足最左前缀、否定条件（!=、NOT IN）等。
主键为什么建议使用自增 ID？
- 自增 ID 保证新数据顺序插入 B+ 树末尾，避免页分裂，减少碎片，提高写入性能和空间利用率。

常见面试变体

"联合索引 (a, b, c)，WHERE b = 1 AND c = 2 能走索引吗？"
"什么情况下应该使用前缀索引？"
"索引越多越好吗？为什么？"
"覆盖索引是什么？有什么优势？"

记忆口诀

索引设计七原则：选对列、区分高、最左前、覆盖好、控数量、前缀省、下推快

联合索引顺序：区分度高放前头，等值条件优先走，范围查询放最后

总结

索引设计需要在查询性能和写入成本之间做权衡。核心原则是：选择高频查询且区分度高的列建立索引；联合索引遵循最左前缀原则，区分度高的列放前面；善用覆盖索引避免回表；控制索引数量避免过度索引；长字符串可使用前缀索引节省空间。记住，索引不是越多越好，要根据实际业务场景和查询模式进行设计。

发布于2025年12月29日|22:05:38