10 lỗi phổ biến của Developers khi viết câu lệnh SQL

Dưới đây là những lỗi phổ biến thường gặp trong quá trình chúng ta viết câu lệnh SQL.

Thực tế thì lỗi này vẫn còn xảy ra khá phổ biến trong các bài viết, trang diễn đàn chia sẻ sau nhiều năm xuất hiện của JDBC, ngay cả khi nó là 1 điều đơn giản để nhớ và hiểu. Có vẻ như 1 số nhà phát triển không sử dụng PreparedStatements vì 1 số lý do dưới đây:

• Họ không hiểu về PreparedStatements
• Họ nghĩ rằng sủ dụng PreparedStatements sẽ chậm hơn.
• Sử dụng PreparedStatements sẽ tốn nhiều effort hơn.

Đầu tiên, chúng ta cần phá bỏ những suy nghĩ trên. Bởi vì theo ước tính thì trên 96% các trường hợp, sử dụng PreparedStatements tốt hơn static statment. Lý do là:

• Bạn có thể loại bỏ đợc lỗi cú pháp khi nối chuỗi rằng buộc các giá trị.
• Bạn có thể loại bỏ được các câu lệnh SQL injection từ việc nối chuối các ràng buộc giá trị.
• Bạn có thể tránh được các trường hợp thêm nhiều loại dữ liệu tinh vi như TIMESTAMP, dữ liệu nhi phân...
• Bạn có thể tiếp tục mở PreparedStatements trong một khoảng thời gian, sử dụng chúng với các giá trị ràng buộc mới thay vì đóng chúng ngay lập tức (ví dụ như trong Postgres).
• Bạn có thể sử dụng chia sẻ con trỏ thích ứng (Oracle-speak) trong các cơ sở dữ liệu phức tạp hơn. Điều này giúp ngăn chặn các câu lệnh SQL phân tích cú pháp cho mỗi tập hợp các giá trị ràng buộc mới.

Tuy nhiên trong 1 số trường hợp nhất định, bạn vẫn có thể sử dụng inline bind values (rằng buộc nội tuyến) khi viết các rằng buộc điều kiện cho câu lệnh SQL, với các trường giá trị được quy định là hàng số. Ví dụ:

• ACTIVE = true
• DELETED = 1

Vậy giải pháp đưa ra là:

• Mặc định, luôn luôn nghĩ đến việc sử dụng câu lệnh PreparedStatements thay thế cho câu lệnh static statement. Và đặc biệt không sử dụng cộng chuỗi điều kiện các variable để tránh SQL injection.

Lỗi này là khá thường xuyên và có thể dẫn đến giảm hiệu suất trong quá trình truy vấn cơ sở dữ liệu ứng dụng của bạn . Hãy nhìn vào 2 hiệu ứng làm giảm effect của ứng dụng khi select all quá nhiều cột.

Tác động xấu đến ứng dụng sử lý phía backend

• Khi bạn SELECT * với bảng 50 coloumns, thì bạn sẽ sử dụng lại giữa các DAOs, bạn cần transfer nhiều dữ liệu từ database đến JDBC ResultSet. Ngay cả khi bạn không đọc dữ liệu từ ResultSet, nó đã được truyền qua đường dây và được nạp vào bộ nhớ của bạn bằng trình điều khiển JDBC. Đó là một sự lãng phí IO và bộ nhớ nếu bạn chỉ cần 2-3 trong số những cột đó.

Tác động xấu đến thực thi câu lệnh trong database.

• Khi thao tác truy vấn xảy ra với 1 lượng lớn dữ liệu, cùng với các câu lệnh JOIN , điều kiện rất phức tạp, thì việc lấy về nhiều dữ liệu cột không dùng đến thì việc thực thi câu lệnh sẽ tốn nhiều effort hơn.

Giải pháp:

• Chỉ sử dụng SELECT * khi cần thiết.
• Tuy nhiên điều này thực sự là khá khó để đạt được khi sử dụng ORMs

• Đây không phải là sai lầm với rất nhiều ảnh hưởng đến hiệu suất hoặc tính chính xác của SQL. Tuy nhiên, các nhà phát triển SQL cần phải nhận thức được rằng mệnh đề JOIN không phải là một phần của câu lệnh SELECT
• Tiêu chuẩn SQL 1992 xác định tham chiếu bảng như sau:

