MySQL十九：分库分表实践_mysql分库分表后如何查询数据

尺有所短，寸有所长；不忘初心，方得始终。

请关注公众号：星河之码

在很多小型应用中都没真正使用分库分表，但是说起来并不陌生，因为我们在面试中经常会被问到，今天我们从从以下几个方面来聊聊分库分表：是什么？解决什么？怎么做？为什么要这么做？即：

• 分库分表是什么？
• 分库分表解决什么问题？
• 分库分表怎么做？
• 分库分表什么时候做？
• 分库分表引发的问题是什么？
• 分库分表中间件有哪些？

一、什么是分库分表

分库分表其实很好理解，顾名思义，即把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。但是需要明确一点，分库分表不是一件事，而是三件事，也就是分库分表的三种方案：

• 只分库不分表
• 只分表不分库
• 既分库又分表

1.1 只分库不分表

从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中，多个数据库同时提供服务。

1.2 只分表不分库

从单张表拆分成多张表的过程，将数据散落在多张表内。

1.3 既分库又分表

把存于一个数据库的单表数据分散到不同库的多个表中。

二、分库分表解决什么问题

大型应用系统需要处理大量用户的请求，比如微信，美团，淘宝等每天都会产生海量的数据，我们知道当数据量或者请求数达到一定数量之后，数据库就会产生性能瓶颈，而为了缓解数据量的压力，比较普遍的方案一方面是使用NoSQL，而另一方面就是分库分表。

2.1 分库解决什么问题

分库就是在我们系统的业务量增长到一定程度之后，解决数据库本身的性能瓶颈问题，所以一般都是业务量增长，遇到以下两种情况时就会考虑分库：

• 数据库QPS过高，连接数不足

  数据库的连接是有上限的，在高并发下，大量请求访问数据库，可能会让数据库宕机，致使整个服务不可用，因此我们可以把单个数据库拆分成多个数据库，分摊请求，缓解单个数据的读写压力，提高并发量。

• 磁盘瓶颈

  数据量是随着业务量的增多而增多的，而单个数据库的磁盘存储量也是有限的，把单个数据库拆分成多个数据库，缓解磁盘压力，降低磁盘使用率。

2.2 分表解决什么问题

在之前的《单表最大2000W行数据》中，分析了单表的数据存储量，当单表存储的数据过多时，查询数据会非常慢，在高并发场景下，一个慢查询可能会导致整个数据库的宕机。所以一般当单个表的数据量太大，可以考虑通过分表来提升查询效率。

一般推荐单表数据量在 上千万级别时就要考虑分表，因为千万级别可能会导致B+Tree高度变高。

不清楚单表数据量存储量大小的，可以参考一下单表最大2000W行数据这篇文章。

因此分表可以解决单个表数据被分散，查询是B+Tree（MySQL）的高度比较低，减少磁盘IO，提升效率。

三、分库分表怎么做

当我们使用分库分表时，都在物理空间的拆分，主要有两种拆分模式，都可以应用到分库或分表中：

• 垂直拆分

  垂直拆分又称为纵向拆分，应用时有垂直分库和垂直分表两种方式，主要解决表过多或者是表字段过多问题，一般谈到的垂直拆分主要指的是垂直分库。

  • 垂直分库：是将不同的表分离到不同的库中。
  • 垂直分表：修改表结构按照访问的差异将不同的列拆分到不同的表中。

• 水平拆分

  水平拆分又称为横向拆分，应用时有水平分库和水平分表两种方式，解决表中记录过多，缓解单机单库的性能瓶颈和压力问题。一般谈到的水平拆分主要指的是水平分库。

  • 水平分库：将数据切分到不同的数据库上，每个数据库都具有相同的表，只是数据行不一样。
  • 水平分表：将一张表水平切分，不同的记录可以分开保存，拆分成几张结构相同的表。

  水平拆分不再像垂直拆分那样将数据根据业务逻辑分类，而是通过一定的策略将数据分散至多个库或表中，每个库或表仅包含数据的一部分行。

3.1 垂直分库

