MogDB 列存表管理

原创 MogDB 2024-08-07

什么是列存储

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。行存储和列存储的差异请参见图1。

图 1 行存储和列存储的差异

上图中，左上为行存表，右上为行存表在硬盘上的存储方式。左下为列存表，右下为列存表在硬盘上的存储方式。从上图可以很清楚地看到，行式存储下一张表的数据都是放在一起的，但列式存储下都被分开保存了。

行存表和列存表的优缺点及适用场景

行、列存储有如下优缺点:

存储模型	优点	缺点
行存	数据被保存在一起。INSERT/UPDATE容易。	选择(Selection)时即使只涉及某几列，所有数据也都会被读取。
列存	查询时只有涉及到的列会被读取。投影(Projection)很高效。任何列都能作为索引。	选择完成时，被选择的列要重新组装。INSERT/UPDATE比较麻烦。

一般情况下，如果表的字段比较多（大宽表），查询中涉及到的列不多的情况下，适合列存储。如果表的字段个数比较少，查询大部分字段，那么选择行存储比较好。

存储类型	适用场景
行存	点查询(返回记录少，基于索引的简单查询)。增、删、改操作较多的场景。
列存	统计分析类查询 (关联、分组操作较多的场景)。即席查询（查询条件不确定，行存表扫描难以使用索引）。

MogDB支持行列混合存储。行、列存储模型各有优劣，建议根据实际情况选择。通常MogDB用于TP场景的数据库，默认使用行存储，仅对执行复杂查询且数据量大的AP场景时，才使用列存储。

行存表和列存表的选择

更新频繁程度

数据如果频繁更新，选择行存表。
插入频繁程度

频繁的少量插入，选择行存表。
表的列数

表的列数很多，选择列存表。
查询的列数

如果每次查询时，只涉及了表的少数（<50%总列数）几个列，选择列存表。
压缩率

列存表比行存表压缩率高。但高压缩率会消耗更多的CPU资源。

列存表的限制

列存表不支持数组。
列存表的数量建议不超过1000个。
列存表的表级约束只支持PARTIAL CLUSTER KEY，不支持主外键等表级约束。
列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。
列存表不支持alter命令修改字段约束。
列存表支持delta表，受参数enable_delta_store 控制是否开启，受参数deltarow_threshold控制进入delta表的阀值。

列存相关参数

cstore_buffers

列存所使用的共享缓冲区的大小，默认值: 32768KB。
partition_mem_batch

指定缓存个数，为了优化对列存分区表的批量插入，在批量插入过程中会对数据进行缓存后再批量写盘。默认值: 256 。
partition_max_cache_size

指定数据缓存区大小，为了优化对列存分区表的批量插入，在批量插入过程中会对数据进行缓存后再批量写盘。默认值: 2GB。
enable_delta_store

为了增强列存单条数据导入的性能和解决磁盘冗余问题，是否需要开启列存delta表功能，与参数DELTAROW_THRESHOLD 配合使用。默认值: off。

建表语法

MogDB创建普通表默认是未压缩的行存表。

MogDB=# \dt
No relations found.
MogDB=# create table test_t(id serial primary key ,col1 varchar(8),col2 decimal(6,2),create_time timestamptz not null default now());
NOTICE:  CREATE TABLE will create implicit sequence "test_t_id_seq" for serial column "test_t.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_t_pkey" for table "test_t"
CREATE TABLE
MogDB=# \dt+
                                     List of relations
 Schema |  Name  | Type  | Owner |  Size   |             Storage              | Description
--------+--------+-------+-------+---------+----------------------------------+-------------
 public | test_t | table | omm   | 0 bytes | {orientation=row,compression=no} |
(1 row)

MogDB=#
复制

创建列存表，需要指定orientation=column，默认压缩级别是low。

