С улучшением производительности, мне было интересно, полезны ли и какие индексы в таблице соединений (специально используется в контексте Rails 3 has_and_belongs_to_many).

Настройка модели и таблицы

Мои модели Foo и Bar и для каждого соглашения rails, у меня есть таблица соединений, называемая bars_foos. В этой таблице bar_id:integer и foo_id:integer нет первичного ключа или временных меток, делающих старые поля. Мне интересно узнать, какой из следующих индексов лучше и без дублирования:

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

Вероятное использование

Наиболее часто используемым будет экземпляр модели Foo, я буду запрашивать связанный с ним bars с использованием синтаксиса RoR foo.bars и наоборот с bar.foos для экземпляра модели Bar.

Они будут генерировать запросы типа SELECT * FROM bars_foos WHERE foo_id = ? и SELECT * FROM bars_foos WHERE bar_id = ? соответственно, а затем использовать эти результирующие идентификаторы для SELECT * FROM bars WHERE ID in (?) и SELECT * FROM foos WHERE ID in (?).

Пожалуйста, исправьте меня в комментариях, если я ошибаюсь, но я не верю, что в контексте приложения Rails он попытается выполнить запрос, в котором он указывает оба идентификатора типа SELECT * FROM bars_foos where bar_id = ? AND foo_id = ?.

Базы данных

В случае, если есть конкретные методы оптимизации базы данных, я, скорее всего, буду использовать PostgreSQL. Однако другие, использующие этот код, могут захотеть использовать его в MySQL или SQLite в зависимости от конфигурации Rails, поэтому все ответы будут оценены.

Ответы

Ответ 1

Ответ

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

tl; dr Пояснение

Короткий ответ tl; dr для моего конкретного случая (и для охвата всех будущих баз) - это выбор # 2, что я и подозревал. Тем не менее, выбор № 3 будет работать очень хорошо, поскольку, в зависимости от моего использования данных, дополнительное время и пространство, используемые для создания составного индекса, могут сократить будущие запросы запросов.

Полное объяснение

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

Так как внешние ключи обычно являются идентификаторами другого класса AR, мощность обычно будет высокой. Но опять же, это зависит от ваших данных. В моем примере, если есть много Foo, но несколько Bar s, многие записи в моей таблице соединений будут иметь simliar bar_id s. Если bar_id имеет низкую мощность, индекс на bar_id никогда не может использоваться и может мешаться за счет того, что база данных выделяет время и ресурсы * для добавления к этому индексу каждый раз, когда создается новая запись bars_foos, То же самое происходит со многими Bar и несколькими Foo и несколькими из них.

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

Еще один хороший ответ, который я получил в офисе: "Почему вы беспокоитесь о своих индексах? Создайте приложение!"

Сноски

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

Ответ 2

Зависит от того, как вы собираетесь запрашивать данные.

Предполагая, что вы хотите найти все эти...

WHERE bar_id = ?
WHERE foo_id = ?
WHERE bar_id = ? AND foo_id = ?

... тогда вам следует, вероятно, перейти с индексом на {bar_id, foo_id} и индексом на {foo_id}.

В то время как вы могли бы также создать третий индекс на {bar_id}, цена поддержания дополнительного индекса, вероятно, перевешивала бы выгоду лучше clustering в меньшем индексе.

Также, как вы планируете cover ваши запросы с индексами? Некоторые из альтернатив, например...

{foo_id, bar_id} и {bar_id}
{foo_id, bar_id} и {bar_id, foo_id}

... может лучше охватывать определенные типы запросов.

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

(Отказ от ответственности: я не знаком с Ruby. Этот ответ исходит только из перспективы базы данных.)

Лучшие индексы SQL для таблицы соединений