业务背景
有一个学习记录表数据达到亿级，后台有众多复杂的统计查询，随着数据量增大，查询性能逐渐下降，且每周有仍400万多的数据记录，因此考虑分表将大表拆分，优化查询速度。
表分区的概念
将一个大表分成物理上小片的表，但在逻辑上一个完整的表。
表分区的好处
1.在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时
2.当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能
3.批量装载和删除可以通过增加或者去除分区来完成。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE删除一个分区远快于批量操作
备注
以下的表数据已做处理
步骤1
处理学习记录表中的非法数据，做好备份
步骤2
创建表分区的主表 不要索引和约束
CREATE TABLE "table"."log_main" (
"logid" varchar(30) NOT NULL,
"version" varchar(10) ,
... ,
...
);
步骤3
我下面是按照学习记录的期次version进行表分区的划分，也可以按照学习时间的进行分区划分，取决于你的业务查询SQL经常落到哪些范围内，合适的分区能减少查询分区的范围
不同的分区的范围之间没有重叠
`
CREATE TABLE table.log_2_season(
CHECK (version like '2-%' )
)inherits (table.log_main);
CREATE TABLE table.log_3_season(
CHECK (version like '3-%' )
)inherits (table.log_main);
CREATE TABLE table.log_4_season(
CHECK (version like '4-%' )
)inherits (table.log_main);
CREATE TABLE table.log_5_season(
CHECK (version like '5-%' )
)inherits (table.log_main);
CREATE TABLE table.log_6_season(
CHECK (version like '6-%' )
)inherits (table.log_main);
CREATE TABLE table.log_7_season(
CHECK (version like '7-%' )
)inherits (table.log_main);
CREATE TABLE table.log_8_season(
CHECK (version like '8-%' )
)inherits (table.log_main);
CREATE TABLE table.log_9_season(
CHECK (version like '9-%' )
)inherits (table.log_main);
CREATE TABLE table.log_10_season(
CHECK (version like '10-%' )
)inherits (table.log_main);
CREATE TABLE table.log_11_season(
CHECK (version like '11-%' )
)inherits (table.log_main);
`
步骤4
创建触发器函数
注意最后一条ELSE用来处理不在分区范围内的数据,只要在维护了新的分区只需要维护新的触发器函数即可
CREATE OR REPLACE FUNCTION table_log_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.version like '2-%' ) THEN
INSERT INTO table.log_2_season VALUES (NEW.);
ELSIF ( NEW.version like '3-%') THEN
INSERT INTO table.log_3_season VALUES (NEW.);
ELSIF ( NEW.version like '4-%') THEN
INSERT INTO table.log_4_season VALUES (NEW.);
ELSIF ( NEW.version like '5-%') THEN
INSERT INTO table.log_5_season VALUES (NEW.);
ELSIF ( NEW.version like '6-%') THEN
INSERT INTO table.log_6_season VALUES (NEW.);
ELSIF ( NEW.version like '7-%') THEN
INSERT INTO table.log_7_season VALUES (NEW.);
ELSIF ( NEW.version like '8-%') THEN
INSERT INTO table.log_8_season VALUES (NEW.);
ELSIF ( NEW.version like '9-%') THEN
INSERT INTO table.log_9_season VALUES (NEW.);
ELSIF ( NEW.version like '10-%') THEN
INSERT INTO table.log_10_season VALUES (NEW.);
ELSIF ( NEW.version like '11-%') THEN
INSERT INTO table.log_11_season VALUES (NEW.);
ELSE
INSERT INTO table.log_garbage_season VALUES (NEW.*);
END IF;
RETURN NULL;
END;
此处是两个$$符号，编辑器显示不了
LANGUAGE plpgsql;
步骤5
在主表上创建触发器
CREATE TRIGGER insert_table_log_main BEFORE INSERT ON table.log_main FOR EACH ROW
EXECUTE FUNCTION table_log_insert_trigger ( );
步骤6
插入几条测试数据做测试,看看是否能进入对应的分区
INSERT INTO table.log_main("logid", "version", ....) VALUES ();
删除测试数据
delete from table.log_main;
步骤7
数据迁移，不建议使用Navicat或者pgadmin操作数据库，例如Navicat存在客户端SQL超时的问题，可能是7200秒左右
此处建议使用数据库查询工具 psql
直接在服务器端操作SQL，能够减少通信时间，且没有超时的问题
1.切换数据库用户
su - postgres(数据库角色)
2.连接到数据库 12345端口
psql -p 15432
3.切换到postgres1数据库
\c postgres
4.显示字符集 utf-8
\encoding UTF8
5.编写SQL语句 ;结尾 回车执行
insert into table.log_main as select * from 业务表;
步骤8
给各个分区表创建索引
CREATE INDEX "log_2_season$version" ON "table"."log_2_season"(version);
CREATE INDEX "log_3_season$version" ON "table"."log_3_season"(version);
...
步骤9
给各个分区表增加唯一索引（没有的话就不用加）
CREATE UNIQUE INDEX "log_2_season$Unique_logid" ON "table"."log_2_season" (logid);
CREATE UNIQUE INDEX "log_3_season$Unique_logid" ON "table"."log_3_season" (logid);
以下是个人测试过后的补充说明
在主表上创建规则
CREATE RULE "insert_log_2_season" AS
ON INSERT TO "table"."log_main" WHERE
(version LIKE '2-%')
DO INSTEAD
INSERT INTO "table"."log_2_season" VALUES(NEW.*);
CREATE RULE "insert_log_3_season" AS
ON INSERT TO "table"."log_main" WHERE
(version LIKE '3-%')
DO INSTEAD
INSERT INTO "table"."log_3_season" VALUES(NEW.*);
该步骤可以替换上述的步骤4和5
测试库： log表 4600万数据
使用触发器 耗时约20分钟，使用规则约耗时4分钟
规则的开销比触发器大很多，但是这种开销是每个查询只有一次，而不是每行一次，因此
这种方法可能对批量插入的情况有优势。不过，在大部分情况下，触发器方法将提供更好
的性能。
如果从一开始规划的创建表的时候，并且预见会在未来有非常多的数据，个人建议使用触发器实现子表数据的分发。
如果是已有的表分表个人使用规则速度处理更快些。
分表后测试对比
同一个SQL查询查询时间从5秒变为了2秒，效果明显。
查询条件需要落到对应的子表里才行。
数据维护
触发器:增加新的子表需要维护触发器函数
规则：增加新的子表需要新增一条规则