学习笔记之-分库分表概念,分库分表带来的事务,关联查询,分页,排序,主键避重等问题,Sharding-JDBC解决分库分表后所引发的部分问题_shardingjdbc分表后分页查询-程序员宅基地

技术标签: database  java  数据库  

Sharding-JDBC分库分表

1 概述

1.1.分库分表是什么

随着公司业务快速发展,数据库中的数据量猛增,访问性能也变了,优化迫在眉睫。分析一下问题出现在哪儿呢﹖关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重

方案1 :
通过提升服务器硬件能力来提高数据处理能力,比如增加存储容量、CPU等,这种方案成本很高,并且如果瓶颈在MySQL本身那么提高硬件也是有限的

方案2∶
把数据分散在不同的数据库中,使得单一数据库的数据量变小来缓解单一数据库的性能问题,从而达到提升数据库性能的目的,如下图∶将电商数据库拆分为若干独立的数据库,并且对于大表也拆分为若干小表,通过这种数据库拆分的方法来解决数据库的性能问题。

在这里插入图片描述
分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题,将原来独立的数据库拆分成若干数据库组成,将数据大表拆分成若干数据表组成,使得单一数据库、单一数据表的数据量变小,从而达到提升数据库性能的目的。

1.2.分库分表的方式

分库分表包括分库分表两个部分,在生产中通常包括∶垂直分库、水平分库、垂直分表、水平分表四种方式。

1.2.1.垂直分表

下边通过一个商品查询的案例讲解垂直分表:
通常在商品列表中是不显示商品详情信息的,如下图:
在这里插入图片描述
用户在浏览商品列表时,只有对某商品感兴趣时才会查看该商品的详细描述。因此,商品信息中商品描述字段访问频次较低,且该字段存储占用空间较大,访问单个数据IO时间较长;商品信息中商品名称、商品图片、商品价格等其他字段数据访问频次较高。

由于这两种数据的特性不一样,因此他考虑将商品信息表拆分如下:
将访问频次低的商品描述信息单独存放在一张表中,访问频次较高的商品基本信息单独放在一张表中。
在这里插入图片描述

在这里插入图片描述

小明进行的这一步优化,就叫垂直分表
垂直分表定义:将一个表按照字段分成多表,每个表存储其中一部分字段。

它带来的提升是︰
	1.为了避免lO争抢并减少锁表的几率,查看详情的用户与商品信息浏览互不影响
		为什么大字段IO效率低︰
			第一是由于数据量本身大,需要更长的读取时间;
			第二是跨页,页是数据库存储单位,很多查找及定位操作都是以页为单位,单页内的数据行越多数据库整体性能越好,而大字段占用空间大,单页内存储行数少,因此ro效率较低。
			第三,数据库以行为单位将数据加载到内存中,这样表中字段长度较短且访问频率较高,内存能加载更多的数据,命中率更高,减少了磁盘Io,从而提升了数据库性能。
	2.充分发挥热门数据的操作效率,商品信息的操作的高效率不会被商品描述的低效率所拖累。
分表原则:

一般来说,某业务实体中的各个数据项的访问频次是不一样的,部分数据项可能是占用存储空间比较大的BLOB或是TEXT。例如上例中的商品描述。所以,当表数据量很大时,可以将表按字段切开,将热门字段、冷门字段分开放置在不同库中,这些库可以放在不同的存储设备上,避免IO争抢。垂直切分带来的性能提升主要集中在
热门数据的操作效率上,而且磁盘争用情况减少。

通常我们按以下原则进行垂直拆分:
	1.把不常用的字段单独放在一张表;
	2.把text , blob等大字段拆分出来放在附表中;
	3.经常组合查询的列放在一张表中;

1.2.2.垂直分库

通过垂直分表性能得到了一定程度的提升,但是还没有达到要求,并且磁盘空间也快不够了,因为数据还是始终限制在一台服务器,库内垂直分表只解决了单一表数据量过大的问题,但没有将表分布到不同的服务器上,因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

经过思考,他把原有的SELLER_DB(卖家库),分为了PRODUCT_DB(商品库)和STORE_DB(店铺库),并把这两个库分散到不同服务器,如下图:
在这里插入图片描述

由于商品信息商品描述业务耦合度较高,因此一起被存放在PRODUCT_DB(商品库);而店铺信息相对独立,因
此单独被存放在STORE_DB(店铺库)。

小明进行的这一步优化,就叫垂直分库。

垂直分库是指按照业务将表进行分类,分布到不同的数据库上面,每个库可以放在不同的服务器上,它的核心理念是专库专用

它带来的提升是∶
	解决业务层面的耦合,业务清晰
	能对不同业务的数据进行分级管理、维护、监控、扩展等
	高并发场景下,垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈

垂直分库通过将表按业务分类,然后分布在不同数据库,并且可以将这些数据库部署在不同服务器上,从而达到多个服务器共同分摊压力的效果,但是依然没有解决单表数据量过大的问题。

1.2.3.水平分库

经过垂直分库,数据库性能问题得到一定程度的解决,但是随着业务量的增长,PRODUCT_DB(商品库)单库存储数据已经超出预估。粗略估计,目前有8w店铺,每个店铺平均150个不同规格的商品,再算上增长,那商品数量得往1500w+上预估,并且PRODUCT_DB(商品库)属于访问非常频繁的资源,单台服务器已经无法支撑。此时该如何优化?

再次分库?但是从业务角度分析,目前情况已经无法再次垂直分库。
尝试水平分库,将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。
在这里插入图片描述
也就是说,要操作某条数据,先分析这条数据所属的店铺ID。如果店铺ID为双数,将此操作映射至RRODUCT_DB1(商品库1);如果店铺ID为单数,将操作映射至RRODUCT_DB2(商品库2)。此操作要访问数据库名称的表达式为RRODUCT_DB[店ID%2+1]

小明进行的这一步优化,就叫水平分库
水平分库是把同一个表的数据按一定规则拆到不同的数据库中,每个库可以放在不同的服务器上。

对比:垂直分库是把不同表拆到不同数据库中,水平分库它是对数据行的拆分,不影响表结构

它带来的提升是︰
	·解决了单库大数据,高并发的性能瓶颈。
	·提高了系统的稳定性及可用性。稳定性体现在Io冲突减少,锁定减少,可用性指某个库出问题,部分可用

