前一阵子清理线上 MySQL翻出来一张 320GB 的 InnoDB 日志表。这张表三个月前改成历史归档以后就再没产生过一笔交易但每天还占着整台实例将近三分之一的磁盘空间。DBA 朋友扫了一眼说“这种只写不读、只增不改的冷数据你放 InnoDB 里不是给自己找罪受吗换个 ARCHIVE 引擎试试。”我一开始半信半疑后来真把这张 320GB 的表迁到了一张不到 28GB 的 ARCHIVE 表里数据行数一模一样磁盘占用直接砍掉九成。这个反差让我想把 ARCHIVE 引擎从头到尾聊清楚。这篇文章不是去背官方文档而是基于我实际把生产库里的日志流水、历史订单、审计记录迁到 ARCHIVE 表后踩过的坑和总结出来的经验。内容包括ARCHIVE 到底适合什么场景、建表时有哪些反直觉的限制、写入和查询的真实表现、怎么从 InnoDB 平滑迁移以及哪些情况下建议你干脆别碰 ARCHIVE。如果你正在纠结历史数据到底该放 MySQL 的哪个引擎这篇文章应该能帮你少走不少弯路。1. ARCHIVE 引擎到底在解决什么问题把“冷数据”从 InnoDB 里解放出来1.1 冷数据把 InnoDB 撑满这件事比想象中更常见大多数业务一开始不会太在意冷数据订单表、日志表、消息表全部堆在 InnoDB 里看着也就几十 GB。可日子一长你会发现这些表里真正被访问的数据可能只有最近三个月剩余的历史数据既不会产生更新也很少被查询却要跟着每次备份、每次 Binlog 复制、每次全表扫描一起承担成本。我做过一个统计某业务库总容量 1.2TB其中超过 2 年未访问的流水数据占掉 780GB。这些行没有一次 UPDATE唯一的操作只是偶尔被监管要求捞出某个月的数据或者做一次离线统计。用 InnoDB 存这些数据本质上是给保姆开豪车功能严重过剩。ARCHIVE 引擎就是冲着这个痛点来的。它的官方定位很直白专门用来存储大量、不经常被访问、以批量插入和批量读取为主的历史归档数据。它最大的卖点是压缩存储底层用 zlib 把整行数据压缩后写入磁盘通常能省下 70% 到 90% 的空间。1.2 ARCHIVE 和 InnoDB 的本质差异功能少所以轻InnoDB 是一个通用型存储引擎支持事务、行级锁、外键、二级索引、崩溃恢复几乎能覆盖所有业务表需求。这些能力非常好用但也意味着每一行数据都需要额外的页结构、索引维护和写日志开销。ARCHIVE 把这些能力全部去掉了。它不提供事务不提供行级锁不提供索引不支持 UPDATE。它只做两件事插入时压缩读取时解压。没有了索引就不需要维护 B 树没有了事务就不需要记录 Undo 日志没有了更新就不用为修改而预留空间。代价都省在明面上换来的是极高的压缩率和相对简单的写入路径。有人吐槽 ARCHIVE 不支持索引太废物但如果你换一个角度看正因为不需要维护索引它才能把磁盘占用压到这么低。每个索引都会引入额外的存储和写入开销InnoDB 下每行数据加索引后膨胀 20% 到 30% 是常有的事。ARCHIVE 直接把这个开销清零了。1.3 什么时候应该考虑 ARCHIVE什么时候别碰我习惯用三条标准来判断一张表是否适合迁到 ARCHIVE数据一旦写入不会再修改业务逻辑里只有 INSERT 和 SELECT。查询频率低且大多是按时间范围做全量扫描或离线导出不需要按业务键做精准定位。数据量大、压缩收益明显磁盘或备份成本已经能感知到了。反过来只要命中下面任何一种情况ARCHIVE 就不合适数据需要 UPDATE、需要事务保证、需要按订单号或用户 ID 频繁点查、需要外键约束、需要唯一索引来防重。这些需求都属于 OLTP 的范畴老老实实留在 InnoDB 上就好。2. 建表与写入能插能查、不能随便改这是 ARCHIVE 的门规2.1 一个最简 ARCHIVE 表长什么样ARCHIVE 表建起来没有想象中复杂跟普通建表语句一样只是引擎换成 ARCHIVE。下面是我用来存访问日志的一个简化版本CREATE TABLE access_log_2023 ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, request_time DATETIME(3) NOT NULL, app_name VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED DEFAULT NULL, request_path VARCHAR(512) NOT NULL, status_code SMALLINT NOT NULL, cost_ms INT NOT NULL, message VARCHAR(1000) DEFAULT NULL ) ENGINEARCHIVE DEFAULT CHARSETutf8mb4;看到这里重点来了这张表里没有 PRIMARY KEY也没有二级索引。如果你在建表时给 ARCHIVE 表加一个 PRIMARY KEY比如PRIMARY KEY (id)MySQL 会直接报错。ARCHIVE 引擎从上到下就没有实现索引数据结构主键、唯一键、普通索引全部不支持。第一次接触 ARCHIVE 的同学经常会在这个地方卡住如果不给主键以后怎么按 ID 查单条数据答案是查不了或者说每次按 ID 查询都只能把整张表全扫一遍然后逐行解压比对。这个特性不是 bug而是设计如此。ARCHIVE 根本不打算为“点查”场景服务它的假设是你会用批量导出代替点查用时间结构代替索引结构。2.2 AUTO_INCREMENT 在 ARCHIVE 里的特殊地位ARCHIVE 允许表里有 AUTO_INCREMENT 列这一点常常让人误以为它能当主键用。实际上这个自增列很特殊它不需要必须是索引也不会被强制唯一。也就是说如果你显式插入一条记录指定id 100之后再自动生成一条记录MySQL 并不会因为“主键冲突”而报错因为它根本没有主键约束。这带来的管理成本是如果你想用 id 标记行身份就必须保证写入来源是可控的最好在应用层或上游生成 ID尽量避免依赖数据库自己生成。如果依赖数据库自增又不去校验重复值那么归档之后可能出现两条 id 相同的记录。普通业务表里不可想象的事在 ARCHIVE 表里是真实存在的。我现在的做法是在源表里保留原业务主键列迁到 ARCHIVE 表时不叫它主键只叫它 biz_id。写入前用发号器或源表的自增值保证唯一性查询时也不把它当成索引使用只是把它当作“标识”传给下游。2.3 写入性能实测单条插入很憋屈批量导入才是强项ARCHIVE 的写入路径是插入时对整行做 zlib 压缩然后顺序追加到文件末尾。逻辑上很像在记流水账不需要担心页分裂和索引维护。因此大批量顺序写入非常快而并发小事务写入往往因为没有事务优化而表现平淡。我在同一台 MySQL 实例上做过对比用 10 个并发线程向 InnoDB 表逐条插入访问日志每秒大概能插入 1.2 万条转到 ARCHIVE 表用相同方式逐条插入每秒只有 3000 到 5000 条差距主要来自表级锁和压缩带来的 CPU 开销。但改成INSERT INTO ... SELECT一次塞 10 万行或者用 LOAD DATA 导入文件时ARCHIVE 的吞吐能轻松超过 InnoDB 的批量导入。这个结论很实用如果你打算在业务事务里顺手写一条 ARCHIVE那体验会很差。正确姿势是先攒一批再集中导入。日志系统、埋点系统、离线归档任务通常都是这个模式所以 ARCHIVE 在真实场景里表现并不差。3. 查询的真实体验全表扫描加解压别被“存储引擎”骗了3.1 没有索引的查询到底是怎么走的只要执行SELECT * FROM access_log_2023 WHERE id 123456MySQL 就会从文件头开始逐行读压缩块、解压、检查条件然后把满足条件的行返回。全表有多大这次查询就大概要花多久。这跟 InnoDB 走主键聚簇索引的毫秒级查询完全不是一回事。更隐蔽的问题是ARCHIVE 表也会计入查询缓存之外的各种统计扫描COUNT(*)、MAX(id)、MIN(id)这类看起来很轻的聚合在 InnoDB 里可以走索引快速拿到在 ARCHIVE 里却需要把整张表解压一遍。我曾经在一张 6000 万行的 ARCHIVE 表上执行SELECT COUNT(*) FROM access_log_2023耗时接近 40 秒。换成 InnoDB如果没有可用的覆盖索引也要走二级索引扫描但有了索引后快得多ARCHIVE 是实打实全扫。这不是说明 ARCHIVE 不能查而是要管理预期。用它做“按月导出”“拉取某天全部日志”这类全扫描任务没问题但别指望它承担在线业务的高频点查。3.2 实际可行的查询加速思路按时间切表替代索引既然 ARCHIVE 不提供索引那就用 MySQL 最古老的优化思路——切表。我实践下来最顺手的方案是按月建一张 ARCHIVE 归档表。比如access_log_202301、access_log_202302、access_log_202303每月一张应用侧根据查询时间直接路由到具体表。这种做法的好处是单表数据量可控全表扫描时间也能接受。清理历史时直接 DROP TABLE 整张表比 DELETE 快得多。备份和迁移时很有弹性可以针对单月做处理。如果你的 MySQL 版本支持分区也可以把 ARCHIVE 表做成年月分区。不过我更推荐按月建物理表因为逻辑简单、排查方便不需要跟分区裁剪行为较劲。3.3 写代码时容易被忽略的两个性能细节第一在查询 ARCHIVE 表时尽量只 SELECT 需要的列避免SELECT *。虽然压缩是按整行做的读取时仍必须解压整行才能拿到字段但结果是只取两列和取全部列在传输层和下游处理上的差异巨大。很多时候瓶颈不在 MySQL而在把几万行大字段发到应用端再反序列化。第二不要在一个事务里同时查询多张大 ARCHIVE 表。虽然 ARCHIVE 无事务但查询会造成较长时间的表级读锁可能阻塞同库其他表的写入。我的习惯是在业务低峰期跑归档数据的查询脚本或者在主实例上停掉对该表的写入任务后再做离线统计。4. 存储压缩效果实测为什么同样数据能压到十分之一4.1 压缩链路和行格式说明ARCHIVE 的压缩方式很朴素每一行插入时都会被 zlib 压缩然后顺序写入。这跟 InnoDB 的透明页压缩不是一回事。InnoDB 的 COMPRESSED 行格式是在页级别做压缩压缩比受页面空洞影响而且更新频繁时可能膨胀ARCHIVE 则是行级全量压缩天然适合重复度高的日志文本。你需要知道的一点是ARCHIVE 表不需要像 MyISAM 那样额外执行myisampack来压缩它写入时就压缩好了。也没有ROW_FORMATCOMPRESSED这种开关引擎内部固定走压缩路线。想获得更高压缩比能做的主要是两件事字段类型尽量精确不要把时间存成 VARCHAR多利用重复文本比如日志级别、服务名这种低基数列压缩效果最明显。4.2 一组成熟业务库的对比数据这里分享一次完整迁移的实测数据供参考引擎表内容行数磁盘占用查询耗时InnoDB访问日志 2023 年1.2 亿328GBCOUNT(*) 需 1.4 秒走二级索引统计ARCHIVE访问日志 2023 年1.2 亿29.5GBCOUNT(*) 约 3 分 20 秒InnoDB短信发送流水8600 万128GB按 create_time 范围查询约 4 秒ARCHIVE短信发送流水8600 万12.8GB按 create_time 范围查询约 28 秒注意这次对比里 InnoDB 表是有二级索引的但索引本身也占空间。ARCHIVE 表把索引空间全省了再加上 zlib 压缩日志文本最终压缩比大约在 9 到 11 倍。短信流水里很多字段是重复的模板号、状态码压缩比甚至更高。代价也很明显全表扫描时间从秒级涨到了分钟级。所以 ARCHIVE 不是用于加速查询的是用于降低存储成本的。存储成本降下来查询速度接受不了的时候就得靠前面说的按月切表来控制单表大小别把一整年数据堆在一张 ARCHIVE 表里。5. 把 InnoDB 历史数据迁到 ARCHIVE 的完整动作5.1 迁移前先想清楚这三件事我在动手迁移前会先确认三个问题顺序不能反第一数据是否会再被 UPDATE。只要存在任何一条更新逻辑ARCHIVE 就出局。第二业务是否能接受查询耗时从毫秒变慢到秒级甚至分钟级。如果不能考虑保留最近几个月在 InnoDB只把更早数据归档。第三迁移期间是否允许源表停止写入或减少写入。如果完全不能停机就得做增量迁移方案复杂度会高很多。多数日志类和流水类业务其实都能接受“只读归档”所以第一个问题最容易确认。第二个问题一般可以通过切表和冷热分层解决真正需要纠结的是第三个怎么在不下线的情况下迁数据。5.2 分批拷贝而不是一条 ALTER 梭哈很多新手会尝试直接执行ALTER TABLE access_log ENGINEARCHIVE;这条语句在生产环境非常危险。MySQL 执行 ALTER TABLE 时会在原表基础上重建一份新表期间表级锁会阻塞读写单表几百 GB 时等于是给自己制造故障。我奉劝一句话测试库里随便玩生产库千万不要这么干。正确的批量迁移思路是先建好 ARCHIVE 目标表再用分批INSERT INTO ... SELECT把源表数据搬过去最后在原表上按主键范围分批 DELETE。我经常用下面这个存储过程来做分批复制DELIMITER $$ DROP PROCEDURE IF EXISTS sp_archive_batch$$ CREATE PROCEDURE sp_archive_batch( IN p_cutoff_time DATETIME, IN p_batch_size INT ) BEGIN DECLARE v_copied_id BIGINT DEFAULT 0; DECLARE v_rows INT DEFAULT 1; WHILE v_rows 0 DO INSERT INTO access_log_archive ( request_time, app_name, user_id, request_path, status_code, cost_ms, message ) SELECT request_time, app_name, user_id, request_path, status_code, cost_ms, message FROM access_log_source WHERE id v_copied_id AND request_time p_cutoff_time ORDER BY id LIMIT p_batch_size; SET v_rows ROW_COUNT(); SET v_copied_id v_copied_id p_batch_size; END WHILE; END$$ DELIMITER ;调用方式CALL sp_archive_batch(2023-01-01 00:00:00, 50000);这个存储过程的核心逻辑是每次取源表主键 ID 大于某个偏移量的前 5 万行插入归档表。源表在 ID 上有主键索引所以分批扫描是走索引的效率可控。因为v_copied_id每次增加 batch 大小即使源表 ID 有空洞最终复制进度也不会漏行当复制行数不足 batch 时循环自然结束。如果你不想用存储过程也可以用 Python、Go 或 Shell 脚本实现同样的循环。关键点是别一次性把上亿行塞进一条 INSERT避免归档表所在实例的内存和临时空间被打满。5.3 迁移后的校验与源表清理数据搬完并不意味着结束我做校验时会执行三组 SQLSELECT COUNT(*) FROM access_log_source; SELECT COUNT(*) FROM access_log_archive; SELECT MIN(request_time), MAX(request_time) FROM access_log_archive;COUNT(*)在 ARCHIVE 表上是全扫描执行较慢要预留时间。如果两边行数不一致需要从源表重新补数。但注意因为 ARCHIVE 没有唯一约束重跑同一批数据可能产生重复行所以在校验前就要保证源表在归档时间段内不再写入或者写入的数据时间戳大于p_cutoff_time这样重跑不会污染已归档部分。校验通过后开始分批删除源表旧数据。我建议一次删除 5 万到 10 万行然后停顿几秒避免源表锁和主从延迟被拉爆DELETE FROM access_log_source WHERE id BETWEEN 1 AND 100000;继续删下一段时可以动态向后推进也可以写一个 WHILE 循环脚本。整体思路是先复制再校验最后慢慢清源表。只要把顺序定死出问题也能随时恢复。6. 我踩过的 ARCHIVE 的坑以及什么时候该换别的方案6.1 坑一把自增 ID 当主键用后面查数据时才傻眼我刚上手 ARCHIVE 时就想当然地复制了 InnoDB 的建表习惯给归档表加了id BIGINT NOT NULL AUTO_INCREMENT然后在应用层把 id 当作业务主键。结果某次数据核对时发现表里有两条同样的 id因为 AGAINARCHIVE 不强制唯一性。这个坑特别隐蔽因为表没有报错业务还是在正常走。后来我改了策略如果是日志流水直接不关心 ID 唯一性用业务时间加流水号做标识如果必须保留唯一 ID就由上游发号器显式传入禁止依赖数据库自增。6.2 坑二用 mysqldump 备份 ARCHIVE 表慢到怀疑人生ARCHIVE 表没有索引mysqldump 默认逻辑又是逐表全扫导出。一张 1 亿行的 ARCHIVE 表备份时直接把备份窗口拖到了几个小时。吃过这个亏之后我给归档表单独设立了备份策略不再依赖常规 mysqldump 全量备份而是把 ARCHIVE 表放在独立实例或独立逻辑库。备份方式改为物理文件备份或使用专门工具做流式导出。如果必须用 mysqldump加上--quick --skip-lock-tables --no-create-info同时错开业务高峰。核心思想是归档表的数据量级别和普通业务表完全不同不能用同一套备份链路。6.3 坑三在 ARCHIVE 表上做 DELETE成本比想象中高官方文档里提到 ARCHIVE 支持 DELETE 和 REPLACE但实际用下来DELETE 并不会因为你说删一行就很快。由于没有索引定位行需要全表扫描和解压删除条件如果是非尾部数据代价非常高。我后来定的规矩是ARCHIVE 表一旦写入就默认不删不改。真要做数据清理直接按月 DROP TABLE或者新建一张表再把需要保留的数据导过去。6.4 ARCHIVE 与几个替代方案的对比如果你看完上面这些限制发现 ARCHIVE 不够用我建议按这个表格重新选型方案索引事务UPDATE压缩率适合场景InnoDB 普通表支持支持支持低在线交易、主业务表InnoDB 压缩行格式支持支持支持中低读写都有的较大表ARCHIVE不支持不支持不支持高冷日志、流水归档MyISAM myisampack支持不支持压缩后只读高只读归档、报表导出CSV不支持不支持不支持无压缩外部系统交换数据如果历史数据还需要被在线服务点查我宁可把它放回 InnoDB只是到一定时间后清理一下或者用 MySQL 8.0 的不可见索引、数据字典机制来减少空间浪费。如果要做比较重的离线分析那更建议直接把这些数据同步到 ClickHouse 之类的列式存储别在 MySQL 里硬扛。7. 最后想留下的几条实用建议说真的ARCHIVE 引擎不是新技术MySQL 4.1 时代就有了但很多人一直不敢用无非是怕它功能受限。实际上只要把它定位成“冷数据存储层”而不是“行数据库”它就能发挥非常大的价值。我的习惯是所有超过三个月、最多半年的流水数据只要确认不再修改就统一迁到按月的 ARCHIVE 表里。迁完后源表瘦身明显主从同步压力减轻备份时间也从五小时缩到四十分钟。偶尔需要排查问题时拿那条记录对应的月份去查对应 ARCHIVE 表虽然慢但完全忍得了。最后再分享一个可以让你少踩坑的小技巧ARCHIVE 表建立后一定要在监控里单独画一张表空间趋势图并且预留出足够的数据目录空间。因为压缩能帮你省空间但并不会阻止磁盘被写满一旦某个月的日志量暴增你还是得提前扩容。先想清楚查询路由再谈压缩收益先控制好单表大小再享受低成本存储。顺序反了ARCHIVE 就会变成又慢又难维护的“存档垃圾场”。
