干货分享｜一文示例优炫数据库的列存用法

优炫数据库支持行存储和列存储引擎可以同时工作，支持单机与MPP部署方式，适用 OLTP/OLAP混合业务场景。

什么情况下会用到列存？概括为三点：

1

针对OLAP类型的表，行数特别大(百万、千万甚至上亿)；

2

关键业务以查询为主；

3

查询每次查询涉及列数较少(占总列数的10%左右或更低)；

所以，存储数据读取效率不高，执行速度较慢，又不能满足业务需求，推荐使用列存功能。

优炫数据库支持列存储，支持列存数据的索引、约束、清理、并行扫描、分区、并发与日志等功能。

数据按列存储，单独存放，在某些场景下可大幅降低系统 I/O(如:宽表情况下的聚合运算在某个或某几个列上)。数据类型一致，特征相似，支持高效压缩，降低磁盘空间，实现高效聚合。数据在读取过程不产生冗余数据，适合于在少量列上计算数据聚集的数据仓库负载，或需要对单列定期更新但不修改其他列的情况。

用法介绍

初始化集群

./initdb -D column -W
Enter new superuser password:
Enter it again:
/*输入一个新的管理员口令*/

启动数据库服务

./ux_ctl start -D ../column

连接数据库

./uxsql -d uxdb
Password:
/*输入初始化时设置的口令 */

创建列存表

//创建普通列存表
createtabletable_name(idint,nametext,ageint)with(orientation=column, appendonly=true);

//创建带约束的列存表
createtableproducts(product_noint,nametext,pricenumericCONSTRAINTpositive_priceCHECK(price>0))WITH(orientation=column, appendonly=true);

//创建带数据压缩的列存表
createtabletbl_col_compressed(idint,nametext,ageint)with(orientation=column,appendonly=true,compresstype=zlib, compresslevel=5);

//查看表数据占用存储空间
selectux_relation_size('tbl_col_compressed');compresstype表示配置压缩算法，可配置zlib和RLE_TYPE。compresslevel表示压缩算法对 于的压缩级别:当compresstype配置zlib时，取值1~9;当compresstype配置RLE_TYPE时，取值1~4。数值越大压缩效率越高，cpu消耗较大。表数据存储采用压缩方式，节省磁盘存 储空间，但是在数据写入、读取过程会引入压缩、解压缩的CPU损耗。

修改列存表

//增加列

alter table table_name add column address text;

//删除列
alter table table_name drop column address;

删除列存表(包含表数据和表定义) drop table table_name;

增、删、改、查表数据

 //插入

insert into table_name VALUES(1,'zhangsan1',23);

//删除
delete from table_name WHERE id=1;

//更改
update table_name SET name='lisi' WHERE id=2;

//查找
select * FROM table_name;
select name,age FROM table_name WHERE id=3;

导入/导出表数据

从文本文件导入表数据

create table column_copy (id int,name text,age int) WITH(orientation=column, appendonly=true);

copy column_copy FROM '/home/uxdb/uxdbinstall/dbsql/bin/copy_from.txt' (DELIMITER ' '); copy_from.txt文件内容示例

1 wanger 23
2 zhangsan 25 3 lisi 28

将表数据导出到文本文件

copy column_copy TO '/home/uxdb/uxdbinstall/dbsql/bin/copy_to.txt';

清理表数在对表执行删除、修改后，执行vacuum操作可删除表数据文件中残留的旧记录值。vacuum FULL table_name;

创建索引

为加速特定列上的条件查询速度，可在特定列上创建索引。

create index index_name ON table_name(id);

//查看表和索引定义 \d table_name

//查询索引详细信息 \di index_name

//删除索引
drop index index_name;

//创建brin粗粒度索引
create index index_name ON table_name using brin(column_name) with(pages_per_range=128);

128表示128个块对应一个索引条目，一个块包含128个表记录行。pages_per_range越小， 索引越精细，索引文件里索引行记录越多，占用空间越大。

Partition表分区

范围分区列存表被分区到由键列或列集定义的“范围”中，分配给不同分区的值范围之间没有重叠。例如，可以按日期范围进行分区， 也可以按特定业务对象的标识符范围进行分区。

创建分区主表

CREATE TABLE column01 (id int,name text,age int) PARTITION BY RANGE(id) 

WITH(appendonly=true,orientation=column); parttition by指定分区方式和分区键。

创建分区子表(以5个子表为例)

CREATE TABLE column01_1000 PARTITION OF column01 FOR VALUES FROM('1') TO('1000') WITH(appendonly=true,orientation=column);

CREATE TABLE column01_2000 PARTITION OF column01 FOR VALUES FROM('1000') TO('2000') WITH(appendonly=true,orientation=column);

CREATE TABLE column01_3000 PARTITION OF column01 FOR VALUES FROM('2000') TO('3000') WITH(appendonly=true,orientation=column);

CREATE TABLE column01_4000 PARTITION OF column01 FOR VALUES FROM('3000') TO('4000') WITH(appendonly=true,orientation=column);

CREATE TABLE column01_5000 PARTITION OF column01 FOR VALUES FROM('4000') TO('5000') WITH(appendonly=true,orientation=column);

查看分区子表

\d column01_1000

插入数据

insert into column01 values (generate_series(1,4999),'name1',34);

查看数据

EXPLAIN select * from column01;

删除分区子表

DROP TABLE column01_1000;

列表分区

列存表通过明确列出每个分区中出现的键值进行分区。

创建分区主表

create table cs_list_part (id int8,random_char varchar(100),day_id varchar(8))

PARTITION BY LIST(day_id) WITH(appendonly=true,orientation=column);

创建分区子表(以3个子表为例)

CREATE TABLE cs_list_part_p20171130 PARTITION OF cs_list_part FOR VALUES in ('20171130');

CREATE TABLE cs_list_part_p20171201 PARTITION OF cs_list_part FOR VALUES in ('20171201');

CREATE TABLE cs_list_part_p20171202 PARTITION OF cs_list_part FOR VALUES in ('20171202');

CREATE TABLE cs_list_part_p20171203 PARTITION OF cs_list_part FOR VALUES in ('20171203');

插入数据

insert into cs_list_part select * from (
select generate_series(1, 5) as id, md5(random()::text) as info all
select generate_series(1, 5) as id, md5(random()::text) as info all
select generate_series(1, 5) as id, md5(random()::text) as info all
select generate_series(1, 5) as id, md5(random()::text) as info ) t0;

使用不存在的分区值插入记录，插入失败并会报错。

insert into cs_list_part select * from(selectgenerate_series(1, 5) as id, md5(random()::text) as info ) t0;

ERROR: no partition of relation "cs_list_part" found for row

DETAIL: Partition key of the failing row contains (day_id) = (20171129).

查询数据

 //分区主表

select * from cs_list_part order by day_id,id;

//分区子表
select * from cs_list_part_p20171130;