百万行访问日志表的瘦身改造：Nginx 日志落盘、按天分区与预聚合，让统计查询从秒级回到毫秒级

  1.  Home
  2. Text  
    3. Share to：

很多站长给站点加访问统计的方式是：在 Nginx 里记一条日志，然后让 PHP 在每次请求结束时再往 MySQL 写一行。刚开始几千 PV 一天，一切正常；等到日 PV 过万、表里躺了几百万行，问题就一个接一个地来了——后台统计页打开要转十秒、凌晨的汇总脚本把数据库 CPU 打满、磁盘两个月涨了十几个 GB、某天突然发现统计接口在拖慢整站。

这篇讲清楚一件事：访问日志这种「只写、按时间段聚合查询、极少单条读取」的数据，用 InnoDB 单表存是不合适的，以及该怎么改。给出的方案适用于日 PV 几万到几十万的个人站规模，不需要引入 ClickHouse 这种重型方案。

先说结论：核心是三条——写入异步化、存储按日期分区、查询只查聚合结果。三条都做到，百万行级别的统计表可以稳定跑在 1 核 1G 的机器上。

一、先算一笔账：为什么单表会垮

假设一篇文章一条访问记录，每行存 URL 哈希、IP、UA、Referer、时间、地区，平均 300 字节。日 PV 一万，一天约 3MB，一年约 1.1GB；如果算上索引（时间索引、URL 索引），实际磁盘占用会到 2.5 倍左右，也就是年增 2~3GB。这个体积本身不可怕，可怕的是它的行数：一年 365 万行。

在这个量级上，三类操作会开始变慢：

  • 范围统计：WHERE created_at BETWEEN ... 加 GROUP BY，即使有时间索引，也要回表取大量行，随机 I/O 一上来就慢。
  • 去重统计：算独立访客要 COUNT(DISTINCT ip)，这是纯内存与临时表操作，行数越多越慢，而且几乎没有索引能帮上忙。
  • 分页查询：后台翻到第 100 页时的 LIMIT 100000, 20，MySQL 要真的扫过前十万行再丢掉，越翻越慢。

更隐蔽的成本在写入侧：每来一个请求就 INSERT 一次，等于每个请求都多一次事务提交、一次 fsync（取决于 innodb_flush_log_at_trx_commit），而且会和前台的文章查询抢同一把 InnoDB 的锁与 buffer pool。日 PV 一万的话，一天一万次额外写事务，对低配 VPS 是很实在的负担。

先量一下，别凭感觉优化

动手改之前先确认瓶颈真的在统计表上。下面几条查询能给出准确判断。

-- 1) 表有多大、行数多少（InnoDB 的行数是估算值，但量级够用）
SELECT table_name,
       table_rows,
       ROUND(data_length/1024/1024, 1)  AS data_mb,
       ROUND(index_length/1024/1024, 1) AS index_mb
FROM information_schema.tables
WHERE table_schema = '站点库' AND table_name LIKE '%stat%';

-- 2) 是不是有大量全表扫描/临时表落盘
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

-- 3) 统计表上到底有哪些索引（索引太多会拖慢写入）
SHOW INDEX FROM 站点库.visit_log;

如果 sys.statement_analysis 里排在前面的确是统计相关的 SELECT，那么优化对象就是它。如果前面是文章列表的查询，那你该先去看那部分——这一点很重要，很多人一上来就优化统计表，结果真正的瓶颈在主题的 SELECT *。

二、第一步：把写入从 PHP 挪到 Nginx 日志

最省事的改动是不再用 PHP 记访问。Nginx 本来就会把每个请求写进 access.log，这是零额外开销的（内核层面的追加写）。让 PHP 只负责解析日志、批量入库，请求路径上就少了一次数据库交互。

关键是把需要的字段在日志格式里配齐，不然后期想统计地区、UA 就没了。

# /etc/nginx/conf.d/log-format.conf
# 在 http 块里定义，注意字段顺序要和后面的解析逻辑一致
log_format stat '$time_iso8601\t$status\t$request_method\t$host\t$request_uri\t'
                '$remote_addr\t$http_user_agent\t$http_referer\t'
                '$request_time\t$body_bytes_sent\t$http_x_forwarded_for';

