PostgreSQL 11 新特性解读: 分区表增加哈希分区
本文共 6271 字，大约阅读时间需要 20 分钟。
PostgreSQL 11 的一个重量级新特性为分区表得到较大增强，例如支持哈希分区(HASH)表，因此 PostgreSQL 支持范围分区(RANGE)、列表分区(LIST)、>哈希分区(HASH)三种分区方式，本文简单演示下哈希分区表。
Hash Partitioning
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
Hash分区表的分区定义包含两个属性，如下：
- modulus: 指Hash分区个数。
- remainder: 指Hash分区键取模余。
创建分区表语法
CREATE TABLE table_name ( ... )[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } CREATE TABLE table_namePARTITION OF parent_table [ () ] FOR VALUES partition_bound_spec 创建数据生成函数
为了便于生成测试数据，创建以下两个函数用来随机生成指定长度的字符串，创建 random_range(int4, int4) 函数如下：
CREATE OR REPLACE FUNCTION random_range(int4, int4)RETURNS int4LANGUAGE SQLAS $$ SELECT ($1 + FLOOR(($2 - $1 + 1) * random() ))::int4;$$;
接着创建random_text_simple(length int4)函数，此函数会调用random_range(int4, int4)函数。
CREATE OR REPLACE FUNCTION random_text_simple(length int4)RETURNS textLANGUAGE PLPGSQLAS $$DECLARE possible_chars text := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; output text := ''; i int4; pos int4;BEGIN FOR i IN 1..length LOOP pos := random_range(1, length(possible_chars)); output := output || substr(possible_chars, pos, 1); END LOOP; RETURN output;END;$$;
random_text_simple(length int4)函数可以随机生成指定长度字符串，如下随机生成含三位字符的字符串。
mydb=> SELECT random_text_simple(3); random_text_simple -------------------- LL9(1 row)
随机生成含六位字符的字符串，如下所示：
mydb=> SELECT random_text_simple(6); random_text_simple -------------------- B81BPW(1 row)
后面会用到这个函数生成测试数据。
创建哈希分区父表
CREATE TABLE student ( stuname text , ctime timestamp(6) without time zone) PARTITION BY HASH(stuname);
创建索引
CREATE INDEX idx_stuendt_stuname on student using btree(stuname);
创建子表
CREATE TABLE student_p0 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 0);CREATE TABLE student_p1 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 1);CREATE TABLE student_p2 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 2);CREATE TABLE student_p3 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 3);
查看分区表定义
francs=> \d+ student Table "francs.student" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------------------------------+-----------+----------+---------+----------+--------------+------------- stuname | text | | | | extended | | ctime | timestamp(6) without time zone | | | | plain | | Partition key: HASH (stuname)Indexes: "idx_stuendt_stuname" btree (stuname)Partitions: student_p0 FOR VALUES WITH (modulus 4, remainder 0), student_p1 FOR VALUES WITH (modulus 4, remainder 1), student_p2 FOR VALUES WITH (modulus 4, remainder 2), student_p3 FOR VALUES WITH (modulus 4, remainder 3)
从以上看出表 student 和它的四个分区。
插入测试数据
使用之前创建的函数 random_text_simple() 生成100万测试数据，如下。
INSERT INTO student(stuname,ctime) SELECT random_text_simple(6),clock_timestamp() FROM generate_series(1,1000000);
查看分区表数据
表数据如下
francs=> SELECT * FROM student LIMIT 3; stuname | ctime ---------+--------------------- 4JJOPN | 2018-09-20 10:45:06 NHQONC | 2018-09-20 10:45:06 8V5BGH | 2018-09-20 10:45:06(3 rows)
统计分区数据量
francs=> SELECT tableoid::regclass,count(*) from student group by 1 order by 1; tableoid | count ------------+-------- student_p0 | 250510 student_p1 | 249448 student_p2 | 249620 student_p3 | 250422(4 rows)
可见数据均匀分布到了四个分区。
根据分区键查询
francs=> EXPLAIN ANALYZE SELECT * FROM student WHERE stuname='3LXBEV'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Append (cost=0.42..8.44 rows=1 width=15) (actual time=0.017..0.018 rows=1 loops=1) -> Index Scan using student_p3_stuname_idx on student_p3 (cost=0.42..8.44 rows=1 width=15) (actual time=0.017..0.017 rows=1 loops=1) Index Cond: (stuname = '3LXBEV'::text) Planning Time: 0.198 ms Execution Time: 0.042 ms(5 rows)
根据分区键stuname查询仅扫描分区 student_p3，并走了索引。
根据非分区键查询
francs=> EXPLAIN ANALYZE SELECT * FROM student WHERE ctime='2018-09-20 10:53:55.48392'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..13761.36 rows=4 width=15) (actual time=37.891..39.183 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Append (cost=0.00..12760.96 rows=4 width=15) (actual time=23.753..35.006 rows=0 loops=3) -> Parallel Seq Scan on student_p0 (cost=0.00..3196.99 rows=1 width=15) (actual time=0.014..28.550 rows=1 loops=1) Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone) Rows Removed by Filter: 250509 -> Parallel Seq Scan on student_p3 (cost=0.00..3195.34 rows=1 width=15) (actual time=29.543..29.543 rows=0 loops=1) Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone) Rows Removed by Filter: 250422 -> Parallel Seq Scan on student_p2 (cost=0.00..3185.44 rows=1 width=15) (actual time=8.260..8.260 rows=0 loops=3) Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone) Rows Removed by Filter: 83207 -> Parallel Seq Scan on student_p1 (cost=0.00..3183.18 rows=1 width=15) (actual time=22.135..22.135 rows=0 loops=1) Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone) Rows Removed by Filter: 249448 Planning Time: 0.183 ms Execution Time: 39.219 ms(18 rows)
根据非分区键ctime查询扫描了分区表所有分区。
总结
本文演示了 PostgreSQL 哈希分区表的创建、测试数据的生成导入和查询计划，后面博客演示分区表增强的其它方面。
参考
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例！
购买链接：
转载地址：http://lticm.baihongyu.com/
你可能感兴趣的文章
更新脚本
查看>>
用Android-X86和VMware打造高性能Android开发环境
查看>>
我的友情链接
查看>>
学习Hadoop找到的一些资源
查看>>
经纬度精度差别
查看>>
【08】分析类
查看>>
垃圾收集的种类
查看>>
HRegionServer启动后自动关闭的问题
查看>>
maven插件assembly利用profiles打不同环境发布包
查看>>
Android系统学习总结1--init和Zygote
查看>>
参考人体排毒时间表与自我时间调整(与诸君共勉)
查看>>
深入字节码 -- 使用 ASM 实现 AOP
查看>>
CListCtrl使用技巧
查看>>
内存监控工具
查看>>
linux 下查看一个进程运行路径
查看>>
CSS 优先级
查看>>
ElasticSearch 集群状态图形化界面：cerebro
查看>>
Spark内存管理模型
查看>>
shell脚本基础
查看>>
shell 脚本总结
查看>>