--- title: 如何查看 OceanBase 数据库中的 Buffer 表-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于如何查看 OceanBase 数据库中的 Buffer 表相关的常见问题和使用技巧，帮助您快速解决如何查看 OceanBase 数据库中的 Buffer 表的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 如何查看 OceanBase 数据库中的 Buffer 表 更新时间：2026-05-21 01:56 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x 内容类型：TechNote 在 LSM-Tree 架构下，对数据行的删除并不是原地删除，而是通过写入一行 Delete 操作来标记删除，如果某个用户业务表主要操作为频繁的插入与删除，从绝对数据量上来看可能并不大，但是由于 LSM-Tree 的架构特征，会导致对这类表的增量数据中存在大量的 Delete 标记，即使用户的实际扫描范围很小，但是 SSTable 内部无法快速识别这些数据是否删除，仍然要扫描大量包含删除标记的数据，导致一个小查询的响应时间可能远远超过用户预期。为了解决这类表的查询性能问题，OceanBase 数据库为用户特别提供了一种支持自定义的表模式，称为 Queuing 表（从业务角度有时候也会称为 Buffer 表），对其实现了特殊的转储策略。 本文主要介绍如何检查一张表是否是buffer表，以及列出当前系统中用户设置过的所有buffer表。 ## 详细说明 ### 检查某张具体的表是否是 Buffer 表 **说明：对于某张具体的表，可以通过该表的DDL定义语句中的table_mode=xxx关键字来判断该表是否为一张buffer表。如果没有该关键字，则为一张普通表**。 - OceanBase 数据库 V2.x/V3.x/V4.x 版本的 MySQL 租户中可以通过 show create table xxx 来检查某张表是否为 Buffer 表。 ```shell MySQL [test]> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `pk1` int(11) NOT NULL, `id` int(11) DEFAULT NULL, PRIMARY KEY (`pk1`) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLE_MODE = 'QUEUING' 1 row in set (0.005 sec) ``` - OceanBase 数据库 V2.x/V3.x/V4.x 版本的 Oracle 租户中可以通过 show create table xxx 和 `dbms_metadata.get_ddl` 函数来检查某张表是否为 Buffer 表。 ```shell obclient [SYS]> show create table t1\G *************************** 1. row *************************** TABLE: T1 CREATE TABLE: CREATE TABLE "T1" ( "ID" NUMBER(*,0) ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLE_MODE = 'QUEUING' 1 row in set (0.006 sec) obclient [SYS]> select dbms_metadata.get_ddl('TABLE', 'T1', 'SYS') from dual\G *************************** 1. row *************************** DBMS_METADATA.GET_DDL('TABLE','T1','SYS'): CREATE TABLE "T1" ( "ID" NUMBER(*,0) ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 TABLE_MODE = 'QUEUING' 1 row in set (0.050 sec) ``` ### 列出系统中用户设置过的所有的 Buffer 表 **备注：一张主表上的附属表如索引表、LOB 辅助表的 table_mode 和主表是一样的**。 - OceanBase 数据库 V2.x/V3.x 中。 **说明：底层用于记录用户表属性的相关虚拟表中有一个 table_mode 字段，该字段为 bigint 类型，在 OceanBase 数据库 V2.x/V3.x 中将与 0xff 做 bitwise 与运算后，0 对应 NORMAL 表，1 对应 QUEUING 表**。 - NORMAL: 0 - QUEUING: 1 ```shell MySQL [test]> create table t1 (id int, c1 varchar(100), c2 text); Query OK, 0 rows affected (0.095 sec) MySQL [test]> alter table t1 table_mode='queuing'; Query OK, 0 rows affected (0.035 sec) MySQL [test]> create index t1_idx1 on t1(c1); Query OK, 0 rows affected (0.582 sec) -- 切换到 root@sys 系统租户下查询 MySQL [oceanbase]> SELECT t.table_id, t.table_name, d.database_name, case when (t.table_mode & 0xff)=1 then "QUEUING" end as table_mode FROM oceanbase.__all_virtual_table t join oceanbase.__all_virtual_database d on t.tenant_id=d.tenant_id and t.database_id=d.database_id WHERE (t.table_mode & 0xff)=1 AND t.tenant_id = 1001 order by table_name; +------------------+--------------------------------+---------------+------------+ | table_id | table_name | database_name | table_mode | +------------------+--------------------------------+---------------+------------+ | 1100611139453837 | t1 | test | QUEUING | | 1100611139453838 | __idx_1100611139453837_t1_idx1 | test | QUEUING | +------------------+--------------------------------+---------------+------------+ 2 rows in set (0.003 sec) -- 切换到普通 MySQL 租户下查询 MySQL [test]> SELECT t.table_id, t.table_name, d.database_name, case when (t.table_mode & 0xff)=1 then "QUEUING" end as table_mode FROM oceanbase.__all_table_v2 t join oceanbase.__all_database d on t.database_id=d.database_id WHERE (t.table_mode & 0xff)=1 order by table_name; +----------+--------------------------------+---------------+------------+ | table_id | table_name | database_name | table_mode | +----------+--------------------------------+---------------+------------+ | 50061 | t1 | test | QUEUING | | 50062 | __idx_1100611139453837_t1_idx1 | test | QUEUING | +----------+--------------------------------+---------------+------------+ 2 rows in set (0.001 sec) obclient [SYS]> create table t1 (id int, c1 varchar(100), c2 clob); Query OK, 0 rows affected (0.073 sec) obclient [SYS]> alter table t1 table_mode='queuing'; Query OK, 0 rows affected (0.034 sec) obclient [SYS]> create index t1_idx1 on t1(c1) local; Query OK, 0 rows affected (0.674 sec) -- 切换到普通 Oracle 租户下查询 obclient [SYS]> SELECT t.table_id, t.table_name, d.database_name, case when bitand(t.table_mode,255)=1 then 'QUEUING' end as table_mode FROM sys.all_virtual_table_real_agent t join sys.all_virtual_database_real_agent d on t.database_id=d.database_id WHERE bitand(t.table_mode,255)=1 order by table_name; +------------------+--------------------------------+---------------+------------+ | TABLE_ID | TABLE_NAME | DATABASE_NAME | TABLE_MODE | +------------------+--------------------------------+---------------+------------+ | 1101710651081565 | T1 | SYS | QUEUING | | 1101710651081566 | __idx_1101710651081565_T1_IDX1 | SYS | QUEUING | +------------------+--------------------------------+---------------+------------+ 2 rows in set (0.002 sec) ``` - OceanBase 数据库 V4.x（V4.2.1 BP5+、V4.2.3+、V4.3.2+）中。 **说明：底层用于记录用户表属性的相关虚拟表中有一个 table_mode 字段，该字段为 bigint 类型，在 OceanBase V4.x 中将与 0xff 做 bitwise 与运算后，0 对应 NORMAL，1 对应 QUEUING，3 对应 MODERATE，4 对应 SUPER，5 对应 EXTREME**。 - NORMAL: 0 - QUEUING: 1 - MODERATE: 3 - SUPER: 4 - EXTREME: 5 ```shell MySQL [test]> create table t1 (pk1 int primary key, id int); Query OK, 0 rows affected (2.201 sec) MySQL [test]> create table t2 (pk1 int primary key, id int) partition by hash(pk1) partitions 3; Query OK, 0 rows affected (2.993 sec) MySQL [test]> create table t3 (rb text); Query OK, 0 rows affected (2.193 sec) MySQL [test]> create table t4 (id int primary key, col1 int); Query OK, 0 rows affected (2.170 sec) MySQL [test]> create unique index t4_idx1 on t4(col1); Query OK, 0 rows affected (4.133 sec) MySQL [test]> alter table t1 set table_mode='queuing'; Query OK, 0 rows affected (2.091 sec) MySQL [test]> alter table t2 set table_mode='moderate'; Query OK, 0 rows affected (2.692 sec) MySQL [test]> alter table t3 table_mode='super'; Query OK, 0 rows affected (2.090 sec) MySQL [test]> alter table t4 table_mode='extreme'; Query OK, 0 rows affected (2.086 sec) -- 切换到 root@sys 系统租户下查询 MySQL [oceanbase]> SELECT t.table_id, t.table_name, d.database_name, case when (t.table_mode & 0xff)=1 then "QUEUING" when (t.table_mode & 0xff)=3 then "MODERATE" when (t.table_mode & 0xff)=4 then "SUPER" when (t.table_mode & 0xff)=5 then "EXTREME" end as table_mode FROM oceanbase.__all_virtual_table t join oceanbase.__all_virtual_database d on t.tenant_id=d.tenant_id and t.database_id=d.database_id WHERE (t.table_mode & 0xff) in (1,3,4,5) AND t.tenant_id = 1010 order by table_name; +----------+-------------------------+---------------+------------+ | table_id | table_name | database_name | table_mode | +----------+-------------------------+---------------+------------+ | 500163 | t1 | test | QUEUING | | 500164 | t2 | test | MODERATE | | 500168 | t3 | test | SUPER | | 500171 | t4 | test | EXTREME | | 500169 | __AUX_LOB_META_500169_ | test | SUPER | | 500170 | __AUX_LOB_PIECE_500170_ | test | SUPER | | 500172 | __idx_500171_t4_idx1 | test | EXTREME | +----------+-------------------------+---------------+------------+ 7 rows in set (0.020 sec) MySQL [oceanbase]> SELECT t.table_id, t.table_name, d.database_name, case when (t.table_mode & 0xff)=1 then "QUEUING" when (t.table_mode & 0xff)=3 then "MODERATE" when (t.table_mode & 0xff)=4 then "SUPER" when (t.table_mode & 0xff)=5 then "EXTREME" end as table_mode FROM oceanbase.__all_virtual_table t join oceanbase.__all_virtual_database d on t.tenant_id=d.tenant_id and t.database_id=d.database_id WHERE (t.table_mode & 0xff) in (1,3,4,5) AND t.tenant_id = 1010 and t.table_name not like '\_\_%' order by table_name; +----------+------------+---------------+------------+ | table_id | table_name | database_name | table_mode | +----------+------------+---------------+------------+ | 500163 | t1 | test | QUEUING | | 500164 | t2 | test | MODERATE | | 500168 | t3 | test | SUPER | | 500171 | t4 | test | EXTREME | +----------+------------+---------------+------------+ 4 rows in set (0.020 sec) -- 切换到普通 MySQL 租户下查询 MySQL [oceanbase]> SELECT t.table_id, t.table_name, d.database_name, case when (t.table_mode & 0xff)=1 then "QUEUING" when (t.table_mode & 0xff)=3 then "MODERATE" when (t.table_mode & 0xff)=4 then "SUPER" when (t.table_mode & 0xff)=5 then "EXTREME" end as table_mode FROM oceanbase.__all_virtual_table t join oceanbase.__all_database d on t.database_id=d.database_id WHERE (t.table_mode & 0xff) in (1,3,4,5) order by table_name; +----------+-------------------------+---------------+------------+ | table_id | table_name | database_name | table_mode | +----------+-------------------------+---------------+------------+ | 500163 | t1 | test | QUEUING | | 500164 | t2 | test | MODERATE | | 500168 | t3 | test | SUPER | | 500171 | t4 | test | EXTREME | | 500169 | __AUX_LOB_META_500169_ | test | SUPER | | 500170 | __AUX_LOB_PIECE_500170_ | test | SUPER | | 500172 | __idx_500171_t4_idx1 | test | EXTREME | +----------+-------------------------+---------------+------------+ 7 rows in set (0.010 sec) obclient [SYS]> create table t1 (id int); Query OK, 0 rows affected (2.180 sec) obclient [SYS]> create table t2 (id int, name varchar(100)); Query OK, 0 rows affected (2.160 sec) obclient [SYS]> create table t3 (id int, name varchar(100)); Query OK, 0 rows affected (2.156 sec) obclient [SYS]> create index t3_idx1 on t3(name) local; Query OK, 0 rows affected (4.635 sec) obclient [SYS]> create table t4 (id int, name clob); Query OK, 0 rows affected (3.231 sec) obclient [SYS]> alter table t1 set table_mode='queuing'; Query OK, 0 rows affected (2.106 sec) obclient [SYS]> alter table t2 set table_mode='moderate'; Query OK, 0 rows affected (2.092 sec) obclient [SYS]> alter table t3 table_mode='super'; Query OK, 0 rows affected (2.096 sec) obclient [SYS]> alter table t4 table_mode='extreme'; Query OK, 0 rows affected (2.733 sec) -- 切换到普通 Oracle 租户下查询： obclient [SYS]> SELECT t.table_id, t.table_name, d.database_name, case when bitand(t.table_mode,255)=1 then 'QUEUING' when bitand(t.table_mode,255)=3 then 'MODERATE' when bitand(t.table_mode,255)=4 then 'SUPER' when bitand(t.table_mode,255)=5 then 'EXTREME' end as table_mode FROM sys.all_virtual_table_real_agent t join sys.all_virtual_database_real_agent d on t.database_id=d.database_id WHERE bitand(t.table_mode,255) in (1,3,4,5) order by table_name; +----------+-------------------------+---------------+------------+ | TABLE_ID | TABLE_NAME | DATABASE_NAME | TABLE_MODE | +----------+-------------------------+---------------+------------+ | 500057 | T1 | SYS | QUEUING | | 500058 | T2 | SYS | MODERATE | | 500059 | T3 | SYS | SUPER | | 500061 | T4 | SYS | EXTREME | | 500062 | __AUX_LOB_META_500062_ | SYS | EXTREME | | 500063 | __AUX_LOB_PIECE_500063_ | SYS | EXTREME | | 500060 | __idx_500059_T3_IDX1 | SYS | SUPER | +----------+-------------------------+---------------+------------+ 7 rows in set (0.004 sec) ``` ## 适用版本 - OceanBase 数据库 V2.x/3.x 版本。 - OceanBase 数据库 V4.2.1 BP5（oceanbase-4.2.1.5-105000072024041817）及之后版本。 - OceanBase 数据库 V4.2.3（oceanbase-4.2.3.0-100000052024041220）及之后版本。 - OceanBase 数据库 V4.3.2（oceanbase-4.3.2.0-100000442024071321）及之后版本。 Previous [Queuing 表运行使用机制](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005428696) Next [如何快速判断一张表是否存在 buffer 表](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006142762) ![有帮助](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) 咨询热线