MySQL分库分表分区

数据库存储的演进

什么时候需要进行分库分表

参考什么时候需要分表分库？

一般来说，能不分表分库就不要分表分库。当业务正常时，使用单表即可，当业务出现了性能瓶颈时，再依次考虑分区、分表和分库。尽量避免"过度设计"和"过早优化"。

单表数据量达到多少才需要考虑分表？有说500 万行的，有说2000万行的，还有说5000万行的。

我们一般可以将数据库分为：单库单表 - 单库多表 - 多库多表。

• 在平时的业务开发中，我们应该优先考虑单库单表；
• 如果数据量比较大，且热点数据比较集中、历史数据很少访问，我们可以考虑表分区；
• 如果访问热点数据分散，基本上所有的数据都会访问到，我们可以考虑单库多表；
• 如果并发量比较高、海量数据以及每日新增数据量巨大，我们可以考虑多库多表。

能不分表分库，就不要分表分库。这是因为

• 一旦分表，我们可能会涉及到多表的分页查询、多表的 JOIN 查询问题。
• 一旦分库，除了跨库分页查询、跨库 JOIN 查询，还会存在跨库事务的问题。

这些问题无疑会增加我们系统开发的复杂度。

单库单表

单库单表是最常见的数据库设计，很多业务刚开始时都只需要单库单表，即数据存储只使用一个服务器结点，这个服务器上部署着一个数据库，数据库里对于每个存储场景只有一张表。（如果有多个服务器结点，就需要考虑分布式问题了，会使数据存储更加复杂。）

比如说将所有订单数据存放在一张表里，那么所有的历史订单都可以在这张表上查到。但是随着时间的推移和业务的发展，数据表中存放的数据量越来越大，当数据量达到一定程度的时候，会影响到db的性能（比如索引效率变低)，因此可以考虑通过分区、分表、分库的方法来优化db的设计。

并不是说分库分表就一定会比单库单表效率更高，任何策略都需要视具体业务场景来进行取舍。

其中，mysql本身就支持分区功能，而分表和分库需要开发者自己设计和实现。

分区

参考MySQL数据库表分区功能详解

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面，一张表主要对应着三个文件，

• frm文件：存放表结构
• myd文件：存放表数据
• myi文件：存放表索引

如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这时就可以使用mysql的分区功能。

分区是指根据一定规则，将数据库中的一张表分解成多个更小的的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成，最常见的分区方法就是按照时间进行分区。在合适的业务场景下，使用数据表分区可以优化db性能：

• 查询时可以直接在对应的分区里查找，不需要对整个数据表进行查找
• 不同的分区可以存放在不同的磁盘上，不仅可以存放更多的数据，而且在进行某些聚合查询时可以同时在多个不同的磁盘上并行操作，最后合并，提高查询效率。
• 删除一部分的分区数据时，可以直接将整个对应的分区删除，效率高（没有分区的数据表在删除数据时需要移动数据、调整索引）

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。

以最常用的RANGE分区为例，根据范围分区，范围应该连续但是不重叠，使用 PARTITION BY RANGE, VALUES LESS THAN关键字。在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。

一个根据数值范围分区的例子

drop table if exists employees;
create table employees(
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null default 0,
    store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21)
);

按照这种分区方案，新增的行记录就会按照store_id的取值，插入到对应的分区文件中（包括数据分块和索引分块）

更多场景下会使用时间来进行分区。

分表

参考MySQL的分区/分库/分表总结

一张超大的数据表会带来很多问题：

1. 单表数据量太大，所有读写请求施加在同一张表上，导致频繁的加锁解锁操作，导致性能降低。
2. 单表数据量太大，对应的索引也会很大，导致索引查询效率降低。（ MySQL 在 InnoDB 存储引擎下创建的索引都是基于 B+ 树实现的，所以查询时的 I/O 次数很大程度取决于树的高度，随着 B+ 树的树高增高，I/O 次数增加，查询性能也就越差。）

因此出现了分表的优化方法，分表是指把一张表按一定的规则分解成多个具有独立存储空间的实体表，系统读写时需要根据定义好的规则得到对应的表名，然后操作它。

分表之后，/mysql/data下每一张表都有三个配套的文件：一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。

分表与分区的区别

分表和分区看起来十分相似，因为分区就是分表的数据库实现版本。在MySQL 5.1分区功能出现以前，要想解决超大表问题，只能采用分表操作，因为这类问题十分常见，MySQL才自带了一个分区功能，以达到相同的效果。所以你可以直接说分区就是分表的替代，分表是分区出现以前的做法。不过这不代表我们就没有必要学习分表了，这是因为

