如何提高查询性能

0. 引言

查询性能优化的本质是减少 IO（磁盘/网络）与计算量。一条 SQL 的优化路径是固定的：先看执行计划（EXPLAIN），再针对瓶颈环节（访问类型、扫描行数、排序、临时表）精准改造。本文给出完整的"三步分析法"与高频场景优化方案，并附真实 Bad SQL 案例。

1. SELECT 的执行过程回顾

一条 SELECT 在 Server 层经历：连接 → 解析 → 预处理 → 优化器生成执行计划 → 执行引擎按计划取数 → 排序/分组/投影 → 返回。优化器是"黑盒"，但我们可以通过 EXPLAIN 观察到它"看到了什么、选择了什么"，并可通过 optimizer trace 深入其决策过程。

sql

-- 查看优化器详细决策（8.0）
SET optimizer_trace = "enabled=on";
SELECT ...;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

2. EXPLAIN 执行计划"三步曲"

拿到一条慢 SQL，按三步读 EXPLAIN：

sql

EXPLAIN SELECT o.id, u.name FROM user_order o
  JOIN user u ON o.user_id = u.id
  WHERE o.created_at >= '2026-01-01' AND o.status = 1
  ORDER BY o.created_at DESC LIMIT 20;

第一步：看 type（访问类型）——最核心

type	含义	性能
system	系统表，仅一行	🟢 极优
const	主键/唯一键等值查询，最多一行	🟢 极优
eq_ref	JOIN 时被驱动表按主键/唯一键等值匹配	🟢 优
ref	非唯一索引等值匹配	🟡 良
range	索引范围扫描（>、<、BETWEEN、IN）	🟡 良
index	全索引扫描（索引树遍历）	🟠 中
ALL	全表扫描	🔴 差，必须优化

优化目标：把 ALL 提升到 range 及以上。ALL 出现在大表上意味着扫描所有数据页，是慢查询的第一信号。

第二步：看 key 与 rows（用到的索引与估算行数）

key：实际使用的索引（空 = 没走索引）；
rows：优化器估算的扫描行数（估算，8.0 基于持久化统计信息；EXPLAIN ANALYZE（8.0.18+）给出实际执行数据）；
对比 rows 与表实际行数：偏差大说明统计信息过期（ANALYZE TABLE 修复）。

第三步：看 Extra（附加信息）——揭示隐藏问题

Extra	含义	处理
Using index	覆盖索引，零回表	✅ 理想
Using index condition	ICP 索引下推生效	✅ 良好
Using where	存储引擎返回后 Server 层再过滤	检查是否可下推到索引
Using filesort	文件排序（不是磁盘文件，是内存/磁盘排序）	⚠️ 优化 ORDER BY 走索引
Using temporary	使用临时表（GROUP BY/DISTINCT 常见）	⚠️ 改索引或改写 SQL
Using join buffer	JOIN 时被驱动表无法用索引，用缓冲	🔴 给被驱动表连接列加索引
Using index for group-by	松散索引扫描	✅ 良好

EXPLAIN ANALYZE（8.0.18+）：EXPLAIN ANALYZE SELECT ... 真实执行并输出每个节点的实际行数、耗时与循环次数，比传统 EXPLAIN 的估算值可靠得多，是 8.0 排障利器。

3. 高频场景优化

3.1 ORDER BY 优化

sql

-- ❌ Using filesort：排序无法利用索引
SELECT * FROM t WHERE a=1 ORDER BY b;
-- ✅ 联合索引 (a,b)：a 过滤 + b 天然有序，零 filesort
CREATE INDEX idx_a_b ON t(a, b);

排序方向要一致：(a ASC, b DESC) 需要 8.0 降序索引配合；
大结果集排序：ORDER BY 走索引优先；否则 max_sort_length/sort_buffer_size 调优，或分页限制返回量。

3.2 GROUP BY 优化

sql

-- ❌ Using temporary + filesort：先临时表分组再排序
SELECT status, COUNT(*) FROM t GROUP BY status;
-- ✅ 建 (status) 索引：松散索引扫描，避免临时表

GROUP BY 的列尽量来自同一索引的最左前缀；
只关心分组无需排序：GROUP BY status ORDER BY NULL（老技巧，8.0 优化器一般已自动处理）。

3.3 COUNT 优化

