随笔- 100 文章- 0 评论- 3 阅读- 30413

一：海量数据下Mysql架构演变升级+分库分表优缺点

1.面试题

　　这边有个数据库-单表1千万数据，未来1年还会增⻓多 500万，性能⽐较慢，说下你的优化思路

思路
- 千万不要一上来就说分库分表，这个是最忌讳的事项
- 一定要根据实际情况分析，两个角度思考
  - 不分库分表
    - 软优化
      - 数据库参数调优
      - 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
      - 优化数据库索引结构
      - 优化数据表结构优化
      - 引入NOSQL和程序架构调整
    - 硬优化
      - 提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘
  - 分库分表
    - 根据业务情况而定，选择合适的分库分表策略（没有通用的策略）
      - 外卖、物流、电商领域
    - 先看只分表是否满足业务的需求和未来增长
      - 数据库分表能够解决单表数据量很大的时,数据查询的效率问题，
      - 无法给数据库的并发操作带来效率上的提高，分表的实质还是在一个数据库上进行的操作，受数据库IO性能的限制
    - 如果单分表满足不了需求，再分库分表一起
结论
- 在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
- 如果数据量极大，且业务持续增长快，再考虑分库分表方案

2.分库分表解决的问题

分库分表解决的现状问题
- 解决数据库本身瓶颈
  - 连接数：连接数过多时，就会出现‘too many connections’的错误，访问量太大或者数据库设置的最大连接数太小的原因
  - Mysql默认的最大连接数为100.可以修改，而mysql服务允许的最大连接数为16384
  - 数据库分表可以解决单表海量数据的查询性能问题
  - 数据库分库可以解决单台数据库的并发访问压力问题
- 解决系统本身IO、CPU瓶颈
  - 磁盘读写IO瓶颈，热点数据太多，尽管使用了数据库本身缓存，但是依旧有大量IO,导致sql执行速度慢
  - 网络IO瓶颈，请求的数据太多，数据传输大，网络带宽不够，链路响应时间变长
  - CPU瓶颈，尤其在基础数据量大单机复杂SQL计算，SQL语句执行占用CPU使用率高，也有扫描行数大、锁冲突、锁等待等原因
    - 可以通过 show processlist; 、show full processlist，发现 CPU 使用率比较高的SQL
    - 常见的对于查询时间长，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能问题SQL，清楚相关影响问题的情况可以kill掉
    - 也存在执行时间短，但是CPU占用率高的SQL，通过上面命令查询不到，这个时候最好通过执行计划分析explain进行分析

3.Mysql数据库分库分表后的六大问题你是否知道怎么解决

问题一：跨节点数据库Join关联查询
- 数据库切分前，多表关联查询，可以通过sql join进行实现
- 分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦
问题二：分库操作带来的分布式事务问题
- 操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务
问题三：执行的SQL排序、翻页、函数计算问题
- 分库后，数据分布再不同的节点上，跨节点多库进行查询时，会出现limit分页、order by排序等问题
- 而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）
问题四：数据库全局主键重复问题
- 常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题
问题五：容量规划,分库分表后二次扩容问题
- 业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容
问题六：分库分表技术选型问题
- 市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

二：常见分库分表

1.垂直分表

需求：商品表字段太多，每个字段访问频次不一样，浪费了IO资源，需要进行优化
垂直分表介绍
- 也就是“大表拆小表”，基于列字段进行的
- 拆分原则一般是表中的字段较多，将不常用的或者数据较大，长度较长的拆分到“扩展表如text类型字段
- 访问频次低、字段大的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中
- 垂直拆分原则
  - 把不常用的字段单独放在一张表;
  - 把text，blob等大字段拆分出来放在附表中;
  - 业务经常组合查询的列放在一张表中

2.垂直分库

需求：C端项目里面，单个数据库的CPU、内存长期处于90%+的利用率，数据库连接经常不够，需要进行优化
垂直分库讲解
- 垂直分库针对的是一个系统中的不同业务进行拆分，数据库的连接资源比较宝贵且单机处理能力也有限
- 没拆分之前全部都是落到单一的库上的，单库处理能力成为瓶颈，还有磁盘空间，内存，tps等限制
- 拆分之后，避免不同库竞争同一个物理机的CPU、内存、网络IO、磁盘，所以在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈
- 垂直分库可以更好解决业务层面的耦合，业务清晰，且方便管理和维护
- 一般从单体项目升级改造为微服务项目，就是垂直分库

