GBase 8a 查询优化实战：EXPLAIN 解读、物化视图与慢查询调优

本文从实际遇到的慢查询出发，讲解如何用 EXPLAIN 读懂执行计划、物化视图的正确使用姿势、CTE 的适用场景，以及几个高频的查询调优技巧。

一、用 EXPLAIN 读懂执行计划

1.1 基本用法

EXPLAIN
SELECT dept_id, SUM(amount)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY dept_id;

GBase 8a 的 EXPLAIN 输出是一个树形结构，每行代表一个执行算子（Operator）。读取顺序是从下往上、从内到外——最底层的算子最先执行。

1.2 关键算子解读

1.3 重点看 Redistribute

Redistribute 意味着数据需要跨节点传输，是 MPP 查询中最大的网络开销来源。如果 EXPLAIN 中出现多次 Redistribute，需要思考：

• JOIN 的两张表分布键是否一致？（不一致则必须 Redistribute）
• GROUP BY 列是否是分布键？（不是则需要 Redistribute 后再聚合）

优化目标：减少 Redistribute 次数，最好降至 0（全部本地操作）。

1.4 一个实际案例

原始慢查询（~30 秒）：

SELECT
    o.dept_id,
    d.dept_name,
    SUM(o.amount) AS total
FROM orders o
JOIN dept d ON o.dept_id = d.dept_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.dept_id, d.dept_name;

EXPLAIN 显示：

1. SeqScan orders（分区裁剪，只扫 2024 年）
2. Redistribute orders by dept_id（orders 的分布键是 customer_id，不是 dept_id）
3. SeqScan dept（dept 是小表，分布表）
4. Redistribute dept by dept_id
5. HashJoin
6. HashAgg
7. Gather

问题：dept 是一个只有 100 行的小表，却被当成分布表做了 Redistribute。

解法：将 dept 改为复制表，彻底消除两次 Redistribute：

-- 重建 dept 为复制表
CREATE TABLE dept_rep (
    dept_id   INT,
    dept_name VARCHAR(64)
) REPLICATED;

INSERT INTO dept_rep SELECT * FROM dept;

改完后 EXPLAIN 只剩：SeqScan → HashJoin（本地）→ HashAgg → Gather，耗时降至 3 秒。

二、物化视图：预计算加速分析查询

2.1 什么是物化视图

普通视图是一个 SQL 别名，每次查询都实时计算。物化视图（Materialized View）把查询结果持久化存储，后续查询直接读预计算结果，适合频繁查询但数据更新不频繁的聚合报表场景。

2.2 创建物化视图

-- 创建一个按部门、按日汇总的销售物化视图
CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT
    dept_id,
    order_date,
    COUNT(*)            AS order_cnt,
    SUM(amount)         AS total_amount,
    AVG(amount)         AS avg_amount
FROM orders
GROUP BY dept_id, order_date;

2.3 物化视图的权限要求

这是实际使用中最容易踩的坑。 物化视图底层需要读取 gclusterdb 中的系统元数据，创建或查询时如果报以下错误：

ERROR: View 'gclusterdb.xxx' references invalid table(s) or column(s)
       or function(s) or definer/invoker of view lack rights to use them

解决方法：给物化视图的使用者授予 gclusterdb 的查询权限：

-- 以管理员账号执行
GRANT SELECT ON gclusterdb.* TO 'your_user'@'%';

授权后即可正常创建和查询物化视图。

2.4 刷新物化视图

物化视图的数据不会自动随底层表更新，需要手动刷新：

-- 全量刷新（清空后重新计算，期间查询会看到旧数据）
REFRESH MATERIALIZED VIEW mv_sales_daily;

GBase 8a 目前不支持增量刷新（REFRESH ... WITH DATA），只有全量刷新。对于超大基表，全量刷新耗时较长，建议在业务低峰期执行，或者改用基于时间分区的手动维护策略（每天只重算昨日分区数据写入汇总表）。

2.5 查询重写（Query Rewrite）

GBase 8a 支持基于物化视图的自动查询重写：当用户查询与物化视图的定义匹配时，优化器自动将查询重定向到物化视图：

-- 原始查询
SELECT dept_id, SUM(amount) FROM orders
WHERE order_date = '2024-06-01'
GROUP BY dept_id;

-- 如果 mv_sales_daily 已存在且包含该数据
-- 优化器可能自动改写为：
-- SELECT dept_id, total_amount FROM mv_sales_daily
-- WHERE order_date = '2024-06-01'

是否触发重写取决于优化器的代价估算。可通过 EXPLAIN 确认是否命中物化视图：

EXPLAIN SELECT dept_id, SUM(amount) FROM orders
WHERE order_date = '2024-06-01'
GROUP BY dept_id;
-- 输出中若出现 mv_sales_daily，说明命中了物化视图

