Есть большая таблица. Как выбрать из нее все данные в цикле, пачками по 10к записей?

Question

[Jodes]

Понятно, что нужно юзать LIMIT и OFFSET, но я не пойму как сдвигать OFFSET?
Поясните принцип или ткните в пример кода.
Спасибо.

Answer 1

[Евгений]

<?php
$limit = 10000;
$offset = 0;
do {
$data = $db->query("SELECT * FROM `tbl` ORDER BY `id` ASC LIMIT $limit OFFSET $offset")->fetchAll();
$offset += count($data); 
}
while(count($data));

Comments

[FanatPHP]

Не надо делать глупости.

[Евгений]

FanatPHP: Ага и ловить потуги типа
mysql server has gone away, allowed memory size of xxxx bytes exhausted (tried to allocate yyy bytes)

[FanatPHP]

С какого перепугу там будет memory size?

[Евгений]

FanatPHP: когда ты в переменную веберь хуиллиард записей.
Да и вообще разработчики того же yii2 и сфинкса глупости тоже делают?
www.yiiframework.com/doc-2.0/guide-db-active-recor...
sphinxsearch.com/docs/current.html#conf-sql-range-step

[FanatPHP]

10 тысяч в одну переменную тоже много. Выбирать надо одну.

[Евгений]

FanatPHP: ну представь что у тебя оперативки 8гб, а данных в таблице 100гб, как ты ее всю сразу выберешь?

[FanatPHP]

Еще раз повторую. Выбирать надо одну запись. И никакая память не переполнится. Я понимаю, что жертвам ОРМ-ов трудно это понять, но ты уж попробуй осилить

[Евгений]

FanatPHP: я понимаю что с базой в 5 мегабайт ты это не поймешь

[Jodes]

FanatPHP: Жертва ORM? Выбирать одну запись? Зачем мне одна, если мне нужна вся таблица?

[Евгений]

Jodes: Обычно такие задачи стоят ТОЛЬКО перед сервис-скриптами. Ну например, надо базу текстов песен переложить на эластик поиск, для этого хочешь не хочешь придется вытащить все записи, и тащить порциями зачастую единственный вариант.
Но если вся таблица с овер 10к записями тащится через орм, то это уже нездоровая фигня.

[FanatPHP]

У тебя ОРМ головного мозга. Чтобы переложить что-то куда-то, не нужно сначала вытаскивать все записи.

[Jodes]

Nc_Soft: Это как раз и есть сервис-скрипт, задача которого взять данные из одной базы и унести в другую. Данных много, sql-файлами не выйдет, ибо разные СУБД.
Спасибо за пример, то что нужно.
Вы часом "родом" не с небезызвестного конструктора мобильных сайтов? Сильно уж ник похож :)

[Jodes]

FanatPHP: Укажите более другой метод, если он вам известен.

[Евгений]

Jodes: речь про веник? ))

[FanatPHP]

Любой вменяемый сервис скрипт трати памяти ровно столько, сколько занимает ОДНА строка в базе. Десятки лет тысячи программистов работают с базами данных, объем которых в миллионы раз превышает разумный объем памяти, необходимый скрипту для работы. И только для пользователей несчастного похапе такая задача становится неодолимым препятствием, которое приходится натужно, скрипя извилинами, преодолевать, изобретая уродливые велосипеды.

[Jodes]

Nc_Soft: Именно :)

[Jodes]

FanatPHP: Ну т.е. вы предлагаете нагнуть СУБД миллионом запросов в цикле таща записи по одной и ждать пока код отработает до второго пришествия Христа?

[FanatPHP]

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

[Jodes]

FanatPHP: Приведите пример.

[FanatPHP]

Jodes: весь инернет засран классическим примером while ($row = $res->fetch()) { процессим строку }. Если ты мне объяснишь, где этот код умудрится сожрать памяти больше, чем занимает одна строка, я с удовольсвтием послушаю

[Jodes]

FanatPHP: Простите, а вы видели видели время выполнения этого, так сказать, примера?

