Xin chào mn, lâu rồi mình không viết bài rùi nhỉ .
Hôm nay mình sẽ chia sẻ 1 bài viết chủ đề SQL, khá là hơi lạc lạc đề với mấy bài mình chia sẻ trước đây. Trước đây mình toàn dùng Pandas của Python là Chính SQL thì cũng chỉ mấy câu Select hay Sort thôi, cũng phải nói là khá là gà về SQL.
Bài Toán của mình gặp phải: Build ETL bằng DBT, như mn cũng biết DBT sẽ dùng SQL kiểu Jinja ý, mình sẽ không bàn về DBT ở đây nhé mn có thể tham khảo bài viết của mình ở đây . Mà chạy Transform DBT mất tận 2-3 ngày thì xem như là bỏ vì vậy làm sao để giảm Cost? Tăng cấu hình (cái này cũng khó nha vì budget có hạn) => Mình phải tối ưu code SQL làm sao để giảm được thời gian chạy. Mình cũng vât lộn mãi vì SQL của mình cũng chỉ là biết để dùng chứ cũng không phải giỏi lắm. Mn cùng xem mình giải quyết như nào nhé.
Common Table Expression (CTE)
Vậy thì vì sao “WITH … AS (…)” có thể tối ưu ETL SQL của ?
Chia nhỏ logic, giảm phức tạp
Thay vì viết một truy vấn “khổng lồ”, bạn tách thành các bước đặt tên rõ ràng—ví dụ raw_events, deduped_events, joined_customers, final_kpi. Code trở nên dễ đọc, dễ debug; khi lỗi xảy ra bạn chỉ cần xem đúng bước liên quan thay vì dò cả truy vấn dài.
Tái sử dụng kết quả trung gian
Nhiều sub-query lặp đi lặp lại sẽ tốn thời gian tối ưu và CPU. Với CTE, bạn định nghĩa một lần rồi gọi nhiều lần. Phần lớn warehouse (Snowflake, Postgres, BigQuery Standard SQL…) chỉ thực thi một lần rồi chia sẻ kết quả cho mọi chỗ dùng, nhờ đó giảm I/O và CPU.
Đẩy bộ lọc sớm (predicate push-down)
Nếu bộ lọc được đặt ngay trong CTE đầu tiên (ví dụ chỉ lấy active = true và dữ liệu 7 ngày gần nhất), mọi CTE sau chỉ xử lý tập dữ liệu đã rút gọn. Điều này đặc biệt hiệu quả trên kho dữ liệu cột như Snowflake, BigQuery hay Redshift: lượng dữ liệu quét ít hơn, chi phí cũng thấp hơn.
Thay thế bảng tạm vật lý khi dùng dbt
Trong dbt, bạn có thể biến mỗi bước trung gian thành ephemeral model (qua config(materialized='ephemeral')). dbt sẽ tự “inline” model này thành CTE, không ghi ra bảng tạm, giúp cắt giảm chi phí lưu trữ và quyền truy cập, đồng thời vẫn giữ khả năng ref() giữa các bước như xếp LEGO.
Dưới đây là ví dụ về CTE
SELECT * FROM {{ ref('stg_orders') }} WHERE order_date >= {{ dbt_date.today_minus(7) }} {% endset %} WITH recent_orders AS ( {{ recent_orders }} ),
agg AS (
SELECT customer_id, COUNT(*) cnt
FROM recent_orders
GROUP BY 1
)
SELECT * FROM agg;
Cách dùng CTE hiệu quả trong DBT
để CTE phát huy tối đa công lực trong DBT thì chúng ta cần dùng thêm: is_incremental()
WITH base AS ( SELECT * FROM {{ source('fct_events') }} {% if is_incremental() %} WHERE event_time > (SELECT MAX(event_time) FROM {{ this }}) {% endif %}
)
Túm lại: CTE không chỉ giúp “đẹp” code; nó giảm duplicate query, đẩy bộ lọc sớm, tránh bảng tạm, hỗ trợ incremental và unit test—toàn bộ đều dẫn tới truy vấn nhanh hơn
Đánh Index hiệu quả
đánh index có thể giúp chúng ta tăng tốc truy vấn quan trọng, giảm chi phí quét dữ liệu, nhưng không biến hệ thống thành “rừng index” tốn storage và làm chậm ghi.
Vậy thì đánh index như thế nào mới hiệu quả?
Code mà người giao trước cho mình index vào element mà chỉ có tầm 10 cluster trong khi đó data thì khoảng 3 triệu dòng . Vì vậy mình phải đánh index lại chọn những trường phù hợp và hiệu quả, có những table mình phaải dùng 2 element làm index. Mình thấy quan trọng nhất là hiểu dữ liệu mà bạn đang có là gì.
Khi quyết định có nên tạo chỉ mục (index) cho một cột hay không, hãy tự đặt ra bốn câu hỏi sau và đối chiếu ý nghĩa của từng câu hỏi:
Truy vấn nào thật sự “nóng”? – Chỉ nên đánh index trên những cột (hoặc tổ hợp cột) thường xuyên được quét (scan) hay kết hợp (join) và có điều kiện lọc rõ ràng. Nếu truy vấn ít xuất hiện hoặc chỉ chạy ad-hoc, index sẽ không mang lại lợi ích đáng kể.
Cardinality ra sao? – Cột có giá trị đa dạng (high-cardinality) càng xứng đáng được index vì khả năng phân tán và loại bỏ bản ghi cao. Với cột low-cardinality (chỉ lặp lại < 10 giá trị), trình tối ưu hoá ít tận dụng index, hiệu quả giảm đáng kể.
Mẫu truy vấn có ổn định không? – Nếu cấu trúc truy vấn thay đổi thường xuyên, index dễ trở nên lỗi thời hoặc vô dụng. Hãy quan sát log truy vấn hoặc dashboard BI trong một khoảng thời gian đủ dài trước khi quyết định.
Chi phí ghi và cập nhật có chấp nhận được? – Mỗi thao tác INSERT/UPDATE phải duy trì lại index, làm tăng độ trễ và tốn CPU. Với hệ thống OLTP ghi rất nhiều, cần cân nhắc kỹ trước khi thêm index mới.
Với mỗi hệ quản trị sẽ có các cơ chế khác nhau: Ví dụ PostgreSQL thường dùng:
• B-tree (mặc định) cho =, <, >.
• GIN cho JSONB, array.
• BRIN cho bảng rất lớn, giá trị gần-nhau theo thời gian.
• Partial Index: WHERE is_active.
• Covering Index: INCLUDE(column_x) giảm truy cập bảng.
Và phải luôn chạy EXPLAIN (ANALYZE, BUFFERS) trước & sau. Kiểm tra pg_stat_user_indexes để xoá index “unused”.
Khi nào không nên thêm Index
Bảng chỉ thỉnh thoảng truy vấn, chủ yếu ghi (OLTP nặng).
Cột low-cardinality (status IN ('A','B','C')) + bảng nhỏ (< 100k dòng).
Truy vấn ad-hoc, không xuất hiện trong dashboard/ELT định kỳ.
Đánh index thông minh là nghệ thuật cân bằng:
Đúng chỗ – nơi thật sự đắt khi thiếu index.
Đúng loại – phù hợp engine & mẫu truy vấn.
Đúng thời điểm – sau khi quan sát log, không suy đoán
DISTINCT vs JOIN – “Giấu rác” hay “Gỡ rối”?
Nhiều bạn mới tối ưu SQL thường thêm DISTINCT để “khử trùng lặp cho nhanh”, nhưng nếu lý do thật sự nằm ở JOIN sai thì DISTINCT chỉ che khuyết điểm chứ không chữa tận gốc. Chính mình ban đầu vào cũng gặp phải, tất nhiên có những cái mình dùng DIstinct nó lại hiệu quả nhưng không phải là tất cả. Dưới đây là vài nguyên tắc giúp bạn quyết định nên dùng DISTINCT hay viết lại JOIN.
Distinct lúc nào hợp lý?
-
Dedup dữ liệu thô: Bảng raw log/CDC có thể ghi lặp, bạn cần lấy unique record cho staging.
-
Khởi tạo bảng dimension: Lấy danh sách duy nhất các giá trị (country list, product category…)
-Thống kê nhanh, ad-hoc : Bạn cần liệt kê nhanh giá trị khác nhau, không quan tâm cost.
Lúc nào không nên dùng Distinct?
Mỗi khi Join phải Distinct => có vẻ hơi cấn rùi nhỉ => kiểm tra lại điều kiện Join, tránh cartesian hoặc 1-N.
Khi mà bạn dùng DIstinct xong lại còn Groupby => chỉ cần mỗi Groupby thui.
Distinct trong sub-query, rồi outer query join tiếp => gom JOIN vào cùng một CTE
Hiệu năng kém dù đã tăng warehouse Xem Query Plan: bước Aggregate (distinct) chiếm % thời gian? → thay bằng window function hoặc semi-join.
JOIN
Để JOIN sao cho tối ưu thì phải xác định quan hệ trước? customers (1) ⟶ (N) orders
Nếu cần “một hàng-một khách”, bạn không JOIN trực tiếp đơn hàng, mà phải roll-up trước:
WITH ord_agg AS ( SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id
)
SELECT c.*, o.total_orders
FROM customers c
LEFT JOIN ord_agg o USING (customer_id);
Và nên dùng Explain và Analysis Explain để xem có tối ưu hơn được không?
Hạn chế dùng Subquery
Code SQl mà mình được bàn giao có rất nhiều Subquery và mình không biết phải gỡ ở đâu? (vòng lặp n lần) => gỡ rối từng phần 1 để giảm cost, vì vậy Subquery đôi lúc rất tiện nhưng lại hại lúc cost quá lớn
=> Mình đã chia nhỏ code của SQL ra từng CTE một sử dụng window function (ROW_NUMBER(), AVG() OVER …) và JOIN + GROUP BY (tùy thuộc vào từng model). Trong DBT có ephemeral model sẽ không tạo table và không tốn cost,.
Và quan trọng vẫn là luôn dùng Explain để xem cost như nào để biết mình tối ưu đúng hay không. việc nào quan trọng mình nhắc nhiều lần
Kết Luận
Ở bài viết này mình nghĩ là chưa đầy đủ nhưng đây là những cách giúp mình tối ưu cost từ 48h xuống còn 1h( chính xác là tầm 40p), có thể 1 số chỗ mình hiểu chưa đúng lắm mong mọi người góp ý ạ. Mọi người có những tips hay kinh nghiệm nào nữa có thể comment chia sẻ nhé ạ.
Cảm ơn mọi người đã đọc bài viết ạ.
Reference
https://www.google.com/ < đợt đó mình tham khảo nhiều nguồn lắm mình không nhớ nữa >