> **百日打怪升级 · 第26天** · 覆盖索引与最左前缀原则

---

昨天翻车⑤留了个钩子——联合索引没走最左前缀导致的失效，大家看懂了。但有哥们儿追问了一个更细的问题：

> "同样都是联合索引 `(city, age, name)`，我查 `city='北京' AND age=25`，同事查 `city='北京' AND name='张三'`，explain 出来他的 Extra 是 `Using index`，我却什么都没显示——明明都走了索引啊，差在哪？"

差的那一步，叫**回表**。今天就把覆盖索引和它背后的设计原则讲透。

加上第9天（WHERE子句优化）和第24天（EXPLAIN解读）都提过最左前缀，今天不讲"什么是"——讲**为什么 B+ 树决定了这个规则**，以及**实际建索引时怎么把这几条原则串起来用**。

---

## 先说回表——覆盖索引的门槛

**第5天**（B+树详解）讲过：InnoDB 是索引组织表，数据存在聚簇索引（主键）的叶子节点上。二级索引的叶子节点存的是**主键值**，不是完整行。

所以走二级索引查完整行，得两步：

```
二级索引找到记录 → 拿主键值 → 回聚簇索引查完整行
```

这第二步就是**回表**。关键是——**回表是随机 IO**。二级索引和聚簇索引的叶子节点大概率不在同一个磁盘页上。数据量大时，回表成了最慢的一环。

```sql
-- 假设 user 有联合索引 (city, age)
-- 这个查询需要回表
EXPLAIN SELECT * FROM user WHERE city = '北京' AND age = 25;
-- 走索引了（type=ref），但 Extra 没有任何标记
-- Extra 什么也不显示 ≠ 没走索引，而是 "走了索引，但回了表"
```

> `Using index` 才是不回表的信号。啥都没写，说明老老实实回了表。

---

## 覆盖索引：把回表省掉

如果你查的所有列都在同一个索引里——二级索引的叶子节点直接包含了你要的全部数据——就不用回表了。

```sql
-- 假设 city 表有联合索引 (city_id, city_name, province_id)

-- 需要回表 ❌
EXPLAIN SELECT * FROM city WHERE city_id = 1001;
-- Extra: 无（NULL，需要回表拿 * 的所有列）

-- 覆盖索引 ✅
EXPLAIN SELECT city_id, city_name FROM city WHERE city_id = 1001;
-- Extra: Using index
```

**`Using index` 是覆盖索引的标志，不是"用了索引"的意思。** 这个是新手最容易搞混的命名——用了索引但回了表，Extra 啥都不显示。只有没回表，才显示 `Using index`。

### 跟第24天的 Extra 表串起来看

**第24天**（EXPLAIN解读）给过一张 Extra 对照表，今天补上"回表"这个维度：

| Extra | 回表了没？ | 怎么理解 |
|-------|-----------|---------|
| `Using index` | 没回表 | 覆盖索引，索引自己就够了 |
| `Using index condition` | 少了回表，但没完全省 | ICP 在引擎层做了 pre-filter，减少了回表次数 |
| `Using where` | 回了表 | Server 层拿到完整行之后再过滤 |
| 啥都不写 | 回了表 | 走了索引，但数据不够又去聚簇索引拿 |
| `Using where; Using index` | 没回表 | 覆盖索引 + Server 层额外过滤 |

### 一个日常场景：空间换 IO

你的订单表经常按 `user_id` 查 `order_amount` 和 `order_status`：

```sql
SELECT order_amount, order_status FROM orders WHERE user_id = 1001;
```

如果只建了 `(user_id)` 索引，流程是：二级索引找到 `user_id=1001` 对应的主键 → 回表取那两列。

改成联合索引 `(user_id, order_amount, order_status)`：

```sql
EXPLAIN SELECT order_amount, order_status FROM orders WHERE user_id = 1001;
-- Extra: Using index  ✅ 不回表
```

`order_amount` 和 `order_status` 直接塞进了索引叶子节点。查询完全不需要碰聚簇索引。

**代价呢？** 索引体积大了——原来只存 `user_id` + 主键，现在多了两列的副本。写入变慢，磁盘占用变多。

覆盖索引的本质是**用空间换 IO**。值不值得取决于场景：一个每天被调几万次的接口，值。一个你跑一次就不看的导出脚本，不值。

---

## 最左前缀的 B+ 树视角

**第9天**（WHERE子句优化）系统讲过最左前缀是啥，**第25天**（索引失效）又把它当成翻车现场复现了一遍。但大部分人只记住了"规则"，没理解**规则从哪里来**。

