springboot应用分库分表

梦里梦外; 2023-01-24 05:20 164阅读 0赞

业界公认MySQL单表容量在1千万或者或100G以下是最佳状态，一般mysql单机差不多1000QPS，因为这时它的BTREE索引树高在3~5之间。并且不到万不得已不用轻易使用分库分表，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。分库分表的中间件只是完成sql执行结果和未分库分表前一致，具体的分库分表操作只有自己操作。

对于技术选型来说，因为部门有组是使用Mycat做的，那就在这里用Mycat和ShardingSphere做一下比较：

*  Mycat：8.7K的star，是一个中间件，开发过程中需要单独部署。 mycat是支持SQL92标准，遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。作为对比可以参考上表中的Sharding-Proxy，需要单独部署，由于遵守Mysql原生协议，应用时不需要特殊处理，和使用MySQL是一样的，所以应用场景不受限制；但是mycat不支持二维路由，仅支持单库多表或多库单表，同时由于自定义连接池，这样就会存在mycat自身维护一个连接池，MySQL也有一个连接池，任何一个连接池上限都会成为性能的瓶颈，而mycat的连接池设计也略显粗暴，当请求链接数大于设置连接池上限时直接抛出异常，因此在配置mycat连接池的大小是，需要结合场景做合理设置。总的来说，mycat以逻辑表的形式屏蔽掉应用处理分库分表的复杂逻辑，遵守Mysql原生协议，跨语言，跨平台，有着更为通用的应用场景。
 *  sharding-jdbc：13.2K的star，ShardingJdbc是ShardingSphere中关于jdbc增强方式的一种，而且ShardingSphere已经孵化为apache顶级项目，java的每一个服务都持有一个Sharing-JDBC，这个JDBC以Jar包的形式提供，基本上可以认为是一个增强版的jdbc驱动，需要一些分库分表的配置，业务开发人员不需要去对代码进行任何的修改。可以很轻松的移植到SpringBoot，ORM等框架上。它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。  
    1、适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。  
    2、支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。  
    3、支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th><em>Sharding-JDBC</em></th> 
   <th><em>mycat</em></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>性能</td> 
   <td>高</td> 
   <td>中</td> 
  </tr> 
  <tr> 
   <td>应用场景限制</td> 
   <td>java应用</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>是否支持自定义sharding路由</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>最大支持sharding路由维度</td> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>分布式事务</td> 
   <td>支持</td> 
   <td>支持弱xa、支持XA分布式事务（1.6.5）</td> 
  </tr> 
  <tr> 
   <td>限制</td> 
   <td>不支持子语句,不支持UNION 和 UNION ALL,不支持批量插入,不支持DISTINCT聚合</td> 
   <td>详见《MYCAT权威指南》</td> 
  </tr> 
  <tr> 
   <td>是否开源</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
 </tbody> 
</table>

本文将使用ShardingSphere作为中间件，ShardingSphere包含三个重要的产品，ShardingJDBC、ShardingProxy和ShardingSidecar。其中sidecar是针对service mesh定位的一个分库分表插件，目前在规划中。ShardingJDBC是用来做客户端分库分表的产品，而ShardingProxy是用来做服务端分库分表的产品。  
原因是它可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零，且：

*  可适用于任何基于Java的ORM框架，如JPA、Hibernate、Mybatis、Spring JDBC Template或直接使用JDBC。
 *  可基于任何第三方的数据库连接池，如DBCP、C3P0、 BoneCP、Druid等。
 *  理论上可支持任意实现JDBC规范的数据库。虽然目前仅支持MySQL，但已有支持Oracle、SQLServer等数据库的计划。

Sharding-JDBC定位为轻量Java框架，使用客户端直连数据库，以jar包形式提供服务，无proxy代理层，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式，harding-JDBC分片策略灵活，可支持等号、between、in等多维度分片，也可支持多分片键。SQL解析功能完善，支持聚合、分组、排序、limit、or等查询，并支持Binding Table以及笛卡尔积表查询，shardingjdbc的核心功能是数据分片和读写分离，通过ShardingJDBC，应用可以透明的使用JDBC访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

## 原理 ##

1.架构：

![14430142-2ff7e6f973b09824.png][]

image.png

每个JAVA应用包含Sharding-JDBC依赖，通过Sharding-JDBC操作数据库

2.Sharding-JDBC接收到sql语句后的操作：

![14430142-0e8f541014de6bf4.png][]

