ClickHouse 列式存储实战：个人站日志分析从 MySQL 迁移、MergeTree 建表与查询调优

Author： zz1984
发布时间：October 2, 2026
5 views
No comments
8389 字数
Categories：技术教程

为什么个人站长会用到 ClickHouse

大多数个人站长的数据栈是这样的：网站本身的业务数据放在 MySQL，访问日志交给 Nginx access.log，再配一个 GoAccess 或者 Umami 看流量。这套组合在日访问几千到几万的量级下完全够用。但只要你做过一次"按分钟粒度统计某类接口的 p95 响应时间、按国家维度看下载量、把最近半年的访问日志做漏斗分析"，就会立刻撞上 MySQL 的天花板：一张两千万行的日志表，随便一个 GROUP BY 都要跑十几秒，还经常把 Buffer Pool 冲得一塌糊涂，连累线上的读写。

ClickHouse 就是为这类"写多读少、一次写入成批、查询总是聚合"的列式数据库。它的强项不是事务，也不是高并发点查，而是把几十亿行数据在秒级内扫完并聚合出结果。本文不打算把 ClickHouse 当成一个泛泛的"大数据组件"来介绍，而是站在个人站长能实实在在落地的角度，讲清楚怎么在一台 2 核 4G 的 VPS 上把它装起来、怎么把 Nginx 日志灌进去、怎么写出不把自己坑死的查询。

列式存储到底快在哪里

要理解 ClickHouse 为什么快，先要理解行存和列存的区别。MySQL 的 InnoDB 按行存放，一行的所有字段在磁盘上是挨着的。当你执行 SELECT sum(bytes) FROM access_log WHERE status = 200 时，InnoDB 必须把每一行的所有列都读进内存，哪怕你只要 bytes 和 status 两列。数据量一大，无效 I/O 就成了主要开销。

ClickHouse 按列存放，每一列单独成文件。同样这条查询，它只需要读取 bytes 和 status 两个列文件，其他列碰都不碰。更关键的是，列式存储的压缩效率极高：同一列的数据类型一致、重复度高，配合 ClickHouse 默认的 LZ4 或者 ZSTD 压缩算法，日志类数据通常能压到原体积的 1/10 甚至更低。原本 50GB 的日志，落到 ClickHouse 里可能只有 4GB，读盘量自然大幅下降。

再加上两个杀手级设计：一是向量化执行，一次处理一批数据而不是一行一行循环，充分压榨 CPU 的流水线；二是稀疏主键索引加数据跳过索引（skip index），能在扫描前跳过大量无关的数据块。这三点叠加，才造就了那种"几十亿行数据秒级出结果"的观感。需要强调的是，ClickHouse 的 UPDATE 和 DELETE 是异步的 mutation，代价很高，所以它绝不适合放业务主表，只适合放"写完就基本不改"的日志和指标。

在单机 VPS 上安装与最小化配置

个人站没必要上集群，一台机器足够。官方提供了一键安装脚本，Debian/Ubuntu 下执行：

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 \
  --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo systemctl enable --now clickhouse-server

装完第一件事是搞清楚它默认只监听 127.0.0.1 的 9000（原生协议）和 8123（HTTP 接口）。这其实是好事——在一个公网 VPS 上，ClickHouse 裸奔是极其危险的，它的默认用户 default 早期版本甚至是空密码。我们要做的第一件事就是设置密码，并确保它永远不暴露公网：

# /etc/clickhouse-server/users.d/default-password.xml
<clickhouse>
  <users>
    <default>
      <password>你的强密码</password>
      <networks>
        <ip>127.0.0.1</ip>
        <ip>::1</ip>
      </networks>
    </default>
  </users>
</clickhouse>

<networks> 这一段是保命配置，它规定除了本机回环地址，任何来源都无法用这个账号登录。如果确实需要远程连接（比如本地用 DBeaver 连过去），正确做法是走 SSH 隧道，而不是把 8123 端口对公网开放：

ssh -L 8123:127.0.0.1:8123 root@你的VPS -N

然后本地连 http://localhost:8123 即可。记住一条铁律：日志分析库被拖走，等于把网站全部访问者 IP、UA、URL 参数拱手送人，合规风险和安全隐患都不小。

建表：MergeTree 家族与分区键的选择

ClickHouse 最常用的表引擎是 MergeTree 家族。建一张日志表的典型写法如下：

