Использование более одного индекса на таблицу опасно?

Ответ 1

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

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

Я хотел бы поделиться примером, который я только что создал на своем 2-летнем ПК, используя стандартную установку MySQL. Я знаю, что вы отметили вопрос SQL Server, но этот пример должен быть легко преобразован. Я вставляю строки 1,000,000 в три таблицы. Одна таблица без индексов, одна таблица с индексом один и одна таблица с индексами девять.

drop table numbers;
drop table one_million_rows;
drop table one_million_one_index;
drop table one_million_nine_index;

/*
|| Create a dummy table to assist in generating rows
*/
create table numbers(n int);

insert into numbers(n) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

/*
|| Create a table consisting of 1,000,000 consecutive integers
*/   
create table one_million_rows as
    select d1.n + (d2.n * 10)
                + (d3.n * 100)
                + (d4.n * 1000)
                + (d5.n * 10000)
                + (d6.n * 100000) as n
      from numbers d1
          ,numbers d2
          ,numbers d3
          ,numbers d4
          ,numbers d5
          ,numbers d6;


/*
|| Create an empty table with 9 integer columns.
|| One column will be indexed
*/
create table one_million_one_index(
   c1 int, c2 int, c3 int
  ,c4 int, c5 int, c6 int
  ,c7 int, c8 int, c9 int
  ,index(c1)
);

/*
|| Create an empty table with 9 integer columns.
|| All nine columns will be indexed
*/
create table one_million_nine_index(
   c1 int, c2 int, c3 int
  ,c4 int, c5 int, c6 int
  ,c7 int, c8 int, c9 int
  ,index(c1), index(c2), index(c3)
  ,index(c4), index(c5), index(c6)
  ,index(c7), index(c8), index(c9)
);


/*
|| Insert 1,000,000 rows in the table with one index
*/
insert into one_million_one_index(c1,c2,c3,c4,c5,c6,c7,c8,c9)
select n, n, n, n, n, n, n, n, n
  from one_million_rows;

/*
|| Insert 1,000,000 rows in the table with nine indexes
*/
insert into one_million_nine_index(c1,c2,c3,c4,c5,c6,c7,c8,c9)
select n, n, n, n, n, n, n, n, n
  from one_million_rows;

Мои тайминги:

• 1 м строк в таблицу без индексов: 0,45 секунды
• 1m строк в таблицу с 1 индексом: 1,5 секунды
• 1 м строк в таблицу с 9 индексами: 6,98 секунд

Я лучше с SQL, чем статистика и математика, но я бы хотел подумать, что: Добавив 8 индексов к моей таблице, добавлено (6,98-1,5) 5,48 секунд. Каждый индекс затем предоставил 0,685 секунды (5,48/8) для всех 1 000 000 строк. Это означало бы, что добавленные накладные расходы для каждой строки на индекс составили бы 0,000000685 секунд. НЕКОТОРЫЕ ВЫЗОВЫ СОВЕТА ДИРЕКТОРОВ!

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

Ответ 2

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

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

Ответ 3

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

Для SQL Server мое правило:

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

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

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

Лучший контент для индексирования поступает от Kimberly Tripp - Queen of Indexing - см. ее сообщения в блоге здесь.

Ответ 4

Если вам не нравятся очень медленные чтения, у вас должны быть индексы. Не уходите за борт, но не бойтесь быть либеральным. КАЖДЫЙ FK должен быть проиндексирован. Вы посмотрите на каждый из этих столбцов на вставках в другие таблицы, чтобы убедиться, что ссылки установлены. Индекс помогает. Как и тот факт, что индексированные столбцы часто используются в соединениях и выбираются.

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

Ответ 5

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

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

Но обычный совет: если вы хотите узнать, что быстрее: profile!

Ответ 6

Так много зависит от вашей схемы и запросов, которые вы обычно запускаете. Например: если вам обычно нужно выбрать более 60% строк вашей таблицы, индексы не помогут вам, и будет дешевле сканирование таблицы, чем сканирование индексов, а затем поиск строк. Фокусные запросы, которые выбирают небольшое количество строк в разных частях таблицы или которые используются для объединений в запросах, вероятно, получат выгоду от индексов. Правильный индекс в нужном месте может сделать или сломать функцию.

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

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

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

Ответ 7

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

Сколько индексов для использования зависит от того, как используется таблица. Часто обновляемая таблица обычно имеет меньше индексов, чем та, которая читается гораздо чаще, чем она обновляется.

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

Ответ 8

Каждая таблица должна иметь PK, которая, конечно, индексируется (как правило, кластеризованная), то каждый FK также должен быть проиндексирован. Наконец, вы можете захотеть индексировать поля, по которым вы часто сортируете, если их данные хорошо различаются: для поля с 5 возможными значениями в таблице с 1 миллионом записей индекс не будет иметь большого преимущества.
Я склонен быть минималистичным с индексами, пока db не начнет хорошо заполняться и... медленнее. Легко определить узкие места и добавить только нужные индексы в этой точке.

Ответ 9

Оптимизация поиска с помощью индексов должна быть тщательно разработана, чтобы отражать фактические шаблоны запросов. Разумеется, для таблицы с Первичным ключом у вас будет хотя бы один кластерный индекс (то, как данные фактически хранятся), тогда любые дополнительные индексы используют расположение данных (кластерный индекс).
После анализа запросов, которые выполняются против таблицы, вы хотите создать индекс (ы), который их охватывает. Это может означать создание одного или нескольких индексов, но это сильно зависит от самих запросов. Это решение не может быть сделано только путем просмотра статистики столбцов.
Для таблиц, где они в основном вставляются, т.е. Таблицы ETL или что-то в этом роде, вы не должны создавать первичные ключи или фактически отбрасывать индексы и повторно создавать их, если данные слишком быстро изменяются или полностью воссоздаются/воссоздаются. Лично мне было бы страшно зайти в среду с жестко закодированным правилом индексов на коэффициент таблицы.