问题：垂直分库分表可以提高并发，但是依然没有解决单表数据量过大的问题

3.水平分表

需求：当一张表的数据达到几千万时，查询一次所花的时间长，需要进行优化，缩短查询时间
都是大表拆小表
- 垂直分表：表结构拆分
- 水平分表：数据拆分
水平分表
- 把一个表的数据分到一个数据库的多张表中，每个表只有这个表的部分数据
- 核心是把一个大表，分割N个小表，每个表的结构是一样的，数据不一样，全部表的数据合起来就是全部数据
- 针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去
- 但是这些表还是在同一个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据量过大的问题
- 减少锁表时间，没分表前，如果是DDL(create/alter/add等)语句，当需要添加一列的时候mysql会锁表，期间所有的读写操作只能等待

4.水平分库

需求：高并发的项目中，水平分表后依旧在单个库上面，1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢，需要进行优化
水平分库
- 把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上
- 水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构
- 每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据
- 水平分库的粒度，比水平分表更大

5.总结

垂直角度（表结构不一样）
- 垂直分表: 将一个表字段拆分多个表，每个表存储部分字段
  - 好处: 避免IO时锁表的次数，分离热点字段和非热点字段，避免大字段IO导致性能下降
  - 原则：业务经常组合查询的字段一个表；不常用字段一个表；text、blob类型字段作为附属表
- 垂直分库：根据业务将表分类，放到不同的数据库服务器上
  - 好处：避免表之间竞争同个物理机的资源，比如CPU/内存/硬盘/网络IO
  - 原则：根据业务相关性进行划分，领域模型，微服务划分一般就是垂直分库
水平角度（表结构一样）
- 水平分库：把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上
  - 好处: 多个数据库，降低了系统的IO和CPU压力
  - 原则
    - 选择合适的分片键和分片策略，和业务场景配合
    - 避免数据热点和访问不均衡、避免二次扩容难度大
- 水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构
  - 单个表的数据量少了，业务SQL执行效率高，降低了系统的IO和CPU压力
  - 原则
    - 选择合适的分片键和分片策略，和业务场景配合
    - 避免数据热点和访问不均衡、避免二次扩容难度大

三：分库分表常见的策略

1.range

　　方案一：自增id，根据ID范围进行分表（左闭右开）

规则案例
- 1~1,000,000 是 table_1
- 1,000,000 ~2,000,000 是 table_2
- 2,000,000~3,000,000 是 table_3
- ...更多
优点
- id是自增长，可以无限增长
- 扩容不用迁移数据，容易理解和维护
缺点
- 大部分读和写都访会问新的数据，有IO瓶颈，整体资源利用率低
- 数据倾斜严重，热点数据过于集中，部分节点有瓶颈

2.range延伸

Range范围分库分表，有热点问题，所以这个没用？
- 关于怎么选择分库分表策略问题，如果业务适合就行，没有万能策略！！！！
- 基于方案一：自增id，根据ID范围进行分表延伸解决方案，你能想到多少种
范围角度思考问题 (范围的话更多是水平分表)
数字
- 自增id范围
时间
- 年、月、日范围
- 比如按照月份生成库或表 pay_log_2022_01、pay_log_2022_02
空间
- 地理位置：省份、区域（华东、华北、华南）
- 比如按照省份生成库或表

微博发送记录、微信消息记录、日志记录，id增长/时间分区都行
- 水平分表为主，水平分库则容易造成资源的浪费
网站签到等活动流水数据时间分区最好
- 水平分表为主，水平分库则容易造成资源的浪费
大区划分（一二线城市和五六线城市活跃度不一样，如果能避免热点问题，即可选择）
- saas业务水平分库（华东、华南、华北等）

3.hash取模

　　方案二：hash取模（Hash分库分表是最普遍的方案）

为啥不之间取模，如果取模的字段不是整数型要先hash，统一规则就行

　　案例规则

用户ID是整数型的，要分2库，每个库表数量4表，一共8张表
用户ID取模后，值是0到7的要平均分配到每张表

A库ID = userId % 库数量 2 
表ID = userId / 库数量 2 % 表数量4

优点
- 保证数据较均匀的分散落在不同的库、表中，可以有效的避免热点数据集中问题，
缺点
- 扩容不是很方便，需要数据迁移

四：分库分表常见的中间件

1.业界常见分库分表中间件

