Sharding-JDBC教程：Spring Boot整合Sharding-JDBC实现分库分表+读写分离

╰+哭是因爲堅強的太久メ 2021-12-19 16:15 248阅读 0赞

> 转载请标明出处：  
> http://blog.csdn.net/forezp/article/details/94343671  
> 本文出自[方志朋的博客][Link 1]

**个人博客纯净版[https://www.fangzhipeng.com/db/2019/07/01/shardingjdbc-dbs-tbls-ms.html][https_www.fangzhipeng.com_db_2019_07_01_shardingjdbc-dbs-tbls-ms.html]**

在上一篇文章介绍了如何使用Sharding-jdbc进行分库+读写分离，这篇文章将讲述如何使用Sharding-jdbc进行分库分表+读写分离。

## 架构回顾 ##

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]

## 案例详解 ##

本案例有6个数据库，两个主库，四个从库，信息如下：

<table> 
 <thead> 
  <tr> 
   <th align="center">数据库类型</th> 
   <th align="center">数据库</th> 
   <th align="center">ip</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">主</td> 
   <td align="center">cool</td> 
   <td align="center">10.0.0.3</td> 
  </tr> 
  <tr> 
   <td align="center">从</td> 
   <td align="center">cool</td> 
   <td align="center">10.0.0.13</td> 
  </tr> 
  <tr> 
   <td align="center">从</td> 
   <td align="center">cool</td> 
   <td align="center">10.0.0.17</td> 
  </tr> 
  <tr> 
   <td align="center">主</td> 
   <td align="center">cool2</td> 
   <td align="center">10.0.0.3</td> 
  </tr> 
  <tr> 
   <td align="center">从</td> 
   <td align="center">cool2</td> 
   <td align="center">10.0.0.13</td> 
  </tr> 
  <tr> 
   <td align="center">从</td> 
   <td align="center">cool2</td> 
   <td align="center">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;
    
    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文件的依赖包不变，详情可见源码。

在工程的application中做sharding-jdbc的分库分表配置，代码如下：

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

## 测试 ##

测试同上一篇文章，这里不在重复。

## 源码下载 ##

https://github.com/forezp/SpringBootLearning/tree/master/sharding-jdbc-example/sharding-jdbc-db-ms-tbl

## 资料参考 ##

https://github.com/apache/incubator-shardingsphere-example/releases/tag/3.1.0.M1

https://shardingsphere.apache.org/document/current/cn/overview/

https://github.com/apache/incubator-shardingsphere

![SouthEast][]  
扫码关注有惊喜

**（转载本站文章请注明作者和出处 [方志朋的博客][Link 2]）**

[Link 1]: http://blog.csdn.net/forezp
[https_www.fangzhipeng.com_db_2019_07_01_shardingjdbc-dbs-tbls-ms.html]: https://www.fangzhipeng.com/db/2019/07/01/shardingjdbc-dbs-tbls-ms.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: /images/20211219/2a2392f9ba8743d0947617b6b47bf7c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: /images/20211219/2a61d2d7ddd34ee0abf021f5f13afe7e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9mb3JlenAuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]: /images/20211219/7e4ecaae5a27486bbb7b732986a8785f.png
[SouthEast]: /images/20211219/e5c80ce812aa42e6a1cf129487d419d8.png
[Link 2]: https://www.fangzhipeng.com