OB 运维 | Join 估行不准选错执行计划该如何优化？

爱可生开源社区

发布于 2024-03-01 15:20:55

710

发布于 2024-03-01 15:20:55

文章被收录于专栏：爱可生开源社区爱可生开源社区

作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]，欢迎讨论。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1600 字，预计阅读需要 15 分钟。

数据库版本：OceanBase3.2.3.3

1问题描述

一个 Join 查询，关联字段包含组合主键中的第 1、2、4 个字段，走 Nested-Loop Join 时，被驱动表只能匹配主键的前两个字段，成本 cost1 较低，但实际效率不高；并且驱动表的扇出 n（也就是输出行数）估行比实际小很多。在计算总成本时：

Join 总成本 ≈（驱动表成本 + n*cost1）

在本案例中驱动表成本是固定的，执行计划中 n 的估算值只有 5000，但实际值有 60 万，cost1=154。计算成本时，n*cost1 比实际小很多，优化器最终选择了 Nested-Loop Join，如果被驱动表可以匹配主键的全部字段，效率是很高的但这里由于只能匹配前两个字段，效率较差，导致整个查询耗时非常长。

2分析过程

1. 分析执行计划

问题 SQL 如下（执行耗时 500s+）：

select
 count(*) from 
 (
  SELECT
    JGBM AS QYDJID,
    SEGMENT3 AS FNUMBER,
    PERIOD_NAME AS SSQJ,
    ...
  FROM
    (
      SELECT
        ...
      FROM
        DC_ACCOUNTBALANCE_TEMP A,
        DEF_ACCOUNTCONFIG B,
        DC_ACCOUNT C,
        NVAT_ACCANDTAXIDMAPFORP07 D,
        BI_CHOICEOFUNIT E
      WHERE
        A.SEGMENT1 = D.ZTJGBM
        AND D.SBDWID = E.SBDWID
        AND B.JGBM = E.DEPTCODE
        AND B.YXQSNY <= (
          substr(A.PERIOD_NAME, 4, 6) || substr(A.PERIOD_NAME, 1, 2)
        )
        AND (
          substr(A.PERIOD_NAME, 4, 6) || substr(A.PERIOD_NAME, 1, 2)
        ) <= B.YXJZNY
        AND C.QYDJID = B.SYZT
        AND C.FNUMBER = A.SEGMENT3
        AND C.ACCOUNTYEAR = substr(A.PERIOD_NAME, 4, 6)
        AND a.period_name = '10-2023'
    ) SUB
  GROUP BY
    JGBM,
    SEGMENT3,
    PERIOD_NAME
) X 
left join DC_ACCOUNTBALANCE A 
ON (
  A.SSQJ = X.SSQJ
  AND A.QYDJID = X.QYDJID
  AND A.FNUMBER = X.FNUMBER
);

执行计划如下（多余信息已删除），结合 SQL 内容进行解读：

X 表是 A、B、C、D、E 等 5 张表关联的结果，然后与 A 表进行关联查询。从执行计划看，主要成本在 X 表，因此先执行 X 部分确认是否慢在这部分，执行耗时只要 5 秒，结果有 61 万行，但执行计划中估行只有 5123。
X 部分很快，慢在 A 部分，因为是 Nested-Loop Join，A 作为被驱动表会循环查询 61万次（batch_join=false），每次查询走主键，执行计划13号算子中 range_key([A.SSQJ(0x7eb5a42ec400)], [A.QYDJID(0x7eb5a42ed840)], [A.DATAUSE(0x7ec8f84434e0)], [A.FNUMBER(0x7eb5a42eec80)]), range(MIN ; MAX) 部分信息说明索引里有 4个字段，但是range_cond([A.SSQJ(0x7eb5a42ec400) = ?(0x7ec8f8451e20)], [A.QYDJID(0x7eb5a42ed840) = ?(0x7ec8f8452950)])这部分表示只能用到索引的前两个字段，这会是慢的原因吗？有个信息可以提供佐证：A:table_rows:32310843, physical_range_rows:391, logical_range_rows:391 优化器估算A表每次查询需要扫描 391 行，这个效率确实是不高的。
在估算 Nested-Loop Join 的总成本时，计算逻辑是驱动表的成本+驱动表的扇出*\被驱动表查询一次的成本，这个 SQL 中驱动表的扇出（5123）比实际值（61 万）小很多，估算出的总成本比实际小很多。

