为什么要分库分表？

 

减小数据库的负担，缩短查询时间。

1、数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大;

2、数据操作，增删改查的开销也会越来越大；

3、服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

4、当一张表的数据达到几千万时，查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。

 

#指标	分库分表前	分库分表后
并发支撑情况	MySQL 单机部署，扛不住高并发	MySQL从单机到多机，能承受的并发增加了多倍
磁盘使用情况	MySQL 单机磁盘容量几乎撑满	拆分为多个库，数据库服务器磁盘使用率大大降低
SQL 执行性能	单表数据量太大，SQL 越跑越慢	单表数据量减少，SQL 执行效率明显提升

 

常见的分库分表中间件对比

功能	Sharding-JDBC	TDDL	Cobar	MyCat	Atlas
基于客户端还是服务端	客户端	客户端	服务端	服务端	服务端
分库分表	有	有	有	有	有
中间层	否	否	是	是	是
支持的数据库	任意	任意	MySQL	任意	Mysql
开发团队	当当-->apache	阿里	阿里	社区	360

 

常见分库分表算法:

取模分片算法

公式：key mod x （x为自然数）

 

哈希取模分片算法

公式：hash(key) mod x（x为自然数）

 

基于分片边界的范围分片算法

根据key键的范围进行分片(BETWEEN AND)

 

基于日期进行分片

指定划分的开始时间以及结束时间,同时指定每个分区所占用的时间段范围进行分片

分库分表实施策略

垂直拆分：

一个数据库由很多表构成，每个表对应着不同的业务，垂直切分是指:按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者压力分担到不同的库上面

水平拆分：

水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。

 

垂直拆分的优点

1. 跟随业务进行分割，和最近流行的微服务概念相似，方便解耦之后的管理及扩展

2. 高并发的场景下，垂直拆分使用多台服务器的CPU、I/O、内存能提升性能

3. 能实现冷热数据的分离

垂直拆分的缺点

1. 部分业务表无法join，应用层需要很大的改造，只能通过聚合的方式来实现

2. 当单库中的表数据量增大的时候依然没有得到有效的解决

3. 涉及到分布式事物

水平拆分的优点

1. 不存在单库大数据和高并发的性能瓶颈

2. 提高了系统的稳定性和负载能力

3. 应用侧的改动较小，不需要根据业务来拆分

水平拆分的缺点

1. 需要增加一层路由的计算，不带分片键查询会产生广播SQL。

2. 跨库join的性能比较差

3. 涉及到分布式事物

 

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特性

分库分表

• sql解析功能完善,支持聚合,分组,排序,limit等查询,并且支持级联表和笛卡尔积的表查询
• 支持内/外连接查询
• 分片策略灵活,可支持=,between,in等多维度分片,以及自定义分片策略
• 基于hint的强制分库分表路由

读写分离

• 一主多从的读写分离配置,可配合分库分表使用
• 基于hint的强制分库分表路由

事物

• 标准化事务接口
• XA 强一致事务
• 柔性事务

其他

• 统一的分布式基于时间序列id生成器
• 可适用于java的ORM框架
• 可基于第三方数据库连接池

Sharding-JDBC性能分析

 

 

Sharding-JDBC流程

Sharding jdbc是基于JDBC协议实现的，当我们获得dataSource时，这个dataSource是Sharding jdbc自己定义的一个SpringShardingDataSource类型的数据源；

该数据源在返回getConnection()及prepareStatement()时，分别返回ShardingConnection和ShardingPreparedStatement的实例对象。

在executeQuery()时，ShardingPreparedStatement会根据逻辑sql，经过分库分表策略逻辑计算，获得分库分表的路由结果SQLRouteResult；

SQLRouteResult中包含真实的数据源以及转换后的真正sql，利用真实的数据源去执行获得ResultSet；

 

一共有以下几个阶段

SQL 解析 => 执行器优化 => SQL 路由 => SQL 改写 => SQL 执行 => 结果归并

1. SQL解析:分为词法解析和语法解析。 先通过词法解析器将 SQL 拆分为一个个不可再分的单词。再使用语法解析器对 SQL 进行理解，并最终提炼出解析上下文。

2. 执行器优化:合并和优化分片条件，如 OR 等。

3. SQL 路由:根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。

4. SQL 改写:将 SQL 改写为在真实数据库中可以正确执行的语句。

5. SQL 执行:通过多线程执行器异步执行。

6. 结果归并:将多个执行结果集归并以便于通过统一的 JDBC 接口输出。

 

sharding-jdbc核心概念

• 逻辑表：水平拆分的数据库（表）的相同逻辑和数据结构表的总称
  • 例：订单数据根据主键尾数拆分为10张表，分别是t_order_0到t_order_9，他们的逻辑表名为t_order
     
• 真实表：在分片的数据库中真实存在的物理表
  • 即上个示例中的t_order_0到t_order_9
     
• 数据节点：数据分片的最小单元
  • 由数据源名称和数据表组成，例：ds_0.t_order_0
     
• 动态表：逻辑表和物理表不一定需要在配置规则中静态配置
  • 如，按照日期分片的场景，物理表的名称随着时间的推移会产生变化
     
• 绑定表：指分片规则一致的主表和子表
  • t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系
  • 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升
     
• 广播表：指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致
  • 例如：字典表
     
• 分片键：用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。
  • 例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段

  • 

     

 

如何使用:

1. 引入maven依赖

 

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>${latest.release.version}</version>
</dependency>

 

2. 规则配置

Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置。

 

JAVA

 

SpringBoot

 

3. 强制路由

ShardingSphere使用ThreadLocal管理分片键值进行Hint强制路由。

可以通过编程的方式向HintManager中添加分片值，该分片值仅在当前线程内生效。

 

