Как ускорить процесс выполнения запроса в БД?

Question

[Сергей Манько]

Привет всем! В таблице есть почти 500к записей. Всего три столбца: id, url, data.
В столбце data хранится json. Каждый день выполняется запрос, который создает массив из тоже примерно 500к записей и потом циклом я проверяю есть ли в бд запись с таким url и если есть, то присваивает полю data единицу, если такого урл нет в базе, то создает новую запись.
Это всё работает ну очень долго, в районе 2х часов.
Прикрепляю код:

foreach ($data as $key => $item) {
	$res = $mysqli->query("SELECT * FROM `name` WHERE url = '".$item['url']."'");
	if($res->num_rows){
		$mysqli->query("UPDATE `name` SET data = '1' WHERE url = '".$item['url']."'");
	} else{
		$mysqli->query("INSERT INTO `name` (url, data) VALUES ('".$item['url']."','".$item['price']."')");
	}
}

Answer 1

[Илья Караваев]

Какой тип таблиц используете?
Если InnoDB, то при частых обновления и вставках, в больших таблицах, вы всегда будете получать подобный результат. Самый простой способ в этом случае ускорить, перенести таблицу в MyISAM.

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

И третье. Обновляйте по id, зачем же вы ищете, потом опять ищете для обновления по url. Строчку вы уже получили, можно так считать. Используйте сразу id. И вообще запрос упростите, пишите SELECT `id` FROM... Другие данные Вам не нужны. И ускорите получение и ускорите обновление.

Кстати. Четвертое. Если вы все равно всем записям меняете поле data на 1, можно так же обновление сделать пачкой. Собирайте id найденных записей и обновляйте через UPDATE `name` SET `data` = '1' WHERE `id` IN (1,2,5,7,88,123).

Comments

[Сергей Манько]

Тип таблицы MyISAM

[Илья Караваев]

Сергей Манько: обновил в пятый раз пост... Попробуйте остальные рекомендации. Пачками обновлять и добавлять значительно экономичнее и быстрее.

[Сергей Манько]

Илья Караваев: Спасибо! Буду пробовать.

[Илья Караваев]

Сергей Манько: Чем больше думаю, тем больше советов. Вы можете еще использовать подготовленные выражения. Запрос в этом случае отправляется на сервер БД один раз и потом передаются только данные. Что тоже быстрее. И надеюсь у Вас есть индекс по полю url?

[Сергей Манько]

Илья Караваев: В этом и проблема. Без понятия как создавать эти индексы. Гуглю, но не могу разобраться.

[Илья Караваев]

Сергей Манько: давайте подумаем, как это лучше сделать. Индекс может быть построен для поля в 255 символов. Если бы у Вас ссылки помещались в это лимит, вы бы могли сделать просто:

CREATE INDEX inx_url ON table(url);

Если же поле больше, то тут уже спорный вопрос. В целом, индекс можно строить и по text полю. Я лично никогда по столь большим полям не строил индексы, по опыту подсказать не могу. Пробуйте команду выше. Если что, в нее можно добавить CREATE FULLTEXT INDEX...

[Сергей Манько]

Илья Караваев: у меня теперь появилось поле offer_id. Там тип поля Bigint. Может можно для него индекс создать и поиск по нему буду запускать

Answer 2

[RidgeA]

Какая длинна значений url?
Можно сделать уникальный индекс по полю url и потом одним запросом обновить все
https://dev.mysql.com/doc/refman/5.7/en/insert-on-...
Только про json я не понял

Comments

[Сергей Манько]

я ошибся, там не json, просто склеивается дата и цена. Длина поля урл - 300

Answer 3

[Therapyx]

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

Answer 4

[Сергей Манько]

Спасибо всем. Создал индексы, добавил поле offer_id, которое гораздо короче, чем урл и буду пробовать!

Answer 5

[AJ]

Дополнительно перейдите на PDO, используйте PREPARED_STATEMENTS, отключите буферизацию и эмуляцию подготовленных запросов в PDO. PHP очень сильно выжирает ресурсы по эмуляции. А с подготовленными запросами MySQL не будет каждый раз парсить сам запрос. Достаточно биндить значения и отправлять.

Answer 6

[Антон Антон]

Не может 500к записей в таких запросах работать больше 2 часов. при 500 к записей даже при тэйбл скан это будет в диапазоне минут (размер строки не более пол килобайта, соответственно, 250 мегабайт на таблицу, что примерно 10-30 секунд на 5400 хдд на запрос селект и апдейт). Ну, или машина совсем старая или виртуалка, ужатая по ресурсам.
В данном случае посоветую сделать таблицу с индексом по хэшу урл (для ограничения длины колонки с индексом) и проверять его. В зависимости от бизнес логики может быть можно сократить запрос до insert .. on duplicate key update