Tăng tốc database index phần 16. - Điều kiện ngu (Obfuscated Conditions) với Dates

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

Điều kiện ngu là gì? xin phép mình dịch mạnh mẽ thế cho dễ hiểu, đại loại nó là những điều kiện trong WHERE làm index không hiệu quả. Phần này sẽ trình bày những anti-patterns mà hay gặp phải khi truy vấn. Nôi dung như sau

1. Dates — Nội dung bài này
2. Numeric Strings — Don’t mix types
3. Combining Columns — use redundant where clauses
4. Smart Logic — The smartest way to make SQL slow
5. Math — Databases don’t solve equations

Ok phần này sẽ trình bày phần Date trước. Kiểu DATE bản thân nó hay bị nhầm với DATETIME, nhất là với Oracle DATE luôn bao gồm TIME. Để so sánh với các điều kiện với DATETIME mà chỉ cần so sánh ngày thông thường sẽ làm như sau: Dùng một hàm để chặt Time đi, ví dụ hàm TRUNC trong Oracle, thực ra hàm này không hề chặt Time đi mà nó đơn giản reset Time về nửa đêm, vì Oracle không có kiểu DATE chuẩn chỉ. Để truy vấn dữ liệu bỏ qua Time cần sử dụng hàm ở cả hai bên dấu "=" Ví dụ tìm kiếm những bản ghi trong bảng sales ngày hôm qua

SELECT ... FROM sales WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

Câu lệnh trên chạy chuẩn, không có lỗi gì, tuy nhiên nó bị chậm vì không ăn index trên cột SALE_DATE. Đã được giải thích trong bài Tăng tốc database index phần 8 - Function- Tìm kiếm không phân biệt chữ Hoa chữ Thường- UPPER và LOWER. Truy vấn trên hàm TRUNC(sale_date) không thể sử dụng index trên cột SALE_DATE.

Có một cách để giải quyết vấn đề này là thêm index cho hàm TRUNC

CREATE INDEX index_name ON sales (TRUNC(sale_date))

Nhưng với index này cần luôn sử dụng TRUNC(sale_date) trong điều kiện WHERE. Nếu bạn thỉnh thoảng dùng TRUNC thỉnh thoảng dùng hàm khác thì bạn cần thêm một index khác. Cần tới 2 index.

Vấn đề xảy ra khi bạn muốn truy vấn chỉ với ngày (PURE DATE) trong một khoảng thời gian dài hơn ví dụ câu dưới đây:

SELECT ... FROM sales WHERE DATE_FORMAT(sale_date, "%Y-%M") = DATE_FORMAT(now() , "%Y-%M")

Câu lệnh trên truy vấn theo string format của Year và Month. Nó vẫn có thể được xử lý bằng index hàm DATE_FORMAT, tuy nhiên với MYSQL trước bản 5.7 thì không hỗ trợ index trên hàm. Với cách này thì mỗi khi có một chức năng nào đó lại phải thêm index cho function dù đã có index trên sale_date (chả nhẽ dành cả thanh xuân để thêm index).

Một cách thay thế khác là sử dụng câu lệnh với điều kiện chính xác với (>,< ) so sánh cả time luôn cho nó chất và không dùng hàm ở bên trái dấu "=". Với ngày thì thay vì dùng hàm DATE(), hay TRUNC nên dùng hàm so sánh như BETWEEN hoặc >,< 0h và 24h, như vậy sẽ sử dụng được index và nhanh hơn.

Trong một số trường hợp tính ngày khó hơn thì như thế nào? Trong trường hợp cần lấy dữ liệu trong một quý. Lúc này câu truy vấn như sau:

SELECT ... FROM sales WHERE sale_date BETWEEN quarter_begin(?) AND quarter_end(?)

Lúc này hàm quarter_begin và quarter_end đơn giản trả về thời điểm bắt đầu và kết thúc một quý, bên trái dấu "=" là sale_date không nằm trong function nào nên vẫn index bình thường. Để tính được hàm này hơi phức tạp một chút vì quarter_end cần trả về thời điểm ngay trước khi chuyển sang quý sau. Dưới đây là một số cách với các DB hay dùng.

MYSQL

