Sharding-JDBC之分库分表与读写分离

一、分库分表
- 什么是分库分表
- 分库分表的方式
- 分库分表的技术模式
二、初识Sharding-JDBC
- Sharding-JDBC概述
- Sharding-JDBC基本概念
- Sharding-JDBC执行过程
- 引入依赖
- 规则配置
- 创建DataSource
三、水平分表
- 创建数据库/表
- 配置分片规则
- - 1.properties配置
  - 2.yaml配置
  - 3.Java配置
  - 4.xml方式配置
- 编写代码
- 执行测试
四、水平分库
- 创建数据库/表
- 配置分片规则
- 执行测试
- - 插入测试
  - 查询测试(无分片键)
  - 查询测试(含分片键)
五、垂直分库
- 创建数据库/表
- 配置分片规则
- 执行测试
六、垂直分表
- 创建数据库/表
- 配置分片
- 新增查询
- 普通查询
- 分页查询
七、公共表
- 创建公共表
- 指定公共表
- 执行测试
八、Sharding-JDBC读写分离
- 添加同步数据库
- 规则配置
- 写入测试
- 查询测试

一、分库分表

什么是分库分表

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

分库分表的方式

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

垂直分表：

把表的字段按访问频次、是否是大字段的原则拆分为多个表，既能使业务清晰，还能提升部分性能。

垂直分库：

把多个表按业务进行分类，分别存放在不同的库，从而将压力分散至不同的数据库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，但是需要解决跨库带来的所有复杂问题。

水平分库：

把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。但是它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。

水平分表：

把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，能小幅提升性能，仅仅作为水平分库的一个补充优化。

分库分表的技术模式

中间件Proxy模式

Proxy属于中间层方案，在应用程序和MySQL之间搭建一层Proxy。中间层介于应用程序与数据库间，
需要做一次转发，基于JDBC协议转发，如Mycat

Client 客户端模式

Client模式属于客户端直连方案。此方案的优势在于轻便，对兼容性、性能以及DBA影响小。如当当
网的sharding-jdbc，阿里的TDDL

二、初识Sharding-JDBC

官网: https://shardingsphere.apache.org/index_zh.html

Sharding-JDBC概述

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

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。

支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

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

Sharding-JDBC基本概念

逻辑表：

逻辑表是指一组具有相同逻辑和数据结构表的总称。如：订单表t_order 拆分成 t_order_0,t_order1…t_orderN。t_order就可以表示为逻辑表，在应用程序中操作的就是逻辑表。

真实表：

数据库中真实存在的物理表，即t_order_0 到 t_order_N

数据节点：

数据节点是分库分表中一个不可再分的最小数据单元（表），它由数据源名称和数据表组成，如：db1.t_order_1

绑定表：

指分片规则一致的主表和子表。绑定表之间的分区键完全相同，则此两张表互为绑定表关系。

如:product_info,product_descript,它们都是按 product_id 字段分片，因此两张表互为绑定表关系。如：在垂直分表时,一张表拆分成两张表,如果不配置绑定表关系，就会出现笛卡尔积关联查询

广播表：

存在于所有的分片数据源中的表，表结构和表中的数据在每个数据库中均完全一致。一般是为字典表或者配置表 t_config，某个表一旦被配置为广播表，只要修改某个数据库的广播表，所有数据源中广播表的数据都会跟着同步。

分片键：

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。

如: 将 t_order 表分片以后,当执行一条SQL时，通过对字段order_id取模的方式决定，这条数据该在哪个数据库中的哪个表中执行，此时 order_id 字段就是 t_order 表的分片健。

分片算法：

通过分片算法将数据分片，在实际开发中,会用 >=、<=、>、<、BETWEEN 和 IN 等条件作为分片规则，自定义分片逻辑进行分片。

由于分片算法和业务实现紧密相关，因此Sharding-JDBC是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。自定义的算法要实现Sharding-jdbc 提供的接口

sharding-jdbc提供了4种分片算法接口

1、精确分片算法 PreciseShardingAlgorithm
精确分片算法用于单个字段作为分片键，有 = 与 IN 条件的分片，在标准分片策略下使用。

2、范围分片算法 RangeShardingAlgorithm
范围分片算法用于单个字段作为分片键，有BETWEEN AND、>、<、>=、<=  等条件的分片，在标准分片策略下使用。

3、复合分片算法 ComplexKeysShardingAlgorithm
复合分片算法用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。在复合分片策略下使用。

