mysql大数据解决方案--分区分表分库

亦凉 2022-02-03 13:17 180阅读 0赞

> 这篇文章主要介绍了MyBatis实现Mysql数据库分库分表操作和总结,需要的朋友可以参考下

### **前言** ###

作为一个数据库，作为数据库中的一张表，随着用户的增多随着时间的推移，总有一天，数据量会大到一个难以处理的地步。这时仅仅一张表的数据就已经超过了千万，无论是查询还是修改，对于它的操作都会很耗时，这时就需要进行数据库切分的操作了。对于一个大型的互联网应用，海量数据的存储和访问成为了系统设计的瓶颈问题，对于系统的稳定性和扩展性造成了极大的问题。通过数据切分来提高网站性能，横向扩展数据层已经成为架构研发人员首选的方式。

> •水平切分数据库：可以降低单台机器的负载，同时最大限度的降低了宕机造成的损失；
> 
> •负载均衡策略：可以降低单台机器的访问负载，降低宕机的可能性；
> 
> •集群方案：解决了数据库宕机带来的单点数据库不能访问的问题；
> 
> •读写分离策略：最大限度了提高了应用中读取数据的速度和并发量；

### 问题描述 ###

> 1、单个表数据量越大，读写锁，插入操作重新建立索引效率越低。
> 
> 2、单个库数据量太大（一个数据库数据量到1T-2T就是极限）
> 
> 3、单个数据库服务器压力过大
> 
> 4、读写速度遇到瓶颈（并发量几百）

解决问题的思路：根据自己的实际情况，当单表过大的时候进行分表，数据库过大的时候进行分库，高并发的情况考虑读写分离和集群。

数据拆分的方式有：***分区、分表、分库***

•分区 ：就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

•分表 ：就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

•分库：就是分成多个数据库实例，每个数据库实例中表名相同

> 将整个数据库比作图书馆，一张表就是一本书。当要在一本书中查找某项内容时，如果不分章节，查找的效率将会下降。而同理，在数据库中就是分区。

<table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td>什么时候考虑使用分区？</td> 
 <td>分区解决的问题</td> 
 </tr> 
 <tr> 
 <td>分区</td> 
 <td> 1、一张表的查询速度已经慢到影响使用的时候。 2、sql经过优化 3、数据量大 4、表中的数据是分段的 5、对数据的操作往往只涉及一部分数据，而不是所有的数据 </td> 
 <td>主要可以提升查询效率</td> 
 </tr> 
 <tr> 
 <td>分表</td> 
 <td> 1、一张表的查询速度已经慢到影响使用的时候。 2、sql经过优化 3、数据量大 4、当频繁插入或者联合查询时，速度变慢 </td> 
 <td> 1、分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了 2、查询一次的时间短了 3、数据分布在不同的文件，磁盘I/O性能提高 4、读写锁影响的数据量变小 5、插入数据库需要重新建立索引的数据减少 </td> 
 </tr> 
 </tbody> 
</table>

**分区和分表的区别与联系**

•分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。

•分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。

•当访问量大，且表数据比较大时，两种方式可以互相配合使用。

•当访问量不大，但表数据比较多时，可以只进行分区。

**常见分区分表的规则策略（类似）**

1、Range（范围）（用户1-10000操作第一张表，用户10001-20000操作第二张表）

2、Hash（哈希）

3、按照时间拆分 （根据不同的日期分表，如一个月一张表，这个月就操作这张表，下个月就下张表）

4、Hash之后按照分表个数取模 (“%”取模也是最简单的一种) 下面会介绍

5、在认证库中保存数据库配置，就是建立一个DB，这个DB单独保存user\_id到DB的映射关系

**例如：这里实现不借助任何插件的形式，用mybatis实现分表，取模的形式**

1.  我们模拟用户表数据量超过千万（虽然实际不太可能）
2.  用户表原来的名字叫做`user_tab`，我们切分为`user_tab_0`和`user_tab_1`（实际也可能不是这么随意的名字），这样就能把原来千万的数据分离成两个百万的数据量的两张表了。
3.  如何操作这两张表呢？我们利用userId也就是用户的唯一标识进行区分。
4.  `userId%2 == 0`的用户操作表`user_tab_0`，同理`userId%2 == 1`的用户操作表`user_tab_1`
5.  那么在MyBatis中sql语句如何实现呢？下面是举例查询一个用户的sql语句

