ClickHouse 3 — Họ engine MergeTree

Ở bài 2 chúng ta đã thấy ClickHouse lưu dữ liệu theo cột, nén mạnh, và tổ chức thành các "part" trên đĩa. Nhưng chính xác thì cái gì quyết định dữ liệu được sắp xếp thế nào, được nén ra sao, được gộp lúc nào, khi nào tự xoá? Câu trả lời nằm ở table engine — và cụ thể hơn, ở họ engine MergeTree. Đây là kiến thức nền tảng nhất để dùng ClickHouse đúng: chọn sai ORDER BY hay dùng nhầm biến thể là bảng chậm gấp chục lần hoặc trả về số sai.

Table engine là gì trong ClickHouse?

Trong PostgreSQL hay Oracle, "engine lưu trữ" gần như cố định (heap + B-tree). Trong ClickHouse, mỗi bảng phải khai báo rõ engine — nó định nghĩa toàn bộ hành vi vật lý của bảng: dữ liệu lưu ở đâu, có sắp xếp không, có index không, đọc/ghi song song thế nào, có replicate không, có tự khử trùng lặp hay cộng dồn không.

Có vài nhóm engine chính:

Trong series này, khi nói "bảng ClickHouse" gần như luôn là MergeTree hoặc một biến thể của nó. Các engine khác (Distributed, Kafka) sẽ gặp ở bài 6 và bài 4; chúng thường "bọc" một bảng MergeTree bên dưới.

MergeTree — engine nền tảng

MergeTree là engine cơ sở mà mọi biến thể khác kế thừa. Ý tưởng cốt lõi rất giống LSM-tree (log-structured merge tree) mà bạn có thể đã gặp ở RocksDB/Cassandra:

1. Mỗi lần INSERT, ClickHouse không ghi chèn vào dữ liệu cũ. Nó tạo ra một part mới — một thư mục chứa các file cột đã được sắp xếp theo ORDER BY và nén sẵn.
2. Nền tảng chạy các luồng merge bất đồng bộ ở hậu trường, gộp nhiều part nhỏ thành part lớn hơn (giống compaction trong LSM).
3. Khi đọc, ClickHouse quét song song tất cả các part liên quan.

Đây là lý do vàng khiến ClickHouse ghi nhanh khủng khiếp: insert chỉ là ghi tuần tự một part mới, không cần cập nhật index B-tree tại chỗ. Nhưng nó cũng sinh ra hệ quả quan trọng — nhiều part nhỏ = đọc chậm và tốn tài nguyên, nên bạn phải insert theo lô lớn (batch), không insert từng dòng.

ORDER BY — quyết định sắp xếp và primary key

ORDER BY là tham số quan trọng nhất của MergeTree. Nó vừa xác định:

• Thứ tự sắp xếp vật lý dữ liệu bên trong mỗi part. Vì cột đã được sắp xếp, các giá trị giống/gần nhau nằm cạnh nhau → nén tốt hơn nhiều và quét tuần tự nhanh.
• Primary key thưa (sparse primary index): mặc định, primary key = ORDER BY. ClickHouse KHÔNG tạo index cho từng dòng như B-tree. Thay vào đó nó chia dữ liệu thành các "granule" (mặc định 8192 dòng) và chỉ lưu giá trị khoá của dòng đầu mỗi granule. Index này bé xíu, nằm gọn trong RAM, dùng để nhảy đến vùng granule chứa dữ liệu cần và bỏ qua phần còn lại.

Nguyên tắc chọn ORDER BY:

• Đặt các cột hay dùng để lọc (WHERE) ở đầu, theo thứ tự độ chọn lọc thấp → cao (low cardinality trước). Ví dụ ORDER BY (branch_id, event_date, customer_id) cho phép prune hiệu quả khi lọc theo chi nhánh rồi ngày.
• Cột đầu tiên nên là cột bạn gần như luôn lọc/gộp theo.
• Đừng nhồi quá nhiều cột — mỗi cột thêm làm index dài và merge tốn hơn.

PARTITION BY — phân vùng để prune và quản lý TTL

PARTITION BY chia bảng thành các partition logic độc lập (mỗi partition là tập part riêng). Khác với ORDER BY (sắp xếp trong part), partition là ranh giới thô ở cấp thư mục.

Quy ước thực tế:

