MYSQL запрос выполняется очень медленно

Попробуйте эти вещи, чтобы увеличить производительность вашего запроса:

• определите индексирование в вашей структуре базы данных и получите только те столбцы, которые вам нужны.
• Не используйте * в запросе выбора.
• И не ставьте идентификаторы в кавычках, как User.id='51394'вместо User.id= 51394,
• Если вы даете идентификаторы в кавычках, то ваша индексация не будет работать. Такой подход повышает производительность ваших запросов на 20% быстрее.
• Если вы используете ENGINE=MyISAM тогда вы не сможете определить индексирование между таблицей базы данных, измените ядро ​​базы данных на ENGINE=InnoDB, И создайте некоторую индексацию, например внешние ключи, полнотекстовую индексацию.

Если я понимаю, для всех результат SELECT * FROM AdvanceBulkInsert … вы запускаете запрос SELECT cf.*и для всех SELECT cf.*запускаешь SELECT * FROM User

Я думаю, проблема в том, что вы посылаете слишком много запросов на базу.

Я думаю, что вы должны объединить все ваши запросы на выборку только в одном большом запросе.

Для этого:

• заменить
  SELECT * FROM AdvanceBulkInsert по EXISTS IN (SELECT * FROM AdvanceBulkInsert where ...) или JOIN

• заменить SELECT * FROM User по NOT EXISTS IN(SELECT * from User WHERE )

Затем вы вызываете обновление для всех результатов объединенного выбора.

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

Редактировать:

Теперь я вижу ваш код:

Некоторые приводят:

• у вас есть индекс для cf.customTypeId, cfv.customFieldId, cfsa.customFieldId, пользователь. dateOfBirth, пользователь. firstName, user.lastName?

• вам не нужно делать LEFT JOIN CustomFieldSubArea, если у вас есть WHERE, который использует CustomFieldSubArea, достаточно простого JOIN CustomFieldSubArea.

• Вы будете часто запускать запрос 2 с relatedId = 0, может быть, вы можете сохранить результат в var?

• если вам не нужны отсортированные данные, удалите «ORDER BY cf.sortOrder, cf.label». Остальное, добавить индекс на cf.sortOrder, cf.label

Когда вам нужно выяснить, почему запрос занимает много времени, вам нужно проверить отдельные детали. Как вы показали в вопросе Объясните заявление может вам очень помочь Обычно наиболее важные столбцы:

• select_type — это всегда должен быть простой запрос / подзапрос. Связанные подзапросы доставляют много хлопот. К счастью, вы не используете
• возможные ключи — какие ключи этот выбор будет искать по
• rows — сколько строк-кандидатов определяется ключами / кэшем и другими методами. Чем меньше номер, тем лучше
• Дополнительно — «использование» говорит вам, как именно найдены строки, это самая полезная информация

Я бы опубликовал аналитику для 1-го и 3-го запросов, но они оба довольно простые. Вот разбивка для запроса, который доставляет вам неприятности:

EXPLAIN SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId,
cfv.relatedId, cfv.fieldValue, cfv.createdAt
FROM `CustomField` `cf`
INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId
LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId
and relatedId = 0
LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id
WHERE ((relatedTable = 'people' and enabled = '1')
AND (onCreate = '1'))
AND (cfsa.subarea='peoplebulkinsert')
ORDER BY cf.sortOrder, cf.label

• INNER JOIN CustomType ctyp on ctyp.id знак равно cf.customTypeId
• ЛЕВЫЙ НАРУЖНЫЙ РЕЙТИНГ CustomValue cfv on cf.id знак равно cfv.customFieldId
  а также relatedId = 0
• LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId знак равно cf.id
• ГДЕ ((relatedTable = «люди» и включен = ‘1’)
  А ТАКЖЕ (OnCreate = ‘1’))
  А ТАКЖЕ (cfsa.subarea= ‘Peoplebulkinsert’)
• СОРТИРОВАТЬ ПО cf.sortOrder, cf.label

Решение

Позвольте мне объяснить выше список. Смелый столбцы полностью должны иметь индекс. Присоединение таблиц — это дорогостоящая операция, которая в противном случае должна проходить через все строки обеих таблиц. Если вы создадите индекс для соединяемых столбцов, механизм БД найдет гораздо более быстрый и лучший способ сделать это. Это должно быть обычной практикой для любой базы данных

курсивный столбцы не обязательно должны иметь индекс, но если у вас большое количество строк (20 000 — большое количество), у вас также должен быть индекс для столбцов, которые вы используете для поиска, это может не иметь такого влияния на скорость обработки, но стоит лишнее время.

Таким образом, вам нужно добавить указатели в эти столбцы

• CustomType — идентификатор
• CustomField — customTypeId, id, relatedTable, включен, onCreate, sortOrder, метка
• CustomValue — customFieldId
• CustomFieldSubArea — customFieldId, подрайон

