学习心得

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

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

0.进入系统

su - omm
gsql -r

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

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


insert into test_row values('AA', 'This is fun!', generate_series(1, 10000));
insert into test_row values('BB', 'This is NOT fun!', generate_series(10001, 90000));
insert into test_row values('ZZ', 'This is End!', generate_series(90001, 100000));


insert into test_col values('AA', 'This is fun!', generate_series(1, 10000));
insert into test_col values('BB', 'This is NOT fun!', generate_series(10001, 90000));
insert into test_col values('ZZ', 'This is End!', generate_series(90001, 100000));

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

\d+

• 回显

                                      List of relations
 Schema |   Name   | Type  | Owner |  Size   |             Storage              | Description
--------+----------+-------+-------+---------+----------------------------------+-------------
 public | products | table | omm   | 0 bytes | {orientation=row,compression=no} |
 public | test_col | table | omm   | 5800 kB | {orientation=row,compression=no} |
 public | test_row | table | omm   | 11 MB   | {orientation=row,compression=no} |
(3 rows)

结论: 行存表占用空间更大.

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

analyze VERBOSE test_row;
analyze VERBOSE test_col;

• 回显

INFO:  analyzing "public.test_row"(sgnode pid=5266)
INFO:  ANALYZE INFO : "test_row": scanned 1442 of 1442 pages, containing 100000 live rows and 100001 dead rows; 30000 rows in sample, 100000 estimated total rows(sgnode pid=5266)
ANALYZE

INFO:  analyzing "public.test_col"(sgnode pid=5266)
INFO:  ANALYZE INFO : "test_col": scanned 721 of 721 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(sgnode pid=5266)

explain analyze insert into test_col values('XX', 'xxxx', '123333');
explain analyze insert into test_row values('XX', 'xxxx', '123333');

• 回显

 Insert on test_col  (cost=0.00..0.01 rows=1 width=0) (actual time=0.372..0.373 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.484 ms
(4 rows)

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 [Bypass]
 Insert on test_row  (cost=0.00..0.01 rows=1 width=0) (actual time=0.066..0.067 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
 Total runtime: 0.132 ms
(4 rows)

结论: 行存表插入速度更快.

4.清理数据

DROP TABLE test_row;
DROP TABLE test_col;

• 回显

DROP TABLE
DROP TABLE