mysql 按时间分库分表_sharding-jdbc 按时间分库分表

一、 分库分表规则

会计系统按会计时间分库分表(一月一张表，一年一个库)：

以center_entry_flow表为例：

arch_center_acct(database)

center_entry_flow 20170431之前

center_acct(database now)

center_entry_flow 20170501到20170631分录流水

center_entry_flow_201707 20170701到20170731分录流水

center_entry_flow_201708 20170801到20170831分录流水

...

center_acct_2018(database)

center_entry_flow_201801 20180101到20180131分录流水

center_entry_flow_201802 20180201到20180231分录流水

...

二、改造点：

1、分布式键生成方式

1.1、sharding-id:(类twitter的Snowflake)

其二进制表示形式包含四部分，从高位到低位分表为：1bit符号位(为0)，41bit时间位，10bit工作进程位，12bit序列位。

eg:

78427487717031936

2305843009213694082

缺点：要设置工作进程位(10bit)

该标志在Java进程内是唯一的，如果是分布式应用部署应保证每个进程的工作进程Id是不同的。该值默认为0，目前可以通过三种方式设置。

调用静态方法CommonSelfIdGenerator.setWorkerId(“xxxx”)设置。

设置Java的系统变量，也就是再启动命令行中设置-Dsjdbc.self.id.generator.worker.id=xxx设置。

设置系统环境变量，通过SJDBC_SELF_ID_GENERATOR_WORKER_ID=xxx设置。

变通方式：workid通过hostname和IP生成

1.2、自定义表

表结构如下，实现方式：

最大ID存在数据库中，每次增加表中maxId值后获取id；出于效率原因，可以减少数据库访问次数，每次获取100个。

CREATE TABLE id_generator_table (

id int(11) DEFAULT NULL COMMENT '主键',

table_name varchar(255) DEFAULT NULL COMMENT '表名',

max_id int(11) DEFAULT NULL COMMENT '最大id'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3、其它

Ticket Server(数据库生成方式)

这个是Flickr在遇到生成全局ID问题时采用的办法。利用了数据库中auto_increment的特性和MySQL特有的REPLACE INFO命令，专门一个数据库实例用来产生ID。

2、 修改不支持sql

参考sharding-jdbc官方文档，待整理

3、 幂待校验

月底24点切换表时，通过redis setIfNull 校验幂等

4、 sharding jdbc bug

4.1 多表查询时，统计数据条数时错误

类方法路径：

com.dangdang.ddframe.rdb.sharding.merger.ResultSetFactory#getResultSet

case SINGLE: return buildSingle(shardingResultSets);

改为

case SINGLE: return buildMultiple(shardingResultSets, mergeContext);

参考：

相关jar包

com.dangdang

sharding-jdbc-core

1.4.1

com.dangdang

sharding-jdbc-config-spring

1.4.1

分库分表后要进行SQL改写

改定分为两部分，一部分是将分表的逻辑表名称替换为真实表名称。另一部分是根据SQL解析结果替换一些在分片环境中不正确的功能。

avg计算

在分片的环境中，以avg1 +avg2+avg3/3计算平均值并不正确，需要改写为(sum1+sum2+sum3)/(count1+count2+ count3)。这就需要将包含avg的SQL改写为sum和count，然后再结果归并时重新计算平均值。

分页

1.假设每10条数据为一页，取第2页数据。在分片环境下获取limit 10, 10，归并之后再根据排序条件取出前10条数据是不正确的结果。正确的做法是将分条件改写为limit 0, 20，取出所有前2页数据，再结合排序条件算出正确的数据。可以看到越是靠后的Limit分页效率就会越低，也越浪费内存。

2.有很多方法可避免使用limit进行分页，比如构建记录行记录数和行偏移量的二级索引，或使用上次分页数据结尾ID作为下次查询条件的分页方式。