Hint方式主要使用场景：

1.分片字段不存在SQL中、数据库表结构中，而存在于外部业务逻辑。

2.强制在主库进行某些数据操作。

 

基于暗示(Hint)的数据分片

Hint分片算法需要用户实现org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm接口。

ShardingSphere在进行Routing时，如果发现LogicTable的TableRule采用了 Hint的分片算法，将会从HintManager中获取分片值进行路由操作。

 

添加分片键值

• 使用hintManager.addDatabaseShardingValue来添加数据源分片键值。
• 使用hintManager.addTableShardingValue来添加表分片键值。

分库不分表情况下，强制路由至某一个分库时，可使用hintManager.setDatabaseShardingValue方式添加分片。通过此方式添加分片键值后，将跳过SQL解析和改写阶段，从而提高整体执行效率。

 

基于暗示(Hint)的强制主库路由

        使用hintManager.setMasterRouteOnly设置主库路由。

 

---

 

实战背景分析:

• 消息表目前总数据:2亿
• 每日新增数据:9.1号: 261W 、9.2号:261W 、9.3号:388W 、9.4号:268W 、9.5号:352W 

改造一共经过四个流程

• 

 

 

 

分库分表配置

数据源配置

spring.shardingsphere.datasource.ds4.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds4.initial-size=5
spring.shardingsphere.datasource.ds4.max-wait=2000
spring.shardingsphere.datasource.ds4.min-idle=5
spring.shardingsphere.datasource.ds4.max-active=20
spring.shardingsphere.datasource.ds4.validation-query=SELECT 1
spring.shardingsphere.datasource.ds4.test-on-borrow=false
spring.shardingsphere.datasource.ds4.test-while-idle=true
spring.shardingsphere.datasource.ds4.query-timeout=10
spring.shardingsphere.datasource.ds4.not-full-timeout-retry-count=3
spring.shardingsphere.datasource.ds4.time-between-eviction-runs-millis=60000
spring.shardingsphere.datasource.ds4.min-evictableIdle-time-millis=300000
spring.shardingsphere.datasource.ds4.connection-init-sqls=set names utf8mb4;
spring.shardingsphere.datasource.ds4.filters=stat

spring.shardingsphere.datasource.ds4.url=jdbc:mysql://xxxxxx:3306/test4?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai&autoReconnect=true
spring.shardingsphere.datasource.ds4.username=xxxx
spring.shardingsphere.datasource.ds4.password=xxxxxxx


spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = chat_number
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{Math.abs(chat_number.hashCode())% 5}
spring.shardingsphere.sharding.tables.chat_msg.table-strategy.inline.sharding-column=chat_number
spring.shardingsphere.sharding.tables.chat_msg.table-strategy.inline.algorithm-expression=chat_msg_$->{Math.abs(chat_number.hashCode())% 100}
spring.shardingsphere.sharding.tables.chat_msg.actual-data-nodes=ds$->{0..4}.chat_msg_$->{0..99}
spring.shardingsphere.props.sql.show = false

 

基于springboot的快速集成多数据源的启动器。

<dependency>
 <groupId>com.baomidou</groupId>
 <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
 <version>3.1.1</version>
</dependency>

如何接入:

1、排除原生Druid的快速配置类。

DruidDataSourceAutoConfigure在DynamciDataSourceAutoConfiguration之前，其会注入一个DataSourceWrapper;

会在原生的spring.datasource下找url,username,password等。

而我们动态数据源的配置路径是变化的。

 

 

2、配置数据源,删除旧的形式数据源配置

spring.datasource.dynamic.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.dynamic.druid.initial-size=5
spring.datasource.dynamic.druid.max-wait=2000
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.max-active=20
spring.datasource.dynamic.druid.validation-query=
spring.datasource.dynamic.druid.init-connection-sqls=set names utf8mb4;
spring.datasource.dynamic.druid.filters=stat
spring.datasource.dynamic.druid.proxyFilters = slowSqlAlertFilter
spring.datasource.dynamic.druid.stat.enabled=true
spring.datasource.dynamic.druid.stat.db-type=mysql
spring.datasource.dynamic.druid.stat.log-slow-sql=false
spring.datasource.dynamic.druid.stat.slow-sql-millis=3000
spring.datasource.dynamic.druid.stat.merge-sql=true
spring.datasource.dynamic.druid.stat.alert-app-id=6e076e7d-5120-4dbc-b582-07d2ac571628
spring.datasource.dynamic.datasource.master.url=jdbc:mysql://xxxxx:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai&autoReconnect=true
spring.datasource.dynamic.datasource.master.username=xxxxxx
spring.datasource.dynamic.datasource.master.password=xxxx
spring.datasource.dynamic.primary=master

 

3、整合Sharding-Jdbc

通过 @DS(DataSourceConstant.SHARDING)进行切换分库分表数据源

 

3、使用

 

 

问题一:

spring.shardingsphere.datasource.ds4.connection-init-sqls=set names utf8mb4;

 

参数:connection-init-sqls，它其实定义是个集合，在DruidAbstractDataSource里面的方法接收的是一个集合

DruidAbstractDataSource源码

 

shardingsphere下DataSourceUtil源码

 

 

static {
    generalClassType = Sets.newHashSet(new Class[]{Boolean.TYPE, Boolean.class, Integer.TYPE, Integer.class, Long.TYPE, Long.class, String.class});
}

解决办法:

重写shardingsphere下DataSourceUtil类,添加Collection.class至generalClassType集合

 

static {
    generalClassType = Sets.<Class<?>>newHashSet(boolean.class, Boolean.class, int.class, Integer.class, long.class, Long.class, String.class, Collection.class);
}

 

数据同步方案对比

落地实现方案一：

 

落地实现方案二（采用）：