分库分表、MySQL分区表

一、前言

为什么需要拆分

   读写分离主要应对的是数据库读并发，没有重点去解决数据库存储问题。

   随着业务量的增长，数据量也会随之增加，进而影响 SQL 的执行效率。所以，分库分表的结构，是为了提高部分 SQL 的性能、降低单库单表的数据存储压力。

什么时候需要拆分表

1. 阿里开发手册建议：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。如果预计三年后的数据量达不到这个级别，则不考虑该问题。

2. MySQL 中，当 InnoDB Buffer Pool (默认128MB) 不足以将索引页装载到内存中时，会使得 SQL 查询产生磁盘 IO 读取索引，从而导致性能显著下降，此时也需要拆分。

   • InnoDB 中，假设聚簇索引为 bigInt（8byte），那么 2层的B+树，索引大小为16K；3层的B+树，索引大小为20MB；4层的B+树，索引大小为25 GB。计算逻辑可参考

二、分表的方案与其带来的问题

垂直分表

   按照字段进行拆分，常规的方案是冷热分离（将使用频率高字段放到一张表里，剩下使用频繁低的字段放到另一张表里）。

   当需要跨表查询字段时，我们可以使用数据库中间件（sharding-sphere，mycat）来完成，或在业务代码中实现，有一定的代码侵入性。

水平分表

   按照表中的记录进行分片。分表数量 = 数据总量 / 单表数据量。其中数据总量，取一年后可能到达的最大数据量计算；单表数据量按推荐的 500W 条记录计算；拆分的“种子”需要结合业务中常用的字段，尽可能使数据分布的更均匀。

   当需要跨表查询行数据时，需要自主实现重写业务中的 SQL，或者引入中间件 sharding-sphere、sharding-jdbc，有一定的代码侵入性。

   水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

MySQL分区表

   MySQL分区表的分表方法和水平分表的数据拆分逻辑一致，但不同的是，分区表执行 SQL 时，由 MySQL 内部实现 SQL 路由的功能，不用去改造业务代码。MySQL分区的更多介绍

三、分库

   上面个提到的，都是分表的方案。分表的方案下，基本能够满足国内大部分公司了，但当分表已经解决不了数据快速增长的问题时，就需要进一步考虑分库的问题了。

   分库的目的，是因为 一台 MySQL 实例的读写已到性能瓶颈，需要更多的物理硬件来提高 DB 性能。

   分库后，虽然在一定程度上，提高了 DB 性能，但同时将引入一些问题，如跨库查询（分页、排序、函数等）、只能使用分布式事务等，所以与其考虑 MySQL 的分库，不如考虑使用更优秀的 DB。

四、拆分后带来的问题与解决方案

Primary Key、Unique Key如何保证唯一性

自增主键无法在分表场景下保证全局唯一性，所以PK或UK需要使用其他方案来生成。

1. 分布式ID，自己实现一套分布式ID生成算法，或者使用开源的比如雪花算法
2. 分表后不使用自动生成的主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表的订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。

非Sharding Key查询的方案有那些？

问题描述：

   当选择了 user_id 作为分片键后，如果需要根据orderId 查找 userId 时，由于查询条件的orderId不是分片键，所以需要查询所有分片才能得到最终的结果，其耗时非常高。

   非Sharding Key查询需要扫描所有分片，对此我们有几个思路，用空间换时间，记录对应的映射关系，或能够提前知道orderId对应的分片是哪个，避免全分片扫描，都可以提高效率。因此，我们有如下的非Sharding Key查询方案。

1. 冗余Sharding Key和orderId的映射关系

   查询时 2 条 SQL 都可以通过分片键进行查询，这样能保证只需要在单个分片中完成查询操作，不论有多少个分片，也只需要查询 2个分片的信息，这样 SQL 的查询性能可以得到极大的提升。

2. 冗余到数仓，使用ES等方式去查询
   存在一定的延时，有一定的业务局限性。

3. 基因法：将SK信息冗余记录到查询的非SK字段中
基因法有两种实现。

• 将分表的基因提取出来，冗余到查询的非SK字段中
• 将分表的字段，直接冗余到查询的非SK字段中（淘宝订单中，就是采用的这个方式，将订单号的末尾冗余了SK）

如何安全、平滑的完成数据迁移？

1. 停机迁移
   • 需要对外暂停服务
2. 双写方案：写入操作时，写两个库，并对两个库的数据进行核对和同步
   • 有业务代码侵入性，迁移过程中可能出现数据风险
3. 使用数据库同步工具 Canal 完成树迁移

五、业务案例

   某支付部门，23.4.1一天产生订单6670558个订单，平均一个月产生约2亿订单，需要考虑分库分表方案。由于分库需要考虑复杂的分布式事务，且影响DB的吞吐量，所以我们考虑分表方案是否能瞒住数据述求。

   其分区键，我们设置的是订单号中的两个分区位+父订单号中的两个分区位+外部订单号的hash值被100取模，将数据分配到100张表中，具体的建表sql如下。

