划线反馈

如何检查一张表是行存表还是列存表或者行列冗余表

更新时间：2026-05-21 01:56

适用版本：V4.3.x、V4.4.x内容类型：TechNote

本文主要介绍如何通过 show create table xxx; 语句以及查询内部系统表来检查一张用户表是行存表还是列存表或者行列冗余表。

详细说明

Step 1）准备测试表结构

MySQL 模式租户：

-- MySQL模式租户
MySQL [test]> CREATE TABLE example_row ( -- 行存表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (all columns);
Query OK, 0 rows affected (0.41 sec)

MySQL [test]> CREATE TABLE example_column ( -- 列存表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (each column);
Query OK, 0 rows affected (0.13 sec)

MySQL [test]> CREATE TABLE example_compound ( -- 行列冗余表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (all columns, each column);
Query OK, 0 rows affected (0.12 sec)

Oracle 模式租户：

obclient(SYS@oracle)[SYS]> CREATE TABLE example_row ( -- 行存表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (all columns);
Query OK, 0 rows affected (0.182 sec)

obclient(SYS@oracle)[SYS]> CREATE TABLE example_column ( -- 列存表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (each column);
Query OK, 0 rows affected (0.131 sec)

obclient(SYS@oracle)[SYS]> CREATE TABLE example_compound ( -- 行列冗余表
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) with column group (all columns, each column);
Query OK, 0 rows affected (0.290 sec)

Step 2）使用 `show create table xxx;` 来检查一张用户表是行存表还是列存表或者行列冗余表

MySQL 模式租户：

MySQL [test]> show create table example_row;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_row | CREATE TABLE `example_row` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns) |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [test]> show create table example_column;
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_column | CREATE TABLE `example_column` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [test]> show create table example_compound;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_compound | CREATE TABLE `example_compound` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns, each column) |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Oracle 模式租户：

obclient(SYS@oracle)[SYS]> show create table example_row;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE       | CREATE TABLE                                                                                                                                                                                                                                                                                                    |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXAMPLE_ROW | CREATE TABLE "EXAMPLE_ROW" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns) |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.046 sec)

obclient(SYS@oracle)[SYS]> show create table example_column;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE          | CREATE TABLE                                                                                                                                                                                                                                                                                                       |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXAMPLE_COLUMN | CREATE TABLE "EXAMPLE_COLUMN" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.022 sec)

obclient(SYS@oracle)[SYS]> show create table example_compound;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE            | CREATE TABLE                                                                                                                                                                                                                                                                                                                      |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXAMPLE_COMPOUND | CREATE TABLE "EXAMPLE_COMPOUND" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns, each column) |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.023 sec)

Oracle 模式租户下还可以使用 DBMS_METADATA.GET_DDL 函数来检查一张表是行存表还是列存表或者行列冗余表：

obclient(SYS@oracle)[SYS]> select dbms_metadata.get_ddl('TABLE', 'EXAMPLE_ROW', 'SYS') from dual;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('TABLE','EXAMPLE_ROW','SYS')                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE "EXAMPLE_ROW" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.350 sec)

obclient(SYS@oracle)[SYS]> select dbms_metadata.get_ddl('TABLE', 'EXAMPLE_COLUMN', 'SYS') from dual;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('TABLE','EXAMPLE_COLUMN','SYS')                                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE "EXAMPLE_COLUMN" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.058 sec)

