【Clickhouse系列】增删改查：对比mysql

目录

1. 写入操作 (INSERT)

2. 删除操作 (DELETE)

3. 更新操作 (UPDATE)

4. 查询操作 (SELECT)

5. 总结对比表：

6. 参考链接

---

核心哲学差异：

• MySQL： 面向在线事务处理。核心目标是保证数据的强一致性、原子性和低延迟的单行操作（点查、点写），适用于高并发、频繁小数据量修改的业务系统（如用户中心、订单系统）。
• ClickHouse： 面向在线分析处理。核心目标是实现海量数据（PB级）的超高速聚合查询和批量导入，牺牲了实时写入和单点修改的效率，追求极高的查询吞吐量。适用于数据仓库、实时分析、日志处理等场景。

详细对比：

1. 写入操作 (INSERT)

• • ClickHouse:

• • • 架构/原理： 基于Log-Structured Merge-Tree思想。数据首先写入内存缓冲区(MemTable)，当缓冲区满或达到阈值时，异步刷写到磁盘形成不可变的数据片段。写入是批量、高吞吐、低频率的操作。INSERT语句本身是异步的（除非使用SYSTEM FLUSH LOGS强制刷写内存表），客户端通常很快返回，但数据真正落盘并可见有一定延迟。
    • 表存储： 数据按分区键（通常按时间）物理存储在磁盘的不同目录中。每个分区内，数据按主键（或ORDER BY键）排序存储。每次INSERT通常会生成一个新的数据片段。ReplicatedMergeTree引擎的表写入会通过ZooKeeper/Keeper协调复制到副本。
    • 特点： 极高写入吞吐量（每秒百万甚至千万行），但单次写入延迟较高（毫秒到秒级），不适合高频、小批量的实时写入。建议大批量（如>1000行/次）写入。

• • MySQL (InnoDB):

• • • 架构/原理： 基于B+Tree索引结构。写入操作（包括插入、更新、删除）需要修改B+树索引页和数据页。为了保证ACID（特别是持久性D），每次修改都需要写Redo Log。写入通常是实时、低延迟、单行或小批量。
    • 表存储： 数据存储在.ibd文件中，按聚簇索引（通常是主键）组织。数据和主键索引存储在一起。二级索引存储的是主键值。
    • 特点： 低写入延迟（微秒到毫秒级），支持高并发的小事务写入。写入吞吐量受限于磁盘IOPS和锁竞争（行锁、间隙锁等）。

2. 删除操作 (DELETE)

• • ClickHouse:

• • • 架构/原理： DELETE操作极其低效且不推荐。它不是一个即时、原地删除操作。执行DELETE时：

• • • • 对于MergeTree系列表，会生成一个特殊的异步删除标记（Mutation），记录要删除的行或分区范围。
      • 后台有专门的线程（或多个线程）在未来的某个时刻（通常是写入压力较小时）扫描数据片段，将标记删除的行物理排除，并重写整个受影响的数据片段。这是一个重量级、资源密集型、高延迟的操作。
      • 在删除标记生效前，查询会自动过滤掉被标记删除的行。

• • • 表存储： 删除操作不会立即释放磁盘空间，直到后台合并完成。删除大量数据**首选按分区删除 (ALTER TABLE ... DROP PARTITION/PART) **，这几乎是瞬间完成的，因为它直接删除整个分区目录。
    • 特点： 避免单行或小范围DELETE。分区级删除非常高效。删除操作是异步的，对查询性能有潜在影响（需要过滤标记）。

• • MySQL (InnoDB):

• • • 架构/原理： 删除操作相对高效。

• • • • 如果是通过主键删除，InnoDB会立即在B+树中定位到该行，将其标记为删除（打上删除标记）。
      • 被删除行占用的空间并不会立即回收，而是进入一个空闲链表，可以被后续的插入操作复用（称为行重用）。
      • 真正的空间回收需要等到Purge线程清理undo日志和OPTIMIZE TABLE操作（重建表）。

• • • 表存储： 删除操作修改B+树结构和数据页。空间管理在页内和页间进行。
    • 特点： 支持高效的单行删除。删除操作是事务性的（可回滚）。删除大量数据时，DELETE可能较慢（逐行标记），TRUNCATE TABLE（DDL，瞬间清空表）或DROP TABLE + CREATE TABLE更快。

3. 更新操作 (UPDATE)

• • ClickHouse:

• • • 架构/原理： UPDATE操作同样极其低效且不推荐，其实现机制与DELETE类似：

• • • • 执行UPDATE也会生成一个异步Mutation标记。
      • 后台线程在合并数据片段时，会读取旧数据，应用更新逻辑，然后重写整个包含修改行的数据片段。这相当于删除旧行 + 插入新行。

• • • 表存储： 与DELETE类似，不会立即修改原数据，而是通过标记和重写片段实现。同样首选按分区更新（通过删除旧分区+插入新数据的方式）。或者使用CollapsingMergeTree/VersionedCollapsingMergeTree/ReplacingMergeTree等引擎通过插入新版本数据并在查询时合并的方式来模拟更新（更高效）。
    • 特点： 强烈避免频繁或大范围的UPDATE操作。它是ClickHouse最不擅长的操作类型之一。设计表结构时应考虑“只追加”模式。

