SpringBoot中使用ShardingJdbc切分数据库表

作者简介：胡宗棠，中移（苏州）软件技术有限公司，云计算软件高级研发工程师，从事公有云产品平台研发、架构设计；目前专注于大型分布式系统的高并发、高可用设计。曾就职于蚂蚁金服支付宝，甲骨文中国研发中心。

本文主要将业界知名的开源分库分表中间件—ShardingJdbc集成至SpringBoot工程中，利用ShardingJdbc的数据库切分能力来实现库表水平切分和扩展的目标，提高分布式系统整体的并发量，解决数据库中的单表因数据量过大而带来得各种瓶颈和影响（本文所述的ShardingJdbc中间件以其1.X版本为参考，2.X版本和1.X版本有较大区别，在后面的文章中会有介绍）。

分库分表中间件ShardingJdbc介绍

ShardingJdbc是当当开源的数据库水平切分的中间件，其代表了客户端类的分库分表技术框架（这一点与MyCat不同，MyCat本质上是一种数据库代理）。ShardingJdbc定位为轻量级数据库驱动，由客户端直连数据库，以jar包形式提供服务，未使用中间层，无需额外部署，无其他依赖，业务系统开发人员与数据库运维人员无需改变原有的开发与运维方式。因此ShardingJdbc即为增强版的JDBC驱动，可以实现旧代码迁移零成本的目标。

下面的是ShardingJdbc的架构图：

从上面的架构图中，可以看出ShardingJdbc与业务工程集成起来十分方便与快捷，同时其提供的分片规则配置、SQL解析、SQL改写、SQL路由、SQL执行以及结果归并等强大的功能，使得业务开发人员无需在这些方面花费较大多的精力，而可以更加专注于业务流程的开发。其主要的特点如下：

（1）分片规则配置

ShardingJdbc的分片逻辑非常灵活，支持分片策略自定义、复数分片键、多运算符分片等功能。

（2）JDBC规范重写

ShardingJdbc中间件对标准JDBC规范的重写思路是针对DataSource、Connection、Statement、PreparedStatement和ResultSet五个核心接口封装，将多个JDBC实现类集合（如：MySQL JDBC实现/DBCP JDBC实现等）纳入ShardingJdbc实现类管理。

（3）SQL解析

SQL解析作为分库分表类中间件框架的核心之一，其性能和兼容性是最重要的衡量指标。目前常见的SQL解析器主要有fdb/jsqlparser和Druid。在ShardingJdbc1.5.0之前的版本是采用解析速度相对最快的Druid作为SQL解析器。ShardingJdbc在1.5.0.M1 正式发布时，将SQL解析引擎从Druid替换成了自研的。新引擎仅解析分片上下文，对于 SQL 采用“半理解”理念，进一步提升性能和兼容性，同时降低了代码复杂度。

（4）SQL改写

这里一部分是将分表的逻辑表名称替换为实际真实的分表名称。另一部分是根据SQL解析结果替换一些在分片环境中不正确的功能。其中，1.5.0之前的版本，SQL改写是在SQL路由之前完成的，而在1.5.x中调整为SQL路由之后，因为SQL改写可以根据路由至单库表还是多库表而进行进一步优化。

（5）SQL路由

SQL路由是指根据分片规则配置，将待执行的SQL定位至真正的DB数据源。

（6）SQL执行

这里指的是路由至真实的DB数据源后，ShardingJdbc将采用多线程并发执行SQL，并完成对addBatch等批量方法的处理。

（7）结果归并

ShardingJdbc支持通遍历类、排序类、聚合类和分组类四种结果并归方式。普通遍历类最为简单，只需按顺序遍历ResultSet的集合即可。排序类结果则将结果先排序再输出，因为各分库的分片结果均按照各自条件完成排序，所以采用归并排序算法整合最终结果。聚合类分为3种类型，比较型、累加型和平均值型。分组类相对最为复杂，需要将所有的ResultSet结果放入内存，使用Map-Reduce算法分组，最后根据排序和聚合条件做相关处理。最为消耗内存，损失性能的地方就是这里了，可以考虑使用limit合理的限制分组数据大小。

