Запрос MYSQL выполняется очень медленно
Я разработал модуль массовой загрузки пользователей. Есть 2 ситуации, когда я выполняю массовую загрузку 20 000 записей, когда в базе данных нет записей. Это займет около 5 часов. Но когда в базе данных уже есть около 30 000 записей, загрузка происходит очень и очень медленно. Загрузка 20 000 записей занимает около 11 часов. Я просто читаю CSV-файл с помощью метода fgetcsv
.
if (($handle = fopen($filePath, "r")) !== FALSE) {
while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
if (count($peopleData) == $fieldsCount) {
//inside i check if user already exist (firstName & lastName & DOB)
//if not, i check if email exist. if exist, update the records.
//other wise insert a new record.
}}}
Ниже приведены выполняемые запросы. (Я использую фреймворк Yii)
SELECT *
FROM `AdvanceBulkInsert` `t`
WHERE renameSource='24851_bulk_people_2016-02-25_LE CARVALHO 1.zip.csv'
LIMIT 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
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
Если существует, обновите пользователь:
UPDATE `User` SET `id`='51394', `address1`='49 GRANDE RUE',
`mobile`='', `name`=NULL, `firstName`='Franck',
`lastName`='ALLEGAERT ', `username`=NULL,
`password`=NULL, `email`=NULL, `gender`=0,
`zip`='60310', `countryCode`='DZ',
`joinedDate`='2016-02-23 10:44:18',
`signUpDate`='0000-00-00 00:00:00',
`supporterDate`='2016-02-25 13:26:37', `userType`=3,
`signup`=0, `isSysUser`=0, `dateOfBirth`='1971-07-29',
`reqruiteCount`=0, `keywords`='70,71,72,73,74,75',
`delStatus`=0, `city`='AMY', `isUnsubEmail`=0,
`isManual`=1, `isSignupConfirmed`=0, `profImage`=NULL,
`totalDonations`=NULL, `isMcContact`=NULL,
`emailStatus`=NULL, `notes`=NULL,
`addressInvalidatedAt`=NULL,
`createdAt`='2016-02-23 10:44:18',
`updatedAt`='2016-02-25 13:26:37', `longLat`=NULL
WHERE `User`.`id`='51394'
Если пользователь не существует, вставьте новую запись.
Тип движка таблицы - MYISAM. Только столбец электронной почты имеет индекс.
Как я могу оптимизировать это, чтобы сократить время обработки?
Запрос 2 занял 0,4701 секунды, что означает, что для 30 000 записей потребуется 14103 секунды, что составляет около 235 минут. около 6 часов.
Обновление
CREATE TABLE IF NOT EXISTS `User` (
`id` bigint(20) NOT NULL,
`address1` text COLLATE utf8_unicode_ci,
`mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '1 - female, 2-male, 0 - unknown',
`zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`joinedDate` datetime DEFAULT NULL,
`signUpDate` datetime NOT NULL COMMENT 'User signed up date',
`supporterDate` datetime NOT NULL COMMENT 'Date which user get supporter',
`userType` tinyint(2) NOT NULL,
`signup` tinyint(2) NOT NULL DEFAULT '0' COMMENT 'whether user followed signup process 1 - signup, 0 - not signup',
`isSysUser` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 - system user, 0 - not a system user',
`dateOfBirth` date DEFAULT NULL COMMENT 'User date of birth',
`reqruiteCount` int(11) DEFAULT '0' COMMENT 'User count that he has reqruited',
`keywords` text COLLATE utf8_unicode_ci COMMENT 'Kewords',
`delStatus` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0 - active, 1 - deleted',
`city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`isUnsubEmail` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 - ok, 1 - Unsubscribed form email',
`isManual` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 - ok, 1 - Manualy add',
`longLat` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Longitude and Latitude',
`isSignupConfirmed` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Whether user has confirmed signup ',
`profImage` tinytext COLLATE utf8_unicode_ci COMMENT 'Profile image name or URL',
`totalDonations` float DEFAULT NULL COMMENT 'Total donations made by the user',
`isMcContact` tinyint(1) DEFAULT NULL COMMENT '1 - Mailchimp contact',
`emailStatus` tinyint(2) DEFAULT NULL COMMENT '1-bounced, 2-blocked',
`notes` text COLLATE utf8_unicode_ci,
`addressInvalidatedAt` datetime DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `AdvanceBulkInsert` (
`id` int(11) NOT NULL,
`source` varchar(256) NOT NULL,
`renameSource` varchar(256) DEFAULT NULL,
`countryCode` varchar(3) NOT NULL,
`userType` tinyint(2) NOT NULL,
`size` varchar(128) NOT NULL,
`errors` varchar(512) NOT NULL,
`status` char(1) NOT NULL COMMENT '1:Queued, 2:In Progress, 3:Error, 4:Finished, 5:Cancel',
`createdAt` datetime NOT NULL,
`createdBy` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `CustomField` (
`id` int(11) NOT NULL,
`customTypeId` int(11) NOT NULL,
`fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`defaultValue` text COLLATE utf8_unicode_ci,
`sortOrder` int(11) NOT NULL DEFAULT '0',
`enabled` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
`listItemTag` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`required` char(1) COLLATE utf8_unicode_ci DEFAULT '0',
`onCreate` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
`onEdit` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
`onView` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
`listValues` text COLLATE utf8_unicode_ci,
`label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`htmlOptions` text COLLATE utf8_unicode_ci
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `CustomFieldSubArea` (
`id` int(11) NOT NULL,
`customFieldId` int(11) NOT NULL,
`subarea` varchar(256) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `CustomValue` (
`id` int(11) NOT NULL,
`customFieldId` int(11) NOT NULL,
`relatedId` int(11) NOT NULL,
`fieldValue` text COLLATE utf8_unicode_ci,
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM AUTO_INCREMENT=86866 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Весь PHP-код здесь http://pastie.org/10737962
Обновление 2
Объясните вывод запроса
8 answers
Индексы - ваш друг.
UPDATE User ... WHERE 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
в таблицу. Прочитайте мою кулинарную книгу по построению индексов.
Попробуйте выполнить следующие действия, чтобы повысить производительность запросов:
- определите индексацию в структуре базы данных и получите только те столбцы, которые вам нужны.
- Не используйте * в запросе выбора.
- И не ставьте идентификаторы в кавычки, как
User.id='51394'
, вместо этого делайтеUser.id= 51394
. - Если вы указываете идентификаторы в кавычках, то ваша индексация не будет работать. Такой подход повышает производительность ваших запросов на 20% быстрее.
- Если вы используете
ENGINE=MyISAM
, то вы не можете определить индексацию между вашими таблица базы данных, измените компонент database engine на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 )
Затем вы вызываете обновите все результаты объединенного выбора.
Вы также должны по очереди выполнять свой запрос, чтобы определить, какой из этих запросов занимает больше всего времени, и вы также должны использовать ANALYSE , чтобы определить, какая часть запроса занимает время.
Редактировать:
Теперь я вижу ваш код:
Некоторые зацепки:
У вас есть индекс для cf.customtypeid, cfv.customfieldid, cfsa.customfieldid, пользователя. Дата рождения, пользователь. Имя, пользователь. фамилия ?
Вам не нужно выполнять ЛЕВОЕ СОЕДИНЕНИЕ с областью пользовательских полей, если у вас есть место, где вы используете область пользовательских полей, достаточно простого СОЕДИНЕНИЯ с областью пользовательских полей.
Вы будете запускать запрос 2 много раз с relatedId = 0, возможно, вы сможете сохранить результат в var?
Если вам не нужны отсортированные данные, удалите "ЗАКАЗ ПО cf.сортировщику, cf.метке". В противном случае добавьте индекс в cf.сортировщик, cf.метка
Когда вам нужно выяснить, почему запрос занимает много времени, вам нужно проверить отдельные детали. Как вы показали в вопросе , Объяснение утверждения может вам очень помочь. Обычно наиболее важными столбцами являются:
- select_type - это всегда должен быть простой запрос/подзапрос. Связанные с этим подзапросы доставляют много хлопот. К счастью, вы не используете никаких
- возможные ключи - По каким ключам этот выбор будет искать
- строки - сколько строк-кандидатов определяется ключи/кэш и другие методы. Чем меньше число, тем лучше
- Дополнительно - "использование" говорит вам, как именно найдены строки, это самая полезная информация
Анализ запросов
Я бы опубликовал аналитику для 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
- ВНУТРЕННИЙ пользовательский тип СОЕДИНЕНИЯ ctyp на ctyp.id = см. customtypeid
- ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Пользовательское значение cfv на cf.id = cfv.Пользовательское поле и связанный идентификатор = 0
- СЛЕВА ПРИСОЕДИНИТЬСЯ к пользовательскому полю cfsa на cfsa.customfieldid = cf.id
- ГДЕ ((Связанная таблица = "люди" и включено = '1') И (Создать = '1')) И (cfsa.подобласть ='peoplebulkinsert')
- ПОРЯДОК ПО ср.сортировщик , ср. метка
Решение
Позвольте мне объяснить приведенный выше список. Жирный шрифт столбцы полностью должны иметь индекс. Объединение таблиц - дорогостоящая операция, которая в противном случае должна проходить через все строки обеих таблиц. Если вы сделаете индекс по соединяемым столбцам, механизм БД найдет гораздо более быстрый и лучший способ сделать это. Это должно быть обычной практикой для любой базы данных
Столбцы курсивом не обязательно должны иметь индекс, но если у вас большое количество строки (20 000 - это большое количество) у вас также должен быть индекс по столбцам, которые вы используете для поиска, это может не оказать такого влияния на скорость обработки, но стоит дополнительного времени.
Поэтому вам нужно добавить индексы в эти столбцы
- Идентификатор пользовательского типа
- Пользовательское поле - пользовательский идентификатор, идентификатор, связанная таблица, включено, onCreate, сортировщик, метка
- Пользовательское значение - идентификатор пользовательского поля
- Область настраиваемых полей - идентификатор настраиваемого поля, область
Для проверки результаты попробуйте снова запустить инструкцию explain после добавления указателей (и, возможно, нескольких других запросов select/insert/update). В дополнительном столбце должно быть написано что-то вроде "Использование индекса", а в столбце possible_keys должен быть список используемых ключей (даже два или более на запрос соединения).
Примечание: В вашем коде есть некоторые опечатки, вы должны исправить их на случай, если кому-то еще тоже понадобится поработать над вашим кодом: "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 запроса для каждой отдельной записи. Для этих 3 запросов потребуется 3 обращения к базе данных (и если вы используете yii для хранения записей в объектах yii, это может еще больше замедлить работу).
Можете ли вы добавить уникальный ключ на имя/фамилию /DOB и один на адрес электронной почты?
Если это так, то вы можете просто ВСТАВИТЬ....ПРИ ОБНОВЛЕНИИ ДУБЛИКАТА КЛЮЧА. Это позволило бы свести его к одному запросу для каждой записи, значительно ускоряя процесс.
Но большим преимуществом этого синтаксиса является то, что вы можете вставлять/обновлять сразу много записей (обычно я придерживаюсь около 250), поэтому еще меньше обращений к базе данных.
Вы можете создать класс, в который вы просто передаете записи и который вставляет, когда количество записей соответствует вашему выбору. Также добавьте вызов для вставки записей в деструктор, чтобы вставить любые окончательные записи.
Другой вариант - прочитать все во временной таблице, а затем использовать это как источник для присоединения к вашей пользовательской таблице для выполнения обновлений/вставки. Для этого потребуется немного усилий с индексами, но массовая загрузка во временную таблицу выполняется быстро, и обновление с помощью полезных индексов будет быстрым. Использование его в качестве источника для вставок также должно быть быстрым (если вы исключаете уже обновленные записи).
Другой проблемой, по-видимому, является ваш следующий запрос, но вы не уверены, где вы его выполняете. Похоже, что его нужно выполнить только один раз, и в этом случае он возможно, это не имеет большого значения. Вы не указали структуру таблицы 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
Смотрите, чтобы вы могли попытаться сократить запрос и проверить с помощью компилятора sql online, проверьте период времени, который затем включите в проект.
Всегда выполняйте массовый импорт в рамках транзакции
$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%.