大数据集高效 SQL 编写完整指南

适用于 MySQL、PostgreSQL、Oracle、Hive、SparkSQL、ClickHouse 等关系型 / 数仓引擎，区分OLTP 在线数据库和OLAP 大数据分析引擎，从原理、编码规范、索引、执行计划、踩坑点逐层说明。

一、核心前置原则

减少扫描数据量（最高优先级）：能过滤尽早过滤，避免全表扫描；
减少数据传输：不要 SELECT *，只取需要字段；
减少计算开销：避免列运算、函数破坏索引；
避免大结果集落地内存：分页、分批、流式读取；
引擎差异：MySQL 侧重索引优化；Spark/Hive/ClickHouse 侧重分区、分桶、数据存储格式。

二、通用 SQL 编写规范（所有数据库适用）

1. SELECT 子句优化

✅ 推荐

sql

-- 只查询业务必需字段
select id, name, create_time from order_info where status = 1;

❌ 禁止

sql

SELECT * FROM order_info;

原因：

读取更多磁盘 IO、网络传输；
无法使用覆盖索引；
大量冗余数据占用内存。

拓展：如果索引包含查询字段，数据库直接走索引不回表（覆盖索引，OLTP 核心优化手段）。

2. WHERE 条件：索引友好，前置过滤

（1）不要在索引列上使用函数、运算

❌ 失效（索引无法使用）

sql

WHERE DATE(create_time) = '2026-07-30'
WHERE id + 1 = 1000
WHERE UPPER(name) = 'TEST'

✅ 改写

sql

WHERE create_time >= '2026-07-30' AND create_time < '2026-07-31'
WHERE id = 999
WHERE name = 'TEST'

（2）优先等值查询，范围后置

联合索引遵循最左匹配原则

索引：idx_status_createtime(status, create_time)

✅ 正确顺序

sql

WHERE status = 1 AND create_time >= 'xxx'

（3）谨慎使用 `!= <> NOT IN IS NOT NULL`

这类条件很容易导致索引失效，大数据集尽量规避；

NOT IN 子查询如果存在 NULL，会直接返回空结果，优先改用 NOT EXISTS。

❌ 低效

sql

SELECT * FROM order_info WHERE user_id NOT IN (SELECT user_id FROM black_user);

✅ 高效

sql

SELECT o.* FROM order_info o
WHERE NOT EXISTS (SELECT 1 FROM black_user b WHERE b.user_id = o.user_id);

（4）避免隐式类型转换

sql

-- user_id 是int，传入字符串，触发隐式转换，索引失效
WHERE user_id = '12345'

3. JOIN 关联优化（大数据最容易翻车）

核心规则：小表驱动大表

MySQL：INNER JOIN优化器会自动选择；LEFT JOIN左边是驱动表，尽量把小表放左边；
SparkSQL/Hive：执行引擎自动优化，但仍建议遵循；

❌ 错误：大表驱动小表

sql

-- order(千万级) LEFT JOIN user(万级)
FROM order o LEFT JOIN user u ON o.uid = u.uid

✅ 优化思路：先过滤大表，再关联

sql

FROM (SELECT uid,amount FROM order WHERE create_time >= '2026-07') o
LEFT JOIN user u ON o.uid = u.uid

禁止笛卡尔积

缺少关联条件 ON，两张表行数相乘，数据爆炸。

JOIN 字段要求

关联字段数据类型一致、建立索引；数仓场景尽量避免多表连环 JOIN（3 张以上谨慎）。

4. ORDER BY & GROUP BY 优化

排序尽量利用索引有序性
索引有序时，数据库无需额外 filesort；
如果无法使用索引排序：减少排序行数，先 WHERE 过滤再排序。
GROUP BY 先过滤，后聚合
❌

sql

SELECT status,count(*) FROM order_info GROUP BY status HAVING status=1;

✅

sql

SELECT status,count(*) FROM order_info WHERE status=1 GROUP BY status;

HAVING 在聚合后过滤；WHERE 在聚合前过滤，优先使用 WHERE。

大数据避免内存聚合溢出
MySQL 调大 sort_buffer_size / group_concat_max_len；分布式引擎开启溢写到磁盘。

5. LIMIT 分页深坑（千万级分页）

❌ 低效偏移，扫描大量数据

sql

SELECT id,name FROM order_info ORDER BY id LIMIT 1000000,20;

✅ 主键分页优化（书签分页）

sql

SELECT id,name FROM order_info WHERE id > 1000000 ORDER BY id LIMIT 20;

