Sharding-JDBC--读写分离+分库分表

Bertha 。 2023-02-16 12:04 34阅读 0赞

首发网址：[Sharding-JDBC--读写分离+分库分表\_IT利刃出鞘的博客-CSDN博客][Sharding-JDBC--_IT_-CSDN]

# 配置说明 #

[配置说明][Link 1]（官网）

[行表达式][Link 2]（官网）

# 主从搭建 #

**其他网址**

> [Sharding-JDBC教程：Mysql数据库主从搭建\_方志朋的专栏-CSDN博客\_shardingjdbcmysqll][Sharding-JDBC_Mysql_-CSDN_shardingjdbcmysqll]

# 读写分离 #

**其他网址**

> [Sharding-JDBC教程：SpringBoot整合Sharding-JDBC实现读写分离\_方志朋的专栏-CSDN博客][Sharding-JDBC_SpringBoot_Sharding-JDBC_-CSDN]

上边“主从创建”中已经详细的讲解了如何构建Mysql5.7的读写分离，并且已经构建好了。详细信息如下

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <th>数据库类型</th> 
   <th>数据库</th> 
   <th>ip</th> 
  </tr> 
  <tr> 
   <td>主</td> 
   <td>cool</td> 
   <td>10.0.0.3</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool</td> 
   <td>10.0.0.13</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool</td> 
   <td>10.0.0.17</td> 
  </tr> 
 </tbody> 
</table>

在数据量不是很多的情况下，我们可以将数据库进行读写分离，以应对高并发的需求，通过水平扩展从库，来缓解查询的压力。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70][]

**创建数据**

> 主库里面执行以下的数据库初始化脚本：
> 
>     USE `cool`;
>     
>     DROP TABLE IF EXISTS `user`;
>     
>     CREATE TABLE `user` (
>       `id` int(12) NOT NULL AUTO_INCREMENT,
>       `username` varchar(12) NOT NULL,
>       `password` varchar(30) NOT NULL,
>       PRIMARY KEY (`id`),
>       UNIQUE KEY `idx-username` (`username`)
>     ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
> 
> 上边“主从创建”中，主从数据库已经搭建好了，所以执行完上面的脚本后，2个从库应该也有user表。

**搭建工程**

> 本篇文章中使用Spring Boot 2.0.3+MyBatis+Druid+Sharding-JDBC+MySQL进行读写分离的案件讲解。
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1][]
> 
> pom.xml添加依赖
> 
>     <dependency>
>                 <groupId>org.springframework.boot</groupId>
>                 <artifactId>spring-boot-starter-web</artifactId>
>             </dependency>
>     
>             <dependency>
>                 <groupId>org.mybatis.spring.boot</groupId>
>                 <artifactId>mybatis-spring-boot-starter</artifactId>
>                 <version>1.3.2</version>
>             </dependency>
>     
>             <dependency>
>                 <groupId>mysql</groupId>
>                 <artifactId>mysql-connector-java</artifactId>
>             </dependency>
>     
>             <dependency>
>                 <groupId>com.alibaba</groupId>
>                 <artifactId>druid-spring-boot-starter</artifactId>
>                 <version>1.1.10</version>
>             </dependency>
>     
>             <dependency>
>                 <groupId>io.shardingsphere</groupId>
>                 <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
>                 <version>3.1.0</version>
>             </dependency>

**application.yml**

> SpringBoot工程配置文件application.yml做以下的配置：
> 
>     sharding:
>       jdbc:
>         dataSource:
>           # 数据库的名称(多个数据源的名称，与下边同等级的配置项名字相同)
>           names: db-test0,db-test1,db-test2
>           # 配置主库
>           db-test0: #org.apache.tomcat.jdbc.pool.DataSource
>             type: com.alibaba.druid.pool.DruidDataSource
>             driverClassName: com.mysql.jdbc.Driver
>             url: jdbc:mysql://10.0.0.3:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
>             username: root
>             password:
>             #最大连接数
>             maxPoolSize: 20
>           db-test1: # 配置第一个从库
>             type: com.alibaba.druid.pool.DruidDataSource
>             driverClassName: com.mysql.jdbc.Driver
>             url: jdbc:mysql://10.0.0.13:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
>             username: root
>             password:
>             maxPoolSize: 20
>           db-test2: # 配置第二个从库
>             type: com.alibaba.druid.pool.DruidDataSource
>             driverClassName: com.mysql.jdbc.Driver
>             url: jdbc:mysql://10.0.0.17:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
>             username: root
>             password:
>             maxPoolSize: 20
>         config:
>           masterslave: # 配置读写分离
>             load-balance-algorithm-type: round_robin # 配置从库选择策略(轮询(round_robin)/随机(random))
>             name: db1s2
>             master-data-source-name: db-test0    #主数据源名称
>             slave-data-source-names: db-test1,db-test2    #从数据源名称，多个用逗号隔开
>         props:
>           sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
>             show: true

