前言

在上一篇文章中，我们谈到了如何使用 event scheduler 定期将过期数据归档到其他表中，从而达到冷热数据分离，加快热数据的访问速度。同时，我们也在文末也提到了采用该种方案可能会带来的一些问题:

  • 在可重复读的隔离级别下，根据访问规则本应该在归档表内的数据因为归档 transaction 尚未提交导致此时归档数据对其他 transaction 不可见的问题
  • 由于归档数据的操作需要在原表删除大量过期记录，如果使用的是 myisam 引擎，此时会长时间锁住整张表，阻塞其他的操作。在 innodb 引擎中，情况也不太乐观，删除大量数据虽然不会占用表锁，但是对应的行会被锁住。同时，此时 trigger 也会被触发，而且mysql 也会为 delete 操作记录 undo log 和 redo log，消耗大量的 I/O 资源。

由此可见，通过 event scheduler 并非是一个很好的方案，虽然解决了冷热数据分离的问题，但同时也带来了一些新的问题。那么是否有一种性价比更高的方案达到冷热分离呢？分表！

相信大家对分表肯定不陌生，分表按大家熟知的可以分为垂直分表和水平分表:

  • 垂直分表：将含有较多字段的单张表根据字段切分为多张表，如将含有A，B 两个字段的表分别切分为含有 A 字段的表和含有 B 字段的表；
  • 水平分表：将表中的记录根据规则分配到多个表。如将含有记录 1，2，3 的一张表拆分到 3 张结构相同的表中，每张表只含有一条记录。

今天我们将要讨论的是水平分表。

分表的原理

首先我们需要知道为什么在表数据较多的情况下索引的效率会变低？在数据结构中，B 树是一个老生常谈的话题，作为一棵自平衡搜索树，在每次的插入和更新操作时都会进行自旋来维持平衡性，通过平衡性来保证较小的搜索深度。对于含有 m 个记录的 n 叉 B 树来说，树的高度（秩）为 lognm，其搜索的时间复杂度为 O(lognm)。在 innodb 中，数据及其索引的底层存储结构就是 B 树，所以每次当我们在插入以及更新数据时，对应的索引就需要自旋，特别当表上的索引较多或非连续性插入读取时将会造成大量的 I/O 消耗。同时，大量数据也同时产生了大量的索引记录，此时内存不足以一次载入整个索引进行查询，当我们查找某些记录时很可能会需要多次分批将索引载入内存才能找到对应记录（如果 select 的列不满足覆盖索引，此时还需要根据记录的 id 访问聚簇索引获取信息）。

由此可见，在面对大量数据时，会因为内存以及磁盘的限制导致查询速度较慢。那么我们将表的索引大小减少到内存足以一次能够载入不就解决这个问题了吗！我们可以通过：

  1. 增大数据库的内存足以一次载入需要的整个索引
  2. 减少单张表的记录数，也即减少单张表的索引大小

A partition is a division of a logical database or its constituent elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability[1] reasons, or for load balancing.

通过分表我们可以把单张表的数据分散到多个结构相同的较小规模的表中，从而减少了单张表的索引大小，让内存足以一次载入。分表相当于做了一次粗粒度的数据索引，他将分表规则运算结果相同的数据聚集在同一个 partition 内，从而提升数据的访问效率。

分区表由多个底层表组成，每个底层表都由句柄对象表示。在对分区表进行操作时，mysql 会首先打开所有的底层表，此时根据 sql 语句判断是否可以过滤部分分区，然后再对选中的底层表进行操作。例如，当删除一条数据时，Mysql 需要先找到这条记录，如果 WHERE 语句恰好和分区表达式匹配，那么所有不包含这条记录的分区都会被过滤掉。

在一些特殊的场景下，mysql 并不能过滤掉额外的的分区，这会导致大量的额外消耗。例如：

Handling of NULL with RANGE partitioning. If you insert a row into a table partitioned by RANGE such that the column value used to determine the partition is NULL, the row is inserted into the lowest partition.

如 Mysql 文档中强调的一样，向使用 range partition 的分区表插入 null 值时，此时始终会插入到第一个分区中。同样，当在 select 语句中出现不合法的值或者 null 时，第一个分区也会被选中。例如, 假设按照PARTITION BY RANGE YEAR(order_date), 当我们使用where order_date between '2020-02-01' AND '2020-02-31'时，此时2020和第一个分区会同时被选中。

因为在每次数据操作时都要打开所有分区并且进行筛选的因素，所以分区的数目过大反而会导致查询成本上升。特别是对于 range partition，mysql 需要对每个 parition 的分区列进行判断来进行筛选正确答案，类似这样的线形搜索在 parition 较多的情况下成本较大。同时，每个 parition 都是一个句柄对象，当分区数量较多同时也会造成因操作系统限制而产生的Got error ... from storage engine: Out of resources when opening file 错误。

关于分表的一些限制

  • 对于 innodb 来说，分区表不支持外键约束
  • 同一个分区表的所有底层表都应当使用同一种数据库引擎（如 innodb）
  • 在 mysql 的一些特定版本中，query cache 对于分区表不好使（可参考https://bugs.mysql.com/bug.php?id=53775）
  • 分区表不支持全文索引
  • Partition key 或者表达式可以被解析为 integer（ float 和 decimal 不行)
  • Partition key 不支持列前缀索引
  • 分区表限制了数据访问的方式，当不按照 partition key 的声明方式访问数据时将会访问所有的 partitions
  • partition key 中的所有列都必须在表的每个唯一索引中（包括主键）

关于分表的一些 tips

  • 当你足够相信 partition 能够帮到你时，再使用 partition
  • 直到你的数据超过 > 1M 行时，再考虑使用 partition
  • 虽然 mysql 5.6.7 已经最多支持 8192 个 partitions，但是尽量保持你的 partition 个数在 20-50 之间（mysql 8.0 版本从文档中移除了 50 个 partitions 的建议，建议通过测试得到最适合的 partitions 个数）.
  • SUBPARTITIONS 没什么用
  • 创建一个”无用“的分区，例如在根据 TO_DAYS() 进行分区时，指定第一个分区为 LESS THAN (0)，这样即使因为非法值需要打开第一个分区，带来的开销也很小
 CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dt DATE
)
 PARTITION BY RANGE (TO_DAYS(dt)) (
       PARTITION start        VALUES LESS THAN (0),
       PARTITION from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16'))
);
  • 相较于 delete 删除整个 partition 的数据，drop partition 的开销更少
  • 不要 count partition.使用 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1';

如何分表

创建分区表：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (0),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990),
    PARTITION p3 VALUES LESS THAN (2000)
);

在已有分区表上增加 partition:

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

修改已有分区：

ALTER TABLE members
    REORGANIZE PARTITION p1 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
); # 将原来的一个分区变成两个分区
ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO (
    PARTITION p1 VALUES LESS THAN (1970)
); # 将原来的两个分区变成一个分区

向分区表中插入数据:

INSERT INTO members VALUES ('desk', 'organiser', '2003-10-15');

总结

今天我们一起了解 mysql 分表的使用以及原理，其中的大部分知识都同样适用于其他的关系型数据库例如 postgresql。正如在上面的提到的一样，分表也不是数据库实践中的银弹，在一些场景下反而会造成性能的下降。只有在我们了解其原理和适用场景后，才能够综合比较各种方案的优劣，并且在方案的使用过程中尽量避免一些外部限制导致的不符合预期的行为。

本文参考