Как оптмизировать INSERT-SELECT SQL запрос?
Есть такой запрос:
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; |
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 минута.
Как можно уменьшить это время? Каким образом можно оптимизировать запрос?
Дополнительно:
Запрос синтаксически неверен и приведёт к ошибке.
Как оптмизировать INSERT-SELECT SQL запрос?
Оптимизировать его SELECT-часть.
Всего 500+ тыс строк.
Где именно?
Верен и к ошибке не приведет.
После сделанного исправления - да, верен.
500 строк в таблице table1.
А 500 тысяч где - в таблице table2?
Задавая вопрос по оптимизации, следует выложить:
1. Полный CREATE TABLE для всех таблиц (ниже указано, что СУБД MySQL либо MariaDB - значит, полный вывод SHOW CREATE TABLE tablename)
2. Сведения о статистике данных - общей и в разрезе используемых условий связывания/отбора
3. План выполнения проблемного запроса (для INSERT..SELECT - также отдельно план выполнения его SELECT-части и сведения о сравнительной скорости выполнения SELECT и INSERT..SELECT)
4. Сведения о сервере БД - точное название СУБД, включая точную версию (вывод SELECT VERSION();), сведения об аппаратной конфигурации сервера и основных настройках СУБД
1. Полный CREATE TABLE не очень хотелось бы выкладывать, проект не личный и не хочется подобную информацию выкладывать в открытый доступ. Именно поэтому все названия были заменены. А точного ответа я не требую, мне нужно лишь общее направление, подобно тому что высказал mayton2019 в своем ответе на вопрос
2. Для этого я слишком глуп. У меня есть 2 таблицы и запрос, нужно сделать его быстрее - и на этом все
3. Не особо понял про план, но в целом ответ как на второй пункт
4. MySQL, а подробнее я не знаю. Но на будущее запомню и буду смотреть что там за субд
Как всегда автор не указывает тип 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; |
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). Но для начала
надо хотя-бы с выборкой разобраться.
- Мне пришлось загуглить что такое dbms. MySQL(MariaDB) вроде оно. Из этого, я думаю, понятно что я даже не близко к эксперту баз данных
Касательно join, вспомнил что при добавлении его сильно начинает тормозить запрос. Так что может, да где-то там. В остальном пойду гуглить
- Дмитрий Баскаков, гугли как построить индексы в MariaDB.
- mayton2019, загуглил, помогло. В целом, как я понял, индексы могут многое. Так что теперь нужно и их изучать. Спасибо за ответ
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для оптимизации INSERT-SELECT SQL запроса, можно использовать несколько методов:
1. Используйте конкретные столбцы в SELECT запросе: вместо SELECT * укажите только те столбцы, которые вам действительно нужны. Это сократит объем передаваемых данных и ускорит выполнение запроса.
INSERT INTO table2 (column1, column2, column3) SELECT column1, column2, column3 FROM table1 WHERE condition;
2. Оптимизируйте индексы: убедитесь, что у таблиц, участвующих в запросе, есть соответствующие индексы для быстрого поиска данных. Например, создайте индексы для столбцов, используемых в условии WHERE.
3. Используйте временные таблицы: если вам необходимо выполнить сложные преобразования данных перед вставкой, вы можете создать временную таблицу для хранения результатов SELECT запроса, а затем выполнить INSERT из временной таблицы.
CREATE TEMPORARY TABLE temp_table SELECT column1, column2 FROM table1 WHERE condition; INSERT INTO table2 (column1, column2) SELECT column1, column2 FROM temp_table;
4. Используйте множественные значения для вставки: если у вас есть несколько строк данных для вставки, вы можете вставить их все за один запрос, что может быть более эффективно, чем множественные отдельные запросы.
INSERT INTO table2 (column1, column2) SELECT column1, column2 FROM table1 WHERE condition; INSERT INTO table2 (column1, column2) SELECT column1, column2 FROM table1 WHERE condition2;
Соблюдение этих принципов поможет оптимизировать INSERT-SELECT SQL запрос и улучшить производительность вашей базы данных.