索引优化：何时建索引、何时不建
大家好，我是一名拥有10年以上经验的DBA老兵。
做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。
让我们开始今天的第6天内容。
🎯 面试考点
- 索引在哪些场景下会失效？
- 如何判断一个字段是否需要建索引？
- 索引的选择性（Cardinality）是什么？如何计算？
- 为什么有些索引加了等于没加？
背景引入
💡 说白了：索引不是你想加，想加就能加
你有没有遇到过这种情况：
- 明明加了索引，查询还是慢得像蜗牛
- 表才几万条数据，一跑查询就卡死
- 开发说"给我加个索引"，加完发现然并卵
说实话，90%的索引问题，都源于两个问题：不该加的加了，该加的没加。
今天的目标：学会判断什么时候该建索引，什么时候不该建。
核心概念
什么是索引的选择性？
💡 说白了：选择性，就是看这一列的数据"有多不一样"
选择性 = 唯一值数量 / 总行数
- 性别：只有"男""女"两个值，选择性 = 2/N ≈ 0
- 手机号：几乎每个人都不一样，选择性 ≈ 1
面试必问：
- 什么是索引的选择性？
- 如何计算索引的选择性？
- 选择性低的索引为什么无效？
面试解答：
Q: 什么是索引的选择性？
索引的选择性是指索引列中不重复值的数量与总行数的比值。选择性越接近1，说明数据越分散，索引效率越高；选择性越接近0，说明数据越集中，索引效率越低。
Q: 选择性低的索引为什么无效？
当选择性很低时（比如性别字段只有男女两个值），MySQL优化器倾向于全表扫描而不是走索引。因为索引的B+树只是为了快速定位一小部分数据，如果索引返回的数据量超过全表的20%-30%，优化器就认为全表扫描更划算。
何时该建索引？
记住这个公式：WHERE + JOIN + ORDER BY + GROUP BY = 索引列
| 场景 | 是否该建 | 原因 |
|---|---|---|
| WHERE条件常用 | ✅ 必须建 | 快速定位数据 |
| JOIN关联字段 | ✅ 必须建 | 减少嵌套循环次数 |
| ORDER BY排序 | ✅ 必须建 | 避免filesort |
| GROUP BY分组 | ✅ 必须建 | 避免临时表 |
| 区分度高的列 | ✅ 优先建 | 选择性接近1 |
| 区分度低的列 | ❌ 别建 | 浪费空间，没效果 |
何时不该建索引？
⚠️ 真实踩过的坑：这两个坑，我踩过不下10次
坑1：区分度低的列建索引
-- 性别字段，只有"男""女""未知"
CREATE INDEX idx_gender ON user(gender); -- 别建！选择性≈0
-- 手机号，每个用户都不同
CREATE INDEX idx_phone ON user(phone); -- 必须建！选择性≈1
坑2：索引列参与了运算
-- 错误：索引列用了函数
SELECT * FROM user WHERE YEAR(create_time) = 2026;
-- 正确：索引列单独使用
SELECT * FROM user WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
实战案例
场景一：验证选择性对索引的影响
创建测试表，插入10万条数据，分别在性别和手机号上建索引，用EXPLAIN查看执行计划的区别
-- 创建测试表
CREATE TABLE test_index (
id INT PRIMARY KEY AUTO_INCREMENT,
gender VARCHAR(10),
phone VARCHAR(20),
create_time DATETIME
);
-- 插入10万条测试数据（MySQL 8.0+ 递归CTE）
INSERT INTO test_index (gender, phone, create_time)
WITH RECURSIVE cte AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 100000
)
SELECT
CASE WHEN RAND() < 0.5 THEN '男' ELSE '女' END,
CONCAT('138', LPAD(FLOOR(RAND() * 100000000), 8, '0')),
DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 365) DAY)
FROM cte;
-- 查看数据分布
SELECT gender, COUNT(*) as cnt FROM test_index GROUP BY gender;
结果预判：
- gender 字段只有两种值，选择性≈2/100000=0.00002，EXPLAIN 会显示全表扫描
- phone 字段唯一值多，选择性≈1，会走索引
场景二：索引列参与运算导致失效
对比索引在有运算和无运算下的执行计划
ALTER TABLE test_index ADD INDEX `IDX_CREATE_TIME` (`create_time`);
-- 测试1：索引列参与运算（失效）
EXPLAIN SELECT * FROM test_index WHERE YEAR(create_time) = 2026;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_index | NULL | ALL | NULL | NULL | NULL | NULL | 100000 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
-- 测试2：索引列单独使用（生效）
EXPLAIN SELECT * FROM test_index WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | test_index | NULL | range | IDX_CREATE_TIME | IDX_CREATE_TIME | 6 | NULL | 100000 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
结果分析：
- 测试1：type=ALL（全表扫描），索引完全失效
- 测试2：type=range（走索引），会根据实际扫描的数据量，动态选择是否使用索引，如果匹配到的数据很多效率同样不高
💡 AI 辅助实战
🤖 AI 能不能帮我判断索引有没有生效？
能！通过 MCP（Model Context Protocol）连接数据库，批量执行 EXPLAIN，让大模型分析执行计划：
mcp-db-analyzer 使用姿势
这是一个开源的多数据库分析 MCP Server，支持：
- 检查索引问题（detect index problems）
- 分析查询计划（explain query plans）
- 检测表膨胀（analyze table bloat）
部署方式：
# 通过 npx 安装
npx -y mcp-db-analyzer
配合 AI 使用：
提示词示例：
"请帮我分析以下 SQL 的执行计划，识别性能问题：SELECT * FROM orders WHERE status=1 AND create_time > '2026-01-01'"
AI 会返回：
- EXPLAIN 结果：type=ALL, rows=500000, Using filesort
- 问题：全表扫描 + 文件排序
- 建议：给 status + create_time 建联合索引
避坑指南
⚠️ 真实踩过的坑：
不要给"状态类"字段建索引
- 状态字段只有0/1、已读/未读等几种值
- 选择性接近0，加了也是全表扫描
- 建议：业务层面过滤，不要依赖索引
不要在长字符串上建全文索引之外的索引
- 几百个字符的TEXT字段，索引体积太大
- 建议：用前缀索引，或者全文索引
联合索引注意最左前缀
- 建立了(a,b,c)索引，相当于有(a)、(a,b)、(a,b,c)三种索引
- 但不能用(b,c)，否则索引失效
- 建议：把区分度高的放前面
思考题
🤔 互动时间：
- 如果一个表有1000万条数据，其中"状态"字段只有"启用"和"禁用"两种值，应该给状态字段建索引吗？
- 如果你发现某个查询很慢，但是WHERE条件涉及的字段都已经建了索引，可能是什么原因？
- 如何快速查看一个字段的选择性？
总结
🎯 面试考点
- 索引选择性：唯一值/总行数，越接近1越好
- 失效场景：区分度低、索引列参与运算、隐式类型转换
- 建索引原则：WHERE/JOIN/ORDER BY/GROUP BY常用字段优先建，区分度低的别建
- 避坑指南：状态类字段、长字符串、联合索引最左前缀
下期预告：SQL优化工具推荐 —— 面试必问！
全本合集：《每天一个MySQL知识点，百日打怪升级》 您的关注是我每日更新的动力
有问题欢迎评论区交流，明天见！