当一个应用难以再细粒度的垂直切分或切分后数据量行数巨大,存在单库读写存储性能瓶颈,这时候就需要进行水平分库了,经过水平切分的优化,往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库,需要额外进行数据操作的路由工作,因此大大提升了系统复杂度。

1.2.4.水平分表

按照水平分库的思路对他把PRODUCT_DB_X(商品库)内的表也可以进行水平拆分,其目的也是为解决单表数据量大的问题,如下图:
在这里插入图片描述
与水平分库的思路类似,不过这次操作的目标是表,商品信息及商品描述被分成了两套表。如果商品ID为双数,将此操作映射至商品信息1表;如果商品ID为单数,将操作映射至商品信息2表。此操作要访问表名称的表达式为商品信息[商品ID%2+1]

小明进行的这一步优化,就叫水平分表
水平分表是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中。
对数据行的拆分,不影响表结构

它带来的提升是︰
	·优化单—表数据量过大而产生的性能问题
	·避免lO争抢并减少锁表的几率

库内的水平分表,解决了单一表数据量过大的问题,分出来的小表中只包含一部分数据,从而使得单个表的数据量变小,提高检索性能。

1.3.分库分表带来的问题

分库分表能有效的缓解了单机和单库带来的性能瓶颈和压力,突破网络lO、硬件资源、连接数的瓶颈,同时也带来了一些问题。

1.3.1.事务一致性问题

由于分库分表把数据分布在不同库甚至不同服务器,不可避免会带来分布式事务问题。

1.3.2.跨节点关联查询

在没有分库,我们检索商品时可以通过以下SQL对店铺信息进行关联查询:
在这里插入图片描述

但垂直分库[商品信息]和[店铺信息]不在一个数据库,甚至不在一台服务器,无法进行关联查询

可将原关联查询分为两次查询,第一次查询的结果集中找出关联数据id,然后根据id发起第二次请求得到关联数据,最后将获得到的数据进行拼装。

1.3.3.跨节点分页、排序函数

跨节点多库进行查询时,limit分页、 order by排序等问题,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序。

如,进行水平分库后的商品库,按ID倒序排序分页,取第一页:
在这里插入图片描述
在这里插入图片描述
以上流程是取第一页的数据,性能影响不大,但由于商品信息的分布在各数据库的数据可能是随机的,如果是取第N页,需要将所有节点前N页数据都取出来合并,再进行整体的排序,操作效率可想而知。所以请求页数越大,系统的性能也会越差。
在使用Max、Min、Sum、Count之类的函数进行计算的时候,与排序分页同理,也需要先在每个分片上执行相应的函数,然后将各个分片的结果集进行汇总和再次计算,最终将结果返回。

1.3.4.主键避重

在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。
在这里插入图片描述

1.3.5.公共表

实际的应用场景中,参数表、数据字典表等都是数据量较小,变动少,而且属于高频联合查询的依赖表。例子中地理区域表也属于此类型。

可以将这类表在每个数据库都保存一份,所有对公共表的更新操作都同时发送到所有分库执行。

由于分库分表之后,数据被分散在不同的数据库、服务器。因此,对数据的操作也就无法通过常规方式完成,并且它还带来了一系列的问题。好在,这些问题不是所有都需要我们在应用层面上解决,市面上有很多中间件可供我们选择,其中Sharding-JDBC使用流行度较高,我们来了解一下它。

1.4 Sharding-JDBC介绍

1.4.1 Sharding-JDBC介绍

Sharding-JDBC是当当网研发的开源分布式数据库中间件,从3.0开始Sharding-JDB.C被包含在Sharding-Sphere中,之后该项目进入进入Apache孵化器,4.0版本之后的版本为Apache版本。

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈,它由Sharding-JDB.C.Sharding-Proxy和Sharding-Sidecar (计划中)这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能,可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

咱们目前只需关注Sharding-JDBC,它定位为轻量级Java框架,在Java的JDBC层提供的额外服务。它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动完全兼容JDBC和各种ORM框架

Sharding-JDBC的核心功能为数据分片和读写分离,通过Sharding-JDBC,应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源,而不用关心数据源的数量以及数据如何分布。

·适用于任何基于Java的ORM框架,如:Hibernate, Mybatis, Spring ]DBC Template或直接使用JDBC。
·基于任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP等。
·支持任意实现JDBC规范的数据库。目前支持MySQL , Oracle, SQLServer和PostgreSQL

在这里插入图片描述

简单来讲就是:Sharding-JDBC就是统一我们所分的所有库与所有表,程序员在开发中直接使用Sharding-JDBC来操作数据库就行,无需关系具体操作那个数据库那个表,Sharding-JDBC会帮我们做

1.4.2与jdbc性能对比

1.性能损耗测试︰服务器资源充足、并发数相同,比较JDBC和Sharding-JDBC性能损耗,Sharding-JDBC相对JDBC损耗不超过7%。
在这里插入图片描述
⒉性能对比测试︰服务器资源使用到极限,相同的场景JDBC与Sharding-JDBC的吞吐量相当。
3.性能对比测试︰服务器资源使用到极限,Sharding-JDBC采用分库分表后,Sharding-JDBC吞吐量较DBC不分表有接近2倍的提升。
在这里插入图片描述

2.Sharding-JDBC快速入门

2.1需求说明

本章节使用Sharding-JDBC完成对订单表的水平分表,通过快速入门程序的开发,快速体验Sharding-DBC的使用方法。

人工创建两张表,t_order_1和t_order_2,这两张表是订单表拆分后的表,通过Sharding-Jdbc向订单表插入数据,按照一定的分片规则,主键为偶数的进入t_order_1,另一部分数据进入t_order_2,通过Sharding-dbc查询数据,根据SQL语句的内容从t_order_1或t_order_2查询数据。

2.2.环境搭建

2.2.1环境说明

在这里插入图片描述

2.2.2创建数据库


#创建订单库order_db
CREATE DATABASE `order_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
#在order_db中创建t_order_1、t_order_2表
DROP TABLE IF EXISTS `t_order_1`; 
CREATE TABLE `t_order_1` 
( `order_id` BIGINT(20) NOT NULL COMMENT '订单id', 
`price` DECIMAL(10, 2) NOT NULL COMMENT '订单价格',
 `user_id` BIGINT(20) NOT NULL COMMENT '下单用户id', 
 `status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; 