前端不要提供无上限跳页，大数据场景只支持上一页 / 下一页。

三、OLTP（MySQL/PostgreSQL 在线业务库）专属优化

1. 索引设计重中之重

不要滥用索引：写入（INSERT/UPDATE/DELETE）会维护索引，降低写入性能；
联合索引：把区分度高、等值条件放前面；
避免前缀模糊匹配 LIKE '%keyword'，无法走索引；LIKE 'keyword%' 可以；
区分度极低字段不要建索引（例如 status 只有 0/1）。

2. 避免锁与事务拖慢查询

大查询长时间占用事务，产生行锁、表锁，拖垮整个数据库；

超大报表不要在线主库执行，路由到只读从库。

3. 分批查询，禁止一次性拉取百万结果

java

运行

// 伪代码：分批游标查询，不要一次性加载全部数据
Long lastId = 0;
while(true) {
    List<Data> list = sql("select * from table where id > ? limit 1000", lastId);
    if(list.isEmpty()) break;
    lastId = list.getLast().getId();
}

四、OLAP（SparkSQL / Hive / ClickHouse 大数据数仓）专属优化

在线数据库和大数据引擎优化思路完全不同！

1. 分区（Partition）第一优先级

按时间分区（dt日期），查询强制带上分区过滤，避免扫描全量历史数据

sql

-- 必须携带分区条件
SELECT * FROM dwd_order WHERE dt >= '2026-07-29' AND dt <= '2026-07-30';

2. 存储格式优化

优先使用 ORC / Parquet 列式存储（压缩、谓词下推、跳过不读取列）
避免 Text 文本格式，IO 开销巨大

3. 谓词下推

尽量将过滤条件下推到表扫描阶段，不要等 JOIN / 聚合完成再过滤；

不要嵌套多余子查询阻止谓词下推。

4. ClickHouse 特殊要点

使用主键索引、稀疏索引；
避免高基数字段 GROUP BY；
使用 PREWHERE 替代 WHERE，提前过滤。

5. 数据倾斜（分布式 SQL 头号难题）

现象：某个 Reduce 任务执行极慢，大部分任务跑完等待单个节点

解决方案：

过滤倾斜 key；
加盐打散 JOIN 键；
开启引擎自动倾斜优化（Spark spark.sql.adaptive.enabled）

五、必备工具：执行计划（Explain）

优化 SQL 第一步：看执行计划！

sql

-- MySQL
EXPLAIN SELECT xxx FROM table WHERE ...;

-- PostgreSQL
EXPLAIN ANALYZE SELECT ...;

-- SparkSQL
EXPLAIN EXTENDED SELECT ...;

重点关注指标：

type（MySQL）：ALL 全表扫描 → 最差；ref/range 良好；
rows：预估扫描行数，越小越好；
Extra：Using filesort、Using temporary 代表需要优化；
分布式引擎：查看是否触发全表扫描、分区裁剪是否生效。

六、高频踩坑黑名单（大数据集严禁）

SELECT *
索引列使用函数、运算、隐式转换
超大 OFFSET 分页
不使用分区条件查询数仓大表
IN 子查询替代 EXISTS
多表无顺序疯狂 JOIN、笛卡尔积
业务高峰期在主库执行长时间大查询
一次性加载几十万行数据到应用内存
在 GROUP BY、ORDER BY 后大量过滤（滥用 HAVING）
使用 LIKE '%xxx%' 模糊检索（大数据建议使用搜索引擎 ES 替代）

七、通用优化流程（标准化步骤）

梳理需求：确认是否必须全量数据，能否缩小时间范围、过滤条件；
改写 SQL：只选必要字段，条件前置，优化 JOIN 顺序；
Explain 执行计划，定位全表扫描、文件排序、临时表；
添加合适索引（OLTP）/ 确认分区裁剪生效（OLAP）；
测试验证，对比执行耗时；
超大结果集实现分批 / 流式读取，防止 OOM；
报表类查询迁移至只读库 / 数仓，不占用在线业务库。

八、拓展架构方案（当 SQL 优化到达瓶颈）

如果单条 SQL 无论怎么优化依然很慢：

预计算：定时任务计算结果存入汇总表（宽表）；
索引升级：MySQL → Elasticsearch 全文检索；
冷热分离：历史数据归档至对象存储；
分库分表：业务库水平拆分；
同步数据到 OLAP 引擎（ClickHouse/Doris）做分析查询。

原文链接 https://www.yijunzhao.cn/archives/large-datasets-efficient-sql-guide

欢迎访问小易撩挨踢

https://www.yijunzhao.cn/

分享