4、Hint分片算法 HintShardingAlgorithm
Hint分片算法不使用任何的分片键和分片策略，将 SQL 路由到目标数据库和表，通过手动干预指定SQL的目标数据库和表信息，也叫强制路由。

自定义分片算法:
自定义分片算法实现按月分表

@Slf4j
public class ShardingAlgorithmMonth implements PreciseShardingAlgorithm<Date> {

    /**
     * 执行分片策略
     * @param collection           候选表集合
     * @param preciseShardingValue 精确分片值:任务的执行时间
     * @return 数据路由到的表名称
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        String node = null;
        try {
            DateFormat dateFormat = new SimpleDateFormat("yyyy_M");
            String dateStr = dateFormat.format(preciseShardingValue.getValue());
            for (String nodeCandidate : collection) {
                if (nodeCandidate.endsWith(dateStr)) {
                    node = nodeCandidate;
                    break;
                }
            }
        } catch (Exception e) {
            log.error("sharding-sphere doSharding exception {}", e.getMessage());
        }
        return node;
    }
}

# 指定t_order表的分片策略,使用标准分片策略,以及自定义分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=cn.ybzy.demo.conf.ShardingAlgorithmMonth

分片策略:

分片策略由分片算法和分片健组合而成，正可用于分片操作的是分片键 + 分片算法, 分片算法做具体的数据分片逻辑。

分片策略分类

Sharding分片策略继承自ShardingStrategy，提供了5种分片策略：

1、标准分片策略 StandardShardingStrategy

标准分片策略适用于单分片键，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。

其中PreciseShardingAlgorithm是必选的，用于处理 = 和 IN 的分片。
RangeShardingAlgorithm是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片，如果不配置RangeShardingAlgorithm，SQL中的条件等将按照全库路由处理。

2、复合分片策略 ComplexShardingStrategy

复合分片策略，同样支持对 SQL语句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片键，具体分配片细节完全由应用开发者实现。

3、行表达式分片策略 InlineShardingStrategy
行表达式分片策略，支持对 SQL语句中的 = 和 IN 的分片操作，但只支持单分片键。这种策略通常用于简单的分片，不需要自定义分片算法，可以直接在配置文件中接着写规则。

t_order_$->{t_order_id % 2} 代表 t_order 对其字段 t_order_id取模，拆分成2张表，而表名分别是t_order_0 到 t_order_1。

4、Hint分片策略 HintShardingStrategy
Hint分片策略，对应上边的Hint分片算法，通过指定分片健而非从 SQL中提取分片健的方式进行分片的策略。

5.不分片的策略 NoneShardingStrategy
如果某张表不需要分库分表，就可以不指定分库分表策略，让这张表的数据直接落到指定的数据源中即可

分布式主键：

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

ApacheShardingSphere 内置了UUID、SNOWFLAKE 两种分布式主键⽣成器，默认使⽤雪花算法（snowflake）⽣成64bit的⻓整型数据。不仅如此它还抽离出分布式主键⽣成器的接口，⽅便我们实现⾃定义的⾃增主键⽣成算法。

Sharding-JDBC执行过程

当Sharding-JDBC接受到一条SQL语句时，会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 =>结果归并，最终返回执行结果。

引入依赖

<dependency>
	   <groupId>mysql</groupId>
	   <artifactId>mysql-connector-java</artifactId>
	   <version>5.1.49</version>
 </dependency>

 <dependency>
	   <groupId>org.mybatis.spring.boot</groupId>
	   <artifactId>mybatis-spring-boot-starter</artifactId>
	   <version>2.1.3</version>
 </dependency>
 <dependency>
	   <groupId>org.apache.shardingsphere</groupId>
	   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	   <version>4.1.1</version>
  </dependency>
  <dependency>
 	  <groupId>com.alibaba</groupId>
	  <artifactId>druid</artifactId>
	  <version>1.1.21</version>
 </dependency>

规则配置

Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。配置是整个Sharding-JDBC的核心，是Sharding-JDBC中唯一与应用开发者打交道的模块。配置模块也是Sharding-JDBC的门户，通过它可以快速清晰的理解Sharding-JDBC所提供的功能。

创建DataSource

通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。

三、水平分表

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

优点：

1.优化了单一表数据量过大而产生的性能问题

2.避免 IO争抢并减少锁表的几率

创建数据库/表

CREATE DATABASE order_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

```java
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;

配置分片规则

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

1.properties配置

server.port=8888
spring.application.name = sharding-jdbc
server.servlet.context-path = /

#sharding-jdbc分片规则配置

#数据源 起名:db1
spring.shardingsphere.datasource.names = db1

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

# 指定t_order表的数据分布情况，配置数据节点 分布在db1.t_order_1，db1.t_order_2  t_order:逻辑表非真实表,任意符合场景即可  db1.t_order_$->{1..2}: db1.t_order_1,db1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db1.t_order_$->{1..2}

# 指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法，保证id全局唯一  order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 指定t_order表的分片策略，分片策略包括分片键和分片算法 分表策略的表达式: t_order_$->{order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
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


#数据字段的映射,驼峰映射
mybatis.configuration.map-underscore-to-camel-case = true

#日志配置信息
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug
logging.level.druid.sql = debug

2.yaml配置

server:
  port: 8888
  servlet:
    context-path: /
    
spring:
  application:
    name: sharding-jdbc
    
  shardingsphere:
    #数据源 起名:db1
    datasource:
      names: db1
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_db?useUnicode=true
        username: root
        password: 123456
    #指定t_order表的数据分布情况，配置数据节点    
    sharding:
      tables:
        t_order:
          actualDataNodes: m1.t_order_$->{1..2}
          #指定t_order表的分片策略,片策略包括分片键和分片算法
          tableStrategy:
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_$->{order_id % 2 + 1}
          #指定t_order表的主键生成策略为SNOWFLAKE    
          keyGenerator:
            type: SNOWFLAKE
            column: order_id
    #打开sql输出日志        
    props:
      sql:
        show: true
        
#驼峰映射        
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    
# 日志配置  
logging:
  level:
    root: info
    org.springframework.web: info
    com.itheima.dbsharding: debug
    druid.sql: debug

3.Java配置

使用配置类需要在SpringBoot启动类中屏蔽使用spring.shardingsphere配置项的类，否则该配置类会读取配置文件，从而报错。

@SpringBootApplication(exclude = {SpringBootConfiguration.class})

在这里插入图片描述

@Configuration
public class ShardingJdbcConfig {


    /**
     * 定义数据源集合
     *
     * @return
     */
    Map<String, DataSource> createDataSourceMap() {
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");
        dataSource1.setUsername("root");
        dataSource1.setPassword("123456");
        Map<String, DataSource> result = new HashMap<>();
        result.put("db1", dataSource1);
        return result;
    }

