ClickHouse: Overview, MySQL Migration, Performance Comparison, and Practical Tips

1. What is ClickHouse? ClickHouse is a column‑oriented DBMS designed for online analytical processing (OLAP). It stores data by columns rather than rows, which reduces I/O and enables high compression, making it much faster for analytical queries compared to traditional row‑based databases such as MySQL or PostgreSQL.

2. Business Problem A business had a 50 million‑row table in MySQL with join queries taking over 3 minutes. After indexing, sharding, and logical optimizations the performance was still unsatisfactory, so they decided to use ClickHouse to accelerate the queries.

3. ClickHouse Practice

3.1 Installation on macOS The author installed ClickHouse via Docker, referencing a tutorial (https://blog.csdn.net/qq_24993831/article/details/103715194). A compiled installation is also possible but more complex.

3.2 Data Migration from MySQL to ClickHouse ClickHouse supports most MySQL syntax, offering five migration approaches:

CREATE TABLE ENGINE = MySQL – keep data in MySQL.

INSERT INTO SELECT – create table then import.

CREATE TABLE AS SELECT – create and import in one step.

CSV offline import.

Streamsets.

The author chose the third method (CREATE TABLE AS SELECT) and executed the following statement:

<code style="padding:15px 16px 16px;color:#dcdcdc;display:-webkit-box;font-family:'Operator Mono',Consolas,Monaco,Menlo,monospace;font-size:12px">CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = MergeTree AS SELECT * FROM mysql('host:port','db','database','user','password')</code>

3.3 Performance Test Comparison

Type

Data Volume

Table Size

Query Speed

MySQL

50 million

10 GB

205 s

ClickHouse

50 million

600 MB

≤ 1 s

The migration reduced query time from over three minutes to under one second, a performance gain of about 200×.

3.4 Data Synchronization Schemes

Two common approaches are described:

Temporary Table : Create a temp table in ClickHouse, bulk‑load MySQL data, then replace the original table. Suitable for moderate data volumes with frequent incremental changes.

Synch : Use the open‑source tool synch , which reads MySQL binlog, converts statements, and pushes them via a message queue for near‑real‑time sync.

3.5 Why ClickHouse Is Fast

Only the required columns are read, avoiding full‑row I/O.

Same‑type column storage enables ten‑fold compression, further reducing I/O.

ClickHouse applies specialized search algorithms tailored to storage patterns.

4. Pitfalls Encountered

Differences in data types between MySQL and ClickHouse caused errors; the solution was to cast IDs to a common unsigned type, e.g., toUInt32(h.id) = toUInt32(ec.post_id). Additionally, ClickHouse’s MergeTree engine guarantees only eventual consistency, so for strict consistency a full‑copy sync is recommended.

5. Summary

By adopting ClickHouse, the author solved the MySQL query bottleneck, achieving sub‑second responses for datasets up to 2 billion rows. ClickHouse also scales horizontally via clusters, making it a viable solution for large‑scale analytical workloads.