从 33GB 到 7.4GB：一次 Hologres SQL 优化实战

背景

随着 CRM 数据持续增长，一批早期为了快速交付而编写的 SQL 开始暴露问题：查询能返回正确结果，但会长期占用大量 CPU 和内存，影响同一集群中的其他在线请求。

当前数仓主要由两类计算引擎承担：

问题的核心不是简单地把所有查询迁移到某一个引擎，而是让正确的任务运行在正确的位置，并尽可能减少每个执行阶段需要处理的数据量。

文中的表名与业务字段均已脱敏。所有优化都必须先验证业务语义和结果集一致性，再比较性能。

先建立一个判断标准

一条分析 SQL 的成本，通常由以下因素共同决定：

1. 扫描了多少行、多少列；
2. JOIN 前后的中间结果有多大；
3. 是否发生大规模排序、聚合或数据重分布；
4. 是否把离线任务放到了在线查询引擎；
5. 数据分布、索引与过滤条件能否被执行计划利用。

因此，这次优化始终遵循一个原则：尽量让数据在进入 JOIN、GROUP BY 和 ORDER BY 之前就变小。

一、全量计算改成增量计算

原任务每小时刷新一次用户关系数据，但每次都会从约 300 万行的主表开始计算，再连续关联多个用户、门店和上下级关系表。

简化后的原始结构如下：

with base as (
  select
    su.user_id,
    su.manager_id,
    store.store_id,
    manager.level as manager_level
  from social_user su
  left join store_user store
    on store.user_id = su.user_id
  left join social_user manager
    on manager.user_id = su.manager_id
)
select * from base;

业务侧绝大多数数据是冷数据，每小时真正发生变化的只是一小部分记录。因此可以用更新时间限定本次参与计算的数据，并把结果增量覆盖到目标表。

with changed_user as (
  select *
  from social_user
  where update_time >= date_trunc('day', current_timestamp)
),
changed_store as (
  select *
  from store_user
  where update_time >= date_trunc('day', current_timestamp)
),
base as (
  select
    su.user_id,
    su.manager_id,
    store.store_id,
    manager.level as manager_level
  from changed_user su
  left join changed_store store
    on store.user_id = su.user_id
  left join social_user manager
    on manager.user_id = su.manager_id
)
select * from base;

主表参与计算的数据量由约 300 万行降至约 4000 行。实测结果：

这里有两个必要前提：

• 目标表必须支持可靠的增量覆盖或 UPSERT；
• 需要处理迟到数据、更新时间不可靠、任务失败重跑等情况，不能只写一个 current_date 条件就认为数据一定完整。

更稳妥的做法是维护任务水位，并为水位保留一段回看窗口：

where update_time >= :last_success_time - interval '2 hours'
  and update_time < :current_task_time

二、只返回真正需要的数据

排行榜页面只展示前 50 条，但旧查询会返回全部结果。加入分页后，查询时间从 13190ms 降至 2447ms，约降低 81.4%。

select
  user_id,
  total_profit,
  sale_count
from user_sales
order by total_profit desc, sale_count desc, user_id desc
limit 50 offset 0;

不过，LIMIT 并不保证上游扫描与聚合也只处理 50 行。如果 SQL 同时存在全量聚合、全量排序或 count(*) over()，数据库仍可能完成大部分计算后才截取结果。

优化分页时还应检查：

• 排序字段是否能利用索引或数据分布；
• 页面是否真的需要在每次请求中计算总数；
• 深分页是否可以改成游标分页。

例如，用上一页最后一条记录作为游标：

select
  user_id,
  total_profit,
  sale_count
from user_sales
where (total_profit, sale_count, user_id)
    < (:last_profit, :last_sale_count, :last_user_id)
order by total_profit desc, sale_count desc, user_id desc
limit 50;

游标分页可以避免 offset 很大时反复扫描并丢弃前面的结果。

三、在语义允许时用 INNER JOIN 收敛结果集

旧 SQL 中多处使用 LEFT JOIN，但后续业务条件要求左右两侧都必须存在数据。这会让无效行先进入中间结果，再在后续阶段被过滤。

-- 原查询
from users u
left join filtered_sales fs
  on u.user_id = fs.group_id
left join crm_user cu
  on cu.user_id = u.user_id

确认业务语义后，可以改成内连接：

from users u
inner join filtered_sales fs
  on u.user_id = fs.group_id
inner join crm_user cu
  on cu.user_id = u.user_id

实测中，输出行数与业务结果一致，性能变化如下：

这项优化不能机械套用。LEFT JOIN 会保留左表中没有匹配项的记录，INNER JOIN 则只保留匹配成功的记录。必须通过行数、关键指标汇总和抽样数据三层校验，确认结果语义没有变化。

四、Hologres 负责在线查询，MaxCompute 负责离线计算

