SQL优化过程

背景

当前数据查询量大，早期SQL开发侧重功能实现，未充分考虑服务器资源（CPU、内存）消耗，导致数仓服务器负载较高。目前数仓主要由两大系统构成：MaxCompute（简称MC）和 Hologres（简称Holo）。

  • Hologres：基于内存的实时查询系统，速度快，适合几百GB以内的在线查询，但对硬件要求高。
  • MaxCompute：基于Hadoop的离线计算平台，支持PB级数据处理，资源消耗低，但数据延迟为T+1，适合批处理。

由于Hologres承担了大部分查询任务，其CPU和内存占用居高不下，亟需通过SQL优化降低资源消耗。

⚠️ 注意：所有SQL优化必须基于实际业务逻辑，避免因优化导致数据错误。

优化方案

核心目标：在联表查询过程中，尽可能减少中间结果集的数据量，从而降低计算和内存开销。

联表方式对比

联表类型 说明 优化建议
笛卡尔积 两表行数相乘，数据量爆炸，性能最差 避免使用
Inner Join 仅保留两表匹配的记录 适合两表均有数据的场景
Left Join 以左表为基础，右表无匹配则补NULL 建议左表尽可能小
Right Join 以右表为基础，左表无匹配则补NULL 建议右表尽可能小

实际优化案例

1. 全量计算，增量更新

场景

某用户信息表 target_user_info 需每小时从源表 source_user_data 计算更新。

问题

原始SQL全量扫描 source_user_data（约300万条），性能差。

优化思路

只处理当日更新的数据，大幅减少中间表数据量。

优化前（全量扫描）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH base AS (
    SELECT
        a.user_id,
        a.level,
        b.name,
        b.dept_id,
        a.supervisor_id,
        CASE WHEN a.supervisor_id = 0 THEN '' ELSE b2.name END AS supervisor_name,
        CASE WHEN a.supervisor_id = 0 THEN 0 ELSE a2.level END AS supervisor_level
    FROM public.source_user_data a
    LEFT JOIN public.user_profile b ON b.user_id = a.user_id
    LEFT JOIN public.source_user_data a2 ON a2.user_id = a.supervisor_id
    LEFT JOIN public.user_profile b2 ON b2.user_id = a.supervisor_id
)
SELECT * FROM base;

优化后（增量处理）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH base AS (
    SELECT
        a.user_id,
        a.level,
        b.name,
        b.dept_id,
        a.supervisor_id,
        CASE WHEN a.supervisor_id = 0 THEN '' ELSE b2.name END AS supervisor_name,
        CASE WHEN a.supervisor_id = 0 THEN 0 ELSE a2.level END AS supervisor_level
    FROM public.source_user_data a
    LEFT JOIN public.user_profile b 
        ON date(to_timestamp(b.update_time)) = current_date 
        AND b.user_id = a.user_id
    LEFT JOIN public.source_user_data a2 
        ON date(to_timestamp(a2.update_time)) = current_date 
        AND a2.user_id = a.supervisor_id
    LEFT JOIN public.user_profile b2 
        ON date(to_timestamp(b2.update_time)) = current_date 
        AND b2.user_id = a.supervisor_id
    WHERE date(to_timestamp(a.update_time)) = current_date
)
SELECT * FROM base;

效果

  • 数据量：300万 → 4000（降低约1000倍）
  • 执行时间：3919ms → 190ms（**提升95%**）
  • 内存占用：33G → 7.4G（**降低79%**）

2. 分页查询，减少展示

场景

用户排行榜接口，通常只展示前50名。

优化前（无分页）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH sales_data AS (
    SELECT user_id, SUM(sales_amount) AS total_sales
    FROM sales_daily
    WHERE brand_id = 'B12345'
      AND sale_date BETWEEN '20240807' AND '20241104'
    GROUP BY user_id
),
user_rank AS (
    SELECT u.user_id, u.nick, s.total_sales
    FROM user_info u
    JOIN sales_data s ON u.user_id = s.user_id
)
SELECT ur.*, p.profile_name, p.avatar
FROM user_rank ur
JOIN user_profile p ON ur.user_id = p.user_id
ORDER BY ur.total_sales DESC
-- 无 LIMIT，返回全部数据

优化后（添加分页）

1
2
3
...
ORDER BY ur.total_sales DESC
LIMIT 50 OFFSET 0;

效果

  • 执行时间：13190ms → 2447ms（**提升81.4%**）
  • 内存占用：基本持平

3. 内连接替代左连接

场景

用户与销售数据关联，业务要求用户必须有销售记录。

优化前（使用 LEFT JOIN）

1
2
3
4
...
FROM user_info u
LEFT JOIN sales_data s ON u.user_id = s.user_id
...

优化后（改用 INNER JOIN）

1
2
3
4
...
FROM user_info u
INNER JOIN sales_data s ON u.user_id = s.user_id
...

效果

  • 执行时间：1727ms → 451ms（**提升74.1%**）
  • 内存占用：23G → 7.3G（**降低68.3%**）
  • 数据一致性：验证无误

4. 合理选择数仓引擎

示例：查询销售明细表前10万条

引擎 执行时间
Hologres 165ms
MaxCompute 1473ms

✅ 建议：Hologres用于在线查询，MaxCompute用于离线批处理

5. 批量计算 + 全量查询分离

在 MaxCompute 中创建 Hologres 外表

1
2
3
4
5
6
7
8
9
CREATE EXTERNAL TABLE IF NOT EXISTS holo_user_group_mc (
    user_id STRING,
    group_name STRING
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
LOCATION 'jdbc:postgresql://<host>:80/dbname?currentSchema=dim&table=user_group'
TBLPROPERTIES (
  'odps.federation.jdbc.target.db.type' = 'holo'
);

6. 使用 Serverless 模式隔离查询

1
2
3
$this->query("SET hg_computing_resource = 'serverless';");
$result = $this->query($sql);
$this->query("RESET hg_computing_resource;");

7. 索引优化

聚簇索引（按用户ID排序查询）

1
2
3
4
5
6
7
CREATE TABLE dim_user_sales (
    user_id BIGINT,
    sale_date STRING,
    amount DECIMAL
) WITH (
    clustering_key = 'user_id'
);

位图索引（低基数字段，如用户类型）

适用于字段值较少的分类字段（如：普通用户、VIP、管理员等）。

总结

优化方法 适用场景 性能收益
增量更新 按时间更新的表 高
分页查询 展示类接口，只看前N条 高
内连接替代左连接 两表必须匹配的场景 高
合理选择数仓引擎 区分在线查询与离线计算 中高
Serverless 查询 临时、突发查询 资源隔离
聚簇索引 按某字段排序或范围查询 高
位图索引 低基数分类字段 中

✅ 核心思想：减小中间结果集，按需加载，合理分配计算资源。