MogDB=# create table column_t(id serial,col1 varchar(8),col2 decimal(6,2),create_time timestamptz not null default now()) with (orientation=column );
NOTICE:  CREATE TABLE will create implicit sequence "column_t_id_seq" for serial column "column_t.id"
CREATE TABLE
MogDB=# \dt+
                                        List of relations
 Schema |   Name   | Type  | Owner |  Size   |               Storage                | Description
--------+----------+-------+-------+---------+--------------------------------------+-------------
 public | column_t | table | omm   | 16 kB   | {orientation=column,compression=low} |
 public | test_t   | table | omm   | 0 bytes | {orientation=row,compression=no}     |
(2 rows)

MogDB=#
MogDB=# \d+ column_t
                                                        Table "public.column_t"
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Has OIDs: no
Options: orientation=column, compression=low
复制

列存表添加局部聚簇存储列。

MogDB=# \d+ column_t
                                                        Table "public.column_t"
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Has OIDs: no
Options: orientation=column, compression=low

MogDB=# alter table column_t add PARTIAL CLUSTER KEY(id);
ALTER TABLE
MogDB=# \d+ column_t
                                                        Table "public.column_t"
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Partial Cluster :
    "column_t_cluster" PARTIAL CLUSTER KEY (id)
Has OIDs: no
Options: orientation=column, compression=low

MogDB=#
复制

直接创建带局部聚簇存储的列存表。

MogDB=# create table column_c(id serial,col1 varchar(8),col2 decimal(6,2),create_time timestamptz not null default now(),PARTIAL CLUSTER KEY(id)) with (orientation=column );
NOTICE:  CREATE TABLE will create implicit sequence "column_c_id_seq" for serial column "column_c.id"
CREATE TABLE
MogDB=# \d+ column_c
                                                        Table "public.column_c"
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_c_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Partial Cluster :
    "column_c_cluster" PARTIAL CLUSTER KEY (id)
Has OIDs: no
Options: orientation=column, compression=low

MogDB=#
复制

有关列存表支持的数据类型，请参见参考指南>支持的数据类型下的列存表支持的数据类型.

列存与行存对比

磁盘使用空间

列存表默认大小16K，low压缩级别。
行存表默认大小0bytes，非压缩级别。

分别向两个表中插入100万条数据，占用磁盘大小对比。

MogDB=# \dt+
                                      List of relations
Schema |   Name    | Type  | Owner |  Size   |                 Storage                 | Description
--------+-----------+-------+-------+---------+-----------------------------------------+-------------
public | column_t  | table | omm   | 16 kB   | {orientation=column,compression=low}    |
public | column_th | table | omm   | 16 kB   | {orientation=column,compression=high}   |
public | column_tm | table | omm   | 16 kB   | {orientation=column,compression=middle} |
public | row_tc    | table | omm   | 0 bytes | {orientation=row,compression=yes}       |
public | test_t    | table | omm   | 0 bytes | {orientation=row,compression=no}        |
(5 rows)
MogDB=# insert into column_t select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 11328.880 ms
MogDB=# insert into column_th select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 10188.634 ms
MogDB=# insert into column_tm select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 9802.739 ms
MogDB=# insert into test_t select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 17404.945 ms
MogDB=# insert into row_tc select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 12394.866 ms
MogDB=# \dt+
                                       List of relations
Schema |   Name    | Type  | Owner |   Size   |                 Storage                 | Description
--------+-----------+-------+-------+----------+-----------------------------------------+-------------
public | column_t  | table | omm   | 12 MB    | {orientation=column,compression=low}    |
public | column_th | table | omm   | 8304 kB  | {orientation=column,compression=high}   |
public | column_tm | table | omm   | 10168 kB | {orientation=column,compression=middle} |
public | row_tc    | table | omm   | 58 MB    | {orientation=row,compression=yes}       |
public | test_t    | table | omm   | 58 MB    | {orientation=row,compression=no}        |
(5 rows)
MogDB=#
复制

列存表开启的压缩级别越高，占用磁盘空间越少。
行存表开启压缩后，磁盘空间大小占比减少不明显。
列存表占用磁盘空间比行存表占用磁盘空间少近6倍。