CREATE TABLE orders (
    net_order_id VARCHAR(50) NOT NULL,
    parent_net_order_id VARCHAR(50) NOT NULL,
    source_site_order_id VARCHAR(50) NOT NULL,
    order_time TIMESTAMP NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,

    -- 其他字段
    PRIMARY KEY (net_order_id)
)
PARTITION BY HASH(SUBSTRING(net_order_id, -12, -10) PARTITIONS 100;

---

六、MySQL分区表的深入学习

   MySQL 数据库在 5.1 版本时添加了对分区的支持（仅支持水平分区），属于 局部分区 类型（一个分区中既存放数据又存放索引），全局索引（数据存放在各个分区中，但是所有数据的索引放在一个文件中）类型暂不支持。

   分区的过程是 将一个表或索引分解为多个更小、更可管理的部分。对访问 DB 的应用而言，从逻辑上讲，只有一个表或一个索引，但物理上由多个文件组成。

当前 MySQL 支持以下几种类型的分区：

1. RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。MySQL5.5 开始支持。

2. LIST分区：和 RANGE 分区类型类似，只是 LIST 分区面向的是离散的值。MySQL5.5 开始支持。

3. HASH分区：根据用户自定义的表达式的返回值来进行分区，函数中的返回值不能为负数。MySQL5.1 开始支持。

4. KEY分区：根据 MySQL 提供的哈希函数来进行分区。MySQL5.1 开始支持。

在指定分区列时，需要分区列满足如下条件中的一个即可：

• 是主键
• 属于唯一索引的一部分
• 当创建表时没指定主键或唯一索引时，分区列可以任意指定

可以理解为，存储引擎可以通过指定的列，去构建存储一张表时，那么该列可以作为分区列使用。

分区类型

对于表的分区信息，可使用如下命令查看

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name='salaries_partition'

RANGE 分区

根据分区列的大小，判断数据应该存入哪个分区表中。该类型常用于做日期类型的分区，如按年存放公司销售数据、按月存放公司第三方账单数据。

例：将 sales 表按月进行分区

CREATE TABLE `sales`  (
  money INT UNSIGNED NOT NULL,
  date DATETIME
) engine=Innodb
PARTITION by RANGE(TO_DAYS(date))(
PARTITION p201001 VALUES LESS THEN(TO_DAYS('2010-02-01')),
PARTITION p201002 VALUES LESS THEN (TO_DAYS('2010-03-01')),
PARTITION p201003 VALUES LESS THEN (TO DAYS('2010-04-01'))
);

   此处对分区列采用的是 TO_DAYS() 函数（返回日期和年份0之间的天数），这是因为通过分区列查询时，优化器只能对 YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP() 这类函数进行优化选择。如果不使用规定的函数进行分区，那么在查询时即使使用到了分区列做条件，依然会做全表扫描。

   如果我们要删除 2010-02-01 ~ 2010-02-28 的数据时，可以使用 DELETE 的方式，也可以删除 2010-02-01 数据所在的分区，如：

alter table sales drop partition p201002 ;

List 分区

   LIST分区和RANGE分区非常相似，只是分区列的值是离散的集合，而非连续的。如：

CREATE TABLE t (
a INT,
b INT) ENGINE=INNODB
PARTITION BY LIST(b)(
PARTITION p0 VALUES IN (1,3,5,7,9),
PARTITION p1 VALUES IN (0,2,4,6,8));

同样的，当插入的值，不在定义的分区中时，将抛出异常。

Hash 分区

   HASH 分区可以将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致相同。

使用 Hash 的方式创建分区表：

CREATE TABLE `salaries_partition`  (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`, `from_date`) USING BTREE # emp_no作为被分区的键，必须满足下文中提到的条件
) PARTITION by HASH(emp_no) PARTITIONS 5;  # 按emp_no做hash取模，分为5个分区

重建 Hash 分区：

Alter table salaries_partition partition by hash(emp_no) partitions 3;

从上面分析的SQL可以看到，salaries_partition对外呈现一张表，但 MySQL 在执行时，内部会去分析 SQL，查找需要使用到的表。

Key 分区

   KEY 分区和 HASH 分区相似，不同之处在于 HASH 分区使用用户定义函数的返回值进行分区，KEY 分区使用 MySQL 数据库提供的函数进行分区。

RANGE/List COLUMNS分区

   在前面介绍的四种分区中，分区的条件是：数据必须是整型(interger)，如果不是整型，那应该需要通过函数将其转化为整型。MySQL5.5 版本开始支持 COLUMNS 分区，可视为 RANGE 分区和 LIST 分区的一种进化。COLUMNS 分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE/List COLUMNS 分区可以对多个列的值进行分区。

COLUMNS分区支持以下的数据类型：

• 所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。

• 日期类型，如DATE和DATETIME。其余的日期类型不予支持。

• 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。

   所以，在 Range 分区 用例中，我们不再需要 TO_DAYS 之类的函数了，而直接可以使用 RANGE COLUMNS（date）。

分区中的 NULL

   类似于 ORDER BY 对 NULL 的排序处理一样，MYSQL 的分区总是视 NULL 值视小于任何的一个非 NULL 值。因此对于不同的分区类型，MySQL数据库对于NULL值的处理也是各不相同。

• 对于 RANGE 分区，NULL 值属于最左边的分区。
• List 分区下，NULL 值必须指明所属的分区
• 对于 Hash、Key 类型的分区，会将 NULL 值返回0，并放入对应的分区。

分区表总结

   对于 MySQL 分区表，学习了不同分区方式与其适用的场景，然后了解了关于 NULL 值的在分区中的归类。MySQL 分区，其优点就在于，对查询的业务方而言，不需要改动，由 MySQL 去完成 SQL 的解析、查询、归并结果；但其缺陷，分区表的总入口只有一个，连接数、网络吞吐等资源都受到单机的限制。

   所以，MySQL 分区表的处理方式，适用于数据不是海量（分区后，各个分区内的数据量正常），并发能力要求不高（DB 主从等架构可以对外提供正常服务）的业务；

   对于 MySQL 分区表的缺陷，业界提出了 sharding-sphere + ES 等一套应对更大数据量的架构方案，此处暂留，不做探讨。

---

参考资料：

《InnoDB存储引擎第2版 #表 #分区表》

分库分表方案
分库分表后的问题