Как определить, требуется или необходим Индекс

Я использую инструмент автоматического индексирования в нашей базе данных MS SQL (я изменил сценарий, созданный Microsoft, который просматривает таблицы статистики индекса - Автоматическое индексирование автоматически ). Из статистики теперь у меня есть список рекомендаций для индексов, которые нужно создать.

Изменить: Индексы, описанные выше, берут информацию из DMV, в которой сообщается, что механизм базы данных будет использовать для индексов, если они были доступны, и скрипты принимают рекомендации Top x (путем поиска , влияние пользователя и т. д.) и поместите их в таблицу.

(Отредактируйте выше, частично взятый из ответа Ларри Коулмана ниже, чтобы уточнить, что делают скрипты)

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

Нужно ли мне запускать SQL Profiler, или лучше проверить код, запрашивающий таблицы? И есть ли у вас другой совет?

99 голосов | спросил misterjaytee 4 Jam1000000amTue, 04 Jan 2011 01:59:58 +030011 2011, 01:59:58

6 ответов


73

Я использую сценарии анализа индекса Джейсона Страта (старое местоположение) . Они рассказывают вам, как используются ваши существующие индексы, а также о том, сколько пропущенных индексов было бы использовано. Обычно я не добавляю индексы, если они не составляют более 5 или 10% запросов в таблице.

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

Обновление: Статьи блога анализа индекса Джейсона Страта для новых сценариев (новое местоположение)

Двойное обновление: В эти дни я использую sp_BlitzIndex® при анализе индексов.

ответил Jeremiah Peschka 4 Jam1000000amTue, 04 Jan 2011 02:04:45 +030011 2011, 02:04:45
42

Существует несколько понятий и терминов, которые важно понимать при работе с индексами. Поиски, сканирование и поиск - это некоторые из способов, которыми индексы будут использоваться с помощью операторов select. Селективность ключевых столбцов является неотъемлемой частью определения эффективности индекса.

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

Как правило, операции поиска более эффективны, чем сканирование, из-за физического запроса меньшего набора данных. Бывают ситуации, когда это не так, например, очень маленький набор исходных данных, но это выходит за рамки вашего вопроса.

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

Чтобы определить, насколько эффективен индекс, вы должны определить избирательность ваших индексных ключей. Селективность может быть определена как процент от отдельных записей к общим записям. Если у меня есть таблица [person] со 100 полными записями, а столбец [first_name] содержит 90 различных значений, мы можем сказать, что столбец [first_name] на 90% выборочно. Чем выше избирательность, тем эффективнее индексный ключ. Соблюдая избирательность, лучше всего поставить свои наиболее избирательные столбцы в свой индексный ключ. Используя мой предыдущий пример [person], что, если бы у нас был столбец [last_name], который был на 95% избирательным? Мы хотели бы создать индекс с [last_name], [first_name] как индексный ключ.

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

ответил Matt M 11 Jam1000000amTue, 11 Jan 2011 01:09:10 +030011 2011, 01:09:10
22

Недавно я открыл фантастический бесплатный скрипт от людей в BrentOzar Unltd http://www.brentozar.com/blitzindex/

Это хороший анализ того, какие индексы существуют, как часто они используются и как часто механизм запросов ищет индекс, который не существует.

Это правило, как правило, хорошее. Иногда это немного завышает идеи. Я до сих пор делал следующее:

  • Удалены индексы, которые НИКОГДА не читались (или, может быть, менее 50 раз в месяц).
  • Добавлены самые очевидные индексы для внешних ключей и полей, которые, как я знаю, мы используем много.

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