DROP TABLE IF EXISTS `t_order_2`; 
CREATE TABLE `t_order_2` 
( `order_id` BIGINT(20) NOT NULL COMMENT '订单id',
 `price` DECIMAL(10, 2) NOT NULL COMMENT '订单价格', 
 `user_id` BIGINT(20) NOT NULL COMMENT '下单用户id', 
 `status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

2.2.3.引入maven依赖

引入 sharding-jdbc和SpringBoot整合的Jar包:

<dependency> 
	<groupId>org.apache.shardingsphere</groupId> 
		<artifactId>sharding‐jdbc‐spring‐boot‐starter</artifactId> 
	<version>4.0.0‐RC1</version> 
</dependency>

具体spring boot相关依赖及配置请参考资料中dbsharding/sharding-jdbc-simple工程,本指引只说明与Sharding- JDBC相关的内容。

2.2.4.分片规则配置

分片规则配置是sharding-jdbc进行对分库分表操作的重要依据,配置内容包括:数据源、主键生成策略、分片策 略等。 在

使用application.properties或者application.yml或者配置类的方式

application.properties中配置

server.port=56081

spring.application.name = sharding-jdbc-simple-demo

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

spring.main.allow-bean-definition-overriding = true

mybatis.configuration.map-underscore-to-camel-case = true

#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m1

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.183.9:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = mysql

# 指定t_order表的数据分布情况,配置数据节点 m1.t_order_1,m1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{
    1..2}

# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 指定t_order表的分片策略,分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{
    order_id % 2 + 1}

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

swagger.enable = true

logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug
logging.level.druid.sql = debug

application.yml中配置

server:
  port: 56081
  servlet:
    context-path: /sharding-jdbc-simple-demo
spring:
  application:
    name: sharding-jdbc-simple-demo
  http:
    encoding:
      enabled: true
      charset: utf-8
      force: true
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names: m1 #1.首先定义数据源m1,并对m1进行实际的参数配置。
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.183.9:3306/order_db?useUnicode=true
        username: root
        password: mysql
    sharding:
      tables:
        t_order:
          actualDataNodes: m1.t_order_$->{
    1..2} #2.指定t_order表的数据分布情况,他分布在m1.t_order_1,m1.t_order_2 
          tableStrategy:
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_$->{
    order_id % 2 + 1} #4.定义t_order分片策略,order_id为偶数的数据落在t_order_1,为奇数的落在t_order_2,分表策略的表达式为 t_order_$->{order_id % 2 + 1}
          keyGenerator:
            type: SNOWFLAKE #3.指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法,保证id全局唯一 
            column: order_id
    props:
      sql:
        show: true
mybatis:
  configuration:
    map-underscore-to-camel-case: true
swagger:
  enable: true
logging:
  level:
    root: info
    org.springframework.web: info
    com.itheima.dbsharding: debug
    druid.sql: debug

配置类的方式

package com.fs.dbsharding.simple.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author Administrator
 * @version 1.0
 **/
//@Configuration
public class ShardingJdbcConfig {
    

    //配置分片规则
    // 定义数据源
    Map<String, DataSource> createDataSourceMap() {
    
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://192.168.183.9:3306/order_db?useUnicode=true");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        Map<String, DataSource> result = new HashMap<>();
        result.put("m1", dataSource1);
        return result;
    }
    // 定义主键生成策略
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
    
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE","order_id");
        return result;
    }

    // 定义t_order表的分片策略
    TableRuleConfiguration getOrderTableRuleConfiguration() {
    
        TableRuleConfiguration result = new TableRuleConfiguration("t_order","m1.t_order_$->{1..2}");
        result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2 + 1}"));
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());

        return result;
    }
    // 定义sharding-Jdbc数据源
    @Bean
    DataSource getShardingDataSource() throws SQLException {
    
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        //spring.shardingsphere.props.sql.show = true
        Properties properties = new Properties();
        properties.put("sql.show","true");
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,properties);
    }

}

2.3编写程序

2.3.1.数据操作

Dao
package com.fs.dbsharding.simple.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator.
 */
@Mapper
@Component
public interface OrderDao {
    

    /**
     * 插入订单 
     * application.yml中 type: SNOWFLAKE #3.指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法,保证id全局唯一 ,所以这里不需要插入id,会由Sharding-JDBC来生成
     * @param price
     * @param userId
     * @param status
     * @return
     */
    @Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
    int insertOrder(@Param("price")BigDecimal price,@Param("userId")Long userId,@Param("status")String status);

    /**
     * 根据id列表查询订单
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}

2.3.2.测试

编写单元测试:
package com.fs.dbsharding.simple;

import com.fs.dbsharding.simple.dao.OrderDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author fs
 * @version 1.0
 **/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {
    ShardingJdbcSimpleBootstrap.class})
public class OrderDaoTest {
    

    @Autowired
    OrderDao orderDao;

    //测试插入
    @Test
    public void testInsertOrder(){
    
        for(int i=1;i<20;i++){
    
            orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS");
        }
    }


    //测试查询
    @Test
    public void testSelectOrderbyIds(){
    
        List<Long> ids = new ArrayList<>();
        ids.add(373897739357913088L);
        ids.add(373897037306920961L);

        List<Map> maps = orderDao.selectOrderbyIds(ids);
        System.out.println(maps);
    }
}

执行testInsertOrder:控制台日志
在这里插入图片描述
通过日志可以发现order_id为奇数的被插入到t_order_2表,为偶数的被插入到t_order_1表,达到预期目标。

执行testSelectOrderbyIds:控制台日志
在这里插入图片描述通过日志可以发现,根据传入order_id的奇偶不同,sharding-jdbc分别去不同的表检索数据,达到预期目标。

2.4.流程分析

通过日志分析,Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿:
(1)解析sql,获取片键值,在本例中是order_id
(2)Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1},知道了当order_id为偶数时,应该往 t_order_1表插数据,为奇数时,往t_order_2插数据。
(3)于是Sharding-JDBC根据order_id的值改写sql语句,改写后的SQL语句是真实所要执行的SQL语句。
(4)执行改写后的真实sql语句
(5)将所有真正执行sql的结果进行汇总合并,返回。

3.Sharding-JDBC执行原理

3.1基本概念

在了解Sharding-JDBC的执行原理前,需要了解以下概念:

逻辑表
水平拆分的数据表的总称。例:订单数据表根据主键尾数拆分为10张表,分别是 t_order_0 、 t_order_1 到 t_order_9 ,他们的逻辑表名为 t_order 。

真实表
在分片的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9 。

数据节点
数据分片的最小物理单元。由数据源名称和数据表组成,例: ds_0.t_order_0 。

绑定表
指分片规则一致的主表和子表。例如: t_order 表和 t_order_item 表,均按照 order_id 分片,绑定表之间的分区 键完全相同,则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联,关联查询效率将大 大提升。

举例说明,如果SQL为:

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在不配置绑定表关系时,假设分片键 order_id 将数值10路由至第0片,将数值11路由至第1片,那么路由后的SQL 应该为4条,它们呈现为笛卡尔积:t_order_0, t_order_1, t_order_item_0, t_order_item_1,就有4种语句,从而产生笛卡尔积

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在配置绑定表关系后,路由的SQL应该为2条:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

广播表
指所有的分片数据源中都存在的表,表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与 海量数据的表进行关联查询的场景,例如:字典表。

分片键
用于分片的数据库字段,是将数据库(表)水平拆分的关键字段。例:将订单表中的订单主键的尾数取模分片,则订 单主键为分片字段。 SQL中如果无分片字段,将执行全路由,性能较差。 除了对单分片字段的支持,Sharding- Jdbc也支持根据多个字段进行分片。

分片算法
通过分片算法将数据分片,支持通过 = 、 BETWEEN 和 IN 分片。分片算法需要应用方开发者自行实现,可实现的灵 活度非常高。包括:精确分片算法 、范围分片算法 ,复合分片算法 等。例如:where order_id = ? 将采用精确分 片算法,where order_id in (?,?,?)将采用精确分片算法,where order_id BETWEEN ? and ? 将采用范围分片算 法,复合分片算法用于分片键有多个复杂情况。

分片策略
包含分片键和分片算法,由于分片算法的独立性,将其独立抽离。真正可用于分片操作的是分片键 + 分片算法,也 就是分片策略。内置的分片策略大致可分为尾数取模、哈希、范围、标签、时间等。由用户方配置的分片策略则更 加灵活,常用的使用行表达式配置分片策略,它采用Groovy表达式表示,如: t_user_$->{u_id % 8} 表示t_user 表根据u_id模8,而分成8张表,表名称为 t_user_0 到 t_user_7 。

自增主键生成策略
通过在客户端生成自增主键替换以数据库原生自增主键的方式,做到分布式主键无重复。

3.2.SQL解析

当Sharding-JDBC接受到一条SQL语句时,会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并 ,最终返回执行结果。
在这里插入图片描述
SQL解析过程分为词法解析语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号,称为Token。并根据 不同数据库方言所提供的字典,将其归类为关键字,表达式,字面量和操作符。 再使用语法解析器将SQL转换为抽 象语法树。

例如,以下SQL:

SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18

解析之后的为抽象语法树见下图:
在这里插入图片描述
为了便于理解,抽象语法树中的关键字的Token用绿色表示,变量的Token用红色表示,灰色表示需要进一步拆 分。最后,通过对抽象语法树的遍历去提炼分片所需的上下文,并标记有可能需要SQL改写(后边介绍)的位置。 供分片 使用的解析上下文包含查询选择项(Select Items)、表信息(Table)、分片条件(Sharding Condition)、自增 主键信息(Auto increment Primary Key)、排序信息(Order By)、分组信息(Group By)以及分页信息 (Limit、Rownum、Top)。

3.3.SQL路由

SQL路由就是把针对逻辑表的数据操作映射到对数据结点操作的过程。

根据解析上下文匹配数据库和表的分片策略,并生成路由路径。 对于携带分片键的SQL,根据分片键操作符不同可 以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是 BETWEEN),不携带分片键的SQL则采用广播路由。根据分片键进行路由的场景可分为直接路由、标准路由、笛卡 尔路由等。

标准路由

标准路由是Sharding-Jdbc最为推荐使用的分片方式,它的适用范围是不包含关联查询或仅包含绑定表之间关联查 询的SQL。 当分片运算符是等于号时,路由结果将落入单库(表),当分片运算符是BETWEEN或IN时,则路由结 果不一定落入唯一的库(表),因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。 举例说明,如果按 照 order_id 的奇数和偶数进行数据分片,一个单表查询的SQL如下:

SELECT * FROM t_order WHERE order_id IN (1, 2);

那么路由的结果应为:

SELECT * FROM t_order_0 WHERE order_id IN (1, 2); 
SELECT * FROM t_order_1 WHERE order_id IN (1, 2);

绑定表的关联查询与单表查询复杂度和性能相当。举例说明,如果一个包含绑定表的关联查询的SQL如下:

SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_id IN (1, 2);

那么路由的结果应为:

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);

可以看到,SQL拆分的数目与单表是一致的。

笛卡尔路由

笛卡尔路由是最复杂的情况,它无法根据绑定表的关系定位分片规则,因此非绑定表之间的关联查询需要拆解为笛 卡尔积组合执行。
如果上个示例中的SQL并未配置绑定表关系,那么路由的结果应为:

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);

笛卡尔路由查询性能较低,需谨慎使用。

全库表路由
对于不携带分片键的SQL,则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路 由、单播路由和阻断路由这5种类型。其中全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作, 主要包括不带分片键的DQL(数据查询)和DML(数据操纵),以及DDL(数据定义)等。例如:

SELECT * FROM t_order WHERE good_prority IN (1, 10);

则会遍历所有数据库中的所有表,逐一匹配逻辑表和真实表名,能够匹配得上则执行。路由后成为

SELECT * FROM t_order_0 WHERE good_prority IN (1, 10); 
SELECT * FROM t_order_1 WHERE good_prority IN (1, 10); 
SELECT * FROM t_order_2 WHERE good_prority IN (1, 10); 
SELECT * FROM t_order_3 WHERE good_prority IN (1, 10);

3.4.SQL改写

工程师面向逻辑表书写的SQL,并不能够直接在真实的数据库中执行,SQL改写用于将逻辑SQL改写为在真实数据 库中可以正确执行的SQL。
就是逻辑表转变为真实表的一个过程,称为sql改写
如一个简单的例子,若逻辑SQL为:

SELECT order_id FROM t_order WHERE order_id=1;

假设该SQL配置分片键order_id,并且order_id=1的情况,将路由至分片表1。那么改写之后的SQL应该为:

SELECT order_id FROM t_order_1 WHERE order_id=1;

再比如,Sharding-JDBC需要在结果归并时获取相应数据,但该数据并未能通过查询的SQL返回。 这种情况主要是 针对GROUP BY和ORDER BY。结果归并时,需要根据 GROUP BY 和 ORDER BY 的字段项进行分组和排序,但如果原 始SQL的选择项中若并未包含分组项或排序项,则需要对原始SQL进行改写。 先看一下原始SQL中带有结果归并所 需信息的场景:

SELECT order_id, user_id FROM t_order ORDER BY user_id;

由于使用user_id进行排序,在结果归并中需要能够获取到user_id的数据,而上面的SQL是能够获取到user_id数据 的,因此无需补列。 如果选择项中不包含结果归并时所需的列,则需要进行补列,如以下SQL:

SELECT order_id FROM t_order ORDER BY user_id;

由于原始SQL中并不包含需要在结果归并中需要获取的user_id,因此需要对SQL进行补列改写。补列之后的SQL 是:

SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;

3.5.SQL执行

Sharding-JDBC采用一套自动化的执行引擎,负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源 执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行;也并非直接将执行请求放入线程池去并发执行。它 更关注平衡数据源连接创建以及内存占用所产生的消耗,以及最大限度地合理利用并发等问题。 执行引擎的目标是 自动化的平衡资源控制与执行效率,他能在以下两种模式自适应切换:

内存限制模式

使用此模式的前提是,Sharding-JDBC对一次操作所耗费的数据库连接数量不做限制。 如果实际执行的SQL需要对 某数据库实例中的200张表做操作,则对每张表创建一个新的数据库连接,并通过多线程的方式并发处理,以达成 执行效率最大化。

连接限制模式

使用此模式的前提是,Sharding-JDBC严格控制对一次操作所耗费的数据库连接数量。 如果实际执行的SQL需要对 某数据库实例中的200张表做操作,那么只会创建唯一的数据库连接,并对其200张表串行处理。 如果一次操作中 的分片散落在不同的数据库,仍然采用多线程处理对不同库的操作,但每个库的每次操作仍然只创建一个唯一的数 据库连接。

内存限制模式适用于OLAP操作,可以通过放宽对数据库连接的限制提升系统吞吐量; 连接限制模式适用于OLTP操 作,OLTP通常带有分片键,会路由到单一的分片,因此严格控制数据库连接,以保证在线系统数据库资源能够被 更多的应用所使用,是明智的选择。

3.6.结果归并

将从各个数据节点获取的多数据结果集,组合成为一个结果集并正确的返回至请求客户端,称为结果归并。 Sharding-JDBC支持的结果归并从功能上可分为遍历、排序、分组、分页和聚合5种类型,它们是组合而非互斥的 关系。 归并引擎的整体结构划分如下图。
在这里插入图片描述
结果归并从结构划分可分为流式归并、内存归并和装饰者归并
流式归并和内存归并是互斥的,装饰者归并可以在 流式归并和内存归并之上做进一步的处理。
内存归并很容易理解,他是将所有分片结果集的数据都遍历并存储在内存中,再通过统一的分组、排序以及聚合等 计算之后,再将其封装成为逐条访问的数据结果集返回。

流式归并
是指每一次从数据库结果集中获取到的数据,都能够通过游标逐条获取的方式返回正确的单条数据,它与 数据库原生的返回结果集的方式最为契合。 下边举例说明排序归并的过程,如下图是一个通过分数进行排序的示例图,它采用流式归并方式。 图中展示了3张 表返回的数据结果集,每个数据结果集已经根据分数排序完毕,但是3个数据结果集之间是无序的。 将3个数据结 果集的当前游标指向的数据值进行排序,并放入优先级队列,t_score_0的第一个数据值最大,t_score_2的第一个 数据值次之,t_score_1的第一个数据值最小,因此优先级队列根据t_score_0,t_score_2和t_score_1的方式排序 队列。
在这里插入图片描述
下图则展现了进行next调用的时候,排序归并是如何进行的。 通过图中我们可以看到,当进行第一次next调用 时,排在队列首位的t_score_0将会被弹出队列,并且将当前游标指向的数据值(也就是100)返回至查询客户端, 并且将游标下移一位之后,重新放入优先级队列。 而优先级队列也会根据t_score_0的当前数据结果集指向游标的 数据值(这里是90)进行排序,根据当前数值,t_score_0排列在队列的最后一位。 之前队列中排名第二的 t_score_2的数据结果集则自动排在了队列首位。

在进行第二次next时,只需要将目前排列在队列首位的t_score_2弹出队列,并且将其数据结果集游标指向的值返 回至客户端,并下移游标,继续加入队列排队,以此类推。 当一个结果集中已经没有数据了,则无需再次加入队 列。
在这里插入图片描述
可以看到,对于每个数据结果集中的数据有序,而多数据结果集整体无序的情况下,Sharding-JDBC无需将所有的 数据都加载至内存即可排序。 它使用的是流式归并的方式,每次next仅获取唯一正确的一条数据,极大的节省了 内存的消耗。

装饰者归并是对所有的结果集归并进行统一的功能增强,比如归并时需要聚合SUM前,在进行聚合计算前,都会通 过内存归并或流式归并查询出结果集。因此,聚合归并是在之前介绍的归并类型之上追加的归并能力,即装饰者模 式。

4.水平分表

水平分表是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中。

5.水平分库

前面已经介绍过,水平分库是把同一个表的数据按一定规则拆到不同的数据库中,每个库可以放在不同的服务器 上。接下来看一下如何使用Sharding-JDBC实现水平分库,咱们继续对快速入门中的例子进行完善。

(1)将原有order_db库拆分为order_db_1、order_db_2

在这里插入图片描述

(2)分片规则修改

由于数据库拆分了两个,这里需要配置两个数据源。 分库需要配置分库的策略,和分表策略的意义类似,通过分库策略实现数据操作针对分库的数据库进行操作。

# 定义多个数据源 
spring.shardingsphere.datasource.names = m1,m2 
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/order_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/order_db_2?useUnicode=true 
spring.shardingsphere.datasource.m2.username = root spring.shardingsphere.datasource.m2.password = root 

... 

# 分库策略,以user_id为分片键,分片策略为user_id % 2 + 1,user_id为偶数操作m1数据源,否则操作m2。 
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column = user_id 
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression = m$‐>{
    user_id % 2 + 1}

分库策略定义方式如下:

#分库策略,如何将一个逻辑表映射到多个数据源 
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= # 分片策略属性值 

#分表策略,如何将一个逻辑表映射为多个实际表 
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分 片策略属性值

Sharding-JDBC支持以下几种分片策略: 不管理分库还是分表,策略基本一样。

standard:标准分片策略,对应StandardShardingStrategy。提供对SQL语句中的=, IN和BETWEEN AND的 分片操作支持。StandardShardingStrategy只支持单分片键,提供PreciseShardingAlgorithm和 RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的,用于处理=和IN的分片。 RangeShardingAlgorithm是可选的,用于处理BETWEEN AND分片,如果不配置 RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理。

complex:符合分片策略,对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, IN和 BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复 杂,因此并未进行过多的封装,而是直接将分片键值组合以及分片操作符透传至分片算法,完全由应用开发 者实现,提供最大的灵活度。

inline:行表达式分片策略,对应InlineShardingStrategy。使用Groovy的表达式,提供对SQL语句中的=和 IN的分片操作支持,只支持单分片键。对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java 代码开发,如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8,而分成8张表,表名称为 t_user_0 到 t_user_7 。

hint:Hint分片策略,对应HintShardingStrategy。通过Hint而非SQL解析的方式分片的策略。对于分片字段 非SQL决定,而由其他外置条件决定的场景,可使用SQL Hint灵活的注入分片字段。例:内部系统,按照员工 登录主键分库,而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。 none:不分片策略,对应NoneShardingStrategy。不分片的策略。

目前例子中都使用inline分片策略,若对其他分片策略细节若感兴趣,请查阅官方文档: https://shardingsphere.apache.org

(3)插入测试

修改testInsertOrder方法,插入数据中包含不同的user_id

    @Test
    public void testInsertOrder() {
    
        for (int i = 0; i < 10; i++) {
    
            orderDao.insertOrder(new BigDecimal((i + 1) * 5), 1L, "WAIT_PAY");
        }
        for (int i = 0; i < 10; i++) {
    
            orderDao.insertOrder(new BigDecimal((i + 1) * 10), 2L, "WAIT_PAY");
        }
    }

执行testInsertOrder:
在这里插入图片描述
通过日志可以看出,根据user_id的奇偶不同,数据分别落在了不同数据源,达到目标。

(4)查询测试

调用快速入门的查询接口进行测试:

    /**
     * 根据id列表查询订单
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);

通过日志发现,sharding-jdbc将sql路由到m1和m2:
在这里插入图片描述
问题分析:
由于查询语句中并没有使用分片键user_id,所以sharding-jdbc将广播路由到每个数据结点。
下边我们在sql中添加分片键进行查询。

在OrderDao中定义接口:

    @Select({
    "<script>", " select *  from t_order t where t.order_id in <foreach collection='orderIds' item='id' open='(' separator=',' close=')'> #{id} </foreach> and t.user_id = #{userId} </script>"})
    List<Map> selectOrderbyUserAndIds(@Param("userId") Integer userId, @Param("orderIds") List<Long> orderIds);

编写测试方法:

    @Test
    public void testSelectOrderbyUserAndIds() {
    
        List<Long> orderIds = new ArrayList<>();
        orderIds.add(373422416644276224L);
        orderIds.add(373422415830581248L);
        //查询条件中包括分库的键user_id int user_id = 1; 
        List<Map> orders = orderDao.selectOrderbyUserAndIds(user_id, orderIds);
        JSONArray jsonOrders = new JSONArray(orders);
        System.out.println(jsonOrders);
    }

执行testSelectOrderbyUserAndIds:
在这里插入图片描述
查询条件user_id为1,根据分片策略m$->{user_id % 2 + 1}计算得出m2,此sharding-jdbc将sql路由到m2,见上 图日志。

6.垂直分库

前面已经介绍过,垂直分库是指按照业务将表进行分类,分布到不同的数据库上面,每个库可以放在不同的服务器 上,它的核心理念是专库专用。接下来看一下如何使用Sharding-JDBC实现垂直分库。

(1)创建数据库

创建数据库user_db

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

在user_db中创建t_user表

DROP TABLE IF EXISTS `t_user`; 
CREATE TABLE `t_user` 
( `user_id` bigint(20) NOT NULL COMMENT '用户id', 
`fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
 `user_type` char(1) DEFAULT NULL COMMENT '用户类型', 
 PRIMARY KEY (`user_id`) USING BTREE ) 
 ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