• 分区和分表并不是互斥的，二者可以搭配使用。
• 分区只能替代水平分表的功能，无法取代垂直分表的功能。
• 分区没法突破数据库层面，所有分区都要在同一个数据库下，而分表可以将子表分配在同一个库中，也可以搭配分库的策略，将子表分配在不同库中，后者可以突破单个数据库服务器结点的性能限制。

水平分表

水平分表指的是将数据行按照一定的规则分配到不同的子表中，与分区的原理相同，但是与分区不同的是，分表可以选择将切分出来的子表分散到不同的服务器结点的数据库上，从而突破单个数据库的磁盘IO瓶颈。

注意分表之后，需要开发者自己写代码实现，如何将读写请求发送到对应的子表中去。

垂直分表

水平分表分的是行记录，而垂直分表，分的是列字段。一般的使用场景是，表中的字段较多或者有数据长度较长（比如text，blob，varchar(1000)以上的字段）的字段时，我们将不常用且字段数据量大的字段拆分到“扩展表”上。这样避免查询时，数据量太大造成的“跨页”问题

分库

分库的主要目的是为突破单节点数据库服务器的I/O能力限制，解决数据库水平扩展性问题。如果不拆库，那么单库所能支持的吞吐能力和磁盘空间，就会成为制衡业务发展的瓶颈。

分区和分表可以把单表分到不同的硬盘上，但不能分配到不同服务器上。一台机器的性能是有限制的，用分库可以解决单台服务器性能有限的问题（注意只有分表能够搭配分库策略，分区无法搭配分库策略）

分库也可以分成两种，

• 水平分库：将一个数据表的行记录拆分到不同库的子表中（例如用户表，可以使用注册时间的范围来分表，将2020年注册的用户表usrtb2020部署在usrdata20中，2021年注册的用户表usrtb2021部署在usrdata21中。）
• 垂直分库：将一个数据表的不同列字段拆分到不同库的子表中（按照业务类型区分，例如可以将用户相关的表都放置在usrdata这个库中，将订单相关的表都放置在odrdata中，以此类推。）

实际上就是，水平分表搭配分库策略就是水平分库，垂直分表策略搭配分库策略就是垂直分库。

分库分表会遇到的问题

参考数据库分库分表思路

分布式事务一致性问题

当更新内容同时分布在不同库中（比如一条写操作语句需要同时修改不同库中的数据表），就遇到了分布式事务的问题，因为mysql只能在一个数据库中保证事务的原子性，无法保证在不同库上的操作都能执行成功，如果在库1上修改成功，在库2上修改失败，就会导致一致性的问题。

跨节点关联查询 join

在垂直分库（将不同的业务数据存放在不同的库表上）的场景下常见的问题，mysql的join只能将同一个数据库中不同数据表的数据关联起来，不支持跨节点将不同数据库的数据表的数据关联起来。

因此在分库分表的情况下一般都不进行join操作。

跨节点分页排序、函数

在水平分库（按照某些字段或规则将行记录拆分到不同的库表上）的场景下常见的问题。

order by排序 + limit分页

mysql不支持跨节点下order by + limit操作，开发者需要自己实现这个逻辑：先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。如图所示：

Max、Min、Sum、Count等函数

同样，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回，如图

全局主键避重问题

常见于水平分库场景下的问题，在分库分表的情况下，无法再用数据库的自增长ID来作为主键，需要开发者生成一个全局唯一ID作为主键，全局唯一ID的生成的方法比如有：

• uuid：根据时间戳、网卡mac地址等参数生成一个全球唯一ID，包含32个16进制数字。优点是可以本地生成，缺点是长度太大，存储开销大。
• 基于redis、zookeeper等来生成唯一ID：分布式全局唯一ID

其他

除了分区、分表和分库外，其实还有数据库分片，参考数据库分片（Database Sharding)详解

大致的说，就是在创建数据表时，指定某一个列为分片键，然后该数据表的数据就会被分成几个不同的分片，分布在不同的数据库机器节点上。前提是：

• 要求数据库层级支持分片功能，否则只能自己在应用程序层级实现分片功能（字节云的rds就支持分片功能）
• 数据库需要是分片库，在分片库里才能创建分片表

优点：支持水平扩展，分片表可以承担更大的数据库查询压力。

缺点：分片表只能根据分片键进行查询，如果查询条件where子句不带分片键，则无法查询数据。