Как оптимизировать ключи в MYSQL?

Question

[Max Ba]

Мужики, подскажите, что не так со структурой и как оптимизировать ключи?

Есть две таблицы:
1) `id`, `firma`, `product`, `price` (ключ только `id` AI смысловой нагрузки не имеет, просто порядковый номер)
2) `id`, `firma`, `product`, `price` (аналогично)

Нужно обновить таблицу 1, на основании таблицы 2.

UPDATE `tab1` t1
INNER JOIN `tab2` t2
ON t1.`firma` = t2.`firma` 
AND t1.`product` = t2.`product` 
SET t1.`price` = t2.`price`

Запрос на обновление идет 50 секунд!!! И это при 22.000 записей в одной и 5.000 записей во второй.

Я не очень понимаю принцип работы составных ключей. Наверно тут он и нужен. Есть просто INDEX, есть UNIQUE, также можно сгруппировать два поля в 1 индекс. Посоветуйте, как быть с индексом?

Answer 1

[Максим Федоров]

Рекомендую это к прочтению

Answer 2

[Евгений Вольф]

В Вашем вопросе отсутствует солидная часть вводных данных. Какие ключи, где они "есть", сколько идёт выборка, почему именно INNER JOIN? и так далее.

Ключи (индексы) у Вас должны стоять на тех полях, которые участвуют в условии выборки, т.к. в фильтрации. В вашем конкретном случае, это:

ON t1.`firma` = t2.`firma` AND t1.`product` = t2.`product`

то есть, ключи (индексы) должны быть установлены на полях firma и product, при этом, если Вы хотите добиться максимальной скорости при подобной операции - помимо того, что на полях firma и product должны быть проставлены индексы типа KEY - крайне желательно, что бы они были цифрами, т.к. с числами машины работают много быстрее.

Вообще, у всех объектов, которые Вы вот таким вот образом пытаетесь "склеить" друг с другом, должен быть какой-то уникальный идентификатор, ID, GUID или какой-то другой (на ваш выбор).

*Касательно именно чисел - зависит от ситуации конечно, в отдельных случаях это могут быть например GUID'ы, крайне желательно, что бы это были НЕ названия фирма, аля "ООО Рога и копыта" и НЕ названия продуктов, вроде "Батон нарезной #1".

Ну а если всё-таки Вам ну очень хочется использовать именно названия (заголовки) вместо уникальной идентификации каждого объекта, то имейте в виду, что в индекс попадают первые 255 символов. И, индекс по текстовым полям (CHAR/VARCHAR) - и сам по себе будет занимать больше, нежели его числовой аналог, и как следствие будет работать медленнее, нежели бы он был числом (идентификатором объекта).

Comments

[Max Ba]

Спасибо за такой ответ расширенный. У меня числовые индексы. Я попробовал сделать отдельные индексы и составные, и не пойму в них разницы. В выборке используется обязательно эти 2 поля. firma и produc. Разницы в скорости нет между отдельным индексом и составным.

Но проблема решена, прирост с созданием таких индексов ускорил до 1 сек запрос с 50, поэтому думаю вопрос решен на первое время. Касаемо GUID я пока не изучал, но мысль неплохая.

[Евгений Вольф]

maximka787: касательно GUID - запрос вида "SELECT UUID();" - вернёт UUID/GUID, некоторый уникальный идентификатор, иногда очень полезно использовать его, вместо ID. Особенно в БД типа MySQL, где "из коробки" не предусмотрены такие вещи, как например, "последовательности".

По поводу составных индексов - составной индекс в принципе не глобально отличается от обычного, если говорить совсем просто, Вы могли бы записать дату в одно поле и повесить на неё индекс, а могли бы создать 3 поля (год, месяц, день) и повесить составной индекс на 3 поля сразу. Если углубляться в тонкости работы MySQL, разница между ними будет, но... даже не знаю как это в кратце объяснить, в пределах "ответа". Касательно составного индекса, могу "по быстрому" дать один совет: если у Вас в выборке (в условиях) обязательно участвую два поля сразу - можете попробовать повесить на них составной индекс, если же эти поля участвуют в выборке по одному (или могут участвовать в такой выборке) - вешайте на каждое поле свой индекс. А вообще, мне кажется, лучше не заморачиваться с тонкостями составных индексов, в Вашем случае, с вероятностью 99% отлично подойдут обычные.

[Max Ba]

Еще раз большое Вам спасибо. Все очень предельно понятно написали. Буду углублять знания.