ClickHouse 部署与构建

MeteorCat2025-12-262025-12-26

传统 MySQL/PostgreSQL 在单表动不动超过 100G 之后就会考虑用分库分表硬抗, 但是本身就是治标不治本的问题.

哪怕分表出来, 后台也是需要分页查询展示数据, 单表 100G 以上多表合并分页分页查询要CPU和内存占满都要几十秒

基于这种大规律数据日志的情况, 传统的 MySQL/PostgreSQL 已经很难负载这部分功能, 需要寻求其他数据库支持.

也是在这种情况下, 就需要了解到 列存数据库(Column-Oriented Storage) 和 行存数据库(Row-Oriented Storage) 的概念.

在此过程之中就了解到列数据库 - ClickHouse:

特性	行存数据库（MySQL/PostgreSQL）	列存数据库（ClickHouse）
存储方式	按行存储，一行的所有列数据连续存放	按列存储，一列的所有行数据连续存放
查询效率	适合整行读取（如事务、单条记录查询），但查询少量列时需加载整行数据，IO开销大	适合列投影查询（如统计、聚合、筛选少数列），仅加载需要的列，IO效率极高
压缩比	同列数据类型分散，压缩率低（通常1:3~1:5）	同列数据类型一致，压缩率高（通常1:10~1:30），大幅减少存储占用
写入特性	单行/小批量写入高效（事务支持好）	批量写入高效，单行写入性能差（部分列存不支持事务）
索引适配	B+树索引为主，适合点查	稀疏索引、位图索引为主，适合范围查询和聚合

这里就是比较明显的特性区分, 主要是要知道两者保存数据库的原理/优缺点和应用方向:

####################################################################################################################
# 传统行数据库保存和查询原理: Where name = name2
            [ id ][name][pwd ]
                 →      → 
第1行 ---    [id1][name1][pwd1] ← 1. 查询到第一行先按照第一行偏移到 name 这列, 匹配是否等于 name2 
第2行 ---    [id2][name2][pwd2] ← 2. 查询不匹配之后递归偏移到下一行首位再偏移到 name, 在此之后验证
.............................  ← 3. 不断偏移直到完全没有找到数据, 代表直接跳过
# 行数据库查询数据需要加载整行数据(留意:逐行偏移是行级串行处理), 当数据库量特别大的时候IO耗时特别长
# 可以想象为了查询 name = name2 的一行数据库, 需要从头到尾扫描整张表的数据(索引只能缓解部分情况, 最终数据量级上来也没有办法)
# 大部分 IO 操作其实是无效操作, 比如我们只需要对比 name 字段, 根本不需要加载 id 和 pwd 相关等字段数据并做偏移
# 所以这里就衍生出以列保存的数据库, 专门用于缓解这种无意义 IO 耗时的情况
# 注意: 数据库落地的数据会被加载到内存方便查询, 所以这部分不止 CPU 暴涨, 内存也是直接处于高负荷情况
####################################################################################################################


####################################################################################################################
# 列数据库则是因为保存机制不同, 所以查询也不同: Where pwd = pwd3

        第1列     第2列     第1列   ← 1. 偏移到 pwd 那一列的数据, 跳过不相关的数列行
[id]    [id1]    [id2]    [id3]                 ↓     
[name]  [name1]  [name2]  [name3]               ↓
[pwd]   [pwd1]   [pwd2]   [pwd3]  ← 2. 直接匹配 pwd 这一行字段, 开始偏移查询每个值做比对, 最终只需要查询 name 字段相关的值
.............................     ← 3. 只关注对应的数据行, 其他数据基本不会碰到, 节省大量 IO 加载操作
# 有张10000列的表, 列数据库查询只涉及第3列之后开始匹配
#   假设单行有 10000 列, 每列 10 字节 → 单行大小 = 10000列 × 10字节/列 = 100000字节 ≈ 97.66KB
# 
# - 行数据库要把整行读出来再跳到第3列不断读取没一行再跳第3列匹配
#   需要处理的总数据量 = 1000万行 × 97.66KB/行 ≈ 931.32GB
# 
# - 列数据库只需要跳到第3列然后直接匹配对应数据即可
#   需要处理的总数据量 = 1000万行 × 10字节/行 = 100MB
#   
# 两者的查询过程中的 IO 消耗完全不是一个量级, 很变态的查询差距
# 这种列匹配方式被称为向量化执行, 能够满足更大数据吞吐量
####################################################################################################################

至此就区分出两种数据库类别:

OLTP(事务型操作): 数据需要完整可靠事务处理, 数据更新频繁,需要强一致性
OLAP(分析型操作): 海量数据分析和分页处理, 庞大的数据需要批量数据的查询、统计、分组

注意: 因为列数据库这种方式, 也就要求数据最好带有不可变性(删除或者修改会打乱内部的数据分块, 数据会被优化固定连续数据块)

如果修改/删除列数据库的数据, 会导致需要查询那部分数据重建关联的数据块(大规模修改成本极大), 而删除则是标记删除而非真正删除

所以基于这些特性, 可以划分两种数据库的区别:

数据库类型	适用场景	数据特性	核心诉求
行存（OLTP）	事务型操作	可变：高频增删改	强一致性、低延迟
列存（OLAP）	分析型操作	不可变：批量写入后极少修改	高吞吐、低IO、快速聚合