- Cobar（已经被淘汰没使用了）
- TDDL
  - 淘宝根据自己的业务特点开发了 TDDL （Taobao Distributed Data Layer）
  - 基于JDBC规范，没有server，以client-jar的形式存在，引入项目即可使用
  - 开源功能比较少，阿里内部使用为主
- Mycat
  - 地址 http://www.mycat.org.cn/
  - Java语言编写的MySQL数据库网络协议的开源中间件，前身 Cobar
  - 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理
  - 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库
  - 和ShardingShere下的Sharding-Proxy作用类似，需要单独部署
- ShardingSphere 下的Sharding-JDBC
  - 地址：https://shardingsphere.apache.org/
  - Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈
    - 它由 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar 3个独立产品组合
  - Sharding-JDBC
    - 基于jdbc驱动，不用额外的proxy，支持任意实现 JDBC 规范的数据库
    - 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖
    - 可理解为加强版的 JDBC 驱动，兼容 JDBC 和各类 ORM 框架

最感兴趣的是Mycat和ShardingJdbc区别，也是被面试官问比较多的
- 两者设计理念相同，主流程都是SQL解析-->SQL路由-->SQL改写-->结果归并
- sharding-jdbc
  - 基于jdbc驱动，不用额外的proxy，在本地应用层重写Jdbc原生的方法，实现数据库分片形式
  - 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的，性能高
  - 代码有侵入性
- Mycat
  - 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库
  - 客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器
  - 缺点是效率偏低，中间包装了一层
  - 代码无侵入性

2.sharding-jdbc常用术语

数据节点Node
- 数据分片的最小单元，由数据源名称和数据表组成
- 比如：ds_0.product_order_0
真实表
- 在分片的数据库中真实存在的物理表
- 比如订单表 product_order_0、product_order_1、product_order_2
逻辑表
- 水平拆分的数据库（表）的相同逻辑和数据结构表的总称
- 比如订单表 product_order_0、product_order_1、product_order_2，逻辑表就是product_order
绑定表
- 指分片规则一致的主表和子表
- 比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
- 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

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

3.分配算法

数据库表分片（水平库、表）
- 包含分片键和分片策略
分片键 (PartitionKey)
- 用于分片的数据库字段，是将数据库(表)水平拆分的关键字段
- 比如prouduct_order订单表，根据订单号 out_trade_no做哈希取模，则out_trade_no是分片键
- 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片

分片策略（先了解，后面有案例实战）

行表达式分片策略 InlineShardingStrategy（必备）
- 只支持【单分片键】使用Groovy的表达式，提供对SQL语句中的 =和IN 的分片操作支持
- 可以通过简单的配置使用，无需自定义分片算法，从而避免繁琐的Java代码开发
- prouduct_order_$->{user_id % 8}` 表示订单表根据user_id模8，而分成8张表，表名称为`prouduct_order_0`到`prouduct_order_7

标准分片策略StandardShardingStrategy（需了解）
- 只支持【单分片键】，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
- PreciseShardingAlgorithm 精准分片是必选的，用于处理=和IN的分片
- RangeShardingAlgorithm 范围分配是可选的，用于处理BETWEEN AND分片
- 如果不配置RangeShardingAlgorithm，如果SQL中用了BETWEEN AND语法，则将按照全库路由处理，性能下降

复合分片策略ComplexShardingStrategy（需了解）
- 支持【多分片键】，多分片键之间的关系复杂，由开发者自己实现，提供最大的灵活度
- 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持

Hint分片策略HintShardingStrategy（需了解）
- 这种分片策略无需配置分片健，分片健值也不再从 SQL中解析，外部手动指定分片健或分片库，让 SQL在指定的分库、分表中执行
- 用于处理使用Hint行分片的场景，通过Hint而非SQL解析的方式分片的策略
- Hint策略会绕过SQL解析的，对于这些比较复杂的需要分片的查询，Hint分片策略性能可能会更好
不分片策略 NoneShardingStrategy（需了解）
- 不分片的策略。

五：分库分表入门

1.pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jun</groupId>
    <artifactId>multidbpro</artifactId>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.12.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <!--JDK版本，如果是jdk8则这里是 1.8-->
        <java.version>1.8</java.version>
        <spring.boot.version>2.5.5</spring.boot.version>
        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <skipTests>true</skipTests>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>

        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                    <addResources>true</addResources>
                </configuration>
            </plugin>
            <!--添加配置跳过测试-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>


