接手ClickHouse之前我一直以为增删改查全世界都一个样。毕竟SQL的标准摆在那里MySQL怎么写ClickHouse能差到哪去真正上手第一天就被打脸在MySQL里一条UPDATE瞬间改完走人在ClickHouse里却要等后台合并慢慢生效在MySQL里ALTER TABLE删个列分分钟在ClickHouse里如果表已经有几十亿行ADD COLUMN虽然秒返回背后的数据部件却要一个个重写。这篇文章就记录一下我从SQL还是那个SQL的幻觉里被拽出来的过程把创建数据库、创建表、增删改查、修改字段、添加字段、删除字段这些最常用的操作挨个过一遍重点讲清楚每个操作在ClickHouse里的真实行为以及为什么它跟MySQL不一样。不管是刚接触ClickHouse的初学者还是准备从MySQL迁过来的团队都值得花十分钟把这套逻辑理顺后面少踩很多坑。1. 先搞清楚ClickHouse的增删改查和MySQL到底差在哪1.1 从实时行修改到异步部件重写ClickHouse最初的设计目标是分析型查询追求的是海量数据下读得快、聚合快。为了达到这个目标底层存储引擎用的是MergeTree系列数据以不可变的part数据部件形式落盘。每次写入一批数据就会生成一个新的part后台线程再把小part按照同分区合并成大part。这个设计跟MySQL的B树行存储是两套完全不同的逻辑。所以ClickHouse的UPDATE和DELETE本质上不是找到那一行改掉而是告诉服务器这批数据要改服务器异步地把包含这些数据的part整块重写一遍。更新十行和更新十万行后台付出的代价可能差不多——因为都要重写整块part。这就决定了你在使用习惯上必须调整小事务高频更新的玩法在ClickHouse里行不通批量操作才是正确姿势。1.2 DML在ClickHouse中的真实行为对照我用一张表把两类数据库的核心差异列出来方便团队内部对齐认知操作MySQL行为ClickHouse行为INSERT实时写入行实时写入生成新partUPDATE实时修改命中行异步Mutation后台重写partDELETE实时删除命中行异步Mutation后台重写partALTER ADD COLUMN8.0部分场景秒级大表可能锁表秒级改元数据旧数据读时按默认值填充ALTER DROP COLUMN行存储删除列物理重写标记删除part后台重写这套差异解释了为什么ClickHouse官方文档里会反复强调Mutation慎用、要设计成分区级别的增删或批量任务。理解了这一点后面再看具体语法会顺畅很多。注意ClickHouse的UPDATE和DELETE都是异步Mutation操作提交后立即返回但数据变更在后台逐步完成。期间查询可能读到旧版本数据所以代码逻辑里不能依赖执行完立刻读到新值。2. 创建数据库与建表从基础语法到分区/排序键设计2.1 创建数据库默认引擎是AtomicClickHouse创建数据库的语法很简单CREATE DATABASE IF NOT EXISTS test_db ENGINE Atomic COMMENT 测试数据库;如果不指定ENGINE新版本默认是Atomic。Atomic和早期版本默认的Ordinary最大的区别在于Atomic支持原子性的DROP和RENAME操作删库或改名时不会出现中间状态还支持把删掉的表恢复到指定时间点。生产环境建议明确指定ENGINE Atomic别用默认值去赌版本行为。创建完可以执行SHOW DATABASES;确认或者USE test_db;切换当前库。如果是集群环境还会在语句后面加ON CLUSTER cluster_name让所有节点同步执行这个在分布式部署时几乎是必选项。2.2 建表的核心参数PARTITION BY、ORDER BY、PRIMARY KEY创建MergeTree表的完整示例CREATE TABLE test_db.events ( event_date Date, event_time DateTime, user_id UInt64, event_type LowCardinality(String), amount Decimal(18, 2), remark String DEFAULT ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_date) PRIMARY KEY user_id ORDER BY (user_id, event_time) TTL event_time INTERVAL 90 DAY;这段SQL里有几个参数最容易被MySQL转过来的同学忽略PARTITION BY是分区键这里按月分区数据进入表时就会按月份划到不同分区。分区是一级目录最大的作用不是加速查询而是方便做数据生命周期管理——比如过期直接DROP PARTITION秒级清理一整月数据比DELETE几十亿行不知道快到哪里去了。ORDER BY是排序键它决定了同一个part内数据按哪些列排序存放。ClickHouse的稀疏索引就建立在排序键上所以最常用的过滤字段应该尽量排在ORDER BY前面。注意ORDER BY和PRIMARY KEY是独立的但默认情况下PRIMARY KEY会取ORDER BY的前缀如果你不写PRIMARY KEY它就等于ORDER BY全字段。PRIMARY KEY在ClickHouse里不是唯一约束它只是一个稀疏索引。换句话说主键相同的行可以同时存在不会像MySQL那样报错。这一点在做数据去重需求时要格外小心想要去重得用ReplacingMergeTree不能指望主键。2.3 类型选择别一上来就String建表时字段类型选得对不对直接影响存储空间和查询速度。经验之谈能用数值绝不用字符串状态字段用UInt8或Enum8低基数字段用LowCardinality(String)比如事件类型、渠道来源存储和查询都有收益金额用Decimal(18, 2)不要用Float否则你对账会崩时间字段统一用DateTime或DateTime64别用String存时间根本没法做分区和范围过滤。TTL那一行是顺便演示的在MergeTree里可以直接声明行级TTL到期后自动删除或归档。这也是ClickHouse比传统OLTP数据库更适合做日志、事件数据冷热分层的原因。建表之后要改表名用RENAME TABLE test_db.events TO test_db.events_archive;这个操作在Atomic引擎下是原子的而且可以同时RENAME多张表括号包起来就行。删表的语法是DROP TABLE IF EXISTS test_db.events;后面可以带SYNC关键字表示同步删除否则是异步的。3. 插入与查询日常读写的基本功3.1 INSERT的三种姿势和应用场景ClickHouse的INSERT语法和标准SQL大体一致但实际使用中三种姿势各有讲究-- 方式一VALUES适合少量数据、临时调试 INSERT INTO test_db.events VALUES (2024-06-01, 2024-06-01 10:00:00, 1001, view, 0.00); -- 方式二指定列名插入 INSERT INTO test_db.events (event_date, event_time, user_id, event_type, amount) VALUES (2024-06-01, 2024-06-01 10:00:01, 1002, click, 1.50); -- 方式三INSERT SELECT适合表间迁移、ETL INSERT INTO test_db.events SELECT event_date, event_time, user_id, event_type, amount FROM test_db.events_staging WHERE event_date 2024-06-01;方式三是生产环境最常用的尤其是数据从临时表、Staging表往正式表搬运时非常顺手。它支持跨表、跨库查出来的数据直接灌入目标表比先查出来再逐行写入效率高出几个量级。3.2 关于批量写入我给一个硬性建议ClickHouse极度不建议一条一条INSERT。每执行一次INSERT都会在存储层生成一个partpart太多不仅占用文件句柄还会拖慢查询合并的速度。大批量写入时建议在client端拼好一批数据一次INSERT几千行起步如果是日志类采集至少攒一万行或攒满几MB再提交。用clickhouse-client批量导入文件数据是最典型的场景clickhouse-client \ --databasetest_db \ --queryINSERT INTO events FORMAT CSV \ events.csvFORMAT支持CSV、TSV、JSONEachRow、Parquet等等。我这里单独提醒一下如果你用JSONEachRow格式字段名和表字段名必须完全对得上对不上就要在INSERT里显式列名映射否则解析会报错。3.3 SELECT查询聚合比点查更体现价值查询语法上ClickHouse和MySQL很像SELECT col FROM table WHERE cond GROUP BY col ORDER BY col LIMIT n这些都没问题。区别在于ClickHouse的强项是聚合分析比如按用户、按天做汇总SELECT user_id, count() AS pv, sum(amount) AS gmv FROM test_db.events WHERE event_date 2024-06-01 GROUP BY user_id ORDER BY gmv DESC LIMIT 20;这里有个细节值得注意ClickHouse里count()的括号里没有字段名时就是对行数计数。另外如果过滤很碎、返回行数很少ClickHouse的性能反而不如MySQL因为列式存储的随机点查天生不是强项。设计表结构时把高频查询的维度排在ORDER BY前缀查询计划才能命中稀疏索引。想观察SQL执行细节可以用EXPLAIN PLAN SELECT ...看看有没有走索引、扫描了哪些part。还有一种情况是修改表结构后立刻SELECT要注意如果某个刚添加的字段还处于Mutation状态查询可能短暂读不到新数据代码里要做容错别把异常直接抛给用户。4. 修改字段结构ALTER TABLE的完整操作手册4.1 添加字段ADD COLUMN生产环境往一张大表加字段是最常见也最容易被轻视的操作。语法ALTER TABLE test_db.events ADD COLUMN IF NOT EXISTS user_name String DEFAULT unknown AFTER user_id, ADD COLUMN IF NOT EXISTS ip String DEFAULT AFTER user_name;这里有几个关键点。IF NOT EXISTS必须加尤其是在自动化脚本里重复执行不会报错AFTER user_id控制新列的位置不指定默认加在最后DEFAULT定了老数据的默认值。ClickHouse的ADD COLUMN只改元数据不会立刻重写磁盘上的part所以无论表多大基本都是秒级完成旧数据在读取时自动填充默认值。这是它比MySQL在大表加列上更从容的原因。但别高兴太早ADD COLUMN不会马上重写part意味着如果你给新列设置了复杂的默认表达式查询时每行都要临时算一遍可能拖慢首次查询。所以默认值不要写太贵的表达式。4.2 删除字段DROP COLUMN删字段的语法ALTER TABLE test_db.events DROP COLUMN IF EXISTS remark;执行后字段会从表结构中移除底层数据part要等后台Mutation重写后才真正释放空间。所以删列后立刻看磁盘空间可能变化不大这是正常的。另外要小心如果有物化视图或字典还在引用这个字段DROP COLUMN会失败或导致相关查询报错。我在生产环境删列前一定会先查一遍system.columns里有没有依赖这个列的物化视图列再动手。4.3 修改字段MODIFY COLUMN与类型转换限制修改字段类型、默认值或注释用MODIFY COLUMNALTER TABLE test_db.events MODIFY COLUMN user_name Nullable(String), MODIFY COLUMN event_type String COMMENT 事件类型, MODIFY COLUMN amount Decimal(18, 3);类型修改不是全能的核心规则是只能做不丢信息的兼容扩展。比如UInt8可以改成UInt16、Int64可以改成Nullable(Int64)、String可以改成Nullable(String)但反过来从Nullable转非Nullable、从LowCardinality(String)转DateTime这类通常会被拒绝或产生不可预知的数据异常。常用类型变更对照如下原类型可修改为说明UInt8UInt16 / UInt32 / Int16 / Int32数值范围扩大StringNullable(String) / LowCardinality(String)变体扩展DateTimeDateTime64(3)精度提升Decimal(18,2)Decimal(18,3) / Decimal(38,2)精度或范围扩大Nullable(String)String一般不允许可能报错修改字段类型本质上也会触发Mutation大表上耗时会比较久建议结合system.mutations观察进度。如果只是改字段注释则改的是元数据秒级完成。4.4 重命名字段RENAME COLUMN把字段改名ALTER TABLE test_db.events RENAME COLUMN user_name TO nickname;这个操作只动元数据不重写part速度快到可以接受。但要注意两点一是改名后旧的物化视图、字典、报表SQL全部要同步更新否则就变哑字段了二是如果表上有依赖这个列的投影Projection改名之后投影也需要重建否则没有办法正常使用。所以改名我喜欢在凌晨低峰期做给下游留一晚上的查错时间。5. 更新与删除数据理解Mutation的代价与使用时机5.1 UPDATE语法简单代价不小ClickHouse更新数据的标准写法ALTER TABLE test_db.events UPDATE amount 0, status refunded WHERE user_id 1003 AND event_date 2024-06-01;一次可以更新多个字段用逗号分隔。注意ClickHouse的UPDATE必须带WHERE不带WHERE直接执行会被服务端拒绝这是为了防止你把整张表数据误改掉。UPDATE提交后会在system.mutations表里生成一条任务后台逐part执行复制旧part、改数据、替换旧part的动作。如果命中的分区大、part数量多整个Mutation可能要跑几分钟甚至更久。5.2 DELETE同样异步同样谨慎删除数据的标准写法ALTER TABLE test_db.events DELETE WHERE event_type spam AND event_date 2024-06-01;同样的规则必须带WHERE执行后异步生效。我见过不止一个新手把ClickHouse当MySQL用写了个高频定时任务每两分钟就执行一次DELETE清理最近过期数据结果表越来越大查询越来越慢。原因就是每次Mutation都在制造新的part版本历史版本不合并垃圾数据还在磁盘上躺着。5.3 轻量级DELETE新版本的另一种选择ClickHouse 22.8以后引入了轻量级DELETE语法DELETE FROM test_db.events WHERE user_id 1003;它在底层默认走ALTER TABLE ... DELETE的Mutation逻辑但也提供了enable_lightweight_delete设置。如果版本支持它可以做到比标准Mutation更细粒度的行级标记删除。需要特别注意这个语法和MergeTree的去重/更新语义不同它真正标记的是行级删除部分场景下查询性能会比硬删除略差。到底用哪种我的建议很简单——确认你们的版本和官方文档兼容清单生产环境优先用标准Mutation轻量删除先在测试库验证。5.4 Mutation的代价与替代方案说句掏心窝的话Mutation在ClickHouse里应该算万不得已才用的操作。真正做数据订正的通用方案有两类第一类是用分区Drop替换。如果你的数据天然按时间分区需要把1月份某批脏数据全部替换掉可以先导入正确数据到临时表再用新分区交换最后ALTER TABLE ... DROP PARTITION清掉脏分区。这样比逐行UPDATE快几个数量级。第二类是用ReplacingMergeTree合并去重。建表时指定ENGINE ReplacingMergeTree(version)写入相同主键的多行数据后台合并时保留最新版本。适合以追加方式实现更新的场景比如用户档案快照、订单状态流。Mutation的正确使用场景是低频订正比如发现某天凌晨的埋点数据字段解析错了需要把所有异常值批量改掉或者外部数据源推了一批无效数据要清理。低频、大批量、可接受异步生效这三个条件同时满足才适合用Mutation。6. 我在实际项目中总结的几条字段与数据操作经验6.1 字段变更前先做三个检查经过几次线上小事故之后我现在操作生产库字段强制自己过三关一是查依赖。在system.tables里搜哪些物化视图的表表达式引用了当前表在system.dictionaries里看有没有字典把这张表当数据源。有依赖就优先处理依赖否则字段一删下游全部报错。二是量规模。用SELECT partition, count() FROM system.parts WHERE table events GROUP BY partition;看part数量。part越少、表越小Mutation越快如果有几千个part一个ALTER删列能跑一小时。三是选窗口。字段变更尽量安排在业务低峰期因为Mutation和后台合并会抢IO资源。如果是7x24的在线服务至少要评估变更期间的查询延迟影响。6.2 观察Mutation进度不要干等执行完UPDATE或DELETE用下面这条SQL看任务状态SELECT database, table, command, is_done, latest_fail_time, latest_fail_reason FROM system.mutations WHERE table events ORDER BY create_time DESC LIMIT 5;is_done等于1表示任务完成等于0表示还在跑。如果latest_fail_reason不为空多半是类型转换问题或磁盘空间不足修复后可以重新提交Mutation。注意ClickHouse不会自动重试失败的Mutation得人工介入这块在监控里一定要加上告警。6.3 高频踩坑速查表报错/现象原因解决办法DB::Exception: Table default.events doesnt exist没建表或库名写错确认库名书写USE后再执行Cannot parse input: expected ...FORMAT导入时字段顺序类型不匹配用显式列名或检查CSV表头Mutation without WHERE is not allowedUPDATE/DELETE没带WHERE补WHERE条件Memory limit exceeded查询或Mutation占用内存超限加WHERE范围缩小处理数据量删了列但磁盘空间没释放Mutation没跑完观察system.mutations等完成大表ADD COLUMN后首次查询很慢默认值表达式太重使用简单常量默认值6.4 最后一个小技巧用临时表验证字段变更不管加字段还是删字段我的习惯都是先建一张和正式表结构一模一样的小表在临时表上跑完一遍的语法和执行计划再把SQL拿到正式库执行。这种先排练再上场的做法救过我很多次。ClickHouse的ALTER语法在不同版本之间差异不小尤其是24.x之后部分参数行为有调整官网的CHANGELOG和新版本文档是唯一可靠依据。如果你们的集群版本比较老参考网上的新语法一定要先看版本说明否则很容易写出当前集群不认识的语句。这套增删改查、建库建表、字段维护的体系我用下来最大的感受是ClickHouse不是MySQL的平替它是另一种思维模型。你把实时行级改的心态丢掉把批量、分区、不可变part这套逻辑装进来才会真正觉得它顺手。希望这篇能帮你少走几段弯路。