写在前面

sharding jdbc 提供了多种接入方式。我这里只介绍两种接入方式:

  1. 引入sharding-jdbc的springboot starter包,通过application.yml进行配置(当前文章的主要介绍内容)
  2. 通过java代码的硬编码方式接入(比较推荐,下一篇文章详细介绍)

分库分表工具介绍:ShardingSphere

这里我就不过多赘述,直接贴上ShardingSphere的官网地址,想深入了解的小伙伴可以自行查阅
https://shardingsphere.apache.org/document/current/cn/overview/
ps:(小声逼逼一句)我觉得这个官方文档,只适合看看介绍,如果真的要实战操作,根据这个官方文档写的,真的会让人头大的

此demo的介绍

我原本做的是一个停车场的系统,现在由于数据量过多,需要将订单表拆分,而我这里的拆分逻辑是根据停车场id进行拆分,例如200个停车场就是200张订单表。
总的来说

分库逻辑:
按照创建时间的年份分库
分表逻辑:
按照停车场id进行分表

准备工作

数据库脚本

由于我们不是所有的表都要分表,所以数据库就有默认库,默认库是存放除了分表以外其他表的数据,具体配置见下面的详细步骤中的设置默认库
默认库:sharding2020

  1. /*
  2. Navicat MySQL Data Transfer
  3. Source Server : 开发数据库 4.71
  4. Source Server Version : 50730
  5. Source Host : 192.168.4.71:3307
  6. Source Database : sharding2020
  7. Target Server Type : MYSQL
  8. Target Server Version : 50730
  9. File Encoding : 65001
  10. Date: 2020-05-08 11:05:41
  11. */
  12. SET FOREIGN_KEY_CHECKS=0;
  13. -- ----------------------------
  14. -- Table structure for t_car_park
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `t_car_park`;
  17. CREATE TABLE `t_car_park` (
  18. `id` varchar(64) NOT NULL,
  19. `name` varchar(100) DEFAULT NULL COMMENT '名称',
  20. `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  21. PRIMARY KEY (`id`)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='停车场表';
  23. -- ----------------------------
  24. -- Table structure for t_order_0
  25. -- ----------------------------
  26. DROP TABLE IF EXISTS `t_order_0`;
  27. CREATE TABLE `t_order_0` (
  28. `id` varchar(64) NOT NULL,
  29. `name` varchar(100) DEFAULT NULL COMMENT '名称',
  30. `car_park_id` varchar(64) DEFAULT NULL COMMENT '停车场id',
  31. `no` varchar(100) DEFAULT NULL COMMENT '订单号',
  32. `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  33. PRIMARY KEY (`id`)
  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试分表';
  35. -- ----------------------------
  36. -- Table structure for t_order_1
  37. -- ----------------------------
  38. DROP TABLE IF EXISTS `t_order_1`;
  39. CREATE TABLE `t_order_1` (
  40. `id` varchar(64) NOT NULL,
  41. `name` varchar(100) DEFAULT NULL COMMENT '名称',
  42. `car_park_id` varchar(64) DEFAULT NULL COMMENT '停车场id',
  43. `no` varchar(100) DEFAULT NULL COMMENT '订单号',
  44. `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  45. PRIMARY KEY (`id`)
  46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试分表';

其中t_car_park就是停车场表,属于不用分表的业务表
第二个库:sharding2019

  1. /*
  2. Navicat MySQL Data Transfer
  3. Source Server : 开发数据库 4.71
  4. Source Server Version : 50730
  5. Source Host : 192.168.4.71:3307
  6. Source Database : sharding2019
  7. Target Server Type : MYSQL
  8. Target Server Version : 50730
  9. File Encoding : 65001
  10. Date: 2020-05-08 11:06:06
  11. */
  12. SET FOREIGN_KEY_CHECKS=0;
  13. -- ----------------------------
  14. -- Table structure for t_order_0
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `t_order_0`;
  17. CREATE TABLE `t_order_0` (
  18. `id` varchar(64) NOT NULL,
  19. `name` varchar(100) DEFAULT NULL COMMENT '名称',
  20. `car_park_id` varchar(64) DEFAULT NULL COMMENT '停车场id',
  21. `no` varchar(100) DEFAULT NULL COMMENT '订单号',
  22. `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  23. PRIMARY KEY (`id`)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试分表';
  25. -- ----------------------------
  26. -- Table structure for t_order_1
  27. -- ----------------------------
  28. DROP TABLE IF EXISTS `t_order_1`;
  29. CREATE TABLE `t_order_1` (
  30. `id` varchar(64) NOT NULL,
  31. `name` varchar(100) DEFAULT NULL COMMENT '名称',
  32. `car_park_id` varchar(64) DEFAULT NULL COMMENT '停车场id',
  33. `no` varchar(100) DEFAULT NULL COMMENT '订单号',
  34. `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  35. PRIMARY KEY (`id`)
  36. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试分表';

注意
这里有一个限制,如果配置了分库,则两个库以torder为开头的表,在两个库中的表结构表数量一定要是一样的,不然查询的时候shardingjdbc就会报错!!!

pom文件

  1. <dependencies>
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-web</artifactId>
  5. </dependency>
  6. <dependency>
  7. <groupId>org.springframework.boot</groupId>
  8. <artifactId>spring-boot-starter-test</artifactId>
  9. <scope>test</scope>
  10. <exclusions>
  11. <exclusion>
  12. <groupId>org.junit.vintage</groupId>
  13. <artifactId>junit-vintage-engine</artifactId>
  14. </exclusion>
  15. </exclusions>
  16. </dependency>
  17. <!--mybatisplus-->
  18. <dependency>
  19. <groupId>com.baomidou</groupId>
  20. <artifactId>mybatis-plus-boot-starter</artifactId>
  21. <version>3.2.0</version>
  22. </dependency>
  23. <!--lombok-->
  24. <dependency>
  25. <groupId>org.projectlombok</groupId>
  26. <artifactId>lombok</artifactId>
  27. <scope>provided</scope>
  28. </dependency>
  29. <!--fastjson-->
  30. <dependency>
  31. <groupId>com.alibaba</groupId>
  32. <artifactId>fastjson</artifactId>
  33. <version>1.2.60</version>
  34. </dependency>
  35. <!--mysql-->
  36. <dependency>
  37. <groupId>mysql</groupId>
  38. <artifactId>mysql-connector-java</artifactId>
  39. <version>5.1.44</version>
  40. </dependency>
  41. <!--druid-->
  42. <dependency>
  43. <groupId>com.alibaba</groupId>
  44. <artifactId>druid</artifactId>
  45. <version>1.1.20</version>
  46. </dependency>
  47. <dependency>
  48. <groupId>org.springframework.boot</groupId>
  49. <artifactId>spring-boot-starter-log4j2</artifactId>
  50. </dependency>
  51. <!--sharding jdbc springboot-->
  52. <dependency>
  53. <groupId>org.apache.shardingsphere</groupId>
  54. <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  55. <version>4.0.0-RC2</version>
  56. </dependency>
  57. <dependency>
  58. <groupId>org.apache.shardingsphere</groupId>
  59. <artifactId>sharding-jdbc-spring-namespace</artifactId>
  60. <version>4.0.0-RC2</version>
  61. </dependency>
  62. </dependencies>

pom文件需要注意的问题:

  1. sharding-jdbc-spring-boot-starter以及sharding-jdbc-spring-namespace这里的版本用的是4.0.0-RC2,其他版本我也没试过,不知道有没有问题。
  2. 如果引用了sharingjdbc starter包,那么druid的包不能是starter的,只能是普通版本的
  3. 目前这个版本sharingjdbc包的版本好像和mybatisplus的版本有冲突,问题会在分页的时候出现,那就是分页的时候,汇总字段,会出现long类型转int的错误,经过测试,mybatis plus 版本选择3.3.1.tmp,sharding的版本选择4.0.0就不会出现这样的问题

application.yml

  1. spring:
  2. shardingsphere:
  3. datasource:
  4. # 数据库名称,可自定义,可以为多个,以逗号隔开,每个在这里定义的库,都要在下面定义连接属性
  5. names: ds2019,ds2020
  6. #年份为2019年的库
  7. ds2019:
  8. # 采用的数据库连接池,druid
  9. type: com.alibaba.druid.pool.DruidDataSource
  10. driver-class-name: com.mysql.jdbc.Driver
  11. url: jdbc:mysql://192.168.4.71:3307/sharding2019?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
  12. username: root
  13. password: 123456
  14. maxActive: 20
  15. initialSize: 5
  16. maxWait: 60000
  17. minIdle: 5
  18. timeBetweenEvictionRunsMillis: 60000
  19. minEvictableIdleTimeMillis: 300000
  20. validationQuery: SELECT 1 FROM DUAL
  21. testWhileIdle: true
  22. testOnBorrow: false
  23. testOnReturn: false
  24. #是否缓存preparedStatement,也就是PSCache。在mysql下建议关闭。 PSCache对支持游标的数据库性能提升巨大,比如说oracle。
  25. poolPreparedStatements: false
  26. #要启用PSCache,-1为关闭 必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true 可以把这个数值配置大一些,比如说100
  27. maxOpenPreparedStatements: -1
  28. #配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
  29. filters: stat,wall,log4j2
  30. #通过connectProperties属性来打开mergeSql功能;慢SQL记录
  31. connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  32. #合并多个DruidDataSource的监控数据
  33. useGlobalDataSourceStat: true
  34. loginUsername: druid
  35. loginPassword: druid
  36. #年份为2020年的库
  37. ds2020:
  38. type: com.alibaba.druid.pool.DruidDataSource
  39. driver-class-name: com.mysql.jdbc.Driver
  40. url: jdbc:mysql://192.168.4.71:3307/sharding2020?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
  41. username: root
  42. password: 123456
  43. maxActive: 20
  44. initialSize: 5
  45. maxWait: 60000
  46. minIdle: 5
  47. timeBetweenEvictionRunsMillis: 60000
  48. minEvictableIdleTimeMillis: 300000
  49. validationQuery: SELECT 1 FROM DUAL
  50. testWhileIdle: true
  51. testOnBorrow: false
  52. testOnReturn: false
  53. #是否缓存preparedStatement,也就是PSCache。在mysql下建议关闭。 PSCache对支持游标的数据库性能提升巨大,比如说oracle。
  54. poolPreparedStatements: false
  55. #要启用PSCache,-1为关闭 必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true 可以把这个数值配置大一些,比如说100
  56. maxOpenPreparedStatements: -1
  57. #配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
  58. filters: stat,wall,log4j2
  59. #通过connectProperties属性来打开mergeSql功能;慢SQL记录
  60. connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  61. #合并多个DruidDataSource的监控数据
  62. useGlobalDataSourceStat: true
  63. loginUsername: druid
  64. loginPassword: druid
  65. sharding:
  66. default-data-source-name: ds2020
  67. #需要拆分的表,可以设置多个
  68. tables:
  69. #需要进行分表的逻辑表名,用MyBatis或者MyBatis-Plus操作数据库时只需要操作逻辑表即可,xml文件也只需要配置逻辑表
  70. t_order:
  71. #实际的表结点,下面代表的是ds2019.t_order_0、ds2019.t_order_1、ds2020.t_order_0、ds2020.t_order_1 这几张表
  72. actual-data-nodes: ds$->{2019..2020}.t_order_$->{0..1}
  73. #分库策略,按照创建时间的年份分库,如果不用分库的,直接注释掉分库相关的代码
  74. database-strategy:
  75. standard:
  76. sharding-column: create_time
  77. precise-algorithm-class-name: com.example.demo.config.sharding.CreateTimeShardingDatabaseAlgorithm
  78. table-strategy:
  79. # 分表策略,根据自己的需要写的分表策略,这里我是根据car_park_id这个字段的值作为后缀,来确定放到哪张表
  80. standard:
  81. sharding-column: car_park_id
  82. precise-algorithm-class-name: com.example.demo.config.sharding.CarParkShardingTableAlgorithm
  83. props:
  84. # 是否打印逻辑SQL语句和实际SQL语句,建议调试时打印,在生产环境关闭
  85. sql:
  86. show: true
  87. #是否输出Mybatis-Plus代执行的SQL语句
  88. logging:
  89. level:
  90. com.example.demo.module.dao: trace
  91. server:
  92. port: 8888

这里需要注意的问题

  1. database-strategy: 这个的意思是自定义分库策略,同样的table-strategy:这个代表的是自定义分表策略。而这和官方文档提供的分表分片策略有所不同,官方文档提供的是
    分库分表之springboot   sharding jdbc - 图1
    这个的意思是直接通过id取模分表,比如id为3,3%2 = 1,所以这条数据会被分配到t_order_1这种表中,这样的好处就是不用自己写配置类,比较方便,但是这种形式不够灵活,因为自己写分表配置类,也可以实现上面所说的功能。
    官方提供的分片策略有很多,可以支持自己定义,很灵活。
    可以参考:
    https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sharding/
    这里一定要记住关键字的区别:
    standard 代表自定义分表策略,需要配合precise-algorithm-class-name属性指定自定义分表策略所在的类
    inline 代表的是行表达式分片策略,可以通过简单的算法表达式直接进行分表

设置默认库
分了多个库,必须设置默认数据库!!!必须!!!不然普通业务表shardingjdbc不知道去哪个库取。
配置方式参考配置文件中的default-data-source-name: sharding2020
这里面填的数据库名字和上面配置的names必须是一样的

分库策略类

  1. package com.example.demo.config.sharding;
  2. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
  3. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
  4. import java.text.SimpleDateFormat;
  5. import java.util.Collection;
  6. import java.util.Date;
  7. /**
  8. * @title: CarParkShardingTableAlgorithm
  9. * @projectName shardingdemo
  10. * @description: 按创建时间分库
  11. * @author zhy
  12. * @date 2020/5/611:25
  13. */
  14. public class CreateTimeShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Date> {
  15. @Override
  16. public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
  17. Date value = preciseShardingValue.getValue();
  18. SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
  19. StringBuilder sb = new StringBuilder();
  20. sb.append("ds").append(sdf.format(value));
  21. return sb.toString();
  22. }
  23. }

分表策略类

  1. package com.example.demo.config.sharding;
  2. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
  3. import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
  4. import java.util.Collection;
  5. /**
  6. * @title: CarParkShardingTableAlgorithm
  7. * @projectName shardingdemo
  8. * @description: 按停车场id分表
  9. * @author zhy
  10. * @date 2020/5/611:25
  11. */
  12. public class CarParkShardingTableAlgorithm implements PreciseShardingAlgorithm<String> {
  13. @Override
  14. public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
  15. StringBuilder sb = new StringBuilder();
  16. String value = preciseShardingValue.getValue();
  17. //获取设置的虚拟表名称,这里获取到的logicTableName=t_order
  18. String logicTableName = preciseShardingValue.getLogicTableName();
  19. //拼接实际的表名称,value为carParkId字段的值
  20. sb.append(logicTableName).append("_").append(value);
  21. return sb.toString();
  22. }
  23. }

最后贴上源码地址,可以直接跑起来,看看效果,记得修改自己的数据库地址哦
https://github.com/zhyhuayong/shardingSpringbootDemo

存在的问题

这种方式适合表结构一定的方案,例如按月分表,每个月的表结构都能确定下来,也就是application.yml中的actual-data-nodes这个配置能确定下来,项目启动之后就不变,可以一直使用。但是问题来了,我这边的分表逻辑是按停车场id分表,停车场的是可以增删的,那么我现在分表的actual-data-nodes这个也要随着我停车场的改变而改变,所以这个问题困扰了我很久,最后我通过sharding-jdbc基于java的配置,加上ShardingSphere的服务编排治理,实现了在项目运行中动态的改变actual-data-nodes。详情请参考下一篇文章