这里换一个视角——不看规则，看 B+ 树里数据到底怎么排的：

联合索引 `(a, b, c)` 在 B+ 树里排序规则：先比 a，a 相同比 b，b 相同比 c。

所以索引叶子节点上的数据排列长这样：

```
(a=1, b=1, c=1)
(a=1, b=1, c=2)
(a=1, b=2, c=1)
(a=2, b=1, c=1)
(a=2, b=2, c=3)
```

注意 `c` 的值——`1, 2, 1, 1, 3`——**它在一个 `a` 的跨度和 `b` 的跨度内是散乱的**。

所以：

- `WHERE a = 1` → ✅ 在索引树里能定位到 a=1 的那一段连续的叶子节点
- `WHERE a = 1 AND b = 2` → ✅ 在 a=1 的范围里能定位到 b=2
- `WHERE a = 1 AND c = 3` → **a 能用，c 用不了**。因为排完 a 就排 b，c 在 b 这个层级没有序，没法在索引里直接二分查找
- `WHERE b = 2` → **用不了**。跳过了 a，无从开始

最左前缀不是 MySQL 发明的一条"规矩"，**它是 B+ 树排序规则的自然推论**。理解了这个，你就不会把最左前缀背成"必须从第一个列开始"的八股——而是知道：**索引列的顺序决定了索引的有序性，改变了顺序就破坏了有序性。**

### 那列的顺序到底怎么定？

核心就两条，按优先级排序：

**① 等值条件 > 范围条件**

```sql
WHERE a = 1 AND c > 10 AND b = 2
```

MySQL 优化器会把 WHERE 重排成 `a = 1 AND b = 2 AND c > 10`。`a` 和 `b` 是等值条件，`c` 是范围。索引应该建 `(a, b, c)`——等值放前面，范围放最后。一旦范围条件用上了，它后面的列就失效了（第9天讲的"范围查询后列失效"）。

**② 区分度高的 > 区分度低的**

```sql
-- 联合索引 (gender, user_name)
-- gender 只有 2 个值，区分度低
-- user_name 几乎每条都不同，区分度高
```

`(user_name, gender)` 比 `(gender, user_name)` 好——高区分度的列在前，能更快缩小范围。但这条优先级低于①：等值条件必须先安排，然后才考虑区分度。

### ❗ 一个容易忽视的细节：范围条件的位置不只看类型，还要看值

```sql
-- 联合索引 (category_id, price)

-- 场景 A
WHERE category_id = 10 AND price > 100  -- category_id 等值 ✅

-- 场景 B
WHERE category_id > 5 AND price = 200    -- category_id 范围，price 索引失效 ❌
```

同样都是等值+范围的结构，场景 B 因为 category_id 是范围，直接导致 price 的索引位用不上。

这就是设计索引时最容易犯的错：**只盯着"哪些字段"而没看"怎么查"**。

---

## 一个实战：把上面所有原则串起来

有个帖子表，核心查询长这样：

```sql
SELECT id, title FROM posts
WHERE site_id = 1
  AND category_id = 10
  AND status = 'published'
  AND created_at > '2026-05-01'
ORDER BY created_at DESC
LIMIT 20;
```

索引怎么设计？

**第一步：挑字段**
- `site_id` — 等值，区分度中等（约 100 个站点）→ 放最左
- `category_id` — 等值，区分度中等（约 50 个分类）→ 放第二
- `status` — 等值，区分度低（3 个状态）→ 放第三
- `created_at` — 范围 → 放最后（因为范围条件后的列失效，它正好不需要后面有列）

**第二步：决定要不要覆盖**
查询只拿 `id` 和 `title`。如果把 `title` 塞进索引，Extra 能显示 `Using index`，彻底省掉回表。但 `title` 一般是 VARCHAR(200+)——索引会膨胀不少。

取舍：高频接口 → 加。低频场景 → 不加。

**第三步：ORDER BY 走索引吗？**

索引 `(site_id, category_id, status, created_at)` 里，到了 `created_at` 这一层，同一 `(site_id, category_id, status)` 内的数据已经按 `created_at` 天然有序了。所以 `ORDER BY created_at DESC` 可以直接走索引倒序遍历，Extra 不会出现 `Using filesort`。

---

## 延伸一招：前缀索引

聊完"前缀"，还有一个常见面试点——**前缀索引**。

当你要给一个很长的字符串列（比如 `VARCHAR(255)`）建索引时，可以只取前 N 个字符来建索引：