[Jodes]

FanatPHP: И да, что по вашему сей код делает?

[FanatPHP]

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

[Jodes]

FanatPHP: Еще одно уточнение, как по вашему выглядит SQL-запрос до fetch?

[Jodes]

FanatPHP: И если вы скажете что это 'SELECT field FROM tbl WHERE ...', то у меня для вас плохие новости.

[FanatPHP]

Jodes: с удовольствием послушаю.

[Jodes]

FanatPHP: А что тут слушать? Это преусловутая выборка по одной записи в цикле. Чего я и пытаюсь избежать.

[Евгений]

Jodes: он говорит про запрос select * from tbl, и через fetch() извлекать по одной записи

[FanatPHP]

Ты совершенно не читаешь, что тебе пишут. Про запрос в цикле я тебе уже писал. WHERE кстати в твоем запросе лишнее. Но это все детали. Ты почему-то главного никак понять не можешь. при том что это главное составляет основу основ баз данных. Запрошенные данные любое API читает по одной строке. И чтобы превысить совершенно мизерный объем памяти, надо обладать ну совсем кривыми руками.

[Jodes]

Nc_Soft: И ожидать до китайской пасхи? Этого, как и поедания памяти я и хотел избежать.

[Jodes]

FanatPHP: Уважаемый, where там не лишнее от слова "совсем".
Выборка построчно циклом, как в вашем примере, это не выход. Абсолютно не выход.
За сим давайте закончим.

[Евгений]

FanatPHP: Вот ты меня озадачил. Я наконец понял что ты имел в виду так называемый datareader, но у меня не получается реализовать на pdo, не подскажешь как?
вот вариант "в лоб" grab.by/D6Ge
вот покумекал с курсором grab.by/D6Hu
ну а вот вариант с батчами по 10к grab.by/D6IQ
в таблице около 230к записей

[Jodes]

Nc_Soft: Думаю он имел ввиду как раз то, что по ссылке grab.by/D6Ge

[FanatPHP]

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

[FanatPHP]

Jodes: ты же < a href=Есть большая таблица. Как выбрать из нее все данные в цикле, пачками по 10к записей?>сам писал, что нужна вся таблица, а теперь отказываешься. Впрочем, наличие where ничего не меняет.

[Евгений]

FanatPHP: так работает, спасибо

[Jodes]

FanatPHP: Да, писал, извиняюсь.

[FanatPHP]

Jodes: ахахахахах! Вот это-то и есть самое смешное :) Как будто твой способ с запросами в цикле хоть чем-то отличается от "китайской пасхи". Тебе реально надо понять буквально две базовые вещи про базы данных: выборка всегда идет по одной строке - и неавжно, сколько строк ты запросил. Выборка с лимитом в цикле выбирает одни и те же данные столько раз, сколько оборотов в цикле.

[Jodes]

FanatPHP: Но как бы вам не смешно было, выборка с лимитом отработала за 3 минуты. А ваш вариант работает вот уже как 20 минут.
И вот это уже действительно смешно.

[Jodes]

FanatPHP: Вы можете и дальше продолжать втолковывать так называемые "прописные истины", но факт остается фактом.

[Евгений]

Jodes: это быстрее выполняется чем с лимитами
<?php
$db = new PDO("mysql:host=localhost;dbname=dwl", 'root', 'root');
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$q = $db->prepare("SELECT * FROM files ORDER BY id_file ASC");
$q->execute();
while ($row = $q->fetch()) {
echo "$row[id_file] \n";
}

[FanatPHP]

Jodes: ты пойми, что ты сейчас споришь не со мной, а со своими неверными представлениями. Твои запросы в цикле будут точно так же выбирать по 1 записи. База данных так работает, она по-другому не умеет. Поэтому повторяя запросы в цикле ты сделаешь что угодно, но только не ускоришь свои выборки. Чтобы сделать лимит 1000000, 10000, база должна сначала выбрать 100000 записей, и только потом начать отдавать тебе данные. Чем больше у тебя значение оффсета, тем дольше у тебя будет работать скрипт. Поэтому я и смеялся над твоими словами про китайскую пасху. Не потому что я такой злобный карлик, а потому что все ровно наоборот. Даже если одним запросом долго, то в цикле будет еще дольше