obclient(SYS@oracle)[SYS]> select dbms_metadata.get_ddl('TABLE', 'EXAMPLE_COMPOUND', 'SYS') from dual;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('TABLE','EXAMPLE_COMPOUND','SYS')                                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE "EXAMPLE_COMPOUND" (
"ID" NUMBER(*,0),
"NAME" VARCHAR2(50),
"AGE" NUMBER(*,0),
PRIMARY KEY ("ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns, each column) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.056 sec)

Step 3）通过查询内部系统表来检查一张用户表是行存表还是列存表或者行列冗余表

系统 sys 租户下：

MySQL [oceanbase]> SELECT
  A.TENANT_ID,
  A.TABLE_ID,
  A.TABLE_NAME,
  CAST (
    CASE
    WHEN A.TABLE_TYPE IN (0) THEN 'SYSTEM TABLE'
    WHEN A.TABLE_TYPE IN (1) THEN 'SYSTEM VIEW'
    WHEN A.TABLE_TYPE IN (2) THEN 'VIRTUAL TALBE'
    WHEN A.TABLE_TYPE IN (3,6,8,9) THEN 'USER TABLE'
    WHEN A.TABLE_TYPE IN (4) THEN 'USER VIEW'
    WHEN A.TABLE_TYPE IN (5) THEN 'INDEX'
    WHEN A.TABLE_TYPE IN (7) THEN 'MATERIALIZED VIEW'
    WHEN A.TABLE_TYPE IN (10) THEN 'TMP TABLE ALL'
    WHEN A.TABLE_TYPE IN (11) THEN 'AUX VERTICAL'
    WHEN A.TABLE_TYPE IN (12,13) THEN 'LOB AUX TABLE'
    WHEN A.TABLE_TYPE IN (14) THEN 'EXTERNAL TABLE'
    WHEN A.TABLE_TYPE IN (15) THEN 'MATERIALIZED VIEW LOG'
    ELSE 'UNKNOWN'
    END
      AS CHAR(128)
  ) AS TABLE_TYPE,
  CAST (
    COALESCE(C.STORE_MODEL, 'ROW_STORE') AS CHAR(128)
  ) AS STORE_MODEL,
  CAST (
    CASE (A.TABLE_MODE & 255)
    WHEN 0 THEN 'NORMAL'
    WHEN 1 THEN 'QUEUING'
    WHEN 2 THEN 'AUX'
    WHEN 3 THEN 'QUEUING_MODERATE'
    WHEN 4 THEN 'QUEUING_SUPER'
    WHEN 5 THEN 'QUEUING_EXTREME'
    ELSE 'UNKNOWN'
    END
      AS CHAR(128)
  ) AS TABLE_MODE
FROM
  OCEANBASE.__ALL_VIRTUAL_TABLE A
  LEFT OUTER JOIN (
    SELECT
      TENANT_ID,
      TABLE_ID,
      GROUP_CONCAT(
        DISTINCT STORE
        ORDER BY
          STORE ASC SEPARATOR ';'
      ) AS STORE_MODEL
    FROM
      (
        SELECT
          TENANT_ID,
          TABLE_ID,
          (
            CASE COLUMN_GROUP_TYPE
            WHEN 0 THEN  NULL
            WHEN 1 THEN 'ROW_STORE'
            WHEN 2 THEN 'COL_STORE'
            WHEN 3 THEN 'COL_STORE'
            WHEN 4 THEN 'COL_STORE'
            ELSE 'UNKNOWN_STORE'
            END
          ) AS STORE
        FROM
          OCEANBASE.__ALL_VIRTUAL_COLUMN_GROUP B
        GROUP BY
          TENANT_ID,
          TABLE_ID,
          COLUMN_GROUP_TYPE
      )
    GROUP BY
      TENANT_ID,
      TABLE_ID
  ) C USING(TENANT_ID, TABLE_ID)
where (A.TENANT_ID=1002 or A.TENANT_ID=1010) and A.TABLE_NAME like 'example_%';
+-----------+----------+------------------+------------+---------------------+------------+
| TENANT_ID | TABLE_ID | TABLE_NAME       | TABLE_TYPE | STORE_MODEL         | TABLE_MODE |
+-----------+----------+------------------+------------+---------------------+------------+
|      1010 |   500228 | example_row      | USER TABLE | ROW_STORE           | NORMAL     |
|      1010 |   500230 | example_compound | USER TABLE | COL_STORE;ROW_STORE | NORMAL     |
|      1010 |   500229 | example_column   | USER TABLE | COL_STORE           | NORMAL     |
|      1002 |   500229 | EXAMPLE_ROW      | USER TABLE | ROW_STORE           | NORMAL     |
|      1002 |   500233 | EXAMPLE_COMPOUND | USER TABLE | COL_STORE;ROW_STORE | NORMAL     |
|      1002 |   500231 | EXAMPLE_COLUMN   | USER TABLE | COL_STORE           | NORMAL     |
+-----------+----------+------------------+------------+---------------------+------------+
6 rows in set (0.13 sec)

普通 MySQL 模式租户下：

MySQL [oceanbase]> show tenant;
+---------------------+
| Current_tenant_name |
+---------------------+
| mysqlt              |
+---------------------+
1 row in set (0.00 sec)

MySQL [oceanbase]> SELECT
  A.TABLE_ID,
  A.TABLE_NAME,
  CAST (
    CASE
    WHEN A.TABLE_TYPE IN (0) THEN 'SYSTEM TABLE'
    WHEN A.TABLE_TYPE IN (1) THEN 'SYSTEM VIEW'
    WHEN A.TABLE_TYPE IN (2) THEN 'VIRTUAL TALBE'
    WHEN A.TABLE_TYPE IN (3,6,8,9) THEN 'USER TABLE'
    WHEN A.TABLE_TYPE IN (4) THEN 'USER VIEW'
    WHEN A.TABLE_TYPE IN (5) THEN 'INDEX'
    WHEN A.TABLE_TYPE IN (7) THEN 'MATERIALIZED VIEW'
    WHEN A.TABLE_TYPE IN (10) THEN 'TMP TABLE ALL'
    WHEN A.TABLE_TYPE IN (11) THEN 'AUX VERTICAL'
    WHEN A.TABLE_TYPE IN (12,13) THEN 'LOB AUX TABLE'
    WHEN A.TABLE_TYPE IN (14) THEN 'EXTERNAL TABLE'
    WHEN A.TABLE_TYPE IN (15) THEN 'MATERIALIZED VIEW LOG'
    ELSE 'UNKNOWN'
    END
      AS CHAR(128)
  ) AS TABLE_TYPE,
  CAST (
    COALESCE(C.STORE_MODEL, 'ROW_STORE') AS CHAR(128)
  ) AS STORE_MODEL,
  CAST (
    CASE (A.TABLE_MODE & 255)
    WHEN 0 THEN 'NORMAL'
    WHEN 1 THEN 'QUEUING'
    WHEN 2 THEN 'AUX'
    WHEN 3 THEN 'QUEUING_MODERATE'
    WHEN 4 THEN 'QUEUING_SUPER'
    WHEN 5 THEN 'QUEUING_EXTREME'
    ELSE 'UNKNOWN'
    END
      AS CHAR(128)
  ) AS TABLE_MODE
FROM
  OCEANBASE.__ALL_TABLE A
  LEFT OUTER JOIN (
    SELECT
      TABLE_ID,
      GROUP_CONCAT(
        DISTINCT STORE
        ORDER BY
          STORE ASC SEPARATOR ';'
      ) AS STORE_MODEL
    FROM
      (
        SELECT
          TABLE_ID,
          (
            CASE COLUMN_GROUP_TYPE
            WHEN 0 THEN  NULL
            WHEN 1 THEN 'ROW_STORE'
            WHEN 2 THEN 'COL_STORE'
            WHEN 3 THEN 'COL_STORE'
            WHEN 4 THEN 'COL_STORE'
            ELSE 'UNKNOWN_STORE'
            END
          ) AS STORE
        FROM
          OCEANBASE.__ALL_COLUMN_GROUP B
        GROUP BY
          TABLE_ID,
          COLUMN_GROUP_TYPE
      )
    GROUP BY
      TABLE_ID
  ) C USING(TABLE_ID)
where A.TABLE_NAME like 'example_%';
+----------+------------------+------------+---------------------+------------+
| TABLE_ID | TABLE_NAME       | TABLE_TYPE | STORE_MODEL         | TABLE_MODE |
+----------+------------------+------------+---------------------+------------+
|   500229 | example_column   | USER TABLE | COL_STORE           | NORMAL     |
|   500228 | example_row      | USER TABLE | ROW_STORE           | NORMAL     |
|   500230 | example_compound | USER TABLE | COL_STORE;ROW_STORE | NORMAL     |
+----------+------------------+------------+---------------------+------------+
3 rows in set (0.00 sec)

适用版本

OceanBase 数据库 V4.3.5（oceanbase-4.3.5.0-100000122024123020）及之后版本。

名词解释

行存表

特点：
- 数据按行存储，每行的所有列数据连续存储在磁盘或内存中。
- 适用于 OLTP（在线事务处理） 场景，如银行交易、订单管理等。
优势：
- 事务处理高效： 适合频繁插入、更新整行数据的场景（如订单状态变更）。
- 低延迟： 单条记录的读写速度较快。
劣势：
- 分析性能差： 当查询仅涉及部分列时，需读取完整行，浪费 I/O 资源。
- 压缩率低： 不同列的数据类型差异大，难以高效压缩。

列存表

特点：
- 数据按列存储，同一列的数据连续存储，不同列的数据分开存储。
- 适用于 OLAP（在线分析处理） 场景，如数据仓库、报表分析等。
优势：
- 分析性能高： 查询仅需读取相关列，减少 I/O 开销。
- 高压缩率： 同类型数据连续存储，压缩效率更高（如整数列）。
- 并行计算友好： 适合大规模数据分析（如 MapReduce）。
劣势：
- 事务处理差： 不适合频繁更新单条记录（需修改多列数据）。
- 写入开销大: 插入数据时需将值分发到多列存储区域。

行列冗余表

特点：
- 行存、列存各存一份数据副本。
优势：
- 两份数据副本分别适用于 OLTP 类和 OLAP 类查询，由优化器自动选择最适合的数据副本。
劣势：
- 因为存储了两份数据副本，存储空间占用也会相应增加，相当于使用空间换时间。
- 对数据增删修改时虽然在 MemTable 中保存的还是行存的格式，但是在租户合并时需要同时维护行存和列存两份数据，导致合并耗时增加。