9.案例
9.1.需求描述
电商平台商品列表展示，每个列表项中除了包含商品基本信息、商品描述信息之外，还包括了商品所属的店铺信息，如下：
本案例实现功能如下：
1、添加商品
2、商品分页查询
4、商品统计
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-boot-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');
INSERT INTO `region` VALUES (4, '410100', '郑州市', 1, '410000');
9.5.实现步骤
9.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>
9.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
9.5.3 添加商品
实体类，参考基础工程：
DAO实现
@Mapper
@Component
public interface ProductDao {
//添加商品基本信息
@Insert("insert into product_info(store_info_id,product_name,spec,region_code,price)
value(#{
storeInfoId
}, # {
productName
}, # {
spec
}, # {
regionCode
}, # {
price
})
")
@Options(useGeneratedKeys = true, keyProperty = "productInfoId", keyColumn = "id") int insertProductInfo(ProductInfo productInfo);
//添加商品描述信息
@Insert("insert into product_descript(product_info_id,descript,store_info_id) value(#{productInfoId}, #{descript}, #{storeInfoId})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id") int insertProductDescript(ProductDescript productDescript);
}
service实现，针对垂直分库的两个库，分别实现店铺服务、商品服务
@Service
public class ProductServiceImpl implements ProductService {
@Autowired
private ProductDao productDao;
@Override
@Transactional
public void createProduct(ProductInfo product) {
ProductDescript productDescript = new ProductDescript();
productDescript.setDescript(product.getDescript());
productDao.insertProductInfo(product); //新增商品基本信息
productDescript.setProductInfoId(product.getProductInfoId());
productDescript.setStoreInfoId(product.getStoreInfoId()); //冗余店铺信息
productDao.insertProductDescript(productDescript); //新增商品描述信息
}
}
controller实现：
/**
* 卖家商品展示
*/
@RestController
public class SellerController {
@Autowired
private ProductService productService;
@PostMapping("/products")
public String createProject(@RequestBody ProductInfo productInfo) {
productService.createProduct(productInfo);
return "创建成功!";
}
}
单元测试：
@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingJdbcDemoBootstrap.class)
public class ShardingTest {
@Autowired
ProductService productService;
@Test
public void testCreateProduct() {
for (long i = 1; i < 10; i++) {
//store_info_id,product_name,spec,region_code,price,image_url
ProductInfo productInfo = new ProductInfo();
productInfo.setProductName("Java编程思想" + i);
productInfo.setDescript("Java编程思想是一本非常好的Java教程" + i);
productInfo.setRegionCode("110000");
productInfo.setStoreInfoId(1);
productInfo.setPrice(new BigDecimal(i));
productService.createProduct(productInfo);
}
}
...
}
这里使用了sharding-jdbc所提供的全局主键生成方式之一雪花算法，来生成全局业务唯一主键。
通过添加商品接口新增商品进行分库验证，store_info_id为偶数的数据在product_db_1,为奇数的数据在product_db_2。
通过添加商品接口新增商品进行分表验证，product_id为偶数的数据在product_info_1、product_descript_1，为奇数的数据在product_info_2、product_descript_2。
9.5.4 查询商品
Dao实现：
在ProductDao中定义商品查询方法：
@Select("select i.*, d.descript, r.region_name placeOfOrigin " +
"from product_info i join product_descript d on i.id = d.product_info_id " +
"join region r on r.region_code = i.region_code order by i.id desc limit #{start},# {
pageSize
}
")
List < ProductInfo > selectProductList(@Param("start") int start, @Param("pageSize") int pageSize);
Service实现：
在ProductServiceImpl定义商品查询方法：
@Override
public List < ProductInfo > queryProduct(int page, int pageSize) {
int start = (page‐ 1) * pageSize;
return productDao.selectProductList(start, pageSize);
}
Controller实现：
@GetMapping(value = "/products/{page}/{pageSize}")
public List < ProductInfo > queryProduct(@PathVariable("page") int page, @PathVariable("pageSize") int pageSize) {
return productService.queryProduct(page, pageSize);
}
单元测试：
@Test
public void testSelectProductList() {
List < ProductInfo > productInfos = productService.queryProduct(1, 10);
System.out.println(productInfos);
}
通过查询商品列表接口，能够查询到所有分片的商品信息，关联的地理区域，店铺信息正确。
总结：
分页查询是业务中最常见的场景，Sharding-jdbc支持常用关系数据库的分页查询，不过Sharding-jdbc的分页功能比较容易让使用者误解，用户通常认为分页归并会占用大量内存。 在分布式的场景中，将 LIMIT 10000000 , 10改写为 LIMIT 0, 10000010 ，才能保证其数据的正确性。 用户非常容易产生ShardingSphere会将大量无意义的数据加载至内存中，造成内存溢出风险的错觉。 其实大部分情况都通过流式归并获取数据结果集，因此ShardingSphere会通过结果集的next方法将无需取出的数据全部跳过，并不会将其存入内存。
但同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到Sharding-Jdbc的内存空间。 因此，采用LIMIT这种方式分页，并非最佳实践。 由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案，例如：
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;
或通过记录上次查询结果的最后一条记录的ID进行下一页的查询，例如：
SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;
排序功能是由Sharding-jdbc的排序归并来完成，由于在SQL中存在 ORDER BY 语句，因此每个数据结果集自身是有序的，因此只需要将数据结果集当前游标指向的数据值进行排序即可。 这相当于对多个有序的数组进行排序，归并排序是最适合此场景的排序算法。
9.5.5 统计商品
本小节实现商品总数统计，商品分组统计
Dao实现，在ProductDao中定义：
//总数统计
@Select("select count(1) from product_info")
int selectCount();
//分组统计
@Select("select count(1) as num from product_info group by region_code having num>1 ORDER BY
region_code ASC ")
List < Map > selectProductGroupList();
单元测试：
@Test
public void testSelectCount() {
int i = productDao.selectCount();
System.out.println(i);
}
@Test
public void testSelectGroupList() {
List < Map > maps = productDao.selectProductGroupList();
System.out.println(maps);
}
总结：
分组统计
分组统计也是业务中常见的场景，分组功能的实现由Sharding-jdbc分组归并完成。分组归并的情况最为复杂，它分为流式分组归并和内存分组归并。 流式分组归并要求SQL的排序项与分组项的字段必须保持一致，否则只能通过内存归并才能保证其数据的正确性。
举例说明，假设根据科目分片，表结构中包含考生的姓名（为了简单起见，不考虑重名的情况）和分数。通过SQL
获取每位考生的总分，可通过如下SQL：
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;
在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游标所指向的数据值，因此可以采用流式归并。如下图所示。
进行归并时，逻辑与排序归并类似。 下图展现了进行next调用的时候，流式分组归并是如何进行的。
通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_java将会被弹出队列，并且将分组值同为“Jetty”的其他结果集中的数据一同弹出队列。 在获取了所有的姓名为“Jetty”的同学的分数之后，进行累加操作，那么，在第一次next调用结束后，取出的结果集是“Jetty”的分数总和。 与此同时，所有的数据结果集中的游标都将下移至数据值“Jetty”的下一个不同的数据值，并且根据数据结果集当前游标指向的值进行重排序。 因此，包含名字顺着第二位的“John”的相关数据结果集则排在的队列的前列。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/131117.html
