分库分表之第五篇

2020-02-11 15:03:41 浏览数 (1)

分库分表之第五篇
  • 9.案例
    • 9.1.需求描述
    • 9.2.数据库设计
    • 9.3.环境说明
    • 9.4.环境准备
      • 9.4.1.mysql主从同步（windows）
      • 9.4.2.初始化数据库
    • 9.5.实现步骤
      • 9.5.1搭建maven工程
      • 9.5.2 分片配置
      • 9.5.3 添加商品
      • 9.5.4 查询商品
      • 9.5.5 统计商品
    • 10. 总结

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）

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

代码语言：javascript复制
 # 设置需要同步的数据库 
 binlog‐do‐db=store_db 
 binlog‐do‐db=product_db_1 
 binlog‐do‐db=product_db_2
9.4.2.初始化数据库

创建store_db数据库，并执行以下脚本创建表 ：

代码语言：javascript复制
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数据库，并分别对两库执行以下脚本创建表:

代码语言：javascript复制
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，并做好Spring boot相关配置。 （2）引入maven依赖

代码语言：javascript复制
<dependency>
<groupId>org.apache.shardingsphere</groupId> 
<artifactId>sharding‐jdbc‐spring‐boot‐starter</artifactId> 
<version>4.0.0‐RC1</version>
</dependency>
9.5.2 分片配置

既然是分库分表，那么就需要定义多个真实数据源，每一个数据库链接信息就是一个数据源定义，如 ：

代码语言：javascript复制
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，咋们有那些真实数据源，如 ：

代码语言：javascript复制
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2

如果需要配置读写分离，还需要告诉Sharding-JDBC，这么多真实数据源，那么有几个是一套读写分离？也就是定义主从逻辑数据源 ：

代码语言：javascript复制
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就是主从逻辑数据源。 最终配置如下，具体的分库分表策略参考注释内容:

代码语言：javascript复制
# 真实数据源定义 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实现

代码语言：javascript复制
@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实现，针对垂直分库的两个库，分别实现店铺服务、商品服务

代码语言：javascript复制
@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实现:

