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).

By using ClickHouse we solved MySQL query bottlenecks: for data sets under 2 billion rows, 90% of queries finish within 1 second, and ClickHouse also supports clustering for larger volumes.

First we clarify basic concepts:

OLTP – traditional relational databases focused on insert/update/delete with strong transaction consistency (e.g., banking, e‑commerce).

OLAP – warehouse‑type databases focused on reading data for complex analysis and decision support.

Note: The article includes a link to 3625 pages of interview questions from large internet companies.

Understanding the difference between column‑store and row‑store databases:

In row‑store systems (MySQL, PostgreSQL, MS SQL Server) data is stored row by row:

In column‑store systems (ClickHouse) data is stored column by column:

The storage comparison is illustrated below:

2. Business Problem

The business layer stored a 50 million‑row table and two auxiliary tables in MySQL; a single join query took over 3 minutes. After index tuning, horizontal sharding, and logical optimization the improvement was limited, so ClickHouse was adopted.

Result: query time reduced to under 1 second, a 200‑fold speedup.

3. ClickHouse Practice

3.1 Installing ClickHouse on macOS

Installation was done via Docker (alternatively, compile from source).

Docker installation guide: https://blog.csdn.net/qq_24993831/article/details/103715194

3.2 Data Migration: MySQL → ClickHouse

ClickHouse supports most MySQL syntax, making migration low‑cost. Five migration approaches are listed:

create table engine mysql – data remains in MySQL.

insert into select from – create table first, then import.

create table as select from – create and import simultaneously.

CSV offline import.

streamsets.

We chose the third method (CREATE TABLE AS SELECT FROM):

CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE=MergeTree AS SELECT * FROM mysql('host:port','db','database','user','password')

3.3 Performance Test Comparison

Type

Data Volume

Table Size

Query Speed

MySQL

50 million

10 GB

205 s

ClickHouse

50 million

600 MB

under 1 s

3.4 Data Synchronization Solutions

Temporary Table Method

Source: Ctrip – create a temp table, fully sync MySQL data into ClickHouse temp table, then replace the original table. Suitable for moderate data volumes with frequent incremental changes.

Synch Tool

Synch is an open‑source sync software that reads MySQL binlog, extracts SQL statements, and processes tasks via a message queue.

3.5 Why is ClickHouse Fast?

Only reads columns required for computation, reducing I/O compared to row‑wise reads.

Same‑type columns enable up to ten‑fold compression, further lowering I/O.

ClickHouse applies storage‑specific search algorithms for optimal performance.

4. Pitfalls Encountered

4.1 Data Type Differences Between ClickHouse and MySQL

Direct MySQL queries caused errors. The solution was to cast IDs to a common unsigned type, e.g., LEFT JOIN B b ON toUInt32(h.id) = toUInt32(ec.post_id) .

4.2 Asynchronous Delete/Update – Only Eventual Consistency

Even the most consistent MergeTree engine guarantees only eventual consistency. For strict consistency, a full‑sync approach is recommended.

5. Conclusion

Through this ClickHouse practice we eliminated MySQL query bottlenecks, achieving sub‑second query times for data sets under 2 billion rows, and ClickHouse also scales with clustering for larger workloads.