需要牢记这些应用场景, 否则乱用这类数据库会导致性能反而爆降.

ClickHouse 集群很难处理部署, 但是胜在单机性能已经足以升任大部分情况, 如果规模扩大之后就需要考虑购买云服务器厂商提供的方案

常规项目量级上去的比较合理的方案如下:

Kafka(解决 "入口削峰"): 做数据队列接收 API 用户日志上报
ClickHouse(解决 "海量日志分析"): 打点上报日志相关数据异步落地
MySQL|PostgreSQL(解决 "核心业务事务"): 核心后续可变业务异步落地

以上三者不是互斥关系, 而且需要按照数据类型和业务融入其中, 从而将海量数据汇总成为后端数据中心节点

部署搭建

如果没有专门的运维, 最好购买云服务厂商的 ClickHouse 服务(一般云服务器商都有带这类数据库服务)

这里需要最好查看官方文档部署手册并在本地启动服务.

官方安装手册: clickhouse

我这边采用官方 Debian/Ubuntu 发行版的来做部署, 具体流程按照官网输入命令:

# 参考地址: https://clickhouse.com/docs/zh/install/debian_ubuntu

# 安装依赖
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

# 下载源签名 GPG KEY 并安装
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

# 设置临时变量保存平台信息
ARCH=$(dpkg --print-architecture)  # 常规这里是 amd64, 除非是用比较特殊 arm64

# 写入源安装地址
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] 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 start clickhouse-server.service
sudo systemctl enable clickhouse-server.service # 开机启动

# 要启动 ClickHouse 客户端, 请运行
clickhouse-client --password # 输入之前安装配置的默认密码
# 这里会提示
# Warnings:
# * Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. 
# You can enable it using `sudo sh -c 'echo 1 > /proc/sys/kernel/task_delayacct'` or by using sysctl.
# 这里是 clickhouse 要求开启系统核心对其做优化, 建议按照他上面的命令执行
sudo vi /etc/sysctl.conf # 在此配置文件中追加 kernel.task_delayacct = 1
sudo sysctl -p # 执行更新

