mysql分库分表中间件6_postgresql数据库中间件pgoneproxy支持二级分库分表

最新推荐文章于 2023-08-01 22:06:15 发布

朱炫

最新推荐文章于 2023-08-01 22:06:15 发布

阅读量245

点赞数

文章标签： mysql分库分表中间件6

本文链接：https://blog.csdn.net/weixin_28825959/article/details/113946529

版权

在很多场景中需要按照多个字段来进行分库分表，比如在电信行业中，一般会根据用户号码以及时间(比如月份)来存储用户通信产生的数据。今天就给大家讲讲，怎么使用pgoneproxy来实现这个功能。现在假设用户号码就是int 型的id号，下面的配置中先根据id进行分表，在根据时间tt进行分表。

[

{

"table" : "bigtest",

"pkey" : "id",

"type" : "int",

"method" : "crc32",

"partitions":

[

{"suffix":"_0", "group":"data1"},

{"suffix":"_1", "group":"data1"}

"subpkey":"tt",

"subtype":"timestamp",

"submethod":"buffer",

"subpartitions":

[

{"suffix":"_2015", "group":"data1", "minval":"1997-01-01 00:00:00", "maxval":"2015-01-01 00:00:00"},

{"suffix":"_2037", "group":"data1", "minval":"2015-01-01 00:00:01", "maxval":"2037-01-01 00:00:00"}

]

}

]

上面通过crc32来针对ID进行分表，存储到后缀为_0,_1的表中。在通过buffer的方式来针对时间字段tt来把数据存储到对应后缀的表中。通过上面的配置，在数据库表中会产生如下的四张表：

pgbench=> \dt;

List of relations

Schema | Name | Type | Owner

--------+------------------+-------+----------

public | bigtest_0_2015 | table | db_user

public | bigtest_0_2037 | table | db_user

public | bigtest_1_2015 | table | db_user

public | bigtest_1_2037 | table | db_user

即小于2015-01-01 00:00:00的数据存放到后缀为2015的表中，其他的数据存放到2037的表中。如果想根据更加具体的时间来分表，则可以通过修改subpartitiions来做到。

下面通过具体的操作来看看实现情况：

pgbench=> \dt;

List of relations

Schema | Name | Type | Owner

--------+------------------+-------+----------

public | pgbench_accounts | table | postgres

public | pgbench_branches | table | postgres

public | pgbench_history | table | postgres

public | pgbench_tellers | table | postgres

(4 rows)

pgbench=> create table bigtest(id int, name varchar(1024), age int, tt timestamp);

CREATE 0

pgbench=> \dt;

List of relations

Schema | Name | Type | Owner

--------+------------------+-------+----------

public | bigtest_0_2015 | table | db_user

public | bigtest_0_2037 | table | db_user

public | bigtest_1_2015 | table | db_user

public | bigtest_1_2037 | table | db_user

public | pgbench_accounts | table | postgres

public | pgbench_branches | table | postgres

public | pgbench_history | table | postgres

public | pgbench_tellers | table | postgres

(8 rows)

pgbench=> insert into bigtest(id, name, age, tt) values(0, 'name0', 30, '2001-01-01 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(1, 'name1', 31, '2002-01-01 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(2, 'name2', 32, '2012-11-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(3, 'name3', 33, '2012-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(4, 'name4', 34, '2013-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(5, 'name5', 35, '2014-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(6, 'name6', 36, '2016-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(7, 'name7', 37, '2017-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(8, 'name8', 38, '2018-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(9, 'name9', 39, '2012-12-11 00:00:00');

INSERT 0 1

pgbench=> insert into bigtest(id, name, age, tt) values(10, 'name10', 40, '2022-12-11 00:00:00');

INSERT 0 1

pgbench=> \dt;

List of relations

Schema | Name | Type | Owner

--------+------------------+-------+----------

public | bigtest_0_2015 | table | db_user

public | bigtest_0_2037 | table | db_user

public | bigtest_1_2015 | table | db_user

public | bigtest_1_2037 | table | db_user

public | pgbench_accounts | table | postgres

public | pgbench_branches | table | postgres

public | pgbench_history | table | postgres

public | pgbench_tellers | table | postgres

(8 rows)

pgbench=> select * from bigtest_0_2015;

id | name | age | tt

----+-------+-----+---------------------

0 | name0 | 30 | 2001-01-01 00:00:00

2 | name2 | 32 | 2012-11-11 00:00:00

4 | name4 | 34 | 2013-12-11 00:00:00

(3 rows)

pgbench=> select * from bigtest_0_2037;

id | name | age | tt

----+--------+-----+---------------------

6 | name6 | 36 | 2016-12-11 00:00:00

8 | name8 | 38 | 2018-12-11 00:00:00

10 | name10 | 40 | 2022-12-11 00:00:00

(3 rows)

pgbench=> select * from bigtest_1_2015;

id | name | age | tt

----+-------+-----+---------------------

1 | name1 | 31 | 2002-01-01 00:00:00

3 | name3 | 33 | 2012-12-11 00:00:00

5 | name5 | 35 | 2014-12-11 00:00:00

9 | name9 | 39 | 2012-12-11 00:00:00

(4 rows)

pgbench=> select * from bigtest_1_2037;

id | name | age | tt

----+-------+-----+---------------------

7 | name7 | 37 | 2017-12-11 00:00:00

(1 row)

pgbench=> select * from bigtest;

id | name | age | tt

----+--------+-----+---------------------

0 | name0 | 30 | 2001-01-01 00:00:00

2 | name2 | 32 | 2012-11-11 00:00:00

4 | name4 | 34 | 2013-12-11 00:00:00

6 | name6 | 36 | 2016-12-11 00:00:00

8 | name8 | 38 | 2018-12-11 00:00:00

10 | name10 | 40 | 2022-12-11 00:00:00

1 | name1 | 31 | 2002-01-01 00:00:00

3 | name3 | 33 | 2012-12-11 00:00:00

5 | name5 | 35 | 2014-12-11 00:00:00

9 | name9 | 39 | 2012-12-11 00:00:00

7 | name7 | 37 | 2017-12-11 00:00:00

(11 rows)

pgbench=> select * from bigtest where id = 0;

id | name | age | tt

----+-------+-----+---------------------

0 | name0 | 30 | 2001-01-01 00:00:00

(1 row)

pgbench=> select * from bigtest where tt < '2015-01-01 00:00:00';

id | name | age | tt

----+-------+-----+---------------------

0 | name0 | 30 | 2001-01-01 00:00:00

2 | name2 | 32 | 2012-11-11 00:00:00

4 | name4 | 34 | 2013-12-11 00:00:00

1 | name1 | 31 | 2002-01-01 00:00:00

3 | name3 | 33 | 2012-12-11 00:00:00

5 | name5 | 35 | 2014-12-11 00:00:00

9 | name9 | 39 | 2012-12-11 00:00:00

(7 rows)

pgbench=> select * from bigtest where tt > '2015-01-01 00:00:00';

id | name | age | tt

----+--------+-----+---------------------

6 | name6 | 36 | 2016-12-11 00:00:00

8 | name8 | 38 | 2018-12-11 00:00:00

10 | name10 | 40 | 2022-12-11 00:00:00

7 | name7 | 37 | 2017-12-11 00:00:00

(4 rows)

pgbench=> select * from bigtest where tt > '2015-01-01 00:00:00' and tt < '2018-01-01 00:00:00';

id | name | age | tt

----+-------+-----+---------------------

6 | name6 | 36 | 2016-12-11 00:00:00

7 | name7 | 37 | 2017-12-11 00:00:00

(2 rows)

pgbench=> select * from bigtest where tt > '2014-01-01 00:00:00' and tt < '2015-01-01 00:00:00';

id | name | age | tt

----+-------+-----+---------------------

5 | name5 | 35 | 2014-12-11 00:00:00

(1 row)

pgbench=>

上面展示了从新建表，插入数据，查询数据的整个过程。从实验结果看能够很好的支持上面讲述的场景。

朱炫

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql分库分表中间件6_postgresql数据库中间件pgoneproxy支持二级分库分表

在很多场景中需要按照多个字段来进行分库分表，比如在电信行业中，一般会根据用户号码以及时间(比如月份)来存储用户通信产生的数据。今天就给大家讲讲，怎么使用pgoneproxy来实现这个功能。现在假设用户号码就是int 型的id号，下面的配置中先根据id进行分表，在根据时间tt进行分表。[{"table" : "bigtest","pkey" : "id","type" : "int",...
复制链接

扫一扫