У меня есть приложение, которое должно обновлять большой объем данных по большому количеству записей. В основном он содержит около 7000 вставок и / или обновлений, но требует времени looooong (например, почти 9 минут … в среднем около 0,08 секунды на запрос). По сути, я ищу общие ускорения для создания нескольких таких запросов (я не ожидаю конкретного ответа на мой нечеткий пример … это просто, надеюсь, поможет объяснить).
Вот некоторые примеры профилирования запросов:
SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (timestamp = '2010-10-15T07:30:00-07:00') AND (planet_id = '2010_Gl_581_c') INSERT INTO `habitable_planets` (`planet_id`, `timestamp`, `weather_air_temp`, `weather_cell_temp`, `weather_irradiance`, `weather_wind_float`, `biolumin_to_date`, `biolumin_detected`, `craft_energy_usage`, `craft_energy_consumed_to_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (timestamp = '2010-10-15T07:45:00-07:00') AND (planet_id = '2010_Gl_581_c') INSERT INTO `habitable_planets` (`planet_id`, `timestamp`, `weather_air_temp`, `weather_cell_temp`, `weather_irradiance`, `weather_wind_float`, `biolumin_to_date`, `biolumin_detected`, `craft_energy_usage`, `craft_energy_consumed_to_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Повторите рекламную тошноту (ну, около 7000 раз). Это обновление, которое собирает данные, сгенерированные с интервалами в течение 24-часового периода, а затем делает массивное обновление базы данных один раз в день. Учитывая ограниченный бит, который я показал, у вас есть предложения по ускорению этого процесса?
Например … было бы целесообразно, а не делать выбор для каждой отметки времени, сделать выбор для всего диапазона сразу, а затем перебрать их в скрипте?
Смутно нравится:
SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (planet_id = '2010_Gl_581_c')
присвойте этот результат $foo а затем выполните:
foreach ($foo as $bar) { if ($bar['timestamp'] == $baz) // where $baz is the needed timestamp { // do the insert here } }
EDIT: Чтобы добавить немного к этому, одна вещь, которая улучшила отзывчивость в моей ситуации, заключалась в том, чтобы изменить кучу кода, который проверял существующую запись, и либо сделал вставку, либо обновление в зависимости от результата использования INSERT... ON DUPLICATE KEY UPDATE sql query. Это привело к примерно 30% -ному увеличению скорости в моем конкретном случае, потому что оно сократило по крайней мере одну поездку в базу данных из уравнения и более тысячи запросов, которые это действительно добавляет.
Некоторые полезные ссылки:
Из документации MySQL:
Скорость заявлений INSERT гласит:
Если вы одновременно вставляете много строк из одного и того же клиента, используйте инструкции INSERT с несколькими списками VALUES для вставки нескольких строк за раз. Это значительно быстрее (во многих случаях быстрее), чем использование отдельных однострочных инструкций INSERT. Если вы добавляете данные в непустую таблицу, вы можете настроить переменную bulk_insert_buffer_size, чтобы сделать вставку данных еще быстрее.
Если несколько клиентов вставляют много строк, вы можете получить более высокую скорость, используя инструкцию INSERT DELAYED.
Для таблицы MyISAM вы можете использовать параллельные вставки для добавления строк в то же время, когда выполняются операторы SELECT, если в середине файла данных нет удаленных строк.
При загрузке таблицы из текстового файла используйте LOAD DATA INFILE. Это обычно в 20 раз быстрее, чем при использовании инструкций INSERT.
С некоторой дополнительной работой можно сделать LOAD DATA INFILE еще быстрее запущен для таблицы MyISAM, когда таблица имеет много индексов.
Если вы еще этого не сделали, используйте подготовленные операторы (через mysqli , PDO или другую библиотеку DB, которая их поддерживает). Повторное использование одного и того же подготовленного оператора и простое изменение значений параметров помогут ускорить процесс, поскольку сервер MySQL должен только разобрать запрос.
INSERT s можно загрузить, предоставив несколько наборов VALUES – один запрос, который вставляет много строк, намного быстрее, чем эквивалентное количество отдельных запросов, каждый из которых вставляет одну строку.
То, что вы предлагаете, является правильным. Попытайтесь уменьшить количество запросов, отправляемых на сервер, так как это избавит вас от многочисленных издержек связи.
Конечно, если запрос inital select ranged возвращает слишком много данных, тогда у вас может быть узкое место на PHP.
Возможно, я использовал бы хранимую процедуру, если это возможно, и просто позвоню из моего кода приложения. Кроме того, я бы подумал о правильном индексировании и, возможно, о замене суррогатного ключа на основе целых чисел для planet_id, поскольку ключ с 13-байтным символом не будет таким же результативным в объединениях и поисках, как 4-байтовый целочисленный.
(Я только что прочитал, что в мире все еще есть 10 миллионов триллионов обитаемых планет, поэтому, возможно, вам лучше использовать bigint unsigned: P)
drop procedure if exists update_insert_habitable_planets; delimiter # create procedure update_insert_habitable_planets ( in p_planet_id int unsigned, in p_timestamp datetime, ) proc_main:begin start transaction; -- bulk update / insert whatever commit; end proc_main # delimiter ;