Как испровить медленный запрос к базе данных?

Question

[kiddle]

Здравствуйте, по работе столкнулся с такой задачей, что мне необходимо перенести данные с одной таблицы в другую. К слов на работе есть проекты, которые имеют свои базы данных с аналагичной структурой. Ну так вот процесс INSERT у меня весит сутки, хотя запрос вроде и простой. В то же время тот же запрос на других базах выполняется минут за 20 (при условии, что нужно перенести почти 2 миллиона строк)

SELECT 
 	1, 
 	ap.`sub_id`, 
 	sc.Sub_Class_ID , 
 	ap.`sort`, 
 	CONCAT(ap.`part_picture_id`, '-', ap.`sub_id`), 
 	ap.`id`, 
 	ap.`partname`, 
 	NOW() 
FROM `Acat_Parts` ap 
LEFT JOIN Sub_Class sc on ap.sub_id = sc.Subdivision_ID

Как получилось выяснить весь процесс виснет из-за

LEFT JOIN Sub_Class sc on ap.sub_id = sc.Subdivision_ID

Раньше этот запрос был вида

(SELECT `Sub_Class_ID` FROM `Sub_Class` WHERE `Subdivision_ID`=`sub_id`)

и если убрать обращение к таблицу Sub_Class, то все происходит очень быстро.

Никак не могу найти причину, почему в других проектах аналогичный запрос работает шустро, а в текущем зависает на сутки. Может кто-то сталкивался с подобным?

Comments

[kiddle]

mayton2019, Да, его тоже проиндексировал

[mayton2019]

kiddle, а попробуй сделай analyze table для обоих таблиц как тут пишут.

https://dev.mysql.com/doc/refman/5.7/en/analyze-ta...

[kapp1]

Какие размеры таблиц? Сколько строк?

Если в запросе вместо left join оставить только join работает быстрее? (да я знаю, что вы получите не тот результат, вопрос быстрее или нет)

[kiddle]

kapp1, примерно 1.900.000. Про оптимизацию я пока не думал, одно не ясно почему тот же запрос в других проектах при том же кол-ве строке отрабатывает быстро

[kiddle]

mayton2019, это результат на десять тыс

[kapp1]

kiddle, вы проверили без лефт джоин?
Про какие другие проекты речь? Там такой же движок, конфиг и железо и такие же таблицы?

[kiddle]

kapp1, да, проекты на cms netcat, у этой cms есть уже свои таблицы, плюс к этому созданы точно такие же таблицы как и везде, и все они заполняются примерно такими же данными в +- таком же объеме

[kiddle]

kapp1, джоином сделал только что, прошло быстро!

[AUser0]

А в таблице Sub_class хранится по 100500 записей с повторяющимся значением Subclass_ID, да?

[kiddle]

AUser0, в таблице Sub_Class примерно 90.000 записей. Значения в Subclass_Id все разные

[mayton2019]

kiddle, что у тебя в тексте? Не вижу... Там надо analyze table [table name]... А для запроса - explain select...

Это первое. И второе. Что меня смущает. Ты уверен что мы воюем именно с select?
У тебя 2 конфигурации на одной из них процесс ETL идет 20 минут а в другой - более суток.
Я здесь вижу что проблемой может быть также insert. Он зависит от многих параметров но
главное - от того destincation куда ты вставляешь 2 млн строк. Всяко тут может быть.
Проверяй эту версию.

И помни что любой SQL запрос select имеет 2 фазы. Первая фаза - это execution. Это пока висят
песочные часы. И второе это fetch - когда БД уже начинает выдавать строки на экран. Новички
часто этого не понимают и считают что им интересен только execution. А в твоей задаче
в миграции строк важны обе фазы. Поэтому твои эксперименты по select должны включать
не визуальную оценку (как только получил 1 page). А ПОЛНЫЙ подсчет всех 2 млн строк.
Это если ты хочешь играться с оптимизацией select.

[kapp1]

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

[kiddle]

Всем большое спасибо! Узнал много нового. Оказывается индекс по полю Subdivision_ID не был поставлен! Хотя ставил, видимо не сохранилось. Еще раз спасибо!

[kiddle]

mayton2019, война точна была с select, проверял, когда заменял

(SELECT `Sub_Class_ID` FROM `Sub_Class` WHERE `Subdivision_ID`=`sub_id`)

просто на 1

[mayton2019]

kiddle, тестируй с индексом.

[Akina]

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

И я не понимаю, почему в вопросе упомянуты перенос и INSERT, но показан запрос на выборку. Описывать надо весь процесс.

Ну и 20 минут на вставку 2кк записей - это что-то дофига.

[kiddle]

mayton2019, вы были правы!

Answer 1

[mayton2019]

Из быстрых советов - проверь что Subdivision_ID проиндексирован.