> WHERE子句优化技巧：索引列使用与最左前缀原则

---

大家好，我是一名拥有10年以上经验的DBA老兵。

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

让我们开始今天的第9天内容。

---

> 🎯 **面试考点**

- WHERE 条件里写法不同，索引命中结果差异有多大？
- 最左前缀原则是什么？为什么必须从最左列开始？
- 哪些写法会导致索引直接失效？
- 范围查询之后的列，索引还能用吗？

---

## 背景引入

> 💡 说白了：WHERE 写得好，索引飞起来；WHERE 写得烂，全表扫描到天亮

你有没有遇到过这种情况：

- 明明建了索引，SQL 还是跑得比蜗牛慢？
- EXPLAIN 一看，`type` 是 `ALL`，全表扫描？
- 改了一个 WHERE 条件写法，速度瞬间快了10倍？

说实话，99% 的慢 SQL 根源，就在 WHERE 子句里。

不是索引没建，而是 **WHERE 写法让索引哑火了**。

**今天的目标**：掌握 WHERE 子句的优化技巧，面试必问、工作必用。

---

## 核心概念

### 一、最左前缀原则

> 💡 **说白了**：联合索引就像一把组合密码锁，必须从第一位开始拨，跳过第一位直接拨第二位，锁不开

假设有这么一张表和索引：

```sql
CREATE TABLE user (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50),
    age    INT,
    city   VARCHAR(50),
    status TINYINT,
    INDEX idx_name_age_city (name, age, city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

联合索引 `idx_name_age_city`，列顺序是 `name → age → city`。

**能命中索引的写法**：

```sql
-- ✅ 完整命中，走 idx_name_age_city
WHERE name = '张三' AND age = 25 AND city = '北京'

-- ✅ 命中 name + age 两列
WHERE name = '张三' AND age = 25

-- ✅ 仅命中 name 一列
WHERE name = '张三'
```

**不能命中索引的写法**：

```sql
-- ❌ 跳过 name，索引失效
WHERE age = 25 AND city = '北京'

-- ❌ 跳过 name，索引失效
WHERE city = '北京'

-- ❌ 跳过 name，索引失效
WHERE age = 25
```

**面试必问**：
- 联合索引 `(a, b, c)`，`WHERE b = 1 AND c = 1` 能命中索引吗？
- 联合索引 `(a, b, c)`，`WHERE a = 1 AND c = 1` 能命中哪些列？

> **面试解答**：

**Q: 联合索引 `(a, b, c)`，`WHERE b = 1 AND c = 1` 能命中索引吗？**

> 不能。最左前缀原则要求必须从最左列 `a` 开始，跳过 `a` 直接用 `b`，索引完全失效，走全表扫描。

**Q: 联合索引 `(a, b, c)`，`WHERE a = 1 AND c = 1` 能命中哪些列？**

> 只能命中 `a` 列，`c` 列无法命中。原因是 `b` 列没有出现在条件中，索引在 `b` 处断了，后续的 `c` 无法走索引范围查找，但 MySQL 会用索引条件下推（ICP）在 `a` 的范围内过滤 `c`。

---

### 二、范围查询之后的列失效

> 💡 **说白了**：范围查询就像路上的一个路障，路障之后的索引列全部失效

还是上面的表，同样是 `idx_name_age_city`：

```sql
-- ⚠️ age 用了范围查询，city 列索引失效
WHERE name = '张三' AND age > 20 AND city = '北京'

-- ✅ age 用了等值查询，city 列索引有效
WHERE name = '张三' AND age = 25 AND city = '北京'
```

**规律总结**：

```
联合索引列：name  →  age  →  city
                      ↑
              在这里用了 >、<、BETWEEN、LIKE 'xxx%'
                      ↓
              city 之后的列索引全部失效
```

可以通过 EXPLAIN 对比等值查询和范围查询的 key_len 差异

```sql
> EXPLAIN SELECT * FROM user WHERE name = '张三' AND age > 20 AND city = '北京';
+----+-------------+-------+------------+-------+---------------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_name_age_city,idx_age | idx_name_age_city | 208     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+-------------------+---------+------+------+----------+-----------------------+

> EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 20 AND city = '北京';
+----+-------------+-------+------------+------+---------------------------+-------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys             | key               | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------------+-------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_city,idx_age | idx_name_age_city | 411     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------------+-------------------+---------+-------------------+------+----------+-------+
```

**Q: 为什么范围查询之后的列索引失效？**

> B+ 树索引是按顺序排列的。等值查询能精确定位到某一个节点，后续列可以继续在该节点下排序查找。但范围查询返回的是一段区间，这段区间内后续列的值是无序的（比如 `age > 20` 范围内，`city` 的值是乱序的），所以无法再走索引。

---

### 三、索引列上的操作导致失效

> 💡 **说白了**：索引列上套了函数、参与了运算，索引就相当于被"加密"了，MySQL 找不到了

常见的坑：

```sql
-- ❌ 对索引列使用函数，索引失效
WHERE YEAR(create_time) = 2026
WHERE LEFT(name, 2) = '张三'
WHERE LENGTH(name) > 5

-- ✅ 改写为范围查询，索引有效
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'

-- ❌ 对索引列做运算，索引失效
WHERE age + 1 = 26
WHERE id / 2 = 100

-- ✅ 把运算移到右侧，索引有效
WHERE age = 25
WHERE id = 200

-- ❌ 隐式类型转换，索引失效
WHERE phone = 13800138000  -- phone 是 VARCHAR，给的是数字

-- ✅ 类型匹配，索引有效
WHERE phone = '13800138000'
```

> **面试解答**：

**Q: 为什么对索引列使用函数会导致索引失效？**

> 索引存储的是列的原始值，对列加函数后，MySQL 无法直接用 B+ 树去查函数的返回值（函数结果没有建索引），只能全表扫描逐行计算函数值再比较。解决办法：把条件改写为对原始列的直接判断，或者针对函数建函数索引（MySQL 8.0+ 支持）。

---

### 四、LIKE 模糊查询的边界

> 💡 **说白了**：LIKE 查询，左边加通配符就废了，右边加通配符就还能用

```sql
-- ✅ 右模糊，索引有效（相当于范围查询）
WHERE name LIKE '张%'

-- ❌ 左模糊，索引失效
WHERE name LIKE '%三'