随着业务快速发展，数据库中的数据量猛增，所有的数据限制在一台服务器，数据库物理机本身的CPU、内存、网络IO、磁盘等都会成为性能瓶颈，此时我们可以按照业务的划分，将不同的表放在不同的服务器中，分散流量，减轻单个数据库的压力，提高系统的性能。

垂直分库本质是专库专用，指按照业务将表进行分类，分布到不同的数据库中，每个库可以放在不同的服务器上。

• 垂直分库优点
  • 专库专用，业务层面解耦
  • 能够针对不同业务的数据进行分级管理、维护、监控、扩展
  • 在一定程度上提升了IO、数据库连接数、降低单机硬件资源的瓶颈
• 垂直分库缺点
  • 事务一致性的问题
  • 多表连接查询困难

3.1 垂直分表

在之前的文章《InnoDB的存储结构》中解释了了数据在MySQL的的存储方式，我们知道数据是以数据页的方式存储的，而数据页中的数据是数据行，因此当我们的一行数据过大时，数据页存储的数据行就会减少，也就是说跨数据页查询的概率就会增加，因此垂直分表就是将一个表拆分到多个表，避免出现数据库跨页存储的问题，从而提升查效率。

垂直分表本质是将一个表按照字段分成多表，每个表存储其中一部分字段。

• 垂直分表拆分原则
  • 将热点字段和不常用的字段区分，放在不同的表中
  • 将text，blob等大字段拆分出来放在附表中
  • 将组合查询的列放在一张表中
• 垂直分表优点
  • 减少锁竞争，查询不同字段数据互不影响
  • 可实现冷热分离的数据表设计
  • 可以使得行数据变小，一个数据页能存放更多的数据，最大限度利用数据页缓存，减少查询的 I/O 次 数
• 垂直分表缺点
  • 事务一致性的问题
  • 多表连接查询困难
  • 无法解决单表数据量过大

3.1 水平分库

垂直分库是将不同业务表分别放在了不同数据库中以此减轻单个数据库的性能瓶颈，但是如果某个核心业务的并发非常高，比如订单库，双十一下单的并发非常高，单个的订单库仍然存在单个订单数据库性能瓶颈问题，因此我们可以对数据进行分片，将单个订单库进行拆分成多个库，以此提高数据库总体性能。

水平分库的本质也是分表，是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

• 水平分库优点
  • 解决单个库高并发的性能瓶颈
  • 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可
  • 提高了系统的稳定性和负载能力。
• 水平分库缺点
  • 分片事务的一致性难以解决
  • 数据扩容的难度和维护量极大

3.1 水平分表

当我们的业务量猛增，单表数据达数千万甚至上亿的时候，查询效率会降低，此时我们考虑将数据表按照一定的规则将表中的记录进行分片，存储在不同的表中，以此提高查询效率。

具体单表的存储数据量在《单表最大2000W行数据》文中有详细介绍，有兴趣的可以看看，

水平分表的本质是数据分片，将不同的数据按照一定的规则（ hash取模/range范围）将数据存储在不同的表中，以此减少单表的数据量，提高查询效率。

• 水平分表优点
  • 解决单表数据量大，查询性能下降的问题
  • 可实现多表连接查询
• 水平分表缺点
  • 引发排序、分页、函数计算等问题
  • 数据扩容的难度和维护量极大。

四、解决了什么问题/引发什么问题

通过以上分别对【 垂直分表、 垂直分库，水平分表、水平分库】的分析，可以看出来无论是分库还是分表，垂直划分和水平划分的时候，他们的优缺点很类似，所以接下来总结一下分库分表解决了什么问题/引发什么问题。

4.1分库分表解决了什么问题

• 水平划分
  • 解决了单库的高并发的性能瓶颈，提高了系统的稳定性和负载能力
  • 解决了单表的大数据量的查询效率低问题
  • 可以实现多表的join查询
• 垂直划分
  • 专库专用，能够针对不同业务的数据进行分级管理、维护、监控、扩展
  • 在一定程度上提升了IO、数据库连接数、降低单机硬件资源的瓶颈
  • 减少锁竞争，查询不同字段数据互不影响
  • 可实现冷热分离的数据表设计