    /**
     * 定义主键生成策略
     *
     * @return
     */
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "order_id");
        return result;
    }

    /**
     * 定义t_order表的分片策略
     *
     * @return
     */
    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数据源
     *
     * @return
     * @throws SQLException
     */
    @Bean
    DataSource getShardingDataSource() throws SQLException {
        //创建sharding-jdbc配置对象
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //添加使用定义的分片策略
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        //创建配置类对象,添加一条配置信息
        Properties properties = new Properties();
        properties.put("sql.show", "true");
        //创建ShardingDataSource
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }

}

4.xml方式配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:sharding="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <context:annotation-config/>

    <!--定义数据源-->
    <bean id="db1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/order_db?useUnicode=true"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!--定义分库策略-->
    <sharding:inline-strategy id="tableShardingStrategy" sharding-column="order_id"
                              algorithmexpression="t_order_$->{order_id % 2 + 1}"/>

    <!--定义主键生成策略-->
    <sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>


    <!--定义sharding-Jdbc数据源-->
    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="db1">
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" table-strategyref="tableShardingStrategy"
                                     key-generator-ref="orderKeyGenerator"/>
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>

</beans>

编写代码

@Mapper
@Component
public interface OrderDao {

    /**
     * 新增订单
     *
     * @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);

    /**
     * 修改订单
     *
     * @param orderId
     * @param price
     * @param userId
     * @return
     */
    @Update("update t_order_1 SET price=#{price},user_id=#{userId} WHERE order_id=${orderId}")
    int updateOrder(@Param("orderId") Long orderId, @Param("price") BigDecimal price, @Param("userId") Long userId);

