4. 慢查询分析与SQL优化：慢SQL定位方法、索引优化与分页查询优化

在报表导出服务中，数据库查询往往是性能瓶颈的“重灾区”。一个未优化的慢SQL可能导致整个导出链路超时、连接池耗尽，甚至拖垮数据库实例。本章将系统讲解慢SQL的定位方法、索引优化实战以及分页查询的两种主流方案对比。

4.1 慢SQL定位方法

4.1.1 慢查询日志（Slow Query Log）

MySQL的慢查询日志是定位性能问题的第一手资料。通过配置阈值，系统会自动记录执行时间超过设定值的SQL语句。

-- 查看当前慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（生产环境谨慎）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 分析慢查询日志（使用mysqldumpslow工具）
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

4.1.2 EXPLAIN执行计划分析

获取到慢SQL后，使用EXPLAIN命令分析其执行计划，重点关注以下字段：

-- 示例：分析一个报表查询
EXPLAIN SELECT
    o.order_id, o.order_date, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY o.amount DESC
LIMIT 1000;

-- 重点关注：type、key、rows、Extra

4.2 索引优化实战

4.2.1 复合索引设计原则

报表查询通常涉及多个过滤条件和排序字段，复合索引的设计至关重要。

• 最左前缀原则： 查询条件必须从索引最左列开始匹配。
• 区分度高的列放在前面： 例如 order_date 比 status 区分度更高。
• 覆盖索引： 索引包含查询所需的所有列，避免回表。
• 避免冗余索引： (a, b) 索引可以覆盖 (a) 索引，无需重复创建。

-- 反例：为每个字段单独建索引
CREATE INDEX idx_date ON orders(order_date);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_customer ON orders(customer_id);

-- 正例：根据查询模式建立复合索引
-- 查询：WHERE order_date BETWEEN ? AND ? AND status = ? ORDER BY amount DESC
CREATE INDEX idx_date_status_amount ON orders(order_date, status, amount DESC);

-- 覆盖索引示例：查询只需要 order_id, order_date, amount
CREATE INDEX idx_covering ON orders(order_date, status, amount, order_id);

4.2.2 索引失效场景排查

4.3 分页查询优化：游标分页 vs 偏移分页

报表导出通常需要分批获取大量数据。传统的 LIMIT OFFSET 分页在深翻页时性能急剧下降，而游标分页（Keyset Pagination）能有效解决这一问题。

4.3.1 偏移分页（OFFSET）的问题

当 OFFSET 很大时，数据库仍然需要扫描并丢弃前 N 行数据，导致扫描行数随页码线性增长。

-- 偏移分页：越往后越慢
SELECT * FROM orders
WHERE status = 1
ORDER BY id
LIMIT 1000 OFFSET 100000;
-- 扫描行数：100000 + 1000 = 101000 行

4.3.2 游标分页（Keyset Pagination）

基于上一页最后一条记录的某个唯一有序字段（如主键、时间戳）进行过滤，利用索引直接定位起始位置。

-- 游标分页：每次只扫描需要的行
-- 第一页
SELECT * FROM orders
WHERE status = 1
ORDER BY id
LIMIT 1000;

-- 第二页（传入上一页最后一条记录的 id）
SELECT * FROM orders
WHERE status = 1 AND id > 1000
ORDER BY id
LIMIT 1000;
-- 扫描行数：1000 行（仅限索引扫描）

4.3.3 两种方案对比

4.4 综合优化案例

假设有一个报表导出查询，需要按日期范围导出订单数据，并按金额降序排列。优化前后的对比：

-- 优化前：全表扫描 + 文件排序
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY amount DESC
LIMIT 1000;
-- type: ALL, rows: 5000000, Extra: Using where; Using filesort

-- 优化后：使用复合索引 + 覆盖索引 + 游标分页
-- 1. 创建复合索引
CREATE INDEX idx_date_amount ON orders(order_date, amount DESC, id);

-- 2. 使用覆盖索引（只查询需要的字段）
SELECT order_id, order_date, amount, customer_id
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY amount DESC, id
LIMIT 1000;
-- type: range, key: idx_date_amount, rows: 1000, Extra: Using index condition