4.2分库分表引发什么问题

分库分表之后，虽然能够解决数据库的性能问题，但是也带来了一系列的其他问题：

• 事务问题
• 跨库关联问题
• 排序、分页、函数计算问题
• 分布式ID问题
• 多数据源问题

4.2.1 事务问题

分库分表之后一个无法避免的问题就是问题问题，这也是一个非常频繁的面试问题：分布式事务，针对此类问题常用解决方案有：本地事务表，基于可靠消息（MQ）的解决方案、两阶段事务提交等。具体的解决放方案可参考我之前的文章《分布式集群：分布式事物解决⽅案》，这里不再展开。

4.2.2 跨库关联问题

在单库单表中，我们经常使用JOIN来进行多表查询，但是经过分库分表后多个表可能存在于多个数据库中，无法直接使用join进行联表查询，但是联表查询是非常常见的，所以针对这种情况有以下几种解决方式

• 字段冗余

  利用空间换时间，为了性能而避免join查询，将查询字段冗余。

  例如：订单表保存userId时候，也将用户名称性别等需要查询的字段冗余保存一份，这样查询订单详情时就不需要再去查询用户表了。

• 全局表

  在系统模块中，对于一些依赖的数据表，在每个数据库都保存一份，避免跨库join查询。

• ER 表（绑定表）

  先确定各个业务表的关联关系，将那些存在关联关系的表记录存放在同一个分片上，避免跨分片join问题

• 系统层组装

  在系统层面，分多次查询，将获得到的数据通过代码进行字段拼装。

4.2.3 排序、分页、函数计算问题

分库分表之后，数据分散，在跨节点进行count,order by,group by,limit 以及聚合函数的时候需要特殊处理，可以采用分片的方式：先在每个分片上执行相应的函数，得到结果后在应用程序端进行合并，得到最终结果。

4.2.4 分布式ID问题

分库分表之后，我们不能再依赖数据库自增主键了，分表以后每个表都可以自增，会导致ID 重复或者混乱的问题，因此我们需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

• UUID
• 基于数据库自增单独维护一张 ID表
• 号段模式
• Redis
• 雪花算法（Snowflake）
• 美团Leaf
• 滴滴Tinyid

针对分布式ID的问题，有兴趣的可以看看我之前的文章《分布式集群：分布式ID解决⽅案》，文中详细介绍了几种分布式ID的实现方式，这里就不再展开赘述。

4.2.5 多数据源问题

多数据源主要针对分库，既然数据库变成了多个，那什么时候查询那个库必然是一个必须要解决的问题，一般的解决方式有：应用程序适配和代理层适配。一般我们都会使用比较成熟的中间件来处理。

五、分库分表中间件

目前市面上有很多比较成熟的分库分表中间件，可以帮助我们解决分库分表后多数据源问题

1. shardingsphere（前身 sharding-jdbc）
2. cobar
3. Mycat
4. Atlas
5. TDDL（淘宝）
6. vitess

这里简单介绍一下shardingsphere和Mycat，也是用的比较多的

5.1 Mycat

MyCat属于服务器数据库中间件，是一个基于第三方应用中间件数据库代理框架，客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发具体的真实数据库服务器中。

• Mycat优点
  • 数据添加不会影响到程序
  • 应用层不需管理数据库层方面，由代理层去管理
  • 添加数据源不需要重启程序
• Mycat缺点
  • 程序依赖的中间件，提高系统复杂性和维护工作
  • 中间件本身需要解决高可用问题
  • 增加了proxy，程序性能下降

5.2 ShardingSphere（sharding-jdbc）

ShardingJdbc是一个本地数据库中间件框架，以一个Jar形式在本地应用层重写jdbc原生的方法，实现数据库分片形式。

• ShardingSphere优点
  • 程序自动完成，数据源方便管理
  • 支持sql标准下的任何数据库
• ShardingSphere缺点
  • 以JAR的形式引入，存在代码入侵性，加大开发成本
  • 不能做到动态添加数据源，添加数据源还需要重启程序