Thu thập thống kê sử dụng index và bảng

Hãy tưởng tượng database của bạn giống như một kho hàng bự chảng. Index là mấy cái catalog và danh sách giúp tìm đồ nhanh hơn. Còn bảng thì chính là mấy món hàng nằm trên kệ. Nếu index mà ít được dùng thì cũng như cái catalog bị vứt xó, chẳng ai thèm mở. Nếu bảng được dùng nhiều mà cấu trúc dở hoặc dữ liệu dư thừa, thì kho (database) của mình sẽ bị ì ạch, chạy chậm hẳn đi.

Nhiệm vụ chính khi phân tích:

1. Đánh giá hiệu quả sử dụng index. Ví dụ, index xịn xò của bạn mà chẳng ai dùng? Xóa luôn đi cho nhẹ!
2. Xác định tần suất thao tác đọc và ghi. Giúp biết bảng nào đang được dùng nhiều nhất.
3. Tối ưu hóa query. Thống kê giúp phát hiện chỗ nào có thể tăng tốc xử lý dữ liệu bằng cách thêm hoặc sửa index.

Các view pg_stat_user_indexes và pg_stat_user_tables

Trong PostgreSQL có hai view cực kỳ hữu ích để thu thập thống kê: pg_stat_user_indexes và pg_stat_user_tables. Cùng soi kỹ hơn nhé.

pg_stat_user_indexes: index được dùng thế nào?

Các trường chính:

• relname — tên bảng mà index liên quan tới.
• indexrelname — tên index.
• idx_scan — số lần index được dùng để tìm kiếm.
• idx_tup_read — số dòng được đọc qua index.
• idx_tup_fetch — số dòng thực sự trả về (sau khi lọc).

Ví dụ query:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Ở đây mình:

• sắp xếp dữ liệu theo số lần gọi index (idx_scan), để xem index nào hot nhất.
• nếu index gần như không được dùng (idx_scan = 0), nên cân nhắc: có cần giữ nó không?

Ứng dụng thực tế:

Bạn vừa deploy phiên bản app mới và thêm một index mới. Dùng pg_stat_user_indexes để kiểm tra xem query của bạn đã dùng index mới chưa, hay PostgreSQL vẫn chọn đường cũ, bỏ qua tuyệt tác tối ưu hóa của bạn.

pg_stat_user_tables: xem dữ liệu về bảng

Các trường chính:

• relname — tên bảng.
• seq_scan — số lần quét tuần tự bảng (không dùng index).
• seq_tup_read — số dòng trả về khi quét tuần tự.
• idx_scan — số lần quét bằng index cho bảng.
• n_tup_ins — số dòng được chèn vào
• n_tup_upd — số dòng được cập nhật.
• n_tup_del — số dòng bị xóa.

Ví dụ query:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Chúng ta thấy gì ở đây?

• Bảng có số lần quét tuần tự lớn (seq_scan) có thể là dấu hiệu cần thêm index.
• Số thao tác insert, update, delete giúp hiểu dữ liệu trong bảng thay đổi thường xuyên thế nào.

Ứng dụng thực tế: Bạn làm việc với bảng users, nơi lưu dữ liệu tất cả user của app. Dùng pg_stat_user_tables bạn phát hiện số lần quét tuần tự (seq_scan) của bảng này cao ngất ngưởng. Đó là tín hiệu: nên tạo index cho các cột được dùng nhiều nhất để tăng tốc query.

Ví dụ: phân tích index và bảng trong database thực tế

Giả sử bạn có database với các bảng orders (đơn hàng) và products (sản phẩm). Bạn muốn biết bảng và index được dùng hiệu quả ra sao.

Phân tích index:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Bạn thấy index orders_customer_id_idx được gọi 50 nghìn lần, còn orders_date_idx chỉ có 5 lần. Có thể orders_date_idx không cần thiết.

Phân tích bảng:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

Bảng products liên tục bị quét tuần tự. Đó là gợi ý: catalog sản phẩm thiếu index rồi.

Lỗi phổ biến và cách tránh

Bẫy kinh điển cho newbie là bỏ qua thống kê. Ví dụ, bạn thêm index mới với suy nghĩ: “Xong rồi, query sẽ bay vèo vèo”, nhưng PostgreSQL lại không dùng vì thống kê chưa được cập nhật tự động. Sau khi thay đổi lớn trong bảng, nhớ cập nhật thống kê thủ công bằng lệnh ANALYZE nhé.

Một lỗi nữa là cuồng thêm index. Nhớ là mỗi index chiếm dung lượng ổ cứng và làm chậm thao tác insert, update, delete. Dùng thống kê pg_stat_user_indexes để chắc chắn index thực sự hữu ích, chứ không phải nằm chết dí.

Lợi ích thực tế: dùng ở đâu?

Trong lập trình thực tế: nếu database bị chậm, việc đầu tiên là soi bảng và index.

Khi phỏng vấn: câu hỏi về tối ưu hóa index là kinh điển của phỏng vấn SQL. Giải thích được pg_stat_user_indexes là bạn đã qua nửa bài test rồi.

Khi quản trị database: monitoring là việc hàng ngày của DBA. Không có thống kê về bảng và index thì không tối ưu được gì đâu.