# Spark SQL 进阶实践

大数据开发核心技能：DataFrame 优化、SQL 调优、广播变量、分区策略、数据倾斜处理、生产环境案例，从入门到精通

---

📌 前言

真实生产问题

问题场景：

某电商公司数据平台遇到的问题：

问题 1：Spark SQL 任务太慢
- 同样的 SQL，别人跑 5 分钟，我要跑 1 小时
- 10GB 数据，跑了 2 小时还没完成
- 业务投诉：报表出得太晚

问题 2：数据倾斜严重
- 某个 Reduce 任务跑了 1 小时，进度 99%
- 其他任务都完成了，就等这一个
- 查看发现：某个 Key 有 1 亿条数据

问题 3：内存溢出（OOM）
- Executor 频繁崩溃
- 错误：Java heap space / GC overhead limit exceeded
- 增加内存也没用

问题 4：小文件太多
- 输出目录有 10 万个小文件
- NameNode 内存占用 80%
- 下游查询性能差

Spark SQL 进阶技巧解决：

- DataFrame 优化（Catalyst 优化器）
- SQL 调优（执行计划分析）
- 广播变量（Map Join）
- 分区策略（避免数据倾斜）
- 小文件合并

优化后效果：

- 查询速度：1 小时 → 5 分钟（12 倍）
- 数据倾斜：加盐分散，均匀处理
- 内存溢出：合理配置，稳定运行
- 小文件：10 万 → 100 个

---

🔧 DataFrame 深度优化

Catalyst 优化器

原理：

Spark SQL 核心优势：Catalyst 优化器

SQL → 逻辑计划 → 优化逻辑计划 → 物理计划 → 执行

优化规则：
1. 谓词下推（Predicate Pushdown）
   WHERE 条件尽早过滤

2. 列裁剪（Column Pruning）
   只读取需要的列

3. 常量折叠（Constant Folding）
   提前计算常量表达式

4. Join 重排序
   小表在前，大表在后

实战案例：

from pyspark.sql import SparkSession
from pyspark.sql.functions import *

spark = SparkSession.builder \
    .appName("DataFrame Optimization") \
    .getOrCreate()

# 读取数据
orders = spark.read.parquet("hdfs:///data/orders")
users = spark.read.parquet("hdfs:///data/users")

# ❌ 错误：低效写法
result = orders.join(users, orders.user_id == users.user_id) \
    .filter(orders.amount > 100) \
    .select(orders.order_id, users.user_name, orders.amount)

# ✅ 正确：高效写法
result = orders.filter(orders.amount > 100) \  # 先过滤
    .join(users.hint("broadcast"), orders.user_id == users.user_id) \  # 广播小表
    .select(orders.order_id, users.user_name, orders.amount)

# 查看执行计划
result.explain()

---

执行计划分析

查看执行计划：

# 查看逻辑计划
df.explain()

# 查看详细执行计划
df.explain(extended=True)

# 查看物理计划
df.explain(mode="physical")

执行计划解读：

