Ускорение предложения ORDER BY с индексом

Некластеризованные индексы можно абсолютно использовать для оптимизации сортировки. Индексы - это, по сути, бинарные деревья поиска, что означает, что они содержат значения, отсортированные по порядку.

Однако, в зависимости от запроса, вы можете поставить SQL Server в затруднительное положение.

Если у вас есть таблица со 100 миллионами строк, ваш запрос будет соответствовать 11 миллионам из них, как показано ниже: дешевле ли использовать индекс на category для выбора строк и сортировки результатов по name или для чтения всех 100 миллионов строк индекса, предварительно отсортированного по name, а затем отфильтруйте 89 миллионов из них, установив флажок category?

select ...
from product
where category = ?
order by name;

Теоретически SQL Server может использовать индекс name для чтения строк в порядке и использовать индекс category для эффективной фильтрации? Я настроен скептически. Я редко видел, чтобы SQL Server использовал несколько индексов для доступа к одной и той же таблице в одном запросе (при условии выбора одной таблицы, игнорирования объединений или рекурсивных CTE). Придется проверять индекс 100 миллионов раз. Индексы имеют высокие накладные расходы на поиск по индексу, поэтому они эффективны, когда один поиск значительно сужает набор результатов.

Не видя схемы, статистики и точного запроса, мне трудно сказать, что имеет смысл, но я ожидаю, что я обнаружу, что SQL Server будет использовать индекс для предложения where и сортировать результаты, игнорируя индекс в столбце сортировки.

Индекс в столбце сортировки может использоваться, если вы выбираете всю таблицу. Нравится select ... from product order by name;

Опять же, ваш пробег может отличаться. Это предположение, основанное на прошлом опыте.

Всего два цента.

Другой ответ хорош, но не касается идеи удаления единственного кластерного индекса. Для некоторых эта идея похожа на богохульство: D, но я видел это в действии.

Прежде всего, конечно, некластеризованные индексы можно использовать для ускорения поиска.

Если ваша таблица является таблицей кластеризованных индексов (большинство из них в SQL Server), все другие некластеризованные индексы становятся «вторичными» индексами и, следовательно, не так эффективны. Если ваш запрос извлекает несколько строк (скажем, менее 10 тыс. Строк), вы не заметите «эффекта вторичного индекса». Когда запрос извлекает много строк, вы начинаете видеть эту проблему.

Почему они не настолько эффективны? Потому что все вторичные индексы не указывают на «идентификатор строки». Они этого не делают, потому что в кластеризованных индексированных таблицах нет идентификатора строки. Вторичные индексы вместо этого указывают на ключ. И это может стать медленным, в зависимости от того, насколько плохая избирательность вашего кластерного индекса.

Совершенно другая стратегия, которую стоит рассмотреть хотя бы для целей сравнительного анализа, - это полное удаление кластерного индекса. Таким образом, все строки получат идентификатор строки, и внезапно все вторичные индексы станут настоящими первичными индексами. Если я не ошибаюсь, вам нужно будет (заново) создать свою таблицу как non-clustered; возможно, вы можете просто изменить его, но я не уверен.

Если ваши запросы становятся медленными, я думаю, стоит рассмотреть эту стратегию, чтобы ускорить все ваши вторичные индексы.

Что касается комментария @SeanLange о том, что индексы являются искусством, а не наукой, лучшая панель foo, которую я видел, - это то, где все столбцы таблицы были в первичном ключе. Кроме того, если вы не будете осторожны и просто создадите индексы на основе каждого плана выполнения запроса, вы, вероятно, в конечном итоге сохраните в индексах больше данных, чем то, что находится в реальной таблице.

Идея здесь в том, чтобы использовать покрытые запросы. В вашем случае я видел кластерные индексы, которые находятся в поле идентификации, где некластеризованный индекс содержит первичный ключ (обычно составной первичный ключ), который включает столбец кластеризованного индекса. Оттуда выберите SELECT на основе первичного ключа и порядка в кластеризованном индексе (он уже отсортирован).

Обновление:

Я только что видел план выполнения запроса. Вы получаете результат при сканировании таблицы, что означает, что ни один из столбцов в предложении WHERE не содержится ни в первичном ключе, ни в индексе. Что касается оптимизатора, таблица работает в куче. Следовательно, любой добавляемый вами индекс, содержащий (т. Е. Покрывающий) столбцы, содержащиеся в предложении WHERE, скорее всего, будет использоваться. В результате запрос вернется намного быстрее.

В идеале вы хотите видеть поиск по индексу, а затем сканирование индекса. Обычно оптимизатор ищет уникальный идентификатор по его порядковому номеру в индексе. Это означает, что если столбец идентификаторов является первым столбцом, указанным в индексе, вы должны быть вознаграждены поиском по индексу. Если первый столбец в индексе не уникален, вы получите сканирование индекса. Я бы не сказал, что это жесткие правила, но это мое понимание, основанное на литературе, которую я прочитал, и планах выполнения, которые я видел.