    /**
     * 根据ids查询order
     *
     * @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);
}

执行测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbc.class})
public class OrderDaoTest {

    @Autowired
    OrderDao orderDao;

    @Test
    public void insertOrder() {
        for (int i = 1; i < 10; i++) {
            orderDao.insertOrder(new BigDecimal(i), 1L, "SUCCESS");
        }
    }

    @Test
    public void updateOrderById() {
        Long id = 564780984948490240L;
        int i = orderDao.updateOrder(id, new BigDecimal(1000), 2L);
        System.out.println(i);
    }

    @Test
    public void selectOrderbyIds() {
        List<Long> ids = new ArrayList<>();
        ids.add(564780984948490240L);
        ids.add(564780986995310593L);

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

order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表
在这里插入图片描述
传入参数,根据id找到对应表修改order信息

根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表查询数据

四、水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上

优点：

解决了单库大数据，高并发的性能瓶颈。

缺点：

由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。

创建数据库/表

创建数据 : order_db1与order_db2

CREATE DATABASE order_db1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

每个库创建表 : 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;

配置分片规则

分库需要配置两个数据源，分库需要配置分库策略，和分表策略类似，通过分库策略实现数据操作针对分库的数据库进行操作

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

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

#数据源
spring.shardingsphere.datasource.names =db1,db2

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db1?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/order_db2?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456


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


# 指定t_order表的数据分布情况，配置数据节点  t_order:逻辑表非真实表,任意符合场景即可 db$->{1..2}=>> db1或db2  t_order_$->{1..2}=> t_order_1 或 t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db$->{1..2}.t_order_$->{1..2}

# 指定t_order表的主键生成策略为SNOWFLAKE order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 指定t_order表的分片策略，分片策略包括分片键和分片算法 t_order_$->{order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
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}

执行测试

插入测试

@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);

    @Test
    public void insertOrder(){
        for(int i=1;i<10;i++){
            orderDao.insertOrder(new BigDecimal(i),Long.parseLong(i+""),"SUCCESS");
        }
    }

根据user_id的奇偶不同，数据分别插入到db2与db1，order_id也同样根据奇偶不同插入到t_order_1与t_order_2

查询测试(无分片键)

    @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);

    @Test
    public void testSelectOrderbyUserAndIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(374121806463762432L);
//        ids.add(373897037306920961L);

        List<Map> maps = orderDao.selectOrderbyUserAndIds(4L,ids);
        System.out.println(maps);
    }

查询奇数订单，根据分片策略t_order_ $KaTeX parse error: Expected group after '_' at position 339: \dots单，根据分片策略t_order_̲$ ->{order_id % 2 + 1}计算得出t_order_1与t_order_2，但因没有指定查询user_id，即没有使用分片键user_id，所以sharding-jdbc将广播路由到每个数据结点，即会查询两个数据库，每个数据库查询2次

查询测试(含分片键)

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

   @Test
    public void selectOrderByUserAndIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(564863163246313472L);
        List<Map> maps = orderDao.selectOrderByUserAndIds(1L,ids);
        for (Map map : maps) {
            System.out.println(map);
        }
    }

分片键user_id为1，根据分片策略db $KaTeX parse error: Expected group after '_' at position 72: \dots> 根据分片策略t_order_̲$ ->{order_id % 2 + 1}计算得出t_order_1,sharding-jdbc将sql路由到t_order_1

    @Test
    public void selectOrderByUserAndIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(564863163246313472L);
        ids.add(564863164945006593L);

        List<Map> maps = orderDao.selectOrderByUserAndIds(2L,ids);
        for (Map map : maps) {
            System.out.println(map);
        }
    }

分片键user_id为2，根据分片策略db $KaTeX parse error: Expected group after '_' at position 72: \dots> 根据分片策略t_order_̲$ ->{order_id % 2 + 1}计算得出t_order_1与t_order_2,sharding-jdbc将sql路由到t_order_1与t_order_2

五、垂直分库

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

优点:

1.业务层面解耦,业务分类更清晰

2.针对不同业务的数据进行不同管理、维护、扩展

3.提升了一定的IO性能，降低了单机硬件资源的瓶颈

4.将表按业务分类，分布在不同数据库，数据库在不同服务器上，多个服务器共同分摊压力

缺点：

单表数据量过大

创建数据库/表

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

DROP TABLE
IF
	EXISTS `t_user`;
CREATE TABLE `t_user` (
	`user_id` BIGINT ( 20 ) NOT NULL COMMENT '用户id',
	`user_name` 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;

配置分片规则

#数据源
spring.shardingsphere.datasource.names =db3

spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456

# 指定t_user表的数据分布情况，配置数据节点 固定分配至db3的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db3.t_user

#t_user分表策略,分片策略包括分片键和分片算法 固定分配至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

执行测试

插入测试

    @Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")
    int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);

    @Test
    public void insertUser(){
        for (int i = 1 ; i<10; i++){
            Long id =Long.parseLong(i+"");
            userDao.insertUser(id,"姓名"+ id,i%2==0 ? "男" : "女");
        }
    }

在这里插入图片描述

查询测试

 @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);

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

在这里插入图片描述

六、垂直分表

垂直分表是将一个表按照字段分成多表，每个表存储其中一部分字段。

拆分原则:

1. 把不常用的字段单独放在一张表;

2. 把text，blob等大字段拆分出来放在附表中;

3. 经常组合查询的列放在一张表中;

优点:

避免IO争抢并减少锁表的几率

缺点:

只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

创建数据库/表

创建数据库product_db

CREATE DATABASE product_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

创建product_info_1与product_info_2两张表

DROP TABLE
IF
	EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
	`product_info_id` BIGINT ( 20 ) NOT NULL COMMENT 'id',
	`product_name` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
	`price` DECIMAL ( 10, 0 ) NULL DEFAULT NULL COMMENT '商品价格',
	PRIMARY KEY ( `product_info_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建product_descript_1与product_descript_2两张表

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 '商品描述',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置分片

spring.shardingsphere.datasource.names = db

spring.shardingsphere.datasource.db.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db.url = jdbc:mysql://localhost:3306/product_db?useUnicode=true
spring.shardingsphere.datasource.db.username = root
spring.shardingsphere.datasource.db.password = 123456

# product_info数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = db.product_info_$->{1..2}
# product_info分表策略
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}
# product_info主键生成策略
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE

#product_descript数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = db.product_descript_$->{1..2}
#product_descript分表策略
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}
#product_descript主键生成策略
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

新增查询

 public void createProduct(ProductInfo product);

   @Override
    @Transactional
    public void createProduct(ProductInfo productInfo) {
        ProductDescript productDescript =new ProductDescript();
        productDescript.setDescript(productInfo.getDescript());
        productDao.insertProductInfo(productInfo);
        //将商品信息id设置到productDescript
        productDescript.setProductInfoId(productInfo.getProductInfoId());
        productDao.insertProductDescript(productDescript);
    }
   
    /**
     * 添加商品信息
     * 需要指定主键生成规则
     * @param productInfo
     * @return
     */
    @Insert("insert into product_info(product_name,price) " +
            " values (#{productName},#{price})")
    @Options(useGeneratedKeys = true,keyProperty = "productInfoId",keyColumn = "product_info_id")
    int insertProductInfo(ProductInfo productInfo);

