mysql 集成 sharding-jdbc/springBoot进行读写分离实战配置

在我们是使用数据库的过程中不管是否进行了分库分表，作为常规的产品，数据库读写分离是必备的，一般是一写多读，主库写，从库读；合理解决数据库并发问题。今天我们分享一写两读的数据库架构理念。

1、数据库相关jar 引用：

	       <dependency>
				<groupId>com.alibaba</groupId>
				<artifactId>druid-spring-boot-starter</artifactId>
				<version>1.1.9</version>
			</dependency>
            <dependency>
				<groupId>mysql</groupId>
				<artifactId>mysql-connector-java</artifactId>
				<version>5.1.49</version>
			</dependency>


            <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
             <version>3.1.0</version>
          </dependency>
 
          <dependency>
             <groupId>io.shardingsphere</groupId>
             <artifactId>sharding-core</artifactId>
             <version>3.1.1</version>
         </dependency>
 

2、主从数据库配置：

sharding:
  jdbc:
    datasource:
      names: user0,user1,user2 #数据源集
      user0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sys_db?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
        username: root
        password: 123456
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
      user1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/people_db?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
        username: root
        password: 123456
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
      user2:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/user_sys?useAffectedRows=true&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT&zeroDateTimeBehavior=convertToNull
        username: root
        password: 123456
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true


    config:
      masterslave:
        load-balance-algorithm-type: round_robin #从库负载均衡算法类型，可选值：ROUND_ROBIN，RANDOM。若`load-balance-algorithm-class-name`存在则忽略该配置
        #load-balance-algorithm-class-name: com.nandao.CommonBalance #从库负载均衡算法类名称。该类需实现MasterSlaveLoadBalanceAlgorithm接口且提供无参数构造器
        name: dataSource
        master-data-source-name: user2 #主库数据源名称
        slave-data-source-names: user1,user0 #从库数据源名称列表

        
#  1、读取数据使用从数据库(slave),多个从数据库算法：round_robin（轮询）和random（随机）
#  2、写入数据使用主数据库(master，添加，修改，删除)
#  3、读写分离配置 master为主，slave为从,多个从数据库逗号分隔

3、核心为代码执行查询、新增数据

        TblUser user = new TblUser();
		user.setName("test");
		user.setCityId(1%2==0?1:2);
		user.setCreateTime(new Date());
		user.setSex(true);
		user.setPhone("11111111");
		user.setEmail("xxxxx");
		user.setCreateTime(new Date());
		user.setPassword("eeeeeeeeeeee");

		List<TblUser> tblUsers = tblUserMapper.selectList(new EntityWrapper<TblUser>().last("limit 3"));//从表查询

		tblUserMapper.insert(user);//主表插入

4、打印的核心日志分析验证读写分离的理念

......... INFO  ShardingSphere-SQL - SQL: SELECT  id AS id,`name`,city_id AS cityId,sex,phone,email,create_time AS createTime,`password`  FROM tbl_user 
  
 
 limit 3 ::: DataSources: user1



.......

.........INFO  ShardingSphere-SQL - SQL: INSERT INTO tbl_user 
 ( `name`,
	city_id,
	sex,
	phone,
	email,
	create_time,
	`password` )  VALUES 
 ( ?,
	?,
	?,
	?,
	?,
	?,
	? ) ::: DataSources: user2

查询在从表user1（根据算法决定去哪个从表查询）,保存一直在user2表。

5、注意事项：Sharding-JDBC 目前仅支持一主多从的结构策略

sharding.jdbc 本身 没有做数据主从复制，即把数据从主库复制到从库,因此，所谓的读写分离实际上是行不通的，因为从库里没有数据可读，即读写分离必须建立在主从数据复制的基础之上，这时，需要自己处理多个数据库之间的同步数据操作，一般是运维操作层面的业务，不过随着sharding的发展，后期肯定支持数据主从复制，基于此下篇我们分析mysql 数据库数据主从复制的操作，敬请期待！