Обычно, когда вы смотрите на план выполнения и видите поиск по индексу (index seek), за которым следует поиск по ключу (key lookup), это означает, что запрос выполняется относительно быстро.
Чтобы объяснить это, возьмём таблицу Users из базы данных Stack Overflow и выполним этот запрос, который мы объясняли в этой статье:
SELECT Id, Location
FROM dbo.Users
WHERE Location = 'Helsinki';
Пока у нас есть индекс по столбцу Location, мы можем напрямую «нырнуть» к людям, живущим в Хельсинки, благодаря структуре B-дерева:
Если мы немного изменим запрос, выбрав все столбцы вместо только Id и Location, нам придётся выполнить поиск по ключу (Key Lookup). Для каждого человека, живущего в Хельсинки, нам нужно найти его строку в кластерном индексе, чтобы получить все нужные столбцы. Это не проблема, если в Хельсинки живёт относительно немного людей. Поиск по индексу + поиск по ключу — это, по сути, два поиска по индексу: один в Хельсинки, а затем один поиск (для каждого жителя Хельсинки) по кластерному индексу, по их Id.
Однако давайте добавим немного больше сложности в запрос:
SELECT Id, Location
FROM dbo.Users
WHERE Location = 'Helsinki'
AND Reputation > 10000;
Теперь я ищу только людей с высокой репутацией, которые живут в Хельсинки. Планы выполнения для обоих запросов, с фильтром Reputation и без него, выглядят одинаково:
Но есть кое-что хитрое. Поскольку Reputation нет в нашем индексе по Location, мы выполняем поиск по ключу для каждого человека, живущего в Хельсинки, даже если они не соответствуют нашему фильтру по Reputation. Мы выполняем гораздо больше логических чтений, чем необходимо, чтобы проверить их местоположение, как показано в этой анимации:
Решение: добавить Reputation в индекс, но вот самая интересная часть: Reputation даже не обязательно должен быть в ключе! Он может быть даже в списке включённых столбцов (INCLUDES). Просто нахождение в INCLUDES означает, что нам не нужно выполнять дополнительные логические чтения для поиска по ключу в кластерном индексе.
Чтобы понять, происходит ли это у вас, наведите курсор мыши на оператор Key Lookup в вашем плане запроса и найдите термин «Predicate» без префикса, например «Seek Predicate» — вам нужно искать просто «Predicate», как здесь:
Цифры «1 of 1» в Key Lookup звучат красиво и маленько, как те парни на автошоу, которые говорят, что их Corvette — 1 из 1, хотя на самом деле они имеют в виду, что это была 1 из 1 машин, сделанных в фиолетовом цвете с жёлтой полосой, коричневым замшевым салоном, контрастными ковриками из карбона, сделанных в четверг, парнями по имени Мо. Этот поиск по ключу выполнялся один раз для каждой из 122 строк, найденных в Хельсинки, — хотя на самом деле строк, которые выходят из поиска по ключу с репутацией >10000, гораздо меньше.
Когда это плохо, и как это исправить?
Остаточные предикаты плохи, ЕСЛИ они селективны.
В этом случае Reputation > 10000 действительно очень селективен, поэтому если бы мы могли исправить это на уровне индекса, мы бы выполняли гораздо меньше логических чтений. Чтобы исправить это, я мог бы поднять столбец Reputation в индекс по Location. Меня меньше беспокоит то, находятся ли такие столбцы в ключе индекса, где они в ключе или во включённых столбцах, пока они хотя бы где-то в индексе. Если их вообще нет в индексе, вы с гораздо большей вероятностью столкнётесь с проблемами при выборе между Key Lookup и просмотром таблицы (table scan) в оптимизаторе, что приведёт к проблемам с Parameter Sniffing и к падению производительности «с обрыва».
Если фильтр НЕ селективен — например, если бы фильтр был User.Alive = 1 — то меня это особо не волнует. На самом деле, я бы не стал исправлять такой предикат, потому что я предпочёл бы хранить это значение только один раз (в кластерном индексе) вместо дублирования его в каждом некластерном индексе только потому, что мы часто по нему фильтруем.
Вот почему ваша главная задача при настройке индексов — просто разместить нужные столбцы на листовых страницах индекса. Также стоит углубляемся в такие пограничные случаи, где вам нужно решить, какие столбцы должны быть в ключе, а какие могут дешёво «ехать» во включённых столбцах, чтобы уменьшить количество поисков по ключу из-за остаточных предикатов, но это уже другая тема...
Комментариев нет:
Отправить комментарий