openGauss慢查询终结者：WDR报告分析与SQL优化实战

📝 文章摘要：openGauss 自带了一套类似 Oracle AWR 的性能诊断工具 —— WDR（Workload Diagnosis Report）。本文从实战角度出发，讲解了如何生成、解读 WDR 报告，如何根据报告定位慢查询，以及如何通过索引推荐、SQL 改写、执行计划锁定等手段优化慢查询。文中的案例全部来自真实生产环境优化记录。

⏱ 预计阅读时间：15 分钟

🎯 WDR 是什么？跟 Oracle AWR 什么关系？

做过 Oracle DBA 的应该对 AWR（Automatic Workload Repository）很熟悉。openGauss 的 WDR 就是类似的东西 —— 定期采集数据库的负载快照，然后生成两份快照之间的性能差异报告。

# 安装 WDR 相关插件
gsql -d postgres -c "CREATE EXTENSION dbe_perf;"

# 手动创建 WDR 快照
gsql -d postgres -c "SELECT dbe_perf.create_wdr_snapshot();"

# 查看已有的快照
gsql -d postgres -c "SELECT * FROM dbe_perf.wdr_snapshot ORDER BY snapshot_id;"

输出示例：

 snapshot_id |           start_ts            |            end_ts
-------------+-------------------------------+-------------------------------
           1 | 2025-03-17 10:00:00+08        | 2025-03-17 10:30:00+08
           2 | 2025-03-17 10:30:00+08        | 2025-03-17 11:00:00+08

📊 生成 WDR 报告

-- 生成快照 1 到快照 2 之间的 WDR 报告
SELECT dbe_perf.generate_wdr_report(
         1, -- start_snapshot_id
         2, -- end_snapshot_id
         'summary', -- format: summary / detail / all
         'text' -- format_type: text / html
       );

我喜欢用 HTML 格式，看起来更直观：

-- 保存为 HTML 报告
\o
/tmp/wdr_report_1_2.html
SELECT dbe_perf.generate_wdr_report(1, 2, 'all', 'html');
\o

打开 HTML 报告，能看到以下核心模块：

🔎 如何快速定位慢查询？

WDR 报告内容很多，但作为开发者，我们关注的核心只有几个部分。

1. Top SQL by Elapsed Time

这是最重要的部分——哪些 SQL 消耗了最多时间。

-- 直接在数据库里查 TOP SQL
SELECT query_id,
       query,
       calls,
       total_time / calls AS avg_ms,
       shared_blks_hit,
       shared_blks_read, rows AS avg_rows
FROM dbe_perf.statement_history
WHERE start_time > now() - interval '1 hour'
ORDER BY total_time DESC
  LIMIT 20;

2. 等待事件分析

-- 查看数据库正在等待什么资源
SELECT event, wait_class, count(*)
FROM dbe_perf.wait_events
GROUP BY event, wait_class
ORDER BY count(*) DESC LIMIT 10;

常见的等待事件：

🛠️ 实战案例：三个慢查询的优化过程

案例 1：分页查询拖垮数据库

现象：WDR 报告中总耗时排名第一的 SQL，调用次数不多但每次跑了 5 秒以上。

-- 原始 SQL（MyBatis 生成）
SELECT *
FROM orders
ORDER BY created_at DESC LIMIT 20
OFFSET 99980;

分析：LIMIT 20 OFFSET 99980 意味着数据库要扫描 10 万行并丢弃前 99,980 行，只保留最后 20 行。

-- 执行计划
Limit
(cost=12345.67..12345.72 rows=20 width=128)
  ->  Index Scan Backward using idx_orders_created_at on orders  
        (cost=0.56..12345.67 rows=100000 width=128)
        -- 扫描了 10 万行！

优化方案：改为游标分页（Keyset Pagination）

-- 优化后（前端以滚动加载方式使用）
SELECT *
FROM orders
WHERE created_at < '2025-03-17 12:00:00' -- 上一页最后一条的时间
ORDER BY created_at DESC LIMIT 20;

-- 优化后的执行计划
Limit
(cost=0.56..12.34 rows=20 width=128)
  ->  Index Scan Backward using idx_orders_created_at on orders  
        (cost=0.56..12.34 rows=20 width=128)
        Index Cond: (created_at < '2025-03-17 12:00:00'::timestamp)
        -- 只扫描了 20 行！