-- ❌ 两端模糊，索引失效
WHERE name LIKE '%张三%'
```

**如果必须左模糊怎么办？**

1. **全文索引**：建 FULLTEXT 索引，用 `MATCH ... AGAINST` 查询
2. **倒排索引方案**：把字段反转存入新列，查反转后的右模糊
3. **Elasticsearch**：把数据同步到 ES，用全文检索解决

---

## 实战案例

### 场景一：联合索引顺序设计不当

```sql
-- 表结构：订单表
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT       NOT NULL,
    status      TINYINT   NOT NULL COMMENT '1:待支付 2:已支付 3:已完成',
    create_time DATETIME  NOT NULL,
    amount      DECIMAL(10,2),
    -- 错误的索引设计：status 区分度低，放最左侧
    INDEX idx_wrong  (status, user_id, create_time),
    -- 正确的索引设计：user_id 区分度高，放最左侧
    INDEX idx_right  (user_id, status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 最常用的查询
SELECT * FROM orders WHERE user_id = 10086 AND status = 2 ORDER BY create_time DESC;

-- EXPLAIN 对比：
-- idx_wrong：status 只有 3 个值，区分度极低，命中行数多，性能差
-- idx_right：user_id 先过滤，行数大幅减少，性能好
```

**EXPLAIN 对比结果**：

假设 orders 表有 100 万行数据，user_id 有 1 万个不同值（每个 user_id 约 100 条），status 只有 3 个值（待支付、已支付、已完成）：

```sql
-- idx_wrong（status, user_id, create_time）
-- status=2 匹配约 1/3 的数据 => rows ≈ 33 万
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 2 ORDER BY create_time DESC;
-- +------+-----------+--------+-------+
-- | type | key       | rows   | Extra |
-- +------+-----------+--------+-------+
-- | ref  | idx_wrong | 333333 | NULL  |
-- +------+-----------+--------+-------+

-- idx_right（user_id, status, create_time）
-- user_id=10086 匹配约 1/10000 的数据 => rows ≈ 100
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 2 ORDER BY create_time DESC;
-- +------+-----------+------+-------+
-- | type | key       | rows | Extra |
-- +------+-----------+------+-------+
-- | ref  | idx_right | 100  | NULL  |
-- +------+-----------+------+-------+
```

**关键差异**：

| 对比项 | idx_wrong (status, user_id, create_time) | idx_right (user_id, status, create_time) |
|--------|------------------------------------------|------------------------------------------|
| 最左列区分度 | status 只有 3 个值，极低 | user_id 1 万个值，很高 |
| 预估扫描行数 | **333,333**（33% 的表） | **100**（0.01% 的表） |
| SQL 执行时间 | 慢 | 快 |
| 结论 | 区分度低的列放最左 ≈ 索引失效 | 区分度高的列放最左 = 索引高效 |

> 💡 **面试考点**：两个索引 type 都是 ref，key_len 也一样，但 `rows` 差了 3000 倍。这就是为什么面试常问"联合索引列顺序怎么排"——只看 type 和 key 不够，**rows 才能反映真实差距**。

---

### 场景二：用 OR 条件导致索引失效

```sql
-- ❌ OR 条件，只要有一列没有索引，整条 SQL 走全表扫描
SELECT * FROM user WHERE name = '张三' OR age = 25;

-- ✅ 改写为 UNION ALL，两列分别走各自的索引
SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE age = 25 AND name != '张三';
```

> **关键知识点**：
>
> `UNION ALL` 会把两个 SELECT 的结果直接拼接在一起返回，**不做去重**（相比 `UNION` 少了排序去重的开销，性能更好）。
>
> 改写为 UNION ALL 后，每个分支独立选择索引，互不干扰。同时第二个分支加上 `name != '张三'`，防止两个结果集之间出现重复行（比如刚好有个 name='张三'、age=25 的用户，如果在两个分支都满足条件就会出现重复）。

```sql
-- ==========================================
-- 场景：OR 条件（全表扫描）→ UNION ALL（索引访问）
-- ==========================================

-- 前提：已有 idx_name_age_city(name, age, city) 联合索引
-- 问题：age 列在联合索引中不是最左列，OR 的第二分支无法走索引

-- 第一步：给 age 单独建索引，让第二分支也能走索引
ALTER TABLE user ADD INDEX idx_age (age);

-- ❌ OR 写法：age 条件不能走 idx_name_age_city，MySQL 被迫全表扫描
EXPLAIN SELECT * FROM user WHERE name = '张三' OR age = 25;

-- EXPLAIN 结果解读：
-- +------+------+-------------+
-- | type | key  | Extra       |
-- +------+------+-------------+
-- | ALL  | NULL | Using where |
-- +------+------+-------------+
-- type=ALL → 全表扫描，rows=表总行数
-- 原因：age=25 无法使用 idx_name_age_city（跳过了 name），OR 要求整条 SQL 走同一个执行计划

-- ✅ UNION ALL 改写：两个分支各走各的索引
EXPLAIN
SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE age = 25 AND name != '张三';

-- EXPLAIN 结果解读（重点看两个分支的 type 和 key）：
--
-- 第一个分支：SELECT * FROM user WHERE name = '张三'
-- +------+-------------------+----------------------+
-- | type | key               | Extra                |
-- +------+-------------------+----------------------+
-- | ref  | idx_name_age_city | Using index condition |
-- +------+-------------------+----------------------+
-- type=ref → 通过联合索引最左列 name 精确定位，性能最优
--
-- 第二个分支：SELECT * FROM user WHERE age = 25 AND name != '张三'
-- +------+---------+-------------+
-- | type | key     | Extra       |
-- +------+---------+-------------+
-- | ref  | idx_age | Using where |
-- +------+---------+-------------+
-- type=ref → 通过 idx_age 精确定位 age=25 的行
-- Extra=Using where → 对 name != '张三' 做过滤，剔除两个分支的重复数据

-- 对比总结：
-- ┌──────────────┬──────────┬──────────────────────────────┐
-- │ 对比项       │ OR 查询  │ UNION ALL 改写               │
-- ├──────────────┼──────────┼──────────────────────────────┤
-- │ 扫描方式     │ 全表扫描 │ 两次索引回表                 │
-- │ 扫描行数     │ 全表     │ 仅满足条件的行               │
-- │ 数据量大时   │ 极慢     │ 稳定                         │
-- │ 适用场景     │ 小表     │ 大表 + 有对应独立索引        │
-- └──────────────┴──────────┴──────────────────────────────┘
```

> 💡 **使用 UNION ALL 的注意事项**：
>
> 1. 第二个分支要加 `name != '张三'` 排除重复行，因为 UNION ALL 不做去重
> 2. 如果业务上两个条件的结果集不会重叠（比如按不同状态分类），可以省略去重条件
> 3. 两个分支都需要有独立的索引支持，否则 UNION ALL 也无法加速
> 4. 如果数据量小（几百行以内），OR 全表扫描也无所谓，UNION ALL 的优化在数据量大时才明显

---

## 避坑指南

> ⚠️ 真实踩过的坑：

1. **联合索引列顺序错了，建了等于没建**
   - 把区分度低的列（status、性别）放在最左侧，效果等于全表扫描
   - 建议：**高区分度列放最左**，等值列放前，范围列放后

2. **时间范围查询忘了用范围，硬套函数**
   - `WHERE YEAR(create_time) = 2026` 每行都要算函数，索引完全废掉
   - 建议：改成 `WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'`

3. **VARCHAR 列条件不加引号，触发隐式转换**
   - `WHERE phone = 13800138000` 看起来没问题，实际上 MySQL 把整列转成数字再比，全表扫描
   - 建议：字符串条件**一定要加引号**，而且代码里最好在 ORM 层做类型检查

4. **OR 没想到会让整条 SQL 走全表**
   - 只要 OR 里有一列没索引，整条 SQL 放弃所有索引
   - 建议：OR 多考虑用 `UNION ALL` 改写，或给所有 OR 列都建索引

---

## 思考题

> 🤔 互动时间：

1. 联合索引 `(a, b, c)`，`WHERE a = 1 AND b > 2 AND c = 3`，MySQL 实际命中几列索引？`c` 列是否完全没有用处？
2. 线上有个字段存的是手机号，类型是 `VARCHAR(20)`，但查询时总是忘记加引号，会有什么后果？怎么防止？
3. 同一个 WHERE 条件，加了 `FORCE INDEX` 和不加，结果可能一样吗？什么时候会一样？

---

## 总结

> 🎯 **面试考点**

- **最左前缀原则**：联合索引必须从最左列开始，跳过中间列则该列之后失效
- **范围查询截断**：`>`、`<`、`BETWEEN`、`LIKE 'xxx%'` 之后的列索引失效
- **索引列不能套函数/运算**：会导致索引失效，改写为对原始值的范围判断
- **隐式类型转换**：VARCHAR 列条件不加引号，会触发全表扫描
- **左模糊失效**：`LIKE '%xxx'` 走全表，`LIKE 'xxx%'` 可以走索引
- **OR 要小心**：OR 两侧都需要有索引，否则考虑 `UNION ALL` 改写
- **联合索引设计原则**：高区分度列靠前，等值列在范围列之前

---

## 💡 AI 辅助实战

> 直接把这篇完整文章喂给你的智能体，告诉它：
>
> *"把这篇文章里的所有 SQL 优化知识提取成一条 SKILL 指令。以后我写 SQL 的时候你能自动提醒我哪里会踩坑。"*

---

**下期预告**：JOIN的底层原理与优化 —— 面试必问！

---

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


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

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

本文详解WHERE子句索引优化四大核心：最左前缀原则（联合索引须从最左列开始）、范围查询截断（&gt;、&lt;后列失效）、索引列禁用函数/运算（避免隐式转换）、LIKE右模糊有效而左模糊失效。附实战案例与避坑指南，助你告别全表扫描，面试工作双通关。（239字）

后端

数据库

职业发展

掌握WHERE子句优化技巧：遵循最左前缀原则，避免索引失效。合理设计联合索引顺序，区分度高的列放最左。注意范围查询后的列失效问题，避免索引列上的函数运算和隐式类型转换。LIKE查询右模糊有效，OR条件可改写为UNION ALL提升性能。

数据同步

智能体

MySQL

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


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

datainlong

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

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

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

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

社区新版编辑器体验调研

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

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