Как лучше сравнить базу данных и текстовый файл (артикул-цена, больше 2000 строк)?

Question

[billybons2006]

Я очень сори за простецкий вопрос, но сформулировать точнее не смог: как правильнее (чтобы не перегружать хостинг) сравнить цены из текстового файла (2000-3000 строк) и из базы данных, основываясь на артикуле?

Ну, считал я из файла артикулы и цены в массив.

$data[0][acticle]
$data[0][price]

А дальше как сравнить базу данных и полученный массив?

Не запускать же для каждого артикула из файла новый SELECT типа:

foreach ($data as $key) {
	// само собой, ниже просто идея, а не запрос
	SELECT * FROM table WHERE article LIKE %$key['article']%
	...
	
	//Ну и дальше что надо - цену обновить, например или не обновлять, в зависимости от условий...
	
	if цена_из_бд > $key['price']
		UPDATE table SET price=$key['price'] WHERE article=$key['article']
}

Или только так и иначе никак? Это ж 2000-3000 запросов надо сделать. Бред, вроде бы.

Я немного боюсь, не напряжно ли это для хостинга да и вообще, может, это в корне неверная идея?

Вроде сейчас 2000 строк это ерунда, но ведь их (строк) может стать 10000 через год...

Answer 1

[FanatPHP]

Несколько вещей чисто для информации, чтобы ты немного начал ориентироваться в окружающем пространстве. Потому что, как все новички, ты думаешь совершенно не о том.
1. 2000 строк - это не база, а песочница с куличиками. И 10 - тоже. О нагрузках можно начинать думать начиная с сотен тысяч.
2. САМЫЙ ад в твоих рассуждениях - это LIKE, который ты считаешь штатным средством поиска по артикулу. Вот с такими идеями даже и меленькую базу можно поставить колом.
3. Стандартный вариант работы в таких случаях - читаешь файл в массив, чтобы артикулы были ключами. Дальше запрашиваешь все строки из БД, и читая по одной, ищешь артикул в массиве.
4. Непонятно, зачем в текущей формулировке вообще доставать и сравнивать. Почему нельзя просто в цикле проапдейтить 2000 строк, указывая условия в запросе?
5. Почему у тебя в вопросе ничего нет про удаленные/добавленные строки? Если за этим следить не надо, то задача и вовсе выеденного яйца не стоит. Или ты об том пока не думал?

Comments

[billybons2006]

1. Понято.
2. Понято.
3. Понято.
4 и 5 - именно об этом мне предстоит еще думать, что буду апдейтить, а что - нет. Вопрос был, скорее, академического плана, т.к. насиловать в пустую базу и память, не зная, к примеру, что есть тупо функция для этого, не хотелось.
5. в точку ))

[billybons2006]

Я даже склонен думать, что пункт 5 у вас и п. 3 у Антона Корзунова (ниже) и есть то, что мне надо, хотя вопрос я задал не в ту степь немного.

[FanatPHP]

Ну вот да, в этом беда с академическими вопросами. К примеру, те прайсы, которые обрабатываю я, содержат ссылки на картинки. И их одним SQL запросом уже никак. А так-то да, если логика умещается в SQL, то ON DUPLICATE подходящее решение.

[billybons2006]

Ну у меня прайсы вообще такое содержат, что... Я их сначала из excel удаляю не нужные столбцы (оставляю только артикул, цену и название), потом в xml (кривовато, правда), потом регулярками чищу в notepad++, привожу к виду типа:

Art28736Test prod1261.33

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

Но что могу сказать - даже моему мелкому хостингу 2-5 массивов по 2000 шт. в каждом - пофигу вообще. Я, честно говоря, даже не ожидал. Даже после всего, что здесь писали.

[billybons2006]

billybons2006: порезал мне вставку кода. Art28736Test prod1261.33 - это строка типа "Скобка - Art28736 Скобка закрывается Скобка открывается ну и так далее. В общем, все просто. Правда, именно как xml глючно парсится файл. Но это пока не вопрос, а проект вопроса )) там с кодировками мог напутать...

Answer 2

[Дмитрий Скогорев]

ну, вы же не ежеминутно будете это делать?
не напряжно это и вполне нормально

Answer 3

[Дмитрий Евграфович]

php.ru/manual/ref.array.html

PHP, конечно, много памяти жрет для массивов, но не столько, чтобы вытаскивать по одной строке из базы с целью экономии памяти. Возьмите все строки из базы за один запрос, преобразуйте в нужный массив, обработайте его, затем обновите базу вторым запросом. Когда количество элементов в массиве превысит сто тысяч, почитайте эту статью habrahabr.ru/post/141093 и погуглите, есть довольно интересные советы про оптимизацию по этому поводу... хотя после всех них единственным разумным вариантом кажется скрипт на питоне. Но это уже когда в массивах будет под миллион элементов.

Comments

[FanatPHP]

преобразовывать БД в массив нинужно

[Дмитрий Евграфович]

FanatPHP: не всю бд, а лишь нужные параметры. зачем делать 4000 запросов, когда можно сделать только 2?

[FanatPHP]

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

Answer 4

[Антон Корзунов]