(2)在Sharding-JDBC规则中修改

# 新增m0数据源,对应user_db 
spring.shardingsphere.datasource.names = m0,m1,m2 
... 
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/user_db?useUnicode=true 
spring.shardingsphere.datasource.m0.username = root 
spring.shardingsphere.datasource.m0.password = root 
.... 
# t_user分表策略,固定分配至m0的t_user真实表 
spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes = m$‐>{
    0}.t_user 
spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.sharding‐column = user_id 
spring.shardingsphere.sharding.tables.t_user.table‐strategy.inline.algorithm‐expression = t_user

(3)数据操作

新增UserDao:

package com.fs.dbsharding.simple.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface UserDao {
    
    /*** 新增用户 * @param userId 用户id * @param fullname 用户姓名 * @return */
    @Insert("insert into t_user(user_id, fullname) value(#{userId},#{fullname})")
    int insertUser(@Param("userId") Long userId, @Param("fullname") String fullname);

    /*** 根据id列表查询多个用户 * @param userIds 用户id列表 * @return */
    @Select({
    "<script>", " select", " * ", " from t_user t ", " where t.user_id in", "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>", "#{id}", "</foreach>", "</script>"})
    List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
}

(4)测试

新增单元测试方法:

    @Test
    public void testInsertUser() {
    
        for (int i = 0; i < 10; i++) {
    
            Long id = i + 1L;
            userDao.insertUser(id, "姓名" + id);
        }
    }

    @Test
    public void testSelectUserbyIds() {
    
        List<Long> userIds = new ArrayList<>();
        userIds.add(1L);
        userIds.add(2L);
        List<Map> users = userDao.selectUserbyIds(userIds);
        System.out.println(users);
    }

