分区表 local 索引排序的性能问题

问题描述

两表关联查询，两个 query，如下。

query1：

select a.*,r.client_list_status as clientListStatus,r.client_detail_status as clientDetailStatus,r.end_status as endStatus from xxxx_xxxx_info as a,xxxx_xxxx_status_copy as r where a.xxxxx_code=r.xxxxx_code and a.feedback_id=343888266 order by a.gmt_create desc, a.id desc LIMIT 100;

query2：

select a.*,r.client_list_status as clientListStatus,r.client_detail_status as clientDetailStatus,r.end_status as endStatus from xxxx_xxxx_info as a,xxxx_xxxx_status_copy as r where a.xxxxx_code=r.xxxxx_code and a.feedback_id=344138822 order by a.gmt_create desc, a.id desc LIMIT 100;

两表 a 表为大表，做了 feedback_id 的分区，b 表为小表，为复制表。

上述两个查询中 feedback_id 343888266 结果集在 5w 多行，344138822 结果集在一百多行。

大表创建了排序字段的 local 索引 KEY idx_gmt_create_id (gmt_create, id) LOCAL。

现象：

1. 执行计划一：走 a 表主键 FULL SCAN 的执行计划，对小集合 id 查询很快，10ms 内，大集合 id 查询慢，在 400 多 ms。

   Query Plan: ==============================================================
   |ID|OPERATOR          |NAME                   |EST. ROWS|COST|
   --------------------------------------------------------------
   |0 |LIMIT             |                       |19       |613 |
   |1 | TOP-N SORT       |                       |19       |613 |
   |2 |  NESTED-LOOP JOIN|                       |19       |592 |
   |3 |   TABLE SCAN     |a                      |18       |46  |
   |4 |   TABLE SCAN     |r(uk_xxxxx_code_vaild)|2        |30  |
   ==============================================================
   Outline Data:
   -------------------------------------
   
   /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("distribute_online.a"@"SEL$1" "distribute_online.r"@"SEL$1" ))
      USE_NL(@"SEL$1" ("distribute_online.r"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("distribute_online.r"@"SEL$1" ) LOCAL LOCAL)
      NO_USE_NL_MATERIALIZATION(@"SEL$1" ("distribute_online.r"@"SEL$1" ))
      FULL(@"SEL$1" "distribute_online.a"@"SEL$1")
      INDEX(@"SEL$1" "distribute_online.r"@"SEL$1" "uk_xxxxx_code_vaild")
      END_OUTLINE_DATA
   */
   

2. 执行计划二：走 a 表 local 索引的执行计划，对小集合 id 查询很慢，平均 5 秒+ 超时，对大集合 id 查询很快，在 100ms 内。

   Query Plan: =====================================================================
   |ID|OPERATOR         |NAME                        |EST. ROWS|COST   |
   ---------------------------------------------------------------------
   |0 |LIMIT            |                            |10       |8145322|
   |1 | NESTED-LOOP JOIN|                            |10       |8145322|
   |2 |  TABLE SCAN     |a(idx_gmt_create_id,Reverse)|10       |8145034|
   |3 |  TABLE SCAN     |r(uk_xxxxx_code_vaild)     |2        |30     |
   =====================================================================
   Outline Data:
   -------------------------------------
   /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("distribute_online.a"@"SEL$1" "distribute_online.r"@"SEL$1" ))
      USE_NL(@"SEL$1" ("distribute_online.r"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("distribute_online.r"@"SEL$1" ) LOCAL LOCAL)
      NO_USE_NL_MATERIALIZATION(@"SEL$1" ("distribute_online.r"@"SEL$1" ))
      INDEX(@"SEL$1" "distribute_online.a"@"SEL$1" "idx_gmt_create_id")
      INDEX(@"SEL$1" "distribute_online.r"@"SEL$1" "uk_xxxxx_code_vaild")
      END_OUTLINE_DATA
   */
   

   走执行计划一，query1 查询耗时 400 多ms。走执行计划二的话，query2 耗时 5-6 秒甚至超时。

问题原因

索引逆序扫描的时候，存储层访问优化不适用，导致性能不行。

适用版本

OceanBase 数据库 V3.x 版本。

解决方法

• 升级 OceanBase 数据库至 V4.2.1 GA (oceanbase-4.2.1.0-100000182023092722) 版本。该版本支持逆序下压。

• 增加 feedback_id 为索引前缀，改善扫描性能。