Tối ưu hóa truy vấn dữ liệu trên SQL là quan trọng để đảm bảo hiệu suất cao của hệ thống cơ sở dữ liệu. Dưới đây là một số cách bạn có thể sử dụng để tối ưu hóa truy vấn SQL
1: Sử dụng Tên Cột thay vì * trong lệnh SELECT
Nếu chỉ cần dùng một số ít cột từ bảng, không cần sử dụng lệnh SELECT *. Dù cách viết này dễ, nhưng lại gây tốn nhiều thời gian hơn để hoàn thành truy vấn. Bằng cách chỉ chọn những cột cần thiết, bạn đang giảm bớt kích cỡ của bảng kết quả, giảm lưu lượng mạng, và làm tăng hiệu năng của truy vấn.
Ví dụ:
| ☒Truy vấn gốc:SELECT * FROM SH.Sales; | ☑Truy vấn đã được cải thiện:SELECT s.prod_id FROM SH.sales s; |
2: Tránh sử dụng mệnh đề HAVING
Mệnh đề HAVING có tác dụng lọc sau khi các hàng đã được chọn và được sử dụng như một filter. Nếu bạn có thể sử dụng mệnh đề WHERE để thực hiện việc lọc trước khi thực hiện GROUP BY, điều này có thể mang lại hiệu suất tốt hơn vì nó có thể giảm lượng dữ liệu cần xử lý.
Ví dụ:
| ☒Truy vấn gốc:SELECT s.cust_id,count(s.cust_id)FROM SH.sales sGROUP BY s.cust_idHAVING s.cust_id != ‘1660’ AND s.cust_id != ‘2’; | ☑Truy vấn đã được cải thiện:SELECT s.cust_id,count(cust_id)FROM SH.sales sWHERE s.cust_id != ‘1660’AND s.cust_id !=’2′GROUP BY s.cust_id; |
3: Luôn đặt JOIN từ các bảng lớn nhất đến các bảng nhỏ nhất
Ví dụ:
| ☒Truy vấn gốc:SELECT *FROM small_tableJOIN large_tableON small_table.id = large_table.id | ☑Truy vấn đã được cải thiện:SELECT *FROM large_tableJOIN small_tableON small_table.id = large_table.id |
Thêm một lưu ý nữa: Thường xuyên sử dụng INNER JOIN thay vì subquery để nối các bảng. INNER JOIN thường có hiệu suất tốt hơn.
4: Un-nest các truy vấn nội bộ (Subquery)
Khi nói đến “un-nest” trong ngữ cảnh của truy vấn SQL, có thể đang thảo luận về việc giải quyết hoặc chia nhỏ các truy vấn nội bộ để tối ưu hóa hiệu suất và làm cho code trở nên dễ đọc hơn. Dưới đây là một số lý do tại sao có thể cân nhắc un-nest các truy vấn nội bộ:
- Hiệu suất:
- Nếu có nhiều truy vấn nội bộ, có thể dẫn đến việc thực hiện nhiều truy cập cơ sở dữ liệu không cần thiết, làm giảm hiệu suất.
- Bằng cách un-nest các truy vấn nội bộ, bạn có thể giảm số lượng truy cập cơ sở dữ liệu và cải thiện hiệu suất.
- Đọc mã SQL dễ hiểu hơn:
- Các truy vấn nội bộ có thể làm cho mã SQL trở nên phức tạp và khó đọc, đặc biệt là khi có nhiều lớp truy vấn nội bộ.
- Un-nest có thể giúp mã SQL trở nên rõ ràng và dễ đọc hơn bằng cách diễn đạt mỗi yếu tố truy vấn một cách rõ ràng.
- Quản lý lỗi dễ dàng hơn:
- Khi có lỗi xảy ra trong truy vấn, việc un-nest có thể giúp xác định nguồn gốc của lỗi một cách dễ dàng hơn, vì bạn chỉ cần kiểm tra từng phần của truy vấn chính mà không cần theo dõi nhiều cấp truy vấn nội bộ.
Thêm một lưu ý nữa: Tránh dùng subquery trong mệnh đề WHERE
Ví dụ:
| ☒Truy vấn gốc:SELECT *FROM SH.products pWHERE p.prod_id = (SELECT s.prod_id FROM SH.sales s WHERE s.cust_id = 100996 AND s.quantity_sold = 1); | ☑Truy vấn đã được cải thiện:SELECT p.*FROM SH.products p, sales sWHERE p.prod_id = s.prod_id AND s.cust_id = 100996 AND s.quantity_sold = 1; |
5: Luôn GROUP BY theo cột có số lượng giá trị duy nhất lớn nhất trước, sau đó giảm dần.
Ví dụ:
| ☒Truy vấn gốc:SELECT main_category,sub_category,itemid,sum(price)FROM table1GROUP BY main_category, sub_category, itemid | ☑Truy vấn đã được cải thiện:SELECT main_category,sub_category,itemid,sum(price)FROM table1GROUP BY itemid, sub_category, main_category |
6: Loại bỏ các mệnh đề DISTINCT không cần thiết
Khi xem xét ví dụ dưới đây, từ khóa DISTINCT trong truy vấn gốc là không cần thiết vì tên bảng đã chứa p.ID là khóa chính của bảng – đây là một phần của kết quả. Việc đưa mệnh dề DISTINCT sẽ khiến CSDL thực hiện thêm một phép so sánh để loại bỏ các dòng trùng nhau trong tập kết quả.
Ví dụ:
☒Truy vấn gốc:SELECT DISTINCT * FROM SH.sales sJOIN SH.customers cON s.cust_id= c.cust_idWHERE c.cust_marital_status = ‘single’; | ☑Truy vấn đã được cải thiện:SELECT * FROM SH.sales s JOIN SH.customers cON s.cust_id = c.cust_idWHERE c.cust_marital_status= ‘single’; |
7: Sử dụng EXISTS thay cho DISTINCT khi kết nối các bảng có một hoặc nhiều liên kết.
DISTINCT có tác dụng chọn tất cả cột trong bảng, phân tích và lọc ra các cột bị trùng lặp. Thay vào đó, nếu bạn sử dụng sub-query với từ khóa EXISTS, bạn có thể tránh việc phải trả lại toàn bộ một bảng.
Ví dụ:
☒Truy vấn gốc:
| SELECT DISTINCT c.country_id, c.country_nameFROM SH.countries c,SH.customers eWHERE e.country_id = c.country_id; |
☑Truy vấn đã được cải thiện:
| SELECT c.country_id, c.country_nameFROM SH.countries cWHERE EXISTS (SELECT ‘X’ FROM SH.customers e WHERE e.country_id = c.country_id); |
8: Sử dụng UNION ALL thay cho UNION
Mệnh đề UNION ALL nhanh hơn UNION bỏi vì mệnh đề UNION ALL không tính tới các điểm trùng lập và mệnh đề UNION lại tìm các điểm trùng lặp trong bảng khi chọn hàng, dù có điểm trùng lặp hay không hãy ưu tiên sử dụng UNION ALL khi chúng ta biết chắc chắn mỗi dòng trong kết quả sẽ là duy nhất hoặc có thể chấp nhận việc trùng lặp.
Ví dụ:
| ☒Truy vấn gốc:SELECT cust_idFROM SH.salesUNIONSELECT cust_idFROM customers; | ☑Truy vấn đã được cải thiện:SELECT cust_idFROM SH.salesUNION ALLSELECT cust_idFROM customers; |
Thêm một lưu ý nữa: Tránh sử dụng các loại UNION, dùng càng ít càng tốt.
9: Tránh sử dụng OR trong các mệnh đề thỏa mãn nhiều điều kiện
Mỗi khi bạn đặt ‘OR’ trong điều kiện, truy vấn sẽ chậm đi ít nhất một hoặc hai thừa số
Ví dụ:
☒Truy vấn gốc:
| SELECT *FROM SH.costs cINNER JOIN SH.products p ON c.unit_price = p.min_price OR c.unit_price = p.list_price; |
☑Truy vấn đã được cải thiện:
| SELECT *FROM SH.costs cINNER JOIN SH.products p ON c.unit_price = p.min_priceUNION ALLSELECT *FROM SH.costs cINNER JOIN SH.products p ON c.unit_price = p.list_price; |
10: Bỏ các hàm tổng hợp
Viết lại truy vấn bằng cách bỏ các hàm tổng hợp sẽ tăng hiệu suất đáng kế, đặc biệt với các cột đã được đánh index.
Ví dụ:
☒Truy vấn gốc:
| SELECT * FROM SH.salesWHERE EXTRACT(YEAR FROM TO_DATE(time_id, ‘DDMON-RR’)) = 2001 AND EXTRACT(MONTH FROM TO_DATE(time_id, ‘DDMON-RR’)) = 12; |
☑Truy vấn đã được cải thiện:
| SELECT * FROM SH.salesWHERE TRUNC (time_id) BETWEEN TRUNC(TO_DATE(‘12/01/2001’, ’mm/dd/yyyy’)) AND TRUNC (TO_DATE (‘12/30/2001’, ’mm/dd/yyyy’)); |
11: Loại bỏ các phép toán thừa
Các phép toán có thể làm giảm hiệu suất đáng kể nếu cách viết không phù hợp. Mỗi lần truy vấn tìm thấy một hàng, nó sẽ thực hiện lại phép toán. Vì vậy, việc loại bỏ phép toán không cần thiết sẽ giúp truy vấn chạy nhanh hơn rất nhiều.
Ví dụ:
| ☒Truy vấn gốc:SELECT *FROM SH.sales sWHERE s.cust_id +1000 < 35000; | ☑Truy vấn đã được cải thiện:SELECT * FROM SH.sales sWHERE s.cust_id < 25000; |
12: Dùng MAX thay vì RANK
Ví dụ:
| ☒Truy vấn gốc:SELECT *FROM ( SELECT userid, RANK() OVER (ORDER BY prdate DESC) as rank FROM table1)WHERE ranking = 1 | ☑Truy vấn đã được cải thiện:SELECT userid, max(prdate) FROM table1ORDER BY 1 |
13: Dùng ‘regexp_like’ thay cho ‘LIKE’
Ví dụ:
| ☒Truy vấn gốc:SELECT *FROM table1WHERElower(item_name) LIKE ‘%samsung%’ ORlower(item_name) LIKE ‘%xiaomi%’ ORlower(item_name) LIKE ‘%iphone%’ ORlower(item_name) LIKE ‘%huawei%’ | ☑Truy vấn đã được cải thiện:SELECT *FROM table1WHEREREGEXP_LIKE(lower(item_name),’samsung|xiaomi|iphone|huawei’) |
14: Dùng ‘regexp_extract’ thay cho ‘CASE-WHEN LIKE’
Ví dụ:
☒Truy vấn gốc:
SELECT
CASE
WHEN concat(‘ ‘,item_name,’ ‘) LIKE ‘%acer%’ THEN ‘Acer’
WHEN concat(‘ ‘,item_name,’ ‘) LIKE ‘%advance%’ THEN ‘Advance’
WHEN concat(‘ ‘,item_name,’ ‘) LIKE ‘%alfal%’ THEN ‘Alfal’
…
AS brand
FROM item_list;
☑Truy vấn đã được cải thiện:
SELECT
regexp_extract(item_name,'(asus|lenovo|hp|acer|dell|…)’)
AS brandFROM item_list;
– Xem ngay Lộ trình học Data Analyst chuyển ngành thành công trong vòng 6 tháng
– Xem ngay Lộ trình học Marketing Automation & Analytics Coaching 1 on 1 để upgrade kỹ năng phân tích cho Marketer
– Tham gia Vietnam Data Analyst Forum – #1 Informative Group để học hỏi và chia sẻ kiến thức về Data Analytics
– Cập nhật lịch khai giảng, chương trình ưu đãi và nhận tư vấn chuyển ngành miễn phí tại Data Coaching 1 on 1 – UniGap
