【sql篇】大数据SQL五层优化（万字详解）

原创已于 2026-06-10 15:18:35 修改 · 427 阅读

9 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#sql #数据库

于 2026-04-16 00:06:52 首次发布

sql 专栏收录该内容

1 篇文章

订阅专栏

为什么你的SQL总是跑不完？

作为大数据工程师，我见过太多凌晨还在等任务跑完的无奈。一个本应该10分钟完成的报表，跑了1小时还没动静；一个简单的JOIN操作，把集群内存撑爆；一个COUNT(DISTINCT)让整个任务OOM...

这些问题的根源，往往不全在硬件，而在代码层面的认知盲区。

本文基于五层优化体系，从字段类型选择到架构设计，系统性地解决大数据SQL的性能顽疾。每一层都有明确的优化目标～

一、优化金字塔：五层体系总览

1.1 五层架构

第5层：架构级优化（数据治理与分层） —— 决定天花板
第4层：引擎级优化（参数与执行计划） —— 挖掘引擎潜力
第3层：表级优化（存储与结构设计） —— 地基决定高度
第2层：算子级优化（SQL逻辑重写） —— 减少计算量
第1层：字段级优化（类型与裁剪） —— 从源头节流

1.2 核心原则

优化必须自底向上逐层检查，上层优化无法弥补下层的结构性缺陷。

二、第一层：字段级优化（微观层）

这是最基础也最容易被忽视的优化层。建表时的一念之差，可能导致后续查询性能相差10倍以上。

2.1 数值类型选择决策树

| 数据范围 | 推荐类型 | 占用字节 | 典型场景 |
|----------|----------|----------|----------|
| 0 ~ 255 | TINYINT UNSIGNED | 1 | 状态码、年龄、布尔标志 |
| -128 ~ 127 | TINYINT | 1 | 有符号小整数 |
| 0 ~ 65535 | SMALLINT UNSIGNED | 2 | 端口号、小范围ID |
| 0 ~ 16777215 | MEDIUMINT UNSIGNED | 3 | 中等规模ID（MySQL） |
| 常规整数 | INT | 4 | 自增ID、计数器 |
| 超大范围 | BIGINT | 8 | 时间戳、分布式ID |
| 精确小数 | DECIMAL(p,s) | 动态 | 金额（DECIMAL(18,2)） |
| 科学计算 | DOUBLE | 8 | 浮点运算 |

2.1.1 致命陷阱

用VARCHAR存储数值是数仓中最常见的性能杀手。

-- 灾难示例：字符串存储数值
CREATE TABLE bad_orders (
    user_id VARCHAR(50),      -- 实际是纯数字，浪费空间且无法计算
    amount VARCHAR(20),       -- 字符串排序导致金额顺序错乱
    age VARCHAR(3)            -- 无法做范围查询
);

-- 正确姿势：精确类型匹配
CREATE TABLE good_orders (
    user_id BIGINT,           -- 8字节，支持范围查询
    amount DECIMAL(18,2),     -- 精确到分，支持财务计算
    age TINYINT UNSIGNED      -- 1字节，0-255足够覆盖
);

2.1.2 关键认知

数值比较是CPU原生指令，字符串比较需要逐字符字典序比对，性能差距可达10倍以上。

2.2 时间类型精确选择

| 精度需求 | 推荐类型 | 占用字节 | 时区支持 |
|----------|----------|----------|----------|
| 仅日期 | DATE | 3 | 无 |
| 秒级时间戳 | DATETIME | 8 | 无 |
| 秒级+时区 | TIMESTAMP | 4 | 有 |
| 毫秒级 | DATETIME(3) / BIGINT | 8 | 手动处理 |
| 微秒级 | DATETIME(6) | 8 | 无 |
| 纳秒级 | BIGINT | 8 | 手动处理 |

2.2.1 Hive/Spark专用优化

-- 时间戳存储为BIGINT（毫秒）vs STRING，查询速度快
CREATE TABLE time_optimized (
    event_time_ms BIGINT,     -- 比STRING快，比TIMESTAMP省空间
    event_date DATE           -- 分区常用，3字节
) PARTITIONED BY (dt STRING);