同一份明细数据可能同时存在于 Hologres 和 MaxCompute。针对 10 万行的交互式查询，本次测试中 Hologres 约为 165ms，MaxCompute 约为 1473ms，前者延迟低约 89%。

但这不意味着所有任务都应该放到 Hologres：

原始明细
   |
   v
MaxCompute：全量清洗、批量聚合、T+1 计算
   |
   v
Hologres：结果表、实时增量、在线 API 查询

对于 PB / TB 级离线计算，让 MaxCompute 读取 Hologres 外表或原始明细，在离线侧完成聚合，再把结果同步回 Hologres，能够避免批任务抢占在线查询资源。

外表结构可以简化为：

create external table if not exists user_group_external (
  user_id string,
  group_name string
)
stored by 'com.aliyun.odps.jdbc.JdbcStorageHandler'
with serdeproperties (
  'odps.properties.rolearn' = '<ram-role-arn>'
)
location 'jdbc:postgresql://<hologres-endpoint>/<database>?table=<schema.table>';

连接地址、角色 ARN 和库表信息应通过配置管理，不要直接提交到公开仓库。

五、用 Serverless 隔离偶发重查询

少量线上请求必须查询较大的 Hologres 数据集时，可以考虑使用 Serverless 计算资源，把重查询与常驻实例隔离。

set hg_computing_resource = 'serverless';
 
select *
from sales_daily
where pay_day between :start_day and :end_day;
 
reset hg_computing_resource;

可以通过 EXPLAIN 检查执行计划是否使用了目标计算资源。

需要注意：Serverless 解决的是资源隔离和弹性问题，不会自动修复低效 SQL。扫描范围、JOIN 膨胀和错误索引仍然需要单独优化，同时还要评估冷启动、配额和成本。

六、根据查询模式设计索引

聚簇索引

当查询经常按某个字段做范围过滤或有序读取时，可以评估聚簇键：

create table user_sales_result (
  user_id bigint,
  pay_day text,
  pay_price numeric
)
with (
  clustering_key = 'user_id'
);

聚簇键不是越多越好。它更适合稳定、高频的过滤路径，并会增加写入和数据重排成本。

位图索引

培训师、社群类型、状态等低基数字段，适合评估位图索引：

create table social_group (
  user_id bigint,
  group_type text,
  status text
)
with (
  bitmap_columns = 'group_type,status'
);

高基数、频繁更新的字段不适合直接套用位图索引。最终仍应以真实查询计划和压测结果为准。

七、避免几个看起来有效的误区

误区 1：驱动表越小就一定越快

优化器通常会重排 JOIN 顺序，真正重要的是统计信息是否准确、过滤能否下推，以及 JOIN 后的数据是否膨胀。不要只靠 SQL 书写顺序判断执行成本。

误区 2：加了 LIMIT 就不会全表计算

LIMIT 主要限制返回行数。它能否减少扫描、聚合和排序，取决于执行计划、索引和算子下推情况。

误区 3：INNER JOIN 永远比 LEFT JOIN 快

两者首先是业务语义不同，其次才是性能差异。只有确认未匹配行本来就应该被丢弃，才能替换。

误区 4：换到更快的引擎就解决了问题

把低效的全量任务直接搬到内存型引擎，只会更快地消耗完资源。引擎分工与 SQL 本身的优化必须同时进行。

如何验证一次优化没有改坏数据

每次改写都至少做以下检查：

-- 1. 对比总行数
select count(*) from result_before;
select count(*) from result_after;
 
-- 2. 对比关键指标
select
  sum(pay_price),
  sum(sale_count),
  count(distinct user_id)
from result_before;
 
-- 3. 找出两侧差集
select * from result_before
except
select * from result_after;

性能测试也应保持条件一致：使用同一时间范围和数据快照，区分冷缓存与热缓存，多次执行取稳定值，并同时记录耗时、峰值内存、扫描行数和输出行数。

最终检查清单

• 是否能把全量任务改成带水位和补偿窗口的增量任务；
• 是否在扫描阶段就完成时间、租户和状态过滤；
• 是否只读取必要列，避免无意义的 select *；
• JOIN 类型是否与业务语义一致，中间结果是否发生膨胀；
• 排行榜是否只取 Top N，深分页是否可以使用游标；
• 全量聚合是否应迁移到 MaxCompute，Hologres 只保留查询结果；
• 偶发重查询是否需要 Serverless 资源隔离；
• 聚簇键、位图索引和数据分布是否匹配真实访问路径；
• 优化前后是否完成行数、汇总值、差集和业务抽样校验。

总结

这次优化最有效的动作并不是某一个 SQL 语法，而是把计算链路重新拆开：冷数据不重复算，在线接口不承担离线全量任务，无效行不进入后续 JOIN，页面不请求用不到的数据。

最终，核心查询从 3919ms 降至 190ms，内存从 约 33GB 降至约 7.4GB。更重要的是，这套方法能被复用到后续任务：先测量，再缩小数据集；先验证语义，再比较速度。