Влияет ли размер индекса на скорость MySQL?

Question

[Сергей Пуговкин]

Например, надо два индекса:

id1 unique integer
id2 unique char(255)

Скорее всего, при миллиардах записей, id2 char(255) будет слишком велик, что бы полностью уместиться в оперативной памяти, в отличие от id1 integer, и запрос по нему станет медленнее.
Но повлияет ли это на запросы только по полю id1, который должен остаться быстрым?

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

Comments

[Everything_is_bad]

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

[alexalexes]

Экспериментируйте. Индексы - это самостоятельные объекты в СУБД. Вы можете в любой момент добавить индексы к таблице, и в любой момент удалить. Да, будет просадка производительности на момент пересчета индекса, но на структуру хранения самой таблицы это никак не влияет, как там поживают индексы сбоку.

[Akina]

Скорее всего, при миллиардах записей, id2 char(255) будет слишком велик, что бы полностью уместиться в оперативной памяти

Ну при таких объёмах и по INT индекс будет не копеечный. И с учётом разделения памяти "на всех" не факт, что под него найдётся памяти, чтобы весь его всосать.

Плюс индекс по CHAR - как он будет использоваться? если для точного сравнения, то есть смысл использовать HASH индекс, а это уже объёмы куда как меньше. А вероятность коллизии ничтожна.

[Vitsliputsli]

Если планируются миллиарды записей (т.е. это уже подсчитано), под записью понимается не 2 столбца, а скорость ответа от БД считаем не в секундах, то следует пересмотреть подход к разработке БД. На миллиардах записей деградация будет очень ощутимой, так что тут нет речи не то что про индексы длиной 2k, тут скорее всего нужно задумываться о компромиссах в построении БД (ограничивать хранение, перенос контроля целостности в другой слой, шардирование, партиционирование и т.п.).

Answer 1

[Ипатьев]

Вопрос не в том, как индексы "хранятся", а в том, как используются. Индексы не живут в каком-то своем собственном альтернативном пространстве. А используют ту же самую оперативную память. Следовательно, если использовать в запросе какой-то индекс, то БД будет пытаться разместить его в оперативной памяти. И в итоге всем индексам будет не хватать места.

Если есть опасение, что индекс окажется слишком большим, надо заняться такой вещью, как размышления. И задать себе вопросы:

• А нужен ли нам избыточный тип CHAR? Почему бы не использовать нормальный VARCHAR?
  
• Нужен ли нам индекс по всей длине поля, или можно проиндексировать только начало, а остальное легко найдётся перебором?
  
• Если нужен по всей, то не сделать ли дополнительную колонку с MD5 от содержимого поля CHAR, которая будет занимать всего 16 байт, и проиндексировать уже её индекс типа HASH?
  

Answer 2

[Vamp]

Но повлияет ли это на запросы только по полю id1, который должен остаться быстрым?

Напрямую не повлияет.

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

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