GaussDB(DWS)创建行存表的最佳实践

列顺序对表访问有什么影响？

设计一个对比测试，来看看列顺序对于行存表的查询访问性能有什么影响。以最常见的lineitem表为基础，调整列顺序创建多个变种表，然后直接对比select相同列数据的时间。

首先，创建原生的lineitem表并导入数据。

CREATE TABLE LINEITEM( 
L_ORDERKEY BIGINT , 
L_PARTKEY BIGINT , 
L_SUPPKEY BIGINT , 
L_LINENUMBER BIGINT , 
L_QUANTITY DECIMAL(15,2) , 
L_EXTENDEDPRICE DECIMAL(15,2) , 
L_DISCOUNT DECIMAL(15,2) , 
L_TAX DECIMAL(15,2) , 
L_RETURNFLAG CHAR(1) , 
L_LINESTATUS CHAR(1) , 
L_SHIPDATE DATE , 
L_COMMITDATE DATE , 
L_RECEIPTDATE DATE , 
L_SHIPINSTRUCT CHAR(25) , 
L_SHIPMODE CHAR(10) , 
L_COMMENT VARCHAR(44)) ;
copy LINEITEM from '/data/lineitem_10X.dat' with (delimiter '|', encoding 'utf8');
postgres=# select count(*) from lineitem ;
  count
----------
 12002430
(1 row)

Time: 3095.779 ms

调整列顺序创建新表lineitem2， 所有整数类型、时间类型的列连续靠前，所有字符串、decimal类型的列连续靠后放置。 表定义以及导入数据如下

create table lineitem2 (
L_ORDERKEY BIGINT ,
L_PARTKEY BIGINT ,
L_SUPPKEY BIGINT ,
L_LINENUMBER BIGINT , 
L_SHIPDATE DATE ,
L_COMMITDATE DATE ,
L_RECEIPTDATE DATE ,
L_QUANTITY DECIMAL(15,2) ,
L_EXTENDEDPRICE DECIMAL(15,2) ,
L_DISCOUNT DECIMAL(15,2) ,
L_TAX DECIMAL(15,2) ,
L_RETURNFLAG CHAR(1) ,
L_LINESTATUS CHAR(1) ,
L_SHIPINSTRUCT CHAR(25) ,
L_SHIPMODE CHAR(10) ,
L_COMMENT VARCHAR(44) );

insert into lineitem2 select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_SHIPDATE, 
L_COMMITDATE, L_RECEIPTDATE, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, 
L_RETURNFLAG, L_LINESTATUS, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT
 from lineitem ;

调整列顺序创建新表lineitem3，所有字符串、decimal类型的列连续靠前放置，所有整数类型、时间类型的列连续靠后放置。 表定义以及导入数据如下

create table lineitem3(
L_QUANTITY DECIMAL(15,2) ,
L_EXTENDEDPRICE DECIMAL(15,2) ,
L_DISCOUNT DECIMAL(15,2) ,
L_TAX DECIMAL(15,2) ,
L_RETURNFLAG CHAR(1) ,
L_LINESTATUS CHAR(1) ,
L_SHIPINSTRUCT CHAR(25) ,
L_SHIPMODE CHAR(10) ,
L_COMMENT VARCHAR(44),
L_ORDERKEY BIGINT ,
L_PARTKEY BIGINT ,
L_SUPPKEY BIGINT ,
L_LINENUMBER BIGINT , 
L_SHIPDATE DATE ,
L_COMMITDATE DATE ,
L_RECEIPTDATE DATE  );

insert into lineitem3 select L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT,
 L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPINSTRUCT, L_SHIPMODE, 
L_COMMENT, L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, 
L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE from lineitem ;

对比测试一下SQL语句 select L_ORDERKEY , L_PARTKEY , L_SUPPKEY , L_LINENUMBER , L_SHIPDATE , L_COMMITDATE , L_RECEIPTDATE from 的执行效率。 为了消除输出结果的影响，我们在语句前面加上explain performance。

