【第6天】每天一个MySQL知识点，百日打怪升级-阿里云开发者社区

索引优化：何时建索引、何时不建

大家好，我是一名拥有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知识点，百日打怪升级》您的关注是我每日更新的动力

有问题欢迎评论区交流，明天见！

【第6天】每天一个MySQL知识点，百日打怪升级