Thông thường khi bạn xem một kế hoạch thực thi và thấy một index seek theo sau là một key lookup, điều đó có nghĩa là nó đang chạy tương đối nhanh.
Để giải thích, hãy lấy bảng Users trong cơ sở dữ liệu Stack Overflow và chạy truy vấn mà chúng tôi đã giải thích trong bài viết này:
Transact-SQL
123 | SELECT Id, LocationFROM dbo.UsersWHERE Location = 'Helsinki'; |
Miễn là chúng ta có một index trên Location, chúng ta có thể đi thẳng đến những người sống ở Helsinki nhờ vào cấu trúc của B-tree index:
Nếu chúng ta sửa đổi truy vấn một chút bằng cách chọn tất cả các cột thay vì chỉ Id và Location, thì chúng ta phải thực hiện một Key Lookup, giống như chúng ta đã thảo luận trong lớp How to Think Like the Engine. Đối với mỗi người sống ở Helsinki, chúng ta phải tra cứu hàng của họ trong clustered index để lấy tất cả các cột mà chúng ta cần. Tuy nhiên, điều đó không thực sự là vấn đề lớn, miễn là số lượng người sống ở Helsinki tương đối hạn chế. Như tôi đã viết trong bài viết đó, index seek + key lookup về cơ bản là hai index seek: một vào Helsinki, và sau đó là một seek (cho mỗi cư dân Helsinki) trên clustered index, theo Id của họ.
Tuy nhiên, hãy thêm một chút phức tạp vào truy vấn:
Transact-SQL
1234 | SELECT Id, LocationFROM dbo.UsersWHERE Location = 'Helsinki'AND Reputation > 10000; |
Bây giờ, tôi chỉ tìm kiếm những người có danh tiếng cao sống ở Helsinki. Các kế hoạch thực thi cho cả hai truy vấn, có và không có bộ lọc Reputation, đều trông giống nhau:
Nhưng có một điều hơi phức tạp ở đó. Vì Reputation không nằm trong index của chúng ta trên Location, chúng ta đang thực hiện Key Lookup trên mọi người sống ở Helsinki, ngay cả khi họ không đáp ứng bộ lọc Reputation của chúng ta. Chúng ta kết thúc việc thực hiện nhiều lần đọc logic hơn mức cần thiết để kiểm tra vị trí của họ, như hình ảnh động này minh họa:
Giải pháp: thêm Reputation vào index, nhưng đây là phần thú vị: Reputation thậm chí không cần phải nằm trong key! Nó thậm chí có thể nằm trong các includes của index. Chỉ cần nằm trong các includes có nghĩa là chúng ta không phải thực hiện các lần đọc logic bổ sung để thực hiện key lookup từ clustered index.
Để hiểu liệu điều này có đang xảy ra với bạn hay không, hãy di chuột qua toán tử key lookup trong kế hoạch truy vấn của bạn và tìm thuật ngữ "Predicate" mà không có bất kỳ tiền tố nào, như "Seek Predicate" – bạn chỉ đang tìm kiếm "Predicate" đơn thuần, như thế này:
Các con số "1 of 1" trên Key Lookup nghe có vẻ nhỏ, giống như những gã ở triển lãm xe hơi nói rằng chiếc Corvette của họ là 1 trong 1, trong khi thực tế họ có ý nói đó chỉ là 1 trong 1 chiếc xe được hoàn thiện với màu tím và sọc vàng, nội thất da lộn màu nâu, và thảm sàn sợi carbon tương phản, được chế tạo vào một ngày thứ Năm, bởi những gã tên là Mo. Key lookup đó đã được thực hiện một lần cho mỗi một trong 122 hàng được tìm thấy ở Helsinki – khi mà có ít hàng hơn nhiều thực sự đi ra khỏi key lookup với điểm danh tiếng >10.000.
Khi nào điều này là tệ, và làm thế nào để bạn khắc phục nó?
Residual predicates là tệ NẾU chúng có tính chọn lọc.
Trong trường hợp này, Reputation > 10000 thực sự rất có tính chọn lọc, vì vậy nếu chúng ta có thể sửa chúng ở cấp độ index, chúng ta sẽ thực hiện ít lần đọc logic hơn nhiều. Để khắc phục, tôi có thể đưa cột Reputation lên index Location. Tôi ít quan tâm đến việc liệu các cột như thế này có nằm trong key của index hay không, hoặc chúng nằm ở đâu trong key, hoặc trong các includes, miễn là chúng ít nhất nằm ở đâu đó trong index. Nếu chúng không nằm trong index chút nào, bạn sẽ có nhiều khả năng gặp phải các vấn đề với các quyết định key-lookup-versus-table-scan trong trình tối ưu hóa, dẫn đến các vấn đề về parameter sniffing, dẫn đến các tình huống khẩn cấp về hiệu suất bị sụt giảm nghiêm trọng.
Nếu bộ lọc KHÔNG có tính chọn lọc – ví dụ như nếu bộ lọc là User.Alive = 1 – thì tôi không thực sự quan tâm đến việc sửa nó. Trên thực tế, tôi sẽ ổn với việc để nguyên predicate đó, bởi vì tôi thà lưu trữ giá trị đó chỉ một lần (trên clustered index) thay vì sao chép nó trên mọi nonclustered index chỉ vì chúng ta lọc trên nó rất nhiều.