但这个需要结合具体版本功能：

    Spark/Hive 对 TIMESTAMP 类型有原生向量化支持
    存储为 BIGINT 会失去时区处理、日期函数等能力
    现代引擎（Spark 3.x）处理 TIMESTAMP 的性能已经很好

更准确的说法：

    对于纯范围过滤（如 WHERE event_time > x），BIGINT 可能略快；但对于日期函数计算（如 YEAR()、DATE_TRUNC），TIMESTAMP 优于手动转换。

2.3 隐式类型转换：性能的隐形杀手

这是大数据开发中最隐蔽的性能陷阱。

-- 致命：类型不一致导致无法MapJoin
SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id_str;  
-- orders.user_id是BIGINT，users.user_id_str是STRING

-- 灾难后果：
-- 1. 无法广播Join：Spark/Hive要求两边Hash结构完全一致
-- 2. 每条记录强制类型转换，CPU拉满
-- 3. 可能因哈希计算方式不同导致关联失败

关联字段的类型必须绝对一致。建表时统一使用BIGINT存储ID，杜绝混用。

2.4 列裁剪：SELECT * 是性能毒药

-- 禁止：读取所有列
SELECT * FROM user_logs WHERE dt = '2024-01-01';

-- 正确：只取需要的列
SELECT user_id, event_type, event_time 
FROM user_logs 
WHERE dt = '2024-01-01';

原理：ORC/Parquet是列式存储，查1列和查100列的磁盘I/O相差100倍。即使加了WHERE过滤，引擎扫描时仍需将所有列读入内存再丢弃。

三、第二层：算子级优化（逻辑层）

这一层关注SQL逻辑的重写，目标是减少数据移动和计算量。

3.1 谓词下推（Predicate Push Down）

将过滤条件尽可能下推到数据源，减少后续处理的数据量。

-- 低效：先JOIN后过滤
SELECT * 
FROM big_table b
JOIN small_table s ON b.key = s.key
WHERE b.dt = '2024-01-01';

-- 高效：子查询先过滤
SELECT * 
FROM (SELECT * FROM big_table WHERE dt = '2024-01-01') b
JOIN small_table s ON b.key = s.key;

注意：Left Join中，右表条件可以随意下推，左表条件只能部分可以下推（如 WHERE 子句中的左表条件可以，但 ON 子句中的不能）

3.2 Join策略选择

| Join类型 | 适用场景 | 性能特征 |
|----------|----------|----------|
| MapJoin/Broadcast Join | 小表 < 100MB | 无Shuffle，性能最优 |
| Shuffle Hash Join | 两表都大，且Join Key分布均匀 | 需要Shuffle，但比Sort Merge快 |
| Sort Merge Join | 两表都大，且已排序 | 内存友好，适合大数据集 |
| Bucket Join | 两表已按Join Key分桶 | 避免Shuffle，速度提升5-20倍 |

3.2.1 强制MapJoin的Hint

-- Spark SQL
SELECT /*+ BROADCAST(small_table) */ * 
FROM big_table b 
JOIN small_table s ON b.key = s.key;

-- Hive
SELECT /*+ MAPJOIN(small_table) */ * 
FROM big_table b 
JOIN small_table s ON b.key = s.key;

3.2.2 Join顺序优化

小表驱动大表，减少中间结果集。

-- 低效：大表驱动
SELECT * FROM huge_table h  -- 1TB
JOIN big_table b ON h.key = b.key    -- 100GB  
JOIN small_table s ON b.key = s.key; -- 1GB

-- 高效：小表驱动
SELECT * FROM small_table s  -- 1GB
JOIN big_table b ON s.key = b.key    -- 100GB
JOIN huge_table h ON b.key = h.key;  -- 1TB
-- 中间结果：1GB × 100GB → 远小于 1TB × 100GB

3.3 聚合优化：DISTINCT vs GROUP BY


-- 危险：COUNT(DISTINCT)极易OOM
SELECT COUNT(DISTINCT user_id) FROM logs;  -- 全量数据去重

-- 方案1：先GROUP BY再COUNT（精确去重）
SELECT COUNT(*) FROM (SELECT user_id FROM logs GROUP BY user_id) t;

-- 方案2：近似去重（UV统计推荐）
SELECT APPROX_COUNT_DISTINCT(user_id) FROM logs;  -- HyperLogLog算法，内存消耗接近0

