基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
Buffer 表导致的执行计划异常跳变案例分析
更新时间：2026-08-25 02:41
分享
适用版本：V4.3.x内容类型：Troubleshoot
问题现象
在 OceanBase 数据库环境中，特定 SQL 语句在收集统计信息后，执行计划发生了异常跳变，从原本的索引扫描变为全表扫描，导致查询性能显著下降。此 SQL 在收集统计信息后的短时间内（约 4 分钟），执行计划由 0.1 秒的索引扫描变为 16 秒的全表扫描。具体 SQL 如下：
SELECT *
FROM table_name
WHERE `id` = 'A1A5FQE7R5RISB'
AND `market_id` = 'ATVPDKIKX0DER'
AND time BETWEEN '2025-11-04 00:00:00' AND '2025-11-04 23:59:59'
异常计划：
mysql> explain extended select * FROM table_name WHERE `amazon_seller_id` = 'A1A5FQE7R5RISB' AND `amazon_marketplace_id` = 'ATVPDKIKX0DER' AND rc_time BETWEEN '2025-11-10 00:00:00' AND '2025-11-10 23:59:59';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------------------------------------------- |
| |0 |EXCHANGE IN REMOTE | |1381 |560028 | |
| |1 |└─EXCHANGE OUT REMOTE| |1381 |536901 | |
| |2 | └─TABLE FULL SCAN |table_name |1381 |484834 | |
| =============================================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false,false], |
| range_key([table_name.uid(0x7ff208a26590)]), range(MIN ; MAX)always true |
| Optimization Info: |
| ------------------------------------- |
| table_name: |
| table_rows:3411252 |
| physical_range_rows:3666185 |
| logical_range_rows:3417123 |
| output_rows:1381 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[idx_rctime, index_sku, idx_goodsUid, snapshot_date, index_storageUid_sku, idx_goodsUid_rctime, idx_amazonsellerid_amazonmarketplaceid_rctime, cerp_amazon_report_fba_inventory_planning_data] |
| pruned_index_name:[idx_rctime, index_sku, idx_goodsUid, snapshot_date, index_storageUid_sku, idx_goodsUid_rctime] |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
216 rows in set (0.01 sec)
指定索引后的计划：
mysql> explain extended select /*+ INDEX(@"SEL$1" "cerp_bi_data"."table_name"@"SEL$1" "idx_amazonsellerid_amazonmarketplaceid_rctime") */ * FROM table_name WHERE `amazon_seller_id` = 'A1A5FQE7R5RISB' AND `amazon_marketplace_id` = 'ATVPDKIKX0DER' AND rc_time BETWEEN '2025-11-10 00:00:00' AND '2025-11-10 23:59:59';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------------------------------------------------------------------------------- |
| |0 |EXCHANGE IN REMOTE | |1072 |1549917 | |
| |1 |└─EXCHANGE OUT REMOTE| |1072 |1531955 | |
| |2 | └─TABLE RANGE SCAN |table_name(idx_amazonsellerid_amazonmarketplaceid_rctime) |1072 |1491517 | |
| ============================================================================================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| is_index_back=true, is_global_index=false, |
| range_key([table_name.amazon_seller_id(0x7fed85a24cd0)], [table_name.amazon_marketplace_id(0x7fed85a25810)], |
| [table_name.rc_time(0x7fed85a26930)], [table_name.uid(0x7fed85a27840)]), range(A1A5FQE7R5RISB, |
| ATVPDKIKX0DER,2025-11-10 00:00:00.000000,MIN ; A1A5FQE7R5RISB,ATVPDKIKX0DER,2025-11-10 23:59:59.000000,MAX), |
| range_cond([table_name.amazon_seller_id(0x7fed85a24cd0) = cast('A1A5FQE7R5RISB'(0x7fed85bad4a0), VARCHAR(1048576))(0x7fed85bad230)(0x7fed85bacfc0)], |
| [table_name.amazon_marketplace_id(0x7fed85a25810) = cast('ATVPDKIKX0DER'(0x7fed85badc80), VARCHAR(1048576))(0x7fed85bada10)(0x7fed85bad7a0)], |
| [table_name.rc_time(0x7fed85a26930) >= cast('2025-11-10 00:00:00'(0x7fed85bae460), MYSQL_DATETIME(-1, -1))(0x7fed85bae1f0)(0x7fed85badf80)], |
| [table_name.rc_time(0x7fed85a26930) <= cast('2025-11-10 23:59:59'(0x7fed85baec40), MYSQL_DATETIME(-1, -1))(0x7fed85bae9d0)(0x7fed85bae760)]) |
| Optimization Info: |
| ------------------------------------- |
| table_name: |
| table_rows:3411252 |
| physical_range_rows:41044 |
| logical_range_rows:1072 |
| index_back_rows:1072 |
| output_rows:1072 |
| avaiable_index_name:[idx_rctime, index_sku, idx_goodsUid, snapshot_date, index_storageUid_sku, idx_goodsUid_rctime, idx_amazonsellerid_amazonmarketplaceid_rctime, table_name] |
| pruned_index_name:[idx_rctime, index_sku, idx_goodsUid, snapshot_date, index_storageUid_sku, idx_goodsUid_rctime, table_name] |
| stats info:[version=2025-11-09 22:05:35.027100, is_locked=0, is_expired=0] |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
220 rows in set (0.01 sec)
SQL 谓词条件特点
- 等值条件：
amazon_seller_id = 'A1A5FQE7R5RISB' - 等值条件：
amazon_marketplace_id = 'ATVPDKIKX0DER' - 范围条件：
rc_time BETWEEN '2025-11-04 00:00:00' AND '2025-11-04 23:59:59'
这是一个典型的复合条件查询，涉及多个索引列的组合。
-- 主键索引
PRIMARY KEY (`uid`)
-- 关键索引
KEY `idx_amazonsellerid_amazonmarketplaceid_rctime` (`amazon_seller_id`, `amazon_marketplace_id`, `rc_time`) BLOCK_SIZE 16384 LOCAL
KEY `idx_storage_sku_rctime` (`storageCode`, `sku`, `rc_time`) BLOCK_SIZE 16384 LOCAL
问题原因
触发这个问题主要是由于自动收集统计信息触发计划重新生成，并且由于 buffer 表特性导致物理行数远大于逻辑行数，代价计算错误地使用物理行数乘以前缀选择率（V4.3.5 列存代价模型调整接口引入的），使得索引扫描成本被严重高估，从而选择全表扫描而非索引扫描，导致查询性能从 0.1 秒下降到 16 秒。正确代价计算方式：逻辑行数 × 索引前缀选择率，错误路径：使用物理行数 × 前缀选择率。
关键信息
- 收集统计信息后，由于 buffer 表特性导致物理行数远大于逻辑行数，代价计算错误地使用物理行数乘以前缀选择率（V4.3.5 列存代价模型调整接口引入的），导致 SQL 执行计划发生跳变。
- 走索引的计划执行时间为 0.1 秒，走全表扫描的计划执行时间为 16 秒。
问题的风险及影响
- 查询性能显著下降，可能导致系统响应延迟。
影响租户
MySQL 租户、Oracle 租户均受影响。
适用版本
问题由 4.3.5 列存代价模型调整接口引入，预期修复版本为 4.3.5 BP5 Hotfix3。
解决方法
- 绑定索引：通过绑定特定的索引来强制优化器使用索引扫描，避免全表扫描。
- 应急方法：立即检查并确认是否可以绑定索引，以快速恢复查询性能。
规避方式
- 对于 buffer 表的使用场景建议绑定索引，建议定期检查 SQL 执行计划，特别是在收集统计信息后。
- 考虑在 HTAP 环境中开启 SPM（SQL Plan Management），以便更好地管理和控制 SQL 执行计划。
背景知识
基表估行中三种选择率的作用
估行方法：estimation method:[OPTIMIZER STATISTICS]，关闭存储层估行，只使用统计信息。
create table t1(c1 int, c2 int, c3 int, c4 int, index i1(c1,c2,c3));
insert into t1 select uniform(1, 10, random()),uniform(1, 10, random()),uniform(1, 5, random()),uniform(1, 2, random()) from table(generator(1000));
select * from t1 where c1 = 1 and c3 = 1 and c4 = 1;
下面有个实验生成表数据，全表行数 1000，三个谓词 c1 = 1、c3 = 1、c4 = 1 的选择率分别是 0.1、0.2、0.5，并且相互独立。
call dbms_stats.gather_table_stats(null, 't1', method_opt=>'for all columns size 1');
这三个谓词分别属于：
- prefix filter：索引扫描使用的 filter，
c1 = 1。 - postfix filter：索引扫描后，回表前需要计算的 filter，
c3 = 1。 - table filter：回表后计算的 filter，
c4 = 1。
行估算结果：
- 全表行数（
table_rows）：1000 - 索引扫描的行数（
logical_range_rows = table_rows × prefix_filter_sel）：$100 = 1000 \times 0.1$ - 回表的行数（
index_back_rows = logical_range_rows × postfix_filter_sel）：$44 = 100 \times 0.44$ - 基表输出的行数（
output_rows = index_back_rows × table_filter_sel）：$37 = 44 \times 0.84$
obclient(root@mysql)[test]> explain extended_noaddr select /*+ opt_param('_enable_storage_cardinality_estimation', 'false') */ * from t1 where c1 = 1 and c3 = 1 and c4 = 1;
+---------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------+
| ================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------- |
| |0 |TABLE RANGE SCAN|t1(i1)|38 |132 | |
| ================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter([t1.c3 = 1], [t1.c4 = 1]), rowset=256 |
| access([t1.__pk_increment], [t1.c1], [t1.c3], [t1.c4], [t1.c2]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[true,false], |
| range_key([t1.c1], [t1.c2], [t1.c3], [t1.__pk_increment]), range(1,MIN,MIN,MIN ; 1,MAX,MAX,MAX), |
| range_cond([t1.c1 = 1]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| OPT_PARAM('_ENABLE_STORAGE_CARDINALITY_ESTIMATION' 'false') |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| INDEX(@"SEL$1" "test"."t1"@"SEL$1" "i1") |
| OPTIMIZER_FEATURES_ENABLE('') |
| OPT_PARAM('_ENABLE_STORAGE_CARDINALITY_ESTIMATION' 'false') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:1000 |
| physical_range_rows:100 |
| logical_range_rows:100 |
| index_back_rows:44 |
| output_rows:37 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[i1, t1] |
| unstable_index_name:[t1] |
| stats info:[version=2025-12-14 20:21:46.504877, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS] |
| Plan Type: |
| LOCAL |
| Parameters: |
| :0 => 1 |
| :1 => 1 |
| :2 => 1 |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+---------------------------------------------------------------------------------------------------------+
54 rows in set (0.009 sec)
估行方法：estimation method:[OPTIMIZER STATISTICS, STORAGE]，存储层估行结果为 84。
行估算结果（含存储层估行）：
- 全表行数（
table_rows）：1000 - 索引扫描的行数 = 存储层估行结果（
logical_range_rows = storage estimation result）：logical_range_rows:84 - 回表的行数（
index_back_rows = logical_range_rows × postfix_filter_sel）：$37 = 84 \times 0.44$ - 基表输出的行数（
output_rows = index_back_rows × table_filter_sel）：$31 = 37 \times 0.84$
obclient(root@mysql)[test]> create table t1(c1 int, c2 int, c3 int, c4 int, index i1(c1,c2,c3));
Query OK, 0 rows affected (1.624 sec)
obclient(root@mysql)[test]> insert into t1 select uniform(1, 10, random()),uniform(1, 10, random()),uniform(1, 5, random()),uniform(1, 2, random()) from table(generator(1000));
Query OK, 1000 rows affected (0.192 sec)
Records: 1000 Duplicates: 0 Warnings: 0
obclient(root@mysql)[test]> call dbms_stats.gather_table_stats(null, 't1', method_opt=>'for all columns size 1');
Query OK, 0 rows affected (1.921 sec)
obclient(root@mysql)[test]> explain extended_noaddr select * from t1 where c1 = 1 and c3 = 1 and c4 = 1;
+---------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------+
| ================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------- |
| |0 |TABLE RANGE SCAN|t1(i1)|32 |111 | |
| ================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter([t1.c3 = 1], [t1.c4 = 1]), rowset=256 |
| access([t1.__pk_increment], [t1.c1], [t1.c3], [t1.c4], [t1.c2]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[true,false], |
| range_key([t1.c1], [t1.c2], [t1.c3], [t1.__pk_increment]), range(1,MIN,MIN,MIN ; 1,MAX,MAX,MAX), |
| range_cond([t1.c1 = 1]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| INDEX(@"SEL$1" "test"."t1"@"SEL$1" "i1") |
| OPTIMIZER_FEATURES_ENABLE('') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:1000 |
| physical_range_rows:84 |
| logical_range_rows:84 |
| index_back_rows:37 |
| output_rows:31 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[i1, t1] |
| unstable_index_name:[t1] |
| stats info:[version=2025-12-14 20:21:46.504877, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
| Plan Type: |
| LOCAL |
| Parameters: |
| :0 => 1 |
| :1 => 1 |
| :2 => 1 |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+---------------------------------------------------------------------------------------------------------+
52 rows in set (0.149 sec)
为什么只有 c1 能抽取为 range
select * from t1 where c1 = 1 and c3 = 1 and c4 = 1;
索引 range 抽取规则：
- 必须是索引列：
c1、c3是索引列，c4不是。 - 必须形成连续前缀：
c1是第 1 列，可以作为前缀；c3是第 3 列，但c2未指定，不连续。 - 等值条件优先：
c1=1是等值条件，适合 range 抽取。
条件分析：
| 条件 | 索引位置 | 是否连续前缀 | 可抽取 range | 处理方式 |
|---|---|---|---|---|
| c1 = 1 | 第 1 列 | 是 | 是 | range 条件 |
| c3 = 1 | 第 3 列 | 否（c2 缺失） | 否 | 索引内过滤 |
| c4 = 1 | 非索引列 | 否 | 否 | 表级过滤 |
