数据表设计最佳实践

列存选择

OceanBase 数据库目前提供三种列存使用方式：列存表、行列冗余表、列存索引、供用户在不同场景下选择使用。具体语法如下：

• 列存表

  CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);
  

• 行列冗余表

  CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns, each column);
  

  表上即有行存，也有列存（数据冗余一份），优化器基于代价选择行存或列存。

• 列存索引

  CREATE TABLE tbl3(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), INDEX idx1_tbl3_cg (date) WITH COLUMN GROUP(each column));
  

主表使用行存，索引使用列存。

列存常用于 AP 场景，在涉及大量数据的查询中，通过减少扫描数据量，Filter 下压，再结合 Skip Index，通常相对行存有较大的性能优势。当查询涉及数据量小时（如指定主键的单行读取，指定索引范围的小范围扫描），行存又有访问 IO 少，索引回表快的优点。通常我们可以根据业务类型是否为典型的 TP 或 AP 选择行存或列存。

部分场景我们既有高率次简单查询，也有大规模数据的复杂分析，我们可考虑选择行列冗余表或行存表上建列存索引。可按如下简化方案进行评估：

具体对一条 SQL 来说，我们可以简单评估其访问表的 IO 量，评估更适合行存或列存。对一个表的访问，行存至少需要一次 IO，列存每访问一列需要一次 IO。当扫描范围大到行存需要的 IO 数大于等于访问列数 IO 数时（即行存访问 IO 数不占优时），我们就可考虑使用列存替代行存。我们一次 IO 单位为微块，通常大小为 16K~64K 字节（这里按 64K 计算），假设一个表的列数为 N_TAB_COL 平均列的长度为 8 字节，SQL 访问的列数为 N_ACCESS_COL, 访问 N_ROW 行数据，那么当为如下情况是应该选择列存。

N_ROW > N_ACCESS_COL * 64K / (8 * N_TAB_COL) 
即：
N_ROW > 8192 * N_ACCESS_COL / N_TAB_COL

如：

CREATE TABLE t1 (id INT, id2 INT, c1 INT, c2 INT ,c3 VARCHAR(256), c4 VARCHAR(256), c5 VARCHAR(256), PRIMARY KEY(id, id2));
SELECT c1, SUM(c2) FROM t1 WHERE id > 0 AND id < 1000 AND c3 > 'abc' GROUP BY c4;

这里 N_TAB_COL为 7， SQL访问 id、c1、c2、c3、c4 共 5 列 N_ACCESS_COL 为 5，同样假设每列长度为 8，当 id 范围(0, 1000) 内行数 N_ROW > 8192 * 5 / 7, 即扫描行数超约 6 千行时，应使用列存。

可更尽一步简化为以下场景适合列存：

• 宽表（多于 64 列的表），扫描行数大于 1K。
• 非宽表扫描行数大于 8K。

可对业务中的主要 SQL 做上述适合行存或存列评估，如都为适合行存或都为适合列存则对应选择行存或列存。如存在大量行存适合 SQL 同时大量列存存适合 SQL，则考虑选行列冗余表。如仅存在少量 SQL 适合列存，且相关 SQL 只涉及少数列，则可考虑行存表+列存索引。

分区

OceanBase 数据库通过分区控制数据在多台机器间的分布，分区是数据分布的最小单位。考虑到分区间可能的数据倾斜以及未来可能的扩容性，通常我们可以将分区数为一个 Zone 内所有机器 CPU 总数的两倍。

在 AP 场景里，通常涉及多个维度的分析查询，没有一个维度可以将数据进行分区且适用于各查询。而又需要分区将数打散分布到多台机器，此时可按如下方式选择一个列做 hash/key 分区：

• NDV 远大于分区数
• 此列数据没有倾斜，或只有少量倾斜
• 优先选择整形列，时间列，再考虑 varchar/char

通常倾向于查最近的数据，且越新的数查询频率越高。如按天导入业务流水，并对最近交易进行分析。这种情况可考虑先对时间做 List 或 Range 分区，再做 Hash 分区，如：

CREATE TABLE t1 (c1 DATE, c2 INT, c3 INT) 
	PARTITION BY RANGE COLUMNS (c1)
	SUBPARTITION BY HASH (c2) SUBPARTITIONS 32
	(
		PARTITION p0 VALUES LESS THAN ('2024-01-01'),
		PARTITION p1 VALUES LESS THAN ('2024-02-01'),
		PARTITION p2 VALUES LESS THAN ('2024-03-01'),
		PARTITION p3 VALUES LESS THAN ('2024-04-01'),
		PARTITION p4 VALUES LESS THAN ('2024-05-01'),
		PARTITION p5 VALUES LESS THAN ('2024-06-01'),
		PARTITION p6 VALUES LESS THAN ('2024-07-01'),
		PARTITION p8 VALUES LESS THAN ('2024-08-01'),
		PARTITION p9 VALUES LESS THAN ('2024-09-01')
	)
	WITH COLUMN GROUP(EACH COLUMN);

则创建了一个先以时间做 Range 分区，再做 Hash 分区的列存表。