原因：COUNT(DISTINCT)通常在一个Reducer中用HashSet去重，数据量大时必然OOM。而GROUP BY可以利用Map端预聚合，大幅减少传输到Reduce的数据量。

注意：该问题在一些引擎的特定版本已经过时。如Spark 3.x 已经对 COUNT(DISTINCT) 做了自动优化，内部会自动转换为 GROUP BY + COUNT，无需手动改写。

3.4 窗口函数优化

-- 低效：多个窗口函数重复排序
SELECT 
    user_id,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as rn,
    RANK() OVER (ORDER BY amount DESC) as rk,
    SUM(amount) OVER (ORDER BY amount DESC) as cumsum
FROM orders;

-- 高效：命名窗口，一次排序
SELECT 
    user_id,
    ROW_NUMBER() OVER w as rn,
    RANK() OVER w as rk,
    SUM(amount) OVER w as cumsum
FROM orders
WINDOW w AS (ORDER BY amount DESC);  -- 只排序一次

3.5 分区粒度优化

避免单个分区数据量过大导致OOM。

-- 危险：单用户数据量过大
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt)

-- 优化：缩小分区粒度
ROW_NUMBER() OVER (PARTITION BY user_id, dt ORDER BY event_time)
-- 外层再用GROUP BY取每天最新的一条

3.6 多次union后统一处理

## 补充：SQL语法层优化（算子级优化扩展）

### 1. 减少Shuffle次数

#### 1.1 合并多小表聚合

**低效：多次Shuffle**

