Составной индекс Mysql, лучшая практика

#mysql #indexing

Вопрос:

У нас есть структура базы данных , в которой все таблицы имеют cols company и shop , где отношение-одна компания ко многим магазинам. Таким образом, в таблице заказов должны быть cols id , company_id , shop_id , col_a , col_b , col_c … и т. Д. Все запросы к такой таблице будут иметь оператор where, начинающийся с company_id = xx AND shop_id = yy... , за которым следует фактический поиск по определенным столбцам.

Итак, вопрос в том, как построить индекс, например col_a , и col_c когда я знаю, что company_id и shop_id всегда включены в оператор where. Должен ли я создавать составные индексы для каждого типа ( company_id , shop_id , col_a ) и ( company_id , shop_id , col_c ). Или стратегия должна быть составной на ( company_id , shop_id ) и одиночной на ( col_a ) и ( col_c ).

Mysql версии 5.7

Заранее спасибо

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

2. Одна копия таблицы может использовать только один индекс, поэтому комбинировать (идентификатор компании, идентификатор магазина) и одиночные (col_a) и (col_c) не имеет смысла. Я рекомендую вам определить наиболее часто используемые столбцы col_N (2-3, не более) и создать один составной индекс, включающий эти столбцы. С другой стороны, если (company_id, shop_id) очень избирательно, то вам вообще не нужны дополнительные столбцы в индексе.

Ответ №1:

В составном индексе перечислите все столбцы, проверенные с = помощью first. Порядок тех, кто в INDEX(...) нем, не имеет значения.

Затем перечислите один «диапазон», если необходимо, или GROUP BY по порядку, или ORDER BY по порядку. Детали становятся запутанными; см. http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Эти руководящие принципы могут привести к некоторым или всем этим:

 (cid, sid, a, c)
(cid, sid, c, a)

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

Но бесполезно иметь

 (a) and (c)

Если только вам не нужно что-то вроде

 SELECT shop_id FROM t WHERE a = 123

В этом случае вам понадобится одностолбцовый или составной индекс, начиная с a .

(Как указывает Гийом, company_id вероятно, не должно существовать более чем в одной таблице, а именно Shops .)