Sharding-Jdbc学习笔记三之水平分表+读写分离

文章目录

• • 二、 水平分表+读写分离
  • 项目通用配置和项目错误汇总

由于该学习笔记是循序渐进的，因此都是在前面的配置基础上再增加额外配置，正好这种配置又是不冲突的，这样就可以在学习新的配置方式的时候把前面的配置也融合进来，所以这里是在前面读写分离的配置上增加了水平分表。

二、 水平分表+读写分离

由于读写分批和分表的配置是不冲突的，所以也就在配置水平分表的时候将前面配置的读写分离一块配置上了

目标总结：

现在已经有了数据库boot-quick，每个库都有一个表为auth_user, 我们现在在master在增加一个表auth_user，用来做水平分表。这里其实是给自己找事， 如果表命名为auth_user_1, auth_user_2，后面有些表达式会好写一些，可是我就是刚开始没分表，就叫auth_user， 我后来才要分的呀，我就是要犟。

1. sql语句

增加水平分表的sql语句

use `boot-quick`;
CREATE TABLE auth_user_1
(
    id BIGINT(20) NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
    user_name VARCHAR(30) NOT NULL COMMENT '姓名',
    user_token varchar(64) NOT NULL COMMENT '用户随机码，生成**的盐，注册时生成且不可变！',
    password VARCHAR(32) NOT NULL COMMENT '密码',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    birthday DATE NULL DEFAULT NULL COMMENT '生日',
    last_modify_password bigint  COMMENT '最后一次修改密码的时间',
    last_login_time bigint COMMENT '最后一次使用密码登录的时间',
 
    create_by VARCHAR(32) NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    modify_by VARCHAR(32) NULL,
    modify_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    removed INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,
 
    PRIMARY KEY (id)
);

2. 项目配置

######################################水平分表+读写分离配置############################################################
 
# mysql master连接信息
master.mysql.host: localhost
master.mysql.port: 3306
master.mysql.db: boot-quick
master.mysql.username: root
master.mysql.password: 123456
 
# mysql slave0连接信息
slave0.mysql.host: localhost
slave0.mysql.port: 3307
slave0.mysql.db: boot-quick
slave0.mysql.username: root
slave0.mysql.password: 123456
 
# mysql slave0连接信息
slave1.mysql.host: localhost
slave1.mysql.port: 3308
slave1.mysql.db: boot-quick
slave1.mysql.username: root
slave1.mysql.password: 123456
 
spring:
  main:
    allow-bean-definition-overriding: true
 
  # 经测试filter需要配置再datasource.druid下，而每个连接的信息还是得配置在sharding-sphere下的datasource每个自己的
  datasource:
    druid:
      filter:
        stat:
          enabled: true
          log-slow-sql: true
          slow-sql-millis: 3000
        wall:
          enabled: true # 开启WallFilter
          db-type: mysql
      ## 开启内置监控界面 访问路径: /context-path/druid/index.html
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: true
        login-username: admin
        login-password: 123456
        allow:
 
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      # 配置数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${master.mysql.host}:${master.mysql.port}/${master.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${master.mysql.username}
        password: ${master.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
      # 配置数据源
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${slave0.mysql.host}:${slave0.mysql.port}/${slave0.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${slave0.mysql.username}
        password: ${slave0.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
      # 配置数据源
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${slave1.mysql.host}:${slave1.mysql.port}/${slave1.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${slave1.mysql.username}
        password: ${slave1.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
 
    # 分支分片策略
    sharding:
      # https://shardingsphere.apache.org/document/legacy/4.x/document/cn/faq/#6-%E5%A6%82%E6%9E%9C%E5%8F%AA%E6%9C%89%E9%83%A8%E5%88%86%E6%95%B0%E6%8D%AE%E5%BA%93%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%98%AF%E5%90%A6%E9%9C%80%E8%A6%81%E5%B0%86%E4%B8%8D%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E7%9A%84%E8%A1%A8%E4%B9%9F%E9%85%8D%E7%BD%AE%E5%9C%A8%E5%88%86%E7%89%87%E8%A7%84%E5%88%99%E4%B8%AD
      # 为了解决不分库分表的表不需要配置分片规则
      default-data-source-name: master
      # 分表
      tables:
        # 逻辑表名， 即原表名比如有auth_user，现在分为实际的auth_user_1, auth_user_2, auth_user即为逻辑表名
        auth_user:
          # 配置数据表分布情况，哪个表在哪个数据源里,有哪些表
          actual-data-nodes: master.auth_user,master.auth_user_1
          table-strategy:
            # 指定当前表的分片策略
            inline:
              sharding-column: id
              # 配置分表策略， id对2（分表数量）进行取模，为0则使用则为auth_user， 其它则增加后缀"_" + 取模后的值
              algorithm-expression: auth_user$->{id % 2 == 0 ? "":"_" + id % 2}
 
      # 配置分片之后的读写分离，分片之后的读写分离配置和只有读写分离的匹配属性是一样的，但是配置的位置是不同的，分片后读写分离配置要在sharding下
      master-slave-rules:
        # 对上面配置的actual-data-nodes的库配置读写分离策略
        master:
          # 从库负载均衡算法
          load-balance-algorithm-type: round_robin
          # 主库数据源名称， 从上面配置的数据源中选择
          master-data-source-name: master
          # 从库数据源名称列表， 从上面配置的数据源中选择
          slave-data-source-names: slave0,slave1
 
    props:
      #  显示sql具体信息
      sql.show: true
 

3. 项目演示结果

根据逻辑表auth_user的id进行水平分表， 对分表数量进行取模， 为0则使用则为auth_user， 其它则增加后缀"_" + 取模后的值，当前分表数量为2.

至于代码开发部分，其实就是简单的mvc的代码，和使用单表并无区别，这里并不打算用大量的内容去粘贴这些代码

即如果取模后的值为0， 则路由到auth_user；取模后的值为1， 则路由到auth_user_1

路由到auth_user的效果, 查询的时候到两个库查询是因为无法通过userName来确定到底该路由到哪张表，因为我们是通过id分表的

项目通用配置和项目错误汇总

• 默认连接未配置，刚开始使用的版本没有这个问题，后来升到了4.1.1有这个问题

  Action:
  
  Consider the following:
      If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
      If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
  

  则需要在主启动类上排除当前项目所依赖的连接池的自动配置类，如使用的druid

  @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
  

• datasource已定义

  Description:
  
  The bean 'dataSource', defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class] and overriding is disabled.
  
  Action:
  
  Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
  

  则按照上面的提示，加上配置

  spring:
    main:
      allow-bean-definition-overriding: true