[Jodes]

FanatPHP: Супер. А факты куда девать?

[FanatPHP]

Jodes: факты надо уметь интерпретировать. И отличать факты от неверных наблюдений. К примеру многие начинающие пользователи похапе искренне считают фактом то, что у них "база данных переводы строк съела".

[Jodes]

FanatPHP: За сим закончим или будем продолжать спор слепого с глухим?

[Jodes]

Nc_Soft: Протестировал. 2 ядра по 92% загрузки, 121 метр памяти. работает уже 6 минут.
М.б. у меня хост такой особенный, что я в упор не вижу ожидаемого прироста производительности?

[Евгений]

Jodes: а что там делается? только селект?

[Jodes]

Nc_Soft: Да

[Евгений]

если
while ($row=$q->fetch()) {
//какая-то долгая операция со строкой
}
то разница будет не сильно заметна

[Jodes]

Nc_Soft: Ладно, Б-г с ним, еще раз спасибо за код. На сегодня закончим.

[Евгений]

FanatPHP: с этим способом есть проблемы...
Например, если мне надо процессить таблицу и заносить туда данные что я обработал строчку.
Такой вариант совсем не канает (LIMIT 1000 стоит чтобы долго не ждать)
<?php
$db = new PDO("mysql:host=localhost;dbname=dwl", 'root', 'root');
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$q = $db->prepare("SELECT * FROM files ORDER BY id_file ASC LIMIT 1000");
$q->execute();
while ($row = $q->fetch()) {
echo "$row[id_file] \n";
$db->prepare('update files set `time` = NOW() where id_file = ? ')->execute([$row['id_file']]);
}
?>
данные не обновляются, потому что как оказалось при unbuffered query нельзя выполнять другие запросы в этом же коннекте.
А вариант с отдельным коннектом $db1
<?php
$db = new PDO("mysql:host=localhost;dbname=dwl", 'root', 'root');
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$q = $db->prepare("SELECT * FROM files ORDER BY id_file ASC LIMIT 1000");
$q->execute();
$db1 = new PDO("mysql:host=localhost;dbname=dwl", 'root', 'root');
while ($row = $q->fetch()) {
echo "$row[id_file] \n";
$db->prepare('update files set `time` = NOW() where id_file = ? ')->execute([$row['id_file']]);
}
?>
привносит какой-то коллапс, видимо табла лочится, запросы ставятся в очередь, там обновилось всего около сотки строчек и выдало ошибку
Warning: Empty row packet body in /Users/evgenij/projects/snip/fetch.php on line 8
PHP Warning: Empty row packet body in /Users/evgenij/projects/snip/fetch.php on line 8

[Евгений]

там в цикле $db1->prepare('update files set `time` = NOW() where id_file = ? ')->execute([$row['id_file']]);
и оно висит наверное лок таймаут ждет
grab.by/D6RU

Answer 2

[FanatPHP]

Не нужно.
Сколько надо данных - столько и запрашивай, все разом, безо всяких лимитов.

Comments

[Jodes]

У вас видимо оперативная память безлимитная?

[Анатолий Кирсанов]

Jodes: имел печальный опыт, мало памяти - увеличивайте память. В противном случае будете иметь проблемы с изменениями данных между запросами пачек на посещаемом сайте. При добавлении новых строк будете считывать одни и теже в соседних пачках. При удалении - некоторые вообще не увидите.

[FanatPHP]

Jodes: а при чем здесь память? На что ты собрался ее расходовать?

[FanatPHP]

Анатолий Кирсанов: да что за бред? Все прямо как сговорились про память причитать. КУДА ОНА У ВАС УХОДИТ? Едите вы ее, что ли?