=================================================================================
|ID|OPERATOR               |NAME                              |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                  |1        |3947739|
|1 | NESTED-LOOP OUTER JOIN|                                  |5123     |3947543|
|2 |  SUBPLAN SCAN         |X                                 |5123     |3154937|
|3 |   HASH GROUP BY       |                                  |5123     |3154861|
|4 |    HASH JOIN          |                                  |5123     |3149203|
|5 |     TABLE SCAN        |C                                 |81314    |31453  |
|6 |     HASH JOIN         |                                  |63573    |2940900|
|7 |      HASH JOIN        |                                  |1898     |35447  |
|8 |       TABLE SCAN      |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778    |
|9 |       HASH JOIN       |                                  |1736     |32462  |
|10|        TABLE SCAN     |E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660    |
|11|        TABLE SCAN     |B                                 |29154    |11277  |
|12|      TABLE SCAN       |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263|
|13|  TABLE SCAN           |A                                 |1        |154    |
=================================================================================

Outputs & filters: 
-------------------------------------
 ...
  13 - output([remove_const(1)(0x7ec8f846ba40)]), filter([A.FNUMBER(0x7eb5a42eec80) = ?(0x7ec8f8453480)]), 
      access([A.FNUMBER(0x7eb5a42eec80)]), partitions(p0), 
      is_index_back=false, filter_before_indexback[false], 
      range_key([A.SSQJ(0x7eb5a42ec400)], [A.QYDJID(0x7eb5a42ed840)], [A.DATAUSE(0x7ec8f84434e0)], [A.FNUMBER(0x7eb5a42eec80)]), range(MIN ; MAX), 
      range_cond([A.SSQJ(0x7eb5a42ec400) = ?(0x7ec8f8451e20)], [A.QYDJID(0x7eb5a42ed840) = ?(0x7ec8f8452950)])

Used Hint:
...

Optimization Info:
-------------------------------------
...
A:table_rows:32310843, physical_range_rows:391, logical_range_rows:391, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DC_ACCOUNTBALANCE],...

2. 分析表的统计信息

上一步我们分析得出：X 部分查询很快，慢在 A 表查询，要查询 61 万次。A 表查询时使用了主键的前两个字段，因此需要分析一下 A 表的统计信息，主键的 4 个字段的 NDV 分别是多少，结果如下：

SSQJ、QYDJID 两个字段的 NDV 并不高，每组值的重复次数可以通过统计信息估算：32310843/(85*972)=391，这个就是执行计划中的 physical_range_rows:391，意思就是每次查询大概要扫 391 行数据，这个效率如果只执行一次是没啥问题的，但这个 SQL 里需要执行 61 万次，总耗时就大了。
另外 SQL 中关联字段包含了主键的 3 个字段，不在条件里的第 3 个字段 DATAUSE 实际值都为 1，从逻辑上来看，SQL 中加上 AND A.DATAUSE = 1 条件的结果不会变，这样的好处是 A 表查询时可以使用主键的所有字段，每次只需要扫 1 行数据，效率会高很多。另一种更好的方式是主键中去掉 DATAUSE 字段，不过 OB 不支持修改主键。

--查询
select column_name,num_distinct from all_tab_col_statistics where table_name='DC_ACCOUNTBALANCE';
--结果
column_name        num_distinct
SSQJ                85
QYDJID              972
DATAUSE             1
FNUMBER             2616

3. 改写

方法 1：加 AND A.DATAUSE = 1

加条件后，SQL 耗时从 500 秒降到 8 秒，执行计划如下，A 表每次只要扫描 1 行：

