<< 返回文章列表

MogDB 数据库行存表和列存表（占用空间和DML操作）对比

2024年7月22日
M
o
g
D
B
,
行
存
表
,
列
存
表
,
数
据
库
性
能
,
实
操
练
习
杨天成
17
MogDB 支持行存储表和列存储表，也称为堆表和列存表。行存储表通常用于事务处理和随机访问，而列存储表适合分析处理和顺序读取。默认创建的表是行存储表。若要创建列存储表，需要在CREATE TABLE语句中使用WITH (ORIENTATION = COLUMN)选项。列存表的优势在于它们通常在数据仓库和分析场景中有更高的压缩效率和查询处理效率，但代价是写入操作可能会更慢，因为需要额外的压缩和解压缩过程。

行存表

默认创建表的类型。数据按行进行存储，即一行数据紧挨着存储。行存表支持完整的增删改查。适用于对数据需要经常更新的场景。

列存表

数据按列进行存储，即一列所有数据紧挨着存储。单列查询IO小，比行存表占用更少的存储空间。适合数据批量插入、更新较少和以查询为主统计分析类的场景。列存表不适合点查询，insert插入单条记录性能差。
行存表和列存表的选择依据如下：
  • 更新频繁程度：数据如果频繁更新，选择行存表。
  • 插入频繁程度：频繁的少量插入，选择行存表。一次插入大批量数据，选择列存表。
  • 表的列数：表的列数很多，选择列存表。
  • 查询的列数：如果每次查询时，只涉及了表的少数（小于总列数的50%）几个列，选择列存表。

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

下面进行行存表和列存表的相关测试验证如下：

数据库版本

 

 

MogDB 5.0.7 单机

tpccdb=# select version();
                                                                        version                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 5.0.7 build c4707384) compiled at 2024-05-24 10:52:51 commit 0 last mr 1804  on aarch64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

建表

 

 

创建列存表，需要指定orientation=column