• Phân vùng theo tháng (toYYYYMM(event_date)) là mặc định an toàn cho hầu hết bảng log/giao dịch. Theo ngày (toYYYYMMDD) chỉ khi dữ liệu cực lớn và cần drop từng ngày.
• Lợi ích: partition pruning — query lọc theo ngày chỉ đọc partition liên quan; và quản lý vòng đời — ALTER TABLE ... DROP PARTITION '202601' xoá cả tháng tức thì (chỉ xoá thư mục), là cách rẻ nhất để dọn dữ liệu cũ.
• Cảnh báo: quá nhiều partition (ví dụ phân vùng theo giờ, hay theo customer_id) là lỗi kinh điển — sinh hàng nghìn thư mục nhỏ, merge không kịp, đọc chậm. Giữ số partition ở mức hàng chục đến vài trăm.

Lưu ý: partition không thay thế ORDER BY cho việc lọc. Bạn vẫn cần cột ngày trong ORDER BY để prune bên trong partition.

PRIMARY KEY — có thể tách khỏi ORDER BY

Mặc định PRIMARY KEY = ORDER BY, nhưng bạn có thể khai báo PRIMARY KEY ngắn hơn ORDER BY. Điều kiện: PRIMARY KEY phải là tiền tố (prefix) của ORDER BY.