**案例验证**

> 两个接口
> 
>     @RestController
>     public class UserController {
>     
>         @Autowired
>         private UserService userService;
>     
>         @GetMapping("/users")
>         public Object list() {
>             return userService.list();
>         }
>     
>         @GetMapping("/add")
>         public Object add(@RequestParam Integer id,@RequestParam String username,@RequestParam String  password) {
>             User user = new User();
>             user.setId(id);
>             user.setUsername(username);
>             user.setPassword(password);
>             return userService.addUser(user);
>         }
>     }
> 
> 上边“主从创建”中，已经开启了数据库的CRUD日志，日志目录在/var/lib/mysql目录下。
> 
> 调用2个接口，可以在主库对应主机的日志目录下查看插入数据的日志：
> 
>     2019-06-20T02:50:25.183174Z	 2030 Query	select @@session.transaction_read_only
>     2019-06-20T02:50:25.193506Z	 2030 Query	INSERT INTO user (
>               id, username, password
>             )
>             VALUES (
>             134,
>             'forezp134',
>             '1233edwd'
>             )
> 
> 从库对应主机的日志目录下查看查询数据的日志：
> 
>     2019-06-20T02:41:28.450643Z	 7367 Query	SELECT u.* FROM user u
> 
> 这就说明，Sharding-JDBC实现了数据库的读写分离。

# 读写分离+分库分表 #

**其他网址**

> [Sharding-JDBC教程：SpringBoot整合Sharding-JDBC实现分库分表+读写分离\_方志朋的专栏-CSDN博客][Sharding-JDBC_SpringBoot_Sharding-JDBC_-CSDN 1]
> 
> [ShardingJDBC-分库分表\_wolfcode\_cn的博客-CSDN博客\_shardingjdbc分库分表][ShardingJDBC-_wolfcode_cn_-CSDN_shardingjdbc]

在数据量达到500万的时候，这时数据量预估千万级别，我们可以将数据进行分表存储。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2][]

在数据量继续扩大，这时可以考虑分库分表，将数据存储在不同数据库的不同表中，如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3][]

**案例详解**

> 本案例有6个数据库，两个主库，四个从库，信息如下：
> 
> <table style="width:500px;"> 
>  <tbody> 
>   <tr> 
>    <th>数据库类型</th> 
>    <th>数据库</th> 
>    <th>ip</th> 
>   </tr> 
>   <tr> 
>    <td>主</td> 
>    <td>cool</td> 
>    <td>10.0.0.3</td> 
>   </tr> 
>   <tr> 
>    <td>从</td> 
>    <td>cool</td> 
>    <td>10.0.0.13</td> 
>   </tr> 
>   <tr> 
>    <td>从</td> 
>    <td>cool</td> 
>    <td>10.0.0.17</td> 
>   </tr> 
>   <tr> 
>    <td>主</td> 
>    <td>cool2</td> 
>    <td>10.0.0.3</td> 
>   </tr> 
>   <tr> 
>    <td>从</td> 
>    <td>cool2</td> 
>    <td>10.0.0.13</td> 
>   </tr> 
>   <tr> 
>    <td>从</td> 
>    <td>cool2</td> 
>    <td>10.0.0.17</td> 
>   </tr> 
>  </tbody> 
> </table>

**创建数据**

在主库主机的Mysql执行以下脚本，分别为数据库cool和cool2创建5个表，这5个表分别为user\_0、user\_1、user\_2、user\_3、user\_4。执行的脚本如下：

