★ FEATURED ARTICLE 我第一次在 ClickHouse 里执行UPDATE语句的时候心里是发虚的。MySQL 干习惯了改一行数据、删几条记录都是秒回的事情但到了 ClickHouse 这里执行完ALTER TABLE ... UPDATE返回倒是挺快跑了两秒我看后台mutations任务还挂着part 数量一直在涨我就知道“增删改查”这四个字在 ClickHouse 这里真不能按老经验来。这篇文章不是 ClickHouse 官方文档的翻译版而是我从业务开发视角把建库、建表、增删改查、字段维护这些最基础的操作重新捋了一遍的实操笔记。会用大量对比和反直觉提醒告诉你为什么 ClickHouse 的“删改”这么重怎么做才能少踩坑。不管你是刚从 MySQL 迁移过来还是准备把 ClickHouse 接入现有系统照着这篇文章的路径走一遍基本能把日常 DML 和 DDL 操作打通。1. 先搞清楚 ClickHouse 和 MySQL 的“增删改查”差别1.1 同样是增删改查为什么 ClickHouse 这么别扭很多人第一次接触 ClickHouse 都是因为它快查询快、聚合快、导入快。但真上手做业务第一反应往往是这玩意儿怎么连个正经的UPDATE都没有DELETE也这么费劲这是不是个半成品还真不是。ClickHouse 的设计目标从一开始就写得很明白面向在线分析处理OLAP而不是面向事务处理OLTP。它在底层用了列式存储加了 LSM-Tree 风格的合并树MergeTree家族引擎每个批次写入的数据都会落成一个个不可变的“数据部件”part。既然底层数据不可变那所谓更新和删除实际上就是“把旧数据标记成废弃再写入一份新数据”最后由后台线程合并清理。所以你会看到ClickHouse 的增删改查不是做不到而是实现路径完全不同。理解了这一层后面所有操作你都能顺下来。1.2 典型 OLTP 场景里那些“理所当然”的操作在这里行不通我整理了一个对照表方便你从 MySQL 思维快速切换到 ClickHouse 思维操作MySQL 做法ClickHouse 做法需要注意的问题单条插入INSERT INTO xxx VALUES (...)INSERT INTO xxx VALUES (...)高频单条插入会产生海量小 part性能急剧下降批量插入多行 VALUES多行 VALUES /INSERT ... SELECT强烈建议攒批后一次写入修改UPDATE t SET x1 WHERE ...ALTER TABLE t UPDATE x1 WHERE ...异步 mutation重量级操作删除DELETE FROM t WHERE ...ALTER TABLE t DELETE WHERE ...同样异步执行且删除不会立即释放磁盘空间清空表TRUNCATE TABLE tTRUNCATE TABLE t这个倒是同步的但要小心权限删分区无直接概念ALTER TABLE t DROP PARTITION xxx按分区清理数据非常高效强烈推荐这张表里的信息量其实挺大的尤其是“修改”和“删除”这两行。你可能会问为什么 ClickHouse 的 UPDATE/DELETE 要用ALTER TABLE开头因为从语义上讲这个操作不是 DML而是 DDL 性质的 mutation突变操作它会触发一次全表级的重写逻辑。后面我会专门讲它到底是怎么工作的。1.3 什么场景下不要用 ClickHouse 当 OLTP这不是废话我见过有人拿 ClickHouse 存订单主数据然后频繁更新订单状态跑一天就把磁盘撑爆了。如果你需要高频 UPDATE/DELETE、单条随机写入、行级事务请放过 ClickHouse它真的不是干这个的。选择什么存储不是看它“能不能用”而是看它“合不合适”。反过来日志存储、行为分析、用户画像标签宽表、监控指标存储、甚至部分冷热数据分离场景这才是 ClickHouse 的舒适区。你只需要把“增删改查”的频率降下来改成“批量写、批量查、低频删、低频改”体验会完全不一样。2. 库表创建基础得不能再基础但特别坑2.1 创建数据库看起来和 MySQL 一样其实有讲究ClickHouse 创建数据库的语法非常简单基本就是CREATE DATABASE IF NOT EXISTS analyzer ENGINE Atomic COMMENT 分析库;ENGINE这里一般用默认的Atomic就行它支持原子性的表创建、替换和删除操作。老版本默认是Ordinary那种引擎现在基本不推荐了因为它压根不支持原子操作删表的时候还容易出现半删状态。还有一个需要了解的参数是ON CLUSTER。如果你用的是集群模式建库最好加上CREATE DATABASE IF NOT EXISTS analyzer ON CLUSTER company_cluster;company_cluster是你配置的集群名称在/etc/clickhouse-server/config.d/remote_servers.xml里定义。加上这个关键字所有分片节点都会执行同一套 DDL省得你一台台机器跑。另一个隐藏点数据库本身可以指定存储策略。比如你有冷热分层需求建库时直接指定CREATE DATABASE analyzer ENGINE Atomic SETTINGS storage_policy tiered_ssd_hdd;这样库里新建的表如果没单独设置默认会继承这个存储策略。适合那种“热数据放 SSD冷数据落 HDD”的场景省心很多。注意ClickHouse 建库建表都不支持IF NOT EXISTS之外的“存在即更新”逻辑。如果你要调整已有表结构老老实实走ALTER别想用CREATE TABLE IF NOT EXISTS糊弄过去不会帮你改任何东西。2.2 建表引擎、排序键、分区键、主键决定一切建表是 ClickHouse 里最有门道的一步它比 MySQL 多出来的几个概念会直接影响后续查询性能和运维成本。先看一个完整的建表例子CREATE TABLE IF NOT EXISTS analyzer.event_log ( event_date Date, event_time DateTime, user_id UInt64, event_type String, cost Float64, remark String DEFAULT 无 ) ENGINE MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (user_id, event_time) PRIMARY KEY (user_id) TTL event_date INTERVAL 90 DAY SETTINGS index_granularity 8192;简单拆解一下PARTITION BY分区键决定了数据文件按什么维度切分。一般按日期分区按小时也行但不建议颗粒度太细。分区太多会导致后台任务过多影响性能。ORDER BY排序键这不仅是物理存储顺序也是MergeTree生成稀疏主索引的依据。通常把查询中WHERE条件最常出现的字段放在前面。PRIMARY KEY主键ClickHouse 的主键允许重复它只是用来构建索引不强制唯一。所以千万不要拿 MySQL 的“主键唯一”思维来理解它。TTL行级生命周期到期数据会被自动清理或者移动冷存储。index_granularity索引粒度默认 8192意思是每隔 8192 行记录一条索引标记。非必要不建议改默认值在大多数场景下均衡性最好。这里有个很容易被误解的点很多人都以为PRIMARY KEY和ORDER BY必须完全一样其实不然。ORDER BY决定排序和索引PRIMARY KEY是索引字段的子集。实际使用中为了压缩率或者查询过滤你可以让PRIMARY KEY比ORDER BY短比如ORDER BY (a, b, c)PRIMARY KEY (a)。2.3 一个贴近业务的建表实例和注释再给一个我实际用过的例子场景是用户行为事件流CREATE TABLE IF NOT EXISTS app.user_track ( day Date, -- 事件日期分区字段 ts DateTime, -- 事件时间 uid UInt64, -- 用户ID session_id String, -- 会话ID event_name LowCardinality(String), -- 事件名 props String DEFAULT {}, -- JSON 扩展属性 duration UInt32 -- 时长单位毫秒 ) ENGINE MergeTree PARTITION BY day ORDER BY (uid, ts) SETTINGS index_granularity 8192;这个表里我用了LowCardinality(String)如果你的事件名就那么几十种这个类型能把压缩率做得非常漂亮查询时也快。props用字符串存 JSON适合那些不固定的私有字段避免动不动就加列。建表的时候一定要想清楚几个问题数据多久查一次按什么维度查要按时间删数据吗如果这些没想好表建完以后再改字段虽然 ClickHouse 支持但代价比 MySQL 高不少。3. 增删改查实操SQL 写法与执行效果3.1 INSERT 插入批量才是王道ClickHouse 插入数据的语法和 MySQL 差不多只是使用习惯完全不同INSERT INTO app.user_track (day, ts, uid, session_id, event_name, props, duration) VALUES (2025-01-01, 2025-01-01 10:00:00, 1001, s1, click, {page:home}, 200), (2025-01-01, 2025-01-01 10:00:05, 1002, s2, click, {page:list}, 350);这条语句本身没问题但如果你把它跑在热循环里每条一个 insert那噩梦就开始了。ClickHouse 每写入一批数据都会生成一个新的 part积累几百几千个小 part 之后后台合并线程忙不过来查询性能会直线下降。官方其实是建议单次批次至少 1000 行以上或攒够几 MB 再写入。更推荐的做法是直接用INSERT ... SELECT或者从文件导入clickhouse-client --query INSERT INTO app.user_track FORMAT CSV ./events.csv或者INSERT INTO app.user_track SELECT toDate(ts), ts, uid, session_id, event_name, props, duration FROM app.user_track_stage WHERE ts yesterday();这种搬数模式在生产环境特别常见。要注意的是写入过程中如果客户端断连ClickHouse 可能部分写入成功需要靠insert_deduplication机制去重。默认情况下ClickHouse 会记录最近插入的块哈希如果同一批数据重复提交到同一张表后提交的会被丢弃。想用这套去重前提是你往同一种表引擎比如ReplicatedMergeTree里反复插入相同的块且数据块要足够大一般超过 100 万行或者够大的字节数否则可能不触发。3.2 SELECT 查询存量优势但有几个容易踩的坑查询这块 ClickHouse 确实强单表几十亿行做聚合也能秒回。但有几个点和 MySQL 不一样新手很容易踩第一FINAL关键字。如果你用了ReplacingMergeTree、AggregatingMergeTree这类带“事后合并”语义的引擎普通SELECT查到的可能是尚未合并完的中间态数据。想强制拿最终结果得加FINALSELECT * FROM app.user_track FINAL WHERE day today();FINAL查询会显著降低性能尤其在数据量大的时候好使但别滥用。第二LIMIT语义。ClickHouse 默认返回的“第一行”不是传统意义上的“最早插入的行”因为存储是按排序键排列的。所以如果你不指定ORDER BY别猜顺序没意义。第三复杂子查询和 JOIN 能力很有限。虽然新版 ClickHouse 一直在增强 JOIN但和 MySQL 的优化器成熟度还是有差距。能用宽表解决的问题就不要在 ClickHouse 里搞一堆 JOIN很容易跑出超慢查询。3.3 UPDATE/DELETE重量级操作理解 mutation 才是关键这是 ClickHouse“增删改查”里水最深的部分。语法很简单-- 更新 ALTER TABLE app.user_track UPDATE event_name purchase WHERE day 2025-01-01 AND uid 1001; -- 删除 ALTER TABLE app.user_track DELETE WHERE day 2025-01-01 AND session_id s_bad;看起来轻松吧执行也很快返回。但背后实际发生的是ClickHouse 为这条操作生成一个 mutation 任务记录到system.mutations系统表。后台线程开始扫描所有匹配条件的 part能找到的就重构出“新 part”替换旧 part旧 part 标记为废弃。如果 part 数量多、数据量大这个过程会相当慢期间 CPU、磁盘 IO 都会飙升。mutation 完成前你查询到的是新旧 part 混合的数据版本。所以UPDATE和DELETE不是立即生效的是异步被合并执行的。这也是为什么官方一直建议大家设计时就考虑好删改策略。我自己的经验是日常能不带WHERE做全表更新就别带尽量把 mutation 的扫面范围控制在分区级别。例如按天分区情况下删除某一天的数据与其ALTER TABLE app.user_track DELETE WHERE day 2025-03-01;不如直接ALTER TABLE app.user_track DROP PARTITION 2025-03-01;后者瞬间完成磁盘空间立刻释放前者还要等 mutation 慢慢吞吞地清理。这是 ClickHouse 数据治理里最实用的一条。强烈提醒不要高频执行 ALTER UPDATE/DELETE。比如每秒钟去更新一行数据Mutation 队列会堆到几百上千最后集群整体变慢甚至把副本弄挂。3.4 其他“删改”替代方案如果你只是想清理过期数据优先考虑表级TTL建表时设好行级 TTL数据到期自动删不需要手动写 mutation。如果你想定期滚动清理历史分区可以写个定时任务ALTER TABLE app.user_track DROP PARTITION 2026-01-01;如果你用的引擎是ReplacingMergeTree那么“更新”可以变成“插入新行”查询时用FINAL取最新版本。这种以代换更的方式在实时数仓里很常见可以绕开 mutation 带来的压力。4. 字段管理ALTER TABLE 的几条主要路径4.1 添加字段给 ClickHouse 表加字段和 MySQL 基本一样ALTER TABLE app.user_track ADD COLUMN platform String DEFAULT web AFTER event_name;这里AFTER指定新列的位置不加的话默认加到末尾。FIRST可以加在最前面不过一般不建议随便调列顺序影响不大但没必要。需要注意的是给很大的表加字段ClickHouse 是“秒级”完成的因为它只修改了元数据不触发数据重写。新的数据写入时会带上默认值已经存在的老数据在查询时会按默认值返回。这点比 MySQL 8.0 之前的版本强太多那种加一列锁表锁半天的痛换过来就懂有多爽了。如果你想一次加多个字段ALTER TABLE app.user_track ADD COLUMN device_type String DEFAULT pc, ADD COLUMN os_version String DEFAULT ;4.2 删除字段删除字段同样轻量ALTER TABLE app.user_track DROP COLUMN os_version;DROP COLUMN也是只改元数据不立刻物理删除底层文件。真正释放空间发生在后续数据合并过程中。所以这里有个常见误区你删了一个大字段然后立刻去查磁盘空间发现没降多少以为是没执行成功。其实不是等 merge 跑完自然会释放。但注意如果该字段被物化视图、TTL、ORDER BY或分区键引用了删除会直接报错。所以操作前最好看一眼表的完整定义该撤销的先撤销。4.3 修改字段类型、默认值、注释、TTL改字段类型用MODIFY COLUMNALTER TABLE app.user_track MODIFY COLUMN duration UInt64;这里有个很现实的坑ClickHouse 的类型转换不是所有都能直接做。String改成FixedString可能可以String改成数值类型大概率失败尤其当字段里存在无法解析的值时。转换过程中需要重写所有相关数据所以这个操作也是重量级的会生成 mutation。适合修改的场景通常是改默认值MODIFY COLUMN remark DEFAULT 无备注只影响后续写入历史数据不重写轻量。改注释MODIFY COLUMN remark COMMENT 用户备注纯元数据变更。改 TTLALTER TABLE app.user_track MODIFY TTL event_date INTERVAL 120 DAY也会做数据重写算是重量级操作尽量放在低峰期执行。还有一点改字段类型时Date和DateTime之间互转要注意时区问题。建议先小批量验证再跑正式变更。4.4 重命名字段ALTER TABLE app.user_track RENAME COLUMN duration TO event_duration;这个操作也是元数据级很快。但是同样如果该列被物化视图、分析查询引用重命名后相关视图会失效。我在生产环境吃过一次亏改了一个公共字段名结果下游三个报表全部报列不存在。所以关联对象多的时候重命名前老老实实全链路先查一遍。5. part 命名、数据碎片与删改背后的原理5.1 part 到底是什么要理解 ClickHouse 的删改行为就必须理解 part。简单说每次INSERT写入的数据在 MergeTree 里会存放为目录下一个不可变的“数据片段”这就是 part。一个 part 里面保存了列字段对应的数据文件、索引文件、校验文件等。不同批次写入的数据如果分区键相同会生成不同的 part。当你执行SELECT时ClickHouse 会读取所有相关 part再在内存里做合并计算。part 数量越多查询时需要扫描的文件就越多性能自然变差。所以我们日常要尽量少产生小 part多做大批次写入。5.2 part 命名规则及含义ClickHouse 里每个 part 目录名字是有来头的标准格式大致是partition-id_minBlockNum_maxBlockNum_level比如20250101_1_5_2拆开解释一下20250101分区 ID。这里就是日期2025-01-01。1_5这个 part 包含了该分区下的数据块编号从 1 到 5。每次有新数据写入都会生成一个新的 block 编号编号是全局单调递增的。2合并层级level。0 表示刚写入形成的原始 part随着后台 merge 合并level 逐渐增大。如果你在用ReplicatedMergeTreepart 名后面还会有一段 UUID20250101_1_5_2_3f8d1a2c-xxxx这段 UUID 是副本间区分 part 用的每个副本的 part 文件可能名字不同但内容一致。看懂 part 名有什么用第一排查问题的时候你通过system.parts表能看到 part 的生成和合并情况能判断数据是不是卡住了第二如果 part 数量异常多看看是不是写入频率过高、合并线程卡住或者是某个分区长期没有 merge。下面这个查询就很实用SELECT partition, count() AS part_count, sum(rows) AS total_rows, sum(bytes_on_disk) / 1024 / 1024 AS size_mb FROM system.parts WHERE table user_track GROUP BY partition ORDER BY partition DESC LIMIT 10;我用这招定位过“数据倾斜”和“小文件爆炸”问题基本上瞄一眼 part 数量就能猜出个大概。5.3 为什么 UPDATE/DELETE 不能立即见效回到第三节的 mutation。你执行完ALTER TABLE ... UPDATE之后ClickHouse 不是直接在原有 part 上改数据而是做了一次“按分区 按 part 重写”匹配条件的 part 被复制出来修改后再生成一个新 part旧 part 标记成逻辑删除等待后台清理。所以你会看到执行完 mutation 后part 数量瞬间变多、磁盘空间短暂上升这都是正常的。要等后台 merge 把新旧 part 合并干净空间才会降下来。也是因为这个机制mutation 不适合高频执行。你想想每执行一次全表更新相当于把相关数据整体重写一遍成本比 MySQL 的 BTree 原地更新高了一个数量级。5.4 手动优化让 part 快速合并虽然后台会自动 merge但如果你做完大范围删改或者灌完一批历史数据之后想让数据立刻达到最优查询状态可以手动触发OPTIMIZEOPTIMIZE TABLE app.user_track FINAL;FINAL表示把所有 part 合并到最优的为止。这个操作很消耗资源尤其大表建议低峰期或者只针对具体分区执行OPTIMIZE TABLE app.user_track PARTITION 2025-01-01 FINAL;不过我一般建议能不开就不开。ClickHouse 后台自动 merge 已经够用了除非你急等空间释放或者要立刻跑一份全量报表否则手动 optimize 反而容易给集群添乱。mutation 和 merge 任务都可以通过系统表观察SELECT command, create_time, is_done FROM system.mutations WHERE table user_track;is_done 0表示任务还没完成之后会变1。如果长期不完成看看是不是集群压力太大或者有副本挂了。6. 常见问题与排查技巧实录6.1 删了数据磁盘空间却一直不释放这个几乎每天都会有人问。原因很简单ALTER TABLE ... DELETE是逻辑删除旧 part 还在磁盘上要等后台 merge 清理。分区删除DROP PARTITION才是立即释放空间的。解决办法也直接优先用DROP PARTITION删整分区数据非要用 mutation 删除局部数据就等后台 merge 跑完或者低峰期手动OPTIMIZE TABLE ... FINAL加速。6.2 修改字段类型一直卡在 mutation 队列有一次我在一个 20 亿行的表上改字段类型执行了MODIFY COLUMN后system.mutations里排队排了几个小时。后来才发现是因为当时有一批INSERT任务和大查询把机器 CPU 打满了mutation 一直在等资源。排查思路先看system.mutations确认任务是不是is_done 0。再看system.processes看有没有大查询占资源。最后看system.merges确认 merge 任务是否积压。如果是高峰期建议直接等错峰执行。如果 mutation 队列里有大量没用的任务可以通过KILL MUTATION清理掉KILL MUTATION WHERE database app AND table user_track;这个操作要谨慎只取消还没开始执行的 mutation已经跑完的改不回来。6.3 part 数量爆炸查询越来越慢part 数量多的原因基本就三个写入太碎、merge 太慢、分区键设置不合理。排查写法SELECT partition, count(), sum(bytes_on_disk) / 1024 / 1024 AS size_mb FROM system.parts WHERE active 1 AND table user_track GROUP BY partition ORDER BY count() DESC;如果看到某个分区下有几十个甚至上百个 part先检查写入端是不是在低频小批量插入。如果是改成攒批写入或者用 Buffer 表做缓冲。也可以临时调大 merge 线程或提高max_bytes_to_merge_at_max_space_in_pool但这属于治标不治本核心还是控制写入频率。6.4 一个常用操作速查表操作命令示例提醒创建数据库CREATE DATABASE IF NOT EXISTS app ON CLUSTER ck_cluster;集群环境加 ON CLUSTER创建表CREATE TABLE IF NOT EXISTS app.log (...)先想好排序键和分区键插入数据INSERT INTO app.log VALUES (...)批量、批量、批量查询SELECT ... FROM app.log WHERE day today()按分区扫速度快很多更新ALTER TABLE app.log UPDATE ... WHERE ...异步 mutation低峰执行删除ALTER TABLE app.log DELETE WHERE ...能用 DROP PARTITION 就用清空表TRUNCATE TABLE app.log同步操作但要确认权限删除分区ALTER TABLE app.log DROP PARTITION 2025-03-01立竿见影磁盘空间立刻释放添加字段ALTER TABLE app.log ADD COLUMN remark String DEFAULT 秒级只改元数据删除字段ALTER TABLE app.log DROP COLUMN remark空间不会立刻释放改字段类型ALTER TABLE app.log MODIFY COLUMN remark FixedString(16)重量级改前验证改字段注释ALTER TABLE app.log MODIFY COLUMN remark COMMENT 备注纯元数据改字段默认值ALTER TABLE app.log MODIFY COLUMN remark DEFAULT 无只影响后续写入重命名字段ALTER TABLE app.log RENAME COLUMN remark TO comment小心下游依赖查 partSELECT * FROM system.parts WHERE tablelog排查数据合并利器手动合并OPTIMIZE TABLE app.log FINAL慎重低峰用查 mutationSELECT * FROM system.mutations WHERE tablelog卡住时先看这个6.5 关于副本和集群环境的补充如果你用的是ReplicatedMergeTree系列那ALTER操作会自动分发到所有副本不需要在每台机器上单独执行。但要注意mutation 任务在每个副本上都要跑一遍所以集群越大、副本越多总量会成倍增加。做大的表结构变更时别只盯着一台机器看结果要等所有副本都完成才算是真正结束。查看副本同步情况SELECT database, table, replica_name, is_readonly, absolute_delay FROM system.replicas;如果某个副本的延迟特别大先别执行新的 mutation让它追平再说。最后分享几个实战小习惯我自己维护 ClickHouse 集群已经有一段时间了踩过的坑不少。给大家几个压箱底的习惯能帮你少走很多弯路。第一任何ALTER操作特别是UPDATE、DELETE、MODIFY COLUMN执行前先看一眼system.mutations里有没有积压任务。有就先等没积压再上。别搞出一堆 mutation 排队最后全卡住。第二不要每张表都设置过多的分区维度和过细的索引粒度。分区到天是常态到小时是特例索引粒度默认 8192 就别动除非你非常清楚自己在干嘛否则调低只会让索引文件更大查询也没变快。第三日常尽量用“多批次 攒量写入”来替代频繁小 INSERT。我在生产环境里要求写入端至少攒够 10 万行或者 30 秒刷一次这样 part 数量很稳定后台 merge 压力也小查询性能自然稳。第四如果是数据仓库场景我强烈建议给每张表设计一个“生命周期策略”数据保留多久、过期怎么处理、哪个字段做 TTL。早设计好后面就不用天天跑删除任务了。ClickHouse 的学习曲线不算低尤其是从 MySQL 思维转过来的时候会被“异步删改”这个概念卡很久。但一旦理解了它底层不可变 part、后台合并、mutation 重写的模型所有操作都能推演出来。希望这篇笔记能帮你省掉一些不必要的试错时间。
阅读完成 · 觉得有帮助？