效果：5.2 秒 → 8 毫秒，快了 650 倍。

💥 踩坑：游标分页在排序字段有重复值时漏数据

created_at 字段精度到毫秒，但同一毫秒内创建了多笔订单。游标分页用 < 条件会漏掉这些行。

-- 修复：用 (created_at, id) 复合条件确保唯一性
SELECT *
FROM orders
WHERE (created_at, id) < ('2025-03-17 12:00:00.123', 100456)
ORDER BY created_at DESC, id DESC LIMIT 20;

案例 2：N+1 查询导致的批量慢

现象：某个 API 响应时间从 50ms 涨到了 3 秒，WDR 报告显示有大量相同的单行查询。

-- 每次调用这个 API，MyBatis 循环查了 200 次
SELECT *
FROM users
WHERE id = ? -- 执行 200 次
SELECT *
FROM orders
WHERE user_id = ? -- 执行 200 次

优化方案：

-- 改为一对多查询
SELECT u.id,
       u.name,
       u.email,
       json_agg(json_build_object(
         'order_id', o.id,
         'amount', o.total_amount,
         'status', o.status
                )) AS orders
FROM users u
       LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (?, ?, ?, . . .) -- 一次性传入 200 个 ID
GROUP BY u.id;

效果：400 次查询 → 1 次查询，API 响应从 3 秒降回 60ms。

案例 3：UPDATE 没走索引

现象：WDR 报告里看到了大量的 DataFileWrite 等待，配合一条 UPDATE 语句，每次更新几千行但跑了 30 秒。

-- 原始 SQL
UPDATE orders
SET status = 'expired'
WHERE status = 'pending_payment'
  AND created_at < now() - interval '3 days';

分析：created_at 上有索引，但优化器没选，走了全表扫描。原因是 status 字段的分布不均匀（大部分已经是 ‘paid’ 或 ‘shipped’），优化器以为走全表更快。

优化方案：

-- 方案一：创建复合索引
CREATE INDEX idx_orders_status_created_at
  ON orders (status, created_at);

-- 方案二：强制走索引（在确认索引更优的情况下）
SET
enable_seqscan = off;

-- 方案三：拆分 SQL，按小时分批更新（避免大事务锁）
UPDATE orders
SET status = 'expired'
WHERE status = 'pending_payment'
  AND created_at >= now() - interval '4 days'
  AND created_at
    < now() - interval '3 days'
  AND created_at >= '2025-03-16 00:00:00'
  AND created_at
    < '2025-03-16 01:00:00';

效果：30 秒 → 0.8 秒，并且不阻塞其他事务。

📋 索引推荐

openGauss 5.0 开始内置了索引推荐功能（类似 MySQL 的 DAS）：

-- 对一条 SQL 做索引推荐
SELECT *
FROM dbe_perf.index_advisor(
  'SELECT * FROM orders WHERE user_id = 12345 AND status = ''paid'' ORDER BY created_at DESC'
     );

输出：

recommended_index                          | ddl_command
-------------------------------------------+-----------------------------------------------
orders(user_id, status, created_at desc)   | CREATE INDEX idx_orders_user_status_time ON ...

我们一般在开发环境用这个功能跑一遍所有慢查询，批量生成 DDL 脚本，评审后再上线。

💥 踩坑：索引推荐不是万能的

-- 索引推荐给了合并建议
-- 建议将 idx_orders_user_id 和 idx_orders_status 合并为 idx_orders_user_status

-- 但实际上，user_id 的查询条件中 90% 是精确匹配，不用带 status
-- 合并后索引变大了 30%，反而降低了缓存命中率

-- 我们保留了两个独立索引，只对联合查询场景建了第三个复合索引

原则：索引推荐作为参考，上线前要结合业务场景做取舍。不是所有推荐都该照单全收。

🔧 SQL 改写技巧

除了加索引，很多时候改写 SQL 效果更直接。

改写 1：用 EXISTS 代替 IN

-- 慢
SELECT *
FROM orders
WHERE user_id IN (SELECT id
                  FROM users
                  WHERE status = 'blocked');

-- 快（当子表很大时，EXISTS 可以提前终止）
SELECT *
FROM orders o
WHERE EXISTS (SELECT 1
              FROM users u
              WHERE u.id = o.user_id
                AND u.status = 'blocked');

改写 2：避免在 WHERE 条件中做函数计算

