Ответ 1

Индексы - ваш друг.

UPDATE User ... WHERE id = ... - Отчаянно нужен индекс для ID, возможно PRIMARY KEY.

Аналогично для renameSource.

SELECT * 
FROM `User` `t` 
WHERE `t`.`firstName`='Franck' 
  AND `t`.`lastName`='ALLEGAERT ' 
  AND `t`.`dateOfBirth`='1971-07-29' 
  AND (userType NOT IN ("1")) 
LIMIT 1;

Требуется INDEX(firstName, lastName, dateOfBirth); поля могут быть в любом порядке (в этом случае).

Посмотрите на каждый запрос, чтобы узнать, что ему нужно, затем добавьте в таблицу INDEX. Прочитайте мою Поваренную книгу по созданию индексов.

Ответ 2

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

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

Ответ 3

Если я понимаю, для всего результата 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 )

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

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

Edit:

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

Некоторое количество свинца:

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

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

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

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

Ответ 4

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

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

Анализ запросов

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

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
• LEFT OUTER JOIN CustomValue cfv on cf.id= cfv.customFieldId              и relatedId = 0
• LEFT JOIN CustomFieldSubArea cfsa на cfsa.customFieldId= cf.id
• WHERE ((relatedTable = 'people' и enabled = '1') AND (onCreate = '1')) И (cfsa.subarea = 'peoplebulkinsert')
• ORDER BY cf.sortOrder, cf.label

Решение

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

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

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

• CustomType - id
• CustomField - customTypeId, id, relatedTable, enabled, onCreate, sortOrder, label
• CustomValue - customFieldId
• CustomFieldSubArea - customFieldId, подрайон

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

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

Ответ 5

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

Итак, я предлагаю вам посмотреть документацию о LOAD DATA LOCAL INFILE

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

$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)

EDIT: прочитайте документ, но, конечно, вам нужно иметь индекс uniq в вашей таблице пользователей для работы "replace". Таким образом, вам не нужно проверять, существует ли пользователь или нет. И вам не нужно разбирать CSV файл с помощью php.

Ответ 6

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

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

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

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

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

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

Другая проблема - это ваш следующий запрос, но не знаете, где вы это выполняете. Кажется, что его нужно выполнить только один раз, и в этом случае это может не иметь особого значения. Вы не указали структуру таблицы CustomType, но она присоединена к Customfield, а поле 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

Ответ 7

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

Ответ 8

Всегда выполняйте массовый импорт в течение транзакции

        $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%.