海量数据存储Sharding-JDBC分库分表1

1.分库分表简介

1.1.MySQL架构演变

• 单机

• 请求量大查询慢
• 单机故障导致业务不可用

• 主从

• 数据库主从同步，从库可以水平扩展，满足更大读需求
• 但单服务器TPS，内存，IO都是有限的

• 双主

• 用户量级上来后，写请求越来越多
• 一个Master是不能解决问题的，添加多了个主节点进行写入，

• 多个主节点数据要保存一致性，写操作需要2个master之间同步更加复杂

• 分库和分表
  

1.2.数据库性能优化思路

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

• 不分库分表

• 软优化

• 数据库参数调优
• 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
• 
  

• 优先数据库索引结构
• 优化数据表结构优化
• 引入NOSQL和程序架构调整

• 硬优化

• 提升系统硬件（更快的IO、更多的内存）：宽带、CPU、硬盘

• 分库分表

• 根据业务情况而定，选择合适的分库分表策略
• 先看只分表是否能满足业务的需求和未来的增长

• 数据库分表能够解决单表数据量很大时，数据查询的效率问题
• 无法给数据库的并发操作带来效率上的提高，分表的实质还是在一个数据库上进行的操作，受数据库IO性能的限制

• 如果单分表满足不了需求，在分库分表一起使用

• 结论

• 在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。
• 如果数据量极大，且业务持续增长快，在考虑分库分表方案。

1.3.分库分表带来的优点

1、解决数据库本身瓶颈

• 连接数：连接数过多时，就会出现“too many connections”的错误，访问量太大或者数据库设置的最大连接数太小的原因。
• MySQL默认的最大连接数为100，可以修改，而mysql服务允许的最大连接数为16384。
• 数据库分表可以解决单表海量数据的查询性能问题。
• 数据库分库可以解决单台数据库的并发访问压力问题。

2、解决系统本身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进行分析

1.4.分库分表后的六大问题

• 问题一：跨界点数据库join关联查询

• 数据库切分前，多表关联查询，可以通过sql join进行实现。
• 分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦。

• 问题二：分库操作带来的分布式事务问题

• 操作内容同时分布在不同的库中，不可避免会带来跨库事务的问题，即分布式事务。

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

• 分库后，数据分布在不同的节点上，跨节点多库进行查询时，会出现limit分页、order by排序等问题。
• 而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）。

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

• 常见表的id是使用自增id进行实现的，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题。

• 问题五：容量规划，分库分表后二次扩容问题

• 业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容。

• 问题六：分库分表技术选型问题

• 市场分库分表中间件相对较多，框架各有各的优势与短板，应如何选择。

2.常见分库分表介绍

2.1.垂直分表

• 问题：商品表字段太多，每个字段访问频次不一样，浪费了IO资源，需要进行优化。

1、垂直分表介绍

• 也就是“大表拆小表”，基于列字段进行的。
• 拆分原则一般是表中的字段较多，将不常用的或者数据较大，长度较长的拆分到“扩展表 如text类型字段。
• 访问频次低、字段大的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中。
• 2、垂直拆分原则
• 把不常用的字段单独放在一张表。
• 把text，blob等大字段拆分出来放在附表中。
• 业务经常组合查询的列放在一张表中。
• 

2.2.垂直分库

• 问题：C端项目里面，单个数据库的CPU、内存长期处于90%+的利用率，数据库连接经常不够，需要进行优化。

1、垂直分库介绍

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

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

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

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

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

2.3.水平分表

• 问题：当一张表的数据达到几千万时，查询一次所花的时间长，需要进行优化，缩短查询时间。

1、水平分表简介

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

2.4.水平分库

• 问题：高并发的项目中，水平分表后依旧在单个库上面，1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢，需要进行优化。

1、水平分库简介

• 把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上。
• 水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构。
• 每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据。
• 水平分库的粒度，比水平分表更大。
• 
  

2.5.数据库分库分表总结

1、垂直角度（表结构不一样）

• 垂直分表: 将一个表字段拆分多个表，每个表存储部分字段。

• 好处: 避免IO时锁表的次数，分离热点字段和非热点字段，

• 避免大字段IO导致性能下降。
• 原则：业务经常组合查询的字段一个表；不常用字段一个表；text、blob类型字段作为附属表。

• 垂直分库：根据业务将表分类，放到不同的数据库服务器上

• 好处：避免表之间竞争同个物理机的资源，比如CPU/内存/硬盘/网络IO
• 原则：根据业务相关性进行划分，领域模型，微服务划分一般就是垂直分库

2、水平角度（表结构一样）

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

• 好处: 多个数据库，降低了系统的IO和CPU压力
• 原则

• 选择合适的分片键和分片策略，和业务场景配合

• 避免数据热点和访问不均衡、避免二次扩容难度大

• 水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构

• 单个表的数据量少了，业务SQL执行效率高，降低了系统的IO和CPU压力

• 原则

• 选择合适的分片键和分片策略，和业务场景配合
• 避免数据热点和访问不均衡、避免二次扩容难度大

3.水平分库分表常见策略

3.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瓶颈，整体资源利用率低
• 数据倾斜严重，热点数据过于集中，部分节点有瓶颈
• 
  

3.2.Range策略延伸

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

• 数字

• 自增id范围

• 时间

• 年、月、日范围
• 比如按照月份生成 库或表 pay_log_2022_01、pay_log_2022_02

• 空间

• 地理位置：省份、区域（华东、华北、华南）
• 比如按照 省份 生成 库或表

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

• 微博发送记录、微信消息记录、日志记录，id增长/时间分区都行

• 水平分表为主，水平分库则容易造成资源的浪费

• 网站签到等活动流水数据时间分区最好

• 水平分表为主，水平分库则容易造成资源的浪费

• 大区划分（一二线城市和五六线城市活跃度不一样，如果能避免热点问题，即可选择）

• saas业务水平分库（华东、华南、华北等）

3.3.Hash取模策略

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

案例规则

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

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

• 优点

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

• 缺点

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

4.分库分表常见中间件介绍

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

• Cobar（已经被淘汰没使用了）
  
• TDDL
  
• 淘宝根据自己的业务特点开发了TDDL（Taobao Distributed Data Layer）。
• 基于JDBC规范，没有server，以client-jar的形式存在，引入项目即可使用。
• 开源功能比较少，阿里内部使用为主。

Mycat

• 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转发到具体的真实服务器
• 缺点是效率偏低，中间包装了一层
• 代码无侵入性

4.2.ShardingSphere简介

1、什么是ShardingSphere

• 已于2020年4月16日成为Apache软件基金会的顶级项目。
• 是一套开源的分布式数据库解决方案组成的生态圈，定位为Database Plus。
• 它由JDBC、Proxy、和Sidecar这三款既能独立部署，又支持混合部署配合使用的产品组成。

2、三大构成

• ShardingSphere-Sidecar

• 定位为Kubernetes的云原生数据库代理，以Sidecar的形式代理所有对数据库的访问。
• 通过无中心、零侵入的方案提供与数据库的交互，即Database Mesh，又可称为数据库网格。

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

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

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

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

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

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

ShardingSphere-Proxy

• 数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。
• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL
• 它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client, MySQL Workbench, Navicat 等）操作数据
• 
  

4.4.Sharding-Jdbc常见分片算法

• 数据库表分片（水平库、表）

• 包含分片键和分片策略

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

• 不分片的策略。