DML对比

查找单列:

---
---按范围查找，列存比行存快近20倍
---
MogDB=# select col1 from test_t where id>=100010 and id<100020;
   col1
----------
 4257a3f3
 3d397284
 64343438
 6eb7bdb7
 d1c9073d
 6aeb037c
 1d424974
 223235ab
 329de235
 2f02adc1
(10 rows)

Time: 77.341 ms
MogDB=# select col1 from column_t where id>=100010 and id<100020;
   col1
----------
 d4837c30
 87a46f7a
 2f42a9c9
 4481c793
 68800204
 613b9205
 9d8f4a0a
 5cc4ff9e
 f948cd10
 f2775cee
(10 rows)

Time: 3.884 ms

---
---随机查找，列存比行存快近35倍
---

MogDB=# select col1 from test_t limit 10;
   col1
----------
 c2780d93
 294be14d
 4e53b761
 2c10f8a2
 ae776743
 7d683c66
 b3b40054
 7e56edf9
 a7b7336e
 ea3d47d9
(10 rows)

Time: 249.887 ms
MogDB=# select col1 from column_t limit 10;
   col1
----------
 a745d77b
 4b6df494
 76fed9c1
 70c9664d
 3384de8a
 4158f3bf
 5d1c3b9f
 341876bb
 f396f4ed
 abfd78bb
(10 rows)

Time: 7.738 ms
复制

查找全部数据:

---
---行存比列存查询快30%
---
MogDB=# select * from test_t limit 10;
 id |   col1   | col2 |          create_time
----+----------+------+-------------------------------
  1 | c2780d93 |  .37 | 2020-10-26 14:27:33.304108+08
  2 | 294be14d |  .57 | 2020-10-26 14:27:33.304108+08
  3 | 4e53b761 |  .98 | 2020-10-26 14:27:33.304108+08
  4 | 2c10f8a2 |  .27 | 2020-10-26 14:27:33.304108+08
  5 | ae776743 |  .97 | 2020-10-26 14:27:33.304108+08
  6 | 7d683c66 |  .58 | 2020-10-26 14:27:33.304108+08
  7 | b3b40054 |  .44 | 2020-10-26 14:27:33.304108+08
  8 | 7e56edf9 |  .43 | 2020-10-26 14:27:33.304108+08
  9 | a7b7336e |  .31 | 2020-10-26 14:27:33.304108+08
 10 | ea3d47d9 |  .42 | 2020-10-26 14:27:33.304108+08
(10 rows)

Time: 6.822 ms

MogDB=# select * from column_t limit 10;
 id |   col1   | col2 |          create_time
----+----------+------+-------------------------------
  1 | a745d77b |  .33 | 2020-10-26 14:28:20.633253+08
  2 | 4b6df494 |  .42 | 2020-10-26 14:28:20.633253+08
  3 | 76fed9c1 |  .73 | 2020-10-26 14:28:20.633253+08
  4 | 70c9664d |  .74 | 2020-10-26 14:28:20.633253+08
  5 | 3384de8a |  .48 | 2020-10-26 14:28:20.633253+08
  6 | 4158f3bf |  .59 | 2020-10-26 14:28:20.633253+08
  7 | 5d1c3b9f |  .63 | 2020-10-26 14:28:20.633253+08
  8 | 341876bb |  .97 | 2020-10-26 14:28:20.633253+08
  9 | f396f4ed |  .73 | 2020-10-26 14:28:20.633253+08
 10 | abfd78bb |  .30 | 2020-10-26 14:28:20.633253+08
(10 rows)

Time: 9.982 ms
复制

更新数据:

---
---直接更新一个字段，列存比行存快近7倍
---
MogDB=# update test_t set col1=col1;
UPDATE 1000000
Time: 19779.978 ms
MogDB=# update column_t set col1=col1;
UPDATE 1000000
Time: 2702.339 ms
复制