openGauss每日一练第21天|openGauss存储模型-行存和列存

本次课学习的内容是openGauss存储模型-行存和列存。

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。

行、列存储模型各有优劣，通常用于TP场景的数据库，默认使用行存储，仅对执行复杂查询且数据量大的AP场景时，才使用列存储。

root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

作业打卡

1、创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)

omm=# CREATE TABLE t1
omm-# (
omm(# t1_id int,
omm(# t1_name char(20),
omm(# t1_text text
omm(# );
CREATE TABLE
omm=# \d+ t1
                              Table "public.t1"
 Column  |     Type      | Modifiers | Storage  | Stats target | Description 
---------+---------------+-----------+----------+--------------+-------------
 t1_id   | integer       |           | plain    |              | 
 t1_name | character(20) |           | extended |              | 
 t1_text | text          |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into t1 select t1_id, t1_name, t1_text from (select generate_series(1, 100000) as key,(random() * (10^4)) as t1_id, repeat(chr(int4(random() * 30) + 66), 2) as  t1_text, repeat(chr(int4(random() * 20) + 65), 20) as  t1_name);

INSERT 0 100000
omm=# CREATE TABLE t2
omm-# (
omm(# t2_id int,
omm(# t2_name char(20),
omm(# t2_text text
omm(# )
omm-# WITH (ORIENTATION = COLUMN);
CREATE TABLE
omm=# \d+ t2
                              Table "public.t2"
 Column  |     Type      | Modifiers | Storage  | Stats target | Description 
---------+---------------+-----------+----------+--------------+-------------
 t2_id   | integer       |           | plain    |              | 
 t2_name | character(20) |           | extended |              | 
 t2_text | text          |           | extended |              | 
Has OIDs: no
Options: orientation=column, compression=low
omm=# insert into t2 select * from t1;
INSERT 0 100000

2、对比行存表和列存表空间大小

omm=# \d+
                                      List of relations
 Schema | Name | Type  | Owner |  Size   |               Storage                | Description 
--------+------+-------+-------+---------+--------------------------------------+-------------
 public | t1   | table | omm   | 5960 kB | {orientation=row,compression=no}     | 
 public | t2   | table | omm   | 912 kB  | {orientation=column,compression=low} | 
(2 rows)

3、对比查询一列和插入一行的速度

omm=# analyze VERBOSE t1;
INFO:  analyzing "public.t1"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 741 of 741 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze VERBOSE t2;
INFO:  analyzing "public.t2"(gaussdb pid=1)
INFO:  ANALYZE INFO : estimate total rows of "pg_delta_16398": scanned 0 pages of total 0 pages with 1 retry times, containing 0 live rows and 0 dead rows,  estimated 0 total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t2": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE

omm=# explain analyze insert into t1 values(123, 'xxxx', 'aaaa');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 [Bypass]
 Insert on t1  (cost=0.00..0.01 rows=1 width=0) (actual time=0.055..0.056 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Total runtime: 0.142 ms
(4 rows)

omm=# explain analyze insert into t2 values(123, 'xxxx', 'aaaa');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Insert on t2  (cost=0.00..0.01 rows=1 width=0) (actual time=0.151..0.152 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.245 ms
(3 rows)

4、清理数据

omm=# drop table t1;
DROP TABLE
omm=# drop table t2;
DROP TABLE

总结：21天的打卡到此结束啦，学习的内容比较简单，每天花半小时左右就能学完，大家学习的积极性都不错呢，互相促进。通过这个训练营学习到了opengauss的一些基础操作，同时也养成了每天打卡学习的好习惯。感谢恩墨！