前言

今天想要讲述的这个话题是来自 stackoverflow 上的一个讨论, 具体可以访问这里查看原问题详情。问题原意大概是题主有一个 postgresql 数据库，里面有 appointments, messages 几张表，这些表都有日期字段，并且经常被访问到的是近期的数据，而很早以前的数据则很少被访问到。当有请求读取 2 年以前的数据时，此时磁盘读取达到峰值，其他原本几十毫秒就可以返回的请求也被拖慢到几百毫秒。
那么看到这里，大部分的伙伴们大概都知道了这就是很常见的冷热数据分离问题，当然除了另一位回答者 laurenz-albe 提到的提高 RAM 使 db 能够在内存中存储更多的数据以及使用更快访问速度的磁盘以外，同时也可以使用周期性地归档来避免过多的冷数据使得热数据的访问速度被拖慢。

借助 event scheduler 来定期归档

数据归档大家肯定都很熟悉，借助百度百科的定义

数据存档（data archiving）是将不再经常使用的数据移到一个单独的存储设备来进行长期保存的过程。

在我们数据库的语境下，就是 migrate 数据从一个表到另一个表或者到另一个库。我们考虑到历史数据仍然需要被访问，所以我们选择将历史数据归档到另一张表里。
我这里取个巧，我将用 mysql 来演示这个过程。mysql 中的 event scheduler 就像 linux 中的 cron 一样，他能够定期执行任务。mysql 的官方定义如下

The MySQL Event Scheduler manages the scheduling and execution of events, that is, tasks that run according to a schedule.

我们首先建一张 students 表，该表有个 datetime 字段记录学生的入学时间，我们假设越早入学的同学被访问到的概率越小。同时我们再创建一张 students_archive 表，该表在 students 表上再增加个 students_id 字段来记录在源 students 表时的 id。

// 创建 students 表
CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `admission_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admission_time` (`admission_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

// 创建 students_archive 表
CREATE TABLE `students_archive` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `admission_time` datetime DEFAULT NULL,
  `students_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `students_id` (`students_id`),
  KEY `admission_time` (`admission_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

好了，我们再来创建归档数据的 procedure. procedure 的中文名叫存储过程，在 wiki 中的定义为

存储过程，又称存储程序（英语：Stored Procedure），是在数据库存储复杂程序，以便外部程序调用的数据库对象，可以视为数据库的一种函数或子程序。

DELIMITER //

CREATE PROCEDURE archive_students()
 BEGIN

 DECLARE _now DATETIME;
 SET _now := NOW();

 INSERT
 INTO    students_archive(students_id, admission_time, email)
 SELECT  id, admission_time, email
 FROM    students
 WHERE   admission_time < _now - interval 3 day;

 DELETE
 FROM    students
 WHERE   admission_time < _now - interval 3 day;
 END//

我们创建了一个 archive_students 的 procedure 将 3 天前入学的学生从 students 表里都迁移到 students_archive，同时将这些记录从 students 表里删除掉.同时创建一个 event scheduler 定期调用 archive_students.

CREATE EVENT archive_students
ON SCHEDULE EVERY 5 MINUTE
DO 
  call archive_students();

我们在 mysql cli 里通过 show events 可以看到我们刚才创建的定时归档任务。

mysql> show events;
+------------+------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db         | Name             | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------+------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| dummy_data | archive_students | root@localhost | SYSTEM    | RECURRING | NULL       | 5              | MINUTE         | 2020-09-13 17:22:53 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_unicode_520_ci   | utf8mb4_unicode_520_ci |
+------------+------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

在 event scheduler 执行完以后我们可以校验数据是否已经成功地归档到 students_archive 表了呢?

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from students_archive;;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.19 sec)

好了，看到这里其实一些小伙伴们其实心里应该有了一些大致的判断。所以这种方式有什么优缺点呢？
优点：

  • 将冷热数据分表管理，能减少大量冷数据访问对热数据访问的影响

缺点：

  • 如果在原表冷数据的数据量较大，该归档过程本身也会影响到原表的其他请求
  • 表的映射逻辑并不对应用程序透明，应用程序访问数据与表映射逻辑绑定
  • 在可重复读的隔离条件下，可能会存在归档事务正在执行尚未提交，请求根据映射规则访问归档表发现无数据的情况

总结

今天我们一起了解了使用 event scheduler 来定期归档数据，从而达到冷热数据分离。当然想要冷数据不拖慢热数据的访问，还可以使用 partition 这种更常见的办法，partition 可以做到在一定程度上对应用程序透明，使应用不用关心数据的映射逻辑，同时能够降低每张表的体积.
同时 mysql 也有很多的归档工具，比如 pt-archiver，相比于手动 insert 和 delete 会更具效率。