# 在 server 块里单独开一个统计日志，与普通 access_log 分开
access_log /var/log/nginx/stat.log stat buffer=64k flush=10s;

几个细节值得说清楚：

  • buffer=64k flush=10s：让 Nginx 先在内存里攒够再落盘，减少 write 系统调用。代价是日志最多延迟 10 秒出现，对统计完全无所谓。
  • 用 $time_iso8601 而不是默认的 [$time_local]，因为它带时区偏移，解析成时间戳不容易出错。
  • 用 $request_uri 而不是 $uri，前者保留查询串。但要注意，查询串里可能有 ?utm_source= 这类参数，会让「同一篇文章」变成几十个不同的 URL，统计时必须先剥离。
  • 只记 HTML 页面请求：可以在 server 块里用 location ~ \.(css|js|jpg|png|woff2)$ { access_log off; } 把静态资源排除，日志量能降一个数量级。

如果站点前面套了 CDN，$remote_addr 会是 CDN 节点 IP，真实访客 IP 在 $http_x_forwarded_for 里。这一点必须确认，否则独立访客数会变成一个固定的小数字（等于 CDN 节点数）。验证方法很简单：

# 看统计日志的第一列 IP 是不是集中在少数几个（CDN 节点特征）
awk -F'\t' '{print $6}' /var/log/nginx/stat.log | sort | uniq -c | sort -rn | head -5

三、第二步：表结构与分区设计

存储层的核心决策是按天分区。分区带来的最大好处不是查询变快，而是清理历史数据可以秒级完成——ALTER TABLE ... DROP PARTITION 只是改元数据，不需要 DELETE 扫描，也不会产生大量 undo 与 binlog。

但分区有个硬约束必须提前知道：MySQL 分区表的主键与唯一键必须包含分区列。所以主键要写成 (id, visit_date)，不能用自增 id 单独做主键。这是很多人第一天就卡住的地方。