在Spring Boot中实践ShardingJdbc

本节将主要详细介绍在SpringBoot工程中如何集成ShardingJdbc这款切分库表的中间件，并使用其完成对库表的切分/路由，以及在业务开发中的使用。

版本环境

• Spring Boot 1.4.1.RELEASE

• Druid 1.0.12

• JDK 1.8

添加ShardingJdbc的pom依赖

因为当当开源了ShardingJdbc的源码，我们可以通过maven仓库来获得jar包依赖。当访问http://mvnrepository.com/artifact/com.dangdang/sharding-jdbc-core（该链接为1.X版本的）选择自己项目需要的版本（在本次集成中选择的版本为1.4.2），点击进入后复制maven内容到pom.xml内即可。同时，这里需要注意的是在ShardingJdbc 1.5.0.M1正式发布以前，SQL解析引擎仍然是采用阿里的Druid数据源连接池，还需要在Pom中引入Druid的依赖（版本为1.0.12），此外需要保证ShardingJdbc与Druid版本相对应，否则在执行SQL解析时候会导致ShardingJdbc的运行时异常。Pom文件的引入如下图所示：

yaml文件配置

在MySql数据库中创建相应的分库和分表后，需要在SpringBoot工程中的yaml配置文件（或property配置文件）中完成对分库数据源的配置，具体如下：

1.    testShardingDB0:

2.      type: com.alibaba.druid.pool.DruidDataSource

3.      driverClassName: com.mysql.jdbc.Driver

4.      url: jdbc:mysql://{ip}:{port} /res_test_cloud_bill_sharding0?useUnicode=true&characterEncoding=utf-8

5.      username: xxxxxx

6.      password: xxxxxx

7.      max-idle: 500

8.      max-active: 100

9.      min-idle: 50

10.      initial-size: 50

11.      validation-query: SELECT 1

12.      max-wait: -1

13.      test-on-borrow: true

14.      test-on-return: true

15.      test-while-idle: true

16.      num-tests-per-eviction-run: 3

17.      time-between-eviction-runs-millis: 30000

18.      min-evictable-idle-time-millis: 18000000

19.      removeAbandoned: true

20.      removeAbandonedTimeout: 180

21.      # 打开PSCache,并且指定每个连接上PSCache的大小

22.      poolPreparedStatements: false

23.      maxPoolPreparedStatementPerConnectionSize: -1

24.      # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙

25.      filters: stat,wall,log4j

26.      # 通过connectProperties属性来打开mergeSql功能;慢SQL记录

27.      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

28.      # 合并多个DruidDataSource的监控数据

29.      useGlobalDataSourceStat: true

30. ###分库1配置,具体的ip/host:port和数据库用户名和密码根据实际填写

31.    testShardingDB1:

32.      type: com.alibaba.druid.pool.DruidDataSource

33.      driverClassName: com.mysql.jdbc.Driver

34.      url: jdbc:mysql:// {ip}:{port}/res_test_cloud_bill_sharding1?useUnicode=true&characterEncoding=utf-8

35.      username: xxxxxx

36.      password: xxxxxx

37.      max-idle: 500

38.      max-active: 100

39.      min-idle: 50

40.      initial-size: 50

41.      validation-query: SELECT 1

42.      max-wait: -1

43.      test-on-borrow: true

44.      test-on-return: true

45.      test-while-idle: true

46.      num-tests-per-eviction-run: 3

47.      time-between-eviction-runs-millis: 30000

48.      min-evictable-idle-time-millis: 18000000

49.      removeAbandoned: true

50.      removeAbandonedTimeout: 180

51.      # 打开PSCache,并且指定每个连接上PSCache的大小

52.      poolPreparedStatements: false

53.      maxPoolPreparedStatementPerConnectionSize: -1

54.      # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙

55.      filters: stat,wall,log4j

56.      # 通过connectProperties属性来打开mergeSql功能;慢SQL记录

57.      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

58.      # 合并多个DruidDataSource的监控数据

59.      useGlobalDataSourceStat: true

ShardingJdbc分库分表的Bean配置

