Worksheet 2: Thiết kế sort key (ORDER BY)

Thời lượng dự kiến: 20–25 phút Tài liệu tham chiếu: Các engine MergeTree — phần ORDER BY Design

Khái niệm

Mệnh đề ORDER BY của ClickHouse không phải để trang trí. Nó định nghĩa primary index — một index thưa ở mức block, cho phép ClickHouse bỏ qua những block dữ liệu không liên quan khi đánh giá mệnh đề WHERE. Nó cũng quyết định thứ tự sắp xếp vật lý của dữ liệu trong các part, điều này ảnh hưởng đến độ nén.

ORDER BY sai = truy vấn chậm + lưu trữ tốn vô ích. Một ORDER BY bắt đầu bằng UUID nghĩa là không bỏ qua được block nào cho bất kỳ truy vấn phân tích nào (UUID là ngẫu nhiên; không có tiền tố nào sắp xếp được). Một ORDER BY bắt đầu bằng ngày nghĩa là các truy vấn lọc theo ngày bỏ qua phần lớn bảng.

Ba nguyên tắc thiết kế ORDER BY

Nguyên tắc 1: Suy ra từ các bộ lọc truy vấn, không từ schema nguồn. Hãy xem các cột trong WHERE, GROUP BY và JOIN trên những truy vấn thường xuyên nhất của bạn. Các cột được lọc nhiều nhất có lẽ nên xuất hiện trong ORDER BY (miễn là lực lượng không quá lớn). Primary key của bảng nguồn (nếu có) thường không liên quan.

Nguyên tắc 2: Lực lượng thấp trước, lực lượng cao sau. Primary index của ClickHouse có một entry cho mỗi khoảng ~8192 dòng (một granule). Các cột lực lượng thấp (ví dụ: toStartOfMonth(date) = ~48 giá trị phân biệt trong 4 năm) gom nhiều dòng lại với nhau — index có thể bỏ qua toàn bộ granule. Các cột lực lượng cao (ví dụ: trip_id = 50M giá trị phân biệt) là duy nhất theo từng dòng — đặt chúng trước nghĩa là index không bỏ qua được gì cả. Thứ tự này là mặc định, không phải sự phủ định Nguyên tắc 1: một cột mà truy vấn lọc theo khoảng, loại bỏ phần lớn số dòng, vẫn có thể xứng đáng đứng đầu thay cho một cột lực lượng thấp hơn nhưng chỉ luôn được lọc theo đẳng thức.

Nguyên tắc 3: Với ReplacingMergeTree, kết thúc bằng định danh dòng duy nhất. Khóa khử trùng lặp là toàn bộ tuple ORDER BY. Nếu trip_id thiếu khỏi ORDER BY, hai chuyến đi khác nhau có cùng pickup_at và không có cột nào khác nữa sẽ bị coi là trùng lặp. Đặt trip_id cuối cùng để bảo đảm tính duy nhất mà không làm giảm hiệu năng của index.

Bài tập: phân tích workload truy vấn

Trước khi thiết kế sort key, hãy tìm ra các truy vấn thực sự lọc trên những cột nào. Lab NYC Taxi có 7 truy vấn tiêu biểu; với từng truy vấn, hãy chọn cột lọc loại bỏ được nhiều dòng nhất.

Bài tập: ước lượng lực lượng

Với từng cột ORDER BY ứng viên, hãy ước lượng lực lượng của nó trên tập dữ liệu 4 năm, 50M dòng. Phần lớn bảng bên dưới là dữ liệu tham chiếu — hai ô còn trống là số giá trị phân biệt ước lượng của chính pickup_at và lớp lực lượng của nó. Bốn năm là khoảng 126 triệu giây (và chỉ khoảng 2.1 triệu phút), producer gán timestamp cho mọi chuyến đi theo đồng hồ hệ thống, và PICKUP_AT được lưu dưới dạng DateTime64(3, 'UTC') — hãy tính xem bao nhiêu trong số các khe đó có thể bị 50 triệu chuyến đi chiếm dụng trước khi bạn chọn một khoảng.

Bài tập: thiết kế sort key

Dựa trên phân tích workload truy vấn và các ước lượng lực lượng của bạn, hãy đề xuất một ORDER BY cho trips_raw, fact_trips và agg_hourly_zone_trips. Nhắc lại:

• Lực lượng thấp trước → bỏ qua được nhiều block nhất.
• Các cột xuất hiện trong WHERE/GROUP BY của nhiều truy vấn → hãy đưa chúng vào.
• Với các bảng ReplacingMergeTree → kết thúc bằng định danh dòng duy nhất.
• Đừng đưa vào những cột không bao giờ được lọc.

Sau đó làm các câu hỏi lập luận và suy ngẫm khi mọi bảng đã được điền.

Loading worksheet...

Chuyển sang migration-plan.md

Khi bạn đã điền xong worksheet này, hãy sao các quyết định ORDER BY của bạn vào Phần 4 của migration-plan.md và tick ô:

- [ ] Sort key design: completed