tpccdb=# create table tctest(
tpccdb(# id int ,
tpccdb(# info varchar(500),
tpccdb(# val_int int,
tpccdb(# val_float decimal(12,2),
tpccdb(# crt_date date,
tpccdb(# crt_time timestamp,
tpccdb(# remark char(50)
tpccdb(# )with (orientation=column,autovacuum_enabled = off, toast.autovacuum_enabled = off,compression=no);
CREATE TABLE
tpccdb=#

创建行存表

tpccdb=# drop table if exists tctest;
DROP TABLE
tpccdb=# create table tctest(
tpccdb(# id int ,
tpccdb(# info varchar(500),
tpccdb(# val_int int,
tpccdb(# val_float decimal(12,2),
tpccdb(# crt_date date,
tpccdb(# crt_time timestamp,
tpccdb(# remark char(50)
tpccdb(# )
tpccdb-# WITH (autovacuum_enabled = off, toast.autovacuum_enabled = off,compression=no);
CREATE TABLE
tpccdb=#

插入数据

 

 

分别插入1000万数据，记录耗时情况，列存耗时57秒，行存耗时88秒。

列存表
tpccdb=# insert into tctest(id,info,val_int,val_float,crt_date,crt_time,remark)
tpccdb-# select
tpccdb-# generate_series(1,1999999999999999) id,
tpccdb-# md5(random()::text) info,
tpccdb-# generate_series(100,1999999999999999) val_int,
tpccdb-# random()*(10^5) val_float,
tpccdb-# sysdate crt_date,--clock_timestamp(),
tpccdb-# now() crt_time,
tpccdb-# 'first'||(random()*(10^3))::integer remark
tpccdb-# limit 10000000;
INSERT 0 10000000
Time: 57184.073 ms
tpccdb=#
行存表
tpccdb=# insert into tctest(id,info,val_int,val_float,crt_date,crt_time,remark)
tpccdb-# select
tpccdb-# generate_series(1,1999999999999999) id,
tpccdb-# md5(random()::text) info,
tpccdb-# generate_series(100,1999999999999999) val_int,
tpccdb-# random()*(10^5) val_float,
tpccdb-# sysdate crt_date,--clock_timestamp(),
tpccdb-# now() crt_time,
tpccdb-# 'first'||(random()*(10^3))::integer remark
tpccdb-# limit 10000000;
INSERT 0 10000000
Time: 88756.018 ms
tpccdb=#

使用空间对比

 

 

插入1000万数据，列存占用1072MB，行存占用1503MB。

列存表
tpccdb=# \dt+
                                                  List of relations
 Schema |  Name  | Type  | Owner |  Size   |                          Storage                           | Description 
--------+--------+-------+-------+---------+------------------------------------------------------------+-------------
 public | tctest | table | omm9  | 1072 MB | {orientation=column,autovacuum_enabled=off,compression=no} | 
(1 row)
tpccdb=#
行存表
tpccdb=#  \dt+
                                                 List of relations
 Schema |  Name  | Type  | Owner |  Size   |                         Storage                         | Description 
--------+--------+-------+-------+---------+---------------------------------------------------------+-------------
 public | tctest | table | omm9  | 1503 MB | {orientation=row,autovacuum_enabled=off,compression=no} | 
(1 row)

查询对比

 

 

列存表
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 499797924108.32 | 0.00 | 99999.97 | 49979.792410832000 | 2024-07-12 14:29:31 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 966.294 ms
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 499797924108.32 | 0.00 | 99999.97 | 49979.792410832000 | 2024-07-12 14:29:31 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 980.186 ms
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 499797924108.32 | 0.00 | 99999.97 | 49979.792410832000 | 2024-07-12 14:29:31 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 967.425 ms
tpccdb=#
行存表
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 500072069450.31 | 0.01 | 99999.98 | 50007.206945031000 | 2024-07-12 14:53:23 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 20705.293 ms
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 500072069450.31 | 0.01 | 99999.98 | 50007.206945031000 | 2024-07-12 14:53:23 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 11982.013 ms
tpccdb=# select sum(val_float),min(val_float),max(val_float),avg(val_float),max(crt_date),avg(val_int),sum(val_int) from tctest;
       sum       | min  |   max    |        avg         |         max         |         avg          |      sum       
-----------------+------+----------+--------------------+---------------------+----------------------+----------------
 500072069450.31 | 0.01 | 99999.98 | 50007.206945031000 | 2024-07-12 14:53:23 | 5000099.500000000000 | 50000995000000
(1 row)

Time: 12355.293 ms
tpccdb=#

更新对比

 

 

更新100万数据，列存表耗时2秒，行存表8秒。

列存表
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 2035.334 ms
tpccdb=# 
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 2096.676 ms
tpccdb=# 
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 2256.420 ms
tpccdb=#
行存表
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 8367.211 ms
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 8383.472 ms
tpccdb=# update tctest set crt_time=now(),remark='remark_'||md5(random()::text) limit 1000000;
UPDATE 1000000
Time: 8502.280 ms
tpccdb=#

删除对比

 

 

删除100万数据，列存表耗时0.1秒，行存表3秒。

列存表
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 152.016 ms
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 157.918 ms
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 162.581 ms
tpccdb=# 
tpccdb=# \dt+
                                                  List of relations
 Schema |  Name  | Type  | Owner |  Size   |                          Storage                           | Description 
--------+--------+-------+-------+---------+------------------------------------------------------------+-------------
 public | tctest | table | omm9  | 1393 MB | {orientation=column,autovacuum_enabled=off,compression=no} | 
(1 row)
行存表
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 3424.375 ms
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 3278.261 ms
tpccdb=# delete from tctest limit 1000000;
DELETE 1000000
Time: 3261.131 ms
tpccdb=# 

tpccdb=# \dt+
                                                 List of relations
 Schema |  Name  | Type  | Owner |  Size   |                         Storage                         | Description 
--------+--------+-------+-------+---------+---------------------------------------------------------+-------------
 public | tctest | table | omm9  | 1954 MB | {orientation=row,autovacuum_enabled=off,compression=no} | 
(1 row)

小结

 

 

经过测试发现，行存表和列存表，还是存在比较大的差异：

  1. 插入1000万数据，列存耗时57秒，行存耗时88秒，列存占用1072MB，行存占用1503MB。
  2. 更新100万数据，列存表耗时2秒，行存表8秒。
  3. 删除100万数据，列存表耗时0.1秒，行存表3秒。