Как работает поиск записи по индексу в SQL Server?
Что проверяют
Понимаете ли вы, что происходит между WHERE Id = @id и прочитанной строкой: сколько страниц читает сервер и откуда берутся лишние. Для C#-разработчика следом почти всегда идёт вопрос, почему запрос из EF Core или Dapper делает скан, хотя индекс есть.
Как отвечать
- устройство: индекс в SQL Server это B+-дерево из страниц по 8 КБ: корень, промежуточные уровни, листья. Даже на миллионах строк глубина обычно три-четыре уровня, поэтому точечный поиск стоит несколько логических чтений;
- кластерный индекс: в листьях лежат сами строки, упорядоченные по ключу. Он у таблицы один, таблица без него называется кучей (heap);
- некластерный индекс: в листьях ключ и указатель на строку. Если есть кластерный индекс, указатель это его ключ, в куче это RID: номер файла, страницы и слота;
- Index Seek и Index Scan: seek спускается от корня к нужному листу и для диапазона идёт дальше по связному списку листьев, scan читает весь индекс;
- Key Lookup: некластерный индекс нашёл строку, но нужных колонок в нём нет, и за каждой идёт отдельный поиск в кластерном индексе. На тысячах строк оптимизатор сочтёт это дороже скана. Лечится покрывающим индексом с
INCLUDE; - составной ключ: поиск работает по левой части, индекс по
(CustomerId, CreatedAt)не поможет фильтру только поCreatedAt.
Проверять стоит фактическим планом выполнения и SET STATISTICS IO ON, глядя на logical reads.
Где спотыкаются
Не знают про неявное приведение типов. Строковый параметр из SqlCommand или Dapper по умолчанию уходит как nvarchar, и если колонка varchar, в плане появляется CONVERT_IMPLICIT, а seek нередко превращается в scan. В EF Core колонку размечают IsUnicode(false), в Dapper передают DbString { IsAnsi = true }.
Оборачивают колонку в функцию: WHERE YEAR(CreatedAt) = 2026 индекс по дате не использует, нужен диапазон через >= и <.
Повторяют, что кластерный индекс задаёт физический порядок строк на диске. Порядок логический: листья связаны списком, а физически страницы могут лежать вразнобой, это и есть фрагментация.