关于数据库:mysql海量数据表的归档

日常的业务零碎，跟用户相干的操作数据，或者日志记录，往往会越来越多，而晚期数据的查问需要没有那么强，就能够进行归档解决——作为冷数据保留，并从在线数据库中删除。

一般数据表归档

简略的数据归档很好实现，比方有这样一张表：

CREATE TABLE `operation_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `type` varchar(20) NOT NULL,
  `amount` decimal(20,8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_date` (`create_date`),
  KEY `ix_type_date` (`type`,`create_date`)
) 

就能够依照工夫查问，选出指定日期前的数据，保留后，再从数据库中删除。示例代码如下 (python + sqlalchemy)：

# 已获取数据库连贯 connection，或者 session

# 查出所有旧数据
records = connection.execute(’select * from operation_log where create_date < "2022-06-01"‘)

# 保留查出来的数据到文件，或者到冷备份数据库
# ......

# 删除已导出的数据
connection.execute(’delete from operation_log where create_date < "2022-06-01"‘)

海量数据表归档

但如果这个表中寄存了海量数据，比方全表 10 亿条，后果集 1 千万条，显然就不能这样莽撞的执行了：

1. 1 千万条后果集都加载到应用服务器是很危险的，通常会撑爆内存
2. ‘create_date’ 字段尽管有索引，查找速度显然也是不如主键的，

针对问题 1，解决方案就是分段查问，每次迭代查出一批，解决一批，直到解决完所有符合条件的数据；针对问题 2，能够利用主键 id 自增的个性（按记录生成程序增长），把大部分查问，转化为以主键为条件。代码示例如下：

# 已获取数据库连贯 connection，或者 session

BATCH_SIZE = 100000 # 设置批量数据为 10 万条
FINAL_DATE = datetime.date(2022,6,1)

records = connection.execute('select id from operation_log order by id limit 1')
batch_end_id = records[0]['id']  # 先把迭代 id 初始化为
batch_end_date = datetime.date(1980,1,1) # 设置一个很早的起始日期

while batch_end_date < FINAL_DATE:
    batch_end_id += BATCH_SIZE
    records = connection.execute('select * from operation_log where id<{}'.format(batch_end_id))
    
    for record in record:
        record_date = record['create_date']
        if batch_end_date < record_date:
            # 获取本次迭代最晚一条记录的工夫
            batch_end_date = record_date
        if record_date < FINAL_DATE:
            # 保留查出来的数据到文件，或者到冷备份数据库
            # ......

    # 删除已导出的数据
    if batch_end_date >= FINAL_DATE:
        # 只有最初一次迭代，以 date 为条件，保障删除的数据不超过
        connection.execute('delete from operation_log where id<{} and create_date <"{}"'.format(batch_end_id, FINAL_DATE.strftime('%Y-%m-%d')))
        break
    else:
        # 失常迭代只有 id 作为查问条件，速度会快很多
        connection.execute('delete from operation_log where id<{}'.format(batch_end_id))