lineitem2表的查询语句以及执行结果如下：

explain performance select L_ORDERKEY , L_PARTKEY 
, L_SUPPKEY , L_LINENUMBER , L_SHIPDATE , 
L_COMMITDATE , L_RECEIPTDATE from lineitem2;

lineitem3表的查询语句以及执行结果如下：

explain performance select L_ORDERKEY , L_PARTKEY , 
L_SUPPKEY , L_LINENUMBER , L_SHIPDATE , 
L_COMMITDATE , L_RECEIPTDATE from lineitem3;

可以看到，列顺序不同，行存表查询访问的效率差别很大。那么，影响行存表查询访问效率的因素有哪些呢？ 这得从行存表所支持的数据类型和记录结构说起。

数据类型

GaussDB(DWS)提供给用户层的数据类型是比较丰富的。一个数据类型主要涉及的属性包括了：

• 类型名称；
• 类型长度；
• 是否为值传递；
• 对齐字节。

当然还有其他的属性，可以通过\d pg_type命令来查看的，这里主要关心我们所关注的、与记录结构相关的几个属性。

常见的数据类型有布尔类型、字节类型、字符类型、名字、整数类型、字符串类型、OID类型等；这在typname一列可以看到。

typlen表示了数据类型的长度信息。列为正值，表示实际数据类型的长度； 列为负数则表示变长类型，可选值有-1和-2.

• 布尔类型和字符类型，它的每一个数值占用1个字节。
• int2这类short int类的整数，它的每一个数值占用着2个字节。
• int4类的整数，它的每一人数值占用着4个字节。
• bigint/int8这种大整数则占用着8字节大小。
• name类型用来记录对象的名称，使用的是固定的64字节（当然末尾0字符）。
• -1表示的是变长数据，主要包括了其他定长数据类型和字符串数据类型、超长数据类型。
• -2表示的是C字符串类型，尾0结束的字符串。

typbyval是布尔属性，用于表示这些数据类型进行赋值操作、比较操作的时候，是否可以直接用整数类型的= == != >= <= > <这些操作符；这些类型基本都是整数数据，1/2/4/8这四种字节的整数。对于其他的非值传递数据类型，则必须有相应的比较函数和赋值函数。

tpyalign表示数据类型的字节对齐方式。c表示单字节对齐方式；s表示双字节对齐；i表示四字节对齐;d表示8字节对齐。所有的数据类型的对齐方式就这么四种。

下面截取了常见的一些数据类型以及它的主要属性。

那么这种变长的数据类型在磁盘上应该怎么存储呢？简单来讲，主要有两种存储格式，

• 1字节头，后面是数据部分
• 4字节头，后面是数据部分

无论是哪种数据存储格式，头部含有较为重要的信息，包括需要区别字节头长度、是否经过了LZ压缩等信息。

记录结构

上面介绍了数据类型的基本重要知识。在记录结构中，各个列的排列将会遵守对应的数据类型的要求，包括对齐方式，然后依序放置数据即可。以上面的lineitem2表为例，其结构大体如下：

第一个较长是记录的整体结构，第二个图是对user data数据部分的具体表示。

在对记录访问的时候，为了加速记录的访问，使用了一个cached offset的信息，它表示了对应列在记录中的偏移位置是不会随着数据的变化而变化的。也正是因为这一点，这个cached offset也仅对表定义中、前面连续的、定长的列是有效的；一旦出现一个变长的数据列，必然会影响后续列访问的偏移位置，后面列的访问强依赖于前面变长数据的实际长度；当然，还有一个特性是会影响cached offset的，那就是null，如果列属性中出现了可为null的约束，那么null 值映射图也是会影响到cache offset的；仅有非null的约束、在所有记录上共有的特征，这才能够利用上cached offset加速特性。

总结

在创建行存表的时候，可以将数值类型长度不变的、非null的列连续写在前面，其他的列放置表定义后面。这样，查询行存表数据的时候，前面部分的列数据访问性能是较高的。