</project>

2.建库建表

分库分表需求
- 2库2表
数据库
- xdclass_shop_order_0
  - product_order_0
  - product_order_1
- xdclass_shop_order_1
  - product_order_0
  - product_order_1

　　对应的sql：

CREATE TABLE `product_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
  `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单',
  `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3.对应的实体类与mapper

　　实体：

package com.jun.jdbc.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("product_order")
public class ProductOrderDO {
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;
}

　　mapper：

package com.jun.jdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jun.jdbc.model.ProductOrderDO;

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

4.application

package com.jun.jdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.jun.jdbc.mapper")
public class ShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingApplication.class, args);
    }
}

5.配置文件

　　application.properties

spring.profiles.active=inline

　　application-inline.properties

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

# 数据源 db0
spring.shardingsphere.datasource.names=ds0
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456



# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}
# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

6.测试

package com.jun.jdbc;

import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
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 javax.annotation.Resource;
import java.util.Date;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class InlineTest {

    @Resource
    private ProductOrderMapper productOrderMapper;

    @Test
    public void testSaveProductOrder(){
        ProductOrderDO productOrderDO = new ProductOrderDO();
        productOrderDO.setCreateTime(new Date());
        productOrderDO.setNickname("小滴课堂i="+1);
        productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
        productOrderDO.setPayAmount(100.00);
        productOrderDO.setState("PAY");

        productOrderDO.setUserId(Long.valueOf(1+""));
        productOrderMapper.insert(productOrderDO);
    }
}

　　效果：

7.问题

主键重复问题

六：分雪花算法

1.常见的解决方案

业界常用ID解决方案
- 数据库自增ID
  - 利用自增id, 设置不同的自增步长，auto_increment_offset、auto-increment-increment
  - 缺点
    - 依靠数据库系统的功能实现，但是未来扩容麻烦
    - 主从切换时的不一致可能会导致重复发号
    - 性能瓶颈存在单台sql上
- UUID
  - 性能非常高，没有网络消耗
  - 缺点
    - 无序的字符串，不具备趋势自增特性
    - UUID太长，不易于存储，浪费存储空间，很多场景不适用
- Redis发号器
  - 利用Redis的INCR和INCRBY来实现，原子操作，线程安全，性能比Mysql强劲
  - 缺点
    - 需要占用网络资源，增加系统复杂度
- Snowflake雪花算法
  - twitter 开源的分布式 ID 生成算法，代码实现简单、不占用宽带、数据迁移不受影响
  - 生成的 id 中包含有时间戳，所以生成的 id 按照时间递增
  - 部署了多台服务器，需要保证系统时间一样，机器编号不一样
  - 缺点
    - 依赖系统时钟（多台服务器时间一定要一样）

2.什么是雪花算法Snowflake

- twitter用scala语言编写的高效生成唯一ID的算法
- 优点
  - 生成的ID不重复
  - 算法性能高
  - 基于时间戳，基本保证有序递增

雪花算法生成的数字，long类，所以就是8个byte，64bit
- 表示的值 -9223372036854775808（-2的63次方） ~ 9223372036854775807（2的63次方-1）
- 生成的唯一值用于数据库主键，不能是负数，所以值为0~9223372036854775807（2的63次方-1）

3.里面的坑

　　全局唯一不能重复-坑

坑一
- 分布式部署就需要分配不同的workId, 如果workId相同，可能会导致生成的id相同

坑二：
- 分布式情况下，需要保证各个系统时间一致，如果服务器的时钟回拨，就会导致生成的 id 重复

4.实操

　　实体类

　　　　刪除不需要的注解

package com.jun.jdbc.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("product_order")
public class ProductOrderDO {
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;
}

　　配置：

#id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

七：广播表与绑定表

1.广播表

什么是广播表
- 指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致
- 适用于数据量不大且需要与海量数据的表进行关联查询的场景
- 例如：字典表、配置表
注意点：
- 分库分表中间件，对应的数据库字段，不能是sql的关键字，否则容易出问题，且报错不明显

2.广播表配置

　　新增数据源ds1

　　新增sql：

CREATE TABLE `ad_config` (
  `id` bigint unsigned NOT NULL COMMENT '主键id',
  `config_key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
  `config_value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
  `type` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

　　对应实体类：

package com.jun.jdbc.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {
    private Long id;
    private String configKey;
    private String configValue;
    private String type;
}

　　对应的mapper：

package com.jun.jdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jun.jdbc.model.AdConfigDO;


public interface AdConfigMapper extends BaseMapper<AdConfigDO> {
}

　　对应的配置文件：

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456



#配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

　　测试类：

package com.jun.jdbc;

import com.jun.jdbc.mapper.AdConfigMapper;
import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.AdConfigDO;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class BroadcastTest {

    @Resource
    private AdConfigMapper adConfigMapper;

    @Test
    public void testSaveAdConfig() {
        AdConfigDO adConfigDO = new AdConfigDO();
        adConfigDO.setConfigKey("banner");
        adConfigDO.setConfigValue("xdclass.net");
        adConfigDO.setType("ad");
        adConfigMapper.insert(adConfigDO);
    }
}

　　效果：

3.绑定表

什么是绑定表
- 指分片规则一致的主表和子表
- 比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
- 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

4.绑定表配置

　　sql执行：

CREATE TABLE `product_order_item_0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_order_id` bigint DEFAULT NULL COMMENT '订单号',
  `product_id` bigint DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

　　配置文件：

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456


#配置【默认分库策略】
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2 }
#配置分库规则
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 }


# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}


# 指定product_order_item表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order_item.actual-data-nodes=ds$->{0..1}.product_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}

#配置绑定表
spring.shardingsphere.sharding.binding-tables[0] = product_order,product_order_item

　　实体类：

package com.jun.jdbc.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;


@Data
@TableName("product_order_item")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderItemDO {

    private Long id;

    private Long productOrderId;

    private Long productId;

    private String productName;

    private Integer buyNum;

    private Long userId;

}

　　mapper：

package com.jun.jdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jun.jdbc.model.ProductOrderItemDO;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ProductOrderItemMapper extends BaseMapper<ProductOrderItemDO> {
    @Select("select * from product_order o left join product_order_item i on o.id=i.product_order_id")
    List<Object> listProductOrderDetail();
}

　　测试：

package com.jun.jdbc;

import com.jun.jdbc.mapper.AdConfigMapper;
import com.jun.jdbc.mapper.ProductOrderItemMapper;
import com.jun.jdbc.model.AdConfigDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class BindingTest {

    @Resource
    private ProductOrderItemMapper productOrderItemMapper;

    @Test
    public void testBingding(){
        List<Object> list = productOrderItemMapper.listProductOrderDetail();
        System.out.println(list);
    }
}

　　效果：

八：核心流程与多种分片路由

1.核心流程

Sharding-JDBC做的事情
- 长：SQL解析 -> SQL优化 -> SQL路由 -> SQL改写 -> SQL执行 -> 结果归并 ->返回结果
- 短：解析->路由->改写->执行->结果归并

2.标准分片策略+精准分片算法《分表》

StandardShardingStrategy
- 只支持【单分片键】，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
- PreciseShardingAlgorithm 精准分片是必选的，用于处理=和IN的分片
- RangeShardingAlgorithm 范围分片是可选的，用于处理BETWEEN AND分片
- 如果不配置RangeShardingAlgorithm，如果SQL中用了BETWEEN AND语法，则将按照全库路由处理，性能下降

分片算法：

package com.jun.jdbc.strategy.standard;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

public class CustomTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * @param dataSourceNames 数据源集合
     *                        在分库时值为所有分片库的集合 databaseNames
     *                        分表时为对应分片库中所有分片表的集合 tablesNames
     * @param shardingValue   分片属性，包括
     *                        logicTableName 为逻辑表，
     *                        columnName 分片健（字段），
     *                        value 为从 SQL 中解析出的分片健的值
     * @return
     */
    @Override
    public String doSharding(Collection<String> dataSourceNames, PreciseShardingValue<Long> preciseShardingValue) {
        for (String datasourceName : dataSourceNames) {
            String value = preciseShardingValue.getValue() % dataSourceNames.size() + "";
            //product_order_0
            if (datasourceName.endsWith(value)) {
                return datasourceName;
            }
        }
        return null;
    }
}

　　配置：

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

##id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

# 精准分片-水平分表
# 指定product_order表的数据分布情况，配置数据节点,在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}

#指定精准分片算法(水平分表)
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomTablePreciseShardingAlgorithm

　　测试类：

package com.jun.jdbc;

import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.Random;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class StandardTableTest {

    @Resource
    private ProductOrderMapper productOrderMapper;

    /**
     * 精准分片-分表测试
     */
    @Test
    public void testSaveProductOrder() {
        Random random = new Random();
        for (int i = 0; i < 20; i++) {
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("小滴课堂 PreciseShardingAlgorithm i=" + i);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
            productOrderDO.setPayAmount(100.00);
            productOrderDO.setState("PAY");

            productOrderDO.setUserId(Long.valueOf(random.nextInt(50)));

            productOrderMapper.insert(productOrderDO);

        }
    }
}

3.标准分片策略+精准分片算法《分库分表》

　　分片算法

package com.jun.jdbc.strategy.standard;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

public class CustomDatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * @param dataSourceNames 数据源集合
     *                        在分库时值为所有分片库的集合 databaseNames
     *                        分表时为对应分片库中所有分片表的集合 tablesNames
     * @param shardingValue   分片属性，包括
     *                        logicTableName 为逻辑表，
     *                        columnName 分片健（字段），
     *                        value 为从 SQL 中解析出的分片健的值
     * @return
     */
    @Override
    public String doSharding(Collection<String> dataSourceNames, PreciseShardingValue<Long> preciseShardingValue) {

        for (String datasourceName : dataSourceNames) {

            String value = preciseShardingValue.getValue() % dataSourceNames.size() + "";
            //product_order_0
            if (datasourceName.endsWith(value)) {
                return datasourceName;
            }
        }
        return null;
    }
}

　　分片配置

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

##id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

#精准分片-水平分表
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}

#指定精准分片算法(水平分库) 根据user_id分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.precise-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomDatabasePreciseShardingAlgorithm

#指定精准分片算法(水平分表) 根据订单id分表
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomTablePreciseShardingAlgorithm

　　测试类：

package com.jun.jdbc;

import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.Random;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class StandardDbTest {

    @Resource
    private ProductOrderMapper productOrderMapper;

    /**
     * 精准分片-分表测试
     */
    @Test
    public void testSaveProductOrder() {
        Random random = new Random();
        for (int i = 0; i < 20; i++) {
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("小滴课堂 PreciseShardingAlgorithm i=" + i);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0, 32));
            productOrderDO.setPayAmount(100.00);
            productOrderDO.setState("PAY");

            productOrderDO.setUserId(Long.valueOf(random.nextInt(50)));

            productOrderMapper.insert(productOrderDO);

        }
    }
}

4.标准分片策略+范围分片策略《分库分表》

RangeShardingAlgorithm 范围分片
- 用于处理BETWEEN AND语法，没配置的话会报错 Cannot find range sharding strategy in sharding rule.
- 主要是会根据 SQL中给出的分片健值范围值处理分库、分表逻辑

　　分片算法

package com.jun.jdbc.strategy.standard;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Set;

/**
 * 范围分片
 **/

public class CustomRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    /**
     * @param dataSourceNames 数据源集合
     *                        在分库时值为所有分片库的集合 databaseNames
     *                        分表时为对应分片库中所有分片表的集合 tablesNames
     * @param shardingValue   分片属性，包括
     *                        logicTableName 为逻辑表，
     *                        columnName 分片健（字段），
     *                        value 为从 SQL 中解析出的分片健的值
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> dataSourceNames, RangeShardingValue<Long> shardingValue) {
        Set<String> result = new LinkedHashSet<>();

        //between 开始值
        Long lower = shardingValue.getValueRange().lowerEndpoint();

        //between 结束值
        Long upper = shardingValue.getValueRange().upperEndpoint();

        for (long i = lower; i <= upper; i++) {
            for (String datasource : dataSourceNames) {
                String value = i % dataSourceNames.size() + "";
                if (datasource.endsWith(value)) {
                    result.add(datasource);
                }
            }

        }
        return result;
    }
}

　　配置：

　　只需精准水平分表下，增加一个范围分片

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

##id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

#精准分片-水平分表
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}

#指定精准分片算法(水平分库) 根据user_id分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.standard.precise-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomDatabasePreciseShardingAlgorithm

#指定精准分片算法(水平分表) 根据订单id分表
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomTablePreciseShardingAlgorithm

#范围分片(水平分表)
spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.range-algorithm-class-name=com.jun.jdbc.strategy.standard.CustomRangeShardingAlgorithm

　　测试：

package com.jun.jdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.Random;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class StandardRangeTest {

    @Resource
    private ProductOrderMapper productOrderMapper;

    /**
     * 精准分片-分表测试
     */
    @Test
    public void testBetween(){
        productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().between("id",1L,3L));
    }
}

5.复合分片算法

复合分片算法ComplexShardingStrategy (了解即可)
- 提供对SQL语句中的=, IN和BETWEEN AND的分片操作，支持【多分片键】
- 由于多分片键之间的关系复杂，Sharding-JDBC并未做过多的封装
- 而是直接将分片键值组合以及分片操作符交于算法接口，全部由应用开发者实现，提供最大的灵活度

　　分片算法：

package com.jun.jdbc.strategy.complex;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;


public class CustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
    /**
     * @param dataSourceNames 数据源集合
     *                        在分库时值为所有分片库的集合 databaseNames
     *                        分表时为对应分片库中所有分片表的集合 tablesNames
     * @param shardingValue   分片属性，包括
     *                        logicTableName 为逻辑表，
     *                        columnName 分片健（字段），
     *                        value 为从 SQL 中解析出的分片健的值
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> dataSourceNames, ComplexKeysShardingValue<Long> complexKeysShardingValue) {

        // 得到每个分片健对应的值
        Collection<Long> orderIdValues = this.getShardingValue(complexKeysShardingValue, "id");
        Collection<Long> userIdValues = this.getShardingValue(complexKeysShardingValue, "user_id");

        List<String> shardingSuffix = new ArrayList<>();
        // 对两个分片健取模的方式 product_order_0_0、product_order_0_1、product_order_1_0、product_order_1_1
        for (Long userId : userIdValues) {
            for (Long orderId : orderIdValues) {
                String suffix = userId % 2 + "_" + orderId % 2;
                for (String databaseName : dataSourceNames) {
                    if (databaseName.endsWith(suffix)) {
                        shardingSuffix.add(databaseName);
                    }
                }
            }
        }
        return shardingSuffix;
    }

    /**
     * shardingValue  分片属性，包括
     * logicTableName 为逻辑表，
     * columnNameAndShardingValuesMap 存储多个分片健 包括key-value
     * key：分片key，id和user_id
     * value：分片value，66和99
     *
     * @return shardingValues 集合
     */
    private Collection<Long> getShardingValue(ComplexKeysShardingValue<Long> shardingValues, final String key) {
        Collection<Long> valueSet = new ArrayList<>();
        Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValues.getColumnNameAndShardingValuesMap();

        if (columnNameAndShardingValuesMap.containsKey(key)) {
            valueSet.addAll(columnNameAndShardingValuesMap.get(key));
        }
        return valueSet;
    }

}

　　配置：

server.port=8080
spring.application.name=jdbc

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true


# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

##id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

## 复合分片算法,order_id,user_id 同时作为分片健
spring.shardingsphere.sharding.tables.product_order.table-strategy.complex.sharding-columns=user_id,id
spring.shardingsphere.sharding.tables.product_order.table-strategy.complex.algorithm-class-name=com.jun.jdbc.strategy.complex.CustomComplexKeysShardingAlgorithm

　　测试：

package com.jun.jdbc;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jun.jdbc.mapper.ProductOrderMapper;
import com.jun.jdbc.model.ProductOrderDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.Random;
import java.util.UUID;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingApplication.class)
@Slf4j
public class ComplexTest {

    @Resource
    private ProductOrderMapper productOrderMapper;

    /**
     * 复杂分片测试
     */
    @Test
    public void testMultiPartitionKeySelect() {
        productOrderMapper.selectList(new QueryWrapper<ProductOrderDO>().eq("id", 66L).eq("user_id", 99L));
    }
}

九：常见问题解决方案

1.分库分表解决的三大问题

　　问题一：执行的SQL排序、翻页、函数计算问题

分库后，数据分布再不同的节点上，跨节点多库进行查询时，会出现limit分页、order by排序等问题
而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）
解决方式：
- 业务上要设计合理，利用好PartitionKey，查询的数据分布同个数据节点上，避免跨节点多库进行查询时
- sharding-jdbc在结果合并层自动帮我们解决很多问题（流式归并和内存归并）

　　问题二：数据库全局主键重复问题

常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题
解决方式：
- UUID
- 自研发号器 redis
- 雪花算法

问题三：分库分表技术选型问题
- 市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择
- 解决方式
  - 开源产品：主要是Mycat和ShardingJdbc区别，也是被面试官问比较多的
    - 两者设计理念相同，主流程都是SQL解析-->SQL路由-->SQL改写-->结果归并
    - sharding-jdbc（推荐）
      - 基于jdbc驱动，不用额外的proxy，在本地应用层重写Jdbc原生的方法，实现数据库分片形式
      - 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的，性能高
      - 代码有侵入性
    - Mycat
      - 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库
      - 客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器
      - 缺点是效率偏低，中间包装了一层
      - 代码无侵入性

2.join关联与多维度查询

问题：跨节点数据库Join关联查询和多维度查询
- 数据库切分前，多表关联查询，可以通过sql join进行实现
- 分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦
- 不同维度查看数据，利用的partitionKey是不一样的
- 解决方案
  - 冗余字段
  - 广播表
  - NOSQL汇总
- 案例一
  - 订单需要用户的基本信息，但是分布在不同库上
  - 进行字段冗余，订单表冗余用户昵称、头像
- 案例二
  - 订单表的partionKey是user_id，用户查看自己的订单列表方便
  - 但商家查看自己店铺的订单列表就麻烦，分布在不同数据节点
    - 订单冗余存储在es上一份
    - 业务架构流程

3.分布式事务的问题

常见分布式事务解决方案
- 2PC 和 3PC
  - 两阶段提交, 基于XA协议
- TCC
  - Try、Confirm、Cancel
- 事务消息
  - 最大努力通知型
分布式事务框架
- TX-LCN：支持2PC、TCC等多种模式
  - https://github.com/codingapi/tx-lcn
  - 更新慢（个人感觉处于停滞状态）
- Seata：支持 AT、TCC、SAGA 和 XA 多种模式
  - https://github.com/seata/seata
  - 背靠阿里，专门团队推广
  - 阿里云商业化产品GTS
    - https://www.aliyun.com/aliware/txc
- RocketMq：自带事务消息解决分布式事务
  - https://github.com/apache/rocketmq
高并发项目大课、海量数据项目大课
- MQ+本地Task

4.二次扩容

问题：容量规划,分库分表后二次扩容问题
- 业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容，数据迁移问题
- 取决是哪种分库分表规则
  - Range范围
    - 时间：不用考虑扩容迁移
    - 区域：调整分片粒度，需要全量迁移
  - Hash取模
    - 解决方式
      - 业务最多的是hash取模分片，因扩分库分表涉及到rehash过程
      - 分片数量建议可以成倍扩容策略，只需要【迁移部分数据】即可
        
        旧节点的数据，有一半要迁移至一个新增节点中
    - 更多解决方式
      - 利用一致性Hash思想，增加虚拟节点，减少迁移数据量
      - 专门的数据库表，记录数据存储位置，进行路由
      - ...

posted on 2022-11-22 23:38 曹军阅读(158) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· flink入门学习

· 大数据存储基石进一步学习

· shardingsphere

· 分库分表方案

· WM_分库分表的深入实战剖析v1.0 跨节点join、分页排序分表 io和cpu瓶颈一般有用看4 速

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）

公告

昵称：曹军
园龄： 8年5个月
粉丝： 151
关注： 19

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

elasticsearch(6)

es(1)

flink(4)

git(1)

graph(1)

hbase(6)

java(20)

kafka(1)

linux(5)

mybatis(2)

mysql(4)

netty(4)

nginx(2)

opentsdb(2)

python(2)

rabbitmq(1)

redis(3)

rocketmq(4)

sharding-jdbc(1)

spring security(1)

SRS流媒体(5)

zookeeper(1)

机器学习(7)

面试(1)

萍(2)

前端学习(2)

算法(8)

项目(1)

随笔档案

2024年4月(1)

2024年3月(3)

2023年9月(8)

2023年8月(1)

2023年7月(4)

2023年6月(1)

2023年5月(1)

2023年4月(5)

2023年3月(4)

2023年2月(4)

2023年1月(1)

2022年12月(2)

2022年11月(2)

2022年10月(1)

2022年9月(6)

2022年8月(11)

2022年7月(10)

2022年6月(13)

2022年5月(11)

2022年4月(3)

2022年3月(7)

2022年2月(1)

阅读排行榜

评论排行榜

1. 回来(2)

一：海量数据下Mysql架构演变升级+分库分表优缺点

二：常见分库分表

三：分库分表常见的策略

四：分库分表常见的中间件

五：分库分表入门

六：分雪花算法

七：广播表与绑定表

八：核心流程与多种分片路由

九：常见问题解决方案

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论