- vừa được xem lúc

Mình đã tối ưu code SQL từ 48 giờ xuống còn 1 giờ chạy như nào?

0 0 2

Người đăng: Pham Thi Hong Anh

Theo Viblo Asia

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à 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 >

Bình luận

Bài viết tương tự

- vừa được xem lúc

Giới thiệu Stored Procedure trong SQL Server

Stored Procedure là 1 phần không thể thiếu của SQL Server. Chúng có thể hỗ trợ rất nhiều cho lập trình và cấu hình cơ sở dữ liệu.

0 0 174

- vừa được xem lúc

sử dụng index trong sql query

Index là một trong những yếu tố quan trọng nhất góp phần vào việc nâng cao hiệu suất của cơ sở dữ liệu. Index trong SQL tăng tốc độ của quá trình truy vấn dữ liệu bằng cách cung cấp phương pháp truy xuất nhanh chóng tới các dòng trong các bảng, tương tự như cách mà mục lục của một cuốn sách giúp bạn

0 0 275

- vừa được xem lúc

Hướng dẫn sửa lỗi không cài được SQL Server

Hôm qua do yêu cầu môn học, mình có cài lại Microsoft SQL Server. Trước đó mình có cài rồi, nhưng rồi lâu không dùng nên gỡ ra cho nhẹ máy. Bây giờ có dịp cần nên mình mới cài lại. Chi tiết lỗi mình gặp phải.

0 0 145

- vừa được xem lúc

Bạn nên tránh sử dụng Soft Delete khi có thể, và đây là lý do tại sao

Con người luôn luôn mắc sai lầm. Vì vậy, việc "lo xa" trước mọi tình huống xấu nhất chưa bao giờ là thừa.

0 0 213

- vừa được xem lúc

Sử dụng trigger trong SQL qua ví dụ cơ bản.

Trigger là gì . Cú pháp của Trigger. CREATE TRIGGER tên_trigger ON tên_bảng. FOR {DELETE, INSERT, UPDATE}.

0 0 173

- vừa được xem lúc

Khác biệt giữa khóa chính và khóa ngoại trong SQL

Các khoá chính và khóa ngoại là hai loại ràng buộc có thể được sử dụng để thực thi toàn vẹn dữ liệu trong các bảng SQL Server và đây là những đối tượng cơ sở dữ liệu quan trọng. Trong bài này, tôi muố

0 0 162