活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

&gt; 本文收录 6 个真实慢查询场景，每个案例完整呈现「现象 → 定位工具 → 根因分析 → 优化方案 → 优化效果」的完整链路，覆盖数据倾斜、错误执行计划、中间结果过大、函数阻止分区裁剪等典型问题。


---

## 案例一：全表扫描 3 亿行，分区裁剪未生效

### 现象

一条按日期过滤的查询，执行时间从预期的 5 秒变成 3 分钟：

```sql
SELECT dept_id, SUM(amount)
FROM orders
WHERE DATE_FORMAT(order_date, '%Y%m') = '202406'
GROUP BY dept_id;
```

### 定位

```sql
EXPLAIN
SELECT dept_id, SUM(amount)
FROM orders
WHERE DATE_FORMAT(order_date, '%Y%m') = '202406'
GROUP BY dept_id;
```

EXPLAIN 输出显示：`SeqScan orders`，`rows=300000000`（扫全表 3 亿行），没有出现分区裁剪的 `partition filter` 信息。

### 根因分析

`orders` 表按 `order_date` 做了 Range 分区，但查询条件写的是 `DATE_FORMAT(order_date, '%Y%m') = '202406'`。

**在列上使用函数，会让优化器无法识别分区裁剪条件**。优化器不知道 `DATE_FORMAT(order_date, '%Y%m') = '202406'` 等价于 `order_date BETWEEN '2024-06-01' AND '2024-06-30'`，因此老老实实扫了全表所有分区。

### 优化方案

```sql
-- 改写：直接用范围条件，让优化器识别分区边界
SELECT dept_id, SUM(amount)
FROM orders
WHERE order_date &gt;= '2024-06-01'
  AND order_date &lt;  '2024-07-01'
GROUP BY dept_id;
```

### 优化效果

EXPLAIN 确认只扫 `p2024q2` 一个分区，`rows` 从 3 亿降到 2500 万，执行时间从 3 分钟降至 8 秒。

**通用规则：分区键列不能套函数，否则分区裁剪失效。**

---

## 案例二：数据倾斜，1 个节点跑了 95% 的活

### 现象

按 `province`（省份）GROUP BY 的汇总查询，EXPLAIN 看起来正常，但实际执行需要 20 分钟。

```sql
SELECT province, COUNT(*), SUM(amount)
FROM user_orders
GROUP BY province;
```

### 定位

```sql
-- 开启执行时间统计
SET gcluster_dql_statistic_threshold = 0;

-- 执行查询后检查各节点耗时
SELECT node_name, exec_time, rows_processed
FROM gclusterdb.dql_statistic
ORDER BY start_time DESC
LIMIT 10;
```

输出发现：`node1` 的 `exec_time` 是 1198 秒，而 `node2`、`node3`、`node4` 的 `exec_time` 只有 12~15 秒。`node1` 处理了 2.8 亿行，其他节点各处理不足 50 万行。

### 根因分析

检查表的分布键：

```sql
SHOW CREATE TABLE user_orders;
-- 发现：DISTRIBUTED BY HASH(province)
```

`province` 只有 34 个取值（34 个省份），Hash 后大量数据集中在少数节点。更糟糕的是，某些省份（如广东）的数据量远大于其他省份，导致对应节点独自承担了绝大部分计算。

### 优化方案

**方案一（治本）：修改分布键**

重建表，改用高基数列做分布键：

```sql
-- 新建表，分布键改为高基数的 user_id
CREATE TABLE user_orders_new (
    order_id  BIGINT,
    user_id   BIGINT,
    province  VARCHAR(20),
    amount    DECIMAL(18,2),
    order_date DATE
) DISTRIBUTED BY HASH(user_id)  -- 高基数
PARTITION BY RANGE(order_date) (
    PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 迁移数据后替换原表
```

**方案二（治标）：GROUP BY 时启用多列重分布**

如果暂时不能重建表，对倾斜的 GROUP BY 启用多列 Hash 重分布，打散热点：

```sql
SET _t_gcluster_distinct_multi_redist = 1;
SET _t_gcluster_hash_redistribute_groupby_on_multiple_expression = 1;
```

### 优化效果

重建表后，数据均匀分布在 4 个节点，同样的查询从 20 分钟降至 45 秒。

---

## 案例三：笛卡尔积，中间结果撑满磁盘

### 现象