CREATE TABLE access_log (
    ts        DateTime,
    remote_ip String,
    method    LowCardinality(String),
    path      String,
    status    UInt16,
    bytes     UInt32,
    referer   String,
    ua        String
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (status, ts)
TTL ts + INTERVAL 180 DAY;

几个要点值得展开讲。首先是 PARTITION BY toYYYYMM(ts)，按月分区。分区不宜过细也不宜过粗：按天分区会产生大量小分区，元数据和管理开销上升；按年分区则单分区太大，过期清理粒度太粗。月分区是个稳妥的默认值。分区键一旦选定，后续想改必须重建表，所以建表前想清楚保留策略。

其次是 ORDER BY，它决定主键排序，也是查询加速的核心。原则是"把最常用于过滤的列放在前面"。日志场景里，status 是高频过滤条件，ts 是范围条件，所以 (status, ts) 是合理的。但如果你的查询几乎总是按 ts 范围扫、很少按 status 过滤，就该把 ts 放前面。排序键不匹配查询模式，是 ClickHouse 新手最常见的性能事故。

最后是 TTL ts + INTERVAL 180 DAY，这是列式库的自带生命周期管理，超过半年的分区会被自动合并删除，省得你自己写清理脚本。

把 Nginx 日志灌进去

输入侧有两种主流方案。第一种是直接让 Nginx 把日志写成 JSON，再由 Logstash/Fluent Bit 之类的采集器导入，链路清晰但组件多。第二种是个人站最省事的方式：用 ClickHouse 自带的 clickhouse-client 配合一个定时脚本，把 access.log 按行解析后批量插入。下面给出一个能直接跑的 Python 片段。

import re, datetime, subprocess, json
LINE = re.compile(
    r'(?P<ip>\S+) - - \[(?P<t>[^\]]+)\] '
    r'"(?P<m>\S+) (?P<p>\S+) [^"]*" '
    r'(?P<s>\d+) (?P<b>\d+) "(?P<r>[^"]*)" "(?P<ua>[^"]*)"')

def parse(path):
    rows = []
    for line in open(path, encoding="utf-8", errors="replace"):
        m = LINE.match(line)
        if not m:
            continue
        d = m.groupdict()
        dt = datetime.datetime.strptime(d["t"], "%d/%b/%Y:%H:%M:%S %z")
        rows.append([dt.strftime("%Y-%m-%d %H:%M:%S"),
                     d["ip"], d["m"], d["p"].replace("\t", ""),
                     int(d["s"]), int(d["b"]) if d["b"].isdigit() else 0,
                     d["r"], d["ua"]])
    return rows

rows = parse("/var/log/nginx/access.log")
with open("/tmp/bulk.tsv", "w", encoding="utf-8") as f:
    for r in rows:
        f.write("\t".join(map(str, r)) + "\n")

subprocess.run(
    "clickhouse-client --password xxx "
    "--query \"INSERT INTO access_log FORMAT TSV\" "
    "< /tmp/bulk.tsv", shell=True, check=True)

这里要注意几个坑。日志里的 UA 和 referer 可能含制表符或换行，插入前必须清洗，否则 TSV 解析会错位。IP 字段如果是 IPv6 或者带端口，字符串字段无所谓，但如果用了 IPv4 类型就必须严格合法。最稳妥的方式是启用 Nginx 的 JSON 日志格式，一行一条合法 JSON，解析难度骤降：

log_format json_log escape=json
  '{"ts":"$time_iso8601","ip":"$remote_addr",'
  '"method":"$request_method","path":"$request_uri",'
  '"status":$status,"bytes":$body_bytes_sent,'
  '"rt":$request_time,"ua":"$http_user_agent"}';
access_log /var/log/nginx/access.log json_log;

用 escape=json 后，UA 里的双引号会被自动转义，配合 JSONEachRow 格式一行就搞定。

写出不坑自己的查询

ClickHouse 的查询写法直接决定性能。第一个原则是：能用 count()、sum()、avg() 这类聚合函数解决，就不要去查明细行。列式库对聚合极度优化，对高并发明细点查反而不擅长。

SELECT toStartOfHour(ts) AS h,
       count() AS reqs,
       countIf(status >= 500) AS errs,
       round(sum(bytes)/1024/1024, 2) AS mb
FROM access_log
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY h
ORDER BY h;

第二个原则：善用 countIf、sumIf 这种条件聚合，一条 SQL 就能同时算出总量、错误量、平均耗时，避免多次扫表。第三个原则：不要把过滤条件下沉到 HAVING 里，能写在 WHERE 的务必写在 WHERE，让主键和分区裁剪生效。

要看某个查询到底扫了多少数据，用 EXPLAIN 和查询日志。ClickHouse 有个很实用的系统表 system.query_log，记录每条查询的耗时、扫描行数、内存峰值，是排查慢查询的金矿：

SELECT query_duration_ms, read_rows, formatReadableSize(memory_usage) AS mem,
       substring(query, 1, 120) AS q
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

内存与并发：小机器的调参

ClickHouse 号称很吃内存，但那主要是指大聚合和大 JOIN。个人站只要记住几条：单条查询的默认内存上限由 max_memory_usage 控制，默认约 10GB，在 4G 小机上必须调小，否则一条失控的查询能把整台机器打爆 OOM。可以在用户配置里限制：

<profiles>
  <default>
    <max_memory_usage>2000000000</max_memory_usage>
    <max_threads>2</max_threads>
    <max_execution_time>60</max_execution_time>
  </default>
</profiles>

max_threads 限制单查询并行度，避免小机器被一条查询吃满 CPU。max_execution_time 给查询加超时，防止失控。此外，如果条件允许，把 max_server_memory_usage 设为物理内存的 80% 左右，给系统留出余量。

与 MySQL 的分工边界

最后强调站长的核心问题：什么放 MySQL，什么放 ClickHouse。判断标准很简单——需要事务、需要频繁更新单行、需要唯一约束的，放 MySQL；只追加不修改、以聚合分析为主的，放 ClickHouse。典型的分工是：用户表、订单表、文章表在 MySQL，访问日志、接口指标、埋点事件进 ClickHouse。

两者之间用定时任务同步，别搞实时双写。个人站不需要那么高的时效性，每小时批量导入一次足够。还要留意的是一致性问题：MySQL 的一条记录更新了，ClickHouse 里不会自动跟着变，所以 ClickHouse 里的数据只应作为"分析快照"，不要反过来拿它当业务真相源。

备份、升级与运维要点

ClickHouse 的备份早期靠 ALTER TABLE ... FREEZE 加拷贝，现在有了内置的 BACKUP 命令，语法类似：

BACKUP TABLE access_log TO Disk('backups', 'access_log_2026.zip');

需要先在配置里定义一个名为 backups 的磁盘指向本地目录或 S3。对于个人站，把关键表定期 BACKUP 到本地盘，再用 rsync 拉到异地即可。升级时注意版本兼容性，大版本之间可能有配置项更名，升级前务必看官方的 release notes，并先在测试实例上验证。

日常运维还要盯住几件事：system.parts 里的分区和 part 数量，part 太多说明后台合并跟不上，可能意味着插入过于零碎；磁盘空间，日志库往往比预期涨得快，TTL 策略必须配好；以及 system.merges 里的合并进度。这些都是个人站长期稳定运行 ClickHouse 必须养成的巡检习惯。

总结一下：ClickHouse 不是把 MySQL 换掉，而是给个人站补上"日志与指标分析"这块 MySQL 不擅长的拼图。只要老老实实按月分区、按查询模式设计排序键、把内存和超时卡死、把端口收进 SSH 隧道，一台普通 VPS 也能撑起几十亿行日志的秒级分析，这是列式存储给站长带来的真实红利。

Last modification：October 2nd, 2026 at 08:24 pm

ClickHouse 列式存储实战：个人站日志分析从 MySQL 迁移、MergeTree 建表与查询调优

zz1984 • 2026 年 10 月 02 日

为什么个人站长会用到 ClickHouse

列式存储到底快在哪里

在单机 VPS 上安装与最小化配置

个人站没必要上集群，一台机器足够。官方提供了一键安装脚本，Debian/Ubuntu 下执行：

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 \
  --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo systemctl enable --now clickhouse-server

# /etc/clickhouse-server/users.d/default-password.xml
<clickhouse>
  <users>
    <default>
      <password>你的强密码</password>
      <networks>
        <ip>127.0.0.1</ip>
        <ip>::1</ip>
      </networks>
    </default>
  </users>
</clickhouse>

ssh -L 8123:127.0.0.1:8123 root@你的VPS -N

建表：MergeTree 家族与分区键的选择

ClickHouse 最常用的表引擎是 MergeTree 家族。建一张日志表的典型写法如下：

CREATE TABLE access_log (
    ts        DateTime,
    remote_ip String,
    method    LowCardinality(String),
    path      String,
    status    UInt16,
    bytes     UInt32,
    referer   String,
    ua        String
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (status, ts)
TTL ts + INTERVAL 180 DAY;

最后是 TTL ts + INTERVAL 180 DAY，这是列式库的自带生命周期管理，超过半年的分区会被自动合并删除，省得你自己写清理脚本。

把 Nginx 日志灌进去

import re, datetime, subprocess, json
LINE = re.compile(
    r'(?P<ip>\S+) - - \[(?P<t>[^\]]+)\] '
    r'"(?P<m>\S+) (?P<p>\S+) [^"]*" '
    r'(?P<s>\d+) (?P<b>\d+) "(?P<r>[^"]*)" "(?P<ua>[^"]*)"')

def parse(path):
    rows = []
    for line in open(path, encoding="utf-8", errors="replace"):
        m = LINE.match(line)
        if not m:
            continue
        d = m.groupdict()
        dt = datetime.datetime.strptime(d["t"], "%d/%b/%Y:%H:%M:%S %z")
        rows.append([dt.strftime("%Y-%m-%d %H:%M:%S"),
                     d["ip"], d["m"], d["p"].replace("\t", ""),
                     int(d["s"]), int(d["b"]) if d["b"].isdigit() else 0,
                     d["r"], d["ua"]])
    return rows

rows = parse("/var/log/nginx/access.log")
with open("/tmp/bulk.tsv", "w", encoding="utf-8") as f:
    for r in rows:
        f.write("\t".join(map(str, r)) + "\n")

subprocess.run(
    "clickhouse-client --password xxx "
    "--query \"INSERT INTO access_log FORMAT TSV\" "
    "< /tmp/bulk.tsv", shell=True, check=True)

log_format json_log escape=json
  '{"ts":"$time_iso8601","ip":"$remote_addr",'
  '"method":"$request_method","path":"$request_uri",'
  '"status":$status,"bytes":$body_bytes_sent,'
  '"rt":$request_time,"ua":"$http_user_agent"}';
access_log /var/log/nginx/access.log json_log;

用 escape=json 后，UA 里的双引号会被自动转义，配合 JSONEachRow 格式一行就搞定。

写出不坑自己的查询

SELECT toStartOfHour(ts) AS h,
       count() AS reqs,
       countIf(status >= 500) AS errs,
       round(sum(bytes)/1024/1024, 2) AS mb
FROM access_log
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY h
ORDER BY h;

SELECT query_duration_ms, read_rows, formatReadableSize(memory_usage) AS mem,
       substring(query, 1, 120) AS q
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

内存与并发：小机器的调参

<profiles>
  <default>
    <max_memory_usage>2000000000</max_memory_usage>
    <max_threads>2</max_threads>
    <max_execution_time>60</max_execution_time>
  </default>
</profiles>

与 MySQL 的分工边界

备份、升级与运维要点

ClickHouse 的备份早期靠 ALTER TABLE ... FREEZE 加拷贝，现在有了内置的 BACKUP 命令，语法类似：

BACKUP TABLE access_log TO Disk('backups', 'access_log_2026.zip');

ClickHouse 列式存储实战：个人站日志分析从 MySQL 迁移、MergeTree 建表与查询调优

为什么个人站长会用到 ClickHouse

列式存储到底快在哪里

在单机 VPS 上安装与最小化配置

建表：MergeTree 家族与分区键的选择

把 Nginx 日志灌进去

写出不坑自己的查询

内存与并发：小机器的调参

与 MySQL 的分工边界

备份、升级与运维要点

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

Nginx 301 重定向实战：www、HTTPS 规范化与域名迁移完整指南

js底部飘浮广告代码

OpenResty 风控实战：用 Lua 给 Nginx 加一层地域限制与恶意流量拦截

连接测试文章请忽略2

网站评论防垃圾实战：验证码、过滤规则与反垃圾策略，个人站长完整方案

ClickHouse 列式存储实战：个人站日志分析从 MySQL 迁移、MergeTree 建表与查询调优

为什么个人站长会用到 ClickHouse

列式存储到底快在哪里

在单机 VPS 上安装与最小化配置

建表：MergeTree 家族与分区键的选择

把 Nginx 日志灌进去

写出不坑自己的查询

内存与并发：小机器的调参

与 MySQL 的分工边界

备份、升级与运维要点