PostgreSQL分表

发表于 分类于

PostgreSQL分表

测试表

创建表以存储全部数据

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS a_user_all (
	"id" bigserial NOT NULL,
	"account" VARCHAR ( 255 ),
	"name" VARCHAR ( 255 ),
	"sex" NUMERIC,
	"age" NUMERIC,
	"birthday" date,	
	"generation_time" TIMESTAMP ( 6 ) 
);

随机字符串生成function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Create or replace function random_string(length integer)
returns text as
$body$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$body$
language plpgsql;

插入数据脚本

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO
    a_user_all
    (account, name, sex, age, birthday, generation_time) 
SELECT
	random_string ( 10 ) AS account,
	random_string ( 10 ) AS NAME,
	CEIL ( random( ) * 2 ) AS sex,
	FLOOR ( random( ) * ( 45-10 ) + 10 ) AS age,
	CURRENT_DATE AS birthday,
	now( ) AS generation_time 
FROM
	generate_series ( 1, 100, 1 );

创建主表

TIMESTAMP(6)：小数点右边最多存储6位数字（PostgreSQL 12.6）

使用序列主键(推荐)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE SEQUENCE a_user_seq MINVALUE 1 NO MAXVALUE START WITH 1 INCREMENT BY 1 CACHE 1;
CREATE TABLE IF NOT EXISTS a_user (
	"id" int4 NOT NULL DEFAULT nextval( 'a_user_seq' :: regclass ),
	"account" VARCHAR ( 255 ) NOT NULL,
	"name" VARCHAR ( 255 ),
	"sex" integer,
	"age" NUMERIC,
	"birthday" date,
	"generation_time" TIMESTAMP ( 6 ),
	constraint pk_a_user_id primary key (id), -- 主键约束，可指定键名
	constraint uk_a_user_account unique(account) -- 唯一约束，可指定健名
);
CREATE UNIQUE INDEX idx_uk_a_user_account ON a_user (account); -- 唯一索引
CREATE INDEX idx_a_user_account_name ON a_user (account, name);


insert into a_user (id, account, name, sex, age, birthday, generation_time) 
values (nextval( 'a_user_seq' ), random_string(10), random_string(10), CEIL ( random( ) * 2 ), FLOOR ( random( ) * ( 45-10 ) + 10 ), current_date, now());

ID自动增长

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE IF NOT EXISTS a_user (
	"id" bigserial NOT NULL PRIMARY KEY, -- 主键约束，主键自增
	"account" VARCHAR ( 255 ) NOT NULL UNIQUE, -- 唯一约束
	"name" VARCHAR ( 255 ),
	"sex" integer,
	"age" NUMERIC,
	"birthday" date,
	"generation_time" TIMESTAMP ( 6 )
);
CREATE UNIQUE INDEX idx_uk_a_user_account ON a_user (account);
CREATE INDEX idx_a_user_account_name ON a_user (account, name);


insert into a_user (account, name, sex, age, birthday, generation_time) 
values (random_string(10), random_string(10), CEIL ( random( ) * 2 ), FLOOR ( random( ) * ( 45-10 ) + 10 ), current_date, now());

删除

1
2
3
4
DROP INDEX idx_uk_a_user_account;
DROP INDEX idx_a_user_account_name;
DROP TABLE a_user;
DROP SEQUENCE a_user_seq;

唯一性约束和唯一索引

不同数据库的实现不同，Oracle靠唯一索引实现唯一性，但是PostgreSQL两个都可以实现唯一性（似乎并无区别）。

约束和索引

前者用来检查数据的正确性，后者用来实现数据查询的优化，目的不同。

唯一性约束和唯一索引

在Oracle中，创建一个Constraint，同时也会创建一个该约束对应的唯一索引。创建唯一索引只会创建一个唯一索引，不会创建Constraint。也就是说 唯一性约束是通过创建唯一索引来实现的（Oracle）。

建表时加唯一性约束

1
2
3
4
5
CREATE TABLE IF NOT EXISTS a_user (
	"id" bigserial PRIMARY KEY NOT NULL, -- 主键自增
	"account" VARCHAR ( 255 ) NOT NULL UNIQUE, -- 唯一约束
	constraint idx_uk_a_user_account unique(account) -- 唯一约束
);

给已建好的表加唯一性约束

1
2
ALTER TABLE a_user add unique(account);
ALTER TABLE a_user add constraint uk_a_user_account unique(account); -- 指定健名

区别

删除唯一性约束时可以只删除约束而不删除对应的索引，所以对应的列还是必须唯一；而删除了唯一索引的话就可以插入不唯一的值。（Oracle）

索引使用 create/drop index 创建和删除，而约束使用 alter table tableName add constraint 建立，使用 drop constraint 删除。

创建分区表

手动创建分区表(不推荐)

继承主表和添加限制

分区表继承主表，并且正常情况下都不要为这些分区表添加任何新的列。

重命名主表不影响继承关系 

