达梦数据库 SQL 优化入门 —— 连表查询

背景

你是一个电商平台的运营，需要统计最近90天内支付金额排名前10的客户，显示客户ID、姓名和总支付金额。。而你的电商平台与此相关的有两个表：

-- 用户表（1万条记录）
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    name          VARCHAR(50),
    register_date DATE,
    last_login    DATE
);

-- 订单表（300万条记录）
CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    order_status  VARCHAR(10),
    amount        DECIMAL(10,2)
);

-- 索引（常用过滤字段）
CREATE INDEX idx_orders_date_status_cust ON orders(order_date, order_status, customer_id);

使用sqlark生成数据

原实现

最开始，你按照 “常规思路” 写了 SQL—— 先把两张表连起来，再过滤条件、分组统计。

-- 使用提示器放置达梦优化器优化，强制先连表再过滤再统计，体现原有思路
SELECT /*+ USE_NL(c o) FULL(o) FULL(c) */
    c.customer_id,
    c.name,
    SUM(o.amount) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= SYSDATE - 90
  AND o.order_status = 'PAID'
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC
FETCH FIRST 10 ROWS ONLY;.name;

耗时6.6s左右

执行逻辑

1. orders（300 万行）先与 customers（1 万行）做 JOIN。

2. 在 JOIN 结果集上再做过滤、分组。

问题：

• JOIN 前几乎全表参与，产生数千万级别的中间结果。

• 后续过滤和聚合负担很重。

先把小表（customers）和大表（orders）全量连表，相当于要处理 “1万行 × 300 万行” 的潜在数据量（虽然实际不会完全笛卡尔积，但中间结果依然巨大），之后才过滤日期和订单状态 —— 相当于 “先把所有东西混在一起，再挑想要的”，效率极低。

优化

既然问题出在 “先连表、后处理大表”，那我们就反过来：先把大表的数据 “瘦身”，再和小表连表。这也是很多千万级数据查询优化的核心思路 —— 尽量在连表前，把大表的无效数据过滤掉、把需要聚合的结果算出来，减少连表的数据量。

优化后的 SQL 语句

SELECT 
    c.customer_id,
    c.name,
    t.total_amount
FROM customers c
JOIN (
    SELECT 
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE order_date >= SYSDATE - 90
      AND order_status = 'PAID'
    GROUP BY customer_id
    ORDER BY total_amount DESC
    FETCH FIRST 10 ROWS ONLY
) t ON c.customer_id = t.customer_id
ORDER BY t.total_amount DESC;

耗时0.14s

执行逻辑

1. 在 orders 表中，先做时间和状态过滤。

2. 在过滤结果上 GROUP BY customer_id，得到一个较小的中间表。

3. 最后再与 customers（小表）JOIN。

优势：

• 避免全表 JOIN。

• JOIN 之前数据量大幅缩小。

执行计划对比与解释

从达梦数据库返回的两份执行计划可以看到：

原始 SQL（低效写法）

1   #NSET2: [1040834077, 10, 147] 
2     #PRJT2: [1040834077, 10, 147]; exp_num(3), is_atom(FALSE) 
3       #SORT3: [1040834077, 10, 147]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #HAGR2: [1040834076, 10000, 147]; grp_num(2), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(C.CUSTOMER_ID, C.NAME) ; real_keys(1)
5           #SLCT2: [1039794279, 39872, 147]; C.CUSTOMER_ID = O.CUSTOMER_ID
6             #NEST LOOP INNER JOIN2: [1039794279, 39872, 147] 
7               #CSCN2: [1, 10000, 52]; INDEX33555511(CUSTOMERS as C); btr_scan(1)
8               #BLKUP2: [56, 39872, 95]; IDX_ORDERS_DATE_STATUS_CUST(O)
9                 #SLCT2: [56, 39872, 95]; O.ORDER_STATUS = 'PAID'
10                  #SSEK2: [56, 39872, 95]; scan_type(ASC), IDX_ORDERS_DATE_STATUS_CUST(ORDERS as O), scan_range[(exp11,min,min),(max,max,max)), is_global(0)

执行路径分析（从下往上看）：​​

1. ​​#SSEK2​ (第10行): 使用索引IDX_ORDERS_DATE_STATUS_CUST扫描订单表，估计扫描39872行