• • MySQL (InnoDB):

• • • 架构/原理： 更新操作是核心能力。

• • • • 如果是主键更新且值不变，则直接修改数据页中的行。
      • 如果更新了索引列，则需要修改B+树（可能涉及节点分裂合并）。
      • 如果更新导致行长度变化（如VARCHAR变长），可能需要行迁移（记录移动到新位置，原位置留下指针或标记为删除）。
      • 同样需要写Redo Log保证持久性。

• • • 表存储： 直接在原数据页或迁移后的新位置修改行数据。
    • 特点： 高效支持行级更新，是OLTP的核心操作。支持事务性更新。

4. 查询操作 (SELECT)

• • ClickHouse:

• • • 架构/原理：

• • • • 列式存储： 这是高速分析查询的基石。查询时只读取所需的列，大大减少了磁盘I/O。
      • 向量化执行引擎： 数据不是逐行处理，而是按列“块”进行处理（通常一次处理几千行），充分利用CPU的SIMD指令集进行并行计算。
      • 稀疏索引： PRIMARY KEY定义的是数据的排序顺序，而非唯一约束（允许重复）。它创建的是稀疏索引（每N行一个索引项，默认8192），主要用于快速定位数据块范围，而不是精确查找单行。ORDER BY键（通常与主键一致）对查询性能至关重要。
      • 数据压缩： 按列压缩效率极高（相同数据类型），进一步减少I/O。
      • MPP架构 (分布式查询)： 在集群环境下，查询可以自动拆分成多个子任务，在多个分片（Shard）上并行执行，结果汇总。

• • • 表存储： 数据按列存储在每个数据片段的.bin文件中，有对应的.mrk标记文件（辅助稀疏索引定位数据块）。预排序和列存储使得范围扫描和聚合计算极其高效。
    • 特点： 在聚合查询、多表JOIN（特定场景）、扫描大范围数据、全表扫描方面性能极其强悍（比MySQL快几个数量级）。点查（按主键查单行）效率很低（需要扫描多个数据块），不擅长高并发小查询（资源消耗相对大）。

• • MySQL (InnoDB):

• • • 架构/原理：

• • • • 行式存储： 读取一行需要读取该行所有列的数据（即使查询只用到其中几列）。
      • B+Tree索引： 聚簇索引（主键）存储整行数据，二级索引存储主键值。通过索引可以高效定位单行或小范围行（点查、范围查）。
      • 优化器： 基于成本的优化器选择执行计划（是否使用索引、使用哪个索引、JOIN顺序等）。

• • • 表存储： 数据存储在聚簇索引的叶节点。查询通过遍历B+树快速定位记录。
    • 特点： 在点查、小范围查询、基于索引的精确匹配查询方面效率极高，延迟很低。擅长处理高并发的小查询。全表扫描、大范围聚合查询（尤其是GROUP BY、SUM/COUNT等无合适索引时）效率较低，容易成为性能瓶颈。

5. 总结对比表：

操作	ClickHouse (OLAP)	MySQL (InnoDB, OLTP)
写入 (INSERT)	高吞吐，批量优先，异步落盘，延迟较高。分区高效。	低延迟，实时，支持高并发小事务写入。
删除 (DELETE)	极低效（异步Mutation），避免单行删除。分区删除高效。	高效（行级），事务性，空间可复用。TRUNCATE极快。
更新 (UPDATE)	极低效（异步Mutation），强烈避免。引擎模拟更新或分区替换更优。	高效（行级），事务性，OLTP核心操作。
查询 (SELECT)	聚合、扫描、大范围JOIN极快（列存、向量化、稀疏索引、MPP）。点查效率低，不擅长高并发小查询。	点查、小范围查询极快（B+Tree索引）。大聚合、全表扫描慢。擅长高并发小查询。
设计哲学	分析优先：牺牲实时修改效率，换取海量数据下的极致查询速度。	事务优先：保证ACID，优化单行操作的实时性和并发性。

关键建议：

• 选型： 需要频繁增删改（特别是单行操作）和高并发点查？选MySQL。需要分析海量历史数据做快速聚合报表？选ClickHouse。两者经常配合使用（MySQL处理业务，ClickHouse做分析）。
• ClickHouse最佳实践：

• • 写入： 大批量、低频率写入。使用INSERT ... SELECT或clickhouse-client --query ... --input_format...高效导入。
  • 删除/更新： 尽量通过设计规避。必须删除时，优先使用ALTER TABLE ... DROP/DETACH PARTITION。考虑使用CollapsingMergeTree等引擎处理更新。
  • 查询： 充分利用聚合和预聚合（AggregatingMergeTree, Materialized Views）。谨慎设计ORDER BY键（主键）和分区键。避免高频点查。
  • 表结构： 设计为“只追加”模式，分区合理（通常按时间），选择合适的主键顺序。

理解这些底层架构、存储和原理上的差异，是正确使用和优化ClickHouse与MySQL的关键。希望这份详细的对比能帮助你更好地进行技术选型和数据库设计。

6. 参考链接

Handling Updates and Deletes in ClickHouse

clickhouse docs | en/sql-reference/statements/alter/update

clickhouse blog | handling-updates-and-deletes-in-clickhouse