1
2
CREATE TABLE a_user_1() inherits (a_user);
CREATE TABLE a_user_2() inherits (a_user);

分区表限制，决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠。

1
2
ALTER TABLE a_user_1 ADD CONSTRAINT a_user_1_sex_check_key CHECK (sex = 1);
ALTER TABLE a_user_2 ADD CONSTRAINT a_user_2_sex_check_key CHECK (sex = 2);

建表和限制一起

1
2
create table a_user_1 ( CHECK (sex = 1) ) inherits (a_user);
create table a_user_2 ( CHECK (sex = 2) ) inherits (a_user);

创建索引和约束

分区表上创建索引，该索引并不是严格必须创建的，但在大部分场景下，它都非常有用。

分表字段也应加索引，虽然这会命中所有记录 

1
2
3
4
5
6
7
8
9
10
11
CREATE UNIQUE INDEX idx_uk_a_user_1_account ON a_user_1 (account);
CREATE INDEX idx_a_user_1_account_name ON a_user_1 (account, name);
CREATE INDEX idx_a_user_1_sex ON a_user_1 (sex, account, name);
ALTER TABLE a_user_1 add constraint uk_a_user_1_account unique(account);
ALTER TABLE a_user_1 add constraint pk_a_user_1_id primary key (id);

CREATE UNIQUE INDEX idx_uk_a_user_2_account ON a_user_2 (account);
CREATE INDEX idx_a_user_2_account_name ON a_user_2 (account, name);
CREATE INDEX idx_a_user_2_sex ON a_user_2 (sex, account, name);
ALTER TABLE a_user_2 add constraint uk_a_user_2_account unique(account);
ALTER TABLE a_user_2 add constraint pk_a_user_2_id primary key (id);

继承关系修改

1
2
3
4
5
alter table a_user_1 no inherit a_user; -- 删除继承
alter table a_user_2 no inherit a_user; -- 删除继承

alter table a_user_1 inherit a_user; -- 添加继承
alter table a_user_2 inherit a_user; -- 添加继承

重定向插入数据(使用rule)

trigger和rule不同的是，触发器可以实现异常捕捉等实现更丰富的功能。

rule 不像 function，trigger一样独立存在的，而是依附于表上，当你把表删了，相应的rule就一起被删了。

创建规则

使用rule分流

1
2
3
4
5
CREATE RULE a_user_rule_1 AS
ON INSERT TO a_user
  WHERE sex = 1
DO INSTEAD -- 改成also，会插入主表和子表，待测试
  INSERT INTO a_user_1 VALUES (NEW.*);

插入数据

1
insert into a_user select * from a_user_all;

查询

1
2
3
SELECT * FROM a_user;
SELECT * FROM a_user_1;
SELECT * FROM only a_user;

删除继承关系

删除继承关系后，将不在能查到子表的数据。

若在 A 表上创建rule插入到 B 表，那么 B 不应再继承 A 表，否则会出现循环依赖，无法删除表的情况，当然这不是必须。

缺陷

1
2
insert into a_user (id, account, name, sex, birthday, generation_time) 
values (1000, 'zhangsan', '张三', 9, current_date, now());

执行上述sql发现，不符合规则的数据会插入主表中。实际应用中，主表不需要存储数据，而是希望将那些不满足规则的数据

1、直接丢弃

2、放到一张other表

3、创建一个新规则和新表以接收对应规则的数据

重定向插入数据(触发器)

定义一个trigger，把对主表的数据插入操作重定向到对应的分区表。

创建触发器

1
2
3
4
5
CREATE TRIGGER trig_insert_a_user
BEFORE INSERT
ON a_user
FOR EACH ROW
EXECUTE PROCEDURE func_insert_a_user();

创建分区函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION func_insert_a_user()
returns trigger as
$body$
BEGIN
  IF NEW.sex = 1
    THEN
      INSERT INTO a_user_1 VALUES (NEW.*);
  ELSIF NEW.sex = 2
    THEN
      INSERT INTO a_user_2 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

插入数据

1
insert into a_user select * from a_user_all;

查询

1
2
3
SELECT * FROM a_user;
SELECT * FROM a_user_1;
SELECT * FROM only a_user;

缺陷

1
2
insert into a_user (id, account, name, sex, birthday, generation_time) 
values (1000, 'zhangsan', '张三', 9, current_date, now());

执行上述sql，此时不满足条件的数据丢弃，也可以使用other表接收这些数据。

需求：所有数据都应按照规律存到对应的子表（分区表）中，若不存在这样的表要实现自动新建。

重定向插入数据(触发器)-终极

创建触发器

1
2
3
4
5
CREATE TRIGGER trig_insert_a_user
BEFORE INSERT
ON a_user
FOR EACH ROW
EXECUTE PROCEDURE func_insert_a_user();

创建分区函数

自动创建分区表并插入数据： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE OR REPLACE FUNCTION func_insert_a_user()
returns trigger as
$body$
DECLARE
        sex_colume integer;
        insert_statement TEXT;
