3. SQL查询语句的优化技巧

说实话，我排查过上百个OLAP慢查询，十有八九的问题都出在SQL本身。很多人一上来就调参数、加机器，其实先把SQL写好，性能能提升一大截。这一章我就把自己常用的优化套路分享给你。

核心原则：OLAP的SQL优化，本质上是减少数据扫描量和计算量。别让数据库做它不该做的事。

3.1 分区裁剪：别扫全表

我最常看到的问题就是——查询没带分区条件。你想想看，一张表几百个分区，全扫一遍，不慢才怪。

-- ❌ 错误写法：全表扫描
SELECT user_id, SUM(amount)
FROM order_fact
WHERE dt >= '2024-01-01';

-- ✅ 正确写法：指定分区键
SELECT user_id, SUM(amount)
FROM order_fact
WHERE dt >= '2024-01-01'
  AND province_id = '110000';  -- 分区键

我的习惯：写查询前，先用 SHOW PARTITIONS table_name 看一眼分区结构。确保WHERE条件里包含了高基数的分区键。

3.2 列裁剪：别用 SELECT *

嗯，这个错误太普遍了。我记得有一次帮一个团队排查，他们的查询跑了20分钟，我改成只选需要的列，直接降到3分钟。

-- ❌ 错误写法：读取所有列
SELECT * FROM user_profile
WHERE register_date > '2024-01-01';

-- ✅ 正确写法：只读需要的列
SELECT user_id, user_name, register_date
FROM user_profile
WHERE register_date > '2024-01-01';

为什么会这样？因为OLAP大多是列式存储，你少选一列，它就少读一列的数据。数据量大的时候，差别是几何级的。

3.3 聚合下推：先过滤再聚合

这个技巧说白了就是——能提前过滤的数据，别等到聚合完再过滤。我在项目中遇到过好几次，有人把过滤条件写在HAVING里，导致聚合计算量暴增。

-- ❌ 错误写法：先聚合再过滤
SELECT user_id, COUNT(*) AS cnt
FROM click_log
GROUP BY user_id
HAVING cnt > 1000;

-- ✅ 正确写法：先过滤再聚合
SELECT user_id, COUNT(*) AS cnt
FROM click_log
WHERE event_time > '2024-06-01'
GROUP BY user_id
HAVING cnt > 1000;

避坑指南：我曾经见过一个查询，WHERE条件里写了子查询，结果子查询没走分区裁剪，导致外层也扫了全表。记住：子查询也要做分区裁剪和列裁剪。

3.4 JOIN优化：小表驱动大表

OLAP里JOIN是重灾区。我个人的经验是，能用Broadcast Join就别用Shuffle Join。说白了，就是把小表广播到每个节点，避免数据shuffle。

JOIN类型 适用场景 性能特点
Broadcast Join 小表 < 10MB 无shuffle，极快
Shuffle Join 大表对大表 有shuffle，较慢
Bucket Join 两表都已分桶 局部JOIN，中等 
-- 显式指定Broadcast Join（以Spark SQL为例）
SELECT /*+ BROADCAST(dim_user) */
    f.order_id, u.user_name, f.amount
FROM order_fact f
JOIN dim_user u ON f.user_id = u.user_id;

我的建议：写JOIN之前，先评估一下表的大小。如果小表超过几百MB，Broadcast反而会撑爆内存。这时候可以考虑先对小表做一次聚合或过滤。

3.5 避免数据倾斜

数据倾斜是OLAP的隐形杀手。我记得有一次排查一个跑了6小时的查询，发现某个key的数据量是其他key的100倍，所有计算都压在一个节点上。

常见的解决思路：

  • 加盐（Salting）：给热点key加随机后缀，分散到多个reduce
  • 两阶段聚合：先局部聚合，再全局聚合
  • 调整并行度：适当增大reduce数量
-- 两阶段聚合示例（解决count distinct倾斜）
-- 第一阶段：加随机前缀，局部聚合
SELECT prefix, city, COUNT(DISTINCT user_id) AS partial_cnt
FROM (
    SELECT 
        CONCAT(CAST(RAND()*10 AS INT), '_', city) AS prefix,
        city,
        user_id
    FROM user_log
    WHERE dt = '2024-06-01'
) t
GROUP BY prefix, city;

-- 第二阶段：去掉前缀，全局聚合
SELECT city, SUM(partial_cnt) AS total_cnt
FROM stage1_result
GROUP BY city;

3.6 善用物化视图

如果你的查询模式很固定，比如每天都要跑一次月度汇总，那就别每次都从头算。物化视图就是预计算好的结果，查询时直接读。

-- 创建物化视图（以ClickHouse为例）
CREATE MATERIALIZED VIEW mv_monthly_sales
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(sale_date)
ORDER BY (product_id, sale_date)
AS SELECT
    product_id,
    toStartOfMonth(sale_date) AS sale_month,
    sum(amount) AS total_amount,
    count(*) AS order_count
FROM sales
GROUP BY product_id, sale_month;

注意：物化视图不是银弹。它会占用存储空间，而且数据更新有延迟。我建议只对高频、固定的查询创建物化视图。

3.7 查询执行计划分析

写完了SQL，怎么知道它快不快？看执行计划。我每次优化前都会先跑一遍EXPLAIN，看看是不是走了全表扫描、有没有数据倾斜、JOIN类型对不对。

-- 查看执行计划
EXPLAIN ANALYZE
SELECT user_id, SUM(amount)
FROM order_fact
WHERE dt = '2024-06-01'
GROUP BY user_id;

重点关注几个指标：

  • 扫描行数：是不是比预期多？
  • shuffle数据量：是不是很大？
  • 每个stage的耗时：哪个stage最慢？

3.8 本章知识体系

下面这张图是我自己整理的SQL优化决策流程，每次遇到慢查询，我就按这个顺序排查：

SQL查询优化决策流程 收到慢查询 步骤1：检查分区裁剪 步骤2：检查列裁剪 步骤3：检查聚合下推 步骤4：检查JOIN策略 步骤5：检查数据倾斜 性能达标 优化顺序 1. 先看扫描量 2. 再看计算量 3. 最后看数据分布 常用工具 EXPLAIN ANALYZE SHOW PARTITIONS Profile / Metrics

这张图我用了很多年，每次排查都按这个顺序走，基本不会漏掉关键点。你把它记下来，遇到慢查询就照着做。

3.9 总结一下

SQL优化其实没那么玄乎。核心就三件事：少读数据、少算数据、别让数据倾斜。你把这三点刻在脑子里，写出来的SQL不会差到哪里去。

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