执行testInsertUser:
在这里插入图片描述
通过日志可以看出t_user表的数据被落在了m0数据源,达到目标。

执行testSelectUserbyIds:
在这里插入图片描述
通过日志可以看出t_user表的查询操作被落在了m0数据源,达到目标。

7.公共表

公共表属于系统中数据量较小,变动少,而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可 以将这类表在每个数据库都保存一份,所有更新操作都同时发送到所有分库执行。接下来看一下如何使用 Sharding-JDBC实现公共表。

(1)创建数据库

分别在user_db、order_db_1、order_db_2中创建t_dict表:

CREATE TABLE `t_dict` 
( `dict_id` bigint(20) NOT NULL COMMENT '字典id', 
`type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型', 
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',
 `value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',
  PRIMARY KEY (`dict_id`)
   USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

(2)在Sharding-JDBC规则中修改

# 指定t_dict为公共表 
spring.shardingsphere.sharding.broadcast‐tables=t_dict

(3)数据操作

新增DictDao:

package com.fs.dbsharding.simple.dao;

@Mapper
@Component
public interface DictDao {
    
    /*** 新增字典 * @param type 字典类型 * @param code 字典编码 * @param value 字典值 * @return */
    @Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},# {value})")
    int insertDict(@Param("dictId") Long dictId, @Param("type") String type, @Param("code") String code, @Param("value") String value);

    /*** 删除字典 * @param dictId 字典id * @return */
    @Delete("delete from t_dict where dict_id = #{dictId}")
    int deleteDict(@Param("dictId") Long dictId);
}

