数据库分库分表

最新推荐文章于 2022-12-11 17:47:08 发布

www-zhangyu

最新推荐文章于 2022-12-11 17:47:08 发布

阅读量447

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/u013739073/article/details/103643967

版权

随着业务增长，单库单表面临性能瓶颈。本文详细介绍了分库分表的必要性、概念、垂直与水平分表策略，以及分库分表带来的挑战。重点探讨了ShardingSphere的分库分表实现，包括中间件原理、配置代码示例，展示了如何通过Sharding-JDBC进行SQL改写和执行。

摘要由CSDN通过智能技术生成

一、为什么需要分库分表？

随着业务越来越大，表的数量越来越多，单表数据量越来越大，受单台服务器资源（CPU、内存、磁盘IO）的限制导致响应时间越来越长，按正常来说，一个查询语句超过1秒就属于慢SQL，为了提高数据库的效率、缩短响应时间，有以下几个优化方案：

sql + nosql，将读多写少的数据放入nosql中，比如redis，将数据放入内存中，减少磁盘IO；
优化sql语句，加索引；
读写分离，如果写请求越来越多，如果是一主多从的架构对主数据库的压力较大；
分库分表；
分区，主要用于数据归档

分库分表前后使用情况的比较：

#	分库分表前	分库分表后
并发支撑情况	mysql单机部署，扛不住高并发；	mysql变为多机部署，能承受的并发增加了多倍
磁盘使用情况	mysql单机磁盘容量占用较大	拆分为多个库，数据库服务器磁盘使用率大大降低
SQL执行性能	单表数量大，SQL越跑越慢	单表数据量减少，SQL执行效率提升

二、什么是分库分表？

将一个数据库分为多个数据库，将一个表拆分为多个表；

三、分库分表的方式

3.1 垂直

大表拆小表，大库拆小库，基于数据库中的列进行的。以分库为例，如电商平台，将整个网站中的订单、商品、用户放在不同的数据库中。

优点：

拆分后业务清晰，专库专用；
数据维护简单

缺点：

如果其中一个数据库中某个表数据量大，读写压力大，如双十一订单会大量增加，商品和用户数据库中数据变化小；
部分业务的信息无法通过表的join连接获得，只能在java中通过接口调用。

特点：

每个库（表）的结构都不一样；
每个库（表）中都至少有一列数据和其他库（表）中字段相同，如订单库中的订单表中会有商品表中id；
所有库（表）的并集是全量数据。

3.2 水平

以某个字段按照一定的规律（如取模）将一个表中的数据分到多个表中。

如水平拆分用户表，当前有三个数据库mysql1、mysql2、mysql3，将用户的id和一个指定的数3取模，如果结果为1将数据放入mysql1中，结果为2放入mysql2中。

优点：

单库（表）的数据保持在一定量，相比集中在一个数据库(表)中减少，提升数据库的性能；
切分的表的结构相同，程序改造较少；

缺点：

数据的扩容较难，如果三个数据库都到达了极限，再增加一个数据库，需要将之前数据库的数据按照新的规则重新存放；
拆分的规则很难抽象出来；
部分业务的信息无法通过表的join连接获得，只能在java中通过接口调用；

特点

每个库（表）的结构都是一样的；
每个库（表）的数据都不一样；
所有库（表）的并集是全量数据。

结合两种拆分方式的特点，可以将两种方式结合起来，垂直分库水平分表，按照业务将数据垂直拆分到不同的数据库中，再将每个数据库中数量较大的表按照一定的规则拆分到多个表中。
在这里插入图片描述

3.3 分库分表带来的问题

维护成本增加
需要使用分布式事务
跨库的的数据表不能用join

3.4 分库分表算法

哈希取模：例如通过id和指定的数取模，数据分散均衡，但是扩容麻烦；
RANGE：范围划分，例如将6个月之前的数据切出去放在另外的一张表中，按省份进行划分等，但是有可能会使热点数据划分到一个表中；

四、分库分表中间件

4.1 中间件的产品

分为两类：基于Proxy的和基于JDBC的。

Proxy：myCat，atlas（360开发，基于mysql-proxy二次开发），

JDBC：

shardingSphere，Apache的孵化项目，它的前身是sharding-jdbc，由sharding-jdbc、sharding-proxy、sharding-sidecar三个产品组成，所以它既有基于proxy的又有基于JDBC的中间件；
TDDL，淘宝，部分开源，核心内容收费

4.2 中间件原理

不加中间件时的请求：java项目直接请求mysql数据库

Proxy：java项目请求mysql数据库之前，先经过Proxy中间件，判断要访问哪个数据库。
在这里插入图片描述
JDBC：java项目中引入sharding-jdbc.jar，在项目内部就决定了要访问的数据库。

优缺点：

#	优点	缺点
Proxy	程序员不需要关注底层，不会增加开发难度；增删节点不需要重启项目；跨语言；	性能下降，因为在应用和数据库之间多了一层传输；不支持跨数据库，因为每个数据库的连接协议不一样；
Jdbc	不影响性能；支持跨数据库，因为底层是JDBC，可以连接多个数据库；	需要在代码中引入，增加开发难度；现在的版本仍需要重启，目前正在开发的4.X版本会解决；不支持跨语言，jdbc只能用在java中；

4.3 sharding-jdbc

官网：http://shardingsphere.apache.org/，

4.3.1 分库分表代码及解释

public class JavaSytleTest {
   
   public static void main(String[] args) throws SQLException {
   
      // 配置真实数据源
      //size 2 为两个数据库的datasource
      Map<String, DataSource> dataSourceMap = new HashMap<>();
      // 配置第一个数据源
      BasicDataSource dataSource1 = new BasicDataSource();
      dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource1.setUrl("jdbc:mysql://localhost:3306/orders_0");
      dataSource1.setUsername("root");
      dataSource1.setPassword("password");
      dataSourceMap.put("orders_0", dataSource1);
      // 配置第二个数据源
      BasicDataSource dataSource2 = new BasicDataSource();
      dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource2.setUrl("jdbc:mysql://localhost:3306/orders_1");
      dataSource2.setUsername("root");
      dataSource2.setPassword("password");
      dataSourceMap.put("orders_1", dataSource2);

      ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
      shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
      shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
      shardingRuleConfig.getBindingTableGroups().add("t_order,t_order_item");
      //采用user_id进行分库
      shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
              new InlineShardingStrategyConfiguration("user_id",
                      "orders_${user_id % 2}"));//分别得出orders_0 orders1
      //采用order_id进行分表
      shardingRuleConfig.setDefaultTableShardingStrategyConfig(
              new StandardShardingStrategyConfiguration("order_id",
                      new PreciseShardingAlgorithm<Long>() {
   

         @Override
         public String doSharding(Collection<String> collection, final PreciseShardingValue<Long> preciseShardingValue) {
   
            for (String each : collection) {
   
               System.out.println(each+"--------"+preciseShardingValue.getValue()+"---------"+preciseShardingValue.getValue() % 2);
               if (each.endsWith(preciseShardingValue.getValue()