-- 慢（无法走索引）
SELECT *
FROM orders
WHERE DATE (created_at) = '2025-03-17';

-- 快（可走索引）
SELECT *
FROM orders
WHERE created_at >= '2025-03-17 00:00:00'
  AND created_at < '2025-03-18 00:00:00';

改写 3：用 WITH TIES 替代子查询

-- 原始：查每种状态的最新 10 条
SELECT *
FROM (SELECT *,
             ROW_NUMBER() OVER (
        PARTITION BY status ORDER BY created_at DESC
    ) AS rn
      FROM orders) t
WHERE rn <= 10;

-- openGauss 5.0+ 支持 WITH TIES（语法上更简洁，执行计划一样）
SELECT *
FROM orders
ORDER BY status, created_at DESC
  FETCH FIRST 10 ROWS
WITH TIES;

📈 WDR 定期生成与告警

#!/bin/bash
# /opt/scripts/wdr_monitor.sh — 每小时生成 WDR 报告并检查慢查询

PGHOST="localhost"
PGPORT="5432"
PGUSER="monitor"
PGDB="postgres"

# 获取最新的快照 ID
LAST_SNAPSHOT=$(gsql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDB \
    -t -c "SELECT MAX(snapshot_id) FROM dbe_perf.wdr_snapshot" | tr -d ' ')

# 如果少于 2 个快照，先创建一个
if [ "$LAST_SNAPSHOT" -lt 2 ]; then
    gsql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDB \
        -c "SELECT dbe_perf.create_wdr_snapshot()"
    exit 0
fi

PREV=$((LAST_SNAPSHOT - 1))
REPORT_FILE="/tmp/wdr_${PREV}_${LAST_SNAPSHOT}.html"

# 生成报告
gsql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDB \
    -c "SELECT dbe_perf.generate_wdr_report($PREV, $LAST_SNAPSHOT, 'all', 'html')" \
    -o $REPORT_FILE

# 检查 TOP SQL（平均耗时 > 1s 的触发告警）
gsql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDB \
    -t -c "
    SELECT query, total_time / calls AS avg_ms 
    FROM dbe_perf.statement_history
    WHERE start_time > now() - interval '1 hour'
      AND total_time / calls > 1000
    ORDER BY total_time DESC LIMIT 10;" | while read line; do
    if [ ! -z "$line" ]; then
        echo "[ALERT] Slow query detected: $line" | \
            mail -s "openGauss 慢查询告警" dba@company.com
    fi
done

❓ 常见问题

Q1：WDR 报告会消耗多少性能？

快照创建本身很轻量（约 100ms），我们每小时创建一次，对业务无影响。但如果快照保留过多，查询 dbe_perf.statement_history 会变慢。建议保留 7 天：

SELECT dbe_perf.clean_wdr_snapshot(7); -- 保留最近 7 天

Q2：WDR 和 pg_stat_statements 有什么区别？

pg_stat_statements 是累计统计，WDR 是时间段内的快照对比。WDR 更能反映"这段时间变慢了"的问题。

Q3：索引推荐一定要在生产库上跑吗？

建议在生产环境的只读副本上跑，或者用 EXPLAIN (ANALYZE, BUFFERS) 在测试环境复现。生产库直接跑分析查询本身也会消耗资源。

Q4：慢查询优化一般能从多少优化到多少？

我们统计了优化过的 50 条慢查询，平均优化幅度：98ms → 12ms（约 8 倍）。最夸张的一条从 5.2 秒到 8 毫秒（650 倍）。

📈 慢查询排查流程图

📊 优化工具选择决策树

根据不同的慢查询场景，选择合适的优化工具：

📝 总结

WDR 报告是 openGauss 最重要的性能诊断工具，没有之一。方法论总结为三步：

发现问题 → WDR 定位 TOP SQL + 等待事件
分析问题 → 查看执行计划、检查索引、评估 SQL 改写
解决问题 → 加索引 / 改 SQL / 调参数 → 验证效果

不需要把 WDR 报告的全部内容都看懂。盯住 Top SQL by Elapsed Time 和 Wait Events 两个模块，解决了 TOP 10 的 SQL，基本就解决了 80% 的性能问题。

💬 互动：你遇到过最离谱的慢查询是因为什么原因？加了索引就好的那种不算，必须是查了三天才发现原因的那种。