(4)字典操作测试

新增单元测试方法:

    @Test
    public void testInsertDict() {
    
        dictDao.insertDict(1L, "user_type", "0", "管理员");
        dictDao.insertDict(2L, "user_type", "1", "操作员");
    }

    @Test
    public void testDeleteDict() {
    
        dictDao.deleteDict(1L);
        dictDao.deleteDict(2L);
    }

执行testInsertDict:
在这里插入图片描述
通过日志可以看出,对t_dict的表的操作被广播至所有数据源。 测试删除字典,观察是否把所有数据源中该 公共表的记录删除。

(5)字典关联查询测试

字典表已在各各分库存在,各业务表即可和字典表关联查询。 定义用户关联查询dao:
在UserDao中定义

    /*** 根据id列表查询多个用户,关联查询字典表 * @param userIds 用户id列表 * @return */
    @Select({
    "<script>", " select", " * ", " from t_user t ,t_dict b", " where t.user_type = b.code and t.user_id in", "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>", "#{id}", "</foreach>", "</script>"})
    List<Map> selectUserInfobyIds(@Param("userIds") List<Long> userIds);

定义测试方法:

    @Test
    public void testSelectUserInfobyIds() {
    
        List<Long> userIds = new ArrayList<>();
        userIds.add(1L);
        userIds.add(2L);
        List<Map> users = userDao.selectUserInfobyIds(userIds);
        JSONArray jsonUsers = new JSONArray(users);
        System.out.println(jsonUsers);
    }

