Ускорение предложения ORDER BY с индексом
У меня есть запрос с ORDER BY предложение, которое является медленным из-за таблицы, имеющей более 11 миллионов строк.
Я могу значительно ускорить его, добавив кластерный индекс на столбец в ORDER BY пункт. Однако программное обеспечение создает запрос на заказ по различным столбцам, в зависимости от пользовательских настроек. И вы не можете добавить более одного кластеризованного индекса в таблицу.
Мой вопрос: можно ли использовать некластеризованные индексы для улучшения ORDER BY спектакль? Или в кластерных индексах есть что-то особенное, что означает, что я не смогу быстро отсортировать все столбцы?
Примечание. Я разместил свой реальный запрос и план выполнения в Интернете, но есть и другие проблемы, которые я не хочу здесь обсуждать. Я не создал базу данных и не написал запрос. И запрос все еще очень медленный, даже без IN пункт.
4 ответа
Некластеризованные индексы могут быть абсолютно использованы для оптимизации сортировки. Индексы - это, по сути, двоичные деревья поиска, что означает, что они содержат значения, отсортированные по порядку.
Однако, в зависимости от запроса, вы можете поставить 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 и будет сортировать результаты, игнорируя индекс в столбце sort.
Индекс в столбце сортировки может использоваться, если вы выбираете всю таблицу. подобно select ... from product order by name;
Опять же, ваш пробег может варьироваться. Это предположение, основанное на прошлом опыте.
Просто мои два цента.
Другой ответ хорош, но не касается идеи удаления единственного кластерного индекса. Для некоторых людей эта идея похожа на богохульство:D, но я видел это в действии.
Прежде всего, конечно, некластеризованные индексы могут быть использованы для ускорения поиска.
Если ваша таблица является таблицей кластеризованных индексов (большинство из них в SQL Server), все другие некластеризованные индексы становятся "вторичными" индексами и, следовательно, не столь эффективны. Если ваш запрос извлекает несколько строк (скажем, менее 10 000 строк), вы не заметите "эффект вторичного индекса". Это когда запрос извлекает много строк, когда вы начинаете видеть эту проблему.
Почему они не так эффективны? Потому что все вторичные индексы не указывают на "идентификатор строки". Нет, потому что в кластеризованных индексированных таблицах нет идентификатора строки. Вторичные индексы указывают на ключ вместо этого. И это может стать медленным, в зависимости от того, насколько плоха селективность вашего кластерного индекса.
Совершенно другая стратегия, которую стоит рассмотреть, по крайней мере, для целей бенчмаркинга, заключается в полном удалении кластеризованного индекса. Таким образом, все строки получат идентификатор строки, и внезапно все вторичные индексы станут истинными первичными индексами. Если я не ошибаюсь, вам нужно (заново) создать свою таблицу как non-clustered; может быть, вы можете просто изменить его, но я не уверен.
Если ваши запросы становятся медленными, я думаю, стоит рассмотреть эту стратегию, чтобы ускорить все ваши вторичные индексы.
Что касается комментария @SeanLange о том, что индексы - это искусство, а не наука, то лучший бар foo, который я видел, - это где все столбцы таблицы были в первичном ключе. Кроме того, если вы не будете осторожны и просто создадите индексы на основе каждого плана выполнения запроса, вы, вероятно, в конечном итоге будете хранить больше данных в индексах, чем в фактической таблице.
Идея здесь состоит в том, чтобы использовать покрытые запросы. В вашем случае я видел кластерные индексы, которые находятся в поле идентификатора, где некластеризованный индекс содержит первичный ключ (обычно составной первичный ключ), который включает столбец кластеризованного индекса. Оттуда SELECT основывается на первичном ключе и порядке кластерного индекса (он уже отсортирован).
Обновить:
Я только что видел план выполнения запроса. Вы получаете сканирование таблицы, что означает, что ни один из столбцов в предложении WHERE не содержится ни в первичном ключе, ни в индексе. Что касается оптимизатора, таблица работает в куче. Поэтому любой индекс, который вы добавляете и который содержит (то есть охватывает) столбцы, содержащиеся в предложении WHERE, вероятно, будет использоваться. В результате запрос вернется гораздо быстрее.
В идеале, вы хотите увидеть поиск индекса, а затем сканирование индекса. Обычно оптимизатор ищет уникальный идентификатор по его порядковой позиции в индексе. Это означает, что если столбец идентификаторов является первым столбцом, указанным в индексе, вы должны быть вознаграждены поиском по индексу. Если первый столбец в индексе не является уникальным, то вы получите сканирование индекса. Я бы не сказал, что это жесткие и быстрые правила, но это мое понимание, основанное на литературе, которую я прочитал, и планах исполнения, которые я видел.
Я бы предположил, что лучшим решением этой проблемы было бы
- Создайте суррогатный ключ в качестве индекса кластера
- Создавайте составные индексы для ваших запросов
Например, у вас есть
SELECT a,b,c
FROM tbl
WHERE x=?,y=?,z=?
ORDER BY j,k,l DESC
Затем вы создаете составной индекс
INDEX xyz_jkl (x,y,z,j,k,l DESC)
Таким образом, вы оптимизируете для каждого запроса.
Суррогатный ключ важен для запросов вне этой таблицы. Наличие поля AUTO_INCREMENT также ускоряет INSERT.
Также имейте в виду, что PRIMARY KEY (кластеризованный индекс) всегда включается в индекс.