OB 运维派 | 优化器走对，执行计划导致查询快 1000 倍

数据运维 2023-12-21 剑圣无痕手机阅读

任仲禹

爱可生数据库高级工程师，擅长故障分析和性能优化。

针对 OceanBase 3.2.3 BP8 版本，项目组反馈某 SELECT 语句在⼀段时间内查询耗时极慢，相比正常时耗时差 1000 倍以上。具体如下：

慢 SELECT

SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900-01-01' AND ACCTG_DT SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882'); +---------+ | TOT_CNT | +---------+ | 1 | +---------+ 1 row in set (0.02 sec) MySQL > SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE ACCTG_DT >= '1900-01-01' AND ACCTG_DT SELECT COUNT(*) AS TOT_CNT FROM renzy; +----------+ | TOT_CNT | +----------+ | 25435024 | +----------+ 1 row in set (10.65 sec)

正常执行不慢

MySQL > select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900-01-01' AND ACCTG_DT select * from gv$plan_cache_plan_stat where plan_id=7288229 G *************************** 1. row *************************** ... plan_id: 7288229 ... statement: select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = ?) AND LAWENF_NTIST_TP_CD NOT LIKE ? AND LAWENF_NTIST_TP_CD NOT LIKE ? AND EMRG_STPY_SRC_CD != ? AND ACCTG_DT >= ? AND ACCTG_DT = '' AND ACCTG_DT select * from oceanbase.gv$plan_cache_plan_explain where tenant_id=1005 and port=2882 and plan_id=7288229 and ip='12.240.26.70'G .... PLAN_LINE_ID: 2 OPERATOR: PHY_TABLE_SCAN NAME: renzy(renzy_I5) ROWS: 0 COST: 91 PROPERTY: table_rows:25419080, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[renzy_I2,renzy_I5] ...

为啥要走 I5 索引

通过下面的执行计划和执行耗时可知，第⼀次执行的语句因为字段 ACCTG_DT 检索不到数据，所以走 I5 效率最高。

MySQL > explain extended select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '' AND ACCTG_DT select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '' AND ACCTG_DT = '1900-01-01' AND ACCTG_DT select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '' AND ACCTG_DT select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900-01-01' AND ACCTG_DT select last_trace_id(); +-----------------------------------+ | last_trace_id() | +-----------------------------------+ | YB420CF01A46-0006009AD91C51ED-0-0 | +-----------------------------------+ 1 row in set (0.04 sec) MySQL > select * from oceanbase.gv$sql_audit where trace_id='YB420CF01A46- 0006009AD91C51ED-0-0'G ... TRACE_ID: YB420CF01A46-0006009AD91C51ED-0-0 ... SQL_ID: 2B53F4C1C330C2C089C7518CD71D667A QUERY_SQL: select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900- 01-01' AND ACCTG_DT select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900-01-01' AND ACCTG_DT select last_trace_id(); +-----------------------------------+ | last_trace_id() | +-----------------------------------+ | YB420CF01A46-0006009AD91C51ED-0-0 | +-----------------------------------+ 1 row in set (0.04 sec) MySQL > select * from oceanbase.gv$sql_audit where trace_id='YB420CF01A46- 0006009AD91C51ED-0-0'G ... TRACE_ID: YB420CF01A46-0006009AD91C51ED-0-0 ... SQL_ID: 2B53F4C1C330C2C089C7518CD71D667A QUERY_SQL: select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882') AND LAWENF_NTIST_TP_CD NOT LIKE '12%' AND LAWENF_NTIST_TP_CD NOT LIKE '05%' AND EMRG_STPY_SRC_CD != 'JZ05' AND ACCTG_DT >= '1900- 01-01' AND ACCTG_DT select * from gv$plan_cache_plan_stat where plan_id=7289113 G *************************** 1. row *************************** ... sql_id: 2B53F4C1C330C2C089C7518CD71D667A ... statement: select * from (SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = ?) AND LAWENF_NTIST_TP_CD NOT LIKE ? AND LAWENF_NTIST_TP_CD NOT LIKE ? AND EMRG_STPY_SRC_CD != ? AND ACCTG_DT >= ? AND ACCTG_DT = '' AND ACCTG_DT >> do 17 # 耗时 2分钟27s 命中 2023-11-14 16:05:36 1 YB420CF01A46-0006009B016C66EE-0-0 2023-11-14 16:08:03 >>> do 18 # 耗时 2min12s 命中 2023-11-14 16:08:03 1 YB420CF01A46-0006009AFF8FF46D-0-0 2023-11-14 16:10:15 >>> do 19 # 耗时 2min36s 命中 2023-11-14 16:10:15 1 YB420CF01A46-0006009B012FF1D0-0-0 2023-11-14 16:12:51 >>> do 20 # 耗时 1s内未命中，恢复正常 2023-11-14 16:12:51 1 YB420CF01A46-0006009AFEBDA7C6-0-0 2023-11-14 16:12:51 >>> do 21 2023-11-14 16:12:51 1 YB420CF01A46-0006009B016F1561-0-0 2023-11-14 16:12:52 ...