AI 助理
文档备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

2026-05-08 15
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
PolarClaw，2核4GB
RDS AI 助手，专业版
RDS Agent（兼容OpenClaw），2核4GB
简介： 本文详解WHERE子句索引优化四大核心：最左前缀原则（联合索引须从最左列开始）、范围查询截断（>、<后列失效）、索引列禁用函数/运算（避免隐式转换）、LIKE右模糊有效而左模糊失效。附实战案例与避坑指南，助你告别全表扫描，面试工作双通关。（239字）

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 '%张三%'

如果必须左模糊怎么办？

  1. 全文索引：建 FULLTEXT 索引，用 MATCH ... AGAINST 查询
  2. 倒排索引方案：把字段反转存入新列，查反转后的右模糊
  3. 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 的注意事项：

  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的底层原理与优化 —— 面试必问！

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

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

文章标签：
索引
关系型数据库
MySQL
SQL
存储
没有那多
目录
相关文章
LucianaiB
|
9天前
|
人工智能 JSON 供应链
畅用7个月无影 JVS Claw |手把手教你把JVS改造成「科研与产业地理情报可视化大师」
LucianaiB分享零成本畅用JVS Claw教程（学生认证享7个月使用权），并开源GeoMind项目——将JVS改造为科研与产业地理情报可视化AI助手，支持飞书文档解析、地理编码与腾讯地图可视化，助力产业关系图谱构建。
LucianaiB
23435 9
畅用7个月无影 JVS Claw |手把手教你把JVS改造成「科研与产业地理情报可视化大师」
JEECG
|
13天前
|
人工智能 缓存 BI
Claude Code + DeepSeek V4-Pro 真实评测：除了贵，没别的毛病
JeecgBoot AI专题研究 把 Claude Code 接入 DeepSeek V4Pro，跑完 Skills —— OA 审批、大屏、报表、部署 5 大实战场景后的真实体验 ![](https://oscimg.oschina.net/oscnet/up608d34aeb6bafc47f
JEECG
4510 15
Claude Code + DeepSeek V4-Pro 真实评测：除了贵，没别的毛病
JEECG
|
14天前
|
人工智能 JSON BI
DeepSeek V4 来了！超越 Claude Sonnet 4.5，赶紧对接 Claude Code 体验一把
JeecgBoot AI专题研究 把 Claude Code 接入 DeepSeek V4Pro 的真实体验与避坑记录 本文记录我将 Claude Code 对接 DeepSeek 最新模型（V4Pro）后的真实体验，测试了 Skills 自动化查询和积木报表 AI 建表两个场景——有惊喜，也踩
JEECG
5430 13
兮动人
|
1月前
|
人工智能 自然语言处理 安全
Claude Code 全攻略：命令大全 + 实战工作流（建议收藏）
本文介绍了Claude Code终端AI助手的使用指南，主要内容包括：1)常用命令如版本查看、项目启动和更新；2)三种工作模式切换及界面说明；3)核心功能指令速查表，包含初始化、压缩对话、清除历史等操作；4)详细解析了/init、/help、/clear、/compact、/memory等关键命令的使用场景和语法。文章通过丰富的界面截图和场景示例，帮助开发者快速掌握如何通过命令行和交互界面高效使用Claude Code进行项目开发，特别强调了CLAUDE.md文件作为项目知识库的核心作用。
兮动人
24189 65
Claude Code 全攻略：命令大全 + 实战工作流（建议收藏）