openGauss每日一练第21天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。
行、列存储模型各有优劣，通常用于TP场景的数据库，默认使用行存储，仅对执行复杂查询且数据量大的AP场景时，才使用列存储

学习笔记

• 创建列存表，压缩属性为low

omm=# CREATE TABLE test_t2
omm-# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# )
WITH (ORIENTATION = COLUMN);
CREATE TABLE

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=# \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)

课后作业

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

omm=# create table t_row
omm-# (
omm(#     content varchar2(50),
omm(#     num integer
omm(# );
CREATE TABLE

omm=# create table t_col
omm-# (
omm(#     content varchar2(50),
omm(#     num integer
omm(# ) with (orientation = column); 
CREATE TABLE

omm=# \d+
                                       List of relations
 Schema | Name  | Type  | Owner |  Size   |               Storage                | Description 
--------+-------+-------+-------+---------+--------------------------------------+-------------
 public | t_col | table | omm   | 16 kB   | {orientation=column,compression=low} | 
 public | t_row | table | omm   | 0 bytes | {orientation=row,compression=no}     | 
(2 rows)

omm=# \d+ t_row
                                Table "public.t_row"
 Column  |         Type          | Modifiers | Storage  | Stats target | Description 
---------+-----------------------+-----------+----------+--------------+-------------
 content | character varying(50) |           | extended |              | 
 num     | integer               |           | plain    |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=# \d+ t_col
                                Table "public.t_col"
 Column  |         Type          | Modifiers | Storage  | Stats target | Description 
---------+-----------------------+-----------+----------+--------------+-------------
 content | character varying(50) |           | extended |              | 
 num     | integer               |           | plain    |              | 
Has OIDs: no
Options: orientation=column, compression=low

omm=# insert into t_row 
omm-#     select content, num from (
omm(#         select generate_series(1, 100000) as key, 
omm(#                repeat(chr(int4(random() * 26) + 65), 50) as  content, 
omm(#                (random() * (10^4))::integer as num
omm(#     );
INSERT 0 100000
omm=# insert into t_col select * from t_row;
INSERT 0 100000

omm=# select * from t_row limit 10;
                      content                       | num  
----------------------------------------------------+------
 WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW | 8406
 FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF | 3442
 QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ | 9662
 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | 4288
 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB | 5102
 GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG | 4679
 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB | 7841
 TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT | 2027
 IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII |  674
 WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW | 3743
(10 rows)

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

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

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

omm=# analyze VERBOSE t_row;
INFO:  analyzing "public.t_row"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t_row": scanned 1031 of 1031 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE

omm=# analyze VERBOSE t_col;
INFO:  analyzing "public.t_col"(gaussdb pid=1)
INFO:  ANALYZE INFO : estimate total rows of "pg_delta_16490": 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 : "t_col": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE

omm=# explain analyze select distinct content from t_row;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2281.00..2281.27 rows=27 width=51) (actual time=63.004..63.009 rows=27 loops=1)
   Group By Key: content
   ->  Seq Scan on t_row  (cost=0.00..2031.00 rows=100000 width=51) (actual time=0.013..29.680 rows=100000 loops=1)
 Total runtime: 63.075 ms
(4 rows)

omm=# explain analyze select distinct content from t_col;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Row Adapter  (cost=1154.27..1154.27 rows=27 width=51) (actual time=7.121..7.122 rows=27 loops=1)
   ->  Vector Sonic Hash Aggregate  (cost=1154.00..1154.27 rows=27 width=51) (actual time=7.118..7.118 rows=27 loops=1)
         Group By Key: content
         ->  CStore Scan on t_col  (cost=0.00..904.00 rows=100000 width=51) (actual time=0.040..0.877 rows=100000 loops=1)
 Total runtime: 7.235 ms
(5 rows)

omm=# explain analyze insert into t_row values('hello world', 123);
                                          QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 [Bypass]
 Insert on t_row  (cost=0.00..0.01 rows=1 width=0) (actual time=0.060..0.061 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.148 ms
(4 rows)

omm=# explain analyze insert into t_col values('hello world', 123);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Insert on t_col  (cost=0.00..0.01 rows=1 width=0) (actual time=0.153..0.154 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.239 ms
(3 rows)

4.清理数据

omm=# drop table t_row,t_col;
DROP TABLE

学习资源

• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！