需求	最佳方案
`COUNT(*)` 精确总数	InnoDB 必须扫索引（无捷径）；大表用近似值或缓存
`COUNT(1)`	与 COUNT(*) 等价（不数 NULL）
`COUNT(col)`	只统计非 NULL，明确语义
条件计数	覆盖索引：`SELECT COUNT(*) FROM t WHERE status=1` 配 (status) 索引走覆盖扫描

误区：COUNT(*) 比 COUNT(1) 慢？在 InnoDB 中两者完全等价，优化器都走最小索引扫描。真正昂贵的是大表全量计数——高频场景用 Redis 计数或汇总表。

3.4 JOIN 优化

sql

-- ❌ 小表驱动大表被忽略：被驱动表连接列无索引 → Using join buffer
SELECT * FROM big b JOIN small s ON b.user_id = s.user_id;
-- ✅ 被驱动表 b.user_id 建索引：驱动表（小表）取其值做 eq_ref/ref 查询

被驱动表的连接列必须有索引（JOIN 优化的第一原则）；
驱动表选择：优化器按估算行数选（straight_join 可强制，但先确认优化器判断）；
超过 3 张表 JOIN 警惕：拆分为多次查询在应用层合并，或物化中间结果。

3.5 分页优化

sql

-- ❌ 深分页：OFFSET 1000000 仍要扫描并丢弃前 100 万行
SELECT * FROM t ORDER BY id LIMIT 1000000, 20;
-- ✅ 延迟关联/游标分页：只在索引上定位，再回表取 20 行
SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) tmp
  ON t.id = tmp.id;
-- ✅ 或者基于上一页游标（业务允许时最佳）
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 20;

4. Bad SQL 案例集

案例	问题	优化
`WHERE DATE(created_at)='2026-01-01'`	函数包裹索引列，索引失效	范围条件 `created_at >= '2026-01-01 00:00:00' AND < '2026-01-02'` 或函数索引
`WHERE phone=13800138000`	隐式类型转换，索引失效	字符串加引号
`LIKE '%keyword%'`	前导通配符	全文索引（8.0 支持中文 ngram 分词）/ES
`SELECT *` 大字段	回表取大字段拖慢	只查必要列（覆盖索引）
`OR` 多条件	优化器可能全表扫描	`UNION ALL` 或确保每分支可走索引
`IN (大列表)`	列表过大退化为全扫	分批查询或 JOIN 临时表
无索引外键列 JOIN	Using join buffer	连接列建索引
`SELECT DISTINCT` 大表	临时表 + 排序	聚合下推或物化

5. 慢查询治理闭环

text

慢查询日志（long_query_time=1s）→ 定时采集 → 按频率/耗时排序
→ EXPLAIN 分析 → 索引/改写/架构方案 → 上线验证（对比执行计划与前/后耗时）
→ 沉淀规则（禁止 SELECT *、禁止无索引 UPDATE 等）

sql

-- 8.0 慢查询相关配置
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;            -- 阈值 1 秒
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的查询
-- 查询慢日志
SELECT * FROM mysql.slow_log \G   -- 或文件版：mysqldumpslow /var/log/mysql/slow.log

6. 应用层性能优化

数据库优化有天花板，应用层配合才能"治本"：

缓存：热点数据 Redis 缓存（注意一致性：先更新库再删缓存）；
读写分离：读多写少场景，主库写、从库读（见《如何突破单库性能瓶颈》）；
批量替代循环：INSERT ... VALUES (...),(...) 代替循环单条（减少网络往返）；
连接池：控制连接数，避免连接风暴拖垮数据库；
限流与降级：保护数据库不被突发流量击穿。

7. 小结

三步读 EXPLAIN：type（访问类型）→ key/rows（索引与行数）→ Extra（隐藏问题），8.0 用 EXPLAIN ANALYZE 拿到真实执行数据；
四大高频优化：ORDER BY 走索引避免 filesort、GROUP BY 松散扫描避免临时表、COUNT 用覆盖索引、JOIN 给被驱动表加索引；
深分页用游标/延迟关联，Bad SQL 的核心病根是"索引失效"与"不必要的全表扫描"；
慢查询治理要形成"采集→分析→优化→验证→沉淀"闭环，配合应用层缓存与读写分离。

下一章讲解如何突破单库性能瓶颈：读写分离、分库分表与数据一致性方案。