ClickHouse在国内后端圈子里越来越常见了但很多人第一次上手就被它的“增删改查”搞懵——建表、插数据都很顺手一执行UPDATE或者DELETE就发现性能完全不是想象中那样SQL语法看着跟MySQL差不多实际行为却完全不是一个路数。这篇文章就围绕ClickHouse最常用的创建数据库、创建表、插入查询、修改删除数据、以及添加字段、修改字段、删除字段这些基础操作把背后那些“为什么慢”“为什么突然报错”“为什么数据没删掉”的底层原因讲清楚再给出可以照着抄的实操步骤。适合刚从MySQL、PostgreSQL转过来的后端开发也适合已经在用ClickHouse但被mutation机制、part合并这些概念坑过的同学。1. 先搞清楚ClickHouse的增删改查到底哪儿不一样很多人拿MySQL的经验直接套ClickHouse第一件事就是踩坑。ClickHouse不是传统OLTP数据库它的定位是OLAP核心设计目标是海量数据下的分析查询速度所以它在底层采用的是列式存储 不可变数据文件part的架构。这个架构带来巨大查询性能收益的同时也让增删改查的行为变得非常“另类”。1.1 为什么“能增能查不能随便改删”在ClickHouse里INSERT的数据并不是马上写入原文件而是生成一个个不可变的part文件。你可以把part理解成一张数据“快照”查询的时候ClickHouse会扫描所有相关的part再做合并。UPDATE和DELETE最终也不是直接改原文件而是通过一种叫mutation的机制生成新part再在后台把旧part替换掉。这个设计跟LSM树有点像写入速度飞快但更新和删除会被拆成两步第一步提交mutation任务第二步由后台任务异步执行数据重写。所以执行完ALTER TABLE ... UPDATE并不代表数据立刻变了你立刻去查可能还是旧数据这就是很多人觉得“ClickHouse改了没生效”的根本原因。还有一个关键点ClickHouse没有传统意义上的行级锁、事务隔离等机制。单条更新、单条删除的成本极高因为它要遍历满足条件的part做重写。这也是为什么做技术选型时ClickHouse只适合放日志、行为事件、监控指标这类“写多改少、删很少”的数据。1.2 环境准备与客户端连接实测下来最简单的方式是直接下载官方提供的单机版二进制包或者用Docker起一个容器命令都差不多。docker run -d --name clickhouse-server \ -p 8123:8123 \ -p 9000:9000 \ -e CLICKHOUSE_DBdefault \ -e CLICKHOUSE_USERdefault \ -e CLICKHOUSE_PASSWORD123456 \ clickhouse/clickhouse-server:latest端口方面9000是native协议端口用clickhouse-client连8123是HTTP端口用curl、JDBC、各类可视化工具连。进容器执行clientdocker exec -it clickhouse-server clickhouse-client \ --host 127.0.0.1 --password 123456 --multiquery注意ClickHouse的SQL默认是大小写敏感的字段名、表名如果带大写字母最好统一用反引号包起来。这一点和MySQL默认大小写不敏感不同很多人第一次写SQL在这里翻车。2. 建库建表把家底先盘明白建库建表听起来简单但ClickHouse的表引擎、排序键、分区键互相影响选错后面改起来非常麻烦。所以这一节我尽量把最关键的决策点拆开讲。2.1 创建数据库与常用参数创建数据库的语法非常简单CREATE DATABASE IF NOT EXISTS app_log ENGINE Atomic COMMENT 应用日志库;默认的Atomic引擎就是普通数据库支持原子性操作通常不需要改。但有一点要注意数据库本身有权限体系如果业务上要隔离数据访问建议一个业务一个库不要全部塞到default里。实际操作中我还会在创建库后立刻检查一下权限SHOW CREATE DATABASE app_log;这套命令在日常排障里很实用尤其是你接手别人搭建好的集群时先看库的表引擎、排序键、分区键能少踩很多坑。2.2 创建表MergeTree家族与字段定义细节ClickHouse最强大的表引擎是MergeTree家族日常90%以上的场景选MergeTree就够了。它的核心特点是可以自定义分区键、排序键、主键、TTL并且支持数据压缩。如果没有特殊需求尽量不要用Log、Memory这类表引擎它们在分析场景下能力很受限。看一个标准建表语句CREATE TABLE IF NOT EXISTS app_log.access_log ( user_id UInt64, request_path String, status_code UInt16, request_time Float64, event_time DateTime, remark String DEFAULT no remark COMMENT 备注 ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_time) ORDER BY (user_id, event_time) PRIMARY KEY (user_id) SETTINGS index_granularity 8192;这里有一个容易踩的坑ORDER BY是排序键决定了part内数据的物理排序也决定了稀疏索引的生成PRIMARY KEY是主键默认情况下它只是排序键的前缀并不是唯一约束。ClickHouse里的主键既不能保证唯一也不像MySQL那样自动创建聚簇索引它更像一个“跳跃索引”用来加速查询时的数据裁剪。所以设计表的时候要始终思考一个问题你最频繁的过滤条件是什么把过滤条件字段放在ORDER BY前面查询性能差异会非常大。比如上面这个表如果查询经常带WHERE user_id 123 AND event_time 2025-01-01那排序键的匹配度就很高如果反过来的查询更多就应该把event_time放前面。2.3 分区、主键与排序键的设计关系很多新手以为分区越多越好这是误区。分区的作用是查询时按分区裁剪跳过不需要的数据文件。但ClickHouse每个分区会生成一个独立的part目录分区太细会导致part数量爆炸后台合并压力巨大反而拖慢查询和写入。以时间分区为例日志类数据按天或按月分区基本够了。如果数据量不是特别大按月分区即可。实测中如果一个分区内数据超过几千万行可以考虑缩小粒度但要结合max_partitions_per_insert_block这个参数来控制单次插入的分区数量防止一条INSERT语句一次性生成几百个part把磁盘IO打满。主键顺序对查询的影响更大。比如排序键是(user_id, event_time)那么WHERE event_time BETWEEN ...这种不带user_id的查询就无法使用前缀索引会在Query Profile里看到一个很明显的“Read 100% parts”的现象。解决办法要么调整排序键顺序要么用INDEX加二级跳数索引比如给status_code加一个minmax索引INDEX idx_status status_code TYPE minmax GRANULARITY 4这块内容比较深但建表的时候多想几分钟后面能省几个小时的排障时间。3. 增删改查核心操作语法、执行过程与性能真相这一节是全文的核心。我会把每条操作的SQL语法、执行过程、性能表现都拆开讲重点是让大家理解“这条语句背后到底发生了什么”。3.1 插入数据批量提交才是正确姿势插入数据的语法和标准SQL没什么区别INSERT INTO app_log.access_log (user_id, request_path, status_code, request_time, event_time, remark) VALUES (1001, /api/user, 200, 0.012, 2025-01-01 00:00:00, test), (1002, /api/order, 500, 0.300, 2025-01-01 00:00:01, error);看起来简单实际开发中要注意几点第一单次插入的数据量不要太小。ClickHouse适合大批量写入每批建议至少1000行起步。如果你在业务代码里循环插入几千次每次一行不仅ClickHouse压力大还会生成大量碎片part后台合并任务忙不过来查询性能会断崖式下跌。第二用Values还是用SELECT数据量大的时候尽量用INSERT INTO ... SELECT FROM ...这种表到表的搬运或者通过JDBC驱动开启批量预编译。实测中JDBC下使用PreparedStatementaddBatch()比单条executeUpdate快至少10倍以上。第三注意分区键的离散度。如果一次INSERT的数据落到了几十个分区ClickHouse会在内存里拆分成很多block每个block对应一个分区最终生成多个part磁盘写入放大明显。所以写入任务最好按分区键分批提交比如按天刷日志时一天的数据放一个批次。3.2 查询数据别用SELECT *空间换时间的代价查询语法大致长这样SELECT user_id, count() AS pv, avg(request_time) AS avg_rt FROM app_log.access_log WHERE event_time 2025-01-01 AND user_id 1001 GROUP BY user_id ORDER BY pv DESC LIMIT 10;列式存储的好处是你只查user_id和request_time两列ClickHouse就只加载这两列的数据文件IO开销小得惊人。反过来如果你写了SELECT *它会加载所有列哪怕是完全用不上的大字符串字段数据量和磁盘IO都会成倍增长。对这个点我只能说在ClickHouse里写SELECT * 是最亏的操作没有之一。另外WHERE筛选的顺序也很重要。ClickHouse有一个PREWHERE优化执行查询的时候会把过滤条件下推到读取阶段之前减少加载的数据量。有时候你看到执行计划里出现Read in full granularity就说明过滤条件没有作用到压缩数据块上性能就差。如果表里存在大字段比如remark String这种很长的备注我习惯把它单独拆出去或者用ALTER TABLE ... CLEAR COLUMN清理不需要的列数据减少存储占用。列存数据库的优势必须通过“列裁剪”才能发挥出来。3.3 修改与删除数据mutation机制详解这是ClickHouse和MySQL差异最大的地方值得单独用一个部分来讲。语法看起来非常“标准”-- 修改 ALTER TABLE app_log.access_log UPDATE status_code 404 WHERE user_id 1001; -- 删除 ALTER TABLE app_log.access_log DELETE WHERE event_time 2024-01-01;但是这两条语句都属于mutation操作执行过程是这样的ClickHouse先把更新或删除条件写入一个mutation任务。后台线程扫描所有匹配条件的part复制一份出来在副本上修改数据然后生成新part。新part替换旧part旧part等待回收。整个过程是异步的。你执行完SQL后它可能立即返回成功但实际上后台任务还在跑。查询system.mutations表可以看到执行进度SELECT database, table, mutation_id, command, create_time, is_done FROM system.mutations WHERE table access_log ORDER BY create_time DESC;is_done为0表示还在执行为1表示完成。实际操作中几个非常容易踩的坑更新大范围数据极慢。比如UPDATE status_code 404 WHERE user_id 1001如果user_id1001的数据散落在几千个part里ClickHouse就要把这几千个part都重写一遍耗时可能以分钟甚至小时计。所以mutation这种操作尽量往小范围写条件越精确越好。频繁UPDATE会产生僵尸part。每次mutation都会生成一个新版本part旧part并不会立刻删除而是要等后台合并线程处理。如果一天执行几十次UPDATE/DELETEpart数量会暴涨最后.zombie文件一堆。建议对这类表增加SETTINGS max_part_merging_concurrent_parts 8等参数或者干脆错峰执行批量更新。修改主键/排序键字段会非常昂贵。因为排序键决定了物理数据顺序改了主键字段意味着所有part的数据顺序都得重排这种操作一般只在凌晨低峰期做。说实话当你的业务需要高频单行UPDATE时说明ClickHouse可能不是合适的选择。该用MySQL、PostgreSQL的就用别拿ClickHouse硬扛OLTP场景。3.4 轻量删除ClickHouse的“准实时删除”玩法为了应对部分轻量删除场景较新的ClickHouse版本支持了轻量删除语法DELETE FROM app_log.access_log WHERE status_code 500 SETTINGS allow_experimental_lightweight_delete 1;它比mutation好用的地方在于不会立刻重写所有part而是先给匹配的行打一个删除标记mask查询时自动过滤掉标记行后台合并时再真正清理。但这并不意味着你可以随便用了。轻量删除有前提条件删除条件必须能基于分区裁剪最好直接限定分区。如果一行数据分散在多个part标记数量会膨胀。对于频繁删除的字段建议在排序键中包含它否则删除效率也不高。另外轻量删除只支持DELETE不支持UPDATE。如果业务上需要“准实时删除一批数据”可以考虑这个方案但我个人还是推荐用分区粒度去处理比如保留最近30天数据每天ALTER TABLE ... DROP PARTITION 2024-01-01这是ClickHouse删除效率最高的方式比DELETE快好几个数量级ALTER TABLE app_log.access_log DROP PARTITION 2024-01-01;我接触过不少项目前期用DELETE删过期数据一天慢得不行改成按天DROP PARTITION之后删数据秒级完成后台合并压力也小了。这就是典型的“让ClickHouse干它擅长的事”。4. 字段管理添加字段、修改字段、删除字段的完整指南字段变更这类操作MySQL里通常很快但ClickHouse因为列式存储的原因行为差异很大。下面按添加、修改、删除三个方向展开。4.1 添加字段ADD COLUMN 与 AFTER/FIRST添加字段语法ALTER TABLE app_log.access_log ADD COLUMN request_method String DEFAULT GET AFTER request_path;需要注意几点AFTER和FIRST指定新列在表结构中的位置。这在MySQL里只是为了好看但在ClickHouse里会影响到某些列式操作的性能。如果新列和查询中经常一起出现的列放在相邻位置读取时的局部性会好一些但差距不大。实际中我一般不加AFTER默认追加到末尾。添加字段是元数据操作非常快不会立即重写part。旧数据中该列的默认值会在查询时动态补充。给字段加DEFAULT或者MATERIALIZED时默认值表达式可以是常量也可以是简单函数。注意不要在这里写复杂查询否则每次读旧数据都要执行一次表达式拖慢查询。4.2 修改字段类型变更、默认值、注释、TTL修改字段类型ALTER TABLE app_log.access_log MODIFY COLUMN request_time Float64;修改默认值ALTER TABLE app_log.access_log MODIFY COLUMN remark String DEFAULT no remark;修改注释ALTER TABLE app_log.access_log COMMENT COLUMN request_path 请求路径;这几个操作里最坑的是修改字段类型。假设你之前把status_code定义为UInt8现在想改成UInt16ClickHouse为了确保数据正确会把所有part中该列的数据重新编码也就是一次全量重写。对大表来说这就是一个耗时非常长的mutation任务而且需要额外的磁盘空间来存放新part。所以建表时字段类型要尽量一次定准。整数类型够用就行但要留一点余量日期类型不要用String能用DateTime尽量用DateTime否则后面还得做数据迁移。字段TTL是ClickHouse比较有特色的能力可以直接给字段设置过期时间ALTER TABLE app_log.access_log MODIFY COLUMN remark String TTL event_time INTERVAL 90 DAY;含义是当event_time超过90天后remark字段的值会被清空。这个功能非常适合日志场景比如保留原始数据一年但超过90天的备注信息不再需要就可以这样设置节省存储空间。注意TTL的生效也依赖part的合并不会精确到秒级。4.3 删除字段DROP COLUMN 与数据重写的代价删除字段语法ALTER TABLE app_log.access_log DROP COLUMN remark;这个操作会真正删除该列在所有part中的数据释放存储空间。代价同样是全量重写part大表执行时磁盘占用会短暂上升因为新旧part同时存在。删除字段后如果表上有物化视图依赖这个字段通常会报错或视图失效。遇到这种情况需要先删除或重建物化视图再执行DROP COLUMN。我遇到过一个真实案例开发环境直接DROP COLUMN线上还有SQL在查询该字段结果各种“Column not found”报错刷屏源头就是开发环境和线上表结构没对齐。另外ClickHouse支持将字段标记为REMOVE TTL也支持RENAME COLUMNALTER TABLE app_log.access_log RENAME COLUMN remark TO extra_info;RENAME COLUMN是纯元数据操作速度很快但要确保没有物化视图、字典表还在引用旧字段名。4.4 字段改名与物化列补充物化列MATERIALIZED在ClickHouse里也很有用CREATE TABLE app_log.access_log ( user_id UInt64, event_time DateTime, event_date Date MATERIALIZED toDate(event_time) ) ENGINE MergeTree() ORDER BY (event_date, user_id);物化列的值在插入时自动计算并存储查询时不需要额外计算。但要注意物化列不能通过INSERT手动指定值。如果在已有表上添加物化列语法上支持ALTER TABLE app_log.access_log ADD COLUMN event_date Date MATERIALIZED toDate(event_time);但这会触发一次全表数据重写因为所有已有行的物化列都需要回填。数据量大的时候同样要准备足够的磁盘空间。5. 实战从0到1完成一个完整示例理论讲了一堆我来跑一个完整示例演示从建库、建表、增删改查到字段管理的全过程。假设我们做一个简单的埋点日志系统记录用户访问行为。5.1 场景定义与建表语句业务需求每天千万级访问日志。按天分区存储。常查条件user_id、event_time。需要记录请求路径、状态码、耗时、客户端IP、扩展信息。数据保留90天。建表语句我这样写CREATE DATABASE IF NOT EXISTS demo; CREATE TABLE IF NOT EXISTS demo.user_action_log ( user_id UInt64, event_time DateTime, path String, method String DEFAULT GET, status_code UInt16, duration_ms UInt32, client_ip IPv4, extra String DEFAULT ) ENGINE MergeTree() PARTITION BY toYYYYMMDD(event_time) ORDER BY (user_id, event_time) PRIMARY KEY (user_id) TTL event_time INTERVAL 90 DAY SETTINGS index_granularity 8192;解释几个关键选择PARTITION BY toYYYYMMDD(event_time)按天分区方便按天DROP旧分区。ORDER BY (user_id, event_time)支持按用户和时间范围组合查询。TTL event_time INTERVAL 90 DAY整行数据90天后过期后台自动清理。client_ip IPv4ClickHouse原生支持IPv4/IPv6类型比String更省空间查询也更快这是个容易被忽略的小优化。5.2 数据写入、更新、删除、字段变更全流程插入几条模拟数据INSERT INTO demo.user_action_log (user_id, event_time, path, method, status_code, duration_ms, client_ip, extra) VALUES (1001, 2025-01-01 00:00:00, /api/login, POST, 200, 35, 192.168.1.10, ), (1002, 2025-01-01 00:00:01, /api/order, POST, 500, 120, 192.168.1.11, timeout), (1003, 2025-01-01 00:00:02, /api/user, GET, 200, 8, 192.168.1.12, );查询验证SELECT status_code, count() AS cnt, avg(duration_ms) AS avg_duration FROM demo.user_action_log WHERE event_time 2025-01-01 GROUP BY status_code ORDER BY cnt DESC;执行结果就是常规的分组统计这里不再展开。更新某条数据ALTER TABLE demo.user_action_log UPDATE status_code 502 WHERE user_id 1002 AND event_time 2025-01-01 00:00:01;删除某个用户全部数据ALTER TABLE demo.user_action_log DELETE WHERE user_id 1003;执行后马上查一下mutation状态SELECT command, is_done, latest_fail_reason FROM system.mutations WHERE database demo AND table user_action_log ORDER BY create_time DESC;如果is_done一直是0并且latest_fail_reason不为空说明mutation执行失败了。再演示字段变更-- 添加字段 ALTER TABLE demo.user_action_log ADD COLUMN user_agent String DEFAULT ; -- 修改字段类型 ALTER TABLE demo.user_action_log MODIFY COLUMN status_code UInt16; -- 删除字段 ALTER TABLE demo.user_action_log DROP COLUMN extra;由于我们表比较小这些操作几乎瞬间完成。但在生产环境的大表上同样语句可能需要跑很久。在上线前一定要对比表的数据量和磁盘空间评估mutation大约耗时多久提前跟业务方对齐变更时间窗。5.3 验证与数据一致性检查字段操作和增删改查都完成后可以用下面几个命令验证表结构、分区和part情况。查看表结构SHOW CREATE TABLE demo.user_action_log;查看分区情况SELECT partition_id, name AS part_name, rows, bytes_on_disk FROM system.parts WHERE database demo AND table user_action_log ORDER BY part_name;查看最新数据是否更新成功SELECT user_id, status_code, duration_ms FROM demo.user_action_log FINAL ORDER BY user_id;FINAL修饰符在这里比较实用它会在查询时对同一排序键的行做合并去重。但要记住FINAL会降低查询性能数据量大时不要滥用。6. 常见问题与排查技巧实录最后这部分把我自己实际踩过、也帮别人排查过的问题整理一下基本能覆盖日常90%的“增删改查和字段操作”相关坑。6.1 mutation卡住、不执行怎么办典型现象执行了ALTER TABLE UPDATE或DELETE语法没问题但查system.mutationsis_done一直为0甚至等了半小时也没反应。排查思路先看latest_fail_reason字段如果是空说明任务还在等待执行。看后台合并线程是否有大量任务堆积查询system.merges。如果是超大表、多条mutation同时提交任务会排队。可以用下面的SQL清理已完成但仍占空间的mutation记录KILL MUTATION WHERE database demo AND table user_action_log AND is_done 0;注意KILL MUTATION要慎用它只能取消尚未执行完成的任务。如果某个任务执行到一半取消后可能需要重新提交。6.2 修改字段类型报错怎么办常见报错Cannot convert string to UInt64或Type mismatch。这类问题大多是因为已有数据不符合目标类型。比如字段里存着非数字字符串想改成数值类型ClickHouse是转不过去的。解决办法先查一下脏数据SELECT * FROM 表 WHERE 字段 NOT MATCHING ^[0-9]$ LIMIT 10用合适的正则找到异常行。先UPDATE清掉脏数据再MODIFY COLUMN。如果无法清理就在原表加一个新列用CAST写入新列再切换查询逻辑旧列暂时保留。6.3 删除字段后查询报错怎么办典型报错Unknown column name或者Column xxx does not exist。先检查物化视图、字典、业务SQL里是否还在引用该字段。我一般会先查一下系统表把所有依赖关系找出来SELECT database, table, create_table_query FROM system.tables WHERE create_table_query LIKE %remark%;查到是哪个视图依赖后先DROP VIEW再重新创建。如果是业务SQL引用只能让业务侧配合修改上线。6.4 常用排查SQL速查表需求SQL查看mutation进度SELECT * FROM system.mutations WHERE tablexxx查看part列表和大小SELECT name, rows, bytes_on_disk FROM system.parts WHERE databasexxx AND tablexxx查看后台合并情况SELECT * FROM system.merges WHERE tablexxx强制合并part生产慎用OPTIMIZE TABLE xxx FINAL删除整个分区ALTER TABLE xxx DROP PARTITION 2024-01-01取消卡住的mutationKILL MUTATION WHERE databasexxx AND tablexxxOPTIMIZE TABLE FINAL这行我要单独提一句它会把表的所有part强制合并确实能提升查询性能但会带来极高的CPU和磁盘IO消耗。大表上执行一次可能跑几十分钟甚至几个小时生产环境一定选在低峰期跑。日常不用频繁执行让ClickHouse自己按合并策略去处理就行。在我处理过的项目里真正把ClickHouse用好的团队都会刻意减少对UPDATE和DELETE的依赖能用分区直接DROP的就用分区能通过INSERT幂等覆盖的就用幂等写入实在要改数据的才走mutation。这个思路比记住任何一条具体SQL都重要。每次要执行ALTER TABLE UPDATE之前先问自己一句能不能不更新能不能换个表结构设计想清楚了再动手比什么优化参数都管用。