USE `cool`;
    
    
    /*Table structure for table `user_0` */
    
    DROP TABLE IF EXISTS `user_0`;
    
    CREATE TABLE `user_0` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_1` */
    
    DROP TABLE IF EXISTS `user_1`;
    
    CREATE TABLE `user_1` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_2` */
    
    DROP TABLE IF EXISTS `user_2`;
    
    CREATE TABLE `user_2` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_3` */
    
    DROP TABLE IF EXISTS `user_3`;
    
    CREATE TABLE `user_3` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=148 DEFAULT CHARSET=utf8;
    CREATE TABLE `user_4` (
      `id` INT(12) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(12) NOT NULL,
      `password` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=INNODB AUTO_INCREMENT=148 DEFAULT CHARSET=utf8;
    
    
    USE `cool2`;
    
    
    /*Table structure for table `user_0` */
    
    DROP TABLE IF EXISTS `user_0`;
    
    CREATE TABLE `user_0` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_1` */
    
    DROP TABLE IF EXISTS `user_1`;
    
    CREATE TABLE `user_1` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_2` */
    
    DROP TABLE IF EXISTS `user_2`;
    
    CREATE TABLE `user_2` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_3` */
    
    DROP TABLE IF EXISTS `user_3`;
    
    CREATE TABLE `user_3` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `username` varchar(12) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=148 DEFAULT CHARSET=utf8;
    
    /*Table structure for table `user_3` */
    
    DROP TABLE IF EXISTS `user_4`;
    
    CREATE TABLE `user_4` (
      `id` INT(12) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(12) NOT NULL,
      `password` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx-username` (`username`)
    ) ENGINE=INNODB AUTO_INCREMENT=148 DEFAULT CHARSET=utf8;

案例的工程是在读写分离的工程基础上进行改造，其中pom文件的依赖包不变

sharding:
        jdbc:
            config:
                sharding:
                    default-database-strategy:
                        inline:
                            algorithm-expression: ds_$->{id % 2}
                            sharding-column: id
                    master-slave-rules:
                        ds_0:
                            master-data-source-name: ds-master-0
                            slave-data-source-names: ds-master-0-slave-0, ds-master-0-slave-1
                        ds_1:
                            master-data-source-name: ds-master-1
                            slave-data-source-names: ds-master-1-slave-0, ds-master-1-slave-1
                    tables:
                        user:
                            actual-data-nodes: ds_$->{0..1}.user_$->{0..4}
                            key-generator-column-name: id
                            table-strategy:
                                inline:
                                    algorithm-expression: user_$->{id % 5}
                                    sharding-column: id
            datasource:
                ds-master-0:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.3:3306/cool?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
                    username: root
                ds-master-0-slave-0:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.13:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
                    username: root
                ds-master-0-slave-1:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.17:3306/cool?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
                    username: root
                ds-master-1:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.3:3306/cool2?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
                    username: root
                ds-master-1-slave-0:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.13:3306/cool2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
                    username: root
                ds-master-1-slave-1:
                    driver-class-name: com.mysql.jdbc.Driver
                    password: ''
                    type: com.alibaba.druid.pool.DruidDataSource
                    url: jdbc:mysql://10.0.0.17:3306/cool2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
                    username: root
                names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-0-slave-1,ds-master-1-slave-0,ds-master-1-slave-1

在上面的配置中，其中sharding.jdbc.datasource部分是配置数据库的信息，配置了6个数据库。

*  sharding.jdbc.config.sharding.master-slave-rules.ds\_0.master-data-source-name配置的是ds\_0区的的主库名称，同理ds\_1。
 *  sharding.jdbc.config.sharding.master-slave-rules.ds\_0.slave-data-source-names配置的是ds\_0区的的从库名称，同理ds\_1。
 *  sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column配置的分库的字段，本案例是根据id进行分。
 *  sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression配置的分库的逻辑，根据id%2进行分。
 *  sharding.jdbc.config.sharding.tables.user.actual-data-nodes配置的是user表在真实数据库中的位置，ds\_KaTeX parse error: Expected group after '\_' at position 14: ->\{0..1\}.user\_̲->\{0…4\}表示
 *  数据在ds\_0和ds\_1中的user\_0、user\_1、user\_2、user\_3、user\_4中。
 *  sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column，配置user表数据切分的字段
 *  sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user\_$->\{id % 5\}，配置user表数据切分的策略。
 *  sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id 自动生成id。

**修改Spring Boot启动类的注解**

> @SpringBootApplication，加上exclude=\{DataSourceAutoConfiguration.class\}
> 
>     @SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
>     @EnableConfigurationProperties
>     public class ShardingJdbcDbMsTblApplication {
>         public static void main(String[] args) {
>             SpringApplication.run(ShardingJdbcDbMsTblApplication.class, args);
>         }
>     }

**测试**

> 上边“主从创建”中，已经开启了数据库的CRUD日志，日志目录在/var/lib/mysql目录下。
> 
> 调用2个接口，可以在主库对应主机的日志目录下查看插入数据的日志：
> 
> 从库对应主机的日志目录下查看查询数据的日志：
> 
> 这就说明，Sharding-JDBC实现了数据库的分库分表和读写分离。

[Sharding-JDBC--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/106648587
[Link 1]: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/
[Link 2]: https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/inline-expression/
[Sharding-JDBC_Mysql_-CSDN_shardingjdbcmysqll]: https://blog.csdn.net/forezp/article/details/94173427
[Sharding-JDBC_SpringBoot_Sharding-JDBC_-CSDN]: https://blog.csdn.net/forezp/article/details/94174114
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190629164730341.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190629164651787.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[Sharding-JDBC_SpringBoot_Sharding-JDBC_-CSDN 1]: https://blog.csdn.net/forezp/article/details/94343671
[ShardingJDBC-_wolfcode_cn_-CSDN_shardingjdbc]: https://blog.csdn.net/wolfcode_cn/article/details/100115550
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190630180156149.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190630180217231.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70