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

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

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

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

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

🎯 面试考点

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

背景引入

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

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

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

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

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

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

核心概念

一、最左前缀原则

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

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

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。

能命中索引的写法：

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

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

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

不能命中索引的写法：

-- ❌ 跳过 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：

-- ⚠️ 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 差异

> 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 找不到了

常见的坑：

-- ❌ 对索引列使用函数，索引失效
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 查询，左边加通配符就废了，右边加通配符就还能用

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

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

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

如果必须左模糊怎么办？

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

实战案例

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

-- 表结构：订单表
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 个值（待支付、已支付、已完成）：

-- 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 条件导致索引失效

-- ❌ 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 的用户，如果在两个分支都满足条件就会出现重复）。

-- ==========================================
-- 场景：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 的注意事项：

第二个分支要加 name != '张三' 排除重复行，因为 UNION ALL 不做去重

如果业务上两个条件的结果集不会重叠（比如按不同状态分类），可以省略去重条件

两个分支都需要有独立的索引支持，否则 UNION ALL 也无法加速

如果数据量小（几百行以内），OR 全表扫描也无所谓，UNION ALL 的优化在数据量大时才明显

避坑指南

⚠️ 真实踩过的坑：

联合索引列顺序错了，建了等于没建
- 把区分度低的列（status、性别）放在最左侧，效果等于全表扫描
- 建议：高区分度列放最左，等值列放前，范围列放后
时间范围查询忘了用范围，硬套函数
- WHERE YEAR(create_time) = 2026 每行都要算函数，索引完全废掉
- 建议：改成 WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'
VARCHAR 列条件不加引号，触发隐式转换
- WHERE phone = 13800138000 看起来没问题，实际上 MySQL 把整列转成数字再比，全表扫描
- 建议：字符串条件一定要加引号，而且代码里最好在 ORM 层做类型检查
OR 没想到会让整条 SQL 走全表
- 只要 OR 里有一列没索引，整条 SQL 放弃所有索引
- 建议：OR 多考虑用 UNION ALL 改写，或给所有 OR 列都建索引

思考题

🤔 互动时间：

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

总结

🎯 面试考点

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

💡 AI 辅助实战

直接把这篇完整文章喂给你的智能体，告诉它：

"把这篇文章里的所有 SQL 优化知识提取成一条 SKILL 指令。以后我写 SQL 的时候你能自动提醒我哪里会踩坑。"

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

全本合集：《每天一个MySQL知识点，百日打怪升级》

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

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