    /**
     * 添加商品描述信息
     * 需要指定主键生成规则
     * @param productDescript
     * @return
     */
    @Insert("insert into product_descript(product_info_id,descript) " +
            " value(#{productInfoId},#{descript})")
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    int insertProductDescript(ProductDescript productDescript);

    @Test
    public void createProduct(){
        for (int i=1;i<10;i++){
            ProductInfo productInfo = new ProductInfo();
            productInfo.setProductName("商品名称"+i);
            productInfo.setPrice(new BigDecimal(i));
            productInfo.setDescript("商品描述"+i);
            productService.createProduct(productInfo);
        }
    }

使用sharding-jdbc提供的全局主键生成方式：雪花算法，生成全局业务唯一主键product_info_id。保证product_info_id为偶数的数据插入product_info_1与product_descript_1,为奇数的数据插入product_info_2与product_descript_2

普通查询

List<ProductInfo> selectProductByIds(List<Long> productIds);
@Override
    public List<ProductInfo> selectProductByIds(List<Long> ids) {
        return productDao.selectProductByIds(ids);
    }

 @Select("<script>" +
            "select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id where i.product_info_id in <foreach collection='productIds' open='(' separator=',' close=')' item='id'> #{id} </foreach> order by product_info_id desc" +
            "</script>")
    List<ProductInfo> selectProductByIds(@Param("productIds") List<Long> productIds);

   @Test
    public void selectProductByIds(){
        List<Long> productIds = new ArrayList<>();
        productIds.add(565306560192970753L);
        productIds.add(565306560155222016L);

        List<ProductInfo> productInfos = productService.selectProductByIds(productIds);
        for (ProductInfo productInfo : productInfos) {
            System.out.println(productInfo);
        }
    }

在这里插入图片描述

分页查询

    public List<ProductInfo> selectProduct(int page, int pageSize);
    
 	@Override
    public List<ProductInfo> selectProduct(int page, int pageSize) {
        int start = (page - 1) * pageSize;
        return productDao.selectProductList(start,pageSize);
    }