[Jodes]

FanatPHP: Вам выдать тестовых данных для экспериментов?

[Jodes]

Анатолий Кирсанов: Спасибо. Это не сильно критично в моем случае, т.к. проект "консольный".

[FanatPHP]

Jodes: не надо. Я имею опыт работы с базами данных отличающийся от опыта среднего пользователя похапе. И даже имею некоторую теоретическую подготовку, согласно которой работа с БД большого объема не представляет каких-то особенных требований в плане потребляемой оперативаной памяти.

[Jodes]

FanatPHP: Ну так наставьте несведущих на путь истиный, чего зря холиварить то?

[Анатолий Кирсанов]

FanatPHP: смотря как работать. Если человек написал, что его беспокоит память, значит так оно и есть. А съесть ее можно всегда ... Будут основания для изменения алгоритма - изменит сам.

[FanatPHP]

Jodes: Так я наставляю уже битый час. Не надо никаких лимитов. Сделай один запрос и обрабатывай свои данные спокойно. Управишься на порядок быстрее, чем заставляя базы выбирать одни и те же данные 100500 раз

[Jodes]

FanatPHP: Нужна вообще вся таблица. Не одна гипотетическая запись, а вообще вся. Какой запрос вы предлагаете?

[Анатолий Кирсанов]

Jodes: человек старается объяснить, что для перекладывания записей из одного места в другое не нужно хранить всю пачку единовременно. Вы же о своем алгоритме пишете так, что не ясно зачем ОБЯЗАТЕЛЬНО так делать. Вот и идет "дискуссия" ни о чем. Пачка нужна, если есть какой то алгоритм помимо перекладывания, требующий рассмотрения нескольких записей и это нельзя сделать в один проход (при том самом пресловутом переборе по одной записи). Это что касается PHP.
А на стороне СУБД болячку не обойти - там будет строиться результирующий набор и сожрет он столько памяти, сколько будет выбрано строк.
На стороне PHP можно только не увеличить вдвое требуемый объем памяти.
Чего тогда спор - вот вопрос ...

[Jodes]

Анатолий Кирсанов: Вы тоже уверены в том что вышеупомянтутый в треде "fetch" в цикле сумеет перебрать всю таблицу употребив минимум времени и памяти?

[Анатолий Кирсанов]

Jodes: ответ зависит от блокировок.
Если таблицу не блокировать, то разницы для PHP никакой, что все сразу выбрать, что пачки перебирать.
А вот для СУБД разница есть определенно. Сложность запроса одна и таже, СУБД вынужден делать выборку каждый раз полностью и из нее вычленять диапазон.
Все эти нарезания нужны только если если особый алгоритм (какой - трудно представить, нужно знать задачу), либо идет борьба с блокировками (но там могут быть проблемы, я уже написал о них).

[FanatPHP]

Анатолий Кирсанов: вообще-то можно использовать небуферизованный запрос, и тогда на стороне БД тоже никакого расхода памяти не будет

[Анатолий Кирсанов]

FanatPHP: за инфо спасибо (правда, сходу в mysqi не нашел, есть только в mysql). Тогда свой код в PHP может удвоить или утроить потребность в памяти. Если говорить о MySQL, то он в своей памяти все равно хранит результат и он зависит от количества строк. Так что фокусы в PHP решают не все, нужно понимать что и из какой таблицы берешь ...

[FanatPHP]

Анатолий Кирсанов: php.net/manual/ru/mysqlinfo.concepts.buffering.php

[Анатолий Кирсанов]

FanatPHP: это я видел (сейчас узрел, что примеры по mysqli там есть), уговорить сам MySQL не делать буферизацию это не поможет. Так что вопрос о памяти имеет право на жизнь.

Answer 3

[Jodes]

Хотелось бы оставить это здесь.
Всем спасибо за ответы.

Comments

[Анатолий Кирсанов]

Как бы умно и верно, но все равно не уместно. Кому надо - вытащит и из этого нужное.