一条多表 JOIN 的查询执行了 2 小时后，gcluster 节点磁盘报警，/tmp 目录写满，查询报错终止。

```sql
SELECT a.order_id, b.product_name, c.customer_name
FROM orders a, products b, customers c
WHERE a.order_date = '2024-06-01';
-- 注意：JOIN 条件缺失！
```

### 定位

```sql
EXPLAIN ...
-- 输出中出现 CrossJoin（笛卡尔积）
-- orders 2500万行 × products 10万行 × customers 500万行 = 天文数字的中间结果
```

### 根因分析

SQL 的 `FROM` 子句列了三张表，但 `WHERE` 只有日期过滤，**没有 JOIN 条件**。这是经典的笛卡尔积错误，中间结果行数 = 表行数的乘积。

### 优化方案

补全 JOIN 条件，并为中间结果设置行数上限（防止类似事故再次发生）：

```sql
-- 正确写法
SELECT a.order_id, b.product_name, c.customer_name
FROM orders a
JOIN products  b ON a.product_id  = b.product_id
JOIN customers c ON a.customer_id = c.customer_id
WHERE a.order_date = '2024-06-01';
```

```ini
# 预防配置：gnode 的 gbase.cnf
# 中间结果超过 10 亿行时报错，而不是让磁盘撑满
_gbase_result_threshold = 1000000000
```

---

## 案例四：小表广播导致 JOIN 单节点执行

### 现象

`orders`（大表，分布表）JOIN `dim_channel`（渠道维度表，约 80 万行），查询需要 40 秒，但理论上 80 万行的维度表应该很快。

```sql
SELECT o.order_id, c.channel_name, o.amount
FROM orders o
JOIN dim_channel c ON o.channel_id = c.channel_id
WHERE o.order_date = '2024-06-01';
```

### 定位

```sql
EXPLAIN ...
-- 输出：Broadcast dim_channel（广播到所有节点）
-- 但 dim_channel 行数 80 万 &gt; 默认广播阈值
-- 实际执行走的是：Redistribute dim_channel → 只在 1 个节点做 HashJoin
```

查看参数：

```sql
SHOW VARIABLES LIKE 'gcluster_hash_redist_threshold_row';
-- 值：100000（10 万行），dim_channel 的 80 万行超过了阈值
-- 超过阈值的表不会被广播，而是走 Hash Redistribute
-- 但 Redistribute 后 dim_channel 在每个节点只有部分数据，需要再次跨节点传输
```

### 根因分析

80 万行的维度表超过了广播阈值，优化器选择了 Hash Redistribute，但 `dim_channel` 的分布键与 `orders` 的 `channel_id` 不对齐，导致多次数据 Shuffle，性能低下。

### 优化方案

**方案一（最优）：改为复制表**

80 万行约 100MB 数据，对于复制表可以接受：

```sql
CREATE TABLE dim_channel_rep (
    channel_id   INT,
    channel_name VARCHAR(64)
) REPLICATED;

INSERT INTO dim_channel_rep SELECT * FROM dim_channel;
```

复制表 JOIN 完全本地执行，无 Shuffle。

**方案二（临时）：调大广播阈值**

```sql
SET gcluster_hash_redist_threshold_row = 1000000;  -- 100 万行以下广播
SET gcluster_hash_redistribute_join_optimize = 1;
```

### 优化效果

改为复制表后，JOIN 完全在本地执行，40 秒降至 3 秒。

---

## 案例五：COUNT(DISTINCT) 超慢，内存压力大

### 现象

统计每日活跃用户数（DAU）的查询，在数据量 5 亿行时需要 25 分钟：

```sql
SELECT order_date, COUNT(DISTINCT user_id) AS dau
FROM user_behavior
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY order_date;
```

### 定位

```sql
EXPLAIN ...
-- 输出：Redistribute user_behavior by order_date, user_id
-- 然后在每个节点 HashAgg（COUNT DISTINCT）
-- user_id 基数极高（1 亿+），每个节点的 HashAgg 内存占用巨大
```

同时观察到 gnode 日志中有大量 `heap memory exceeds limit` 警告，说明 COUNT DISTINCT 的 HashAgg 内存不足，溢出到磁盘。

### 优化方案

开启两阶段 DISTINCT 优化，先在本地去重，大幅减少需要传输和最终聚合的数据量：

