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

---

大家好，我是一名拥有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：区分度低的列建索引**
```sql
-- 性别字段，只有"男""女""未知"
CREATE INDEX idx_gender ON user(gender);  -- 别建！选择性≈0

-- 手机号，每个用户都不同
CREATE INDEX idx_phone ON user(phone);   -- 必须建！选择性≈1
```

**坑2：索引列参与了运算**
```sql
-- 错误：索引列用了函数
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查看执行计划的区别

```sql
-- 创建测试表
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，会走索引

---

### 场景二：索引列参与运算导致失效

对比索引在有运算和无运算下的执行计划

```sql
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）

**部署方式**：
```bash
# 通过 npx 安装
npx -y mcp-db-analyzer
```

**配合 AI 使用**：
```markdown
提示词示例：

"请帮我分析以下 SQL 的执行计划，识别性能问题：SELECT * FROM orders WHERE status=1 AND create_time > '2026-01-01'"

AI 会返回：
- EXPLAIN 结果：type=ALL, rows=500000, Using filesort
- 问题：全表扫描 + 文件排序
- 建议：给 status + create_time 建联合索引
```

---

## 避坑指南

> ⚠️ 真实踩过的坑：

1. **不要给"状态类"字段建索引**
   - 状态字段只有0/1、已读/未读等几种值
   - 选择性接近0，加了也是全表扫描
   - 建议：业务层面过滤，不要依赖索引

2. **不要在长字符串上建全文索引之外的索引**
   - 几百个字符的TEXT字段，索引体积太大
   - 建议：用前缀索引，或者全文索引

3. **联合索引注意最左前缀**
   - 建立了(a,b,c)索引，相当于有(a)、(a,b)、(a,b,c)三种索引
   - 但不能用(b,c)，否则索引失效
   - 建议：把区分度高的放前面

---

## 思考题

> 🤔 互动时间：

1. 如果一个表有1000万条数据，其中"状态"字段只有"启用"和"禁用"两种值，应该给状态字段建索引吗？
2. 如果你发现某个查询很慢，但是WHERE条件涉及的字段都已经建了索引，可能是什么原因？
3. 如何快速查看一个字段的选择性？

---

## 总结

> 🎯 **面试考点**

- **索引选择性**：唯一值/总行数，越接近1越好
- **失效场景**：区分度低、索引列参与运算、隐式类型转换
- **建索引原则**：WHERE/JOIN/ORDER BY/GROUP BY常用字段优先建，区分度低的别建
- **避坑指南**：状态类字段、长字符串、联合索引最左前缀

---

**下期预告**：SQL优化工具推荐 —— 面试必问！
**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  您的关注是我每日更新的动力

---

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


做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

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

本文为DBA老兵总结的索引优化实战指南：聚焦“何时建、何时不建”核心问题。详解索引选择性（唯一值/总行数）、失效场景（低区分度、函数运算、隐式转换）及建索引黄金法则——WHERE/JOIN/ORDER BY/GROUP BY高频字段优先，状态类、低选择性列坚决不建。附EXPLAIN实战分析与AI辅助诊断技巧。（239字）

编程语言

索引优化实战指南：掌握何时建索引的关键技巧。区分度高、常用于WHERE/JOIN/ORDERBY/GROUPBY的字段建议建索引，选择性低的字段避免建索引。注意索引列参与运算会导致失效，联合索引需遵循最左前缀原则。通过EXPLAIN分析执行计划，优化查询性能。

数据库

MySQL

腾讯云数据库特惠，新用户首单0.5折起


2026采购季 | AI焕新·智启新局

tencentdb-catalog

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

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

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