Phân tích kế hoạch thực thi truy vấn với EXPLAIN
Trước khi tối ưu hóa, bạn cần hiểu cách Apache Doris chuyển đổi câu lệnh SQL thành kế hoạch thực thi vật lý. Command EXPLAIN cho phép bạn nhìn thấy các bước scan, join, filter và aggregate mà hệ thống sẽ thực hiện.
Thực hiện truy vấn EXPLAIN trên một bảng dữ liệu lớn để xem kế hoạch mặc định.
EXPLAIN SELECT category, SUM(sales_amount) FROM sales_fact WHERE date >= '2023-01-01' GROUP BY category;
Kết quả sẽ hiển thị cây các operator (Fragments). Bạn cần chú ý vào các thông số Scan, Filter, và Aggregation. Nếu thấy số lượng row scan lớn hơn dự kiến, hệ thống đang quét toàn bộ dữ liệu (Full Table Scan) thay vì chỉ quét các phần cần thiết.
Để xem chi tiết hơn về việc phân phối dữ liệu giữa các Fragment, sử dụng tùy chọn VERBOSE.
EXPLAIN VERBOSE SELECT category, SUM(sales_amount) FROM sales_fact WHERE date >= '2023-01-01' GROUP BY category;
Kết quả sẽ hiển thị thêm thông tin về Fragment, Exchange (cách dữ liệu di chuyển giữa các BE) và Cardinality (ước lượng số dòng). Nếu thấy Exchange xảy ra nhiều lần hoặc Cardinality tăng đột biến, đây là dấu hiệu cần tối ưu hóa Partition hoặc Index.
Verify kết quả
Kiểm tra xem kế hoạch có còn chứa TableScan trên toàn bộ bảng không. Nếu đã tối ưu, bạn sẽ thấy các bước Filter hoặc IndexScan được thêm vào trước bước Aggregation.
Áp dụng các kỹ thuật Index để tăng tốc truy vấn
Tạo Bitmap Index cho các cột có Cardinality thấp
Bitmap Index cực kỳ hiệu quả cho các cột có ít giá trị lặp lại (như trạng thái, giới tính, loại sản phẩm). Nó giúp Doris loại bỏ nhanh các block dữ liệu không thỏa mãn điều kiện WHERE.
Tạo Bitmap Index cho cột status trong bảng sales_fact với các giá trị thường gặp.
ALTER TABLE sales_fact ADD BITMAP INDEX idx_status (status) PROPERTIES ("index_type" = "BITMAP");
Kết quả: Hệ thống sẽ tự động tính toán bitmap cho cột này. Khi bạn chạy query WHERE status = 'shipped', Doris sẽ chỉ scan các block chứa giá trị này, giảm I/O đáng kể.
Tạo Inverted Index cho các cột văn bản (Full-text)
Inverted Index phù hợp cho các cột chứa chuỗi văn bản dài, cần tìm kiếm theo từ khóa (như mô tả sản phẩm, nội dung bình luận). Nó hoạt động giống như mục lục của một cuốn sách.
Thêm Inverted Index cho cột product_desc để tìm kiếm nhanh các từ khóa.
ALTER TABLE sales_fact ADD INVERTED INDEX idx_desc (product_desc) PROPERTIES ("index_type" = "INVERTED");
Kết quả: Khi thực hiện WHERE product_desc LIKE '%laptop%', Doris sẽ tra cứu từ điển từ khóa thay vì quét toàn bộ nội dung văn bản.
Tối ưu Zone Map cho các cột số và ngày tháng
Zone Map lưu trữ Min/Max của mỗi Data File (Segment). Đây là cơ chế mặc định nhưng có thể được cấu hình để loại trừ dữ liệu hiệu quả hơn cho các cột số hoặc ngày.
Đảm bảo Zone Map được kích hoạt cho cột date và sales_amount. Trong Doris, Zone Map tự động được tạo cho các cột trong bảng, nhưng bạn có thể kiểm tra xem nó có đang loại bỏ segment không.
SHOW INDEX FROM sales_fact;
Kết quả: Bạn sẽ thấy danh sách các index đã tạo. Đối với Zone Map, Doris sử dụng nó tự động trong quá trình query. Để verify hiệu quả, chạy lại EXPLAIN và xem phần ZoneMapFilter trong plan.
Verify kết quả
Chạy lại câu truy vấn ban đầu với EXPLAIN sau khi đã thêm Index.
EXPLAIN SELECT category, SUM(sales_amount) FROM sales_fact WHERE status = 'shipped' AND product_desc LIKE '%laptop%';
Kiểm tra xem plan có chứa BitmapIndexScan hoặc InvertedIndexScan thay vì TableScan hay không. Thời gian thực thi (latency) trong EXPLAIN ước tính cũng sẽ giảm.
Tối ưu hóa truy vấn SQL với Aggregation và Group By
Sử dụng Rollup (Aggregation Table) cho các truy vấn thường xuyên
Thay vì tính toán lại tổng hợp (SUM, COUNT) mỗi lần truy vấn, hãy tạo sẵn các bảng Rollup (Materailized View trong Doris) lưu kết quả đã tính toán trước.
Tạo Rollup để tổng hợp dữ liệu theo ngày và danh mục, phục vụ cho báo cáo bán hàng hàng ngày.
ALTER TABLE sales_fact ADD ROLLUP rp_daily_category (date, category, sales_amount) AGGREGATE KEY(date, category) DISTRIBUTE BY HASH(date) BUCKETS 10;
Kết quả: Khi bạn chạy query SELECT date, category, SUM(sales_amount) FROM sales_fact GROUP BY date, category, Doris sẽ tự động chọn bảng Rollup rp_daily_category để trả lời, bỏ qua việc scan toàn bộ bảng gốc.
Sử dụng Materialized View cho các truy vấn phức tạp
Với các truy vấn kết hợp nhiều bảng (JOIN) và nhiều nhóm (GROUP BY), Materialized View giúp Doris tự động duy trì và chọn nguồn dữ liệu tối ưu nhất.
Tạo Materialized View để kết hợp dữ liệu bán hàng và thông tin khách hàng.
CREATE MATERIALIZED VIEW mv_customer_sales AS SELECT c.customer_id, c.region, SUM(s.sales_amount) as total_sales FROM sales_fact s JOIN customer_dim c ON s.customer_id = c.customer_id GROUP BY c.customer_id, c.region;
Kết quả: Khi truy vấn SELECT region, SUM(total_sales) FROM mv_customer_sales GROUP BY region, Doris sẽ tự động chuyển đổi query sang dùng Materialized View này, loại bỏ bước JOIN tốn kém.
Tránh sử dụng hàm không xác định (Non-deterministic) trong Group By
Đảm bảo các hàm dùng trong GROUP BY hoặc SELECT là xác định (ví dụ: MIN, MAX, SUM). Tránh dùng UUID() hoặc NOW() trong các biểu thức nhóm vì nó ngăn Doris sử dụng Rollup hoặc Index.
So sánh hai câu lệnh sau. Câu lệnh sai sẽ buộc Doris phải tính toán lại toàn bộ.
-- Không nên: Sử dụng hàm không xác định trong GROUP BY
SELECT category, MAX(NOW()) as last_update FROM sales_fact GROUP BY category;
-- Nên: Tách biệt hoặc dùng giá trị cố định
SELECT category, MAX(update_time) as last_update FROM sales_fact GROUP BY category;
Kết quả: Câu lệnh thứ hai cho phép Doris tận dụng các chỉ mục và Rollup đã có sẵn, trong khi câu lệnh đầu tiên buộc phải quét toàn bộ dữ liệu.
Verify kết quả
Chạy EXPLAIN trên truy vấn tổng hợp. Kiểm tra xem TableScan có chuyển thành RollupScan hoặc MaterializedViewScan không.
EXPLAIN SELECT date, category, SUM(sales_amount) FROM sales_fact WHERE date >= '2023-01-01' GROUP BY date, category;
Cấu hình Partition và Bucketing để phân phối dữ liệu
Phân vùng (Partition) theo thời gian (Time-based Partitioning)
Partition giúp Doris loại bỏ nhanh các phần dữ liệu cũ không cần thiết (Partition Pruning). Dữ liệu nên được chia theo ngày, tháng hoặc năm tùy vào tần suất truy vấn.
Thêm Partition mới cho tháng tiếp theo để đảm bảo dữ liệu mới được lưu vào đúng nơi.
ALTER TABLE sales_fact ADD PARTITION p202304 ("2023-04");
Kết quả: Khi bạn chạy query WHERE date >= '2023-04-01', Doris sẽ chỉ scan Partition p202304 và các partition sau đó, bỏ qua hoàn toàn các partition cũ như p202301 đến p202303.
Cấu hình Bucketing để cân bằng tải (Load Balancing)
Bucketing chia dữ liệu trong một Partition thành các đoạn nhỏ hơn dựa trên Hash của một cột. Điều này giúp cân bằng dữ liệu giữa các BE và giảm xung đột khi ghi dữ liệu.
Thiết lập số lượng Bucket phù hợp với số lượng BE và lượng dữ liệu. Quy tắc chung: Số bucket nên là bội số của số BE, và mỗi bucket không nên vượt quá 2GB - 4GB.
ALTER TABLE sales_fact PARTITION p202304 SET ("bucket_num" = "10");
Kết quả: Dữ liệu trong Partition p202304 sẽ được phân phối đều vào 10 bucket khác nhau. Khi truy vấn, Doris có thể song song hóa việc scan dữ liệu trên các bucket này.
Sử dụng Distributed Query với Partition Key
Để đạt hiệu suất cao nhất, các điều kiện trong WHERE nên bao gồm cột Partition Key. Nếu không, Doris có thể bị buộc phải scan toàn bộ các partition.
Chạy truy vấn với điều kiện trên cột date (partition key) để kiểm tra Partition Pruning.
SELECT COUNT(*) FROM sales_fact WHERE date >= '2023-04-01' AND date < '2023-05-01';
Kết quả: Trong EXPLAIN, bạn sẽ thấy chỉ có một hoặc một vài partition được liệt kê trong Fragment, thay vì toàn bộ danh sách partition của bảng.
Verify kết quả
Kiểm tra trạng thái phân phối dữ liệu bằng lệnh SHOW PARTITIONS và so sánh số lượng row trong mỗi partition.
SHOW PARTITIONS FROM sales_fact;
Kiểm tra xem dữ liệu mới có được ghi vào partition mới nhất không và các partition cũ có đang bị loại bỏ trong query không bằng cách chạy EXPLAIN trên query có điều kiện thời gian.
Điều hướng series:
Mục lục: Series: Triển khai Database Columnar với Apache Doris trên Ubuntu 24.04
« Phần 5: Nhập dữ liệu vào Apache Doris từ nhiều nguồn khác nhau
Phần 7: Quản lý dữ liệu: Backup, Restore và Data Lifecycle Management »