<select id="getUser" parameterType="java.util.Map" resultType="UserDO"> 
 SELECT userId, name FROM user_tab_#{tabIndex} WHERE userId = #{userId} 
 </select>

其中我们传入了两个参数tabIndex和userId，tabIndex就是需要操作表的标示值（0或1），这样如果需要查询userId为5的用户，那么最终出现的sql语句就会是：

SELECT userId, name FROM user_tab_1 WHERE userId = 5

其他多余的DAO服务和实现这里就不多展示了以上就是最简单的实现

**分库：**

<table style="width:700px;"> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>什么时候考虑使用分库？</td> 
 <td> 1、单台DB的存储空间不够 2、随着查询量的增加单台数据库服务器已经没办法支撑 </td> 
 </tr> 
 <tr> 
 <td> 分库解决的问题 </td> 
 <td> 1、其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。&nbsp; </td> 
 </tr> 
 <tr> 
 <td> 垂直拆分 </td> 
 <td> 1、将系统中不存在关联关系或者需要join的表可以放在不同的数据库不同的服务器中。 2、按照业务垂直划分。比如：可以按照业务分为资金、会员、订单三个数据库。 3、需要解决的问题：跨数据库的事务、jion查询等问题。 </td> 
 </tr> 
 <tr> 
 <td> 水平拆分 </td> 
 <td> 1、例如，大部分的站点。数据都是和用户有关，那么可以根据用户，将数据按照用户水平拆分。 2、按照规则划分，一般水平分库是在垂直分库之后的。比如每天处理的订单数量是海量的，可以按照一定的规则水平划分。需要解决的问题：数据路由、组装。 </td> 
 </tr> 
 <tr> 
 <td> 读写分离 </td> 
 <td>1、对于时效性不高的数据，可以通过读写分离缓解数据库压力。需要解决的问题：在业务上区分哪些业务上是允许一定时间延迟的，以及数据同步问题。</td> 
 </tr> 
 </tbody> 
</table>

> 思路：
> 
> 垂直分库-->水平分库-->读写分离

**补充说明：**

> **1、水平切分**
> 
> 简单的说就是，把一张表分离成几张一模一样的表，然后表的名字不同。就和上面最简单的例子一样。
> 
> 这种切分适合于一张表的数据量过大而导致操作时间变慢的情况，如保存的一些记录表。
> 
> **2、垂直切分**
> 
> 把不同的业务模块分成不同的数据库，这些业务模块直接最好是0耦合（简单的说就是毫无关系）。
> 
> 这主要是适合数据量普遍较大，而且业务场景比较分散，互相之间没有逻辑关系的情况。

### 数据拆分以后面临的问题 ###

> 1、添加时主键唯一性的问题；分离之后多张表，就会导致原有的自增长主键不唯一，所以没有办法自增长了，导致问题，解决方案的也是有的，比如单独维护一张主键表专门用来存放当前主键，或者说用别的中间件等。
> 
> 2、新增时的效率问题，虽然不是个大问题，但是新增肯定会多了计算量，这个问题可以忽略不计。
> 
> 3、查询所带来的分页问题，分离成多张表之后，分页查询就很困难了，这也考虑到不同的分离用不同的解决方案，总之会产生问题。
> 
> 4、join时跨库，跨表的问题，关联查询，原本一张表关联别的表或者别的表关联一张表，都很简单，但是现在分离之后就难了。
> 
> 5、事务问题，多张表需要使用分布式事务才能完成原来带有事务的操作。因为原来的事务只是锁一张表现在可能要锁多张了呢。
> 
> 6、分库分表，就变成了分布式事务，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，导致性能降低，系统的复杂度变高。
> 
> 7、扩展性问题，有的切分策略下，对数据的扩展性其实不好，之后如果有更多的数据来了，是说还能再新建表来扩展吗？

### **数据存储的演进** ###

**单库单表**

•单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库中的user表中查到。

**单库多表**

•随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。

•可以通过某种方式将user进行水平的切分，产生两个表结构完全一样的user\_0000,user\_0001等表，user\_0000 + user\_0001 + …的数据刚好是一份完整的数据。

**多库多表**

随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平拆分。

总结：

> 总的来说，优先考虑分区。当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。
> 
> 垂直分库-->水平分库-->读写分离

**分离的原则**