=================================================================================
|ID|OPERATOR               |NAME                              |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                  |1        |3214924|
|1 | NESTED-LOOP OUTER JOIN|                                  |5123     |3214729|
|2 |  SUBPLAN SCAN         |X                                 |5123     |3154937|
|3 |   HASH GROUP BY       |                                  |5123     |3154861|
|4 |    HASH JOIN          |                                  |5123     |3149203|
|5 |     TABLE SCAN        |C                                 |81314    |31453  |
|6 |     HASH JOIN         |                                  |63573    |2940900|
|7 |      HASH JOIN        |                                  |1898     |35447  |
|8 |       TABLE SCAN      |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778    |
|9 |       HASH JOIN       |                                  |1736     |32462  |
|10|        TABLE SCAN     |E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660    |
|11|        TABLE SCAN     |B                                 |29154    |11277  |
|12|      TABLE SCAN       |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263|
|13|  TABLE GET            |A                                 |1        |11     |
=================================================================================
Outputs & filters: 
...
13 - output([remove_const(1)(0x7eb91646c790)]), filter(nil), 
      access([A.SSQJ(0x7eb91646b730)]), partitions(p0), 
      is_index_back=false, 
      range_key([A.SSQJ(0x7eae68cec980)], [A.QYDJID(0x7eae68ceddc0)], [A.DATAUSE(0x7eae68cf05d0)], [A.FNUMBER(0x7eae68cef200)]), range(MIN ; MAX), 
      range_cond([A.DATAUSE(0x7eae68cf05d0) = 1(0x7eae68cefeb0)], [A.SSQJ(0x7eae68cec980) = ?(0x7eb916451ce0)], [A.QYDJID(0x7eae68ceddc0) = ?(0x7eb916452810)], [A.FNUMBER(0x7eae68cef200) = ?(0x7eb916453340)])
...
Optimization Info:
-------------------------------------
A:table_rows:32310843, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback

改写 2：加 Hint 走 Hash Join

前面我们分析 A 表查询只能使用主键索引的前 2 个字段，效率不高，这种情况下可以看下 Hash Join 的执行效率，加 hint /*+ leading(X A) use_hash(A) */ 耗时只要 40 秒。执行计划如下，结合前面的分析进行解读：

被驱动表 A 除了关联条件没有其他条件，要做全表扫描，成本很高，所以总成本也很高，并且显然比 Nested-Loop Join 的成本高，在没有 Hint 干预的情况下，优化器会选 Nested-Loop Join。

=============================================================================
|ID|OPERATOR          |NAME                              |EST. ROWS|COST    |
-----------------------------------------------------------------------------
|0 |SCALAR GROUP BY   |                                  |1        |52828380|
|1 | HASH OUTER JOIN  |                                  |5123     |52828184|
|2 |  SUBPLAN SCAN    |X                                 |5123     |3154937 |
|3 |   HASH GROUP BY  |                                  |5123     |3154861 |
|4 |    HASH JOIN     |                                  |5123     |3149203 |
|5 |     TABLE SCAN   |C                                 |81314    |31453   |
|6 |     HASH JOIN    |                                  |63573    |2940900 |
|7 |      HASH JOIN   |                                  |1898     |35447   |
|8 |       TABLE SCAN |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778     |
|9 |       HASH JOIN  |                                  |1736     |32462   |
|10|        TABLE SCAN|E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660     |
|11|        TABLE SCAN|B                                 |29154    |11277   |
|12|      TABLE SCAN  |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263 |
|13|  TABLE SCAN      |A                                 |32310843 |12497986|
=============================================================================

3总结

这是一个很经典的问题：如果Join 时关联表太多，执行计划容易选错。

原因是估算驱动表的扇出很容易产生误差，尤其 Join 的结果作为驱动表时，相当于要估算 Join 的结果有多少行，这个误差会更大。而优化器在估算 Nested-Loop Join 算法的成本逻辑中，驱动表的扇出对计算结果影响很大，也就是说 Nested-Loop Join 的成本估算结果很容易产生误差，所以执行计划容易选错。

本文关键字：#OceanBase# #Join# #SQL优化#

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-02-28，如有侵权请联系 cloudcommunity@tencent.com 删除

统计