Làm cách nào để database đưa ra quyết định sử dụng index hay không

Vấn đề

• Đã bao giờ bạn gặp tình huống khi query dữ liệu trên database, bạn xem Execution plan (chiến lược thực thi) của câu lệnh rồi thấy database vẫn quét full bảng dù đã đánh index?

• Có một điều quan trọng là không phải lúc nào database cũng dùng index để query. Đó là bởi vì đôi khi quét full bảng còn nhanh hơn so với dùng index. Db sẽ đo cost thực hiện của câu query và so sánh với nhanh rồi chọn ra chiến lược tốn ít cost nhất.

• Vậy dựa vào đâu để db tính cost và đưa ra quyết định như vậy. Đó chính là thông số statistic. Hãy cùng tìm hiểu về statistic trong postgresql (các db khác cũng có cơ chế tương tự).

Statistic

Thông thường, theo định kì db sẽ thu thập một số thông tin thống kê về dữ liệu như là:

• Số lượng bản ghi trong mỗi bảng

• Số lượng giá trị khác nhau ở các cột

• Phân bổ giá trị ở các cột

• Một số giá trị xuất hiện nhiều nhất ở các cột

Để xem statistic trong Postgresql, ta có thể sử dụng pg_stats để xem. Một số giá trị thông dụng trong pg_stats là:

• tablename: tên bảng

• attname: tên cột

• n_distinct: số lượng giá trị khác nhau trong cột

• most_common_vals: một số giá trị xuất hiện nhiều nhất trong cột

• most_common_freqs: tần suất của một số giá trị xuất hiện nhiều nhất trong cột

• histogram_bounds: các giá trị được postgresql chọn ngẫu nhiên để chia khoảng.

Mình ví dụ xem statistic từ bảng engineer

SELECT * FROM pg_stats WHERE tablename = 'engineer'

Kết quả trả về:

Có thể thấy ở cột country_id có 240 giá trị riêng biệt, giá trị xuất hiện nhiều nhất là 26 với tuần suất 0.5 (50%), theo sau đó là giá trị 19 với tần suất 0.002 (0.2 %).

Database lựa chọn sử dụng index dựa trên statistic như thế nào

Vẫn là db ở trên, giả sử mình chọn filter dựa trên country_id.

EXPLAIN ANALYZE SELECT * FROM engineer WHERE country_id = 26;

Với giá trị 26 có tần suất là 50%, Postgresql quyết định không sử dụng index mà chọn quét full bảng với cost là 2743. Vậy nếu thử "ép" postgresql sử dụng index thì sao:

SET enable_seqscan = off;  -- Disable sequential scans;  
EXPLAIN ANALYZE SELECT * FROM engineer WHERE country_id = 26;

Có thể thấy rằng khi dùng index, cost rơi vào khoảng 2767.07, lớn hơn so với khi quét full bảng là 2743 (dù thời gian thực tế có thể nhỏ hơn)

Như vậy database dựa vào statistic để tính ra cost từ đó đưa ra quyết định liệu có nên dùng index hay không.

Lưu ý

• Statistic thông thường hay được tự động update định kì.

• Thường khi khi giá trị tần suất dữ liệu dưới 30% thì db mới lựa chọn sử dụng index.

• Việc cập nhật statistic có thể ảnh hưởng đến performance của database. Vì khi cập nhật statistic db phải chịu thêm tải. Do đó hãy thực hiện update statistic khi db đang ở tải thấp.

• Việc dữ liệu db hay thay đổi có thể làm "cũ" statistic. Vì thế khi kiểm tra và tối ưu câu lệnh, hãy xem xét việc update statistic. Đối với postgresql, có thể thực hiện update statistic bằng câu lệnh ANALYZE table;