1 需求描述
电商平台商品列表展示，每个列表项中除了包含商品基本信息、商品描述信息之外，还包括了商品所属的店铺信息，如下：
本案例实现功能如下：
1、添加商品
2、商品分页查询
4、商品统计
2 数据库设计
数据库设计如下，其中商品与店铺信息之间进行了垂直分库，分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)；商品信息还进行了垂直分表，分为了商品基本信息(product_info)和商品描述信息(product_descript)，地理区域信息(region)作为公共表，冗余在两库中：
考虑到商品信息的数据增长性，对PRODUCT_DB(商品库)进行了水平分库，分片键使用店铺id，分片策略为店铺ID%2 + 1，因此商品描述信息对所属店铺ID进行了冗余；
对商品基本信息(product_info)和商品描述信息(product_descript)进行水平分表，分片键使用商品id，分片策略为商品ID%2 + 1,并将为这两个表设置为绑定表，避免笛卡尔积join；
为避免主键冲突，ID生成策略采用雪花算法来生成全局唯一ID，最终数据库设计为下图：
要求使用读写分离来提升性能，可用性。
3 环境说明
操作系统： Win10
数据库： MySQL-5.7.25
JDK ：64位 jdk1.8.0_201
应用框架： spring-boot-2.1.3.RELEASE，Mybatis3.5.0
Sharding-JDBC ：sharding-jdbc-spring-boot-starter-4.0.0-RC1
4.环境准备
4.1.mysql主从同步(windows)
参考读写分离章节，对以下库进行主从同步配置：
Myqsql使用Sharding-JDBC分表分库和读写分离
# 设置需要同步的数据库 binlog‐do‐db=store_db binlog‐do‐db=product_db_1 binlog‐do‐db=product_db_2
4.2.初始化数据库
创建store_db数据库，并执行以下脚本创建表：
DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `id` bigint(20) NOT NULL COMMENT 'id', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域编码', `region_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域名称', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理区域级别(省、市、县)', `parent_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 `region` VALUES (1, '110000', '北京', 0, NULL); INSERT INTO `region` VALUES (2, '410000', '河南省', 0, NULL); INSERT INTO `region` VALUES (3, '110100', '北京市', 1, '110000'); INSERT INTO `region` VALUES (4, '410100', '郑州市', 1, '410000'); 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, 'XX零食店', 4, '110100'); INSERT INTO `store_info` VALUES (2, 'XX饮品店', 3, '410100');
创建product_db_1、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', 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_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; DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `id` bigint(20) NOT NULL COMMENT 'id', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域编码', `region_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域名称', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理区域级别(省、市、县)', `parent_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 `region` VALUES (1, '110000', '北京', 0, NULL); INSERT INTO `region` VALUES (2, '410000', '河南省', 0, NULL); INSERT INTO `region` VALUES (3, '110100', '北京市', 1, '110000'); INSERT INTO `region` VALUES (4, '410100', '郑州市', 1, '410000');
5 实现步骤
5.1搭建maven工程
（1）搭建工程maven工程shopping，导入资料中基础代码shopping，以dbsharding为总体父工程，并做好
spring boot相关配置。
（2）引入maven依赖
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding‐jdbc‐spring‐boot‐starter</artifactId> <version>4.0.0‐RC1</version> </dependency>
5.2 分片配置
既然是分库分表，那么就需要定义多个真实数据源，每一个数据库链接信息就是一个数据源定义，如：
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://localhost:3306/store_db?useUnicode=true spring.shardingsphere.datasource.m0.username = root spring.shardingsphere.datasource.m0.password = root
m0，就是这个真实数据源的名称，然后需要告诉Sharding-JDBC，咱们有哪些真实数据源，如：
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2
如果需要配置读写分离，还需要告诉Sharding-JDBC，这么多真实数据源，那几个是一套读写分离？也就是定义主从逻辑数据源：
spring.shardingsphere.sharding.master‐slave‐rules.ds0.master‐data‐source‐name=m0 spring.shardingsphere.sharding.master‐slave‐rules.ds0.slave‐data‐source‐names=s0
若我们已经对 m0和s0做了mysql主从同步，那我们需要告诉Sharding-JDBC，m0、s0为一组主从同步数据源，其
中m0为主，s0为从，并且定义名称为ds0，这个ds0就是主从逻辑数据源。
最终配置如下，具体的分库分表策略参考注释内容：
# 真实数据源定义 m为主库 s为从库 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://localhost:3306/store_db?useUnicode=true 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://localhost:3306/product_db_1? useUnicode=true 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://localhost:3306/product_db_2? useUnicode=true 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://localhost:3307/store_db?useUnicode=true 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://localhost:3307/product_db_1? useUnicode=true 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://localhost:3307/product_db_2? useUnicode=true spring.shardingsphere.datasource.s2.username = root spring.shardingsphere.datasource.s2.password = root # 主库从库逻辑数据源定义 ds0为store_db ds1为product_db_1 ds2为product_db_2 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 # 默认分库策略，以store_info_id为分片键，分片策略为store_info_id % 2 + 1，也就是store_info_id为双数的 数据进入ds1，为单数的进入ds2 spring.shardingsphere.sharding.default‐database‐strategy.inline.sharding‐column = store_info_id spring.shardingsphere.sharding.default‐database‐strategy.inline.algorithm‐expression = ds$‐> {store_info_id % 2 + 1} # 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生成为雪花算法，为双数的数据进入product_info_1表，为单数的进入product_info_2 表 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生成为雪花算法，product_info_id为双数的数据进入product_descript_1表，为单 数的进入product_descript_2表 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.column=id spring.shardingsphere.sharding.tables.product_descript.key‐generator.type=SNOWFLAKE # 设置product_info,product_descript为绑定表 spring.shardingsphere.sharding.binding‐tables[0] = product_info,product_descript # 设置region为广播表(公共表)，每次更新操作会发送至所有数据源 spring.shardingsphere.sharding.broadcast‐tables=region # 打开sql输出日志 spring.shardingsphere.props.sql.show = true
![【JDBC实战】水果库存系统 [设计阶段]](https://ucc.alicdn.com/pic/developer-ecology/ca87051385b44859ac0493fb46e913ea.jpg?x-oss-process=image/resize,h_160,m_lfit)
