26.4 日志系统：大数据量存储、查询、归档方案

几乎所有线上系统都会产生日志——操作日志、接口调用日志、业务流水日志、错误日志……这些数据有三座绕不开的大山：写入量大、存储成本高、查询需求复杂但时效性递减。用 MySQL 承接日志系统，不是因为它最强（在某些场景下 Elasticsearch 或 ClickHouse 更合适），而是因为团队熟悉、生态完善，并且能与其他业务表在同一个事务中联动。这里讨论的，是在 MySQL 中的务实落地方法。

日志表的核心特征与设计约束

设计日志表前，先确认这类数据的特点：

写多读少，写入压力巨大：每天可能产生数百万甚至上亿条记录，写吞吐是首要考量。
查多按时间范围，偶尔按关键字：典型查询是“某时间段内的日志”，偶尔需要检索某个用户 ID、订单号或关键词。
数据价值随时间锐减：当天的日志可能频繁访问，七天前的几乎没人看，三个月前的只需留存备查。
不允许更新和删除：日志天生是只追加（append-only）的，不必预留修改余地。
允许少量丢失：相比交易数据，日志对绝对一致性要求稍低，偶尔丢失几条通常可接受。

依据这些特点，可以定下几条硬规矩：

只建必要索引，甚至不建索引，把写入速度放在第一位。
按日期或小时进行范围分区，这是日志管理的命脉。
不做外键，不设触发器，结构极简化。
提前规划归档与清理策略，避免单表膨胀到性能失控。

表结构与分区设计

假设我们需要存储 API 日志，每分钟几万条写入，保留最近 30 天热数据，超期数据归档或删除。

CREATE TABLE api_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    log_time DATETIME(3) NOT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    api_path VARCHAR(128) NOT NULL,
    method VARCHAR(8) NOT NULL,
    request_body TEXT,
    response_code SMALLINT UNSIGNED NOT NULL,
    duration_ms INT UNSIGNED NOT NULL,
    PRIMARY KEY (id, log_time),
    INDEX idx_log_time (log_time),
    INDEX idx_user_time (user_id, log_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p20250301 VALUES LESS THAN (TO_DAYS('2025-03-02')),
    PARTITION p20250302 VALUES LESS THAN (TO_DAYS('2025-03-03')),
    -- …可预先创建好未来一个月分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

几个关键决定：

联合主键 (id, log_time)：InnoDB 要求分区键必须是主键的一部分。用自增 ID 配合 log_time 既能保证唯一性，又不影响分区剪裁。
log_time 索引有冗余但必要：当查询不带上 ID 时，可以用时间索引快速定位分区并做排序。
针对高频查询字段建复合索引：例如 (user_id, log_time)，避免回表。
TEXT 字段放在最后，而且设定足够长度。如果请求体过大，建议只存摘要或存到对象存储，MySQL 中仅保留路径。

写入优化：

批量插入，应用层攒够一秒钟或 1000 条再提交一批，减少事务开销。
采用异步落盘，innodb_flush_log_at_trx_commit=2 可在日志库上适度放松，提高写入速度（允许宕机丢 1 秒数据）。
如果日志可以容忍偶尔丢失，甚至可以临时关闭该连接的 binlog（SET sql_log_bin=0），但需确保数据恢复流程不受影响。

分区管理：自动创建与删除

日志表必须有一套自动化的分区维护脚本，否则迟早出事。基本策略是：

提前创建未来 N 天分区（如每天凌晨执行一次）：

ALTER TABLE api_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p20250303 VALUES LESS THAN (TO_DAYS('2025-03-04')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

p_future 起缓冲区作用，通过 REORGANIZE 切出新分区，不会锁表（5.7+ 大多在线执行）。

删除过期分区（超过保留期限）：

ALTER TABLE api_logs DROP PARTITION p20250201;

注意：使用 DROP PARTITION 会立即释放存储空间，比 DELETE 快几百倍且无事务日志膨胀。

老旧分区归档到低成本存储：

在删除前，可以先用 SELECT INTO OUTFILE 或 mysqldump --where 将分区数据导出为 CSV 或 SQL 文件，然后压缩上传到对象存储或冷备服务器。同时可以将归档表放在另一台只读 MySQL 实例上，查询时切数据源。

查询优化实战

日志查询常常是“大海捞针”——从几十亿条中捞出几百条。用好分区剪裁是第一道防线：

-- 好：指定了时间范围，优化器只扫描 p20250301 和 p20250302 两个分区
SELECT * FROM api_logs
WHERE log_time BETWEEN '2025-03-01 10:00:00' AND '2025-03-02 10:00:00'
  AND user_id = 10086;

-- 坏：没有时间条件，全部分区都要扫
SELECT * FROM api_logs WHERE user_id = 10086;

如果经常需要按关键字搜索（如某个 API 路径、某个 trace_id），单靠 MySQL 的 LIKE '%keyword%' 会很吃力。此时提倡：

前置 ES 或 ClickHouse：用 Binlog 解析工具（如 Canal）将日志同步到搜索引擎，提供全文检索能力，MySQL 仅作原始存档。
在应用层保有多级缓存：热点时间段的数据缓存在 Redis，实时查询走 MySQL 分区。

若非要在 MySQL 里硬扛全文检索，可使用 FULLTEXT 索引（仅限 InnoDB 5.6+），但会严重拖慢写入，且中文分词需要 ngram 解析器（内置或第三方），维护成本高，不建议作为主力方案。

历史数据归档方案

归档不仅仅是把旧数据挪走，还要确保偶尔需要查的时候能找回。常见做法：

同实例迁移：将旧分区数据 INSERT INTO archive_table SELECT * FROM ...，然后 DROP PARTITION。缺点是归档表会越来越大，最终也需要清理。
跨实例同步：搭建一个专用归档库，配置主从复制但不归档（通过 replicate-wild-do-table 过滤）。线上表只保留指定天数，过期的以分区形式存在归档库上。
物理备份归档：利用 XtraBackup 备份后，将备份文件作为归档，必要时恢复后查询。适合极低频查询。
业务方自担责任：要求产品方明确日志查询频率，如果除近 7 天极少再查，可直接删除旧分区，只保留备份文件备查。

关键原则：归档动作必须脚本化、定时执行，并且要监控其是否成功——生产中出现因分区未删除导致磁盘写满的案例屡见不鲜。

总结：日志存 MySQL 的底线清单

必须使用分区表，按 RANGE 或 RANGE COLUMNS 分区，键为时间。
必须自动化分区创建和清理，不允许手动 DROP PARTITION 靠人记忆。
写入使用批量提交，考虑适当放宽持久性换取速度。
避免在日志大表上执行全表扫描类查询，指导产品/运维使用时间过滤。
超过保留期的数据坚决清理或归档，不要把 MySQL 当数据湖。
如果全文检索需求强烈，交由专门搜索引擎处理，MySQL 聚焦于顺序写入和范围扫描。

日志系统是典型的“基础设施类”表，它的设计思路与核心业务表迥异：极简、分区、自动化。把这些做好，即便每天数亿条日志，单机 MySQL 也能跑得稳稳当当，且能轻松维护。