  @Select("select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id order by product_info_id desc limit #{start},#{pageSize}")
    List<ProductInfo> selectProduct(@Param("start")int start, @Param("pageSize") int pageSize);

   public void selectProduct(){
        List<ProductInfo> productInfos = productService.selectProduct(1, 5);
        for (ProductInfo productInfo : productInfos) {
            System.out.println(productInfo);
        }
    }

在这里插入图片描述

七、公共表

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

创建公共表

在springboot配置文件中涉及使用的数据库中添加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;

指定公共表

指定t_dict为公共表

spring.shardingsphere.sharding.broadcast‐tables=t_dict

执行测试

插入测试

 @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);

   @Test
    public void insertDict(){
        dictDao.insertDict(1L,"role","role_1","1号管理员");
        dictDao.insertDict(2L,"role","role_2","2号管理员");
    }

对t_dict表进行插入操作，被广播至所有数据源

修改测试

@Update("update  t_dict set type=#{type},code=#{code},value=#{value} where dict_id = #{dictId}")
    int updateDict(@Param("type") String type, @Param("code") String code, @Param("value") String value, @Param("dictId") Long dictId);

   @Test
    public void updateDict(){
        dictDao.updateDict("role","1","test",2L);
    }

在这里插入图片描述

关联查询测试

    @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> selectUserByIdsAndCode(@Param("userIds") List<Long> userIds, @Param("code") String code);

   @Test
    public void selectUserByIdsAndCode(){
        List<Long> userIds = new ArrayList<>();
        userIds.add(6L);
        userIds.add(8L);
        List<Map> maps = userDao.selectUserByIdsAndCode(userIds,"1");
        for (Map map : maps) {
            System.out.println(map);
        }
    }

在这里插入图片描述

删除测试

 @Delete("delete from t_dict where dict_id = #{dictId}")
    int deleteDict(@Param("dictId") Long dictId);

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

对t_dict表进行删除操作，被广播至所有数据源

八、Sharding-JDBC读写分离

Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库，提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群

要实现Sharding-JDBC的读写分离，首先要进行MySQL主从同步配置 || 其他方式实现同步关系。

添加同步数据库

具体配置参考MySQL主从同步配置

[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-master
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-master\data

#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步的数据库,不配置则同步全部数据库 
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

规则配置

#数据源 db1:主库 db2:从库
spring.shardingsphere.datasource.names =db1,db2

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3307/user_db?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456

# 主库从库逻辑数据源定义 db为user_db
spring.shardingsphere.sharding.master-slave-rules.db.master-data-source-name=db1
spring.shardingsphere.sharding.master-slave-rules.db.slave-data-source-names=db2

#t_user分表策略，固定分配至db的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db.t_user

写入测试

  @Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")
    int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);

    @Test
    public void insertUser(){
        for (int i = 1 ; i<10; i++){
            Long id =Long.parseLong(i+"");
            userDao.insertUser(id,"姓名"+ id,i%2==0 ? "1" : "2");
        }
    }

插入数据，数据全部写入db1主库，在同步到db2从库。
在这里插入图片描述

查询测试

   @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);

    @Test
    public void selectUserByIdsAndCode(){
        List<Long> userIds = new ArrayList<>();
        userIds.add(3L);
        userIds.add(8L);
        List<Map> maps = userDao.selectUserbyIds(userIds);
        for (Map map : maps) {
            System.out.println(map);
        }
    }

查询数据，查询db2从库
在这里插入图片描述

Sharding-JDBC之分库分表与读写分离

Sharding-JDBC之分库分表与读写分离

一、分库分表

什么是分库分表

分库分表的方式

分库分表的技术模式

二、初识Sharding-JDBC

Sharding-JDBC概述

Sharding-JDBC基本概念

Sharding-JDBC执行过程

引入依赖

规则配置

创建DataSource

三、水平分表

创建数据库/表

配置分片规则

1.properties配置

2.yaml配置

3.Java配置

4.xml方式配置

编写代码

执行测试

四、水平分库

创建数据库/表

配置分片规则

执行测试

插入测试

查询测试(无分片键)

查询测试(含分片键)

五、垂直分库

创建数据库/表

配置分片规则

执行测试

六、垂直分表

创建数据库/表

配置分片

新增查询

普通查询

分页查询

七、公共表

创建公共表

指定公共表

执行测试

八、Sharding-JDBC读写分离

添加同步数据库

规则配置

写入测试

查询测试

相关文章