Сколько индексов базы данных слишком много?

Я обычно поступаю так.

1. получить журнал реальные запросы выполняются на данных в обычный день.
2. добавить индексы, чтобы наиболее важные запросы попали в индексы в их плане выполнения.
3. старайтесь избегать индексирования полей, которые имеют много обновлений или вставок
4. после нескольких индексов, получить новый журнал и повторить.

Как и при любой оптимизации, я останавливаюсь, когда достигается требуемая производительность (это очевидно подразумевает, что точки 0. будет получать конкретные требования к производительности).

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

alter index my_index_name monitoring usage;

затем вы можете отслеживать, используется ли индекс или не с этого момента вперед, запросив v$object_usage. Информацию об этом можно найти в руководство администратора базы данных Oracle®.

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

в хранилище данных очень часто используется большое количество индексов. Я работал с таблицами фактов, имеющими двести столбцов и 190 из них индексируются.

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

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

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

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

в парафраз Эйнштейн о простоте, добавьте столько индексов, сколько вам нужно, и не более.

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

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

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

В дополнение к точкам, которые все остальные подняли, оптимизатор на основе затрат несет затраты при создании плана для инструкции SQL, если есть больше индексов, потому что есть больше комбинаций для его рассмотрения. Вы можете уменьшить это, правильно используя переменные привязки, чтобы инструкции SQL оставались в кэше SQL. Затем Oracle может выполнить мягкий анализ и повторно использовать план, который он нашел в прошлый раз.

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

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

Я сделал несколько простых тестов на моем реальном проекте и реальной базе данных MySql. Я уже ответил в этой теме:какова стоимость индексации нескольких столбцов БД?

но я думаю, что будет лучше, если я процитирую его здесь:

Я сделал несколько простых тестов, используя мой реальный проект и реальная база данных MySql.

мои результаты: добавление среднего индекса (1-3 столбца в индексе) к таблице - делает вставки медленнее на 2,1%. Итак, если добавить 20 индексов, ваши вставки быть ниже на 40-50%. Но ваш выбор будет в 10-100 раз быстрее.

Так это нормально, чтобы добавить много индексов? - Он зависит :) я дал вам свои результаты-вы решай!

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

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

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

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

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

помимо простого индексирования, есть реогранизация вашей БД для включения вычисляемых полей поиска, разбиения таблиц и т. д.-Это действительно зависит от ваши формы загрузки и параметры запроса, сколько/какие данные "действительно" должны быть возвращены запросом.

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

для SQL Server я нашел помощник по настройке ядра СУБД полезным-вы настроили "типичные" рабочие нагрузки и он может давать рекомендации по добавлению / удалению индексов и статистики. Я уверен, что другие DBs имеют аналогичные инструменты, либо "официальные", либо сторонние.

Это действительно больше теоретические вопросы, чем практические. Влияние индексов на производительность зависит от аппаратного обеспечения вы имеете, версия Oracle, типы индексов и т. д. Вчера я слышал, что Oracle объявила о выделенном хранилище, сделанном HP, которое должно работать в 10 раз быстрее с базой данных 11g. Что касается вашего случая, то здесь может быть несколько решений: 1. Имейте большое количество индексов (>20) и перестраивайте их ежедневно (ночью). Это было бы особенно полезно, если таблица получает тысячи обновления / удаления ежедневно. 2. Разделите таблицу (если это применимо к вашей модели данных). 3. Используйте отдельную таблицу для новых / обновленных данных и запускайте ночной процесс, который объединяет данные вместе. Это потребует изменения логики приложения. 4. Переключитесь на IOT (index organized table), если ваши данные поддерживают это.

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

Если вы в основном читаете (и несколько обновлений), то на самом деле нет причин не индексировать все, что вам нужно индексировать. Если вы часто обновляетесь, вам может потребоваться быть осторожным в отношении количества индексов. Там нет жесткого числа, но вы заметите, когда все начнет замедляться. Убедитесь, что ваш кластеризованный индекс является тем, который имеет наибольший смысл на основе данных.

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

индекс накладывает стоимость при обновлении базовой таблицы. Индекс дает преимущество, когда он используется для выполнения запроса на сжатие. Для каждого индекса необходимо сбалансировать затраты и выгоды. Насколько медленнее выполняется запрос без индекса? Насколько преимущество работает быстрее? Можете ли вы или ваши пользователи терпеть медленную скорость, когда индекс отсутствует?

можно терпеть дополнительное время, необходимое для завершения обновления?

вам нужно сравните затраты и выгоды. Это специфично для вашей ситуации. Нет никакого магического числа индексов, которое проходит порог "слишком много".

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

сколько там колонок? Мне всегда говорили делать одностолбцовые индексы, а не многоколоночные индексы. Так что не больше индексов, чем количество столбцов, ИМХО.

Что это действительно сводится к тому, не добавить индекс, Если вы не знаете (и это часто означает сбор статистики использования), что он будет использоваться гораздо чаще, чем он обновляется.

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

Sql server дает вам некоторые хорошие инструменты, которые позволяют увидеть, какие индексы на самом деле используются. Эта статья,http://www.mssqltips.com/tip.asp?tip=1239, дает вам некоторые запросы, которые позволяют получить лучшее представление о том, сколько индекс используется, а не сколько он обновляется.

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