一:海量数据下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
-
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 多种模式
-
背靠阿里,专门团队推广
-
阿里云商业化产品GTS
-
RocketMq:自带事务消息解决分布式事务
-
-
高并发项目大课、海量数据项目大课
- MQ+本地Task
4.二次扩容
-
问题:容量规划,分库分表后二次扩容问题
-
业务发展快,初次分库分表后,满足不了数据存储,导致需要多次扩容,数据迁移问题
-
取决是哪种分库分表规则
-
Range范围
- 时间:不用考虑扩容迁移
- 区域:调整分片粒度,需要全量迁移
-
Hash取模
-
解决方式
-
业务最多的是hash取模分片,因扩分库分表涉及到rehash过程
-
分片数量建议可以成倍扩容策略,只需要【迁移部分数据】即可
- 旧节点的数据,有一半要迁移至一个新增节点中
-
-
更多解决方式
- 利用一致性Hash思想,增加虚拟节点,减少迁移数据量
- 专门的数据库表,记录数据存储位置,进行路由
- ...
-
-
-
5.