sharding jdbc分库分表快速入门demo

声明 文中yml中 ￥符号代表$

1. 建立数据库 ds0, ds1库中建立user_0,user_1,user_2表，部分DDL
2. create database ds0;
3. use database ds0;
4. create table user_0 (
   id bigint, user_name varchar(20),
   number bigint,
   primary key(id)
   )
   maven依赖
   
   org.springframework.boot
   spring-boot-starter-web
   
   
   mysql
   mysql-connector-java
   runtime
   
   
   org.apache.shardingsphere
   sharding-jdbc-spring-boot-starter
   4.1.1
   
   
   com.baomidou
   mybatis-plus-boot-starter
   3.4.0
   
   
   org.springframework.boot
   spring-boot-starter-test
   test
   
   
   org.junit.vintage
   junit-vintage-engine
   
   
   
   application.yml
   server:
   port: 7777
   spring:
   shardingsphere:
   datasource:
   names: ds0,ds1 #数据源名称 多个用逗号隔开
   ds0:
   type: com.zaxxer.hikari.HikariDataSource
   driverClassName: com.mysql.cj.jdbc.Driver
   jdbcUrl: jdbc:mysql://localhost:3306/ds0?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8
   username: root
   password: 123456
   hikari:
   minimum-idle: 10
   max-lifetime: 1800000
   connection-timeout: 30000
   connection-test-query: SELECT 1
   idle-timeout: 30000
   pool-name: ds0
   maximum-pool-size: 10
   ds1:
   type: com.zaxxer.hikari.HikariDataSource
   driverClassName: com.mysql.cj.jdbc.Driver
   jdbcUrl: jdbc:mysql://localhost:3306/ds1?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8
   username: root
   password: 123456
   hikari:
   minimum-idle: 10
   max-lifetime: 1800000
   connection-timeout: 30000
   connection-test-query: SELECT 1
   idle-timeout: 30000
   pool-name: ds1
   maximum-pool-size: 10
   sharding:
   tables:
   user:
   actual-data-nodes: ds￥{0…1}.user_￥{0…2}
   database-strategy:
   inline:
   sharding-column: number
   algorithm-expression: ds￥{number % 2}
   table-strategy:
   inline:
   sharding-column: number
   algorithm-expression: user_${number % 3}
   key-generator:
   column: id
   type: SNOWFLAKE
   props:
   worker.id: 100
   max.vibration.offset: 4
   props:
   sql:
   show: true
   测试控制器代码
   public static Long i = 0L;
   @Autowired
   UserService userService;
   @GetMapping("/get")
   public List getUsers() {
   List users = userService.getUsers();
   return users;
   }
   @GetMapping("/insert")
   public Integer insertUsers() {
   User user = new User();
   user.setUserName(“bear” + (i++));
   user.setNumber(i);
   Integer users = userService.insert(user);
   return users;
   }
   首先执行插入 访问http://localhost:7777/insert
   日志输出如下

number为1 1%2 = 1,因此结果应该在ds1库，1%3=1，所以在user_1表，去数据库验证结果

也可以访问http://localhost:7777/get查询刚刚插入的数据， 多次调用insert方法可以发现随着i递增数据是均衡分布到各个表，我们的分库分表是成功生效了，查询的时候可以看到SQL日志输出

因为本文是select * 所以查询语句是遍历了所有的库和所有的表然后聚合的，工作中的查询应该尽量避免where条件不带分表列的查询，因为这样会导致SQL全路由，假如分表很多，一条SQL被路由到多个表执行会耗费很高的性能和很高的连接开销。