Как оптимизировать запрос с INNER JOIN и ORDER BY по нескольким таблицам?

Question

[danforth]

Всем привет, есть таблицы:
Bank 4900 строк
Branch 89000 строк
City 29000 строк
State 51 строка

Есть запрос, вкратце смысл такой:
Выбрать отделения для банка с URL slug, отсортировать по названию города, названию отделения, оставить первые 50 штук.

Делается для пагинации, так как для данного конкретного банка существует 5000 отделений, и вывалить их все на страницу не хочется.

Сам код запроса:

SELECT c.id, c.name, c.slug, s.id, s.name, s.slug, b.id, b.name, b.slug, b.location
FROM city c
INNER JOIN state s ON s.id = c.state_id
INNER JOIN branch b ON b.city_id = c.id
INNER JOIN bank bs ON b.bank_id = bs.id
WHERE bs.slug = 'some-bank'
ORDER BY c.name, b.name
LIMIT 50

Его EXPLAIN:

"id"	"select_type"	"table"	"partitions"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"filtered"	"Extra"
"1"	"SIMPLE"	"bs"	\t	"ref"	"PRIMARY,slug"	"slug"	"767"	"const"	"1"	"100,00"	"Using index; Using temporary; Using filesort"
"1"	"SIMPLE"	"b"	\t	"ref"	"location_bank_fk_idx,location_city_fk_idx"	"location_bank_fk_idx"	"4"	"banks.bs.id"	"17"	"100,00"	\t
"1"	"SIMPLE"	"c"	\t	"eq_ref"	"PRIMARY,city_state_fk"	"PRIMARY"	"8"	"banks.b.city_id"	"1"	"100,00"	\t
"1"	"SIMPLE"	"s"	\t	"eq_ref"	"PRIMARY"	"PRIMARY"	"4"	"banks.c.state_id"	"1"	"100,00"	\t

Сам запрос выполняется около 0.3 секунды, что для сайта многовато.

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

Comments

[danforth]

Максим Федоров, я приложил DDL таблиц в самом начале. Индекс по slug есть (HASH), по поводу FOREIGN KEY, если я не ошибаюсь, в MySQL FK CONTRAINT на InnoDB сразу с индексом идет.

По поводу вашего второго замечания, что нужно выбирать FROM bank bs, возможно вы правы, но вчера пытался сделать так, и производительность была та-же. Возможно, я допустил ошибку, сейчас попробую переписать запрос ещё разок.

upd.: Поменял местами, по времени то же самое. EXPLAIN тоже не изменился. https://pastebin.com/DhMAeHcW

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

запрос с GROUP BY

в заголовке есть GROUP BY, а в примерах его нет

[danforth]

Максим Федоров, спасибо, поправил, вместо GROUP BY хотел сказать INNER JOIN

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

а точно время выполнения у вас 0.3, а не 0.03?

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

ну что — что-либо получилось?

[danforth]

Максим Федоров, время 0.3 секунды. Пробовал разбивать на меленькие запросы, но когда начинаешь джойнить города с отделениями (city и branch), то сразу уходишь в 0.16-0.30 секунд. Попробую на днях залить на VPS этот дамп, может глюк с моей виртуалкой, хотя раньше запросы отрабатывали очень быстро, в этой же виртуалке постгрес + jsonb по 3 млн. товарам искал за 11-20 мс..

[danforth]

Максим Федоров, как вариант денормализация, и поле город класть в таблицу с ветками, рядом. Но не хотелось бы.

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

danforth, да не, тут не столько данных дял денормализации

[Екатерина]

Пробовал ли добавить уникальный индекс в таблицу branch на следующий набор полей (последовательность полей соблюдать):
bank_id, city_id, id
Если нет, то попробуй и после добавления этого индекса обязательно удалить
INDEX `location_bank_fk_idx` (`bank_id`)

Еще вопрос: насколько активно пишется в эти таблицы? Не могут возникать блокировки строк во время запроса?

[justpusher]

А без сортировки сколько выполняется?

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

justpusher, тут была удалена моя ветка -- я удалил ее

простой select с отделениями банков выполняется моментально
с подключением банков с урлом: сразу 0.16 (то есть на единственном JOIN уже валится)
с подклчюеием остального: 0.3

[justpusher]

Диск SSD?
Движок InnoDB? Сколько innodb_buffer_pool_size?
Кеш запросов включён? Результаты с использованием кеша запросов или без?

Answer 1

[Максим Павлючук]

Возможно поможет создание view
https://habrahabr.ru/post/47031/
после делать
SELECT * FROM view_name WHERE slug = 'some-bank' LIMIT 50

Answer 2

[bioroot]

Давно не пользовался MySQL, однако напишу по старой памяти. У вас очень много одиночных индексов, а в вашем случае этого уже не достаточно. Надо переходить минимум на двойные. К примеру, в таблице bank вас интересуют только slug и id. Но в случае одинарных индексов обработка вашего запроса будет происходить примерно следующим образом.

1. MySQL видит ключ slug, лезет в хранилище индекса и находит там указатель на соответствующую строку с данными.
   
2. MySQL смотрит, а что ему ещё нужно из этой таблицы. Оказывается, нужен id для джойна с таблицей branch.
   
3. MySQL лезет в хранилище данных, находит там строчку (или строчки) по указателю из 1-го пункта и из них вынимает id.
   

Если бы у вас был двойной индекс (slug, id), то нужный id обнаружился бы в хранилище индексов рядом со slug. И дальше в данные вообще не понадобилось бы лезть.

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

Comments

[justpusher]

В случае движка InnoDB замечание не актуальное, т.к. id добавляется в конец всех индексов.