★ FEATURED ARTICLE 1. 先搞懂 ClickHouse 到底是个什么数据库1.1 它解决了什么问题如果你做过后台报表、用户行为分析或者物联网数据统计大概率被 MySQL 的count(*)和group by折磨过。数据量到了千万、亿级以后MySQL 的普通索引和行式存储结构会让聚合查询变成一场灾难哪怕加了索引也扛不住几亿行数据的同时扫描。后来我接触到 ClickHouse才真正明白什么叫“为查询而生”。ClickHouse 是一个开源的列式 OLAP 数据库由俄罗斯的 Yandex 开发最初用于 Web 流量分析。它的定位不是替代 MySQL、PostgreSQL 这类 OLTP 数据库而是专门解决“在海量数据中做快速聚合分析”的问题。简单来说它就是为那种“一次写入、频繁查询、查询大多是范围扫描和统计”的场景准备的。我最早用它是因为日志分析。我们一天产生几十亿条日志要按用户、时间、来源做分组统计之前用 MySQL 已经跑到分库分表都救不回来换成 ClickHouse 之后同样的逻辑单机就能跑到秒级甚至毫秒级。这里的关键不是硬件多强而是它从根子上换了存储和计算模型。1.2 列式存储和向量化两个大杀器ClickHouse 为什么快答案藏在“列式存储”和“向量化执行”这两个词里。传统行式存储比如 MySQL、PostgreSQL把一行数据的所有列放在一起适合增删改查但做聚合时经常要把无关字段也读出来。列式存储则把每列单独存放查询只读需要的列。比如你的表有 100 个字段统计时只需要其中 3 个字段列式存储就只扫这 3 列的数据文件IO 量直接少一个数量级。光有列式存储还不够ClickHouse 还在执行引擎上做了向量化处理。普通数据库执行SELECT sum(amount) FROM orders时会一行一行地把谓词判断和累加循环跑完ClickHouse 则把数据分批组织成向量上层用 SIMD 指令一次处理一批数据。你可以把它理解成“一次搬一整箱货”而不是“一件一件搬”吞吐量自然高。还有一个容易被忽略的地方是数据压缩。同一个列里的数据往往相似度极高压缩率能做到 5:1 甚至更高这进一步降低了磁盘 IO。所以很多场景下ClickHouse 的瓶颈根本不是 CPU 或磁盘而是网络带宽或者内存。1.3 什么场景适合它什么场景千万别用它适合的典型场景有用户行为轨迹分析、业务监控指标统计、日志检索聚合、AB 实验报表、广告点击流、IoT 设备数据汇总。这些场景都有共同特征数据量大以写入为主很少修改查询通常是分组聚合和范围扫描。不适合的场景也很明确不要拿它当业务主库来存订单、做事务、频繁更新单行。ClickHouse 的强项是吞吐不是事务一致性。如果你需要 ACID、外键、复杂关联的多表 JOIN还是老老实实用 MySQL、PostgreSQL。ClickHouse 对 JOIN 的支持一直在改进但它的设计哲学是“宽表 预聚合”这也是为什么很多团队在它前面再加一层 Kafka把数据清洗好再灌进去。一句话总结我自己的选型原则如果是给管理层看的“昨晚卖了多少”这类报表查询实时性要求高、数据量大、维度固定用它如果是前台用户创建订单、改库存别碰它。2. 安装部署从单机到可以上生产2.1 安装前的几个判断很多人一上来就抄网上的命令结果装完发现问题百出。安装 ClickHouse 之前先想清楚三件事服务器系统、内存配置、数据盘文件系统。ClickHouse 官方支持主流 Linux 发行版推荐用 CentOS 7 或 Ubuntu 16.04。Windows 上虽然有 Docker 方案但生产环境几乎没人这么干。内存方面单机试用建议至少 4GB因为查询引擎很多内存是用于“临时聚合”的数据量一大几 GB 内存很容易被吃满。数据盘建议用 XFS 或 ext4网络文件系统NFS不要用来保存数据锁和 fsync 检查会出各种诡异问题。另外ClickHouse 默认会占用系统很多内存吗不会默认配置比较保守。但你一旦调高max_memory_usage碰到超内存任务时会直接收到Memory limit exceeded报错这个坑后文会细说。2.2 单机安装实操以 CentOS 7 为例官方仓库安装是这么几步sudo yum install -y yum-utils sudo rpm --import https://packages.clickhouse.com/rpm/clickhouse.asc sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo sudo yum install -y clickhouse-server clickhouse-clientUbuntu 则用sudo apt-get install -y apt-transport-https ca-certificates dirmngr sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4 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 start clickhouse-server sudo systemctl enable clickhouse-server如果没装 systemd可以跑/etc/init.d/clickhouse-server start。启动后客户端连一下试试clickhouse-client --password默认情况下 ClickHouse 只允许本机连接端口是 9000HTTP 端口 8123。远程访问需要在配置里设置listen_host生产环境建议不要直接暴露到公网要么用内网 IP要么走 SSH 隧道。这里有个细节我第一次安装时用客户端连接总是报DB::Exception: Cannot connect to server: Connection refused。检查半天发现是服务其实没起来原因是/etc/clickhouse-server/data目录所在的磁盘没空间了。安装前一定先df -h看看数据目录空间ClickHouse 对磁盘空间很敏感写不了临时文件会直接拒绝启动。2.3 两个关键配置文件config.xml 和 users.xmlClickHouse 的配置不像 MySQL 那样全塞一个 my.cnf它的核心配置在/etc/clickhouse-server/config.xml和/etc/clickhouse-server/users.xml。config.xml管服务器本身监听地址、数据目录、日志路径、分布式表配置等。我常用的几个调整项listen_host默认是127.0.0.1如果要让其他机器访问改为0.0.0.0或指定内网 IP。path数据存储路径默认是/var/lib/clickhouse/大表场景建议把数据目录挂到独立大磁盘。max_server_memory_usage限制整个服务可用内存防止它把机器内存吃光。我的经验是设成物理内存的 70% 左右。users.xml管理用户和安全策略。默认用户default没有密码默认配置下只能在本地访问但为了安全我还是建议加上密码后面会说。你还可以在这里配置max_memory_usage_per_user、max_concurrent_queries和查询时的quota。比如我经常给报表账号限制readonly1防止运维误操作把线上数据改了。改完配置必须重启服务才生效sudo systemctl restart clickhouse-server。2.4 集群部署的最小方案单机玩熟之后总会遇到要上集群的场合。ClickHouse 集群不是由服务器管理节点自动组织而是靠“配置文件里的节点列表 创建分布式表”来实现。最简集群需要三个东西多个 ClickHouse 实例每个实例上创建ReplicatedMergeTree表用 ZooKeeper 或 ClickHouse Keeper 做元数据协调再创建一张Distributed表来路由查询。举个例子remote_servers my_cluster shard replica host10.0.0.1/host port9000/port /replica replica host10.0.0.2/host port9000/port /replica /shard /my_cluster /remote_servers然后建表时指定引擎参数CREATE TABLE events_local ( event_date Date, event_type String, user_id UInt64 ) ENGINE ReplicatedMergeTree(/clickhouse/tables/test/events, {replica}) PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_type); CREATE TABLE events_all AS events_local ENGINE Distributed(my_cluster, default, events_local, rand());这里的my_cluster必须和 config.xml 里定义的集群名一致。ReplicatedMergeTree的第一个参数是 ZooKeeper 路径第二个是副本名不同副本之间必须不同。查询时用events_all写入也用events_all数据会自动路由到本地表。这套架构我第一次看时觉得复杂但理清楚“本地表负责读写分布式表负责转发”后就豁然开朗了。3. 数据类型上手之前先把这些坑踩平3.1 数值类型用错整数类型存储和性能双输ClickHouse 的整数类型非常细从 8 位到 64 位都有还区分有符号和无符号。名称类似Int8、Int16、Int32、Int64以及对应的UInt8、UInt16、UInt32、UInt64。什么时候用什么长度我的原则只有一个尽量满足业务最大范围但不要过度预留。比如用户 ID你如果知道是十位数字用UInt64即可如果设计表时随手用Int8等数据到 127 就把表炸了这种低级错误真的有人犯过。而用String存 ID 是我最反对的存储膨胀、排序和数值函数全部失效。浮点数方面ClickHouse 默认用Float32和Float64。但这里必须提醒如果做金额计算或者需要精确比较千万不要用浮点请使用Decimal类型。比如Decimal(18, 2)表示总位数 18、小数位 2。它内部用十进制存储不会出现0.1 0.2 0.30000000000000004这种尴尬。ClickHouse 还支持Decimal32/64/128可以当成更紧凑的整型封装。布尔类型在旧版本里是没有的新版本可以用Bool但底层底层实际会转成UInt8你插true进去查询显示出来还是1。如果你在兼容旧代码最好直接定义UInt8然后在业务层约定 0/1 含义省得被“真假”搞晕。3.2 字符串和日期时间DateTime 的时区是最大的坑字符串类型最常用的是String它其实就是个字节数组不管你是 UTF-8 中文还是二进制内容都能塞进去。还有一个FixedString(N)用来存定长字符串比如国家代码、订单号。如果你存储的内容不是固定长度用FixedString会因填充空格而出问题所以除非能确认定长否则我建议统一用String。日期和时间类型有三个Date、DateTime、DateTime64。Date只存日期范围到 2149 年DateTime存到秒DateTime64可以精确到亚秒比如DateTime64(3)是毫秒DateTime64(6)是微秒。这里有一个非常经典的坑ClickHouse 的DateTime底层是按 Unix 时间戳UTC存储的只是在输出时按当前会话的时区转换。如果你在服务器上建表用DateTime存了一个北京时间然后从客户端查询发现显示时间和插入时一样这是因为服务端和客户端时区一致。一旦客户端设置了不同时区或者你把数据导入到另一个时区的环境时间就会出现偏移。我的做法是所有时间字段统一用DateTime或DateTime64存 UTC查询时用toDateTime(row_time, Asia/Shanghai)转成本地时间展示。宁可在查询时做转换也不要在存储层混入本地时间否则后患无穷。3.3 复合类型Array、Tuple、Map、NestedClickHouse 原生支持数组、元组和 Map这让它可以轻松存储半结构化数据。Array最常用比如Array(String)表示字符串数组适合存标签。建表时字段类型写Array(UInt64)插入时用花括号语法INSERT INTO tags_table (id, tags) VALUES (1, [red, green]);注意ClickHouse 的数组元素类型是强校验的混入不同类型会报错。查询时可以用arrayContains、arrayLength、arrayJoin来做展开。Tuple适合存固定结构的组合字段比如坐标(Float64, Float64)或者用户资料(name String, age UInt8)。Map在较新版本支持可以动态扩展 key不需要在表结构里预先定义字段。Nested则是旧版用来模拟 Map 和结构体的方式不过现在 Map 已经足够好用。如果你接触过 JSON 数据我可以给个建议别急着把整个 JSON 塞进一个String字段然后查询时用函数解析那样性能很差。更好的是在建表时把常用字段拆出来剩下的用Map或JSON类型新版有原生JSON类型实验特性存储。我记得自己曾把日志的JSON原封不动放进String每次按某个属性过滤都要like %key:value%速度慢到怀疑人生。3.4 类型转换toType 函数和 CASTClickHouse 的类型转换很有特色。你可以用cast(x AS type)也可以用更具体的toInt64、toString、toDateTime等函数。建议优先用to*系列因为语义清晰而且对字符串解析有一些容错。默认情况下CAST(String TO UInt64)遇到非法字符会报错比如12abc转不了。而toUInt64函数加silent后缀如toUInt64OrZero(s)转不了就返回0。这在清洗脏数据时非常有用。还有一点ClickHouse 查询中字符串和数值直接比较时会做隐式转换但某些函数调用时我经常得到ILLEGAL_TYPE_OF_ARGUMENT错误所以尽量显式转换别靠隐式。4. SQL 使用写 ClickHouse 查询和写 MySQL 的区别4.1 建表语句和 MyBatis-Plus 生成 SQL 的差异很多从 Java 后端转来的朋友习惯用 MyBatis-Plus 的代码生成器直接根据TableName和实体类生成CREATE TABLE。但 MyBatis-Plus 默认生成的 SQL 是 MySQL 方言比如CREATE TABLE user (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))。这种表结构如果直接搬到 ClickHouse基本是不行的。ClickHouse 建表必须指定存储引擎常见MergeTree系列并且没有自增主键、没有唯一约束主键也不是用来唯一标识行的而是用于排序和索引。你得把 MySQL 的思维调整过来。例如一个用户事件实体在 MySQL 里可能是CREATE TABLE user_event ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, event_type VARCHAR(50), create_time DATETIME ) ENGINEInnoDB;到 ClickHouse 中的等价写法是CREATE TABLE user_event ( id UInt64, user_id UInt64, event_type String, create_time DateTime ) ENGINE MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (create_time, user_id);注意这里没有主键没有auto_increment。MergeTree的ORDER BY决定了数据在物理上的排列顺序是查询性能的核心。这也解释了为什么 ClickHouse 建表前必须想清楚“我最多的过滤字段是哪些”。如果滥用“第一个字段当主键”的思路可能把查询性能搞到不如 MySQL。如果你真的想从 Java 实体类自动生成 ClickHouse 的建表语句可以自己写个模板遍历类字段把Long映射成UInt64或Int64String映射成StringLocalDateTime映射成DateTime然后用MergeTree()引擎拼成 DDL。网上也有开源插件能做到但核心还是需要理解映射规则。4.2 写入数据INSERT 的姿势决定性能ClickHouse 的写入是“批量写”思维。单条单条INSERT INTO table VALUES (...)也能用但性能极差因为它会产生大量小文件后台 merge 线程忙不过来磁盘 IO 也压力大。推荐的做法是每批次至少写几千行以上比如用clickhouse-client从文件导入clickhouse-client --query INSERT INTO user_event FORMAT CSV data.csv或者在 Java 代码里用 JDBCPreparedStatement批量提交每 1 万行提交一次。我在生产环境中发现单条插入和批量插入性能相差几十倍。“小写入 高频”是 ClickHouse 的大忌如果你有实时流建议先写到 Kafka再用消费端攒批灌入。4.3 查询语法SELECT、WHERE、GROUP BY 的独特行为基础查询和标准 SQL 非常像SELECT event_type, count() AS cnt FROM user_event WHERE create_time now() - INTERVAL 1 DAY GROUP BY event_type ORDER BY cnt DESC LIMIT 10;注意几个差异点聚合函数用的计数是count()不需要*或列名也能统计行数。GROUP BY后不能像 MySQL 那样直接选非聚合列MySQL 的 ONLY_FULL_GROUP_BY 也有类似限制否则报错逻辑上更严格。查询时WHERE里面不能用SELECT子句定义的别名比如WHERE cnt 10在 ClickHouse 会直接报错必须用HAVING cnt 10。LIMIT语法也很刺激它支持LIMIT offset BY这种特殊写法而且默认LIMIT n如果没有ORDER BY返回的行是不确定的。很多人踩过坑查询了一个带GROUP BY但没ORDER BY的聚合结果取LIMIT 10以为是最新数据其实是随机数据。所以务必先排序再取数。4.4 窗口函数ROW_NUMBER、RANK、SUM OVERClickHouse 支持窗口函数语法和标准 SQL 基本相同。我用得最多的是取每组最新一条记录SELECT * FROM ( SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM user_event WHERE create_time now() - INTERVAL 7 DAY ) WHERE rn 1;在旧版本中窗口函数性能一般新版本已经优化不少但还是建议控制分区的大小。我还常用sum(amount) OVER (PARTITION BY user_id ORDER BY create_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)来做累计值比如用户消费金额的累计趋势。窗口函数不能和普通聚合混用需要注意比如不能在同一个SELECT里既GROUP BY又直接引用窗口函数除非窗口函数嵌套在外层子查询中。4.5 DELETE 和 UPDATE不要像 MySQL 一样用ClickHouse 本质上不是为行级更新设计的虽然ALTER TABLE ... DELETE WHERE ...和ALTER TABLE ... UPDATE ... SET ... WHERE ...语法是支持的但它不是实时变更而是异步的 mutation 操作后台会生成新的数据版本并重新标记老版本非常消耗资源。我的建议是能不做就尽量不做。如果非要修正少量历史数据也尽量在低峰期执行并控制影响行数。如果你需要频繁更新状态字段而且每次只改很少的行那选错库了。ClickHouse 更适合把数据看作“不可变事件流”所有变化都通过追加新事件来表达。比如用户改地址你插入一条新地址事件而不是 update 旧记录。4.6 常用函数和防止 SQL 注入的习惯ClickHouse 函数极其丰富日期函数有toDate、toMonth、toStartOfDay、dateDiff字符串有substring、splitByString、replaceAll数组有arrayJoin、arrayFilter空值处理有ifNull、coalesce、nullIf。有一点要提醒ClickHouse 虽然不常见 SQL 注入问题但如果你把它接入了 Web 查询接口仍然要防外部的拼 SQL 攻击。我见过一个项目在报表系统里用字符串拼接用户传参来构造查询条件后来被人在参数里塞了; DROP TABLE恶作剧虽然是异步 mutation 也够吓人的。永远用参数化查询或转义函数别让用户输入直接进 SQL。5. 实战中的常见问题和性能调优记录5.1 内存超限Memory limit exceeded是必修课运行大查询时最常见报错就是DB::Exception: Memory limit exceeded。原因通常是单条查询在聚合时把中间结果都放进了内存。解决思路有几个调高用户配置里的max_memory_usage但不能无限调一般不要超过系统内存的 60%。降低查询并发数。并发高时每个查询都抢占内存优化单个查询往往还不如限制并发。使用更省内存的近似聚合函数比如uniqCombined代替uniq用quantile(0.99)(value)代替精确排序后取分位数。你的 SQL 里如果存在GROUP BY后回来ORDER BY一个大结果集要小心排序内存。可以在SELECT后加LIMIT强制减少输出行数。我自己遇到过跑夜间全量聚合任务明明单查询内存限制是 20GB任务总是挂后来发现同事把并发调到了 64相当于 64 个查询各吃 20GB机器 128GB 直接爆掉。最后在users.xml里把max_concurrent_queries_for_user调成 4问题解决。5.2 查询慢排查先看扫描行数和分区裁剪ClickHouse 查询慢很多时候不是引擎慢而是你没有把“排序键”利用好。首先看settings里max_threads是否被限制然后看表的分区字段是否和WHERE条件匹配。比如表按toYYYYMM(create_time)分区但你查询只用event_date today()如果两者粒度不一致分区裁剪就失效了。我在一次优化中把WHERE从create_time ...改成同时带上event_date today()扫描行数从几亿降到几百万速度直接从十几秒降到几百毫秒。EXPLAIN SYNTAX只能看语法树要分析执行计划可以开启EXPLAIN PIPELINE和EXPLAIN ESTIMATE。记住一个原则ORDER BY排序键放在 WHERE 的等值条件上最有效其次才是范围条件。如果你经常用某个字段做GROUP BY试着把它放到ORDER BY靠前的位置而不是只依赖主键。5.3 时区问题报表时间和数据源时间差了 8 小时这个问题在写DateTime存储时已经说过。再补充一个实务如果你用clickhouse-client查询发现时间显示和自己预期不一致先检查服务端时区和客户端会话时区。可以用下面这个 SQL 查看当前时区SELECT timezone();如果返回 UTC而你插入的数据不带时区标记那么toDateTime(2024-01-01 12:00:00)会被当成 UTC 时间存储展示时如果你的会话也是 UTC自然结果还是 UTC。如果你希望直接用北京时间可以启动客户端时加--timerzone Asia/Shanghai。不过多人协作时最容易出乱子所以还是回到我的建议存储用 UTC展示用toDateTime转换。5.4 空值和去重和 MySQL 的直觉不一样ClickHouse 对NULL的处理需要单独说。建表时如果字段是普通String你插入的值里没有NULL这个字面量甚至不允许直接写NULL默认列是NOT NULL。要为某列允许空值必须显式声明Nullable(String)或Nullable(DateTime)。但我不建议滥用Nullable因为它会增加额外的字节标记存储和查询效率都下降不少。对于缺失数据我常用default_value或特殊值比如0、空字符串来代替NULL这样更贴合 ClickHouse 的高性能倾向。去重用SELECT DISTINCT或uniq()聚合都可以。注意uniq()是精确计数uniqExact()是精确但更慢的版本uniqCombined()是近似计数但极快。对于十亿级客户 ID 统计我优先选uniqCombined误差通常可接受速度却快了数倍。5.5 类型不匹配和隐式转换的坑经常遇到DB::Exception: Type mismatch或者cannot convert。比如你把UInt64和Int64一起做运算ClickHouse 会找公共超类型但有时它不会自动帮你转需要显式cast。以前我用String字段存数字后做SUM直接报错后来先toUInt64OrZero再汇总。在INSERT时如果 CSV 里的数字带引号或者字符串列里混入了非 UTF-8 字节也会触发类型解析失败。建议在导入前做数据清洗而不是依赖服务端容错。5.6 其他容易忽视的运维细节表名和列名默认区分大小写吗表名区分大小写列名不区分实际上 ClickHouse 对列名大小写在存储层没有严格强制但查询时尽量统一。我在建表时强制自己全部用下划线小写免得上线前改大小写。数据目录权限问题。如果你改了path配置而新目录属于别的用户ClickHouse 会启动失败。用chown clickhouse:clickhouse改好权限再重启。日志量很大时默认日志表system.query_log会不断增长建议设置query_log.ttl或定期清理避免把磁盘塞满。副本之间数据差异先看system.replicas表里的is_leader和zookeeper_exception遇到 ZK 超时问题是常事把 ZooKeeper 心跳参数调大一点能缓解。6. 最后分享两个我在实践中养成的习惯第一个习惯是每次建表前先写下三个最常见的查询倒推ORDER BY和PARTITION BY。很多 ClickHouse 表被搞垮不是因为数据太多而是建表没想清楚排序键。你用ORDER BY (user_id, create_time)却总按event_type过滤那查起来等于全表扫再高性能的引擎也白搭。第二个习惯是写入永远走批量。我一直要求团队在代码里强制把写入攒成 5 万行以上再提交除非是业务必须的准实时追踪。这样做不仅让查询更快也让后台 merge 的压力小很多。毕竟 ClickHouse 真正擅长的是“一次性的大口灌入”而不是高频小口喷洒。把安装、类型、SQL 这些基础打牢之后再去看它的分布式副本、物化视图和采样查询你会发现 ClickHouse 的设计哲学处处都围绕两个字极致的查询效率。希望你读完这篇能少走我当初走过的弯路。
阅读完成 · 觉得有帮助？
