文档中心MogDBMogDB StackUqbar
v5.0

文档:v5.0

列存表管理


什么是列存储

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

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

img

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


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

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

存储模型 优点 缺点
行存 数据被保存在一起。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

总结

  1. 列存表比行存表在磁盘空间占用上节省近6倍。
  2. 查询指定字段,列存表比行存表快约20-35倍。
  3. select * 的方式,列存表比行存表慢30%。
  4. 默认压缩方式批量导入数据,列存表比行存表快40%。
Copyright © 2011-2024 www.enmotech.com All rights reserved.