Как оптмизировать INSERT-SELECT SQL запрос?

Question

[Дмитрий Баскаков]

Есть такой запрос:

INSERT INTO table0 (row_1, row_2, row_3, row_4)
SELECT t1.row_5,
       t1.row_6,
       t1.row_7,
       t2.row_8
FROM table1 t1
       LEFT JOIN table2 t2 on t2.row_9 = t1.row_10
WHERE t2.row_11 IS NOT NULL;

Всего 500+ тыс строк. Время выполнения 21 минута.

Как можно уменьшить это время? Каким образом можно оптимизировать запрос?

Comments

[Akina]

Запрос синтаксически неверен и приведёт к ошибке.

Как оптмизировать INSERT-SELECT SQL запрос?

Оптимизировать его SELECT-часть.

Всего 500+ тыс строк.

Где именно?

[Дмитрий Баскаков]

Akina, Верен и к ошибке не приведет. Он не просто так выполнялся 20 минут. Всё корректно

[Дмитрий Баскаков]

Akina, 500 строк в таблице table1. Касательно оптимизации, я так и понял, поэтому и задал вопрос как именно это можно сделать

[Akina]

Дмитрий Баскаков,

Верен и к ошибке не приведет.

После сделанного исправления - да, верен.

500 строк в таблице table1.

А 500 тысяч где - в таблице table2?

Задавая вопрос по оптимизации, следует выложить:

1. Полный CREATE TABLE для всех таблиц (ниже указано, что СУБД MySQL либо MariaDB - значит, полный вывод SHOW CREATE TABLE tablename)
2. Сведения о статистике данных - общей и в разрезе используемых условий связывания/отбора
3. План выполнения проблемного запроса (для INSERT..SELECT - также отдельно план выполнения его SELECT-части и сведения о сравнительной скорости выполнения SELECT и INSERT..SELECT)
4. Сведения о сервере БД - точное название СУБД, включая точную версию (вывод SELECT VERSION();), сведения об аппаратной конфигурации сервера и основных настройках СУБД

[Vitsliputsli]

А что за странные массовые копирования данных, которые нужно делать постоянно, да еще и быстро? Быть может надо разобраться зачем это вообще надо?

[Дмитрий Баскаков]

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

[Дмитрий Баскаков]

Akina, 500 тыс строк в table1

1. Полный CREATE TABLE не очень хотелось бы выкладывать, проект не личный и не хочется подобную информацию выкладывать в открытый доступ. Именно поэтому все названия были заменены. А точного ответа я не требую, мне нужно лишь общее направление, подобно тому что высказал mayton2019 в своем ответе на вопрос
2. Для этого я слишком глуп. У меня есть 2 таблицы и запрос, нужно сделать его быстрее - и на этом все
3. Не особо понял про план, но в целом ответ как на второй пункт
4. MySQL, а подробнее я не знаю. Но на будущее запомню и буду смотреть что там за субд

Answer 1

[mayton2019]

Как всегда автор не указывает тип dbms. Ох-ох-ох..

Запрос состоит из двух функционалов. Один - это собственно выборка - и другой это вставка.
Скорее всего медленно работает выборка в части соединения двух таблиц.

INSERT INTO tabel0 (row_1, row_2, row_3, row_4)
SELECT oa.row_5,
       oa.row_6,
       oa.row_7,
       nw.row_8
FROM table1 t1
       LEFT JOIN table2 t2 on t2.row_9 = t1.row_10
WHERE t2.row_11 IS NOT NULL;

Вот здесь где идет join по row_9 и row_10 надо построить индексы по этим полям.
Потом прогнать analyze по обоим таблицам.

И запустить еще раз.

Вставка тоже может тормозить по разным условиям (триггеры, instead-of триггеры, сложные констрейнты
или просто сложные типы данных наподобие xml/json которые требуют много I/O). Но для начала
надо хотя-бы с выборкой разобраться.

Comments

[Дмитрий Баскаков]

Мне пришлось загуглить что такое dbms. MySQL(MariaDB) вроде оно. Из этого, я думаю, понятно что я даже не близко к эксперту баз данных

Касательно join, вспомнил что при добавлении его сильно начинает тормозить запрос. Так что может, да где-то там. В остальном пойду гуглить

[mayton2019]

Дмитрий Баскаков, гугли как построить индексы в MariaDB.

[Дмитрий Баскаков]

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