# 默认的配置文件在 /etc/clickhouse-server/config.xml, 如果需要将数据放置在外部扩展硬盘之中, 需要修改一下参数
# - 核心数据存储路径: yandex 节点下的 path 节点修改成外置硬盘之中
# - 临时数据路径: yandex 节点下的 tmp_path 节点修改成外置硬盘之中
# - 日志存储路径: yandex 节点下的 logger 节点的 log|errorlog 日志节点按需要修改
# - 数据副本相关路径: yandex 节点下的 data_path|metadata_path 建议也修改过去, 虽然是集群副本相关, 但是建议还是同步放在外置硬盘避免以后用到
# 注意: ClickHouse 的运行用户是 clickhouse:clickhouse
# 假设默认外部扩展硬盘处于 /data/clickhouse, 这里提供迁移方案(注意迁移前要先停止运行服务)
sudo systemctl stop clickhouse-server.service # 关闭服务
sudo cp -rp /var/lib/clickhouse/* /data/clickhouse-data
sudo chown -R clickhouse:clickhouse /data/clickhouse-data 
sudo chmod -R 755 /data/clickhouse-data
sudo systemctl start clickhouse-server.service # 启动服务

这就默认部署好简单的单机 clickhouse 服务, clickhouse 单机性能已经足够强, 基本上单台 8核16G 服务器就可以处理上亿的数据量级.

日常命令

虽然 clickhouse 尽量贴合 SQL 语法, 但是实际应用上还是和 SQL 差距很大, 所以需要了解下日常会用到的命令:

-- 这里的命令都是需要在客户端内执行, 也就是运行 clickhouse-client --password 之后进入命令行模式

-- 查看所有的数据库
SHOW TABLES;


-- 创建专门的数据库, 命名为 logs
CREATE DATABASE IF NOT EXISTS logs;
-- clickhouse 的默认引擎采用 Atomic, 各自引擎的作用如下
-- Atomic: 本地引擎, 支持原子操作(表创建|删除|重命名等操作要么全成功,要么全失败), 数据保存于本地文件系统, 大部分情况优先选择
-- Ordinary: 本地引擎, 旧版本的引擎(21.8版本以下), 除非兼容旧版本否则不采用
-- MySQL: 外部引擎, 不存储本地数据, 仅关联远程 MySQL 数据库并且可直接查询|写入远程 MySQL 表, 支持 MySQL 的事务和索引
-- PostgreSQL: 外部引擎, 和 MySQL 类似, 支持 PostgreSQL 的特性
-- 这里需要说下为什么有本地引擎和外部引擎的差别
-- 本地引擎的源数据都是以 clickhouse 配置的本地数据库专门保存在数据的路径之中
-- 外部引擎则是作为 "代理" 访问远程数据库, 内部的数据其实是远程的 MySQL/PostgreSQL 之类获取
-- 之所以要采用外部引擎, 是因为有的时候需要关联查询某些数据做合表(JOIN)操作
-- 比如 clickhouse 日志表内部有有 uid 字段, 但是没有 username 用户名字段(用户信息表在外部MySQL)
-- 这时候就可以利用外部引擎将数据关联JOIN, 从而直接在数据之中附加对应的 username 字段

-- 这里创建外部的 MYSQL 数据库的代理关联
CREATE DATABASE IF NOT EXISTS mysql_users
    ENGINE = MySQL('192.168.1.100:3306', -- MySQL的IP和端口
                   'users', -- MySQL的数据库名
                   'root', -- MySQL的用户名
                   'root' -- MySQL的密码 
             );


-- 查看远程 mysql_users 的具体用户表 user_info
DESCRIBE mysql_users.user_info;

-- 这里假设本地引擎 logs 库当中有张 logs.app_report(用户上报) 表, 内部有 appid + uid 和外部 MYSQL 关联
SELECT l.appid,   -- 仅查询需要的字段，替代l.*
       l.uid,     -- 注意: 不要采用 l.* 而是要手动匹配字段获取, 否则很影响查询性能
       l.create_time,
       u.username -- mysql 表记录的用户名
FROM logs.app_report l
         LEFT JOIN mysql_users.user_info u
                   ON l.appid = u.appid AND l.uid = u.uid -- 左关联MySQL的user表(通过appid+uid匹配)
WHERE l.create_time >= 1766716678790 -- 检索指定时间
ORDER BY l.create_time DESC;

-- 这里实际上是有性能问题的, 若查询结果集超过1万条,不建议直接用外部引擎关联
-- 1. 本质上关联就是将 MySQL 整张表表查询导入在 clickhouse, MySQL 到 clickhouse 的网络传输会成为瓶颈
-- 2. clickhouse 会将 MySQL 数据在内存中缓存, 如果 MySQL 数据过大易导致内存溢出
-- 所以本质上其实宁愿把 clickhouse 表结构设计成附加冗余字段宽表, 也不推荐采用外部 JOIN 机制
-- 不过有的数据中间件(flink之类)支持远程 MySQL 同步到 clickhouse 本地数据, 然后查询就是本地匹配查询, 这种倒是没有问题
-- 但是如果没有中间件处理, 那么就尽可能不要用到这种外部关联查询


-- 删除外部关联引擎库, 也是删除数据库
-- 谨慎危险操作! 执行这命令之前必须清楚知道自己在干什么
DROP DATABASE IF EXISTS mysql_users;

-- 切换数据库
USE logs;
-- 这里先停下来, 我们需要参考 MySQL/PostgreSQL 的应用上报信息表, 来创建转化成 clickhouse 表

这里需要参考之前 Kafka 做消息队列的用户上报表, 将其转化成 clickhouse 数据表做参考:

# 应用事件上报表
create table if not exists pino_app_report
(
    # 核心主要信息
    id                 CHAR(36)         not null comment '数据主键, 采用UUID来做标识, 如果有相同 UUID 就覆盖该行数据',
    action             int unsigned     not null comment '上报的事件ID',

    # 账号关联信息
    appid              bigint unsigned  not null comment '上报所属的应用ID',
    uid                bigint unsigned  not null comment '用户主键, 在 user_info 表中的主键即是用户UID',

    # 服务器相关信息
    sid                varchar(64)      not null comment 'CP服务器ID标识',
    sname              varchar(64)      not null comment 'CP服务器名称',

    # 角色上报信息
    role_id            varchar(64)      not null default '' comment '玩家在指定服务器当中的角色ID',
    role_name          varchar(64)      not null default '' comment '玩家在指定服务器当中的角色名',
    role_balance       varchar(64)      not null default '' comment '玩家游戏币余额',
    role_level         varchar(64)      not null default '' comment '玩家在指定服务器当中的角色等级',
    role_power         varchar(64)      not null default '' comment '玩家在指定服务器当中的角色战力',
    role_gender        tinyint unsigned not null default '0' comment '玩家角色性别 0 = 没有性别要素,1 = 男,2 = 女, 3 = 非二元性别',
    role_vip           varchar(64)      not null default '' comment '玩家在指定服务器当中的VIP等级',
    role_create_time   bigint unsigned  not null comment '角色创建时间戳, 以毫秒级UTC时间戳为单位',
    role_level_up_time bigint unsigned  not null comment '角色升级时间戳, 以毫秒级UTC时间戳为单位',

    # 游戏相关 - 职位信息
    profession_id      varchar(64)      not null default '' comment '玩家在游戏之中的职位ID',
    profession_name    varchar(64)      not null default '' comment '玩家在游戏之中的职位名称',

    # 游戏相关 - 工会信息
    guild_id           varchar(64)      not null default '' comment '玩家工会或帮派ID',
    guild_name         varchar(64)      not null default '' comment '玩家工会或帮派名称',
    guild_master_id    varchar(64)      not null default '' comment '玩家工会或帮派最高长官ID',
    guild_master_name  varchar(64)      not null default '' comment '玩家工会或帮派最高长官名称',

    create_time        bigint unsigned  not null comment '上报时间, 毫秒级别的UTC时间戳',
    create_ip          varchar(64)      not null comment '上报IP地址',

    # 其他扩展信息
    extra              json             not null default JSON_OBJECT() comment '额外扩展信息, 必须采用 {"xxx":"yyy"} 对象组, 有时候需要记录些特殊元数据',
    primary key (id)
) comment '应用的上报信息表'
    engine = InnoDB
    charset = utf8mb4
    collate = utf8mb4_unicode_ci;

需要清楚 clickhouse 支持的类型, MySQL/PostgreSQL 和 clickhouse 的类型差别还是很大的, 目前支持的类型如下:

ClickHouse类型	作用说明
`Int8`/`UInt8`	8位整数，范围`-128~127`/`0~255`，适合存储性别、状态等小数值（如`role_gender`）
`Int32`/`UInt32`	32位整数，范围`-2^31~2^31-1`/`0~2^32-1`，适合事件ID、角色等级等（如`action`）
`Int64`/`UInt64`	64位整数，范围`-2^63~2^63-1`/`0~2^64-1`，适合应用ID、用户UID等大数值（如`appid`/`uid`）
`UUID`	原生UUID类型，占用16字节（MySQL用36字节字符串），支持合法性校验（如`id`）
`LowCardinality(String)`	低基数字符串优化，适合取值有限的字段（如`sid`/`sname`），压缩率提升10~100倍, 可以视作 enum 枚举压缩优化
`JSON`	原生JSON类型，支持直接解析查询（如`extra`），性能优于MySQL的JSON解析
`DateTime64(3)`	毫秒级时间类型（MySQL需用`bigint`存毫秒戳），适合时间范围查询
`Float32`/`Float64`	32位单精度/64位双精度浮点数，范围与标准IEEE 754一致，存在精度丢失风险; 可用于非精确计算（如游戏战力估算、数据采样率）
`Decimal(P, S)`	定点数，`P`表示总位数，`S`表示小数位数（如`Decimal(20,2)`），无精度丢失; 可用于精确数值计算（如游戏币余额、充值金额）
`Decimal32(S)`/`Decimal64(S)`/`Decimal128(S)`	定点数的细分类型，分别对应32/64/128位存储，`S`为小数位数; 可用于超高精度计算（如金融级交易金额、游戏道具定价）
`String`	无长度限制的字符串类型，按需存储实际字符数，替代MySQL的`varchar/text`; 可用于任意长度字符串（如角色名、工会名称）
`FixedString(N)`	固定长度`N`的字符串，不足补0，超出报错，比`String`更节省存储; 可用于固定长度标识（如32位角色ID、16位设备ID）
`Enum8`/`Enum16`	枚举类型，`Enum8`用1字节存储（对应`Int8`），`Enum16`用2字节（对应`Int16`），仅能存储预定义枚举值; 可用于固定枚举场景（如角色职业、装备品质：`Enum8('normal'=1, 'rare'=2, 'epic'=3)`）
`Date`	日期类型，存储年-月-日，范围`1970-01-01`至`2105-12-31`，占用2字节; 可用于按日期分区/查询（如上报日期）
`Date32`	扩展日期类型，范围`1900-01-01`至`2299-12-31`，占用4字节; 可用于历史数据存储（如老玩家注册日期）
`DateTime`	秒级时间类型，存储年-月-日时:分:秒，范围`1970-01-01 00:00:00`至`2105-12-31 23:59:59`，占用4字节; 可用于秒级时间范围查询（如登录时间）
`Interval`	时间间隔类型（如`IntervalDay`/`IntervalHour`），用于时间运算; 可用于时间偏移计算（如`create_time + Interval 1 Day`）
`IPv4`/`IPv6`	原生IP类型，分别存储IPv4/IPv6地址，占用4/16字节，支持IP范围/子网查询; 可用于存储上报IP、服务器IP（如`create_ip IPv4`）
`Nullable(T)`	可为空类型，`T`为基础类型（如`Nullable(UInt32)`），占用1字节额外存储标识NULL; 可用于允许字段为空的场景（如玩家可选填的工会ID）
`Binary`	二进制类型，存储原始字节数据，替代`String`存储非字符数据; 可用于存储加密数据、图片哈希值
`GeoPoint`/`GeoPolygon`	地理坐标类型，支持经纬度存储和空间查询（如距离计算、区域包含）; 可用于游戏地理场景（如玩家位置、地图区域）
`Array(T)`	数组类型，`T`为元素类型（如`Array(UInt32)`/`Array(String)`），支持动态增删; 可用于多值存储（如玩家拥有的道具ID、角色技能列表）
`Map(K, V)`	键值对类型，`K`为键类型（必须可哈希），`V`为值类型（如`Map(String, String)`）; 可用于灵活属性存储（如玩家自定义标签、设备参数）
`Tuple(T1, T2, ...)`	元组类型，存储不同类型的元素（如`Tuple(UInt64, String, UUID)`），不可变; 可用于组合字段存储（如坐标`Tuple(Float64, Float64)`）
`AggregateFunction`	聚合函数类型，存储预计算的聚合结果（如`AggregateFunction(count, UInt64)`）; 可用于预聚合表（如按小时统计的活跃用户数）
`SimpleAggregateFunction`	简化聚合函数类型，仅支持简单聚合（`sum`/`min`/`max`），性能更优; 可用于轻量预聚合（如角色最高等级、累计充值金额）
`LowCardinality(T)`	低基数优化类型，不仅支持`String`，还支持`Int`/`Date`等类型（如`LowCardinality(UInt32)`））; 可用于低基数数值/日期字段（如服务器ID、游戏大区ID）
`Expression`	表达式类型，基于其他字段计算生成（虚拟列），不占用实际存储; 可用于派生字段（如`report_date Expression Date = toDate(create_time/1000)`）

注意: ClickHouse 之中字段默认是 NOT NULL; 除非显式声明 Nullable({插入的类型类型}), 否则插入 null 值报错(喜欢这设计)

而类型选择需要遵循以下规则:

数值优先：能用数值类型（如UInt64）的场景，绝不使用字符串（如毫秒时间戳、角色等级）；
低基数优化：取值有限的字段，优先用LowCardinality(String)或Enum（如服务器ID、职业）；
复合类型替代JSON：简单多值/键值对用Array/Map，比JSON解析更高效；
特殊类型适配业务：IP用IPv4/IPv6，坐标用GeoPoint，减少手动解析的开销(但是不强求,只是作为规范建议)；
避免Nullable：非必要场景不要用Nullable，会增加存储和查询开销（可通过默认值替代NULL）。

我个人还是比较喜欢采用 Uint64 方面做单纯 UTC 毫秒时间戳而时区转换是外部处理,让内部的数据字段功能更加纯粹(仅范围查询)

这里就是最后转换成 clickhouse 内部表内容, 如下所示来创建和处理:

-- 再次切换到创建 logs 库
USE logs;

-- 创建游戏应用事件上报表
CREATE TABLE IF NOT EXISTS logs.pino_app_report
(
    -- 核心主要信息
    id                 UUID COMMENT '数据主键, UUID标识(ClickHouse自带有专门类型)',
    action             UInt32 COMMENT '上报的事件ID(无符号int对应UInt32)',

    -- 账号关联信息
    appid              UInt64 COMMENT '上报所属的应用ID(bigint unsigned对应UInt64)',
    uid                UInt64 COMMENT '用户主键(外部的用户标识ID)',

    -- 服务器相关信息
    sid                LowCardinality(String) COMMENT 'CP服务器ID标识(低基数，用LowCardinality优化)',
    sname              LowCardinality(String) COMMENT 'CP服务器名称(低基数优化)',

    -- 角色上报信息
    role_id            String COMMENT '玩家角色ID',
    role_name          String COMMENT '玩家角色名',
    role_balance       String COMMENT '玩家游戏币余额',
    role_level         String COMMENT '角色等级',
    role_power         String COMMENT '角色战力',
    role_gender        UInt8 COMMENT '角色性别 0=无,1=男,2=女,3=非二元性别(tinyint unsigned对应UInt8)',
    role_vip           String COMMENT 'VIP等级（若为纯数字可改为UInt8）',
    role_create_time   UInt64 COMMENT '角色创建时间戳（毫秒级UTC数值）',
    role_level_up_time UInt64 COMMENT '角色升级时间戳（毫秒级UTC数值）',

    -- 游戏相关 - 职位信息
    profession_id      LowCardinality(String) COMMENT '职位ID(低基数优化)',
    profession_name    LowCardinality(String) COMMENT '职位名称(低基数优化)',

    -- 游戏相关 - 工会信息
    guild_id           String COMMENT '工会/帮派ID',
    guild_name         String COMMENT '工会/帮派名称',
    guild_master_id    String COMMENT '工会最高长官ID',
    guild_master_name  String COMMENT '工会最高长官名称',

    -- 上报基础信息
    create_time        UInt64 COMMENT '上报时间戳（毫秒级UTC数值）',
    create_ip          String COMMENT '上报IP地址',

    -- 其他扩展信息(ClickHouse支持JSON格式)
    extra              JSON COMMENT '额外扩展信息'
) ENGINE = ReplacingMergeTree() -- 采用合并列引擎
      PARTITION BY toDate(toDateTime64(create_time / 1000, 3)) -- 仍可按日期分区（转换为日期用于分区）
      ORDER BY (appid, uid, create_time, id) -- 按时间数值排序，加速范围查询
      COMMENT '应用的上报信息表';


-- 创建完成查看表是否存在
SHOW TABLES;

-- 查看表详情字段信息
DESC pino_app_report;


---------------------------------------------------------
-- 这里就是比较核心的 clickhouse 设计相关, 需要解析每个表配置的作用
--    
-- ENGINE = ReplacingMergeTree([version_column])
-- version_column - 可选配置, 仅支持整数/Date/DateTime/DateTime64类型, 用于决定重复行中保留版本值最大的行; 若省略，则保留物理存储中最后写入的行
-- 基于核心 MergeTree 的去重和数据替换专用引擎, 其配置设计直接决定了数据的去重规则、保留逻辑和存储特性
-- ReplacingMergeTree 的核心是 ClickHouse 合并数据片段时, 对同一分区内排序键完全相同的行, 按版本列规则保留一行, 其余重复行被删除
-- 简单来说就是如果配置值为时间戳或者整数, 那么相同值同时插入数据的时候是最保留对应值最新那一行, 其他相同的老数据删除
-- ReplacingMergeTree 会去创建表的 'ORDER BY(字段列表)' 视为比较去重的根据, 如果内部有数据重复那么会触发最新保留删除老数据
-- 触发删除老数据保留最新数据的时候, 如果指定了 version_column(比较判断最新字段), 那么就会按照这个指定字段来做比较(最大的保留, 其余小值删除)
-- 那么简单来看就是, 触发去重保留最新数据时:
--   1. 若指定了 version_column, 按该字段的数值大小比较(最大的保留, 其余删除)
--   2. 若未指定 version_column, 保留物理存储中最后写入的行, 删除其他重复行
-- 一般没有特殊需求直接留空即可, 如果想指定的话可以考虑按照 create_time 这类时间戳按照最新比较
--
-- PARTITION BY {分区规则}
-- 注意 clickhouse 的数据也是有分片分区的逻辑, 这样好处就是可以将海量数据分布保存
-- 这里的 PARTITION 就是集群分布时候的将数据分布在不同服务器的关键, 能够有效优化查询性能和数据管理
-- 日志类规则其实也就 年/月/小时 来分布周期分区, 这里面推荐分区规则如下(可以通过 SELECT toDate(toDateTime64(1766721540192 / 1000, 3)) 等命令查看输出):
--    1. PARTITION BY toDate(toDateTime64(create_time / 1000, 3)) 
--       -- 按日分区, 毫秒戳转日期, 最后得出的 2025-12-26, 比较推荐常见的分区规则
--    2. PARTITION BY toYYYYMM(toDateTime64(create_time / 1000, 3)) 
--       -- 按月分区, 数据量相对适中(如日数据超100GB, 但是没满足500GB), 最后得出 201512
--    3. PARTITION BY toDateTime64(create_time / 1000, 3) - INTERVAL (toHour(toDateTime64(create_time / 1000, 3)) % 24) HOUR  
--       -- 按小时分区, 实时性要求高的场景，如高频数据实时监控, 最后得出 2025-12-26 00:59:00.192, 需要用到这种精度来分区那代表这部分至少要毫秒并发级别
--    4. PARTITION BY (appid, toDate(toDateTime64(create_time / 1000, 3)))
--       -- 复合分区, 按 "appid+每天" 分区(多游戏应用共享表), 如果游戏很多的情况(游戏多颗粒度应该更加分散), 这种分区规则也不错
--    5. PARTITION BY uid / 1000000
--       -- 动态数值分区, 如上就是按用户UID范围分区(每100万用户一个分区), 这部分其实用的比较少, 更多也是 appid 动态分区来处理
-- 分区规则关系到后续单机/分布的数据分布, 合理利用能够加速内部数据查询速度
-- 需要注意的是, 要留意好以下分区可能出现的问题点:
--  1. 避免单分区过大, 尽可能将海量数据分散到不同分区, 避免某些分区数据分布不均匀
--  2. 避免分区数量过多, 过多的分区会导致系统导出查询时, 需要拉取过多分区数据
-- 这两者的平衡点需要自己来把控, 按照网上说法来数值化: 
--  1. 单分区大小控制在10GB~50GB以内, 不超过100GB
--  2. 单表分区数量控制在1000个以内, 集群环境不超过5000个
-- PS: 按 Ymd 分区每天分区一年也才 365 左右的分区, 3年之内的数据正好命中在3年数据最大留存周期, 这部分数据后续很少用到, 所以才推荐 Ymd 分区处理
-- 
-- ORDER BY ([排序字段])
-- 注意不要被其他语言的 ORDER BY 迷惑, clickhouse 的 ORDER BY 字段是核心当中的核心, 关系到内部检索和去重
-- 简单来说就是 clickhouse 的 ORDER BY 等同于 PRIMARY KEY(MySQL/PostgreSQL主键)
-- clickhouse 会基于ORDER BY的字段生成稀疏索引(Sparse Index), 默认每 8192 行生成一个索引标记.
-- 查询时, clickhouse 通过索引快速定位目标数据的物理位置, 避免全表扫描
-- 保存时, clickhouse 则是按照这部分字段规则按相邻的物理排序将数据保存起来
-- 所以可以简单按照上面理解为:
--  1. 从检索性能和数据组织的角度, clickhouse 的 ORDER BY 等效于传统数据库的主键
--  2. 从约束性角度, clickhouse 的 ORDER BY 无唯一性要求(如需唯一性,需通过业务层或 ReplacingMergeTree 保证)
-- 加入能够加速查询速度, 但是很容易被人滥用
--  1. 放入过多字段: 索引存储开销增大, 且稀疏索引的过滤效率随字段数增加而降低
--  2. 认为ORDER BY必须保证唯一性: 只有设置 ReplacingMergeTree 才能保证唯一去重逻辑, 其他情况插入的时候是允许重复存在
--  3. 过少字段无法形成索引: 尽可能采用能够作为唯一标识的字段来创建, 比如 appid + uid + create_time 这些能够尽可能标识数据唯一
-- 另外对于字段的位置也是很有讲究的, 越是能够精准匹配或者常用检索的, 越是应该放置在左边
--  1. 字段排列应该以命中频率从高到低来写入, 如下场景来分布:
--      appid（应用）> sid（服务器）> action（事件类型）> uid（玩家）> create_time（时间）
--      那么应该设计成 ORDER BY (appid, sid, action, uid, create_time)
--  2. 按照字段的数据量多少从少到多来写入, 如下场景分布:
--      appid（如 10 个游戏）、sid（如 100 个服务器）、action（如 50 种事件）、uid（如 1000 万玩家）、id（UUID）    
--      那么应该设计成 ORDER BY (uid,action,appid)
-- 更加无脑的方式就是: 数据量越少 + 查询条件越精准(不采用范围查询) + 日常查询频率月多 的相关字段字段越应该向左边移动位置
-- 需要说明的是, UUID 是随机散乱值会导致索引过滤效率极低, 所以这种类型的字段不推荐加入 ORDER BY 之中参与查询和保存
-- 
-- COMMENT '{表注释}'
-- 这个什么好说, 主要是表注释内容
-- 接下来就是其他命令


-- 重命名表
RENAME TABLE logs.pino_app_report TO logs.pino_app_report_bak;

-- 修改表注释
ALTER TABLE logs.pino_app_report
    MODIFY COMMENT '游戏上报日志表（2025版）';

-- 添加字段
ALTER TABLE logs.pino_app_report
    ADD COLUMN IF NOT EXISTS device String COMMENT '设备型号';

-- 手动触发合并(适用于ReplacingMergeTree 的表触发去重)
OPTIMIZE TABLE logs.pino_app_report FINAL;

-- 删除表, 谨慎操作
DROP TABLE IF EXISTS logs.pino_app_report;

-- 以上就是日常的表操作管理命令, 还有些细节配置就需要查询官方文档来参考

之后就准备生成测试数据并且开始常规的查询工作, 后续部分其实更加贴近 MySQL/PostgreSQL 的日常使用

测试与查询

这里生成一些日常的上报数据, 用于添加到 clickhouse 之中备用:

-- clickhouse 插入功能和 MySQL/PostgreSQL 一样, 甚至多了一些特性
-- clickhouse 内部的插入语句扩展其他常规数据没有的, 直接允许 JSON 数据根是做插入
-- FORMAT JSONEachRow 代表数据源格式是 JSON 形式, 以逗号分割每个数据对象组做插入
INSERT INTO logs.pino_app_report FORMAT JSONEachRow
            {"id": "f9e8d7c6-b5a4-3210-9876-543210fedcba", "action": 1001, "appid": 1, "uid": 100001, "sid": "s101", "sname": "烈焰服务器", "role_id": "role_100001", "role_name": "战神归来", "role_balance": "99999.99", "role_level": "80", "role_power": "120000", "role_gender": 1, "role_vip": "8", "role_create_time": 1765000000000, "role_level_up_time": 1766721540192, "profession_id": "p001", "profession_name": "战士", "guild_id": "g1001", "guild_name": "天下第一公会", "guild_master_id": "100000", "guild_master_name": "公会会长", "create_time": 1766721540192, "create_ip": "192.168.1.101", "extra": {"device": "iPhone15", "os": "iOS17", "network": "5G"}},
            {"id": "a1b2c3d4-e5f6-7890-1234-567890abcdef", "action": 1002, "appid": 1, "uid": 100002, "sid": "s101", "sname": "烈焰服务器", "role_id": "role_100002", "role_name": "法师妹妹", "role_balance": "56789.50", "role_level": "75", "role_power": "98000", "role_gender": 2, "role_vip": "5", "role_create_time": 1765100000000, "role_level_up_time": 1766722000000, "profession_id": "p002", "profession_name": "法师", "guild_id": "g1001", "guild_name": "天下第一公会", "guild_master_id": "100000", "guild_master_name": "公会会长", "create_time": 1766722000000, "create_ip": "192.168.1.102", "extra": {"device": "Huawei Mate60", "os": "Android 14", "network": "4G"}},
            {"id": "b2c3d4e5-f6a7-8901-2345-67890abcdef1", "action": 1003, "appid": 1, "uid": 100003, "sid": "s102", "sname": "雷霆服务器", "role_id": "role_100003", "role_name": "刺客信条", "role_balance": "123456.78", "role_level": "90", "role_power": "150000", "role_gender": 1, "role_vip": "10", "role_create_time": 1765200000000, "role_level_up_time": 1766723000000, "profession_id": "p003", "profession_name": "刺客", "guild_id": "g1002", "guild_name": "暗影联盟", "guild_master_id": "100005", "guild_master_name": "暗影首领", "create_time": 1766723000000, "create_ip": "192.168.1.103", "extra": {"device": "Xiaomi 14", "os": "MIUI 15", "network": "5G"}},
            {"id": "c3d4e5f6-a7b8-9012-3456-7890abcdef12", "action": 1004, "appid": 2, "uid": 200001, "sid": "s201", "sname": "清风服务器", "role_id": "role_200001", "role_name": "医者仁心", "role_balance": "78900.00", "role_level": "60", "role_power": "65000", "role_gender": 2, "role_vip": "3", "role_create_time": 1765300000000, "role_level_up_time": 1766724000000, "profession_id": "p004", "profession_name": "牧师", "guild_id": "", "guild_name": "", "guild_master_id": "", "guild_master_name": "", "create_time": 1766724000000, "create_ip": "192.168.1.104", "extra": {"device": "OPPO Find X7", "os": "ColorOS 14", "network": "4G"}},
            {"id": "d4e5f6a7-b8c9-0123-4567-890abcdef123", "action": 1001, "appid": 2, "uid": 200002, "sid": "s202", "sname": "明月服务器", "role_id": "role_200002", "role_name": "弓箭手", "role_balance": "88888.88", "role_level": "70", "role_power": "82000", "role_gender": 3, "role_vip": "6", "role_create_time": 1765400000000, "role_level_up_time": 1766725000000, "profession_id": "p005", "profession_name": "弓箭手", "guild_id": "g2001", "guild_name": "精灵部落", "guild_master_id": "200000", "guild_master_name": "精灵女王", "create_time": 1766725000000, "create_ip": "192.168.1.105", "extra": {"device": "Vivo X100", "os": "OriginOS 4", "network": "5G"}},
            {"id": "e5f6a7b8-c9d0-1234-5678-90abcdef1234", "action": 1005, "appid": 1, "uid": 100001, "sid": "s101", "sname": "烈焰服务器", "role_id": "role_100001", "role_name": "战神归来", "role_balance": "199999.99", "role_level": "80", "role_power": "120000", "role_gender": 1, "role_vip": "8", "role_create_time": 1765000000000, "role_level_up_time": 1766721540192, "profession_id": "p001", "profession_name": "战士", "guild_id": "g1001", "guild_name": "天下第一公会", "guild_master_id": "100000", "guild_master_name": "公会会长", "create_time": 1766726000000, "create_ip": "192.168.1.101", "extra": {"device": "iPhone15", "os": "iOS17", "network": "5G", "recharge_amount": 1000, "recharge_type": "支付宝"}},
            {"id": "f6a7b8c9-d0e1-2345-6789-0abcdef12345", "action": 1006, "appid": 1, "uid": 100003, "sid": "s102", "sname": "雷霆服务器", "role_id": "role_100003", "role_name": "刺客信条", "role_balance": "123456.78", "role_level": "91", "role_power": "155000", "role_gender": 1, "role_vip": "10", "role_create_time": 1765200000000, "role_level_up_time": 1766727000000, "profession_id": "p003", "profession_name": "刺客", "guild_id": "g1002", "guild_name": "暗影联盟", "guild_master_id": "100005", "guild_master_name": "暗影首领", "create_time": 1766727000000, "create_ip": "192.168.1.103", "extra": {"device": "Xiaomi 14", "os": "MIUI 15", "network": "5G", "level_up_reward": "高级装备箱"}},
            {"id": "a7b8c9d0-e1f2-3456-7890-abcdef123456", "action": 1007, "appid": 2, "uid": 200001, "sid": "s201", "sname": "清风服务器", "role_id": "role_200001", "role_name": "医者仁心", "role_balance": "88900.00", "role_level": "60", "role_power": "65000", "role_gender": 2, "role_vip": "3", "role_create_time": 1765300000000, "role_level_up_time": 1766724000000, "profession_id": "p004", "profession_name": "牧师", "guild_id": "g2002", "guild_name": "圣光教会", "guild_master_id": "200006", "guild_master_name": "圣光主教", "create_time": 1766728000000, "create_ip": "192.168.1.104", "extra": {"device": "OPPO Find X7", "os": "ColorOS 14", "network": "4G", "join_guild_time": 1766728000000}},
            {"id": "b8c9d0e1-f2a3-4567-890a-bcdef1234567", "action": 1001, "appid": 3, "uid": 300001, "sid": "s301", "sname": "星辰服务器", "role_id": "role_300001", "role_name": "召唤师", "role_balance": "50000.00", "role_level": "50", "role_power": "45000", "role_gender": 2, "role_vip": "2", "role_create_time": 1765500000000, "role_level_up_time": 1766729000000, "profession_id": "p006", "profession_name": "召唤师", "guild_id": "", "guild_name": "", "guild_master_id": "", "guild_master_name": "", "create_time": 1766729000000, "create_ip": "192.168.1.106", "extra": {"device": "Realme GT Neo5", "os": "Realme UI 5.0", "network": "5G"}},
            {"id": "c9d0e1f2-a3b4-5678-90ab-cdef12345678", "action": 1008, "appid": 3, "uid": 300002, "sid": "s301", "sname": "星辰服务器", "role_id": "role_300002", "role_name": "圣骑士", "role_balance": "60000.00", "role_level": "55", "role_power": "52000", "role_gender": 1, "role_vip": "4", "role_create_time": 1765600000000, "role_level_up_time": 1766730000000, "profession_id": "p007", "profession_name": "圣骑士", "guild_id": "g3001", "guild_name": "圣光军团", "guild_master_id": "300000", "guild_master_name": "圣骑士团长", "create_time": 1766730000000, "create_ip": "192.168.1.107", "extra": {"device": "OnePlus 12", "os": "OxygenOS 14", "network": "5G", "task_complete": "主线任务-星辰之塔"}}
;


-- 其他查询和其他数据库也是一致
SELECT *
FROM logs.pino_app_report
ORDER BY create_time DESC
LIMIT 5;

-- 分页也是一致
SELECT *
FROM logs.pino_app_report
ORDER BY create_time DESC
LIMIT 5 OFFSET 5;

-- 按应用统计事件分布
SELECT appid, action, count(*) AS event_count
FROM logs.pino_app_report
GROUP BY appid, action;

-- 按服务器统计日活跃用户数（DAU）
SELECT sid,
       toDate(toDateTime64(create_time / 1000, 3)) AS dt,
       count(DISTINCT uid)                         AS dau
FROM logs.pino_app_report
GROUP BY sid, dt;

查询没什么好说的, 基本上和其他数据库的查询语法差不多, 只是导入大数据的时候能够明显感觉到快

正式环境日志数据导入之后, 真的能明显感觉到 clickhouse 的 ‘快’ 是什么感觉

至此就是完整的 clickhouse 搭建和使用流程, 其实正式上线的项目并不推荐个人搭建而是直接买云服务商方案, 毕竟能用钱来为数据安全兜底比什么都重要.

其他对应的语言 PHP/Java/Python 操作流程可能后续会额外说明, 基本上也是直接引入第三方库使用即可, 没什么太大难度.