Tăng tốc database index phần 16.2 - Điều kiện ngu (Obfuscated Conditions) với Numeric Strings

Các bạn có thể xem toàn bộ series ở đây

Trong phần này tôi sẽ giới thiệu về nguy hại của việc lưu số bằng kiểu string.

Numeric strings là dữ liệu kiểu số nhưng lại lưu trọng cột kiểu string, mặc dù cách này là một bad practice, nhưng nếu luôn coi nó là string thì nó vẫn có thể sử dụng index.

SELECT ...
  FROM ...
 WHERE numeric_string = '42'

Với câu lệnh như trên, cả hai là string nên index có thể sử dụng được trên cột NUMERIC_STRING. Còn nếu bạn không muốn so sánh với chuỗi '42' mà muốn so sánh với số như lệnh sau:

SELECT ...
  FROM ...
 WHERE numeric_string = 42

với câu lệnh này, thiếu dấu nháy đơn ( '), strong một số hệ quản trị cơ sở dữ liệu (PostgreSQL) sẽ báo lỗi, tuy nhiên một số hệ quản trị dữ liệu khác sẽ thực hiện chuyển đổi kiểu một cách ngầm định như sau:

SELECT ...
  FROM ...
 WHERE TO_NUMBER(numeric_string) = 42

Với chuyển đổi này, rõ ràng thấy hàm xuất hiện bên trái dấu (=), index sẽ không hoạt động ở đây được. Giải pháp của việc này là đẩy hàm sang bên phải dấu bằng (=).

SELECT ...
  FROM ...
 WHERE numeric_string = TO_CHAR(42)

Nhưng tại sao Database không convert theo cách này mà lại dùng cách trước? Nguyên nhân là bởi vì convert từ string sang number luôn có một kết quả rõ ràng, còn ngược lại thì chưa chắc. Một số khi được convert về chuỗi có thể chứa dấu cách, có số 0 ở trước, một giá trị số có thể được convert theo nhiều cách.

Bởi vì database không biết format nào được sử dụng trong cột NUMERIC_STRING , nên database convert từ string sang number, cách này có kết quả rõ ràng hơn. Hàm TO_CHAR chỉ trả về một chuỗi ('42') trong câu lệnh trên nên trong trường hợp có nhiều giá trị như trên, chỉ giá trị đầu tiên (42) được tính là đúng, nếu dùng TO_NUMBER, thì giá trị nào cũng đúng. Với hai cách TO_CHAR và TO_NUMBER không những có sai biệt về hiệu năng, mà còn có khác biệt về ngữ nghĩa.

Việc dùng string để lưu number không những ảnh hưởng tới hiệu năng, mà nó còn có thể gây lỗi, bởi vì lỗi gì đó mà nó không thể convert ngược lại sang number có thể là dấu phân cách, dấu trừ v..v. Bởi vậy bạn luôn luôn nên dùng kiểu number để lưu số.

Trong trường hợp bạn lưu số vào cột number mà muốn so sánh số với chuỗi thì sao. Ví dụ câu lệnh dưới dây

SELECT ...
  FROM ...
 WHERE numeric_number = '42'

Trong trường hợp này database sẽ convert string sang number , vì vậy sẽ không ảnh hưởng tới việc sử dụng index trên cột 'numeric_number' hiệu năng vẫn đảm bảo. Tuy nhiên nếu bạn convert bằng tay theo cách này

SELECT ...
  FROM ...
 WHERE TO_CHAR(numeric_number) = '42'

Thì index sẽ không ăn, và câu lệnh của bạn sẽ chậm đấy.

Chú ý: Có thể bạn không convert câu lệnh như vậy bằng tay, nhưng có thể lệnh của bạn được sinh ra từ framework, ORM hoặc bạn nhét hàm để so sánh trên code rồi tự động sinh ra câu SQL có dạng như trên, sẽ gây ảnh hưởng hiệu năng rất lớn. Vì vậy cần chú ý khi sử dụng hàm trên ORM để tránh trường hợp convert sang câu lệnh chứa điều kiện ngu nhé!

Mình có lập group Standard Dev các bạn có thể Join để xây dựng một cộng đồng lập trình viên level quốc tế nhé!

Database index

Mục lục