分库分表之第五篇 / 四六文摘

分库分表之第五篇

9.案例

9.案例

9.1.需求描述

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

本案例实现功能如下:
1、添加商品
2、商品分页查询
3、商品统计

9.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，最终数据库设计为下图:

要求使用读写分离来提升性能，可用性。

9.3.环境说明

操作系统：win10
数据库：MySQL-5.7.25
JDK ：64位 jdk1.8.0_201
应用框架：spring-bbot-2.1.3.RELEASE，MyBatis3.5.0
Sharding-JDBC ：sharding-jdbc-spring-boot-starter-4.0.0-RC1

9.4.环境准备

9.4.1.mysql主从同步（windows）

参考读写分离章节，对以下库进行主从同步配置：

 # 设置需要同步的数据库  binlog‐do‐db=store_db  binlog‐do‐db=product_db_1  binlog‐do‐db=product_db_2

9.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')

来源：https://www.icode9.com/content-4-766101.html

分库分表之第五篇