执行测试方法,查看日志,成功关联查询字典表:
在这里插入图片描述

8.读写分离

8.1理解读写分离

面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类 型的应用系统来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改操作,从库负责处理查询操作,能 够有效的避免由数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。
在这里插入图片描述
通过一主多从的配置方式,可以将查询请求均匀的分散到多个数据副本,能够进一步的提升系统的处理能力。 使用 多主多从的方式,不但能够提升系统的吞吐量,还能够提升系统的可用性,可以达到在任何一个数据库宕机,甚至 磁盘物理损坏的情况下仍然不影响系统的正常运行。
在这里插入图片描述
读写分离的数据节点中的数据内容是一致的,而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读 写分离联合使用,能够更加有效的提升系统的性能。
Sharding-JDBC读写分离则是根据SQL语义的分析,将读操作和写操作分别路由至主库与从库。
它提供透明化读写 分离,让使用方尽量像使用一个数据库一样使用主从数据库集群。
在这里插入图片描述
Sharding-JDBC提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用,同一线程且同一数据库连接 内,如有写入操作,以后的读操作均从主库读取,用于保证数据一致性。Sharding-JDBC不提供主从数据库的数据 同步功能,需要采用其他机制支持。

在这里插入图片描述

8.1.mysql主从同步(百度找资料配置)

一,新增mysql实例 复制原有mysql如:D:\mysql-5.7.25(作为主库) -> D:\mysql-5.7.25-s1(作为从库),并修改以下从库的my.ini:

8.2.实现sharding-jdbc读写分离

(1)在Sharding-JDBC规则中修改

# 增加数据源s0,使用上面主从同步配置的从库。 
spring.shardingsphere.datasource.names = m0,m1,m2,s0 
... 
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/user_db?useUnicode=true 
spring.shardingsphere.datasource.s0.username = root spring.shardingsphere.datasource.s0.password = root 
.... 
# 主库从库逻辑数据源定义 ds0为user_db 
spring.shardingsphere.sharding.master‐slave‐rules.ds0.master‐data‐source‐name=m0 
spring.shardingsphere.sharding.master‐slave‐rules.ds0.slave‐data‐source‐names=s0
# t_user分表策略,固定分配至ds0的t_user真实表 
spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes = ds0.t_user
....

(2)测试

执行testInsertUser单元测试:
在这里插入图片描述
通过日志可以看出,所有写操作落入m0数据源。

执行testSelectUserbyIds单元测试:
在这里插入图片描述
通过日志可以看出,所有写操作落入s0数据源,达到目标。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/fushuaiCSDN/article/details/121221521

智能推荐

Aptina MT9M114 1.26M camera spec 学习_mt9m114点亮代码-程序员宅基地

