postgresql数据库中间件pgoneproxy支持二级分库分表 http://my.oschina.net/u/918218/blog/715012
在很多场景中需要按照多个字段来进行分库分表，比如在电信行业中，一般会根据用户号码以及时间（比如月份）来存储用户通信产生的数据。今天就给大家讲讲，怎么使用pgoneproxy来实现这个功能。现在假设用户号码就是int 型的id号，下面的配置中先根据id进行分表，在根据时间tt进行分表。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[ { "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来把数据存储到对应后缀的表中。通过上面的配置，在数据库表中会产生如下的四张表：
|
1 2 3 4 5 6 7 8 |
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来做到。
下面通过具体的操作来看看实现情况：
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
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=> |
上面展示了从新建表，插入数据，查询数据的整个过程。从实验结果看能够很好的支持上面讲述的场景。