引入了ShardingJdbc中间件后，在SpringBoot工程中可以通过配置Bean来初始化分库分表的数据源，其中需要设置逻辑表与实际分表的映射关系和分库分表的ID路由策略。

1. @Configuration

2. @MapperScan(basePackages   = "com.chinamobile.bcop.test.sharding.xxxxxxxx",   sqlSessionTemplateRef = "shardingSqlSessionTemplate")

3. public   class MybatisTestShardingConfig {

4.    @Bean(name = "testShardingDB0")

5.    @ConfigurationProperties(prefix = "spring.datasource.tesetShardingDB0")

6.    public DataSource testShardingDataSource0(){

7.        return new   DruidDataSource();

8.    }

9.    @Bean(name = "testShardingDB1")

10.    @ConfigurationProperties(prefix =   "spring.datasource.testShardingDB1")

11.    public DataSource testShardingDataSource1(){

12.        return new   DruidDataSource();

13.    }

14.    @Bean(name = "dataSourceRule")

15.    public DataSourceRule   dataSourceRule(@Qualifier("testShardingDB0") DataSource ds0, @Qualifier("testShardingDB1")   DataSource ds1){

16.        Map<String,   DataSource> dataSourceMap = new HashMap<>();

17.        dataSourceMap.put("shardingDataSource0",   ds0);

18.        dataSourceMap.put("shardingDataSource1",   ds1);

19.        return new   DataSourceRule(dataSourceMap, "shardingDataSource0");

20.    }

21.    @Bean(name =   "shardingRule")

22.    public ShardingRule   shardingRule(@Qualifier("dataSourceRule")DataSourceRule   dataSourceRule){

23.    //表策略

24.    //设置分表映射，将test_msg_queue_bill_record_0~test_msg_queue_bill_record_4

25.    // 几个实际的表映射到test_msg_queue_bill_record逻辑表

26.    //0~4几个表是真实的表，test_msg_queue_bill_record是个虚拟不存在的表,只是供使用

27.    TableRule orderTableRule = TableRule.builder("test_msg_queue_bill_record")

28.        .actualTables(Arrays.asList(

29.        "test_msg_queue_bill_record_0",

30.        "test_msg_queue_bill_record_1",

31.        "test_msg_queue_bill_record_2",

32.        "test_msg_queue_bill_record_3",

33.        "test_msg_queue_bill_record_4"))

34.        .dataSourceRule(dataSourceRule)

35.        .build();

36.    //绑定表策略，在查询时会使用主表策略计算路由的数据源，因此需要约定绑定表策略的表的规则需要一致，可以一定程度提高效率

37.    List<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>();

38.    bindingTableRules.add(new   BindingTableRule(Arrays.asList(orderTableRule)));

39.    return   ShardingRule.builder()

40.                .dataSourceRule(dataSourceRule)    

41.                .tableRules(Arrays.asList(orderTableRule))

42.                .bindingTableRules(bindingTableRules)

43.                .databaseShardingStrategy(new DatabaseShardingStrategy("CUSTOMER_ID", new   CustomizedDbShardingStrategy()))

44.                .tableShardingStrategy(new   TableShardingStrategy("USER_ID", new   CustomizedTableShardingStrategy()))

45.                .build();

46.    }

47.    @Bean(name = "testShardingDataSource")

48.    public DataSource   shardingDataSource(@Qualifier("shardingRule")ShardingRule   shardingRule){

49.        return   ShardingDataSourceFactory.createDataSource(shardingRule);

50.    }

51.    //需要手动声明配置事务

52.    @Bean(name = "testShardingTransactionManager")

53.    public DataSourceTransactionManager   transactitonManager(@Qualifier("testShardingDataSource") DataSource   dataSource){

54.        return new   DataSourceTransactionManager(dataSource);

55.    }

56. //MyBatis   SqlSessionFactory/SqlSessionTemplate/MybatisDAO的Bean初始化省略

57. //……..

在本文的示例中，按照业务要求先对原来的单库单表进行拆分，由原来的单库单表—testmsgqueuebillrecord，分成两个库的多表（每个库中均有testmsgqueuebillrecord0～testmsgqueuebillrecord4五个分表，这里只是示例，在真实的业务场景下需要根据业务数据的总体容量来设定分库分表的规模，究竟是分5个库每个库5表，还是分10个库每个库10表来满足业务要求）。由上面的分库分表配置Bean的代码可见，使用Druid连接池初始化两个分库的数据源后设置ShardingJdbc数据源的分片规则。其中，testmsgqueuebillrecord表为逻辑表，分别与testmsgqueuebillrecord0～testmsgqueuebillrecord4的实际分表名建立映射。如果执行的SQL为“select * from testmsgqueuebillrecord” 就能遍历查完hwmsgqueuebillrecord0~4五个分表。最后，在设置切分规则时候，分别以customerId和userId作为切分数据库和切分数据表的路由规则。根据上面的配置可以得到下面的分库分表逻辑视图：

具体的库表分片路由策略

ShardingJdbc对分片路由策略完全采用开放式的，将这一部分的控制权交给业务侧开发人员手中。业务开发人员可以根据具体业务的需求，设置灵活的分库分表路由策略。在上一节的示例代码中，可以看到在设置分库分表规则的时候有两个比较显著的类——CustomizedDbShardingStrategy和CustomizedTableShardingStrategy。正是这两个类完成了自定义的库表的自定义分片路由策略，CustomizedDbShardingStrategy的示例代码如下：

1. public  class CustomizedDbShardingStrategy implements   SingleKeyDatabaseShardingAlgorithm<String> {

2.    @Override

3.    public String   doEqualSharding(Collection<String> databaseNames,   ShardingValue<String> shardingValue) {

4.         //在这里设置具体的条件为“=”的分库策略

5.         //code here

6.        }

7.    @Override

8.    public Collection<String>   doInSharding(Collection<String> databaseNames,   ShardingValue<String> shardingValue) {

9.        //在这里设置具体SQL语句条件为“In”的分库策略

10.        //code here

11.    }

12.    @Override

13.    public Collection<String>   doBetweenSharding(Collection<String> databaseNames,   ShardingValue<String> shardingValue) {

14.        //在这里设置具体的SQL语句条件为“between”的分库策略

15.        //code here

16.    }

CustomizedTableShardingStrategy的示例代码如下：

1. public class CustomizedTableShardingStrategy implements SingleKeyTableShardingAlgorithm<String> {

2.    @Override

3.    public String doEqualSharding(Collection<String> tableNames, ShardingValue<String> shardingValue) {

4.        //在这里设置具体的条件为“=”的分表策略

5.        //code here

6.    }

7.    @Override

8.    public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<String> shardingValue) {

9.        //在这里设置具体SQL语句条件为“In”的分表策略

10.        //code here

11.    }

12.    @Override

13.    public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<String> shardingValue) {

14.        //在这里设置具体的SQL语句条件为“between”的分表策略

15.        //code here

16.    }

17. }

