эффективное упорядочение ключей в составном индексе MySQL (полиморфные ассоциации WRT Rails и STI)

Ранее я задавал этот вопрос о составных индексах для полиморфных внешних ключей в ActiveRecord < / а>. Основанием для моего вопроса было мое понимание того, что индексы должны основываться на мощности вашего столбца, и, как правило, у Rails типа STI и полиморфных столбцов _type довольно низкая мощность.

Признавая, что ответ на мой вопрос правильный - это имеет значение для индексации столбцов _id с высокой мощностью и столбцов _type с низкой мощностью, потому что вместе они имеют высокую мощность - мой следующий вопрос: как вы должны упорядочить свое соединение индексы?

Индекс [owner_id, owner_type] помещает поле с более высокой мощностью на первое место, а [owner_type, owner_id] помещает поле с более высокой мощностью на второе место. Является ли запрос, использующий первый ключ, более производительным, чем запрос, использующий последний ключ, или они одинаково эффективны?

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


mysql ruby-on-rails indexing activerecord sti
person jmaxyz    schedule 09.02.2011    source источник
comment
В некоторых случаях полиморфные ассоциации Rails используют только 2-3 типа, поэтому, возможно, имеет смысл даже не использовать полиморфные ассоциации и вместо этого использовать отдельные столбцы внешнего ключа, такие как: back_account_id, merchant_id, client_id. Затем создайте отдельные индексы для каждого из этих столбцов. Однако наличие большого количества индексов замедляет редактирование ... Ага - дилемма.   -  person Alex Kovshovik    schedule 17.06.2014

Ответы (2)


arrow_upward
5
arrow_downward

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

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

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

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

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

Итак, из того, что вы объяснили в своем вопросе, вам может быть лучше с двумя индексами: индекс составного ключа на (owner_id, owner_type) и другой на (owner_type).

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

Обновление: есть еще один довольно популярный вопрос SO об индексе составного ключа:

Когда следует использовать составной индекс?

person Nicolas Buduroi    schedule 13.02.2011
comment
Индексирование только по типу owner_type может не иметь большого смысла, если у вас есть таблица со 100 миллионами строк и только 3-5 различными значениями owner_type - это только замедлит вставку / обновление / удаление. - person Alex Kovshovik; 17.06.2014

arrow_upward
4
arrow_downward

TL; DR Сначала укажите тип, затем идентификатор.

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

В противном случае [type, id] даст индекс верхнего уровня, который можно повторно использовать при поиске только по типу. Второе решение всегда будет соответствовать одной строке, поскольку идентификатор уникален по типу, поэтому вы по-прежнему уверены, что после разрешения индекса не будет сканирования строки.

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

person Duke    schedule 17.10.2016