image.png

sql语句经过词法解析拆成不可再分的原子符号（Token），再通过语法解析器被转为抽象语法树：  
例如sql `select id, name from t_user where status = 'ACTIVE' and age > 18`会被分解为：

![14430142-33144b189850d4b1.png][]

image.png

其中SQL路由的意思是经过解析后，确定在哪些数据库执行语句  
例如接收一条sql查询语句 `select * from table where id in (1,2)`会被路由为 `select * from table1 where id in (1,2)`和 `select * from table2 where id in (1,2)`（只是举例，具体路由策略可配置）

最后将两个执行语句结果进行归并，归并方式有三种，分别是流式归并，内存归并和装饰着归并：

![14430142-ad8cb5d369e62d2e.png][]

image.png

其中流式归并用得最多，方法原理如下：  
例如需要将搜索结果进行从大到小排序，每个结果集一开始都会有个游标指向最大值  
结果一：100，95，90  
结果二：99，92，91

![14430142-350fd606002c0a5e.png][]

image.png

## 实践 ##

说明一下，sharding-jdbc只是让查询的sql从多库多表中插入、更新、查询、删除等操作的结果和不分库分表一样，所涉及到的表和库都需要自己先手动创建好

1.依赖引入

<dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>3.1.0</version>
    </dependency>

2.应用配置

*  设置数据源
    
      
    
    
    ![14430142-a961ac0377c67db9.png][]
    
    image.png
    
      
    
    
    （m0,m1,m2类似）
 *  主从库配置
    
      
    
    
    ![14430142-83a379c82b9de65f.png][]
    
    image.png
 *  数据分布设置
    
      
    
    
    ![14430142-37cc1082e3479194.png][]
    
    image.png
 *  主键生成
    
      
    
    
    ![14430142-49efbd974816a96b.png][]
    
    image.png
 *  分片策略
    
      
    
    
    ![14430142-745617c861c2e792.png][]
    
    image.png
 *  分库策略
    
      
    
    
    ![14430142-8da62346fa00a52e.png][]
    
    image.png

像这样一句sql，会分为两条执行：

![14430142-ed305afa6c9081b7.png][]

image.png

--------------------

[https://zhuanlan.zhihu.com/p/146895071?utm\_source=wechat\_session][https_zhuanlan.zhihu.com_p_146895071_utm_source_wechat_session]  
[https://blog.csdn.net/roykingw/article/details/112538200][https_blog.csdn.net_roykingw_article_details_112538200]

[14430142-2ff7e6f973b09824.png]: /images/20221119/8e57b4ccbf0447abba205c58d1c750db.png
[14430142-0e8f541014de6bf4.png]: /images/20221119/8f1abbd742b24cefb20ac7670bf00b98.png
[14430142-33144b189850d4b1.png]: /images/20221119/0d699afa38e346fa948169669317eeeb.png
[14430142-ad8cb5d369e62d2e.png]: /images/20221119/033e64e38eed47be9f40d0301ff41153.png
[14430142-350fd606002c0a5e.png]: /images/20221119/f9ec4ce621f5473795fb82d7b800d7aa.png
[14430142-a961ac0377c67db9.png]: /images/20221119/cd23d796c7ce44dc82cdb5c0ed764646.png
[14430142-83a379c82b9de65f.png]: /images/20221119/e52049708d6740c7830b51181493eeb3.png
[14430142-37cc1082e3479194.png]: /images/20221119/585ab1558caf4716938fc9341ec815aa.png
[14430142-49efbd974816a96b.png]: /images/20221119/e0e7d24f39c94a6aa0ade8936330dc86.png
[14430142-745617c861c2e792.png]: /images/20221119/16d43662497c4ccd91fa663824da4e51.png
[14430142-8da62346fa00a52e.png]: /images/20221119/077170ce95b845c4a68effc1388f891b.png
[14430142-ed305afa6c9081b7.png]: /images/20221119/d3428bf35a064deb99a01663d824e3ef.png
[https_zhuanlan.zhihu.com_p_146895071_utm_source_wechat_session]: https://links.jianshu.com/go?to=https%3A%2F%2Fzhuanlan.zhihu.com%2Fp%2F146895071%3Futm_source%3Dwechat_session
[https_blog.csdn.net_roykingw_article_details_112538200]: https://links.jianshu.com/go?to=https%3A%2F%2Fblog.csdn.net%2Froykingw%2Farticle%2Fdetails%2F112538200