有关分库分表 ShardingSphere-JDBC，这是我见过整理的最全的笔记了

一、分库分表解决的现状问题

• 解决数据库本身瓶颈

  连接数： 连接数过多时，就会出现‘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 进行分析

相关视频解析：mysql视频精讲

二、垂直和水平分库分表区别

• 垂直角度（表结构不一样）垂直分表: 将一个表字段拆分多个表，每个表存储部分字段好处: 避免 IO 时锁表的次数，分离热点字段和非热点字段，避免大字段 IO 导致性能下降原则：业务经常组合查询的字段一个表；不常用字段一个表；text、blob 类型字段作为附属表垂直分库：根据业务将表分类，放到不同的数据库服务器上好处：避免表之间竞争同个物理机的资源，比如 CPU/内存/硬盘/网络 IO 原则：根据业务相关性进行划分，领域模型，微服务划分一般就是垂直分库

• 水平角度（表结构一样）水平分库：把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上好处: 多个数据库，降低了系统的 IO 和 CPU 压力原则选择合适的分片键和分片策略，和业务场景配合避免数据热点和访问不均衡、避免二次扩容难度大水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构单个表的数据量少了，业务 SQL 执行效率高，降低了系统的 IO 和 CPU 压力原则选择合适的分片键和分片策略，和业务场景配合避免数据热点和访问不均衡、避免二次扩容难度大

2.1 垂直分表

• 也就是“大表拆小表”，基于列字段进行的

• 拆分原则一般是表中的字段较多，将不常用的或者数据较大，长度较长的拆分到“扩展表 如 text 类型字段

• 访问频次低、字段大的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中

• 垂直拆分原则

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

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

  业务经常组合查询的列放在一张表中

2.2 垂直分库

• 垂直分库针对的是一个系统中的不同业务进行拆分， 数据库的连接资源比较宝贵且单机处理能力也有限

• 没拆分之前全部都是落到单一的库上的，单库处理能力成为瓶颈，还有磁盘空间，内存，tps 等限制

• 拆分之后，避免不同库竞争同一个物理机的 CPU、内存、网络 IO、磁盘，所以在高并发场景下，垂直分库一定程度上能够突破 IO、连接数及单机硬件资源的瓶颈

• 垂直分库可以更好解决业务层面的耦合，业务清晰，且方便管理和维护

• 一般从单体项目升级改造为微服务项目，就是垂直分库

2.3 水平分表

• 把一个表的数据分到一个数据库的多张表中，每个表只有这个表的部分数据

• 核心是把一个大表，分割 N 个小表，每个表的结构是一样的，数据不一样，全部表的数据合起来就是全部数据

• 针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH 取模等），切分到多张表里面去

• 但是这些表还是在同一个库中，所以单数据库操作还是有 IO 瓶颈，主要是解决单表数据量过大的问题

• 减少锁表时间，没分表前，如果是 DDL(create/alter/add 等)语句，当需要添加一列的时候 mysql 会锁表，期间所有的读写操作只能等待

2.4 水平分库

• 把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上

• 水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构

• 每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据

• 水平分库的粒度，比水平分表更大

​

三、水平分库分表常见策略

3.1 Range

• 范围角度思考问题 (范围的话更多是水平分表)

  数字

  自增 id 范围

  时间

  年、月、日范围

  比如按照月份生成 库或表 pay_log_2022_01、pay_log_2022_02

  空间

  地理位置：省份、区域（华东、华北、华南）

  比如按照 省份 生成 库或表

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

• 规则案例 1~1,000,000 是 table_11,000,000 ~2,000,000 是 table_22,000,000~3,000,000 是 table_3...更多

• 优点 id 是自增长，可以无限增长扩容不用迁移数据，容易理解和维护

• 缺点大部分读和写都访会问新的数据，有 IO 瓶颈，整体资源利用率低数据倾斜严重，热点数据过于集中，部分节点有瓶颈

基于 Range 范围分库分表业务场景

• 微博发送记录、微信消息记录、日志记录，id 增长/时间分区都行水平分表为主，水平分库则容易造成资源的浪费

• 网站签到等活动流水数据时间分区最好水平分表为主，水平分库则容易造成资源的浪费

• 大区划分（一二线城市和五六线城市活跃度不一样，如果能避免热点问题，即可选择）saas 业务水平分库（华东、华南、华北等）

3.2Hash 取模

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

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

• 案例规则

  用户 ID 是整数型的，要分 2 库，每个库表数量 4 表，一共 8 张表

  用户 ID 取模后，值是 0 到 7 的要平均分配到每张表

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

• 优点保证数据较均匀的分散落在不同的库、表中，可以有效的避免热点数据集中问题，

• 缺点扩容不是很方便，需要数据迁移

四、实现方案 ShardingSphere-JDBC

• 地址：https://shardingsphere.apache.org/
  

• Sharding-JDBC

  基于 jdbc 驱动，不用额外的 proxy，支持任意实现 JDBC 规范的数据库

  它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖

  可理解为加强版的 JDBC 驱动，兼容 JDBC 和各类 ORM 框架

• 它使用客户端直连数据库，以 jar 包形式提供服务

• 无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架

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

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

• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库

• 采用无中心化架构，与应用程序共享资源，适用于 Java 开发的高性能的轻量级 OLTP 应用

4.1 常见概念术语讲解

• 数据节点 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 分片，则此两张表互为绑定表关系绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升

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

4.2 常见分片算法讲解

分片算法包括两部分：包含分片键和分片策略

• 分片键 (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（需了解）

  不分片的策略。

4.3 执行流程原理

执行过程为：SQL 解析 -> SQL 优化 -> SQL 路由 -> SQL 改写 -> SQL 执行 -> 结果归并 ->返回结果

原文：https://www.cnblogs.com/yuanbeier/p/16574366.html