BEGIN
  select NEW.sex INTO sex_colume;
  insert_statement := 'INSERT INTO a_user_' || sex_colume || ' VALUES ($1.*)';
  execute insert_statement USING NEW;
  return null;
  EXCEPTION
  WHEN UNDEFINED_TABLE
    THEN
      -- 创建分表并加限制
      execute 'CREATE TABLE IF NOT EXISTS a_user_' || sex_colume || ' ( CHECK (sex = ' || sex_colume || ') ) inherits (a_user)';
      raise notice 'Create a new table a_user_%', sex_colume;
      -- 建索引
      execute 'CREATE UNIQUE INDEX idx_uk_a_user_' || sex_colume || '_account ON a_user_' || sex_colume || '(account)';
      execute 'CREATE INDEX idx_a_user_' || sex_colume || '_account_name ON a_user_' || sex_colume || '(account, name)';
      -- 分表字段也应加索引，虽然这会命中所有记录
      execute 'CREATE INDEX idx_a_user_' || sex_colume || '_sex ON a_user_' || sex_colume || '(sex, account, name)';
      -- 加约束
      execute 'ALTER TABLE a_user_' || sex_colume || ' add constraint pk_a_user_' || sex_colume || '_id primary key (id)';
      execute 'ALTER TABLE a_user_' || sex_colume || ' add constraint uk_a_user_' || sex_colume || '_account unique(account)';
      -- 建立分表后再次插入
      execute insert_statement USING NEW;
      return null;
END;
$body$
LANGUAGE plpgsql;

插入数据

1
2
3
4
insert into a_user select * from a_user_all;

insert into a_user (id, account, name, sex, birthday, generation_time) 
values (1000, 'zhangsan', '张三', 9, current_date, now());

查询

1
2
3
SELECT * FROM a_user;
SELECT * FROM a_user_1;
SELECT * FROM only a_user;

查询发现，不存在的表会自动新建，自此优化完成。

删除触发器

1
2
3
4
SELECT * FROM pg_trigger; -- 列出触发器

drop trigger trig_insert_a_user on a_user;
drop function func_insert_a_user;

执行计划分析

1
2
3
4
5
EXPLAIN ( ANALYZE ON, buffers ON, costs ON, timing ON )
SELECT * FROM a_user_all T;

EXPLAIN ( ANALYZE ON, buffers ON, costs ON, timing ON )
SELECT * FROM a_user T;

分表后的增删改查测试

新增数据

1
2
insert into a_user (id, account, name, sex, birthday, generation_time) 
values (1000, 'zhangsan', '张三', 1, current_date, now());

查询数据

如果想要查询父表的数据，忽略子表，可以使用 ONLY 关键字

1
2
3
SELECT * FROM a_user T where T.account = 'zhangsan';
SELECT * FROM a_user_1 T where T.account = 'zhangsan';
SELECT * FROM only a_user T where T.account = 'zhangsan';

这里需要理解一个概念：给子表插入数据的时候，并不是同时把数据中的共享字段插入到父表中，只是简单通过继承关系使子表的数据在父表可见。如果你指定了 ONLY 关键字，则查询不在从子表中获取数据。

另外需要注意的是，由于继承表的本质原因，一些约束条件可能会被打破；例如，声明一个唯一字段，可能在查询结果中2条相同的值。因此，在使用的继承的过程中，你要特别注意约束；因为他们在各自的表中没有违反约束条件；因此，如果你在查询父表的时候没有制定 ONLY 字段，那它可能会返回你非预期的结果。

同理，update、delete同样可以使用 ONLY 关键字，示例：

1
2
update only a_user set age = 1000 where account = 'zhangsan';
delete from only a_user where account = 'zhangsan'

更新数据

1
update a_user set age = 1000 where account = 'zhangsan';

下面语句执行失败，违反检查约束。因其原本就是根据sex分表，所以无法直接修改sex字段。只能删除后重新插入。

1
2
3
4
update a_user set sex = 2 where account = 'zhangsan';

> ERROR:  new row for relation "a_user_1" violates check constraint "a_user_1_sex_check"
DETAIL:  Failing row contains (1000, zhangsan, 张三, 2, 1000, 2021-11-27, 2021-11-27 00:52:17.048075).

删除数据

1
2
3
4
5
delete from a_user where account = 'zhangsan'

SELECT * FROM a_user T where T.account = 'zhangsan';
SELECT * FROM a_user_1 T where T.account = 'zhangsan';
SELECT * FROM ONLY a_user T where T.account = 'zhangsan';

其他

1
2
3
4
5
select version();

select generate_series(1,30,1);

select generate_series ( '2015-12-01' :: DATE, '2015-12-30' :: DATE, '1 day' :: INTERVAL );

注意事项

VACUUM 或 ANALYZE a_user 只会对主表起作用，要想分析表，需要分别分析每个分区表。

参考

PostgreSQL分区表（Table Partitioning）应用