先看分库表路由策略中的doEqualSharding方法，比如对于SQL语句—“Insert into testmsgqueuebillrecord(CUSTOMERID,USERID,xxxx1,xxxx2……)values(‘testcustid111111’,’testuserid22222’,’xxxx1’,’xxxx2’……)”,doEqualSharding方法中的形参databaseNames为分库的名称（比如shardingDataSource0数据源对应的实际分库名称为restestcloudbillsharding0），shardingValue为分库的键值（在前一节分库分表Bean配置中设定），也就是对应该SQL语句中的“testcustid111111”，我们可以在doEqualSharding方法中设置适合业务需求的键值分片路由规则，比如以“testcustid111111”的哈希值作为分片路由规则来选择该插入SQL究竟应该路由至实际的哪个分库的哪个分表来执行。而另外的两个方法，doInSharding和doBetweenSharding则是作用在SQL条件为“Between”和“In”的语句上。

分布式主键生成

对于传统的数据表设计而言，主键自增都是基本需求。对MySQL而言，分库分表之后，不同表生成全局唯一主键是较为头疼的问题。因为同一个逻辑表所对应的不同实际分表之间的自增键是无法感知，这样会造成有部分分表的主键值重复。我们当然可以通过分表的约束规则来达到数据不重复，但是这需要引入额外的方法来解决重复性问题。