Как правило, вам следует избегать индексов:

  • Очень маленькие таблицы (менее 50-200 записей): часто механизм запросов работает быстрее, если он сканирует таблицу, а не загружает индекс, читает, обрабатывает его и т. д.
  • Избегайте индексов в столбцах с низкой величиной ( http://en.wikipedia.org/wiki/Cardinality_(SQL_statements) ) по первой упомянутой колонке. Например. Индексирование гендерного поля (M /F) очень мало используется, так же полезно проверять таблицу и находить ~ 50%, которые соответствуют. Если он указан после более определенного в индексе (например, [дата рождения, пол]), это лучше - вы можете захотеть, чтобы все самцы родились за определенный промежуток времени.

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

Я сократил несколько таблиц от 900 МБ до 400 МБ, только потому, что они были неструктурированными кучами заранее. http://msdn.microsoft.com/en-us/library/aa933131 (v = sql.80) .aspx

Реорганизовать /Rebuild

Вы должны посмотреть на фрагментированные индексы. Немного фрагментация в порядке, не обманывайтесь! http://technet.microsoft.com/en-us/library/ms189858.aspx Знать разница между реорганизацией и восстановлением!

Регулярно просматривать

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

Сколько

В недавнем видео Brent рекомендует (обычно) не более 5 индексов в таблице с большим количеством записей (например, таблица заказов) и не более 10, если она читается намного больше, чем написано (т.е. таблица журналов для аналитики) http://www.youtube.com/watch?v=gOsflkQkHjg

В целом

Это зависит!

Ваш пробег зависит от базы данных. Покройте очевидные (фамилию сотрудника, дату заказа и т. Д.) На ваших (сейчас /будущих) больших таблицах. Мониторинг, просмотр и настройка при необходимости. Он должен быть частью вашего обычного контрольного списка при управлении вашей базой данных:)

Надеюсь, это поможет!

ответил Greg Robson 10 Maypm13 2013, 16:52:05
13

Обычно каждый из них имеет определенную рабочую нагрузку (запросы) и тщательно тестирует влияние каждого нового индекса на рабочую нагрузку. Этот итерационный процесс должен всегда включать тщательный анализ планов выполнения, который будет показывать, какие индексы используются. Тема анализа запроса является длинной и начинается с выделенной главы MSDN Анализ запроса - хорошая ставка.

Иногда, когда рабочая нагрузка слишком сложна или знание дизайна базы данных является отрывочным, используется механизм базы данных Tuning Advisor , который выполняет некоторый автоматический анализ вашей рабочей нагрузки и предлагает некоторые индексы. Разумеется, эти предложения должны быть тщательно проанализированы, и воздействие должно быть немедленно измерено.

Итак, если вы будете следовать моей идее, добавление индекса и измерение воздействия на самом деле - это всего лишь случай Тестирование A /B : вы запускаете свою рабочую нагрузку без индекса в качестве базовой строки, затем запускаете ее с индексом, измеряете и сравниваете с базовой линией, а затем решаете на основе наблюдаемых и измеренных показателей, если это выгодно. Рабочая нагрузка - это лучший набор тестов хорошего качества, но он также может быть воспроизведением захваченной рабочей нагрузки, см. Как для: Повторить файл трассировки .

Более синтетический ответ - посмотреть sys.dm_db_index_usage_stats и посмотрите, как используются индексы, но обычно это подход для анализа на месте на неизвестной рабочей нагрузке (т. е. консультант, призванный помочь, вероятно, начнет с этого).

ответил Remus Rusanu 16 32011vEurope/Moscow11bEurope/MoscowWed, 16 Nov 2011 02:54:47 +0400 2011, 02:54:47
6

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

Хорошим подходом было бы отсортировать запрос отсутствующих индексов по количеству запросов и рассмотреть возможность добавления первых индексов.

ответил Larry Coleman 4 Jam1000000amTue, 04 Jan 2011 03:23:29 +030011 2011, 03:23:29
-1

Это зависит от того, как эта таблица используется. например скажем, у меня есть таблица, которая читается много раз, но обновления и вставки встречаются редко. Плюс я всегда запрашиваю таблицу в некотором столбце внешнего ключа. Имеет смысл создать (не кластерный) индекс над этим внешним ключом, чтобы ускорить чтение запросов. Но недостатком является то, что ваша вставка, обновление будет медленным.

Есть несколько статистических запросов, которые указывают, сколько запросов времени занимает. Начните с самых медленных. Если предикат запроса не имеет индекса, создание его поможет.

ответил Ankush 16 32011vEurope/Moscow11bEurope/MoscowWed, 16 Nov 2011 02:46:27 +0400 2011, 02:46:27

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132