6.3 <table reference>

<table reference> ::=
    <table name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
  | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
  | <joined table>

• Mệnh đề FROM và bảng join cũng có thể sử dụng các tham chiếu bảng như sau:

7.4 <from clause>

<from clause> ::= 
    FROM <table reference> [ { <comma> <table reference> }... ]

7.5 <joined table>

<joined table> ::=
    <cross join>
  | <qualified join>
  | <left paren> <joined table> <right paren>

<cross join> ::=
    <table reference> CROSS JOIN <table reference>

<qualified join> ::=
    <table reference> [ NATURAL ] [ <join type> ] JOIN
      <table reference> [ <join specification> ]

Giải pháp:

• Nếu bạn viết một mệnh đề JOIN, hãy nghĩ đến mệnh đề JOIN này là một phần của một tham chiếu bảng theo syntax chuẩn:

SELECT c.first_name, c.last_name, o.amount
FROM customer_view c
JOIN order_view o
ON c.cust_id = o.cust_id

• Việc này làm giảm hiệu suất, làm cho câu lệnh JOIN không tường minh.
• Không nên sử dụng:

SELECT c.first_name, c.last_name, o.amount
FROM customer_view c, order_view o
WHERE  c.cust_id = o.cust_id AND c.first_name = 'AAA';

Mà hãy sử dụng chuẩn syntax Join:

SELECT c.first_name, c.last_name, o.amount
FROM customer_view c  JOIN order_view o 
ON  c.cust_id = o.cust_id 
WHERE c.first_name = 'AAA';

• Theo Tiêu chuẩn SQL năm 1992 xác định câu lệnh LIKE như sau:

8.5 <like predicate>

<like predicate> ::=
    <match value> [ NOT ] LIKE <pattern>
      [ ESCAPE <escape character> ]

• Từ khóa ESCAPE nên được sử dụng luôn luôn khi cho phép người dùng nhập vào được sử dụng trong các truy vấn SQL của bạn:

SELECT *
FROM   t
WHERE  t.x LIKE 'some!_prefix%' ESCAPE '!'

• Điều này là tinh tế nhưng rất quan trọng đối với NULLs! Hãy xem xét A IN (X, Y) thực sự có ý nghĩa gì:

 A IN (X, Y)
tương đương với    A = ANY (X, Y)
tương đương với    A = X OR A = Y

• Khi NOT (A IN (X, Y)) thì nó có ý nghĩa là:

NOT (A IN (X, Y))
tương tự với   A NOT IN (X, Y)
tương tự với    A != ANY (X, Y)
tương tự với    A != X AND A != Y

• Điều đó trông giống như sự đảo ngược của các vị từ trước, nhưng nó thực sự không phải. Nếu bất kỳ của X hoặc Y là NULL, thì NOT IN sẽ cho kết quả UNKNOWN trong khi vị từ IN vẫn có thể trả về một giá trị boolean.

• Bạn có thể optimize tốc độ của chương trình hơn bằng cách kết hợp các điều kiện lọc từ khi truy vấn dữ liệu. Thay vì việc sử dụng vòng lặp để lọc dữ liệu sau khi dữ liệu đã được lấy ra từ database.

• Biểu thức giá trị hàng là một tính năng SQL tuyệt vời. Vì vậy sử dụng biểu thức giá trị hàng bất cứ khi nào bạn có thể. Nó sẽ làm cho SQL của bạn súc tích hơn và thậm chí nhanh hơn.

• Một vài developers nghĩ là các rằng buộc khiến cho việc truy vấn và cập nhật dữ liệu của chúng ta chậm hơn. Nhưng nó đảm bảo cho dữ liệu đồng nhất và không bị mất dữ liệu. Trong trường hợp bạn phải thao tác với nhiều records dữ liệu cùng lúc, bạn có thể nghĩ đến việc sử dụng các bảng tạm để chuyển tiếp các dữ liệu cần thiết.

• Một số tập đoàn lớn (như: Twitter or Facebook) thay vì sử dụng SQL truyền thống mà chuyển sang sử dụng NoSQL để thao tác với 1 lượng dữ liệu khồng lồ.