ClickHouse分析数据库部署：香港VPS搭建实时数据分析平台与日志分析实战

MySQL和PostgreSQL是OLTP（事务型）数据库，面对海量数据的分析查询（如统计过去30天每小时的UV/PV）性能很差。ClickHouse是Yandex开源的列式分析数据库（OLAP），同样的查询比MySQL快100~1000倍，本文带你在香港VPS上完整部署。

一、列式存储 vs 行式存储

二、安装ClickHouse

<code"># Ubuntu 22.04安装ClickHouse
apt install -y apt-transport-https ca-certificates curl gnupg

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb lts main" \
    | tee /etc/apt/sources.list.d/clickhouse.list

apt update
apt install -y clickhouse-server clickhouse-client

# 启动服务
systemctl enable clickhouse-server
systemctl start clickhouse-server

# 验证安装
clickhouse-client --query "SELECT version()"

三、基础安全配置

<code"># /etc/clickhouse-server/config.d/network.xml


    
    127.0.0.1
    ::1

<code"># /etc/clickhouse-server/users.d/users.xml


    
        
        
            
            
                127.0.0.1
            
            default
            default
        

        
        
            
            
                127.0.0.1
                Grafana服务器内网IP
            
            readonly
            default
        
    

四、创建Nginx日志分析表

<code">-- 连接ClickHouse
clickhouse-client

-- 创建数据库
CREATE DATABASE IF NOT EXISTS logs;

-- 创建Nginx访问日志表（MergeTree引擎，按日期分区）
CREATE TABLE logs.nginx_access
(
    -- 时间字段（分区键）
    event_time    DateTime,
    event_date    Date MATERIALIZED toDate(event_time),

    -- 请求信息
    remote_addr   String,
    request_method LowCardinality(String),  -- LowCardinality适合低基数字段
    request_uri   String,
    status        UInt16,
    bytes_sent    UInt64,
    request_time  Float32,

    -- 来源信息
    http_referer  String,
    http_user_agent String,

    -- 服务器信息
    server_name   LowCardinality(String),

    -- 索引（加速常见查询）
    INDEX idx_status (status) TYPE minmax GRANULARITY 4,
    INDEX idx_addr (remote_addr) TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)    -- 按月分区
ORDER BY (event_date, status, remote_addr)  -- 排序键（影响查询性能）
TTL event_time + INTERVAL 90 DAY     -- 90天后自动删除
SETTINGS index_granularity = 8192;

-- 创建每小时统计聚合视图（预聚合，加速Dashboard查询）
CREATE MATERIALIZED VIEW logs.nginx_hourly_stats
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, server_name, status)
AS SELECT
    toStartOfHour(event_time) AS hour,
    server_name,
    status,
    count()          AS requests,
    sum(bytes_sent)  AS bytes_total,
    avg(request_time) AS avg_response_time,
    uniq(remote_addr) AS unique_ips
FROM logs.nginx_access
GROUP BY hour, server_name, status;

五、Nginx日志格式与Vector收集

<code"># Nginx配置：JSON格式日志（便于解析）
log_format clickhouse_json escape=json
    '{"event_time":"$time_iso8601",'
    '"remote_addr":"$remote_addr",'
    '"request_method":"$request_method",'
    '"request_uri":"$request_uri",'
    '"status":$status,'
    '"bytes_sent":$body_bytes_sent,'
    '"request_time":$request_time,'
    '"http_referer":"$http_referer",'
    '"http_user_agent":"$http_user_agent",'
    '"server_name":"$server_name"}';

access_log /var/log/nginx/access-ch.log clickhouse_json;

<code"># 安装Vector（高性能日志收集器）
curl --proto '=https' --tlsv1.2 -sSf https://sh.vector.dev | bash

# /etc/vector/vector.toml
[sources.nginx_logs]
type = "file"
include = ["/var/log/nginx/access-ch.log"]
read_from = "end"

[transforms.parse_nginx]
type = "remap"
inputs = ["nginx_logs"]
source = '''
. = parse_json!(.message)
.event_time = parse_timestamp!(.event_time, format: "%+")
'''

[sinks.clickhouse]
type = "clickhouse"
inputs = ["parse_nginx"]
endpoint = "http://127.0.0.1:8123"
database = "logs"
table = "nginx_access"
auth.strategy = "basic"
auth.user = "default"
auth.password = "你的密码"
batch.max_events = 10000
batch.timeout_secs = 10

<code"># 启动Vector
systemctl enable vector
systemctl start vector

# 验证数据写入
clickhouse-client --query "SELECT count() FROM logs.nginx_access"

六、常用分析查询

<code">-- 过去24小时的请求量趋势（每小时）
SELECT
    toStartOfHour(event_time) AS hour,
    count() AS requests,
    uniq(remote_addr) AS unique_visitors,
    avg(request_time) AS avg_response_sec
FROM logs.nginx_access
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

-- 状态码分布
SELECT
    status,
    count() AS count,
    round(100 * count() / sum(count()) OVER(), 2) AS pct
FROM logs.nginx_access
WHERE event_date = today()
GROUP BY status
ORDER BY count DESC;

-- Top 20 最慢页面（P95响应时间）
SELECT
    request_uri,
    count() AS requests,
    quantile(0.95)(request_time) AS p95_sec,
    quantile(0.99)(request_time) AS p99_sec
FROM logs.nginx_access
WHERE event_date = today()
  AND status = 200
GROUP BY request_uri
HAVING requests > 100
ORDER BY p95_sec DESC
LIMIT 20;

-- Top攻击IP（高频4xx请求）
SELECT
    remote_addr,
    count() AS count,
    groupArray(10)(request_uri) AS sample_uris
FROM logs.nginx_access
WHERE event_date = today()
  AND status IN (400, 403, 404, 429)
GROUP BY remote_addr
HAVING count > 100
ORDER BY count DESC
LIMIT 20;

七、Grafana接入ClickHouse

<code"># 在Grafana中安装ClickHouse插件
# Grafana → Plugins → Search "ClickHouse" → Install

# 配置数据源：
# URL: http://127.0.0.1:8123
# Database: logs
# Username: analyst
# Password: 分析用户密码

# 导入社区Dashboard（搜索"ClickHouse Nginx"）
# 或创建自定义Dashboard，数据源选择ClickHouse

八、性能调优

<code"># /etc/clickhouse-server/config.d/performance.xml


    
    8

    
    100

    
    4000000000  

    
    5368709120  

九、总结

ClickHouse将Nginx日志从"只能用grep查"升级为"亚秒级分析查询"，配合Grafana可视化，让服务器流量分析变得直观高效。在4核8G的香港VPS上，ClickHouse可以流畅处理亿级记录的分析查询。IDC.Net的香港VPSNVMe SSD提供极高的顺序读写性能，是运行ClickHouse列式存储的理想硬件基础。