数据库分库分表（七）shardingjdbc读写分离demo

一、读写分离demo：

1、数据库准备：

这里新建了两个数据库，一主二从

每个数据库里面表结构都是一样的，（我这里mysql没有配置主从复制，所以后面的代码演示，从库数据是我手动从主库复制过去的）

CREATE TABLE `t_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2、配置文件：

server.context-path=/sharding
server.port=5555
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml

spring.shardingsphere.datasource.names = master,slave1,slave2
#配置master
spring.shardingsphere.datasource.master.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/sharding_demo_master?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=******
spring.shardingsphere.datasource.master.maxPoolSize = 100
spring.shardingsphere.datasource.master.minPoolSize = 5
#配置slave1
spring.shardingsphere.datasource.slave1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3306/sharding_demo_slave1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=******
spring.shardingsphere.datasource.slave1.maxPoolSize = 100
spring.shardingsphere.datasource.slave1.minPoolSize = 5
#配置slave2
spring.shardingsphere.datasource.slave2.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.url=jdbc:mysql://localhost:3306/sharding_demo_slave2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=******
spring.shardingsphere.datasource.slave2.maxPoolSize = 100
spring.shardingsphere.datasource.slave2.minPoolSize = 5
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置读写分离
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave2,slave1
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
#默认
spring.shardingsphere.sharding.default-data-source-name=master
spring.shardingsphere.masterslave.name=ms

3、数据源配置

package com.demo.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    //指定当前对象作为bean
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

    @Bean(name = "slave1")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.slave1")
    public DataSource slave1DataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

    @Bean(name = "slave2")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.slave2")
    public DataSource slave2DataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
}

4、测试：

（1）新增：执行localhost:5555/sharding/user/add?name=张三1&age=1

2021-12-24 14:36:48.242  INFO 5380 --- [nio-5555-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:36:48.245  INFO 5380 --- [nio-5555-exec-2] ShardingSphere-SQL                       : SQL: insert into t_user(user_name,age) values (?,?) ::: DataSources: master

查看数据库，只有主库插入了数据

（2）查询：多次执行localhost:5555/sharding/user/getById?id=1

通过日志可以看到两个从库在轮询

2021-12-24 14:37:55.661  INFO 5380 --- [nio-5555-exec-5] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:37:55.661  INFO 5380 --- [nio-5555-exec-5] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave2
2021-12-24 14:38:01.974  INFO 5380 --- [nio-5555-exec-6] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:01.974  INFO 5380 --- [nio-5555-exec-6] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave1
2021-12-24 14:38:02.699  INFO 5380 --- [nio-5555-exec-7] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:02.699  INFO 5380 --- [nio-5555-exec-7] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave2
2021-12-24 14:38:03.416  INFO 5380 --- [nio-5555-exec-8] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:03.416  INFO 5380 --- [nio-5555-exec-8] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave1

（3）更新


        update t_user set user_name = #{userName} where id = #{id}

访问localhost:5555/sharding/user/update?name=张三2&id=5，更新主表

2021-12-24 16:19:30.791  INFO 9536 --- [nio-5555-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 16:19:30.798  INFO 9536 --- [nio-5555-exec-2] ShardingSphere-SQL                       : SQL: update t_user set user_name = ? where id = ? ::: DataSources: master

（4）删除


        delete from t_user where id=#{id}

访问localhost:5555/sharding/user/deleteById?id=5

2021-12-24 16:20:47.539  INFO 9536 --- [nio-5555-exec-5] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 16:20:47.539  INFO 9536 --- [nio-5555-exec-5] ShardingSphere-SQL                       : SQL: delete from t_user where id=? ::: DataSources: master

二、垂直分库+水平分库+水平分表+读写分离：

1、数据库准备：

（1）公共库：

user库有一张数据表

CREATE TABLE `t_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

（2）订单表水平分库+水平分表，按年分为2020和2021两个库，这两个库做读写分离再分别有一主一从，总共是4个库

每个库里面都是1到12共12张月表，表结构都是一样的

CREATE TABLE `t_order_1` (
  `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_number` varchar(255) DEFAULT NULL COMMENT '订单号',
  `order_date` date DEFAULT NULL COMMENT '下单时间',
  `user_id` bigint(20) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（我这里Mysql没有配置主从复制，从库的数据仍然是我手动从主库复制过去的）

2、配置文件：

数据库分库分表（七）shardingjdbc读写分离demo

Java相关栏目本月热门文章