代码语言：javascript复制
/**
* 卖家商品展示 */
   @RestController
   public class SellerController {
       @Autowired
       private ProductService productService;
		@PostMapping("/products")
		public String createProject(@RequestBody ProductInfo productInfo) {
		productService.createProduct(productInfo);
		return "创建成功!"; 
	}

单元测试:

代码语言：javascript复制
@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、produt_descript_2。

9.5.4 查询商品

Dao实现 ： 在ProductDao中定义商品查询方法 ：

代码语言：javascript复制
@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定义商品查询方法 ：

代码语言：javascript复制
 @Override
   public List<ProductInfo> queryProduct(int page,int pageSize) {
	int start = (page‐1)*pageSize;
	return productDao.selectProductList(start,pageSize); 
}

Controller实现 ：

代码语言：javascript复制
@GetMapping(value = "/products/{page}/{pageSize}")
public List<ProductInfo> queryProduct(@PathVariable("page")int page,@PathVariable("pageSize")int pageSize){
	return productService.queryProduct(page,pageSize); 
}

单元测试 ：

代码语言：javascript复制
@Test
public void testSelectProductList(){
	List<ProductInfo> productInfos = productService.queryProduct(1,10);
	System.out.println(productInfos); 
}

通过查询商品列表接口，能够查询到所有分片的商品信息，关联的地理区域，店铺信息正确。 总结 ： 分页查询是业务中最常见的场景，Sharding-jdbc支持常用关系数据库的分页查询，不过Sharding-jdbc的分页功能比较容易让使用者误解，用户通常认为分页归并会占用大量内存。在分布式的场景中，将LIMIT 10000000，10改写为Limit 0，10000000，才能保证其数据的正确性。用户非常容易产生ShardingSphere会将大量无意义的数据加载至内存中，造成内存溢出风险的错觉。其实大部分情况都通过流式归并获取数据结果集，因此Sharding-Sphere会通过结果集的next方法将无需取出的数据全部跳过，并不会将其存入内存。 但同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到Sharding-JDBC的内存空间。因此，采用LIMIT这种方式分页，并非最佳实践。由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案，例如 ：

代码语言：javascript复制
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询，例如 ：

代码语言：javascript复制
SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;

排序功能是由Sharding-jdbc的排序归并来完成，由于在SQL中存在ORDER BY语句，因此每个数据结果集自身是有序的，因此只需要将数据结果集当前游标指向的数据值进行排序即可。这相当于多个有序的数组进行排序，归并排序是最适合此场景的排序算法。

9.5.5 统计商品

本小节实现商品总数统计，商品分组统计 Dao实现，在ProductDao中定义 ：

代码语言：javascript复制
//总数统计
@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();

单元测试 ：

代码语言：javascript复制
	@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 ：

代码语言：javascript复制
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;

在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游标所指向的数据值，因此可以采用流式归并。如下图所示。

进行归并时，逻辑与排序归并类似。 下图展现了进行next调用的时候，流式分组归并是如何进行的。

通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_java将会被弹出队列，并且将分组值 同为“Jetty”的其他结果集中的数据一同弹出队列。 在获取了所有的姓名为“Jetty”的同学的分数之后，进行累加操 作，那么，在第一次next调用结束后，取出的结果集是“Jetty”的分数总和。 与此同时，所有的数据结果集中的游标 都将下移至数据值“Jetty”的下一个不同的数据值，并且根据数据结果集当前游标指向的值进行重排序。 因此，包含 名字顺着第二位的“John”的相关数据结果集则排在的队列的前列。

10. 总结

为什么分库分表?分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。 分库分表方式:垂直分表、垂直分库、水平分库、水平分表 分库分表带来问题:由于数据分散在多个数据库，服务器导致了事务一致性问题、跨节点join问题、跨节点分页、 排序、函数，主键需要全局唯一，公共表。 Sharding-JDBC基础概念:逻辑表，真实表，数据节点，绑定表，广播表，分片键，分片算法，分片策略，主键生 成策略 Sharding-JDBC核心功能:数据分片，读写分离 Sharding-JDBC执行流程: SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并 最佳实践: 系统在设计之初就应该对业务数据的耦合松紧进行考量，从而进行垂直分库、垂直分表，使数据层架构清晰明了。 若非必要，无需进行水平切分，应先从缓存技术着手降低对数据库的访问压力。如果缓存使用过后，数据库访问量 还是非常大，可以考虑数据库读、写分离原则。若当前数据库压力依然大，且业务数据持续增长无法估量，最后可 考虑水平分库、分表，单表拆分数据控制在1000万以内。 附 SQL支持说明 详细参考:https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/ 说明:以下为官方显示内容，具体是否适用以实际测试为准 。 支持的SQL

SQL

必要条件

SELECT * FROM tbl_name

SELECT * FROM tbl_name WHERE (col1 = ? or col2 = ?) and col3 = ?

SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ?

SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 =?

SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ?

INSERT INTO tbl_name (col1, col2,…) VALUES (?, ?, …)

INSERT INTO tbl_name VALUES (?, ?,…)

INSERT INTO tbl_name (col1, col2, …) VALUES (?, ?, …), (?, ?, …)

UPDATE tbl_name SET col1 = ? WHERE col2 = ?

DELETE FROM tbl_name WHERE col1 = ?

CREATE TABLE tbl_name (col1 int, …)

ALTER TABLE tbl_name ADD col1 varchar(10)

DROP TABLE tbl_name

TRUNCATE TABLE tbl_name

CREATE INDEX idx_name ON tbl_name

DROP INDEX idx_name ON tbl_name

DROP INDEX idx_name

SELECT DISTINCT * FROM tbl_name WHERE col1 = ?

SELECT COUNT(DISTINCT col1) FROM tbl_name

不支持的SQL

SQL

不支持原因

INSERT INTO tbl_name (col1, col2, …) VALUES(1 2, ?, …)

VALUES语句不支持运算 表达式

INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ?

INSERT … SELECT

SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ?

HAVING

SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2

UNION

SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2

UNION ALL

SELECT * FROM ds.tbl_name1

包含schema

SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name

详见DISTINCT支持情况详 细说明

DISTINCT支持情况详细说明 支持的SQL

代码语言：javascript复制
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT DISTINCT col1 FROM tbl_name
SELECT DISTINCT col1, col2, col3 FROM tbl_name
SELECT DISTINCT col1 FROM tbl_name ORDER BY col1
SELECT DISTINCT col1 FROM tbl_name ORDER BY col2
SELECT DISTINCT(col1) FROM tbl_name
SELECT AVG(DISTINCT col1) FROM tbl_name
SELECT SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1 SELECT COUNT(DISTINCT col1   col2) FROM tbl_name
SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name 
SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1 
SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1

不支持的SQL

SQL

不支持原因

SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name

同时使用普通聚合函数和DISTINCT聚合函数

云数据库SQLServer jdbc sql 数据库

0 人点赞