Vì sao cần tách? Khi bạn cần sắp xếp theo nhiều cột (để nén tốt và khử trùng lặp đúng) nhưng chỉ muốn index thưa dựa trên vài cột đầu, giảm kích thước index trong RAM:

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE txn_events
(
    branch_id   UInt16,
    event_date  Date,
    customer_id UInt64,
    txn_id      UInt64,
    amount      Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
PRIMARY KEY (branch_id, event_date)          -- index thưa gọn, chỉ 2 cột
ORDER BY (branch_id, event_date, customer_id, txn_id);  -- sắp xếp chi tiết hơn

Ở đây index chỉ dựa trên (branch_id, event_date) nên nhẹ, nhưng dữ liệu vẫn sắp xếp tới txn_id để nén tốt.

SAMPLE BY — lấy mẫu xác định

SAMPLE BY cho phép query lấy mẫu một tỷ lệ dữ liệu một cách xác định (deterministic) và nhanh — hữu ích cho dashboard ước lượng trên bảng cực lớn. Cột dùng cho SAMPLE BY phải nằm trong ORDER BY và thường là một hàm hash (ví dụ cityHash64(customer_id)).

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE web_hits
(
    event_date Date,
    user_id    UInt64,
    url        String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, cityHash64(user_id))
SAMPLE BY cityHash64(user_id);

-- SELECT count() * 10 FROM web_hits SAMPLE 0.1 WHERE event_date = today();

SAMPLE 0.1 chỉ đọc ~10% granule → nhanh gấp ~10 lần, đổi lấy sai số thống kê. Với đo lường xu hướng (không cần con số kế toán chính xác) đây là công cụ rất mạnh.

Các biến thể quan trọng và khi nào dùng

Điểm khác biệt duy nhất giữa các biến thể là chúng làm gì khi merge các dòng có cùng khoá sắp xếp (ORDER BY). Ngoài lúc merge, hành vi giống hệt MergeTree.

ReplacingMergeTree — khử trùng lặp theo version

Giữ lại một dòng cho mỗi khoá ORDER BY; khi có nhiều dòng cùng khoá, giữ dòng có version lớn nhất (nếu khai báo cột version) hoặc dòng chèn sau cùng.

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE customer_profile
(
    customer_id UInt64,
    full_name   String,
    kyc_status  String,
    updated_at  DateTime
)
ENGINE = ReplacingMergeTree(updated_at)   -- version = updated_at
ORDER BY customer_id;

Dùng khi: bảng "trạng thái mới nhất" (upsert giả lập) — hồ sơ khách hàng, catalog sản phẩm, snapshot số dư.

Bẫy cực kỳ quan trọng — merge bất định: ReplacingMergeTree chỉ khử trùng lặp khi merge, mà merge chạy bất đồng bộ và không có gì đảm bảo thời điểm. Ngay sau khi insert 2 phiên bản của cùng customer_id, một câu SELECT * FROM customer_profile WHERE customer_id = 123 hoàn toàn có thể trả về cả 2 dòng. Đừng bao giờ giả định dữ liệu đã được khử. Cách xử lý đúng khi đọc:

• Thêm FINAL (SELECT ... FROM customer_profile FINAL) — ép gộp lúc đọc, đúng nhưng chậm và nặng tài nguyên.
• Hoặc dùng aggregation với argMax: SELECT customer_id, argMax(kyc_status, updated_at) FROM customer_profile GROUP BY customer_id — cách này nhanh và tin cậy hơn FINAL cho query lớn.

SummingMergeTree — cộng dồn

Khi merge, các dòng cùng ORDER BY được cộng các cột số lại thành một dòng. Rất hợp cho bảng đếm/tổng đã pre-aggregate.

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE daily_txn_by_branch
(
    branch_id  UInt16,
    txn_date   Date,
    txn_count  UInt64,
    total_amt  Decimal(18, 2)
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(txn_date)
ORDER BY (branch_id, txn_date);   -- key gộp: chi nhánh + ngày

Insert nhiều dòng (branch_A, 2026-06-01, 1, 100) → sau merge thành (branch_A, 2026-06-01, N, tổng). Cùng lưu ý merge bất định: nếu đọc trước khi merge xong, bạn phải SUM(...) GROUP BY ... để có số đúng — đừng đọc thẳng.

AggregatingMergeTree — rollup tổng quát

Tổng quát hoá SummingMergeTree: thay vì chỉ cộng, nó lưu aggregate state (trạng thái trung gian của hàm gộp) cho phép mọi hàm — uniq, avg, quantile, min/max... Cột phải kiểu AggregateFunction(...), ghi bằng hàm -State và đọc bằng hàm -Merge. Đây là engine đứng sau các materialized view rollup mạnh nhất (xem bài 5).

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE mv_uniq_customers
(
    txn_date  Date,
    branch_id UInt16,
    uniq_cust AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (txn_date, branch_id);
-- ghi:  uniqState(customer_id)
-- đọc:  uniqMerge(uniq_cust)

Collapsing / VersionedCollapsing — mô phỏng update/delete

ClickHouse không giỏi update/delete từng dòng (chúng là thao tác nặng, bất đồng bộ qua ALTER ... UPDATE). Để mô phỏng "sửa/xoá" theo dòng, dùng CollapsingMergeTree: mỗi bản ghi có cột sign = +1 (trạng thái) hoặc -1 (huỷ trạng thái cũ). Khi merge, cặp +1/-1 cùng khoá triệt tiêu nhau.

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE account_state
(
    account_id UInt64,
    balance    Decimal(18, 2),
    sign       Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY account_id;
-- "Cập nhật": ghi 1 dòng sign=-1 (giá trị cũ) + 1 dòng sign=+1 (giá trị mới)

VersionedCollapsingMergeTree(sign, version) bổ sung cột version để collapse đúng ngay cả khi các dòng đến sai thứ tự — cần thiết khi dữ liệu từ Kafka có thể lệch trật tự. Đọc collapsing table cũng phải cẩn thận: tính SUM(metric * sign) hoặc dùng FINAL, không đọc thô.

TTL — tự xoá / chuyển tier dữ liệu cũ

TTL (time-to-live) cho phép khai báo quy tắc vòng đời trực tiếp trên bảng: dữ liệu quá hạn sẽ tự động bị xoá hoặc chuyển sang tier lưu trữ rẻ hơn khi merge chạy. Rất hợp với retention log/audit của ngân hàng.

-- ClickHouse (minh hoạ — KHÔNG chạy trong sandbox)
CREATE TABLE audit_log
(
    event_time DateTime,
    user_id    UInt64,
    action     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 90 DAY DELETE,                 -- xoá sau 90 ngày
    event_time + INTERVAL 30 DAY TO VOLUME 'cold';       -- 30 ngày: chuyển đĩa lạnh

TTL cũng có mức cột (col TTL ... ) để xoá riêng một cột nặng sau X ngày, hoặc gộp lại (GROUP BY ... SET) để rollup dữ liệu cũ thành bản tổng hợp. Lưu ý: TTL được áp dụng lúc merge nên cũng không tức thời — dữ liệu quá hạn có thể còn tồn tại một thời gian; nếu cần xoá cứng đúng hạn hãy kết hợp merge_with_ttl_timeout hoặc drop partition thủ công.

Cảnh báo xuyên suốt: merge là bất đồng bộ

Điểm mấu chốt cần khắc cốt ghi tâm cho toàn bộ họ MergeTree: merge là async, nên dữ liệu bạn đọc có thể là bản CHƯA gộp. Điều này đúng cho khử trùng lặp (Replacing), cộng dồn (Summing), rollup (Aggregating), collapse, và cả TTL. Quy tắc an toàn:

• Đừng bao giờ giả định "insert xong là đã dedup/tổng hợp".
• Khi cần kết quả đúng ngay: dùng GROUP BY + argMax/SUM(x*sign)/uniqMerge — nhanh, rẻ, tin cậy.
• FINAL cho kết quả đúng tuyệt đối nhưng đắt; chỉ dùng cho query nhỏ hoặc bảng nhỏ. ClickHouse mới có SETTINGS final = 1 và tối ưu FINAL song song, nhưng vẫn nên tránh trên bảng lớn.
• Chi tiết đọc/tối ưu sẽ ở bài 5 và bài 7.

Use case thực tế

Bối cảnh: Team dữ liệu NCB xây bảng ClickHouse phục vụ dashboard giám sát giao dịch thẻ theo thời gian thực. Nguồn: ~40 triệu bản ghi giao dịch/ngày đổ từ Kafka. Yêu cầu: dashboard tổng giao dịch theo chi nhánh/ngày, giữ chi tiết 90 ngày, số liệu tổng hợp giữ 3 năm.

Thiết kế:

1. Bảng chi tiết card_txn dùng MergeTree, PARTITION BY toYYYYMM(txn_time), ORDER BY (branch_id, txn_time, card_hash). Insert theo batch 50–100k dòng mỗi vài giây (không insert từng dòng từ Kafka — dùng bảng Kafka + materialized view, xem bài 4).
2. TTL trên card_txn: txn_time + INTERVAL 90 DAY DELETE — chi tiết tự dọn sau 90 ngày, khỏi cần cron xoá thủ công. Ước tính giữ ~3.6 tỷ dòng chi tiết, nén cột còn ~15% dung lượng thô.
3. Bảng rollup daily_branch_summary dùng SummingMergeTree, ORDER BY (branch_id, txn_date), được cập nhật bằng materialized view từ card_txn. Dashboard chỉ query bảng này → mỗi query đọc vài nghìn dòng thay vì hàng tỷ, phản hồi dưới 100ms.
4. Bảng trạng thái thẻ card_status dùng ReplacingMergeTree(updated_at) để giữ trạng thái mới nhất (active/blocked). Query đọc bằng argMax(status, updated_at) GROUP BY card_id, không dùng SELECT * để tránh thấy trạng thái cũ chưa merge.

Sự cố điển hình đã tránh: nhóm mới từng phân vùng PARTITION BY toYYYYMMDD + insert từng dòng → sinh hàng chục nghìn part, SELECT count() mất 8 giây và "too many parts" chặn insert. Chuyển sang phân vùng theo tháng + batch insert đưa về dưới 200ms.

Ghi nhớ

• Table engine định nghĩa toàn bộ hành vi vật lý của bảng; 99% bảng phân tích dùng họ MergeTree.
• MergeTree hoạt động kiểu LSM: insert → part mới → merge async. Insert theo lô lớn, tránh nhiều part nhỏ ("too many parts").
• ORDER BY là tham số quan trọng nhất: quyết định sắp xếp vật lý + primary key thưa (granule 8192 dòng). Đặt cột hay lọc lên đầu, low-cardinality trước.
• PARTITION BY thường theo tháng để prune và drop nhanh; đừng phân vùng quá mịn.
• PRIMARY KEY có thể là tiền tố ngắn hơn ORDER BY để index nhẹ; SAMPLE BY cho lấy mẫu xác định trên bảng cực lớn.
• Biến thể khác nhau ở hành vi lúc merge: Replacing (dedup theo version), Summing (cộng dồn), Aggregating (rollup mọi hàm), Collapsing/Versioned (mô phỏng update/delete).
• TTL tự xoá hoặc chuyển tier dữ liệu cũ — hợp retention log/audit; nhưng áp dụng lúc merge nên không tức thời.
• Merge là bất đồng bộ → đọc có thể thấy bản chưa gộp. Dùng GROUP BY + argMax/SUM(x*sign)/uniqMerge; hạn chế FINAL trên bảng lớn.