三、CTE（WITH AS）：复杂查询的可读性与性能

3.1 开启 CTE 支持

GBase 8a 默认关闭 CTE，需要在配置文件中开启：

# gcluster 和 gnode 的 gbase.cnf 均需配置
_t_gcluster_support_cte = 1

3.2 CTE 基本用法

WITH
  -- 第一步：过滤有效订单
  valid_orders AS (
      SELECT order_id, customer_id, dept_id, amount
      FROM orders
      WHERE order_date >= '2024-01-01'
        AND status = 1
  ),
  -- 第二步：按客户汇总
  customer_summary AS (
      SELECT customer_id, SUM(amount) AS total, COUNT(*) AS cnt
      FROM valid_orders
      GROUP BY customer_id
  )
-- 最终查询
SELECT *
FROM customer_summary
WHERE total > 10000
ORDER BY total DESC
LIMIT 100;

3.3 CTE 与子查询的性能差异

GBase 8a 中，CTE 并不总是比子查询快。需要根据情况判断：

用 CTE 更好的场景：

• 同一个中间结果在 SQL 中被引用多次（避免重复计算）
• 逻辑分层明显，可读性需求高

相同子查询多次复用时的优化参数：

-- 开启相同子查询缓存复用优化（减少重复计算）
SET _t_gcluster_reuse_tmp_table_optimize = 1;

用普通子查询更好的场景：

• CTE 只引用一次，优化器无法将其下推时，反而增加了一次中间结果的物化开销

四、高频慢查询场景与调优手段

场景 1：COUNT(DISTINCT) 很慢

-- 统计每个部门的不重复用户数
SELECT dept_id, COUNT(DISTINCT customer_id) FROM orders GROUP BY dept_id;

对于高基数的 DISTINCT 操作，GBase 8a 默认的执行方式可能在大数据集上较慢。调整以下参数：

-- 开启 distinct 时的重分布优化（两阶段 distinct）
SET _t_gcluster_agg_distinct_redist_optimize = 1;

-- 如果同时有 GROUP BY，也开启：
SET _t_gcluster_agg_distinct_redist_optimize_with_groupby = 1;

-- gnode 上的 count distinct 切分算法
-- 0 = 默认；1 = 两阶段（先局部去重再全局）
SET _gbase_optimizer_aggr_distinct = 1;

场景 2：ORDER BY + LIMIT 很慢

当 ORDER BY 的列不是分布键时，gcluster 需要从所有 gnode 收集数据后再做全局排序。数据量大时这一步很慢。

优化思路：

1. 如果是报表场景，先在 gnode 做局部 Top-N，再在 gcluster 做最终归并（GBase 8a 通常会自动做此优化）
2. 避免对超大结果集做 ORDER BY 而不加 LIMIT，会触发全量数据传输到 gcluster

场景 3：GROUP BY 唯一值多导致内存溢出

对于高基数的 GROUP BY（如按 user_id 聚合，有数亿种取值），每个 gnode 的局部聚合会占用大量内存。可开启延迟聚合优化：

-- 对 group 列唯一值多的情况，延迟聚合以减少内存峰值
SET gcluster_delayed_group_by_optimize = 1;

场景 4：大量小 JOIN 导致只有 1 个节点工作

当一张大表和多张小表 JOIN 时，如果小表行数低于阈值，优化器会把小表广播（Broadcast）出去，导致只有 1 个节点做 HashJoin，无法并行。

调整广播阈值和重分布优化参数：

-- 调整触发广播的行数阈值（默认较小，适当调大）
SET gcluster_hash_redist_threshold_row = 1000000;

-- 开启 JOIN 重分布优化
SET gcluster_hash_redistribute_join_optimize = 1;
SET gcluster_hash_redistribute_groupby_optimize = 1;

五、查询调优方法论小结

1. 先跑 EXPLAIN，找出 Redistribute 和全表扫描
       ↓
2. 检查过滤条件是否命中分区裁剪（EXPLAIN 中的 rows 是否大幅减少）
       ↓
3. 检查 JOIN 的两张表分布键是否对齐
       ↓
4. 小表是否已建为复制表（REPLICATED）
       ↓
5. 是否存在高基数 COUNT(DISTINCT) 或大 GROUP BY（调整相关参数）
       ↓
6. 是否存在数据倾斜（某个 gnode 耗时远高于其他）
   → 查 gclusterdb.dql_statistic，对比各节点耗时
       ↓
7. 必要时用物化视图预计算，或在应用层做数据分层

好的执行计划通常的特征：

• 最多 1 次 Redistribute（甚至 0 次）
• 尽早过滤数据（过滤条件下推到 gnode）
• 小表以 Broadcast 而非 Redistribute 方式参与 JOIN
• 各 gnode 执行时间接近（无数据倾斜）