Чтобы проверить результаты, попробуйте снова выполнить оператор объяснения после добавления признаков (и, возможно, нескольких других запросов выбора / вставки / обновления). В дополнительном столбце должно быть написано что-то вроде «Using Index», а в столбце возможных_ключах должны быть указаны используемые ключи (даже два или более на запрос объединения).

Примечание: у вас есть некоторые опечатки в вашем коде, вы должны исправить их на тот случай, если кому-то тоже нужно будет работать с вашим кодом: reqruiteCount в качестве столбца таблицы и fileUplaod в качестве индекса массива в ссылочном коде.

Для моей работы я должен ежедневно добавлять один CSV с 524 столбцами и 10 тыс. Записей. Когда я попытался разобрать его и добавить запись с php, это было ужасно.

Итак, предлагаю вам ознакомиться с документацией о НАГРУЗКА ДАННЫХ ЛОКАЛЬНЫЙ ИНФИЛЬ

Например, я копирую / пропускаю свой собственный код, но адаптирую его к вашим потребностям

$dataload = 'LOAD DATA LOCAL INFILE "'.$filename.'"REPLACE
INTO TABLE '.$this->csvTable.' CHARACTER SET "utf8"FIELDS TERMINATED BY "\t"IGNORE 1 LINES
';

$result = (bool)$this->db->query($dataload);

Где $ filename — это локальный путь вашего CSV (вы можете использовать dirname(__FILE__) забудь это )

Эта команда SQL очень быстрая (всего 1 или 2 секунды для добавления / обновления всех CSV)

РЕДАКТИРОВАТЬ: прочитайте документацию, но, конечно, вам нужно иметь индекс uniq в вашей пользовательской таблице для «замены» работ. Таким образом, вам не нужно проверять, существует ли пользователь или нет. И вам не нужно анализировать файл CSV с php.

Похоже, у вас есть возможность (вероятность?) 3 запроса для каждой записи. Эти три запроса потребуют трех поездок в базу данных (и если вы используете yii для хранения записей в объектах yii, это может еще больше замедлить работу).

Можете ли вы добавить уникальный ключ на имя / фамилию / DOB и один на адрес электронной почты?

Если это так, вы можете просто сделать INSERT …. ON DUPLICATE KEY UPDATE. Это уменьшит его до одного запроса для каждой записи, что значительно ускорит процесс.

Но большим преимуществом этого синтаксиса является то, что вы можете вставлять / обновлять много записей одновременно (я обычно придерживаюсь около 250), так что даже меньше посещений базы данных.

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

Другой вариант — прочитать все во временную таблицу, а затем использовать ее в качестве источника для присоединения к вашей пользовательской таблице для выполнения обновлений / вставок. Это потребует немного усилий с индексами, но массовая загрузка во временную таблицу будет быстрой, а обновление с использованием полезных индексов будет быстрым. Использование его в качестве источника для вставок также должно быть быстрым (если исключить уже обновленные записи).

Другая проблема, кажется, ваш следующий запрос, но не уверен, где вы выполняете это. По-видимому, его нужно выполнить только один раз, и в этом случае это может не иметь большого значения. Вы не указали структуру таблицы CustomType, но она присоединена к полю CustomTypeId и не имеет индекса. Следовательно, это соединение будет медленным. Аналогично для объединений CustomValue и CustomFieldSubArea, которые объединяются на основе customFieldId и не имеют индексов для этого поля (надеюсь, уникальный индекс, так как если эти поля не уникальны, вы получите множество возвращаемых записей — 1 строка для каждой возможной комбинации)

SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId,
cfv.relatedId, cfv.fieldValue, cfv.createdAt
FROM `CustomField` `cf`
INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId
LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId
and relatedId = 0
LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id
WHERE ((relatedTable = 'people' and enabled = '1')
AND (onCreate = '1'))
AND (cfsa.subarea='peoplebulkinsert')
ORDER BY cf.sortOrder, cf.label

проследите за тем, чтобы вы могли попытаться уменьшить запрос и проверить с помощью онлайн-компилятора sql, проверить период времени, а затем включить его в проект.

Всегда делайте массовый импорт внутри перевода

        $transaction = Yii::app()->db->beginTransaction();
$curRow = 0;
try
{
while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
$curRow++;
//process $peopleData
//insert row
//best to use INSERT ... ON DUPLICATE  KEY UPDATE
// a = 1
// b = 2;
if ($curRow % 5000 == 0) {
$transaction->commit();
$transaction->beginTransaction();
}
}
catch (Exception $ex)
{
$transaction->rollBack();
$result = $e->getMessage();
}
//don't forget the remainder.
$transaction->commit();

Я видел, что импортные процедуры ускорились на 500%, просто используя эту технику. Я также видел процесс импорта, который выполнял 600 запросов (смесь выбора, вставки, обновления и отображения структуры таблицы) для каждый строка. Этот метод ускорил процесс на 30%.