分表分库-Sharding-JDBC-入门案例

1、需求描述

使用Sharding-JDBC实现电商平台的商品列表展示，每个列表项中除了包含商品基本信息、商品描述信息之外，还包括了商品所属的店铺信息，如下所示：

2、开发环境

数据库：MySQL-5.7.25
JDK：1.8.0_201
应用框架：spring-boot-2.1.3.RELEASE，Mybatis 3.5.0
Sharding-JDBC：sharding-jdbc-spring-boot-starter-4.0.0-RC1

3、数据库设计

商品与店铺信息之间进行了垂直分库，拆分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)；商品信息还进行了垂直分表，拆分为了商品基本信息(store_info)和商品描述信息(product_info)：

考虑到商品信息的数据增长性，对PRODUCT_DB(商品库)进行了水平分库，分片键使用店铺id，分片策略为店铺ID%2 + 1，对商品基本信息(product_info)和商品描述信息(product_descript)进行水平分表，分片键使用商品id，分片策略为商品ID%2 + 1,并将这两个表设置为绑定表。为避免主键冲突，ID生成策略采用雪花算法来生成全局唯一ID，雪花算法类似于UUID，但是它能生成有序的ID，有利于提高数据库性能。最终数据库设计如下图所示：

4、搭建数据库环境--MySQL主从同步

5、初始化数据库

登录并连接主库，然后执行如下脚本：

1. 执行store_db.sql创建store数据库和store_info表
2. 执行product_db_1.sql创建product_db_1数据库和其中的四张表
3. 执行product_db_2.sql创建product_db_2数据库和其中的四张表

此时观察从库，我们会发现从库中已经存在上述数据库和表，说明主从数据同步已经发挥了作用。

store_db.sql

DROP DATABASE IF EXISTS `store_db`;
CREATE DATABASE `store_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE `store_db`;

DROP TABLE IF EXISTS `store_info`;
CREATE TABLE `store_info` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`store_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺名称',
`reputation` INT(11) NULL DEFAULT NULL COMMENT '信誉等级',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺所在地',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `store_info` VALUES (1, '黑马铺子', 4, '110100');
INSERT INTO `store_info` VALUES (2, '黑马超市', 3, '410100');

product_db_1.sql

DROP DATABASE IF EXISTS `product_db_1`;
CREATE DATABASE `product_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE `product_db_1`;


DROP TABLE IF EXISTS `product_descript_1`;
CREATE TABLE `product_descript_1` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_info_2`;
CREATE TABLE `product_info_2` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

product_db_2.sql

DROP DATABASE IF EXISTS `product_db_2`;
CREATE DATABASE `product_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE `product_db_2`;


DROP TABLE IF EXISTS `product_descript_1`;
CREATE TABLE `product_descript_1` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (
`id` BIGINT(20) NOT NULL COMMENT 'id',
`product_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
INDEX `FK_Reference_2`(`product_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `product_info_2`;
CREATE TABLE `product_info_2` (
`product_info_id` BIGINT(20) NOT NULL COMMENT 'id',
`store_info_id` BIGINT(20) NULL DEFAULT NULL COMMENT '所属店铺id',
`product_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`spec` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格',
`region_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地',
`price` DECIMAL(10, 0) NULL DEFAULT NULL COMMENT '商品价格',
`image_url` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
PRIMARY KEY (`product_info_id`) USING BTREE,
INDEX `FK_Reference_1`(`store_info_id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

6 功能实现

一、parent pom.xml

<?xml version="1.0" encoding="UTF-8"?>
"http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    4.0.0

    com.itheima.dbsharding
    dbsharding
    pom
    1.0-SNAPSHOT

    
        sharding-jdbc-demo
    

    
        org.springframework.boot
        spring-boot-starter-parent
        2.1.3.RELEASE
    

    
        UTF-8
        UTF-8
        1.8
    

    
        
            
                io.springfox
                springfox-swagger2
                2.9.2
            

            
                io.springfox
                springfox-swagger-ui
                2.9.2
            

            
                org.projectlombok
                lombok
                1.18.0
            

            
                javax.interceptor
                javax.interceptor-api
                1.2
            

            
                mysql
                mysql-connector-java
                5.1.47
            

            
                org.mybatis.spring.boot
                mybatis-spring-boot-starter
                2.0.0
            

            
                com.alibaba
                druid-spring-boot-starter
                1.1.16
            

            
                org.apache.shardingsphere
                sharding-jdbc-spring-boot-starter
                4.0.0-RC1
            

            
                com.baomidou
                mybatis-plus-boot-starter
                3.1.0
            

            
                com.baomidou
                mybatis-plus-generator
                3.1.0
            

            
                org.mybatis
                mybatis-typehandlers-jsr310
                1.0.2
            

        
    


    
        ${project.name}
        
            
                src/main/resources
                true
                
                    **/*
                
            
            
                src/main/java
                
                    **/*.xml
                
            
        
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            

            
                org.apache.maven.plugins
                maven-compiler-plugin
                
                    1.8
                    1.8
                
            

            
                maven-resources-plugin
                
                    utf-8
                    true

二、children pom.xml

<?xml version="1.0" encoding="UTF-8"?>
"http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
        dbsharding
        com.itheima.dbsharding
        1.0-SNAPSHOT
    

    4.0.0
    sharding-jdbc-demo

    

        
            org.springframework.boot
            spring-boot-starter-web
        

        
            org.springframework.boot
            spring-boot-starter-actuator
        

        
            org.springframework.boot
            spring-boot-configuration-processor
            true
        

        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
        

        
            com.alibaba
            druid-spring-boot-starter
        

        
            io.springfox
            springfox-swagger2
        

        
            io.springfox
            springfox-swagger-ui
        

        
            org.projectlombok
            lombok
        

        
            mysql
            mysql-connector-java
        

        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
        

        
            com.alibaba
            druid-spring-boot-starter
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter

三、配置文件

server.port=56081

spring.application.name = sharding-jdbc-demo
server.servlet.context-path = /sharding-jdbc-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true

# 开启swagger
swagger.enable = true

# 同名bean允许覆盖
spring.main.allow-bean-definition-overriding=true

# 将带有下划线的表字段映射为驼峰格式的实体类属性
mybatis.configuration.map-underscore-to-camel-case = true

# 定义真实数据源(6个)
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://192.168.56.13:3307/store_db?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = root

spring.shardingsphere.datasource.m1.type =com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.56.13:3307/product_db_1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://192.168.56.13:3307/product_db_2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root
spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://192.168.56.13:3308/store_db?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = root
spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.56.13:3308/product_db_1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = root
spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://192.168.56.13:3308/product_db_2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = root

# 定义逻辑数据源(主从对应关系)
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2

# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = store_info_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithmexpression = ds$->{store_info_id % 2+1}

# 分表策略  3张表
# store_info分表策略，固定分配至ds0的store_info真实表
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column=  id
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression=store_info
# product_info分表策略，分布在ds1,ds2的product_info_1和product_info_2表 ，分片策略为product_info_id % 2+1，product_info_id采用雪花算法
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2}
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column= product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{product_info_id % 2+1}
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2}
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id %2+1}
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
# 设置绑定表
spring.shardingsphere.sharding.binding-tables = product_info,product_descript

# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

7 配置文件详解

一、基础配置

二、Sharding-JDBC相关配置

# 定义真实数据源(6个)

# 定义逻辑数据源(主从对应关系)

#从库的s0从主的m0同步数据

# 分库策略

# 分表策略 3张表
# store_info分表策略，固定分配至ds0的store_info真实表

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法和上面一样

# 设置绑定表

三、相关增删改

java 代码和之前用mybatis 时候一样

实际表有后缀

只写配置文件绑定的逻辑表就完事了

四、总结

使用Sharding-JDBC关键全在配置文件，实际代码和之前用单表时的差不多

分库分表

分表分库-Sharding-JDBC-入门案例

1、需求描述

2、开发环境

3、数据库设计

4、搭建数据库环境--MySQL主从同步

5、初始化数据库

store_db.sql

product_db_1.sql

product_db_2.sql

6 功能实现

一、parent pom.xml

二、children pom.xml

三、配置文件

7 配置文件详解

一、基础配置

二、Sharding-JDBC相关配置

# 定义真实数据源(6个)

# 定义逻辑数据源(主从对应关系)

#从库的s0从主的m0同步数据

# 分库策略

# 分表策略 3张表
# store_info分表策略，固定分配至ds0的store_info真实表

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法和上面一样

# 设置绑定表

三、相关增删改

四、总结

相关

mysql进阶学习三之mycat读写分离和分库分表

MySQL之分库分表

分库分表-Sharding-JDBC

分库分表带来的问题及解决方案

在线进行分库分表中间件的平滑升级，正所谓艺高人胆大

sharing-jdbc实现读写分离及分库分表

分库分表数据库中间件对比

常见的分库分表中间件

分库分表利器——sharding-sphere

MySQL订单分库分表多维度查询

ShardingSphere-JDBC进行分库分表

如何使用sharding-sphere完成读写分离和分库分表?

标签

分表分库-Sharding-JDBC-入门案例

1、需求描述

2、开发环境

3、数据库设计

4、搭建数据库环境--MySQL主从同步

5、初始化数据库

store_db.sql

product_db_1.sql

product_db_2.sql

6 功能实现

一、parent pom.xml

二、children pom.xml

三、配置文件

7 配置文件详解

一、基础配置

二、Sharding-JDBC相关配置

# 定义真实数据源(6个)

# 定义逻辑数据源(主从对应关系)

#从库的s0从主的m0同步数据

# 分库策略

# 分表策略 3张表# store_info分表策略，固定分配至ds0的store_info真实表

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法 和上面一样

# 设置绑定表

三、相关增删改

四、总结

相关

# 分表策略 3张表
# store_info分表策略，固定分配至ds0的store_info真实表

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法和上面一样