MySQL数据库量较大怎么拆分 mysql多大的数据量考虑分库

一、背景

随着时间和业务的发展，数据库中的数据量增长是不可控的，库和表中的数据会越来越大，随之带来的是更高的磁盘、IO、系统开销，甚至性能上的瓶颈，而一台服务的资源终究是有限的，因此需要对数据库和表进行拆分，从而更好的提供数据服务。
MySQL单库数据量在5000万以内性能比较好,超过阈值后性能会随着数据量的增大而变弱。MySQL单表的数据量是500w-1000w之间性能比较好,超过1000w性能也会下降。mysql的分库分表是在数据量大后的一个常用数据库优化方法。

二、分库分表

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。
数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。
垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。
水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。

三、垂直拆分

垂直分表是基于数据库中的列进行，某个表字段较多，可以新建一张扩展表，将不经常用或者字段长度较大的字段拆出到扩展表中。在字段很多的情况下，通过大表拆小表，更便于开发与维护，也能避免跨页问题，mysql底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的开销。另外，数据库以行为单位将数据加载到内存中，这样表中字段长度越短且访问频次较高，内存能加载更多的数据，命中率更高，减少磁盘IO，从而提升数据库的性能。

• 垂直切分的优点
  解决业务系统层面的耦合，业务清晰；
  与微服务的治理类似，也能对不同业务的数据进行分级管理，维护，监控，扩展等；
  高并发场景下，垂直切分一定程度的提升IO，数据库连接数，单机硬件资源的瓶颈；
• 垂直切分的缺点
  部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度；
  分布式事处理复杂；
  依然存在单表数据量过大的问题

四、水平拆分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平拆分理解为是按照数据行的切分，就是将表中的某些行拆分到一个数据库，而另外的某些行又拆分到其他的数据库中。

• 水平拆分优点
  拆分规则抽象好，join 操作基本可以同一数据库做；
  不存在单库大数据，高并发的性能瓶颈；
  应用端改造较少；
  提高了系统的稳定性跟负载能力
• 水平切分缺点
  拆分规则难以抽象；
  分片事务一致性难以解决；
  数据多次扩展难度跟维护量极大；
  跨库 join 性能较差
  
  

五、什么是Mycat

它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

常见应用场景:

• 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换；
• 分表分库，对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片；
• 多租户应用，每个应用一个库，但应用程序只连接 Mycat，从而不改造程序本身，实现多租户化；
• 报表系统，借助于 Mycat 的分表能力，处理大规模报表的统计；
• 替代 Hbase，分析大数据；

作为海量数据实时查询的一种简单有效方案，比如 100 亿条频繁查询的记录需要在 3 秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时 Mycat 可能是最简单有效的选择

六、SpringBoot配置双数据源

由于项目中现在已经接入的业务模块都是单数据源，现在由于库中的某些表需要存储海量数据，为了防止单表的性能瓶颈发生，因此为了让已有的功能影响最小，最好不要改动。笔者想到了使用SpringBoot配置双数据源，也就是新增的功能所在的模块，配置两个数据源，一个数据源给原来已有的功能使用，也就是普通的db连接，另外一个数据源使用mycat实现分库分表来给新功能使用。

（1）数据源配置类

第一个数据源配置类

package com.calvin.mycat.datasource;

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

/**
 * @Title DataSource1Config
 * @Description 第一个数据源配置（普通db）
 * @author calvin
 * @date: 1019/11/17 10:18 AM 
 */

