在 LSM-Tree 架构的数据库中，你是否遇到过这样的困境：明明业务数据量不大，但频繁的插入和删除操作，却让查询响应慢到超出预期？
这背后的 “元凶”，就是 LSM-Tree 的删除机制 —— 数据并非原地删除，而是通过写入Delete操作来标记删除。即使用户的实际扫描范围很小，但是 SSTable 内部无法快速识别这些数据是否删除，仍然要扫描大量包含删除标记的数据，拖慢整体性能。
为了破解这个痛点，OceanBase 数据库特别提供了一种支持自定义的表模式，称为Queuing 表（业务侧也称 Buffer 表），对其实现了特殊的转储策略。
本文结合 MySQL/Oracle 租户、不同 OceanBase 版本，主要介绍如何检查一张表是否是buffer表，以及列出当前系统中用户设置过的所有buffer表。
一、快速判定：单张表是否为 Buffer 表？
核心判断依据：对于某张具体的表，可以通过该表的DDL定义语句中的table_mode=xxx关键字来判断该表是否为一张Buffer表。如果没有该关键字，则为一张普通表。
1.MySQL租户
OceanBase 数据库 V2.x/V3.x/V4.x 版本的 MySQL 租户中可以通过show create table xxx 来检查某张表是否为 Buffer 表。
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)
2.Oracle租户
OceanBase 数据库 V2.x/V3.x/V4.x 版本的 Oracle 租户中可以通过 show create table xxx和dbms_metadata.get_ddl函数来检查某张表是否为 Buffer 表。
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和主表是一样的。
1.OceanBase V2.x/V3.x版本
说明：底层用于记录用户表属性的相关虚拟表的 table_mode 字段为 bigint 类型，在 OceanBase 数据库 V2.x/V3.x 中将与 0xff 做 bitwise 与运算后。
-
0 对应 NORMAL 表
-
1 对应 QUEUING 表
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 系统租户下查询
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)
2.OceanBase V4.x版本（V4.2.1BP5+/V4.2.3+/V4.3.2+）
说明：底层用于记录用户表属性的相关虚拟表中 table_mode 字段为 bigint 类型，在 OceanBase V4.x 中将与 0xff 做 bitwise 与运算后。
-
0 对应 NORMAL
-
1 对应 QUEUING
-
3 对应 MODERATE
-
4 对应 SUPER
-
5 对应 EXTREME
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 系统租户下查询
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)
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 租户下查询
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)
以上就是判断单张 Buffer 表、批量排查系统中所有 Buffer 表的完整指南啦！无论你使用的是 MySQL 还是 Oracle 租户，不管是 OceanBase V2.x/V3.x 还是 V4.x 版本，照着文中的步骤操作就能轻松搞定。
如果你在实际操作中遇到了问题，或者有其他关于 OceanBase 表优化的疑问，欢迎在评论区留言交流，后续我们还会分享更多 OceanBase 实战干货，下次见~