```sql
SELECT key, SUM(val) FROM table1 GROUP BY key
UNION ALL
SELECT key, SUM(val) FROM table2 GROUP BY key
UNION ALL
SELECT key, SUM(val) FROM table3 GROUP BY key;

高效：1次Shuffle，先union起来，外层统一处理。（但要结合数据量）

SELECT key, SUM(val) as total_val
FROM (
    SELECT key, val FROM table1
    UNION ALL
    SELECT key, val FROM table2
    UNION ALL
    SELECT key, val FROM table3
) t
GROUP BY key;

3.7 大IN列表优化

何时优化：IN列表长字符超过1万条（结合实际情况），或超过广播阈值（Spark 10MB / Hive 25MB）

判断方法：

-- Spark/Hive查看执行计划
EXPLAIN SELECT * FROM big_table WHERE id IN (SELECT id FROM huge_list);
-- 若出现 SortMergeJoin/ShuffleHashJoin 而非 BroadcastHashJoin，则需优化

低效：IN数据量大（>1万条或>10MB）

SELECT * FROM big_table WHERE user_id IN (SELECT user_id FROM huge_list);

高效：转SEMI JOIN

SELECT * FROM big_table b
WHERE EXISTS (SELECT 1 FROM huge_list h WHERE h.user_id = b.user_id);

或：分段处理（最终SQL方案）/ 两表join然后 where过滤null值

-- 步骤1：创建分批标记表（10批，每批控制<1万条）
CREATE TEMPORARY TABLE tmp_batches AS
SELECT user_id, NTILE(10) OVER (ORDER BY user_id) AS batch_id
FROM huge_list_table;

-- 步骤2：分批执行（应用层循环batch_id=1..10）
SELECT b.* FROM big_table b
JOIN tmp_batches h ON b.user_id = h.user_id
WHERE h.batch_id = ?;  -- 应用层替换?为1-10

3.8 Multi-Insert（单次扫描多输出）

低效：3个Job

INSERT OVERWRITE TABLE t1 SELECT ... FROM s WHERE dt='2024-01-01';
INSERT OVERWRITE TABLE t2 SELECT ... FROM s WHERE dt='2024-01-01';
INSERT OVERWRITE TABLE t3 SELECT ... FROM s WHERE dt='2024-01-01';

高效：1个Job

FROM s
INSERT OVERWRITE TABLE t1 SELECT ... WHERE type='A'
INSERT OVERWRITE TABLE t2 SELECT ... WHERE type='B'
INSERT OVERWRITE TABLE t3 SELECT ... WHERE type='C';

3.9. 避免相关子查询（每行触发Job）

低效：N+1查询

SELECT a.*, 
    (SELECT MAX(amount) FROM orders b WHERE b.user_id = a.user_id)
FROM users a;

高效：JOIN预聚合

SELECT a.*, b.max_amount
FROM users a
LEFT JOIN (
    SELECT user_id, MAX(amount) as max_amount 
    FROM orders GROUP BY user_id
) b ON a.user_id = b.user_id;

3.10. 避免笛卡尔积

低效：忘记条件

SELECT * FROM t1, t2;  -- 爆炸

高效：明确Join

SELECT * FROM t1 JOIN t2 ON t1.key = t2.key;

3.11. 窗口函数优化

窗口函数替代Self-Join

低效：Self-Join

SELECT a.*, b.prev_amount
FROM orders a
LEFT JOIN orders b ON a.user_id = b.user_id 
    AND b.dt = DATE_SUB(a.dt, 1);

高效：LAG/LEAD

SELECT user_id, amount,
    LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY dt) as prev_amount
FROM orders;

3.12. 集合操作优化

INTERSECT/EXCEPT转JOIN

低效：默认实现

SELECT user_id FROM t1 INTERSECT SELECT user_id FROM t2;

高效：手动转JOIN

SELECT DISTINCT a.user_id FROM t1 a JOIN t2 b ON a.user_id = b.user_id;

3.13. 类型与函数优化

避免函数在分区列上

低效：无法分区裁剪

WHERE DATE_FORMAT(dt, 'yyyy-MM-dd') = '2024-01-01'

高效：直接比较，或者将函数放在常量侧

WHERE dt >= '2024-01-01' AND dt < '2024-01-02'

3.14. 排序优化

DISTRIBUTE BY + SORT BY替代全局ORDER BY

高效：分区内有序

INSERT OVERWRITE TABLE target
SELECT ...
FROM source
DISTRIBUTE BY dept      -- 分发到不同Reducer
SORT BY salary DESC;    -- 每个Reducer内有序

3.15. CTE优化

CTE多次引用物化

低效：重复计算

WITH cte AS (SELECT ... FROM huge_table)
SELECT * FROM cte JOIN ...
UNION ALL
SELECT * FROM cte JOIN ...;

高效：强制物化

-- Spark
WITH cte AS (SELECT /*+ MATERIALIZE */ ... FROM huge_table)

-- 或临时表
CREATE TEMPORARY TABLE tmp_cte AS SELECT ... FROM huge_table;

3.16. 抽样与估算

近似计算替代全量

低效：全量去重

SELECT COUNT(DISTINCT user_id) FROM huge_table;

高效：近似

-- Spark
SELECT APPROX_COUNT_DISTINCT(user_id, 0.05) FROM huge_table;

-- Hive抽样
SELECT COUNT(DISTINCT user_id) FROM (
    SELECT user_id FROM huge_table TABLESAMPLE(1 PERCENT)
) t;

3.17. 动态分区优化

一定注意避免分区爆炸

低效：动态分区过多

INSERT OVERWRITE TABLE t PARTITION(dt, city, hour) SELECT ...;
-- 365×300×24=262万分区

高效：减少分区维度

INSERT OVERWRITE TABLE t PARTITION(dt) 
SELECT ..., dt, city, hour FROM source;
-- city/hour作为普通字段，按dt一级分区

四、第三层：表级优化（结构层）

SQL写得再好，表结构设计不合理也是白搭。

4.1 分区策略：拒绝过度分区

4.1.1 分区粒度选择

假设 HDFS Block = 128MB，目标单分区 100-1000个Block（12.8GB-128GB）：

| 日增量数据 | 推荐分区 | 说明 |
|------------|----------|------|
| < 1GB | 按月分区 | 避免小文件过多 |
| 1-10GB | 按周分区 | 平衡查询与存储 |
| 10-100GB | 按天分区 | 常用方案 |
| > 100GB | 按小时分区 | 避免单分区过大 |

注意：需要结合具体集群以及扫描数据量大小，还有具体的数仓引擎（spark/hive对小文件的容忍度不同）。上面只是简单举个例子，小时分区一定要注意小文件的风险～

4.1.2 致命误区

过度多级分区导致小文件爆炸。

-- 灾难：10万个分区
CREATE TABLE bad_table (
    user_id BIGINT,
    event_type STRING
) PARTITIONED BY (dt STRING, city STRING, hour STRING);  
-- 365天 × 300城市 × 24小时 = 262万个分区

-- 合理：单级分区+分桶
CREATE TABLE good_table (
    user_id BIGINT,
    city STRING,
    event_type STRING
) PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 256 BUCKETS;

4.1.3 铁律

大数据表最好不要超过两级分区，通常按dt一级分区足矣。其他维度放在字段中过滤。

4.2 分桶策略：大表Join的利器

4.2.1 分桶数计算公式

buckets = ceil(总数据量GB / 目标单桶大小GB)
目标单桶大小：1-2GB最佳（具体参考公司服务器）

示例：100GB数据，目标单桶800MB
buckets = ceil(100 / 0.8) = 128
取2的幂次：128 ✓

CREATE TABLE user_events (
    user_id BIGINT,
    event_time TIMESTAMP,
    event_type STRING
)
CLUSTERED BY (user_id) INTO 128 BUCKETS  -- 100GB/128 ≈ 800MB/桶
SORTED BY (event_time) INTO 128 BUCKETS; -- 桶内有序，支持范围查询裁剪

4.2.2 Join优化原理

相同user_id的数据一定在两张表的同一个桶号里，Join时只需把相同桶号的数据拉到一起做本地Join，避免全量Shuffle。

前提条件：

-- 两张表必须满足：
CLUSTERED BY (user_id) INTO 128 BUCKETS  -- 分桶列相同
CLUSTERED BY (user_id) INTO 128 BUCKETS  -- 桶数相同（或成倍数）

-- Hive才会自动触发优化：
SET hive.optimize.bucketmapjoin = true;
SET hive.auto.convert.join = true;

如果桶数不同？

表A：128桶
表B：240桶（不成倍数）
→ 无法直接桶对桶Join，退化为普通Shuffle Join

一句话总结：

分桶让"相同key的数据在相同桶号里预定位"，Join时只需要把相同桶号的两个小文件拉到一起，而不是全表Shuffle后找数据。把"大海捞针"变成"指定抽屉里配对"。

4.3 存储格式选择

核心原则：文件格式决定查询性能天花板，表格式决定数据管理能力

维度	关键决策
列式 vs 行式	SQL分析必选列式（ORC/Parquet），仅流式传输用行式（Avro）
压缩算法	Zstd（2026年标准），兼顾压缩比和解压速度
向量化执行	必须开启，ORC（Hive原生）、Parquet（Spark原生）
谓词下推	ORC（stripe index）、Parquet（页统计）、Iceberg（分区裁剪）

场景	格式	关键优化
ODS贴源	Avro（Kafka）→ Parquet（落地）	流式行式，存储列式
DWD/DWS数仓	ORC（Hive）/ Iceberg（Spark）	向量化 + 分区裁剪
交互分析	StarRocks/Doris（自建存储）	MPP + 物化视图，绕过Hive/Spark
准实时更新	Paimon（LSM-Tree）	秒级可见，增量compaction
冷数据归档	ORC/Parquet + ZSTD（高压缩）	查询极少，压缩比优先

一句话决策：

你的环境	直接选
纯Hive	ORC + Zstd + 向量化
纯Spark	Parquet + Zstd + 向量化
Hive+Spark混用	Parquet（Spark友好）或 Iceberg（统一元数据）
要实时更新	Paimon（Flink生态）
要交互分析	StarRocks/Doris（独立OLAP）

必须避开的坑：

坑	后果	正确做法
Hive用Parquet做主存储	向量化执行弱，查询慢30%+	Hive主存用ORC
Spark用ORC做主存储	TPC-DS性能差11%	Spark主存用Parquet
全用Snappy压缩	压缩比低，IO压力大	2026年切Zstd
Hive ACID做实时	分钟级延迟，强制分桶难维护	用Paimon或Hudi
SELECT * 查宽表	列存优势全丢，IO爆炸	只查需要的列

4.3.1 ORC高级参数配置

CREATE TABLE optimized_table (
    user_id STRING,
    event_time TIMESTAMP,
    properties MAP<STRING,STRING>
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress' = 'ZSTD',              -- 压缩比和速度兼顾
    'orc.compress.size' = '262144',       -- 256KB压缩块
    'orc.stripe.size' = '268435456',      -- 256MB Stripe大小
    'orc.row.index.stride' = '10000',     -- 每1万行一个索引
    'orc.bloom.filter.columns' = 'user_id,event_type',  -- Bloom过滤器加速点查
    'orc.bloom.filter.fpp' = '0.01'       -- 1%误判率
);

五、第四层：引擎级优化（参数层）

当SQL和表结构都优化到位，就需要向引擎要性能。

5.1 Spark SQL核心参数

-- 自适应查询执行（AQE）- Spark 3.0+ 必开
SET spark.sql.adaptive.enabled = true;
SET spark.sql.adaptive.coalescePartitions.enabled = true;
SET spark.sql.adaptive.skewJoin.enabled = true;  -- 自动处理数据倾斜
SET spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes = 400MB;

-- 广播Join阈值
SET spark.sql.autoBroadcastJoinThreshold = 100MB;  -- 根据集群内存调整

-- 并行度设置
SET spark.sql.shuffle.partitions = 200;  -- 默认200，根据数据量调整
-- 经验值：Shuffle分区数 = 总CPU Core数 × 2~3

-- 向量化执行
SET spark.sql.parquet.enableVectorizedReader = true;
SET spark.sql.orc.enableVectorizedReader = true;

5.2 Hive核心参数

-- 向量化执行（ORC格式性能提升2-5倍）
SET hive.vectorized.execution.enabled = true;
SET hive.vectorized.execution.reduce.enabled = true;
向量化执行有数据类型限制，复杂类型（如 MAP、ARRAY、高精度 DECIMAL）会回退到非向量化模式。

-- CBO成本优化器
SET hive.cbo.enable = true;
SET hive.compute.query.using.stats = true;

-- Map端预聚合
SET hive.map.aggr = true;

-- 数据倾斜优化
SET hive.optimize.skewjoin = true;
SET hive.skewjoin.key = 100000;

-- 小文件合并
SET hive.merge.mapfiles = true;
SET hive.merge.mapredfiles = true;
SET hive.merge.size.per.task = 256000000;  -- 256MB

5.3 数据倾斜终极解决方案

5.3.1 诊断方法

Spark UI中某个Task的Shuffle Read/Write数据量是其他Task的几十倍。

5.3.2 现代方案（Spark 3.x）

开启AQE自动处理。

SET spark.sql.adaptive.enabled = true;
SET spark.sql.adaptive.skewJoin.enabled = true;
-- 引擎自动在运行时拆分倾斜的Partition

5.3.3 传统方案（加盐法）


-- 倾斜Key打散：给空值加随机后缀（实际情况需要找出具体的大key，这里将null视为大key）
SELECT 
    IF(a.user_id IS NULL, CONCAT(RAND()*10, '_null'), a.user_id) as join_key,
    a.order_amt
FROM order_tbl a
JOIN (
    -- 小表需要扩充对应后缀
    SELECT user_id FROM user_tbl where user_id is not null
    UNION ALL
    SELECT CONCAT(n, '_null') as user_id 
    FROM user_tbl 
    LATERAL VIEW EXPLODE(ARRAY(0,1,2,3,4,5,6,7,8,9)) t AS n
    WHERE user_id IS NULL
) b ON join_key = b.user_id;

六、第五层：架构级优化（治理层）

6.1 数据分层存储策略

| 层级 | 存储介质 | 响应时间 | 适用场景 |
|------|----------|----------|----------|
| Layer 1: 实时缓存层 | Redis/HBase | 毫秒级 | 热点数据 |
| Layer 2: 实时数仓 | Flink + Kafka | 秒级 | 增量计算 |
| Layer 3: 离线数仓 | Hive/Spark | 分钟/小时级 | 全量计算 |
| Layer 4: 冷存储 | S3/OSS | 小时级 | 归档数据 |

6.2 查询路由策略

| 查询类型 | 路由引擎 | 响应时间 |
|----------|----------|----------|
| 点查（单条记录） | HBase/Redis | < 10ms |
| 实时聚合 | Flink SQL | < 1s |
| 离线分析 | Spark SQL | 分钟级 |
| 即席查询 | Presto/Trino | 秒级 |
| 报表查询 | ClickHouse/Doris | 亚秒级 |

6.3 统计信息收集

-- 表级统计
ANALYZE TABLE orders COMPUTE STATISTICS;

-- 列级统计（CBO优化器必需）
ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMNS user_id, amount, dt;

-- 分区级统计
ANALYZE TABLE orders PARTITION(dt='2024-01-01') COMPUTE STATISTICS;

七、终极检查清单

7.1 字段级检查

- 数值类型是否最小化？（TINYINT→BIGINT递进）
- 字符串长度是否合理？（VARCHAR(n) n最小化）
- 时间类型精度是否匹配业务需求？
- 是否避免使用NULL？（NOT NULL + DEFAULT）
- 是否避免FLOAT/DOUBLE存金额？（DECIMAL精确）
- 枚举值是否用TINYINT替代VARCHAR？
- 大字段是否放在表结构最后？

7.2 查询级检查

- 是否避免SELECT *？
- WHERE条件是否索引友好？（无函数、无隐式转换）
- Join顺序是否小表在前？
- 是否使用Broadcast Join替代Shuffle Join？
- 是否存在相关子查询
- 子查询是否改写为Join？
- 窗口函数是否使用命名窗口减少排序？

7.3 表级检查

- 分区策略是否合理？（避免过度分区）
- 分桶数是否计算准确？（目标1-2GB/桶）
- 存储格式是否为ORC/Parquet？
- 压缩算法是否选择ZSTD/Snappy？
- 是否收集统计信息？（ANALYZE TABLE）

7.4 引擎级检查

- 是否启用向量化执行？
- 是否启用CBO成本优化？
- AQE是否开启？（Spark 3.0+）
- 并行度是否匹配集群资源？
- 小文件合并是否配置？

八、实战案例：

8.1 原始慢查询

SELECT 
    u.user_name,
    COUNT(DISTINCT o.order_id) as order_cnt,
    SUM(o.amount) as total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.create_time >= '2023-01-01'
GROUP BY u.user_name;

8.2 问题诊断

1. users 10GB，orders 5TB
2. orders 表按天分区，但扫描全年数据
3. COUNT(DISTINCT) 全局去重，单Reducer OOM风险
4. 表Join无Hint，产生巨大Shuffle

8.3 优化步骤

Step 1：字段类型优化

原表：user_id VARCHAR(50)，amount VARCHAR(20)，create_time VARCHAR(20)  
优化后：user_id BIGINT，amount DECIMAL(18,2)，create_time INT (Unix时间戳)

Step 2：表结构优化
-- orders表添加分区，按user_id分桶
CREATE TABLE orders_optimized (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(18,2),
    create_time INT
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 4096 BUCKETS;

Step 3：查询重写
-- 优化后SQL（执行3分钟）
WITH filtered_orders AS (
    -- 先过滤，减少数据量
    SELECT order_id, user_id, amount
    FROM orders
    WHERE dt BETWEEN '20230101' AND '20231231'  -- 分区裁剪
      AND create_time >= UNIX_TIMESTAMP('2023-01-01')
),
order_summary AS (
    -- 预聚合，减少Join数据量
    SELECT 
        user_id,
        COUNT(*) as order_cnt,
        SUM(amount) as total_amount
    FROM filtered_orders
    GROUP BY user_id
)
SELECT /*+ BROADCAST(u) */
    u.user_name,
    o.order_cnt,
    o.total_amount
FROM users u
JOIN order_summary o ON u.id = o.user_id;

九、总结：优化的第一性原理

大数据SQL优化的本质，是减少数据移动，计算量，shuffle次数。沿着这个思路，优化路径变得清晰：

慢SQL诊断流程：
先用EXPLAIN看执行计划，重点找Shuffle/Exchange节点。然后问自己：能否消灭Shuffle？（通过MapJoin或分桶）。如果不能，能否减少数据量？（通过谓词下推、列裁剪、分区裁剪）。Shuffle时数据是否扎堆？（处理数据倾斜）。还是慢？最后调整并行度或加资源。

优化是持续的，没有止境。但掌握这套五层优化体系，你就能在面对慢SQL时，有清晰的排查思路和解决方案～

具体优化请结合当前引擎版本，不同版本间底层处理存在差异。最后请各位大佬批评指正～