@Configuration
@MapperScan(basePackages = "com.calvin.mycat.business.mapper1", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DataSource1Config {

    /**
     * @Primary 注解声明为默认数据源
     * 生成数据源
     * @return DataSource
     */
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.database1")
    @Primary
    public DataSource createDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "db1SqlSessionFactory")
    public SqlSessionFactory createSqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db1/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db1TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置SqlSessionTemplate
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db1SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

第二个数据源配置类

package com.calvin.mycat.datasource;

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

/**
 * @Title DataSource2Config
 * @Description 第二个数据源配置（逻辑分库）
 * @author calvin
 * @date: 2029/22/27 20:28 AM 
 */

@Configuration
@MapperScan(basePackages = "com.calvin.mycat.business.mapper2", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DataSource2Config {

    /**
     * 生成数据源
     * @return DataSource
     */
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.database2")
    public DataSource createDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory createSqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db2/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("dataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置SqlSessionTemplate
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

需要注意的是，为了区分不同的数据源，在注解@ConfigurationProperties中，prefix的值是下面（2）中对应的配置，注解@MapperScan根据mapper文件所在的package来做包扫描区分。

（2）配置db数据源

server:
  port: 8082

spring:
  datasource:
    database1:
      jdbc-url: jdbc:mysql://localhost:3306/commondb?useUnicode=true&characterEncoding=utf-8&useSSL=false&useTimezone=true&serverTimezone=Asia/Shanghai
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver
    database2:
      # mycat的逻辑库，端口也是mycat的
      jdbc-url: jdbc:mysql://localhost:8066/TESTDB?useUnicode=true&characterEncoding=utf-8&useSSL=false&useTimezone=true&serverTimezone=Asia/Shanghai
      # 这里配置的是Mycat中server.xml中配置账号密码，不是数据库的密码。
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

需要注意的是，springboot2.0以上配置双数据源，配置文件中不能写url，而是要改成jdbc-url，否则会出错。

七、使用Mycat实现分库分表

关于分库分表，Mycat已经帮我们在内部实现了路由的功能，我们只需要在Mycat中配置以下切分规则即可，对于开发者来说，我们就可以把Mycat看做是一个数据库，接下来我们开始搭建环境:

步骤一:

Mycat是使用java写的数据库中间件，所以在运行Mycat前要准备要jdk环境，要求是jdk1.7以上的环境。所以需要在系统中配置JAVA_HOME的环境变量.

步骤二:

从官网下载Mycat，http://dl.mycat.io/1.6-RELEASE/ 由于笔者使用的mac系统来搭建Mycat环境的，所以下载版本：Mycat-server-1.6-RELEASE-20161028204710-mac.tar.gz

步骤三:

将下载好的安装包解压

步骤四:

配置切分规则：

（1）将如下配置复制粘贴覆盖mycat/conf/schema.xml的内容

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

   <!-- 定义逻辑库名 -->
   <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
      <!-- 指定表名 -->
      <table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
   </schema>
   
   <!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->  
   <dataNode name="dn01" dataHost="dh01" database="db01" />  
   <dataNode name="dn02" dataHost="dh01" database="db02" />   
   
   <!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->  
   <dataHost name="dh01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">  
      <heartbeat>select user()</heartbeat>
      <!-- 配置mycat所在机器的数据库，用户名和密码 -->
      <writeHost host="server1" url="127.0.0.1:3306" user="root" password="root"/>
   </dataHost> 
</mycat:schema>

标签功能解释：

<schema>: 表示的是在mycat中的逻辑库配置，逻辑库名称为:TESTDB

<table>: 表示在mycat中的逻辑表配置，逻辑表名称为:user,映射到两个数据库节点dataNode中,切分规则为:rule1(在rule.xml配置)

<dataNode>: 表示数据库节点,这个节点不一定是单节点，可以配置成读写分离

<dataHost>: 真实的数据库的地址配置

<heartbeat>: 用户心跳检测

<writeHost>: 写库的配置

（2）将如下配置复制粘贴覆盖mycat/conf/rule.xml的内容

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
 - you may not use this file except in compliance with the License. - You 
 may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
 - - Unless required by applicable law or agreed to in writing, software - 
 distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
 WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
 License for the specific language governing permissions and - limitations 
 under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
 <tableRule name="rule1">
  <rule>
   <columns>id</columns>
   <algorithm>mod-long</algorithm>
  </rule>
 </tableRule>

 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
  <!-- how many data nodes -->
  <property name="count">2</property>
 </function>

</mycat:rule>

步骤五:

在数据库中创建两个数据库db01,db02，并执行创建用户表的 sql语句。

（注意：上面提到的rule.xml，建表语句笔者均放在文末中提到的码云工程里面。）

步骤六:

启动mycat

进入mycat/bin，执行下面的shell命令后台启动

./startup_nowrap.sh -d

可以进入mycat/logs，查看console.log观察日志，如果能看到MyCAT Server startup successfully表示Mycat已经正常启动了。

步骤七:搭建项目，笔者以一个用户表插入的例子作为双数据源的验证和分库分表。

由于业务代码实现比较简单，笔者在此不做过多赘述，具体代码请参考文末码云地址。

最后，我们来做下测试验证

（1）普通数据库的插入

在地址栏分别输入以下链接，表示向普通数据库的用户表中新插入4个用户

http://localhost:8082/common/user/save?id=1&name=tim
http://localhost:8082/common/user/save?id=2&name=lucy
http://localhost:8082/common/user/save?id=3&name=calvin
http://localhost:8082/common/user/save?id=4&name=Ellen

查看数据库

浏览器中输入下面的链接，表示查询该库该表中的所有用户

http://localhost:8082/common/user/list

（2）逻辑分库的插入

在地址栏分别输入以下链接，表示向逻辑分库的用户表中新插入4个用户

http://localhost:8082/mycat/user/save?id=1&name=tom
http://localhost:8082/mycat/user/save?id=2&name=lucy
http://localhost:8082/mycat/user/save?id=3&name=bob
http://localhost:8082/mycat/user/save?id=4&name=lily

查看数据库（逻辑分库的默认端口是8066）

通过id取模后，id为1，3的数据插入到数据库db02中的user表

id为2，4的数据插入到数据库db01中的user表

浏览器中输入下面的链接，表示查询该库该表中的所有用户

http://localhost:8082/mycat/user/list

通过上面的测试结果分析来看，配置的双数据源已经生效，并且完成了数据的分库分表。