PostgreSQL/PgSQL使用List分区表代替Hash分区表

目录

0、20230806更新：

1、说在前面的话（业务场景）

2、实现方案

3、实现脚本及测试脚本

4、查询性能测试

0、20230806更新：

周末想起来再验证一下，发现目前PgSQL的版本，如果像下面例子来创建List分区表（“PARTITION BY LIST(SUBSTR(city_name,1,1))”），在实际查询的时候，并不能直接定位到相应的子表。

所以，这个贴子的应用场景，应该是再建一个char(1)的字段，专门用于分区表中记录的分区和定位；

相应的，这个字段保存的是city_name字段的第一个字符。

插入和查询的时候，需要把相应的字符做为插入的值和查询条件来使用，这样性能上就没问题了。

最近家里有事，心情不好，只在这里写一下修改方案，就不更新贴子了。

------正文开始-----------------------------------------------------------------------------------------------------

说实话，我是因为不太理解Hash分区表才有这样的想法的。

1、说在前面的话（业务场景）

如果系统中需要记录所有发放给的客人的卡号，这些信息如果放在一个表里，势必是会造成记录数超级多。

而如果想要使用我之前方案里的短UUID方案的话（生成短的8位UUID（C#+JAVA）），更是需有一个表来做记录和判断，以免发生重号的情况，那么使用分区表以提高查询性能就势在必行了。

此应用场景下，没有中文等多字节的字符。

Hash表的话，我不确定其性能到底怎么样，还是自己维护一组List分区表安心一点。

写SQL也就是半小时的事，先搞好之后记录下来，以后再去测试两种分区表的性能。

2、实现方案

建一个List分区表，字段不用多，保证IO时性能，分区值为某个字段的第一个字母。

3、实现脚本及测试脚本

-- 建分区主表cd_cities
CREATE TABLE cd_cities (
    city_id     bigint not null,
    city_name   text   not null,
    population  bigint
) PARTITION BY LIST(SUBSTR(city_name,1,1));
-- 【LIST(SUBSTR(city_name,1,1))】表示，List分区的关键值为city_name字段第一个字符的值
-- PgSQL默认是区分大小写的，可自行修改为不区分这个字符的大小写

-- 建分区子表，其中cd_cities_default表为不符合分区规则时写入的默认分区子表
CREATE TABLE cd_cities_default PARTITION OF cd_cities DEFAULT;
CREATE TABLE cd_cities_a1 PARTITION OF cd_cities FOR VALUES IN ('A');
CREATE TABLE cd_cities_a2 PARTITION OF cd_cities FOR VALUES IN ('a');
CREATE TABLE cd_cities_b1 PARTITION OF cd_cities FOR VALUES IN ('B');
CREATE TABLE cd_cities_b2 PARTITION OF cd_cities FOR VALUES IN ('b');
CREATE TABLE cd_cities_c1 PARTITION OF cd_cities FOR VALUES IN ('C');
CREATE TABLE cd_cities_c2 PARTITION OF cd_cities FOR VALUES IN ('c');
CREATE TABLE cd_cities_d1 PARTITION OF cd_cities FOR VALUES IN ('D');
CREATE TABLE cd_cities_d2 PARTITION OF cd_cities FOR VALUES IN ('d');
--其他的分区子表，自己加吧，这里只是测试验证一下


-- 写入测试数据
INSERT INTO cd_cities
SELECT 1,'A111',1 UNION ALL
SELECT 2,'a cup of tea',2 UNION ALL
SELECT 3,'BeiJing',3 UNION ALL
SELECT 4,'business',4 UNION ALL
SELECT 5,'C333',5 UNION ALL
SELECT 6,'D444',6 UNION ALL
SELECT 7,'1024MB',7 UNION ALL
SELECT 8,'Green Tea',8 ;


-- 查询所有记录
SELECT * FROM cd_cities;

-- 查询第一个字母为A的分区子表的记录
SELECT * FROM cd_cities_a1;

-- 查询第一个字母为a的分区子表的记录
SELECT * FROM cd_cities_a2;

-- 查询不符合现有分区规则的默认分区子表
SELECT * FROM cd_cities_default;

4、查询性能测试

发现这样设计表结构是失败的，通过Explan分析，引擎没有根据首字母来直接定位分区表的子表，居然枚举了所有分区表才查到记录。

下次有时间，在这个表里添加一个首字母的字段，然后再按这个字段来分区，估计需要这样才能触发PgSQL枚举分区表的性能优化。