```sql
-- 方案一：开启参数优化
SET _gbase_optimizer_aggr_distinct = 1;
SET _t_gcluster_agg_distinct_redist_optimize = 1;
SET _t_gcluster_agg_distinct_redist_optimize_with_groupby = 1;
```

**方案二（彻底解决）：预聚合**

对于固定报表，用物化视图或预聚合中间表，避免每次查询都做大规模 COUNT DISTINCT：

```sql
-- 每天凌晨预计算前一天的 DAU
INSERT INTO dau_daily (order_date, dau)
SELECT order_date, COUNT(DISTINCT user_id)
FROM user_behavior
WHERE order_date = CURDATE() - INTERVAL 1 DAY
GROUP BY order_date;
```

### 优化效果

开启参数后，25 分钟降至 6 分钟。改用预聚合后，报表查询降至毫秒级。

---

## 案例六：OR 条件导致全表扫描

### 现象

带 OR 的过滤查询扫了全表，虽然每个 OR 分支单独查都很快：

```sql
SELECT * FROM orders
WHERE customer_id = 10001
   OR order_date = '2024-06-01';
```

### 根因分析

GBase 8a 的列存引擎对 AND 条件可以有效利用分区裁剪和列投影，但 **OR 条件难以优化**：要找到满足"A 或 B"的行，必须扫描两个条件各自覆盖的数据范围，最坏情况下等同于全表扫描。

### 优化方案

将 OR 改写为 UNION ALL，让每个分支各自走最优路径：

```sql
-- UNION ALL 改写：两个分支各自走分区裁剪和分布键过滤
SELECT * FROM orders WHERE customer_id = 10001
UNION ALL
SELECT * FROM orders WHERE order_date = '2024-06-01'
  AND customer_id != 10001;  -- 去重：排除第一个分支已包含的行
```

或者，如果两个条件不会同时满足（互斥）：

```sql
SELECT * FROM orders WHERE customer_id = 10001
UNION ALL
SELECT * FROM orders WHERE order_date = '2024-06-01';
-- 如果业务确认不会有重复，用 UNION ALL 比 UNION 快（无去重开销）
```

### 优化效果

UNION ALL 改写后，两个分支各自利用了分区裁剪，合并执行时间从 45 秒降至 4 秒。

---

## 慢 SQL 排查流程图

```
收到慢查询报告
      │
      ▼
1. EXPLAIN 看执行计划
   ├─ 有 CrossJoin？→ 检查 JOIN 条件是否完整
   ├─ rows 很大，无分区裁剪？→ 检查 WHERE 条件是否对列用了函数
   ├─ 有多次 Redistribute？→ 检查分布键 / 复制表设计
   └─ 单次 Redistribute 但很慢？→ 查步骤 2
      │
      ▼
2. 查 gclusterdb.dql_statistic
   ├─ 各节点 exec_time 差异 &gt; 10 倍？→ 数据倾斜，换分布键
   └─ 各节点时间相近但都很长？→ 查步骤 3
      │
      ▼
3. 检查具体算子
   ├─ HashAgg COUNT DISTINCT？→ 开启两阶段 distinct 参数
   ├─ Sort 大数据集？→ 加 LIMIT 或减小 ORDER BY 范围
   └─ Broadcast 大表？→ 检查广播阈值，或改复制表
      │
      ▼
4. 是否是固定报表场景？
   └─ 是 → 物化视图 / 预聚合中间表
```

---

## 常见慢查询原因速查

| 现象 | 最可能的根因 | 第一步排查 |
|------|-------------|-----------|
| 查询耗时比预期长 10 倍+ | 分区裁剪未生效 | EXPLAIN 看 rows 是否合理 |
| 某节点 CPU 100%，其他节点空闲 | 数据倾斜 | dql_statistic 各节点耗时对比 |
| /tmp 磁盘暴涨 | 笛卡尔积或 ORDER BY 超大结果集 | EXPLAIN 找 CrossJoin |
| 内存报警后查询报错 | COUNT DISTINCT 大基数 | gnode 日志检查 heap exceeds |
| 带 OR 的查询特别慢 | OR 无法下推，全表扫描 | 改写为 UNION ALL |
| JOIN 总在 1 个节点跑 | 小表被广播，超过阈值后变 Redistribute | 改为复制表或调阈值 |



性能调优

GBase 8a 慢 SQL 诊断实战案例集：从现象到根因的完整排查过程

GBase 8a 慢 SQL 诊断实战案例集：从现象到根因的完整排查过程

评论

热门帖子