```sql
-- 对整个列建索引（索引大，占空间）
INDEX idx_email (email)

-- 只对前 10 个字符建索引（索引小，但可能精度不够）
INDEX idx_email_prefix (email(10))
```

**优点**：索引体积大幅缩小，写入更快，buffer pool 能缓存更多索引页。

**缺点**：可能不精确——不同的 email 前 10 个字符可能一样，需要回表做二次判断。

怎么确定取多长合适？算一下**选择性**：

```sql
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity FROM user;
SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_selectivity FROM user;
SELECT COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS prefix_selectivity FROM user;
```

通常让前缀选择性 ≥ 95% 的完整列选择性就够用了。如果完整选择性是 0.98，前缀选择性到 0.95 以上就可以接受。

> **面试考点**：被问到"长字符串怎么建索引"——前缀索引是标准答案之一，另一个是哈希索引（存一列哈希值做索引）。前缀索引的代价是：不能用于 ORDER BY 和 GROUP BY，也不能用于覆盖索引。

---

## 面试一句话总结

面试官问"覆盖索引和最左前缀"——**有一条红线能串起来：**

> **B+ 树按索引列顺序排序——决定了最左前缀规则；利用这个有序性，把查询需要的列塞进索引——就是覆盖索引。**

展开说三句就够了：
1. 覆盖索引查 Extra 有没有 `Using index`，核心价值是**省回表**
2. 最左前缀不是"规矩"，是 **B+ 树排序规则的自然结果**
3. 联合索引列序：**等值条件 > 区分度 > 范围条件**

---

## 🤖 AI实战工具箱

> 覆盖索引的回表省掉了多少 IO？前缀索引 N 取多少合适？让 AI 给你造个测试环境，亲手跑一遍。

**复制以下内容发给 AI（Claude、GPT 等）：**

> 帮我创建一个 MySQL 测试表，50 万行数据，包含 id(主键)、user_id、order_amount、order_status、email 五个字段。
>
> 分别演示以下场景并给出 EXPLAIN：
> 1. 只建 user_id 索引，查 user_id + order_amount（需要回表）
> 2. 建联合索引 (user_id, order_amount, order_status)，查 user_id + order_amount + order_status（覆盖索引）
> 3. 对 email 字段分别做完整索引和前缀索引 email(10)，对比索引大小和查询表现
>
> 重点看 Extra 列的变化。

---

**下期预告**：联合索引的设计原则——明明建了索引，怎么还是慢？因为你顺序搞反了。

> 评论区聊聊：你有没有为了省回表把所有字段都塞进索引的经历？结果怎么样了？


昨天翻车⑤留了个钩子 —— 联合索引没走最左前缀导致的失效，大家看懂了。但有哥们儿追问了一个更细的问题：

同样建了联合索引，为什么你 Extra 是 Using where，我是 Using index？

本文深入解析覆盖索引与最左前缀原则：揭示`Using index`本质是避免回表（省随机IO），而非单纯“用了索引”；阐明最左前缀实为B+树有序存储的自然结果——联合索引按列序排序，跳过前导列则无法高效定位。强调建索引核心逻辑：等值条件优先、高区分度次之、范围条件置尾，并结合实战案例串讲设计取舍。

后端

数据库

本文深入解析MySQL覆盖索引与最左前缀原则。覆盖索引通过将查询列包含在索引中，避免回表操作，提升性能，其标志是EXPLAIN的Extra列出现“Using index”。最左前缀原则源于B+树的数据排序规则，要求查询条件从索引最左列开始才能有效利用索引。设计联合索引时，应优先将等值查询列置于范围查询列之前，并考虑列的选择性。合理运用这些原则能显著优化数据库查询效率。

MySQL

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


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

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

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

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

社区新版编辑器体验调研

本文深入解析MySQL覆盖索引与最左前缀原则。覆盖索引通过将查询列包含在索引中，避免回表操作，提升性能，其标志是EXPLAIN的Extra列出现“Using index”。最左前缀原则源于B+树的数据排序规则，要求查询条件从索引最左列开始才能有效利用索引。设计联合索引时，应优先将等值查询列置于范围查询列之前，并考虑列的...

同样建了联合索引，为什么你 Extra 是 Using where，我是 Using index？-腾讯云开发者社区-腾讯云

同样建了联合索引，为什么你 Extra 是 Using where，我是 Using index？

同样建了联合索引，为什么你 Extra 是 Using where，我是 Using index？

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