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

学习目标
学习openGauss存储模型-行存和列存

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

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

课程学习
连接数据库
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.

omm=#

1.创建行存表
omm=# CREATE TABLE test_t1
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm-# (
omm(# col3 NUMBER
omm(# );
CREATE TABLE

–压缩属性为no
omm=# \d+ test_t1
Table "public.test_t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=row, compression=no

omm=# insert into test_t1 select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65), 2) as col1, repeat(chr(int4(random() * 26) + 65), 30) as col2, (random() * (10^4))::integer as col3);
INSERT 0 100000

2.创建列存表
omm=# CREATE TABLE test_t2
omm# (
omm# col1 CHAR(2),
omm# col2 VARCHAR2(40),
omm# col3 NUMBER
omm# )
omm# WITH (ORIENTATION = COLUMN);
CREATE TABLE
–压缩属性为low
omm=# \d+ test_t2;
Table "public.test_t2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=column, compression=low
–插入和行存表相同的数据
omm=# insert into test_t2 select * from test_t1;
INSERT 0 100000
3.占用空间对比
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+---------+-------+-------+---------+--------------------------------------+-------------
public | test_t1 | table | omm | 6760 kB | {orientation=row,compression=no} |
public | test_t2 | table | omm | 1112 kB | {orientation=column,compression=low} |
(2 rows)
4.对比读取一列的速度
omm=# analyze VERBOSE test_t1;
INFO: analyzing "public.test_t1"(gaussdb pid=1)
INFO: ANALYZE INFO : "test_t1": scanned 841 of 841 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze VERBOSE test_t2;
INFO: analyzing "public.test_t2"(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of "pg_delta_16402": 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 : "test_t2": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE

5.对比插入一行的速度
–行存表时间少于列存表
omm=# explain analyze insert into test_t1 values('x', 'xxxx', '123');
QUERY PLAN
-----------------------------------------------------------------------------------------------
[Bypass]
Insert on test_t1 (cost=0.00..0.01 rows=1 width=0) (actual time=0.069..0.070 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.180 ms
(4 rows)

omm=# explain analyze insert into test_t2 values('x', 'xxxx', '123');
QUERY PLAN
-----------------------------------------------------------------------------------------------
Insert on test_t2 (cost=0.00..0.01 rows=1 width=0) (actual time=3.265..3.267 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
Total runtime: 3.377 ms
(3 rows)

6.清理数据
omm=# drop table test_t1;
DROP TABLE
omm=# drop table test_t2;
DROP TABLE

课程作业
1.创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)
CREATE TABLE row_test
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
);

CREATE TABLE col_test
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
) WITH (ORIENTATION = COLUMN);

insert into row_test select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 10) + 20), 2) as col1, repeat(chr(int4(random() * 30) + 60), 6) as col2, (random() * (10^4))::integer as col3)
insert into col_test select * from row_test;
\dt

-- 行表
omm=# CREATE TABLE row_test
omm-# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# );
CREATE TABLE
--列表
omm=# CREATE TABLE col_test
omm-# (
omm(# col1 CHAR(2),
omm(# col3 NUMBER
col2 VARCHAR2(40),
omm(# omm(# ) WITH (ORIENTATION = COLUMN);
CREATE TABLE
--插入数据
omm=# insert into row_test select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 10) + 20), 2) as col1, repeat(chr(int4(random() * 30) + 60), 6) as col2, (random() * (10^4))::integer as col3);
INSERT 0 100000
omm=# insert into col_test select * from row_test;
INSERT 0 100000
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+--------------------------------------
public | col_test | table | omm | {orientation=column,compression=low}
public | row_test | table | omm | {orientation=row,compression=no}
(2 rows)
2.对比行存表和列存表空间大小
\d+
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------+-------+-------+---------+--------------------------------------+-------------
public | col_test | table | omm | 1000 kB | {orientation=column,compression=low} |
public | row_test | table | omm | 4360 kB | {orientation=row,compression=no} |
(2 rows)

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

explain analyze select col1 from row_test;
explain analyze select col1 from col_test;


explain analyze insert into row_test values('y', 'yyyy', '123');
explain analyze insert into col_test values('y', 'yyyy', '123');

--查询列速度

omm=# explain analyze select col1 from row_test;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on row_test (cost=0.00..797.91 rows=25691 width=12) (actual time=0.014..23.525 rows=100000 loops=1)
Total runtime: 32.118 ms
(2 rows)

omm=# explain analyze select col1 from col_test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Row Adapter (cost=10.47..10.47 rows=475 width=12) (actual time=0.707..9.991 rows=100000 loops=1)
-> CStore Scan on col_test (cost=0.00..10.47 rows=475 width=12) (actual time=0.701..1.381 rows=100000 loops=1)
Total runtime: 16.527 ms
(3 rows)

--插入一行速度
omm=# explain analyze insert into row_test values('y', 'yyyy', '123');
QUERY PLAN
------------------------------------------------------------------------------------------------
[Bypass]
Insert on row_test (cost=0.00..0.01 rows=1 width=0) (actual time=0.030..0.031 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.123 ms
(4 rows)

omm=# explain analyze insert into col_test values('y', 'yyyy', '123');
QUERY PLAN
------------------------------------------------------------------------------------------------
Insert on col_test (cost=0.00..0.01 rows=1 width=0) (actual time=3.151..3.152 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
Total runtime: 3.237 ms
(3 rows)
4.清理数据

drop table row_test;
drop table col_test;

omm=# drop table row_test;
omm=# DROP TABLE

omm=# drop table col_test;
DROP TABLE