CREATE TABLE visit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visit_date  DATE            NOT NULL,          -- 分区列，必须进主键
  created_at  DATETIME        NOT NULL,
  url_hash    BINARY(16)      NOT NULL,          -- 存 URL 的 MD5 二进制，省空间
  url         VARCHAR(512)    NOT NULL,          -- 原样保留一份，便于排查
  visitor     BINARY(16)      NOT NULL,          -- IP+UA 的哈希，用来做 UV 去重
  status      SMALLINT UNSIGNED NOT NULL,
  ref_host    VARCHAR(128)    NOT NULL DEFAULT '',
  req_time    DECIMAL(6,3)    NOT NULL DEFAULT 0,
  PRIMARY KEY (id, visit_date),
  KEY idx_date_url (visit_date, url_hash),
  KEY idx_date_visitor (visit_date, visitor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(visit_date) (
  PARTITION p20261010 VALUES LESS THAN ('2026-10-11'),
  PARTITION p20261011 VALUES LESS THAN ('2026-10-12'),
  PARTITION pmax      VALUES LESS THAN (MAXVALUE)
);

几个字段设计的取舍：

  • url_hash 用 BINARY(16) 而不是 VARCHAR：16 字节固定长度，比 512 字节的 VARCHAR 索引小三十倍，GROUP BY 时的临时表也小得多。MD5 有碰撞风险，但对「统计哪个页面访问最多」这种用途，碰撞概率可以忽略。真要精确，可以用 UNHEX(MD5(url))，这就是 16 字节。
  • visitor 用 IP+UA 哈希：比直接存 IP 省空间，而且天然做了「同 IP 不同设备算两人」的区分。注意这是隐私相关数据，不存原 IP 也是好事，但如果你需要按地区统计，就必须额外存一份地区代码，不能在事后反推。
  • ref_host 只存来源域名，不存完整 URL。完整 Referer 长度不可控，会把行撑大，而且统计上你真正关心的只是「从哪个站来的」。
  • status 用 SMALLINT：不要用 VARCHAR，也不要用 TINYINT（状态码最大 599，TINYINT 存不下）。

分区维护：提前建，别等 MAXVALUE

上面留了一个 pmax 兜底分区，但所有数据都会堆进 pmax，分区就失去意义了。正确做法是提前建好未来 7~30 天的分区，用一个每日任务维护。

-- 每天跑一次：把 MAXVALUE 分区拆出明天的分区
-- 注意 ORGANIZE PARTITION 会重建 pmax，表越大越慢，所以要保持 pmax 是空的
ALTER TABLE visit_log REORGANIZE PARTITION pmax INTO (
  PARTITION p20261019 VALUES LESS THAN ('2026-10-20'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

清理历史数据就是一句话：

-- 删除 90 天前的分区（秒级完成，不产生大量 binlog）
ALTER TABLE visit_log DROP PARTITION p20260720;
-- 确认分区列表
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.partitions
WHERE table_schema='站点库' AND table_name='visit_log'
ORDER BY PARTITION_ORDINAL_POSITION;

这里有个坑：如果 pmax 里已经攒了很多数据，REORGANIZE PARTITION pmax 会把这些数据全部搬到新分区里，可能锁表很久。所以从第一天起就要保证 pmax 为空，宁可多建几个空分区。

四、第三步：批量导入，而不是逐行插入

解析日志并入库的脚本要用批量 INSERT，一次几百到一千行。逐行插入的话，一万条日志就是一万次事务，比原来的 PHP 写法还慢。

#!/usr/bin/env python3
# /opt/statlog/import.py —— 解析 Nginx stat.log 并批量入库
# 设计要点：记录已读偏移量、批量插入、失败可重入
import hashlib
import os
import pymysql
import sys

LOG = '/var/log/nginx/stat.log'
POS = '/var/lib/statlog/offset'
BATCH = 500
DSN = dict(host='127.0.0.1', user='statwriter', password=os.environ['STAT_PW'],
           database=os.environ['STAT_DB'], charset='utf8mb4', autocommit=False)


def visitor_hash(ip, ua):
    # 归一化 UA：去掉版本号段，减少同浏览器不同版本的碎片
    ua_norm = ' '.join(ua.split()[:6])
    return hashlib.md5(f'{ip}|{ua_norm}'.encode()).digest()


def url_bits(uri):
    # 剥离查询串与末尾斜杠，避免 utm 参数把同一篇文章拆成几十条
    path = uri.split('?', 1)[0]
    if len(path) > 1 and path.endswith('/'):
        path = path[:-1]
    if len(path) > 512:
        path = path[:512]
    return hashlib.md5(path.encode()).digest(), path


def main():
    offset = int(open(POS).read()) if os.path.exists(POS) else 0
    size = os.path.getsize(LOG)
    # 日志被 logrotate 截断/轮转时，偏移量比文件还大，重置为 0
    if offset > size:
        offset = 0
    rows, consumed = [], offset

    with open(LOG, 'r', encoding='utf-8', errors='replace') as f:
        f.seek(offset)
        for line in f:
            consumed = f.tell()
            parts = line.rstrip('\n').split('\t')
            if len(parts) != 11:
                continue                     # 格式不符直接跳过，不要中断整批
            ts, status, method, host, uri, ip, ua, ref, rtime, _bs, xff = parts
            if method != 'GET' or int(status) >= 400:
                continue                     # 只统计成功的页面浏览
            real_ip = (xff.split(',')[0].strip() or ip) if xff else ip
            uh, path = url_bits(uri)
            rows.append((ts[:10], ts.replace('T', ' ')[:19], uh, path,
                         visitor_hash(real_ip, ua), int(status),
                         ref.split('/')[2][:128] if '://' in ref else '',
                         float(rtime)))
            if len(rows) >= BATCH:
                flush(rows)
                rows = []
                open(POS, 'w').write(str(consumed))
    if rows:
        flush(rows)
    open(POS, 'w').write(str(consumed))


def flush(rows):
    sql = ('INSERT INTO visit_log '
           '(visit_date, created_at, url_hash, url, visitor, status, ref_host, req_time) '
           'VALUES (%s,%s,%s,%s,%s,%s,%s,%s)')
    conn = pymysql.connect(**DSN)
    try:
        with conn.cursor() as cur:
            cur.executemany(sql, rows)
        conn.commit()
    finally:
        conn.close()


if __name__ == '__main__':
    sys.exit(main())

几个必须处理的工程问题，缺一个都会出事：

  • 偏移量要在每批提交之后再写入。上面的写法在 flush 之后才更新 POS，如果中途崩溃，最多重复导入最后一批。因此插入语句最好幂等，或者接受少量重复（统计场景完全可接受）。
  • 日志轮转检测：if offset > size: offset = 0 这行不能省。logrotate 之后文件变小，直接从大偏移量读会读到空的，表面看是「统计突然没数据了」。
  • 跳过 4xx/5xx 与非 GET。爬虫的 404 扫描、HEAD 预检、POST 提交都会污染统计，把文章的访问量算虚高。
  • URL 归一化：剥离查询串这一步最容易被忽略。不剥离的话，一个带 UTM 的分享链接会让同一篇文章出现几十条记录，统计出来的「最热文章」全是分享链接的长尾。

五、第四步：预聚合，让后台查询变成毫秒级

即使有了分区，后台统计页每次打开都实时 GROUP BY 几百万行，体验依然很差。解决办法是把聚合结果提前算好，写进一张小表，后台只查小表。

聚合的粒度我一般用「按天 × 按页面」和「按天 × 按来源域名」两张表，加起来行数是页面数 × 天数，一年也就几十万行，而且行很小。

CREATE TABLE stat_daily_page (
  stat_date  DATE         NOT NULL,
  url_hash   BINARY(16)   NOT NULL,
  url        VARCHAR(512) NOT NULL,
  pv         INT UNSIGNED NOT NULL DEFAULT 0,
  uv         INT UNSIGNED NOT NULL DEFAULT 0,
  avg_rtime  DECIMAL(6,3) NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, url_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE stat_daily_ref (
  stat_date  DATE         NOT NULL,
  ref_host   VARCHAR(128) NOT NULL,
  pv         INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, ref_host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

聚合用的 SQL 有一个必须注意的地方：UV 不能跨天相加。同一个人今天和明天都来了，两天各自 UV=1，加起来 UV=2，但真实的「两日独立访客」是 1。所以周报、月报的 UV 必须用明细表重新算，不能用日表求和。这一点如果搞错，报表数字会系统性虚高。

-- 日聚合：只处理「昨天」，可重复执行（幂等）
INSERT INTO stat_daily_page (stat_date, url_hash, url, pv, uv, avg_rtime)
SELECT visit_date, url_hash,
       MIN(url),
       COUNT(*)                       AS pv,
       COUNT(DISTINCT visitor)        AS uv,
       ROUND(AVG(req_time), 3)
FROM visit_log
WHERE visit_date = CURDATE() - INTERVAL 1 DAY
GROUP BY visit_date, url_hash
ON DUPLICATE KEY UPDATE
  pv = VALUES(pv), uv = VALUES(uv), avg_rtime = VALUES(avg_rtime);

-- 区间 UV 必须回明细表算，不能对日表 SUM
SELECT COUNT(DISTINCT visitor) FROM visit_log
WHERE visit_date BETWEEN '2026-10-01' AND '2026-10-07';

-- 后台首页：最近 7 天总 PV，查小表，毫秒级
SELECT SUM(pv) FROM stat_daily_page
WHERE stat_date >= CURDATE() - INTERVAL 7 DAY;

ON DUPLICATE KEY UPDATE 让聚合脚本可以重复跑——这对补数据非常关键。比如某天导入脚本挂了，你补完明细之后直接重跑聚合，不会产生重复数据。

六、第五步：给统计一套独立的资源边界

统计是「锦上添花」的功能，绝不该影响主站。我在低配机器上的做法是给统计相关操作设三道限：

  1. 独立数据库账号，只有 visit_log 与 stat_daily_* 的权限，避免脚本写错库把文章表搞坏。
  2. 导入与聚合任务用 ionice / nice 降低优先级，让它们在磁盘繁忙时自动退让。
  3. 限制单次执行时间，超过就中断，防止一个慢查询把连接池占满。
# systemd service 里加资源限制（比 nice 更硬）
# /etc/systemd/system/statlog-import.service
[Service]
Type=oneshot
EnvironmentFile=/etc/statlog/env
ExecStart=/usr/bin/python3 /opt/statlog/import.py
IOSchedulingClass=idle
IOSchedulingPriority=7
Nice=15
CPUQuota=50%
# 数据库侧再加一层保险：给统计账号设查询超时
# SET SESSION max_execution_time = 20000;  -- 20 秒
PrivateTmp=true

把 SET SESSION max_execution_time 放在统计脚本连接建立之后执行，是防止「一个慢查询拖垮整站」最性价比的一条措施。MySQL 5.7.8 之后支持，单位是毫秒。

七、还要不要考虑 ClickHouse？

结论：日 PV 十万以下不需要。ClickHouse 在列式压缩与聚合性能上确实强，但它带来的是一个新的服务、一套新的运维、一次数据搬迁，还有至少 1~2GB 内存的常驻。对一个跑在 1C1G 上的个人站，这不是优化而是负担。

什么时候该考虑迁移？我给三个判断标准，满足任意两条再动手：

  • 日 PV 稳定超过 20 万，MySQL 聚合脚本每次跑超过 5 分钟；
  • 需要按任意维度组合（地区 + UA + 来源 + 页面）实时下钻，预聚合表已经无法覆盖；
  • 需要保留一年以上明细，且单表超过 5000 万行，分区数上百导致元数据操作变慢。

在那之前，从「PHP 逐条写入」到「日志 + 批量 + 分区 + 预聚合」这一步改造，收益最大、风险最低，通常是十倍以上的查询性能提升。

八、改造后的实测对比

说一下我最近一次改造的实测数据，给一个可参考的量级。站点是 1 核 2G 的 VPS，改造前统计表 380 万行、约 1.6GB（含索引），日 PV 约 1.2 万。

指标改造前改造后
后台统计页首屏6.8 秒0.11 秒
每日聚合任务耗时4 分 20 秒23 秒
请求路径上的 DB 写入每请求 1 次0
清理 90 天前数据DELETE 8 分钟DROP PARTITION 0.2 秒
首屏 TTFB（首页）420ms180ms

TTFB 那一行是意外收获：原来自增写入的事务提交会和前台查询抢 buffer pool，挪走之后前台反而快了不少。这也说明一件事——统计功能的成本不只是存储，还包括它对主站事务路径的干扰。

九、落地顺序与验收

这套改造不要一次全上，按下面的顺序做，每一步都可独立验证、可回退：

  1. 先并行记录：Nginx 开统计日志，同时保留原来的 PHP 写入。跑三天，对比两边的 PV 是否一致。不一致说明日志字段或过滤条件有问题，先别切换。
  2. 建新表并导历史：从旧表迁移最近 90 天数据到分区表，注意主键要带 visit_date。
  3. 切换查询：后台统计页改为查预聚合表，先跑聚合任务把历史数据补上。
  4. 关闭 PHP 写入，观察一天，确认 PV 曲线没有断崖。
  5. 加分区维护任务：每天建未来分区、删过期分区、跑聚合。三个任务用一个脚本串起来，任意一步失败都要告警。

验收就看三个数：后台统计页响应时间、聚合任务耗时、以及 PV 与搜索引擎给出的数据是否在一个合理区间。第三条尤其重要，切换统计实现时最容易出现「数字对不上」，而原因往往是过滤条件变了（比如把 4xx 排除掉了，PV 自然下降 5%~15%，这是正常的）。

最后一句经验：统计系统的第一原则是「不要影响主站」。任何一个统计功能，只要能拖慢首页 100ms，它就不合格。把写入挪出请求路径、把查询挪到预聚合表、把资源占用套上 CPU 与 IO 的笼子，这三件事做完，日 PV 几十万的个人站也能跑得很轻松。

Last modification：October 18th, 2026 at 08:28 pm
© 允许规范转载

相关文章

Leave a Comment