文章浏览阅读1.5k次。MT9M114是出自ON半导体公司的CMOS (CMOS与CCD的区别) digital image sensor, active-pixel array是1296 (H) × 976 (V)=1.26Mp.关键参数 Parameter Typical Value 中文解释 Optical Format 1/6-inch ..._mt9m114点亮代码

计算机科学与技术在军中的应用,计算机科学技术的应用及发展趋势-程序员宅基地

文章浏览阅读2.5k次。114 ?电子技术与软件工程 Electronic Technology & Software Engineering计算机技术应用? the Application of Computer Technology【关键词】计算机科学技术 应用 发展趋势计算机科学技术的发明是人类社会史上的重要财富之一,并经过不断的改革创新,使得计算机科学技术在人们的生活中得到广泛应用。并涉及到社会上各个领域...

Android画板的实现-程序员宅基地

文章浏览阅读87次。这是一个常见的画板功能,常用于画画和手写输入等等,今天就教大家实现这个小功能,这个功能还是比较简单的,只有一个Java文件先看效果图布局代码,只有三个按钮和一张图片<?xml version="1.0" encoding="utf-8"?><LinearLayout ="http://schemas.android.com/a..._androi画板代码

论文代码不开源,应该被直接拒稿?-程序员宅基地

文章浏览阅读4.9k次,点赞3次,收藏5次。公众号关注“GitHubDaily”设为 “星标”,每天带你逛 GitHub!转自机器之心前不久,图灵奖得主 Yann LeCun 公开质疑谷歌大脑的论文无法复现,引起了社区热议。Le..._代码没有开源 如何复现论文

Gridview数据控件的七种字段类型_c# gridview headerimageurl headertext-程序员宅基地

文章浏览阅读1.2k次。http://blog.csdn.net/judyge/article/details/498471899.8 数据控件的七种字段类型(Fields Type)的应用GridView共支持七种字段类型,字段原本应该叫“Column”比较恰当,但ASP.NET 2.0却采用另一个名称“Field”来表示,对于名称的命名祭司认为有点不直观,因为不明的人看了根本不知道Field代表什么东西,但既然ASP_c# gridview headerimageurl headertext

CCS2021论文扫读(二)- 检测相关_deepaid-程序员宅基地

文章浏览阅读6.4k次。Fuzzy Message Detection模糊消息检测许多隐私保护协议采用一种原语,允许发送者将消息“标记”到接收者的公钥,这样只有接收者(拥有相应的密钥)才能检测到该消息是供他们使用的。此类协议的示例包括匿名消息传递、隐私保护支付和匿名跟踪。现有技术的一个限制是接收者不能轻易地将消息检测外包给远程服务器,而不向服务器揭示匹配消息的确切集合。在这项工作中,我们提出了一类新的密码原语,为模糊消息检测方案。这些方案允许接收者派生一个专门的消息检测密钥,该密钥可以识别正确的消息,同时也可以错误地识别具有特_deepaid

随便推点

麒麟3.2安装微软雅黑字体_msyh.ttc-程序员宅基地

文章浏览阅读2.1k次。麒麟3.2基于CentOS6.8,安装类似CentOS从win10系统C:\Windows\Fonts拷贝微软雅黑字体文件三个字体文件为msyh.ttc msyhbd.ttc msyhl.ttc将字体文件拷贝到/usr/share/fonts/kylin-fonts目录,注意不是/usr/share/fonts/chinese/TrueType目录执行如下命令cd /usr/share/fonts/kylin-fontschmod 755 msyh*mkfontscalemk_msyh.ttc

Linux创建用户并赋予Root权限_[root@server opt]# useradd test a forbidden comman-程序员宅基地

文章浏览阅读6.3k次。Linux创建用户并赋予Root权限添加普通用户[root@server ~]# useradd test //添加一个名为test的用户[root@server ~]# passwd test //修改密码Changing password for user test.New UNIX password: //在这里输入新密码Retype new UNIX password: //再次输入新密码passwd: all authentication tokens updated succe_[root@server opt]# useradd test a forbidden command ! ^c

浅谈前端JavaScript编程风格_javascript的lf风格是怎么样的-程序员宅基地

文章浏览阅读3.7k次,点赞4次,收藏20次。前言多家公司和组织已经公开了它们的风格规范,具体可参阅jscs.info,下面的内容主要参考了Airbnb的JavaScript风格规范。当然还有google的编程建议等编程风格 本章探讨如何使用ES6的新语法,与传统的JavaScript语法结合在一起,写出合理的、易于阅读和维护的代码。编程风格块级作用域(1)let 取代 var ES6提出了两个新的声明变量的命令:let和const。其中,_javascript的lf风格是怎么样的

HTTP&nbsp;API接口测试利器PostMan介绍_postman测试api-程序员宅基地

文章浏览阅读878次。HTTP API接口测试利器PostMan介绍转自:http://mp.weixin.qq.com/s?__biz=MjM5ODY4ODIxOA==&mid=2653199638&idx=1&sn=da0dbe86cd88323aa6d8407e604bd36a&scene=0#wechat_redirect一、什么是API接口测试?API接口有多..._postman测试api

如何在Moveit中集成自己的规划算法_如何在moveit中加入自己的规划器-程序员宅基地

文章浏览阅读3.4k次,点赞9次,收藏66次。参考资料:MoveIt!自定义运动规划算法的方法OMPL学习--第三篇之源码安装Moveit!和OMPL(Melodic版本)Ubuntu18.04-编译安装支持Python运动规划库OMPLOMPL库教程翻译Ubuntu 下 OMPL 的安装与使用我个人电脑的系统是ubuntu20第一步查看moveit相关教程,然后发现moveit中的规划器用的是ompl,所以我就打算先学习一下ompl。关于ompl的安装我是相对顺利的。在官网下载源码包以及安装脚本,根据.._如何在moveit中加入自己的规划器

测试框架pytest-(1)开始使用_python 窗体类怎么用pytest-程序员宅基地

文章浏览阅读1.7k次。本文采用pytest框架运行了两个测试用例。与unittest框架相比:引入包不同 pytest的类不需要继承类 测试固件的方法不同,pyest中setup和teardown是小写。 断言方法不同 pytest直接用assert断言import pytestfrom selenium import webdriverclass TestStorm(): def setup(self): self.driver=webdriver.Chrome() _python 窗体类怎么用pytest