本文是 MySQL 知识系列的第八篇——SQL 调优专题第二部分。上一篇讲了 EXPLAIN 执行计划、type 性能 7 档、Extra 关键字、ORDER BY 排序原理。这一篇把所有”调优动作”讲透：怎么设计索引、怎么让索引不失效、慢查询怎么定位、COUNT 该用哪个。
最左前缀匹配原则
定义：MySQL 从左向右匹配联合索引，遇到范围查询就停止匹配。
-- 假设联合索引 (a, b, c, d)
WHERE a = 1 AND b = 2 AND c > 3 AND d = 4
-- 索引命中: a, b, c（c 是范围）
-- 索引失效: d（因为 c 是范围，d 用不到）
-- 调整索引顺序为 (a, b, d, c)
-- 索引命中: a, b, d, c（c 也是范围，所以仍然 stop）
-- 但 a、b、d 都能用上💡 联合索引的列顺序设计远比想象中重要——
(a, b, d, c)和(a, b, c, d)看似相近，实际效果完全不同。
关键细节
1. 范围查询”右侧全部失效”，但 IN 是例外：
WHERE a = 1 AND b = 2 AND c IN (3, 5) AND d = 4
-- 索引命中: a, b, c, d（IN 等价于多个等值，d 不失效）
-- MySQL 优化器会把 IN 优化成可识别形式2. 联合索引必须最左元素出现在条件中：
-- 联合索引 (a, b, c)
WHERE b = 2 AND c = 3 -- ✗ 不会命中（没有 a）
WHERE a = 1 AND c = 3 -- ✗ 只命中 a（跳过 b，c 失效）
WHERE a = 1 AND b = 2 -- ✓ 命中 a, b3. 排序顺序的”序”：
联合索引的数据先按 a 排序，再按 b 排序……所以 a 整体是有序的，但 b 在 a 范围内才有序，整体无序。
⚠️ 这就是”用第二个字段条件判断用不到索引”的根本原因。
索引设计三大黄金法则
法则一：联合索引列顺序——三个优先级
-- 经常使用的列优先
-- 选择性高的列优先（基数大）
-- 宽度小的列优先（占用空间小）典型案例：
-- 错误：(name, status)，name 重复值多（基数低），status 只有 2~5 个值
-- 正确：(status, name)，先按 status 过滤大块数据，再按 name 精确定位法则二：联合索引尽量覆盖 WHERE + ORDER BY
-- 一个联合索引同时解决 WHERE 和 ORDER BY
WHERE a = 1 AND b = 2
ORDER BY c
-- 联合索引 (a, b, c) 可以同时满足 WHERE 和 ORDER BY法则三：能用覆盖索引就不回表
-- 假设联合索引 (user_id, status)
-- 业务只需要 user_id 和 status 两个字段
SELECT user_id, status FROM order WHERE user_id = 1;
-- ✓ 覆盖索引，无需回表索引失效的 5 大经典场景
场景一：函数操作
-- 错误：to_days() 作用在索引列 out_date
SELECT * FROM product
WHERE to_days(out_date) - to_days(current_date) <= 30;
-- 正确：把运算放在常量侧
SELECT * FROM product
WHERE out_date <= date_add(current_date, interval 30 day);原理：索引列必须直接参与比较，被函数包裹就破坏索引的有序性。
场景二：隐式类型转换
-- phone 是 VARCHAR，传入 INTEGER
SELECT * FROM user WHERE phone = 13800138000; -- 索引失效
SELECT * FROM user WHERE phone = '13800138000'; -- 索引命中场景三：前导模糊查询
WHERE name LIKE '%abc'; -- ✗ 索引失效
WHERE name LIKE 'abc%'; -- ✓ 索引命中场景四：负向查询
WHERE status != 1; -- ✗ 索引失效（部分情况）
WHERE status <> 1; -- ✗ 索引失效
WHERE status NOT IN (1, 2); -- ✗ 索引失效场景五：OR 条件中有非索引列
-- 假设只有 name 有索引，age 无索引
WHERE name = 'a' OR age = 10; -- ✗ 整个 OR 走全表扫描前缀索引
-- 对列的前 N 个字符建索引
CREATE INDEX idx_name ON user(name(3));适用场景
- 字符串列很长（VARCHAR(255)），全字段建索引空间浪费
- 前 N 个字符的区分度已经足够高
注意点
- InnoDB 中前缀索引长度 ≤ 767 字节（utf8mb4 字符集下约 191 个字符）
- 前缀索引无法用于 ORDER BY / GROUP BY（索引里只有前缀，不完整）
- 前缀索引选择性会降低：不重复的索引值 / 表记录总数
💡 进阶技巧：身份证号前 6 位都相同，可以倒序存储再建立前缀索引，提升区分度：
CREATE INDEX idx_id_reverse ON user(REVERSE(id_card));
联合索引
CREATE INDEX idx_a_b_c ON table(a, b, c);列顺序选择（按优先级排序）
- 经常使用的列优先
- 选择性高（基数大）的列优先
- 宽度小（占用空间小）的列优先
实战模板
-- 业务查询：根据 user_id 查 user 最近的订单
-- 联合索引设计：(user_id, created_at)
-- 同时满足 WHERE user_id = ? AND ORDER BY created_at DESC
CREATE INDEX idx_user_created ON order(user_id, created_at);覆盖索引
覆盖索引：索引上的列已经覆盖了查询需要的所有列（SELECT、WHERE、GROUP BY、ORDER BY 全部在索引中），完全无需回表。
-- 假设索引 (user_id, status, created_at)
-- 查询只用到 user_id 和 status 两个字段
SELECT user_id, status FROM order WHERE user_id = 1;
-- EXPLAIN 会出现 "Using index"💡 为什么”用 SELECT *” 通常是反模式？因为它基本不可能走覆盖索引，必然回表。
慢查询日志：定位慢 SQL
慢查询日志开启
-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启
SET GLOBAL slow_query_log = ON;
-- 慢查询阈值（单位：秒，可设小数）
SET GLOBAL long_query_time = 0.1; -- 100ms
-- 把没走索引的 SQL 也记录下来
SET GLOBAL log_queries_not_using_indexes = ON;
-- 注意：上述命令 MySQL 重启后失效；永久配置需改 my.cnf调优基本思路
- 慢查询日志定位 SQL
- EXPLAIN 分析 SQL 执行计划
- 关注 type（index/ALL 都不好）
- 关注 Extra（Using filesort / Using temporary 必须优化）
- 调整 SQL 或索引
- 再次跑实际执行时间验证
调优工具：mysqldumpslow
# 分析前 5 条最慢 SQL
mysqldumpslow -t 5 /var/lib/mysql/slow.log常用参数：
-s c：按执行次数排序-s t：按总时间排序-s l：按平均时间排序-t N：前 N 条-g pattern：过滤匹配 pattern 的 SQL
调优工具：pt-query-digest（推荐）
pt-query-digest 是 Percona 公司出品的慢查询分析神器，比 mysqldumpslow 功能强大得多。
输出到文件：
pt-query-digest slow-log > slow_log.report输出到数据库（可记录历史）：
pt-query-digest slow-log --review \
h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
--create-review-table \
--review-history t=hostname_slow关键参数：
| 参数 | 含义 |
|---|---|
h | 数据库 host |
D | 数据库名 |
p | 密码 |
P | 端口 |
u | 用户名 |
t | 目标表 |
--create-review-table | 自动创建 review 表 |
--review-history | 历史记录表 |
💡 生产环境推荐：把 pt-query-digest 的输出定期入库（如每天 0 点执行一次），积累数据做慢 SQL 趋势分析。
COUNT 函数选型
SELECT COUNT(*) FROM table_name;
SELECT COUNT(1) FROM table_name;
SELECT COUNT(id) FROM table_name;
SELECT COUNT(name) FROM table_name;三种写法的实际行为
| 写法 | MySQL 实际做的事 | 性能 |
|---|---|---|
COUNT(*) | 直接挑一个索引树，返回该索引树中数据的个数（MySQL 优化） | ⭐⭐⭐ 最优 |
COUNT(1) | 1 是常量，遍历时给每行”赋 1”，但仍会做非空判断 | 略差 |
COUNT(列名) | 统计该列不为 NULL 的行数 | 取决于列上是否有索引 |
💡 结论：直接用
COUNT(*)——MySQL 专门为它做了优化。
高级技巧：一条 SQL 统计多个条件
-- 推荐：用 COUNT(条件 OR NULL)
SELECT
COUNT(release_year = '2006' OR NULL) AS '2006年电影数量',
COUNT(release_year = '2007' OR NULL) AS '2007年电影数量'
FROM film;原理：COUNT() 不统计 NULL 值；条件 = TRUE 时是 1，条件 = FALSE 时是 0，会被 COUNT 计入——所以要 OR NULL 把 FALSE 转成 NULL 排除。
⚠️ 错误写法对比：
-- 错误：COUNT(release_year = '2006') 会把 FALSE 的行也计入 SELECT COUNT(release_year = '2006') AS '2006年电影数量' FROM film;
索引越多越好吗？
当然不是。 索引是有代价的：
| 代价 | 详细 |
|---|---|
| 空间成本 | 每个索引都是一棵 B+ 树，占用磁盘 |
| 写入成本 | INSERT / UPDATE / DELETE 都要维护所有相关索引 |
| 优化器成本 | 可用索引太多，优化器选错索引的概率上升 |
索引设计原则
- 小表不需要索引（全表扫描更快）
- 读多写少的列适合建索引
- 频繁更新的列谨慎建索引
- 区分度低的列（如
gender）不建议单独建索引 - 联合索引优于多个单列索引（可以走覆盖索引）
实战清单
调优一个慢 SQL 的标准流程：
- ✅ EXPLAIN 看执行计划，重点是 type 和 Extra
- ✅ 确保 type ≥ range，理想是 ref / eq_ref
- ✅ 消除 Using filesort——调整 ORDER BY 或联合索引
- ✅ 消除 Using temporary——GROUP BY 走索引
- ✅ 加合适的索引——按”3 大黄金法则”设计
- ✅ 避免索引失效——函数、隐式转换、前导 %
- ✅ 覆盖索引——只查需要的列
- ✅ 慢 SQL 入库——pt-query-digest 长期跟踪
下一篇（篇九）会讲”CASE 语句、乐观锁与悲观锁实战”——用一条 UPDATE 多字段的 CASE WHEN 写法、三种并发更新方案对比、乐观锁的版本号/时间戳实现、锁选型决策树。