如何查看 OceanBase 数据库中的 Buffer 表

在 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 表。

  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 表。

  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

  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

  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）及之后版本。