2. ​​#SLCT2​ (第9行): 过滤订单状态为’PAID’的记录

3. ​​#BLKUP2​ (第8行): 通过索引回表获取完整订单数据

4. ​​#CSCN2​ (第7行): 全表扫描客户表，扫描10000行

5. ​​#NEST LOOP INNER JOIN2​ (第6行): 对客户表和订单表进行嵌套循环连接

6. ​​#SLCT2​ (第5行): 应用连接条件C.CUSTOMER_ID = O.CUSTOMER_ID

7. ​​#HAGR2​ (第4行): 对连接结果进行分组聚合

8. ​​#SORT3​ (第3行): 对聚合结果进行排序

9. ​​#PRJT2​ (第2行): 投影操作，选择需要的列

10. ​​#NSET2​ (第1行): 返回最终结果集

---

优化 SQL（高效写法）

1   #NSET2: [62, 10, 147] 
2     #PRJT2: [62, 10, 147]; exp_num(3), is_atom(FALSE) 
3       #SORT3: [62, 10, 147]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #HASH2 INNER JOIN: [61, 10, 147]; RKEY_UNIQUE KEY_NUM(1); KEY(T.CUSTOMER_ID=C.CUSTOMER_ID) KEY_NULL_EQU(0)
5           #NEST LOOP INDEX JOIN2: [61, 10, 147] 
6             #ACTRL: [61, 10, 147]
7               #PRJT2: [60, 10, 95]; exp_num(2), is_atom(FALSE) 
8                 #SORT3: [60, 10, 95]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
9                   #HAGR2: [59, 5943, 95]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(ORDERS.CUSTOMER_ID) 
10                    #BLKUP2: [56, 39872, 95]; IDX_ORDERS_DATE_STATUS_CUST(ORDERS)
11                      #SLCT2: [56, 39872, 95]; ORDERS.ORDER_STATUS = 'PAID'
12                        #SSEK2: [56, 39872, 95]; scan_type(ASC), IDX_ORDERS_DATE_STATUS_CUST(ORDERS), scan_range[(exp11,min,min),(max,max,max)), is_global(0)
13            #BLKUP2: [1, 1, 4]; INDEX33555512(C)
14              #SSEK2: [1, 1, 4]; scan_type(ASC), INDEX33555512(CUSTOMERS as C), scan_range[T.CUSTOMER_ID,T.CUSTOMER_ID], is_global(0)
15          #CSCN2: [1, 10000, 52]; INDEX33555511(CUSTOMERS as C); btr_scan(1)

执行路径分析（从下往上看）：​​

1. ​​#SSEK2​ (第12行): 使用索引扫描订单表，估计扫描39872行

2. ​​#SLCT2​ (第11行): 过滤订单状态为’PAID’的记录

3. ​​#BLKUP2​ (第10行): 通过索引回表获取完整订单数据

4. ​​#HAGR2​ (第9行): 对过滤后的订单按客户ID进行分组聚合

5. ​​#SORT3​ (第8行): 对聚合结果排序并取前10名

6. ​​#PRJT2​ (第7行): 投影操作

7. ​​#ACTRL​ (第6行): 控制流程

8. ​​#SSEK2​ (第14行): 对前10名客户，使用索引快速查找客户信息

9. ​​#BLKUP2​ (第13行): 通过索引回表获取完整客户数据

10. ​​#NEST LOOP INDEX JOIN2​ (第5行): 嵌套循环连接前10名客户和他们的聚合结果

11. ​​#CSCN2​ (第15行): 全表扫描客户表（作为哈希连接的构建端）

12. ​​#HASH2 INNER JOIN​ (第4行): 哈希连接客户表和前10名客户的聚合结果

13. ​​#SORT3​ (第3行): 对最终结果进行排序

14. ​​#PRJT2​ (第2行): 投影操作

15. ​​#NSET2​ (第1行): 返回最终结果集

---

对比总结

• 原始写法：

  • 执行顺序是「JOIN → 过滤 → 聚合」

  • JOIN 阶段参与的数据过大，导致性能瓶颈。

• 优化写法：

  • 执行顺序变为「过滤 → 聚合 → JOIN」

  • 大表在 JOIN 前就“瘦身”，大幅减少中间结果集。