目前，有许多第三方技术方案可以解决该问题，比如flickr的全局主键生成方案和uuid的全局主键生成方案。ShardingJdbc在设计之初也有考虑过这个问题，提供了自己的实现方案，其分布式全局ID自动生成器可以根据时间偏移量、工作进程id和同一毫秒的自增量来生成一个64位的Long型数值以保证其全局唯一性。

在工程中需要在pom中添加依赖如下：

在配置中需要完成对其Bean对象的初始化，具体代码如下：

1. @Bean(name = "shardingIdGenerator")

2. public IdGenerator getIdGenerator() {

3.    return new CommonSelfIdGenerator();

4. }

使用ShardingJdbc编写自定义的Dao

经过上面几节内容后，这一节就可以使用配置后的ShardingJdbc所对应的MybatisDao来完成一些业务的应用了。

1. public abstract class AbstractResBillShardingDao {

2.    @Qualifier(value = "hwShardingMyBatisDao")

3.    @Autowired

4.    protected IMybatisDAO mybatisShardingDao;

5.    @Qualifier(value = "shardingIdGenerator")

6.    @Autowired

7.    private IdGenerator idGenerator;

8.    public <T extends BillRecord> int shardingInsertRows (List<T> resBillRecordList, String resType) {

9.        int resultRowNum = 0;

10.        try {

11.            String shardingInsertSqlMapper = MyBatisMapperMapping.ResBatchInsertMapper.getBatchInsertMapperByTypeCode(resType);

12.            if(StringUtils.isEmpty(shardingInsertSqlMapper)) {

13.                log.info("无法匹配到对应的sqlmapper的statement,直接返回,无法匹配的资源类型为:{}",resType);

14.                return resultRowNum;

15.            }

16.            for(BillRecord msgQueueBillRecord:resBillRecordList) {

17.                //设置分布式id主键msgQueueBillRecord.setId(idGenerator.generateId().longValue());

18.            resultRowNum+=mybatisShardingDao.insert(shardingInsertSqlMapper, msgQueueBillRecord);

19.            }

20.        } catch (Exception ex) {

21.            log.error("批量插入数据出现异常", ex);

22.        }

23.        return resultRowNum;

24.    }

25. }

可以看到在这个Dao的类中，批量插入方法—shardingInsertBills，通过自动注入的mybatisShardingDao和idGenerator来完成对数据记录的切分插入，这里需要注意ShardingJdbc目前尚未支持批量插入的SQL语句，因此需要在代码中自己完成遍历数据集合后的单行插入，这里idGenerator用于生成每个待插入记录的全局唯一健值。工程运行的截图日志如下：

使用ShardingJdbc的限制

ShardingJdbc中间件并非是万能，它还是有一些SQL和JDCB接口的使用限制，其最大的使用限制在于如下几点：

（1）对于DataSource接口不支持超时相关的操作。

（2）对于Connection接口不支持存储过程、游标、函数、savePoint、自定义类型映射等。

（3）对于Statement和PreparedStatement接口，不支持返回多结果集的语句和国际化操作。

（4）对于ResultSet接口，不支持对于结果集指针位置判断；不支持通过非next方法改变结果指针位置；不支持修改结果集内容。

（5）SQL语句限制：不支持HAVING；不支持OR，UNION 和 UNION ALL；不支持特殊INSERT，尤其是是批量插入的SQL语句；不支持DISTINCT聚合；不支持dual虚拟表；不支持SELECT LASTINSERTID()；不支持CASE WHEN。

总结

本文主要介绍了如何在Spring Boot工程中完成数据库切分中间件—ShardingJdbc集成以及如何进行分库分表配置、库表分片路由策略设置和分布式主键生成关键点的阐述。限于笔者的才疏学浅，对本文内容可能还有理解不到位的地方，如有阐述不合理之处还望留言一起探讨。

热文推荐：

其他推荐：

长按指纹

一键关注

深入交流、更多福利

扫码加入我的知识星球

点击 “阅读原文” 看看本号其他精彩内容