SQL 优化实践：关于增强 OceanBase本地索引性能的案例

原创

于 2024-03-11 19:59:08 发布 · 1.7k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#oceanbase #分布式数据库 #sql #实践经验 #性能优化 #Oracle迁移 #数据库迁移

本文通过一个案例，帮用户了解在Oracle迁移Oceanbase时，应该如何选择全局索引和本地索引以带来更好的SQL性能。

作者：胡呈清，爱可生的DBA团队成员，擅长故障分析和性能优化。本文约 5000 字，预计阅读需要 15 分钟。

数据库版本：OceanBase V3.2.3.3

案例的问题描述

在进行 Oracle 到 OceanBase的迁移过程中，有张表无法关闭 row movement 功能，导致无法使用 OMS直接迁移数据。为了解决这个问题，在割接窗口期之前，采用 dbcat 工具单独迁移该表结构，在窗口期内，通过数据导入的方式特别处理了这张表。

这是一张分区表，在 Oracle 数据库中，其主键约束并未包含分区键。然而OceanBase数据库要求主键必须包含分区键。因此，在进行Oracle数据迁移至 OceanBase 的过程中，我们有两种不同的处理方式：

OMS 工具：迁移时会将主键转成 全局唯一索引 +NOT NULL 约束，等价 Oracle 的主键约束。表没有显示主键，但会有一个隐式主键（分区键+隐藏自增列）；
dbcat 工具：迁移时会把分区键加入到主键中，这是个本地索引。

这里最主要的区别是：Oracle 上的主键是全局索引，用 dbcat 迁移到 OB 时会变成本地索引，用 OMS 迁移则还是全局索引。然后以下 SQL 做 nested-loop join 时关联字段是主键字段，每次到被驱动表上使用主键查找，需要对所有分区执行，因此慢了。

注：OMS、dbcat 都是迁移工具，不用深究，只需理解为什么会有这种区别即可，下面会做解释。

select
  *
from
  (
    SELECT
      a.act_Id as actId,
      a.data_Id as dataId,
      ...
    from
      T1 a,
      T2 b
    where
      a.data_Id = b.data_Id
      and a.cmp_Status not in ('08')
      and a.crt_Dttm >= to_date('2023-09-15 04:37:49', 'YYYY-mm-dd hh24:mi:ss')
      and a.crt_Dttm < to_date('2023-10-14 04:37:49', 'YYYY-mm-dd hh24:mi:ss')
      ...
    order by
      a.reserve_Begin_Dttm asc,
      a.act_Limit_Date asc,
      a.act_Id asc
  )
where
  rownum <= 10

关于全局索引和本地索引

OB 的官方文档上有非常详细的说明：局部索引和全局索引。因此本文只做些脉络上的补充。

1. 什么是全局索引、什么是本地索引？

首先只有分区表才有全局索引、本地索引的区分。先以 MySQL InnoDB 为例，分区表的每个分区实际上都有独立的表空间，完全可以把分区看成独立的表，因此对于一个索引来说，它也只能是每个分区维护各自的索引结构，这个就是本地索引，并且 InnoDB 只有本地索引，没有全局索引。

相反，一张表的所有分区如果只维护一个索引结构，这个就是全局索引。典型的 Oracle 支持全局索引，并且默认创建的都是全局索引。

2. 以 MySQL DBA 的视角来说，为什么要有全局索引？

从索引查找的效率上对比，分两种情况：

如果 SQL 带分区键查询，分区裁剪后只需要查找少量几个分区，则只需要对这几个分区上的所有进行查找即可，可以降低系统资源的使用，效率更高；
如果 SQL 不带分区键查询，没做分区裁剪，则本地索引需要对所有分区上的索引进行查找；同理，如果进行分区裁剪后还要查找多个分区也一样，会使用更多的系统资源，效率更慢。全局索引则只需要对一个大的索引进行查找，显然更节省成本。

3. Oracle 与 OB 主键的区别

Oracle 的主键约束 = 唯一索引+NOT NULL 约束；

OB 的数据结构上不同于 Oracle，Oracle 是堆表，索引上存的是数据行的指针，索引和数据是分开的。而 OB 是索引组织表，数据都在主键索引上，其他二级索引上存的是主键值。

因此对于分区表来说，OB 上每个分区的数据就是主键，主键必须是本地索引。然后由于主键有唯一约束，得保证全局唯一，而本地索引只能保证分区内唯一，怎么实现？不同的分区，分区键值一定是不一样的，所以可以通过分区键的唯一来保证主键的全局唯一，这就是为什么 OB 上的分区表要求主键必须包含分区键。

同理 Oracle 为什么不要求主键必须包含分区键？因为 Oracle 的主键约束默认创建的是全局唯一索引，它本身就能保证全局唯一，不需要携带分区键实现。Oracle 如果要创建本地唯一索引，也是要求包含分区键的。

4. OB 上全局索引带来的挑战

OB 是一个分布式数据库，全局索引和分区数据的分布位置肯定是不一样的，因此如果查找全局索引后要回表，很容易产生分布式事务，如果要回表的数据量很大，需要多次 rpc 交互，查询效率会下降很明显。

通常 OB 上适合使用全局索引的场景是：

基数很大的索引（即效率很高），高频的点查，并且 WHERE 条件中没有分区键，无法进行分区裁剪；
非分布式架构。

分析过程

介绍完本地索引和全局索引，下面回到慢 SQL 的分析上。

1. 测试复现

迁移到 OB 上被驱动表 b 的相关索引是：PRIMARY KEY(“DATA_ID”, “POLICY_VALID_DATE”)，Oracle 上对应的索引是：GLOBAL UNIQUE(“DATA_ID”)。

为了方便测试，在 OB 上再新建一张表，将两个索引都建上：PRIMARY KEY(“DATA_ID”, “POLICY_VALID_DATE”)，CONSTRAINT “UIDX_DATA_ID2” UNIQUE (“DATA_ID”)。

复现情况如下：

被驱动表默认走主键，进行 nested-loop join，耗时 90 秒
加 hint /*+ index(b UIDX_DATA_ID2) */ 执行，被驱动表强制走全局唯一索引，进行 nested-loop join，耗时只需要 5 秒

注意：这里驱动表输出 8 万行，join 结果也是 8 万行。

执行计划对比，走主键的执行计划：

==================================================================
|ID|OPERATOR                           |NAME    |EST. ROWS|COST  |
------------------------------------------------------------------
|0 |LIMIT                              |        |10       |237614|
|1 | PX COORDINATOR MERGE SORT         |        |10       |237614|
|2 |  EXCHANGE OUT DISTR               |:EX10001|10       |237565|
|3 |   LIMIT                           |        |10       |237565|
|4 |    TOP-N SORT                     |        |10       |237565|
|5 |     NESTED-LOOP JOIN              |        |353      |237420|
|6 |      EXCHANGE IN DISTR            |        |58       |234466|
|7 |       EXCHANGE OUT DISTR (BC2HOST)|:EX10000|58       |234297|
|8 |        PX PARTITION ITERATOR      |        |58       |234297|
|9 |         TABLE SCAN                |A       |58       |234297|
|10|      PX PARTITION ITERATOR        |        |7        |49    |
|11|       TABLE SCAN                  |B       |7        |49    |
==================================================================

Outputs & filters: 
-------------------------------------
  0 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), limit(?), offset(nil)
  1 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), sort_keys([A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00), ASC], [A.ACT_LIMIT_DATE(0x7e7d01e68970), ASC], [A.ACT_ID(0x7e7d01e62040), ASC])
  2 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e