下面总结了几点分离的原则，主要是参考了网络上的，没有任何实际的依据（我也不是个年薪百万的DBA也碰不到那么大的数据去实际检验嘛），所以如果有任何问题也请指出。

> 1、能不分就不分
> 
> 2、能分少就不分多
> 
> 3、多冗余，不关联
> 
> 4、避免使用分布式事务，主要是太难我也不会啊
> 
> 5、单表千万记录以内就不分
> 
> 6、现在不分以后分也来得及
> 
> 7、扩展，耦合，仔细考虑

**实现分离的方式**

最后说说分离的方式，现在流行使用的DAO框架是MyBatis，也有很多别的框架。分离的实现主要有下面几种方式。

<table> 
 <tbody> 
 <tr> 
 <td style="width:83px;">方式</td> 
 <td style="width:267px;">说明</td> 
 <td style="width:101px;">优点</td> 
 <td style="width:397px;">缺点</td> 
 </tr> 
 <tr> 
 <td style="width:83px;">原生实现</td> 
 <td style="width:267px;">就和最上面的例子一样，不需要其他任何的东西，利用原生的框架，自己去控制实现</td> 
 <td style="width:101px;"> 容易控制， 掌握主动权 </td> 
 <td style="width:397px;">代码量多，需要自己很清楚，修改不方便，不支持复杂的切分，比如切分之后还需要做一些分页查询，还有上面说的主键问题等。</td> 
 </tr> 
 <tr> 
 <td style="width:83px;">插件实现</td> 
 <td style="width:267px;">利用框架本身开发的一些插件，去实现这些插件，然后利用插件去访问数据库，直接实现分离</td> 
 <td style="width:101px;">代码量少，实现简单，扩展性好</td> 
 <td style="width:397px;">不易控制，分离方式有限，出现问题难以解决。没有找到特别成熟的插件</td> 
 </tr> 
 <tr> 
 <td style="width:83px;">中间件实现</td> 
 <td style="width:267px;">选用第三方的数据库中间件（Atlas，Mycat，TDDL，DRDS），同时业务系统需要配合数据存储的升级，在访问数据库之前做一些操作使得sql进行相应的变化从而实现分离</td> 
 <td style="width:101px;">耦合小，扩展性好，可以解决分布式事务的问题</td> 
 <td style="width:397px;">实现比较复杂，需要对中间件进行学习，成本较大。维护也是一个大问题，万一挂掉了。。</td> 
 </tr> 
 </tbody> 
</table>

### **实操** ###

**1、单库多表**

单库多表是对数据的水平拆分，多张表的表结构完全相同，数据按照不同的规则进行拆分，存储到对于的数据表中。

![20190509165836980.png][]

这是我按照数据的年份进行拆分的数据表，数据存储的时候根据数据的年份存到对于的表中，查询业务也都是按照年份进行，一般没有跨年份的数据查询，这样就避免了多表查询后数据的合并。

**2、多库单表**

完全相同的数据库，安装不同规则存储各自的数据，下面是spring boot多数据源配置：

> \#更多数据源
> 
> custom.datasource.names=jiangsu,anhui,shandong,hubei,hunan,fujian
> 
> custom.datasource.jiangsu.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.jiangsu.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.jiangsu.url=jdbc:mysql://127.0.0.1:3306/nda\_jiangsu?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.jiangsu.username=root
> 
> custom.datasource.jiangsu.password=
> 
>  
> 
> custom.datasource.anhui.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.anhui.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.anhui.url=jdbc:mysql://127.0.0.1:3306/nda\_anhui?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.anhui.username=root
> 
> custom.datasource.anhui.password=
> 
>  
> 
> custom.datasource.shandong.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.shandong.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.shandong.url=jdbc:mysql://127.0.0.1:3306/nda\_shandong?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.shandong.username=root
> 
> custom.datasource.shandong.password=
> 
>  
> 
> custom.datasource.hubei.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.hubei.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.hubei.url=jdbc:mysql://127.0.0.1:3306/nda\_hubei?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.hubei.username=root
> 
> custom.datasource.hubei.password=
> 
>  
> 
> custom.datasource.hunan.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.hunan.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.hunan.url=jdbc:mysql://127.0.0.1:3306/nda\_hunan?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.hunan.username=root
> 
> custom.datasource.hunan.password=
> 
>  
> 
> custom.datasource.fujian.type=com.zaxxer.hikari.HikariDataSource
> 
> custom.datasource.fujian.driverClassName=com.mysql.jdbc.Driver
> 
> custom.datasource.fujian.url=jdbc:mysql://127.0.0.1:3306/nda\_fujian?useUnicode=yes&characterEncoding=UTF-8
> 
> custom.datasource.fujian.username=root
> 
> custom.datasource.fujian.password=

