🕵️♂️后端面试题：你一般怎么建 MySQL 索引？基于什么原则？遇到过索引失效吗？怎么优化的？
前几天在面试一个候选人，我顺嘴问了句：“你平时是怎么建 MySQL 索引的？”
对方愣了两秒，说：“呃……一般建在 where 里用到的字段上？”
好吧，这确实没错，但这就像你说“吃饭要用嘴”一样——「对但没用」 😂。
今天就来和大家唠唠这个经典老题：
👉 “你一般怎么建索引？基于什么原则？有没有遇到过索引失效？”
一、先搞清楚索引到底是个啥？🔍
我们都知道索引是为了“加速查询”，但它到底怎么加速的呢？
如果你还停留在“索引是数据库的目录”这个比喻，那只能说明你听课很认真，但没真懂 😎。
简单来说，**「MySQL 的 B+Tree 索引」**可以理解成一棵多叉平衡树。
它把数据按键值有序存储在磁盘页里，查找时能快速定位到目标页，从而大幅减少磁盘 IO。
👇下面是一张非常直观的示意图（可以想象成“电话簿结构”）：
[50]
/ \
[10,30] [60,80,100]
查找 30？
👉 先看根节点 50，小于 50，往左走；
👉 左边节点里找到 30，完美命中。
这就是索引的本质：「用空间换时间」。
二、那该怎么建索引？🧠（重点）
当你面对一个表时，脑子里要先有一个“查询路径”的意识：
到底哪些 SQL 语句最频繁？哪些字段出现在 where、join、order by、group by？
举个栗子🌰：
CREATE TABLE user (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
created_at DATETIME,
INDEX idx_name_age_city (name, age, city)
);
为什么是 (name, age, city) 这个组合？
这背后其实有个索引设计的“潜规则”：
❝
「最左前缀原则（Leftmost Prefix Rule）」
❞
也就是说，这个联合索引可以支持：
WHERE name = ?✅WHERE name = ? AND age = ?✅WHERE name = ? AND age = ? AND city = ?✅ 但👇这些就不行：WHERE age = ?❌WHERE city = ?❌
就像打开一个三层密码箱，你得从第一层开始，一层层解锁。 跳过前面直接开第三层？MySQL 表示：哥，这我真帮不了你 😅。
三、索引失效的“坑”们 🕳️（都是血泪史）
你以为建好索引就能高枕无忧？Too young, too simple。
下面这些情况，**「会让索引彻底失效」**👇：
❌ 1. 在索引列上做函数或计算
SELECT * FROM user WHERE YEAR(created_at) = 2024;
👉 索引全废。MySQL 需要对每一行都执行函数，无法走索引。
✅ 优化：
SELECT * FROM user
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
❌ 2. 模糊匹配 % 开头
SELECT * FROM user WHERE name LIKE '%abc';
👉 索引无效，因为 % 开头无法确定前缀。
✅ 优化：
SELECT * FROM user WHERE name LIKE 'abc%';
❌ 3. 隐式类型转换
SELECT * FROM user WHERE phone = 123456;
如果 phone 是字符串类型，MySQL 会做隐式转换，索引同样失效。
✅ 优化：
SELECT * FROM user WHERE phone = '123456';
❌ 4. OR 条件混用非索引字段
SELECT * FROM user WHERE age = 18 OR city = 'Beijing';
👉 如果 city 没索引，age 索引也用不了。
✅ 优化： 为 city 加索引，或拆成两次查询再合并结果。
四、那怎么优化呢？🔧
✅ 1. 用 EXPLAIN 看执行计划
别拍脑袋猜。让 MySQL 告诉你它到底用了哪个索引👇
EXPLAIN SELECT * FROM user WHERE name = 'Tom';
看输出里的 key 字段，显示了使用的索引名称。
✅ 2. 用覆盖索引（Covering Index）
当索引列能覆盖查询字段时，MySQL 不用回表，性能更稳！
-- 索引包括 name、age
SELECT name, age FROM user WHERE name = 'Tom';
这就是“「索引即结果」”的最高境界。
✅ 3. 合理分库分表
当数据量爆炸时（千万级以上），再好的索引也扛不住。 此时就该考虑：是不是该水平拆分了？或者冷热数据分层？
五、一些常见的索引设计“潜规则”📏
| 场景 | 索引策略 |
|---|---|
| 高频查询条件 | 建单列或联合索引 |
| 排序字段 | 索引顺序要和 ORDER BY 一致 |
| JOIN 关联字段 | 两边字段类型必须一致 |
| 低区分度字段（性别、状态） | 谨慎建索引，否则浪费空间 |
| 频繁更新字段 | 索引越多，更新越慢（要平衡！） |
六、最后聊点心里话 💬
我以前也觉得索引就是“加快查询”，直到有一天线上一个 LIKE '%xx%' 把 CPU 打到 300%， 我才意识到——「理解索引原理，远比知道语法更重要。」
所以，下次别人问你：
❝
“你一般怎么建索引？遇到过索引失效吗？”
❞
别只回答“建在 where 上”， 你可以笑着说：“看场景，得先确定查询路径。再按最左匹配和覆盖索引原则来。”
是不是瞬间变专业了 😎？
📘 「总结一下：」
✅ 索引是 B+Tree 实现的有序结构 ✅ 建索引要遵循“最左前缀” ✅ 函数、类型转换、前置模糊匹配会让索引失效 ✅ 用 EXPLAIN 看执行计划，用覆盖索引减少回表
有时候面试官不是想听你背知识点， 而是想知道——「你是否真的理解这些原理。」
🧩 「最后的小作业：」
大家可以自己试试这几条 SQL，配合 EXPLAIN 看看执行计划， 感受一下索引是怎么“走”或者“没走”的。 下次面试，你一定能讲得又稳又有料 💪。
✍️ 作者：Tech有道 📮 如果这篇文章让你恍然大悟，点个 「“在看”」 或分享给朋友吧，我们一起成长 🚀
