数据库分库分表

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

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

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

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

#	分库分表前	分库分表后
并发支撑情况	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 分库分表带来的问题

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

3.4 分库分表算法

1. 哈希取模：例如通过id和指定的数取模，数据分散均衡，但是扩容麻烦；
2. 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