--- title: Buffer 表导致的执行计划异常跳变案例分析-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于Buffer 表导致的执行计划异常跳变案例分析相关的常见问题和使用技巧，帮助您快速解决Buffer 表导致的执行计划异常跳变案例分析的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # Buffer 表导致的执行计划异常跳变案例分析 更新时间：2026-08-25 02:41 适用版本： V4.3.x 内容类型：Troubleshoot ## 问题现象 在 OceanBase 数据库环境中，特定 SQL 语句在收集统计信息后，执行计划发生了异常跳变，从原本的索引扫描变为全表扫描，导致查询性能显著下降。此 SQL 在收集统计信息后的短时间内（约 4 分钟），执行计划由 0.1 秒的索引扫描变为 16 秒的全表扫描。具体 SQL 如下： ```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' ``` 异常计划： ```plain 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) ``` 指定索引后的计划： ```plain 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 谓词条件特点 1. 等值条件：`amazon_seller_id = 'A1A5FQE7R5RISB'` 2. 等值条件：`amazon_marketplace_id = 'ATVPDKIKX0DER'` 3. 范围条件：`rc_time BETWEEN '2025-11-04 00:00:00' AND '2025-11-04 23:59:59'` 这是一个典型的复合条件查询，涉及多个索引列的组合。 ```sql -- 主键索引 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]`，关闭存储层估行，只使用统计信息。 ```sql 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，并且相互独立。 ```sql call dbms_stats.gather_table_stats(null, 't1', method_opt=>'for all columns size 1'); ``` 这三个谓词分别属于： 1. prefix filter：索引扫描使用的 filter，`c1 = 1`。 2. postfix filter：索引扫描后，回表前需要计算的 filter，`c3 = 1`。 3. table filter：回表后计算的 filter，`c4 = 1`。 行估算结果： 1. 全表行数（`table_rows`）：1000 2. 索引扫描的行数（`logical_range_rows = table_rows × prefix_filter_sel`）：$100 = 1000 \times 0.1$ 3. 回表的行数（`index_back_rows = logical_range_rows × postfix_filter_sel`）：$44 = 100 \times 0.44$ 4. 基表输出的行数（`output_rows = index_back_rows × table_filter_sel`）：$37 = 44 \times 0.84$ ```plain 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。 行估算结果（含存储层估行）： 1. 全表行数（`table_rows`）：1000 2. 索引扫描的行数 = 存储层估行结果（`logical_range_rows = storage estimation result`）：`logical_range_rows:84` 3. 回表的行数（`index_back_rows = logical_range_rows × postfix_filter_sel`）：$37 = 84 \times 0.44$ 4. 基表输出的行数（`output_rows = index_back_rows × table_filter_sel`）：$31 = 37 \times 0.84$ ```sql 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) ``` ```plain 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 ```sql 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 | 非索引列 | 否 | 否 | 表级过滤 | 上一篇 [无主键表的 ROWID 和隐藏主键 __pk_increment](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000938731) 下一篇 [如何把 queuing 表转回普通表](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207716) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线