Cơ chế hoạt động của Index Seek và Index Scan trong T-SQL là gì, và làm thế nào để tối ưu hóa các truy vấn có hiện tượng Table Scan trên các bảng dữ liệu lớn hàng triệu dòng?

Question

Accepted Answer

1. Bản chất cơ chế hoạt động

Index Seek: Query Optimizer sử dụng B-Tree Index để tìm kiếm trực tiếp các trang dữ liệu (data pages) chứa giá trị thỏa mãn điều kiện WHERE. Thao tác này cực kỳ hiệu quả, chỉ đọc một số lượng trang dữ liệu tối thiểu.
Index Scan (hoặc Table Scan): Query Optimizer phải duyệt qua toàn bộ các trang dữ liệu của bảng hoặc index từ đầu đến cuối để tìm kiếm dữ liệu. Chi phí I/O và CPU tăng tuyến tính theo kích thước bảng.

2. Nguyên nhân dẫn đến Table Scan / Index Scan

Thiếu Index phù hợp cho mệnh đề WHERE hoặc JOIN.
Sử dụng hàm hoặc toán tử chuyển đổi kiểu dữ liệu lên cột được đánh Index (ví dụ: WHERE YEAR(OrderDate) = 2023 làm mất tác dụng của Index trên cột OrderDate).
Cột đầu tiên trong Composite Index không được sử dụng trong điều kiện tìm kiếm (vi phạm quy tắc Left-most prefix).
Bảng có kích thước quá nhỏ (SQL Server đánh giá Scan nhanh hơn Seek).

3. Phương pháp tối ưu hóa

Sử dụng SARGable Predicates: Viết điều kiện truy vấn có thể tận dụng Index. Ví dụ thay vì WHERE YEAR(OrderDate) = 2023, hãy dùng WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'.
Tạo Covering Index: Đưa các cột cần SELECT vào mệnh đề INCLUDE của Index để tránh việc Bookmark Lookup.
Cập nhật Statistics: Chạy UPDATE STATISTICS định kỳ để Query Optimizer đưa ra Execution Plan chính xác.

4. Lưu ý thực chiến Enterprise
Đối với các hệ thống OLTP, việc để xảy ra Table Scan trên các bảng hàng triệu dòng trong các transaction thường xuyên sẽ gây nghẽn cổ chai CPU và Disk I/O, dẫn đến lock tranh chấp diện rộng. Luôn kiểm tra Execution Plan thực tế trên môi trường Staging với dữ liệu tương đương Production.