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

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

Phần này tôi sẽ nói về các điều kiện ngu ảnh hưởng tới các index kết hợp

Phần đầu tiên sẽ về kiểu date và time, giả sử bạn có một cột lưu date, một cột lưu time. Mà bạn muốn kết hợp chúng để quy thi theo range như sau

SELECT ...
  FROM ...
 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)

Câu lệnh trên để select những bản ghi có thời gian từ 24 giờ trở lại đây. Câu truy vấn không thể sử dụng index trên hai trường (DATE_COLUMN, TIME_COLUMN) bởi vì không truy vấn trực tiếp trên hai trường đó, mà dựa trên hàm.

Bạn có thể tránh chúng bằng việc thêm một column nữa có dạng datetime, và select trên column này mà không cần gọi funtion như sau

SELECT ...
  FROM ...
 WHERE datetime_column
     > DATE_ADD(now(), INTERVAL -1 DAY)

Nhưng thực tế thêm một cột không phải việc có thể thực hiện ngay trên môi trường chính thức.

Một cách khác là sử dụng Function-Base index. Tuy nhiên cũng cần nhớ là cách này không dùng được Mysql bản 5.

Có một cách khác để truy vấn mà vẫn đùng được index kết hợp trên hai cột này DATE_COLUMN, TIME_COLUMN, ít nhất là một phần bằng cách sửa câu lệnh như sau

 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)
   AND date_column
    >= DATE(DATE_ADD(now(), INTERVAL -1 DAY))

Chúng ta để ý câu lệnh trên, so với lệnh ban đầu chúng ta thêm một điều kiện so sánh với date_column, điều này rõ ràng là thêm điều kiện vào câu truy vấn tại sao lại nhanh hơn? Đáng ra thêm điều kiện thì phải so sánh nhiều hơn chậm hơn mới phải chứ? . Rõ ràng thấy điều kiện thứ hai là dư thừa, tuy nhiên nó có thể sử dụng index. Mặc dù nó không hoàn hảo nhưng về hiệu năng rõ ràng là có tốt hơn.

TIP: Có thể sử dụng điều kiện dư thừa trên cột có ý nghĩa nhất trong điều kiện sử dụng range condition kết hợp nhiều điều kiện. Nhắc lại một chút thông thường database truy vấn sẽ có các điều kiện Equal là bằng và chỉ 1, Range là trên một khoảng, Full là toàn bộ.

Một cách khác ít dùng hơn là có thể lưu date và time theo kiểu string, nhưng để so sánh được chính xác thì phải lưu đúng theo kiểu sao cho thứ tự theo thời gian và thứ tự theo string sẽ có kết quả giống nhau ví dụ ISO 8601 (YYYY-MM-DD HH:MM:SS). Như ví dụ dưới đấy sử dụng kiểu TO_CHAR cho Oracle

SELECT ...
  FROM ...
 WHERE date_string || time_string
     > TO_CHAR(sysdate - 1, 'YYYY-MM-DD HH24:MI:SS')
   AND date_string
    >= TO_CHAR(sysdate - 1, 'YYYY-MM-DD')

Nói chung cách này cũng không hay, không nên sử dụng vì như bài trước có nói kiểu nào dùng kiểu đó sẽ hay hơn mà tránh gây hiểu lầm.

Đôi khi chúng ta lại muốn sử dụng Obfuscated Conditions (lần này nó không ngu nên mình để nguyên) để không muốn sử dụng điều kiện đó như một access predicate.

Access predicates: là điều kiện bắt đầu hoặc kết thúc việc tìm kiếm theo index. Nó xác định phạm vi index được quét.

Tại sao lại như vậy? Xem xét câu lệnh sau đây

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name LIKE ?

Như ở bài trước có nói về việc sử dụng query với tham số với like có thể mang lại hiệu năng không tốt. Vì khi query theo tham số execution plan được cache lại, nên các lệnh sau sẽ tiếp tục sử dụng phương án của câu lệnh trước mà không quan tâm tới độ phân tán dữ liệu. Ví dụ nếu như câu lệnh đầu tiên like với pattern sau "abc%" thì sẽ sử dụng được index trên cột last_name. Tuy nhiên câu sau lại like theo điều kiện "%abc" thì sẽ không sử dụng được index trên cột last_name. Vì vậy với toán tử LIKE mà điều kiện chúng ta mỗi lúc pattern một kiểu thì không nên truyền tham số. Để trình optimize phải đoán xem có dùng index hay không. Nếu câu lệnh like luôn có % ở đầu có thể dùng lệnh sau để optimize không cần đoán và cân nhắc việc dùng index trên last_name nữa.

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name || '' LIKE ?

Chỉ cần ghép thêm chuỗi empty vào last_name là đủ để câu truy vấn không sử dụng index trên last_name nữa. Tuy nhiên việc này cũng cần cân nhắc, chỉ nên dùng khi biết chắc chắn nó thực sự cần thiết.

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