== Parsed Logical Plan ==
'Project [order_id#1, user_name#5, amount#3]
+- Filter (amount#3 > 100)
   +- Join Inner, (user_id#2 = user_id#4)
      :- SubqueryAlias orders
      :  +- LogicalRelation [...]
      +- SubqueryAlias users
         +- LogicalRelation [...]

== Optimized Logical Plan ==  ← 优化后
Project [order_id#1, user_name#5, amount#3]
+- BroadcastHashJoin [user_id#2], [user_id#4], Inner
   :- Filter (amount#3 > 100)  ← 谓词下推
   :  +- SubqueryAlias orders
   :     +- Project [order_id#1, user_id#2, amount#3]  ← 列裁剪
   :        +- LogicalRelation [...]
   +- BroadcastExchange HashedRelation
      +- SubqueryAlias users
         +- LogicalRelation [...]

== Physical Plan ==
*(1) Project [order_id#1, user_name#5, amount#3]
+- *(1) BroadcastHashJoin [user_id#2], [user_id#4], Inner
   :- *(1) Filter (amount#3 > 100)
   :  +- *(1) ColumnarToRow
   :     +- FileScan Parquet [...]  ← 列式读取
   +- BroadcastExchange HashedRelation
      +- *(1) ColumnarToRow
         +- FileScan Parquet [...]

关键优化：
✓ 谓词下推：Filter 在 Join 之前
✓ 列裁剪：只读取 order_id, user_id, amount
✓ 广播 Join：小表广播到大表
✓ 列式读取：Parquet 格式

---

🔧 SQL 调优技巧

1. Join 优化

广播 Join（Map Join）：

# 小表广播（< 10MB）
from pyspark.sql.functions import broadcast

orders = spark.read.parquet("hdfs:///data/orders")  # 10GB
users = spark.read.parquet("hdfs:///data/users")    # 10MB

# 自动广播
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "10485760")  # 10MB

# 手动广播
result = orders.join(broadcast(users), orders.user_id == users.user_id)

# 或使用 Hint
result = orders.join(users.hint("broadcast"), orders.user_id == users.user_id)

# 优化效果：
# Common Join：30 分钟
# Broadcast Join：3 分钟

Sort Merge Join（大表 Join）：

# 大表 Join 大表
orders = spark.read.parquet("hdfs:///data/orders")      # 10GB
products = spark.read.parquet("hdfs:///data/products")  # 5GB

# 预排序 + 分桶
orders_bucket = orders.repartition("product_id").sortBy("product_id")
products_bucket = products.repartition("product_id").sortBy("product_id")

# Sort Merge Join
result = orders_bucket.join(
    products_bucket,
    orders_bucket.product_id == products_bucket.product_id,
    "inner"
)

# 优化效果：
# 普通 Join：1 小时
# Sort Merge Join：10 分钟

Join 策略选择：

表大小	推荐策略	配置
小表 × 小表	Broadcast Join	autoBroadcastJoinThreshold
小表 × 大表	Broadcast Join	broadcast()
大表 × 大表	Sort Merge Join	预排序 + 分桶
倾斜 Join	Skew Join	skewJoin.enabled

---

2. 聚合优化

两阶段聚合（解决数据倾斜）：

from pyspark.sql.functions import *

# ❌ 错误：单阶段聚合，易倾斜
result = orders.groupBy("user_id") \
    .agg(
        sum("amount").alias("total_amount"),
        count("order_id").alias("order_count")
    )

# 问题：某个用户 1000 万订单，其他用户 100 单

# ✅ 正确：两阶段聚合
# 第一阶段：加盐局部聚合
salted_orders = orders.withColumn(
    "salt",
    floor(rand() * 10).cast("int")
)

partial_agg = salted_orders.groupBy("user_id", "salt") \
    .agg(
        sum("amount").alias("partial_amount"),
        count("order_id").alias("partial_count")
    )

# 第二阶段：去盐全局聚合
final_result = partial_agg.groupBy("user_id") \
    .agg(
        sum("partial_amount").alias("total_amount"),
        sum("partial_count").alias("order_count")
    )

# 优化效果：
# 单阶段：2 小时（倾斜）
# 两阶段：10 分钟

预聚合优化：

# 先聚合再 Join
# ❌ 错误：先 Join 后聚合
result = orders.join(users, "user_id") \
    .groupBy("user_name") \
    .agg(sum("amount").alias("gmv"))

# ✅ 正确：先聚合后 Join
order_agg = orders.groupBy("user_id") \
    .agg(sum("amount").alias("gmv"))

result = order_agg.join(users, "user_id") \
    .select("user_name", "gmv")

# 优化效果：
# 错误：Shuffle 10GB
# 正确：Shuffle 1GB

---

3. 分区优化

合理设置分区数：

# 查看当前分区数
print(df.rdd.getNumPartitions())

# 设置分区数
spark.conf.set("spark.sql.shuffle.partitions", "200")  # 默认 200

# 根据数据量调整
# 数据量 < 1GB: 50-100
# 数据量 1-10GB: 200-500
# 数据量 10-100GB: 500-2000
# 数据量 > 100GB: 2000+

# 动态调整
df_small = df.filter("dt = '2026-03-24'")  # 小数据
spark.conf.set("spark.sql.shuffle.partitions", "50")

df_large = df.filter("dt >= '2026-01-01'")  # 大数据
spark.conf.set("spark.sql.shuffle.partitions", "1000")

分区裁剪：

# 分区表
orders = spark.read.parquet("hdfs:///data/orders")

# ✅ 正确：分区裁剪
result = orders.filter("dt = '2026-03-24'")
# 只读取一个分区

# ❌ 错误：全表扫描
result = orders.filter("amount > 100")
# 扫描所有分区

# 优化效果：
# 全表扫描：10TB
# 分区裁剪：500GB

---

⚠️ 数据倾斜处理

识别数据倾斜

# 查看 Key 分布
df.groupBy("user_id").count() \
    .orderBy("count", ascending=False) \
    .show(100)

# 判断标准：
# Top 10 Key 占比 > 50%：严重倾斜
# Top 10 Key 占比 > 20%：中等倾斜
# Top 10 Key 占比 < 10%：正常

解决方案

方案 1：加盐分散（GROUP BY 倾斜）

from pyspark.sql.functions import *

# 加盐
salted_df = df.withColumn(
    "salted_key",
    concat(col("user_id"), lit("_"), floor(rand() * 10).cast("int"))
)

# 局部聚合
partial = salted_df.groupBy("salted_key", "user_id") \
    .agg(sum("amount").alias("partial_amount"))

# 全局聚合
result = partial.groupBy("user_id") \
    .agg(sum("partial_amount").alias("total_amount"))

方案 2：广播 Join（Join 倾斜）

# 小表广播
large_df.join(broadcast(small_df), "key")

方案 3：采样倾斜 Key 单独处理

# 找出倾斜 Key
skew_keys = df.groupBy("key").count() \
    .filter("count > 1000000") \
    .select("key") \
    .collect()

# 分离倾斜数据
skew_df = df.join(skew_keys, "key")
normal_df = df.join(skew_keys, "key", "left_anti")

# 分别处理
skew_result = skew_df.repartition("key").groupBy("key").agg(...)
normal_result = normal_df.groupBy("key").agg(...)

# 合并结果
result = skew_result.union(normal_result)

---

🔧 小文件治理

小文件产生原因

1. 分区过多
   每个分区一个文件，分区太多

2. 并行度过高
   1000 个 Task → 1000 个文件

3. 流式写入
   微批次写入，每批一个文件

解决方案

方案 1：合并小文件（推荐）

# 写入前合并
df.coalesce(100) \  # 减少分区数
    .write \
    .mode("overwrite") \
    .parquet("hdfs:///data/output")

# 或使用 repartition
df.repartition(100) \
    .write \
    .mode("overwrite") \
    .parquet("hdfs:///data/output")

# 区别：
# coalesce：不 Shuffle，性能好
# repartition：Shuffle，文件均匀

方案 2：设置目标文件大小

# 配置目标文件大小
spark.conf.set("spark.sql.files.maxRecordsPerFile", "1000000")
spark.conf.set("spark.sql.files.maxPartitionBytes", "134217728")  # 128MB

# 写入
df.write \
    .mode("overwrite") \
    .option("maxRecordsPerFile", "1000000") \
    .parquet("hdfs:///data/output")

方案 3：定期合并（历史数据）

# 读取小文件
df = spark.read.parquet("hdfs:///data/small_files")

# 合并写入
df.write \
    .mode("overwrite") \
    .parquet("hdfs:///data/merged_files")

# 删除原文件
# hdfs dfs -rm -r /data/small_files

---

⚙️ 参数调优

内存配置

# Executor 内存
spark.conf.set("spark.executor.memory", "4g")
spark.conf.set("spark.executor.memoryOverhead", "1g")

# Driver 内存
spark.conf.set("spark.driver.memory", "2g")

# 堆外内存
spark.conf.set("spark.memory.offHeap.enabled", "true")
spark.conf.set("spark.memory.offHeap.size", "512m")

# 存储内存比例
spark.conf.set("spark.memory.storageFraction", "0.5")

并行度配置

# CPU 核心数
spark.conf.set("spark.executor.cores", "4")

# 并行任务数
spark.conf.set("spark.sql.shuffle.partitions", "200")
spark.conf.set("spark.default.parallelism", "200")

# 动态资源分配
spark.conf.set("spark.dynamicAllocation.enabled", "true")
spark.conf.set("spark.dynamicAllocation.minExecutors", "1")
spark.conf.set("spark.dynamicAllocation.maxExecutors", "100")

序列化配置

# Kryo 序列化（比 Java 快 10 倍）
spark.conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
spark.conf.set("spark.kryo.registrator", "com.example.MyRegistrator")

# 压缩
spark.conf.set("spark.io.compression.codec", "snappy")
spark.conf.set("spark.sql.shuffle.compress", "true")

---

🏭 生产环境完整案例

案例：GMV 统计优化（1 小时 → 5 分钟）

优化前：

# 低效写法
orders = spark.read.parquet("hdfs:///data/orders")
users = spark.read.parquet("hdfs:///data/users")
products = spark.read.parquet("hdfs:///data/products")

result = orders \
    .join(users, orders.user_id == users.user_id) \
    .join(products, orders.product_id == products.product_id) \
    .filter("dt >= '2026-01-01' and dt <= '2026-03-24'") \
    .groupBy("user_name", "category_name") \
    .agg(
        sum("amount").alias("gmv"),
        count("order_id").alias("order_count")
    )

result.write.mode("overwrite").parquet("hdfs:///data/gmv_stats")

# 问题：
# 1. 先 Join 后过滤（数据量大）
# 2. 未使用广播（小表未广播）
# 3. 单阶段聚合（数据倾斜）
# 4. 未设置分区数

# 执行时间：1 小时

优化后：

from pyspark.sql.functions import *

# 配置优化
spark.conf.set("spark.sql.shuffle.partitions", "500")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "10485760")

# 读取数据（分区裁剪）
orders = spark.read.parquet("hdfs:///data/orders") \
    .filter("dt >= '2026-01-01' and dt <= '2026-03-24'")
users = spark.read.parquet("hdfs:///data/users")
products = spark.read.parquet("hdfs:///data/products")

# 预聚合
order_agg = orders.groupBy("user_id", "product_id") \
    .agg(
        sum("amount").alias("amount"),
        count("order_id").alias("cnt")
    )

# 广播 Join
result = order_agg \
    .join(broadcast(users), order_agg.user_id == users.user_id) \
    .join(broadcast(products), order_agg.product_id == products.product_id) \
    .groupBy("user_name", "category_name") \
    .agg(
        sum("amount").alias("gmv"),
        sum("cnt").alias("order_count")
    )

# 合并小文件
result.coalesce(100) \
    .write \
    .mode("overwrite") \
    .parquet("hdfs:///data/gmv_stats")

# 优化点：
# 1. 分区裁剪：10TB → 500GB
# 2. 预聚合：减少 Shuffle 数据
# 3. 广播 Join：小表广播
# 4. 合并文件：1000 个 → 100 个

# 执行时间：5 分钟
# 优化效果：12 倍

---

📋 最佳实践清单

SQL 开发

• 先过滤后 Join（减少数据量）
• 小表使用广播 Join
• 大表聚合使用两阶段
• 避免 SELECT *（列裁剪）
• 使用分区字段过滤

参数配置

• 合理设置分区数
• 开启 Kryo 序列化
• 开启压缩（Snappy）
• 配置动态资源分配

数据倾斜

• 分析 Key 分布
• 加盐分散
• 广播小表
• 单独处理倾斜 Key

小文件治理

• 写入前合并分区
• 设置目标文件大小
• 定期合并历史数据

---

📌 总结

核心要点

优化方向	关键技术	性能提升
Join 优化	广播 Join	5-10x
聚合优化	两阶段聚合	5-10x
分区优化	合理分区数	2-5x
数据倾斜	加盐分散	10-50x
小文件	合并分区	10-100x

实践原则

1. 先分析后优化
   使用 explain 分析执行计划

2. 先 SQL 后参数
   SQL 优化收益最大

3. 监控常态化
   定期分析慢查询

4. 文档沉淀
   记录优化经验

---

💡 Spark SQL 调优是持续迭代的过程，建议建立性能监控体系！

---

👋 感谢阅读！

---

🔗 系列文章

• [01-SQL 窗口函数从入门到精通](./01-SQL 窗口函数从入门到精通.md)
• [02-Spark 性能优化 10 个技巧](./02-Spark 性能优化 10 个技巧.md)
• 03-数据仓库分层设计指南
• 04-维度建模实战
• [05-Flink 实时数仓实战](./05-Flink 实时数仓实战.md)
• [06-Kafka 消息队列实战指南](./06-Kafka 消息队列实战指南.md)
• [07-Hive 性能优化实战](./07-Hive 性能优化实战.md)
• [08-Linux 大数据开发必备工具](./08-Linux 大数据开发必备工具.md)
• [09-缓慢变化维 SCD Type 2 详解](./09-缓慢变化维 SCD Type2 详解.md)
• [10-Flink 时间语义与 Watermark 详解](./10-Flink 时间语义与 Watermark 详解.md)
• [11-Hadoop 集群搭建完整教程](./11-Hadoop 集群搭建完整教程.md)
• 12-Spark SQL 进阶实践（本文）
• 下一篇：数据血缘与元数据管理