Tối ưu SQL – Jouney to the West – chap I

Bài viết được sự cho phép của tác giả Kiên Nguyễn

1. SQL – tại sao cần tối ưu

Nhận thấy rằng tối ưu SQL thực sự là một vấn đề quan trọng mà các kỹ sư phần mềm cần biết. Vì vậy, mình viết series bài viết này để chia sẻ chút kiến thức nhỏ nhoi về tối ưu SQL.

Có bất cứ thắc mắc hay phản hồi gì, các bạn hãy comment trực tiếp vào dưới bài viết, mình sẽ phản hồi sớm nhất có thể.

Xem thêm các việc làm SQL hấp dẫn trên TopDev

Khi SQL execute time quá cao, tất nhiên, việc của kỹ sử không phải là tăng thời gian setting time out

2. Một vài lưu ý cơ bản

2.1 AND

Khi sử dụng điều kiện AND, hãy nhớ rằng nếu biểu thức có dạng expression 1 AND expression 2, thì expression 2 chỉ thực hiện khi cái đầu tiên là true.

// Nếu column2 != 'B' thì DBMS sẽ không thực hiện kiểm tra tiếp 
// column1 = 'A'
WHERE column2 = 'B' AND column1 = 'A'

Hầu hết các DBMS hiện này đều thực hiện so sánh từ trái qua phải (ngoại trừ Oracle). Vì vậy ta nên viết như sau:

// So sánh column 2 trước column 1
WHERE column2 = 'B' OR column1 = 'A'

2.2 OR

Khi viết toán tử OR, hầu hết các DBMS đều thực hiện kiểm tra các điều kiện này từ trái qua phải. Mẹo nhỏ có thể áp dụng là viết column theo thứ tự trong table trước.

// So sánh column 2 trước column 1
WHERE column2 = 'B' OR column1 = 'A'

Khác với điều kiện AND, điều kiện OR sẽ thực hiện kiểm tra expression thứ hai khi expression thứ nhất false. Vì vậy, để tối ưu SQL, ta nên viết lại thành

// So sánh column 2 trước column 1
WHERE column1 = 'A' OR column2 = 'B'

2.2 NOT

Đầu tiên, chưa bàn tới việc tối ưu SQL. Với các điều kiện khác equals, viết NOT sẽ khó đọc hơn nhiều. Nếu bài toán hiệu năng không bị ảnh hưởng, nên ưu tiên bỏ viết điều kiện NOT. Ví dụ:

// Với những điều kiện đơn giản, sử dụng NOT làm giảm khả năng dễ đọc của SQL. 
WHERE NOT (column1 > 5)

Nên được viết lại thành:

// Rõ ràng dễ đọc hơn. 
WHERE column1 <= 5

Nếu điều kiện NOT quá phức tạp, có thể sử dụng định luật DeMorgan.

NOT (A AND B) = (NOT A) OR (NOT B)

NOT (A OR B) = (NOT A) AND (NOT B)

2.3 IN

Có một số thím, tuy viết SQL nhiều nhưng vẫn không thể phân biệt được sự khác biệt giữa IN và OR. Từ đó, có suy nghĩ chủ quan rằng điều kiện IN và OR không có khác biệt. Vì chúng đều trả về kết quả như nhau. Không đúng, trong một số trường hợp, sử dụng IN sẽ nhanh hơn trong một số trường hợp (IN is faster in some circumstances)

Tuy nhiên, trường hợp cột có đánh index. Hầu như không có sự khác biệt về thời gian khi sử dụng OR hoặc IN. Một thanh niên ở Stackoverflow đã thử trên Mysql (đối với 100000 records).

// Sử dụng IN cho thời gian phản hồi chỉ 1.2679 s. 
SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

Trong khi đó, sử dụng OR cho thời gian query chậm hơn. Rõ ràng rằng, tối ưu SQL là giảm thời gian truy xuất. Vì vậy, nên sử dụng IN.

// Sử dụng OR cho kết quả trong thời gian 1.7385s.
SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

Tất nhiên, trực quan mà nói. Kết quả có thể có đôi chút khác biệt giữa các DBMS khác nhau, nhưng vẫn khuyên các thím sử dụng IN khi có thể. Quá nhiều điều kiện OR có thể gây thiếu sót hoặc làm SQL trở nên khó đọc hơn.

Bản thân mình nghĩ rằng, chỉ nên sử dụng OR khi điều kiện so sánh là dưới 5.

2.4 UNION

Câu lệnh UNION giúp loại bỏ các row trùng lặp trong cả 2 bảng mà chúng ta UNION. Chà, xem ra thì nó là một cách hay để merge data, nhưng nếu bàn về performance. Liệu có gì không ổn?

// Sử dụng OR cho kết quả trong thời gian 1.7385s.
SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

2.5 EXCEPT

2.6 LIKE

2.7 CASE

Trường hợp có SQL có sử dụng function, để SQL được tối ưu, thay vì sử dụng các điều kiện WHERE gọi function nhiều lần. Ta có thể sử dụng CASE WHEN

// Gọi nhiều funciton giống nhau trong 1 câu SQL làm tốc độ giảm đi. Gây lãng phí
.. WHERE function(column1) = 3
OR function(column1) = 5

Để sử dụng được CASE WHEN ở điều kiện WHERE. Một mẹo nhỏ là sử dụng điều kiện 1=1. Điều này cũng có nghĩa rằng điều kiện WHERE chỉ lấy ra các record thỏa mãn khi giá trị CASE được đáp ứng. Cụ thể như sau:

// Thay vào đó, ta có thể sử dụng CASE WHEN.
// Việc sử dụng CASE WHEN giảm đi nhiều lần gọi function
.. WHERE 1 = 
CASE function(column1)
WHEN 3 THEN 1
WHEN 5 THEN 1
END

3. Kết luận

Rõ ràng mà nói, để tối ưu được SQL, lượng kiến thức mà chúng ta cần có là không hề nhỏ. Tất nhiên, phải hiểu thì mới optimize được chứ.

Bài viết này, tuy nội dung đơn giản, nhưng cũng là một phần kiến thức cơ bản cần nắm để có thể tối ưu được những vấn đề lớn hơn. Vì vậy, cảm ơn các bạn đã đọc bài.

Mình sẽ tiếp tục series này, cập nhật nhiều hơn nữa những nội dung chuyển sâu về tối ưu SQL. Các bạn cũng có thể đọc thêm các bài viết về Convert Oracle sang Postgres, hoặc về MyBatis

4. Tham khảo

• Addison wesley sql performance tuning – Amazon.
• Tips for SQL Database Tuning.
• SQL Performance Explained: Everything Developers Need to Know about SQL Performance.

Bài viết gốc được đăng tải tại kieblog.vn

Có thể bạn quan tâm:

• Test tải hệ thống thực sự cần thiết?
• Mybatis separator – tiện lợi và nguy hiểm
• Các kĩ sư Eureka đã tối ưu ứng dụng chat sử dụng gRPC như thế nào

Xem thêm Việc làm IT hấp dẫn trên TopDev