4、SQL查询优化：慢查询分析、索引优化策略、分页查询优化、子查询与JOIN的性能取舍

报表引擎跑得慢，十有八九是SQL的问题。

我见过太多开发同学，业务逻辑写得飞起，一到SQL层面就翻车。明明数据量才几十万，一个报表能跑三分钟。你想想看，用户点一下查询，然后去泡杯咖啡回来还没出结果，这体验能好吗？

这一章，我就把报表引擎里最常用的SQL优化手段掰开揉碎了讲。全是实战经验，没有废话。

4.1 慢查询分析：先找到病根

优化之前，你得先知道哪里慢。别凭感觉猜，用数据说话。

第一步：开启慢查询日志

我个人习惯在开发环境就把慢查询日志打开，阈值设到1秒。这样平时写代码时，哪个SQL慢了，立刻就能发现。

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 设置慢查询阈值（临时生效）
SET long_query_time = 1;
SET slow_query_log = 1;

第二步：用EXPLAIN分析执行计划

找到慢SQL之后，别急着改。先看看MySQL是怎么执行这条SQL的。

EXPLAIN SELECT * FROM orders WHERE order_date > '2024-01-01' AND status = 'PAID';\n

重点关注这几个字段：

  • type：ALL 就是全表扫描，要警惕；ref 或 range 还算可以；const 或 eq_ref 是最理想的。
  • rows：预估扫描的行数。这个数字越大，越危险。
  • Extra：看到 Using filesort 或 Using temporary，基本就是性能杀手。

核心原则：type 列出现 ALL，或者 rows 超过十万级别，你就得考虑加索引了。

我的经验：有一次排查一个报表慢的问题，EXPLAIN 一看 type=ALL，rows=200万。加了个联合索引后，查询时间从12秒降到了0.3秒。嗯，索引就是这么立竿见影。

4.2 索引优化策略：用对索引，事半功倍

索引不是越多越好。我见过有人一张表建了十几个索引，结果写入慢得要命。索引是空间换时间，得用在刀刃上。

4.2.1 联合索引的最左前缀原则

这是新手最容易踩的坑。联合索引 (a, b, c) 能走索引的情况是：

  • WHERE a = ? —— 走索引
  • WHERE a = ? AND b = ? —— 走索引
  • WHERE a = ? AND b = ? AND c = ? —— 走索引
  • WHERE b = ? —— 不走索引
  • WHERE a = ? AND c = ? —— 只走 a 部分的索引

说白了，查询条件必须从联合索引的最左边开始匹配，不能跳过中间的字段。

4.2.2 覆盖索引：减少回表查询

如果查询的字段都在索引里，MySQL就不需要回表查数据行了。这叫覆盖索引，性能极好。

-- 假设有联合索引 (status, order_date)
-- 这个查询可以直接从索引里拿到数据，不需要回表
SELECT status, order_date FROM orders WHERE status = 'PAID';

我曾经踩过的坑：给一个大表加索引时，没注意字段长度。VARCHAR(255) 的字段直接建索引，索引文件比数据文件还大。后来改成前缀索引，只索引前20个字符，性能提升明显。

4.3 分页查询优化：深分页的噩梦

报表系统里，分页查询太常见了。但深分页（比如 LIMIT 100000, 20）是性能杀手。

为什么深分页慢？

MySQL 执行 LIMIT 100000, 20 时，会先扫描前100020行，然后扔掉前100000行，只返回20行。你想想看，这得多浪费。

优化方案一：子查询分页（推荐）

-- 传统写法（慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化写法（快）
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 100000, 1) 
ORDER BY id 
LIMIT 20;

原理很简单：先通过子查询快速定位到起始位置的主键，然后基于主键索引去取数据。这样只扫描20行，而不是100020行。

优化方案二：记录上次查询的最后一条ID

如果业务允许，用「下一页」的方式代替跳页。每次查询都带上上一页最后一条记录的ID。

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 20;

-- 第二页（带上上一页最后一条的ID）
SELECT * FROM orders WHERE id > 10020 ORDER BY id LIMIT 20;

注意：这种方式只适用于按主键或唯一索引排序的场景。如果排序字段有重复值，需要额外处理。

4.4 子查询与JOIN的性能取舍

这个问题，我经常被问到。到底用子查询还是JOIN？

先说结论：没有绝对的谁好谁坏，得看场景。

4.4.1 子查询的适用场景

子查询在MySQL 5.6之后有了很大的优化（叫「半连接」优化）。但有些场景还是要注意：

-- 这种子查询性能通常不错（半连接优化）
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'ACTIVE');

-- 这种相关子查询性能很差（每行都要执行一次子查询）
SELECT *, 
  (SELECT name FROM customers WHERE id = orders.customer_id) AS customer_name 
FROM orders;

4.4.2 JOIN的适用场景

JOIN 在处理多表关联时，通常比子查询更灵活。但要注意：

  • 关联字段一定要有索引
  • 小表驱动大表（用小表的结果集去关联大表）
  • 避免超过3张表的JOIN，否则执行计划会变得复杂
-- 推荐写法：小表驱动大表
-- 假设 customers 是小表（几千行），orders 是大表（几百万行）
SELECT c.name, o.order_no, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'ACTIVE';

我的建议：能用JOIN就用JOIN，可读性更好。但如果你发现子查询的执行计划显示「Materialize」或「Semi-join」，那子查询的性能可能比JOIN还好。别死记规则，用EXPLAIN说话。

4.5 实战避坑指南

最后，分享几个我这些年踩过的坑：

  • 索引不是万能的：如果查询要返回表中大部分数据（比如超过20%），MySQL会放弃索引，直接全表扫描。因为这时候全表扫描更快。
  • 函数包裹索引列会导致索引失效：WHERE DATE(order_date) = '2024-01-01' 不走索引，改成 WHERE order_date >= '2024-01-01' AND order_date < '2024-01-02' 就走索引了。
  • OR 条件要小心：WHERE a = 1 OR b = 2 可能不走索引。改成 UNION ALL 或者用 IN 会更好。
  • ORDER BY 和 LIMIT 一起用时：如果排序字段没有索引，MySQL 会先排序再取前N条，性能极差。一定要给排序字段加索引。

我曾经犯过的错：给一个报表加索引时，没考虑到写入性能。结果索引建好后，报表查询是快了，但数据导入从原来的10分钟变成了2小时。后来删掉了一些冗余索引，才平衡过来。记住，索引不是免费的。

好了，SQL优化这块，说白了就是三板斧：看执行计划、加对索引、改写法。报表引擎里90%的性能问题，都能用这三招解决。下一章我们聊聊报表数据的缓存策略，那又是另一个有意思的话题了。

← 上一章 📖 返回目录 下一章 →