Даже если такой запрос будет выполнять 5 минут - это совершенно нормально для операции которая выполняется раз в день.
Улучшить ситуацию можно через хранимки или начать использовать https://php.net/manual/ru/mysqli.multi-query.php
С другой стороны у вас совершенно "упушен" момент про индексы, и условия в первом и втором запросе различаются.
Что можно сделать чтобы было "совсем хорошо"
1. Установить тип поля артикуля в char[точный размер]. Вообще "fixed" таблицы, где размер строки всегда можно заранее посчитать работают лучше.
2. Добавить UNIQUE индекс на артикуль. Он же уникальный?
3. Идем по "новым записям" и "INSERT INTO table(...)... ON DUPLICATE KEY UPDATE price=newprice. В общем инсертим строки, но если такая есть - обновляем.
3.1 Если новых строк нет вообще - делаем только апдейты
3.2 Если есть - заводим еще одно поле - "dirty". В начале ставим в 0, в инсертах и апдейтах ставим в 1. После окончания работы строк, которые не обновлялись" будет 0.
4. Можно еще вспомнить, что любые строковые индексы работают плохо, как бы вы их не спрашивали. Можно добавить еще одно поле crc=CRC32(article), которая переведет строку в число.
4.1 Итого UNIQUE вешается на два поля - crc(первое), article
4.2 В WHERE выражениях можно делать WHERE crc=CRC32(?) and article=? - это будет работать очень сильно быстрее и без коллизий.

А вообще можно особо и не париться, например обновлять данные "в лоб" на локальном компе, а потом переливать дампы.

Comments

[FanatPHP]

Очередной диванный теоретик. В один ответ вложил весь свой запас умных слов.

[Антон Корзунов]

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

[FanatPHP]

диванного теоретика очень легко отличить по тому, что он никогда не рассуждает в приложении к конкретному вопросу и никогда не объясняет конкретный механизм действия. Совершая только невнятные пассы руками, типа "наука умеет много гитик", "унутре у ней неонка", "вынос логики в хранимки - панацея". Накой, нахрен логики? "Если у Джона 5 яблок, а у Мэри - три"? Ты ЭТУ логику собрался в хранимки выносить? Чем тебе асинхронные запросы помогут? Ты хоть раз в жизни мультиквери запускал? Зачем рассуждать о вещах, которых ты никогда в жизни своими глазами не видел, а только слышал краем уха? Лучше бы вместо этого вопрос прочитал внимательно. Хранимка асинхронная.

[Антон Корзунов]

FanatPHP: Размер дивана, значит, не измерен.

[FanatPHP]

Кто бы сомневался. Размерами меряйся с друзьями в детском саду, дружочек.

[FanatPHP]

1 - диванная теория, 2 - норм, единственный полезный совет во всем ответе. 3 можно, но не принципиально. 4 - диванный ад, кромешный :) Все что ты сделал - это УВЕЛИЧИЛ размер индекса, не прибавив ничего к скорости :)

[Антон Корзунов]

Я уже понял кто из нас кто. Если не верите в локальность данных и ветвление (1 и 4 пункт) и опыта не хватает, чтобы хотя бы знать (а не понимать почему) - www.slideshare.net/alexclear/c-35881279 (29-30 слайд).
PS: ТС - не слушайте фанатика, он не конструктивен.

[FanatPHP]

Про таких говорят "слышал звон, да не знает где он" :) Твоя проблема в том, что ты никогда не занимался такими вещами сам, а случайно услышал краем уха, когда пацаны базарили. Если ты делаешь облегченный индекс, то основной индекс надо снимать :) По облегченному мы находим 2-5 строк, для выбора между которыми не нужен индекс. То есть, alexclear (чувак, в отличие от тебя, действительно умный) сократил размер индекса, с чего и получил ускорение. В то время как ты размер индекса увеличил. Давно я так не смеялся :)

[Антон Корзунов]

Основной индекс снимать не надо, так как он, по правилам игры, обеспечивает уникальность ключа. Но, так как сам по себе никому не нужен - почему бы ему не побыть частью составного.
А составной - это по сути 1D ключ, где поиск в начале происходит по первой части, а потом по второй. В данном случае первая часть, по природе своей, очень быстро "сходится", а вторая содержит одну или больше записей.
PS: Я уже все понял про ваше воспитание, ведете себя как (анонимный) гопник. Жаль что мы не представлены.

[billybons2006]

По поводу п. 3 - спасибо за идею.

[FanatPHP]

возможно, в постгре это и так, но в мускуле составной - это тупо конкатенация двух ключей, и никакого "поиска по первому" не происходит. Если хотим сохранить уникальный ключ, то надо делать облегченный не в составе составного, а отдельным индексом, и форсить его использование. Учитывая, что в презентации явно написано, что размер ключа упал с 18 до 8, там тоже составной ключ не используется, а "поиск по первому" - твои фантазии.

[Антон Корзунов]

FanatPHP: в отличие от Чистякова я активнее использую MySQL. И два ключа "конкатенировать" невозможно - просто попробуйте представить эту операцию. Это дерево первого ключа, которое "продолжается" деревом второго (точнее узкой нужной выборкой).
Именно по этому вторая часть не доступна без обхода первой.
Именно по этому не бывает эффективных ключей X,Y и им пришлось придумывать spatial решения и другие UB варианты.

[FanatPHP]

Ты не понял принцип, на котором была решена проблема. Если бы делалось так как ты говоришь, то размер индекса увеличился бы, а не уменьшился. Плюс, в любом случае, у парня не 200000000 записей, а 2000, и твои советы - исключительно попытка попонтоваться, без малейшей практической пользы. Но в целом второй вариант ответа чуть лучше чем первый, который был совсем ад.

[Антон Корзунов]

FanatPHP: На сим завершим. Общаться с такими как вы(сэр) бесполезно. Но все еще жаль, что совершенно не понятно с кем именно.

[FanatPHP]

"На сём", мой юный друг, на "на сём". Общаться с такими как я полезно. Как минимум я заставил тебя переписать первоначальный ответ, который был вообще ни о чём. Плюс заставил перечитать вопрос целиком, а не только заголовок. Плюс научил думать перед тем, как строчить ответ, и не делать голословных бессмысленных заявлений. Кругом одна польза.

[Антон Корзунов]

FanatPHP: В злых полицейских обычно чуть больше риторического ехидства. Но оно есть только в последнем сообщении.