这是按照省进行数据拆分，保证各个省的数据完整性,在相关业务操作的时候，根据用户所在的省份查询对应的数据库：

> DynamicDataSourceContextHolder.setDataSourceType(provincename);

**3、多库多表**

在介绍多库多表的时候，给大家介绍一个轻量级分库分表工具，sharding-jdbc，这是当当网自己实现的基本JDBC的数据库多库多表解决方案。可以让你在写业务代码的时候完全按照单库单表进行，多库多表的问题有sharding-jdbc帮你解决，需要自己实现分库分表规则接口，配置分库分表规则。

pom.xml配置

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-devtools</artifactId>
 <optional>true</optional>
 </dependency>

实现分库规则接口

public class DemoDatabaseShardingAlgorithm implements PreciseShardingAlgorithm {
        @Override
        public String doSharding(Collectioncollection, PreciseShardingValue preciseShardingValue) {
            for (String each : collection) {
                System.out.println(each + "==" + preciseShardingValue.getValue());
                if (each.endsWith(Long.parseLong(preciseShardingValue.getValue().toString()) % 2 + "")) {
                    return each;
                }
            }
            throw new IllegalArgumentException();
        }
    }

实现分表规则接口

public class DemoTableShardingAlgorithm implements PreciseShardingAlgorithm {
        @Override
        public String doSharding(Collectioncollection, PreciseShardingValue preciseShardingValue) {
            for (String each : collection) {
                System.out.println(each + "=2=" + preciseShardingValue.getValue());
                if (each.endsWith(Long.parseLong(preciseShardingValue.getValue().toString()) % 2 + "")) {
                    return each;
                }
            }
            throw new IllegalArgumentException();
        }
    }

调用规则

@Bean(name = "shardingDataSource")
    
       DataSource getShardingDataSource()throws SQLException{
    
            ShardingRuleConfiguration shardingRuleConfig;
    
            shardingRuleConfig=new ShardingRuleConfiguration();
    
            shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());
    
            shardingRuleConfig.getBindingTableGroups().add("user_info");
    
            shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",DemoDatabaseShardingAlgorithm.class.getName()));
    
            shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",DemoTableShardingAlgorithm.class.getName()));
    
            return new ShardingDataSource(shardingRuleConfig.build(createDataSourceMap()));
    
            }

这样完成以后，业务代码就可以完全按照单表就行书写，Sharding-JDBC会自动帮你实现分库分表的数据库插入，以及查询时候的多表数据合并。

Sharding-JDBC 采用在 JDBC 协议层扩展分库分表，是一个以 jar 形式提供服务的轻量级组件，其核心思路是小而美地完成最核心的事情。

Sharding-JDBC 还提供了读写分离的能力，用于减轻写库的压力。

此外，Sharding-JDBC 可以用在 JPA 场景中，如 JPA、Hibernate、Mybatis，Spring JDBC Template 等任何 Java 的 ORM 框架。

**不过目前Sharding-JDBC仅支持mysql数据库**

然后还有一个第三方插件mycat也可以实现分库分表的数据插入和查询，不过mycat是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的。

在使用中将mycat查询启动，它自己就成为了一个虚拟数据库，而业务程序是连接的mycat的虚拟数据库的，然后mycat连接实际数据库实现数据的分库分表。

文章参考网上出处，自己先在本地进行总结找不，后来找不到出处，后面如有发现在进行补充

分享几篇文章供大家参考：

[mysql数据库分库分表][mysql]

[mycat教程---数据库的分库分表][mycat_---]

[千万数据分库分表][Link 1]

[20190509165836980.png]: /images/20220203/685b7cd318c341258e3ac749133f5f9d.png
[mysql]: https://blog.csdn.net/u011665991/article/details/90042865
[mycat_---]: https://www.cnblogs.com/mracale/p/8989248.html
[Link 1]: https://blog.csdn.net/u011665991/article/details/90043392