CREATE FUNCTION quarter_begin(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT ( CONCAT ( CONVERT(YEAR(dt),CHAR(4)) , '-' , CONVERT(QUARTER(dt)*3-2,CHAR(2)) , '-01' ) , datetime )
CREATE FUNCTION quarter_end(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH ) , INTERVAL -1 MICROSECOND)

ORACLE

CREATE FUNCTION quarter_begin(dt IN DATE) RETURN DATE
AS
BEGIN RETURN TRUNC(dt, 'Q');
END
CREATE FUNCTION quarter_end(dt IN DATE) RETURN DATE
AS
BEGIN -- the Oracle DATE type has seconds resolution -- subtract one second from the first  -- day of the following quarter RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') - (1/(24*60*60));
END

PostgreeSQL

RETURNS timestamp with time zone AS $$
BEGIN RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql
CREATE FUNCTION quarter_end(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN RETURN date_trunc('quarter', dt) + interval '3 month' - interval '1 microsecond';
END;
$$ LANGUAGE plpgsql 

SQL Server

CREATE FUNCTION quarter_begin (@dt DATETIME )
RETURNS DATETIME
BEGIN RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0) END
CREATE FUNCTION quarter_end (@dt DATETIME )
RETURNS DATETIME
BEGIN RETURN DATEADD ( ms , -3 , DATEADD(mm, 3, dbo.quarter_begin(@dt)) );
END

Ngoài ra chúng ta vẫn có thể dùng hàm, cho những khoảng thời gian khác nếu nó ở bên phải của dấu "=". Ví dụ trường hợp trong ví dụ đầu tiên có thể viết lại là

 sale_date >= TRUNC(sysdate)
AND sale_date < TRUNC(sysdate + INTERVAL '1' DAY)

Chú ý ở đây dùng < với ngày hôm sau, khác với BETWEEN luôn lấy giá trị biên nên phải lấy thời gian lớn nhất của ngày hôm trước.

Một trường hợp điều kiện ngu khác mà hay gặp là so sánh DATE với String như câu lệnh Postgree như sau

SELECT ... FROM sales WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'

Vấn đề lặp lại, ví dụ trên dùng TO_CHAR để convert SALE_DATE, vì dùng hàm nên không ăn index, trong trường hợp này nên sửa câu lệnh như sau

SELECT ... FROM sales WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')

Chỉ cần đổi hàm từ bên trái sang bên phải là hiệu năng đã thay đổi nhiều, bạn có thể thử để thấy kết quả. Nguyên nhân là

1. Sale_date được sử dụng index
2. Hàm TODATE chỉ cần chạy một lần, còn hàm TOCHAR thì phải chạy trên tất cả các bản ghi của bảng SALES để convert rồi so sánh giá trị

Tuy nhiên cách trên vẫn có thể có lỗi, nếu vô tình trong dữ liệu SALE_DATE lại có một time khác với giờ đầu ngày 00:00 , thì bản ghi đó sẽ không được lấy ra. Vì vậy để chắc ăn cần chạy lệnh như sau

SELECT ... FROM sales WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD') AND sale_date < TO_DATE('1970-01-01', 'YYYY-MM-DD') + INTERVAL '1' DAY

Chốt lại là bạn luôn luôn dùng lệnh so sánh rõ ràng (BETWEEN,>,<,<=,<=) với DATE, DATE_TIME để đạt hiệu quả và kết quả tốt nhất.

LIKE trong date

Đôi khi có truy vấn như sau có điều kiện như sau " sale_date LIKE SYSDATE" với điều kiện trên rõ ràng có thể hiểu là ăn index vì không có hàm nào ở chỗ SALE_DATE cả. Tuy nhiên ngầm định bên trong toán tử LIKE sẽ bắt buộc là so sánh string, tùy thuộc vào DBMS của các bạn mà có thể trả về lỗi hoặc convert ngầm định. Predicate Information của Oracle trong execution plan cho kết quả như sau

filter( INTERNAL_FUNCTION(SALE_DATE) LIKE TO_CHAR(SYSDATE@!))

INTERNAL_FUNCTION sẽ convert SALE_DATE nên index trên SALE_DATE cũng không ăn được .

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é! Ngoài ra mình nhận tư vấn, đào tạo, hỗ trợ các vấn đề khó hoặc hiệu năng cho các sản phẩm, bạn nào có nhu cầu có thể liên hệ mình nhé! email nghiand1010@gmail.com