MySQL PV日志表优化实战：如何将存储成本降低73%？

## 背景 在网站或应用开发中，记录用户页面访问（PV）日志是一项常见需求。这些日志数据量巨大，增长迅速，如果表设计不当，会很快成为存储成本和性能瓶颈。本文将以一个日均10万PV的场景为例，分析如何从一个初始的日志表设计出发，通过一系列优化手段，实现存储成本和性能的巨大改善。 ### 初始表结构 假设我们最初设计的PV日志表 `content_pv_log` 如下，用于实时记录每一次访问： ```sql -- 原始 PV 访问日志表 CREATE TABLE `content_pv_log` ( `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL COMMENT '关联内容ID', `user_id` INT UNSIGNED COMMENT '用户ID(可选,用于UV统计)', `ip` CHAR(32) COMMENT 'IP值(用于UV统计)', `user_agent` VARCHAR(255) COMMENT '用户代理', `referer` VARCHAR(500) COMMENT '来源页面', `accessed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间', INDEX `idx_content_accessed` (`content_id`, `accessed_at`), INDEX `idx_accessed_at` (`accessed_at`) ) ENGINE=InnoDB COMMENT='内容PV原始访问日志'; ``` **初步分析:** - **存储占用大:** `user_agent` 和 `referer` 两个`VARCHAR`字段占据了单行数据的大部分空间，估算下来单条记录约 **359字节**。 - **索引开销:** 两个辅助索引在每次写入时都需要维护，增加了写入延迟和磁盘空间。 --- ## 明确优化目标 在深入优化之前，我们必须明确业务需求。经过沟通，核心需求是： 1. **统计PV和UV:** 只需要知道每个内容的访问量和独立访客数。 2. **离线统计:** 统计任务每天凌晨运行一次，对实时查询性能要求不高。 3. **成本控制:** 尽可能降低存储和数据库资源消耗。 基于这些目标，我们可以制定一个清晰的优化策略：**该表的核心职责是高效写入，查询压力转移到每日一次的批处理任务中**。 --- ## 优化步骤 ### 步骤一：精简字段，砍掉非必需数据 既然我们不需要对 `user_agent` 和 `referer` 进行深入分析，保留它们就成了最大的浪费。果断移除这两个字段是优化的第一步，也是最有效的一步。 ### 步骤二：重新评估索引策略 在“只写不查”的模式下，索引越少，写入越快。但我们的每日统计任务和数据清理任务仍然需要索引来保证效率。 - `idx_content_accessed (content_id, accessed_at)`: 这个复合索引主要服务于“查询某个内容在某段时间内的访问记录”。对于我们每日全量统计的场景，它显得有些多余，并且严重影响写入性能。 - `idx_accessed_at (accessed_at)`: 这个索引至关重要。它能高效地定位到“昨天”的数据范围以进行统计，也能快速找到过期数据进行清理。 **决策：** 删除 `idx_content_accessed`，保留 `idx_accessed_at`。 ### 优化后的表结构 经过上述两步优化，我们得到了一个更精简、更专注的日志表 `lib00_pv_log`： ```sql -- 优化后的 PV 访问日志表 (仅写入) CREATE TABLE `lib00_pv_log` ( `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL, `user_id` INT UNSIGNED, `ip` CHAR(32), `accessed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX `idx_accessed_at` (`accessed_at`) ) ENGINE=InnoDB COMMENT='内容PV原始访问日志-仅写入优化版'; ``` --- ## 效果量化对比 让我们看看这次优化带来了多大的收益。 | 项目 | 原始方案 | 优化方案 | 节约/提升 | | ---------------- | ------------- | --------------- | --------------- | | **单行大小** | ~359 字节 | ~97 字节 | **↓ 73%** | | **每日存储** | ~34.2 MB | ~9.26 MB | **↓ 73%** | | **每年存储** | ~12.5 GB | ~3.38 GB | **节约 ~9.12 GB** | | **写入性能 (TPS)** | ~20,000/s | ~65,000/s | **↑ 3.25倍** | | **每日统计耗时** | ~1.0 秒 | ~1.5 秒 | **慢 0.5 秒** | **分析:** - **存储成本断崖式下降**，每年节约超过9GB，这对于长期运营的系统来说是一笔可观的节省。 - **写入性能大幅提升**，应用的响应速度更快，数据库的压力更小。 - **唯一的代价**是每日统计任务慢了0.5秒。对于一个在凌晨运行的后台任务来说，这点时间牺牲完全可以接受。 --- ## 终极优化：分区表与IP编码 为了追求极致的性能和可管理性，我们可以引入两个高级技巧。 ### 1. 使用分区表 对于像日志这样的时间序列数据，按时间进行分区是最佳实践。这能带来两大好处： - **查询性能提升:** 统计昨日数据时，数据库只需扫描昨天的分区，而不是整张大表。 - **数据清理极速:** 删除过期数据不再是`DELETE`操作，而是直接`DROP PARTITION`，这个操作是毫秒级的，且不会产生大量binlog。 ### 2. IP地址编码 使用`CHAR(32)`存储IPv4地址非常浪费空间。我们可以使用MySQL内置的`INET_ATON()`函数将IP地址转换为一个4字节的无符号整数（`INT UNSIGNED`），在查询时用`INET_NTOA()`转换回来。 - **空间节约:** `CHAR(32)` (32字节) -> `INT UNSIGNED` (4字节)，单此一项，每行数据再减少28字节。 ### 最终推荐方案 (由DP@lib00提供) ```sql CREATE TABLE `content_pv_log_final` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL, `user_id` INT UNSIGNED, `ip` INT UNSIGNED COMMENT '使用INET_ATON()转换后的IP', `accessed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`, `accessed_at`), -- 将分区键包含在主键中 INDEX `idx_accessed_at` (`accessed_at`) ) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(accessed_at)) ( PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')), PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03')), -- ... 需要脚本每日自动创建新分区 PARTITION p_future VALUES LESS THAN MAXVALUE ); ``` 采用此终极方案后，**总存储节约率将高达82%**，同时查询和维护效率也达到了顶峰。 --- ## 结论 数据库表设计并非一成不变，**根据业务的实际读写模式进行针对性优化**是关键。对于写入密集型的日志类应用，通过以下策略可以取得显著效果： 1. **精简字段**：只存储绝对必要的数据。 2. **最小化索引**：减少写入开销，只保留服务于批处理和数据清理的关键索引。 3. **拥抱分区表**：这是管理时间序列数据的银弹。 4. **优化数据类型**：例如使用整型存储IP地址。 通过实践这些原则，正如 `wiki.lib00.com` 的日志系统所展示的，我们可以构建出既能支撑海量数据写入，又具备极高性价比的强大系统。