shestero (shestero) wrote,
shestero
shestero

  • Mood:

Размер медленной выборки и «последняя страница» (MySQL - PHP - браузер/клиент)

Рассмотрим простую страндартную задачу: данные из таблицы MySQL через PHP направляются в браузер (или иную программу-клиент). В браузере они отображаются в виде таблицы разбитой на «страницы» по 20, 50 или 100 записей на странице. То есть если записей много, пользователь видит только первые, скажем, двадцать, а ниже таблицы переключатель страниц: 1,2,3,..., последняя, называемый «paginator».

Эта задача весьма «заезжанная» и в ней нет ничего сложного, если трудоёмкость выборки из СУБД не большая. В этом случае как правило скрипты PHP принимают аргументом номер показываемой «страницы» N (по умолчанию - N=1) и размер страницы M из фиксированномго множества(20,50,100, а по умолчанию, скажем, M=20) сначала делают запрос SELECT COUNT(*) …. из таблицы с условиями выборки. Полученый размер выборки T делят на M, получая количество страниц C=M/T (приводя до целого числа в большую сторону), проверяют что N<=C, затем делают выборку записей для текущей «страницы» с помощью ...LIMIT (N-1)*M,M
Однако если таблица в MySQL очень большая полная выборка или подсчёт записей в выборке COUNT(*) может затянуться на продолжительное время. При этом, выдача полезных результатов в текущую страницу (особенно в первую) может происходить удовлетворительно быстро:
При не-буверизированном чтении выборки из MySQL записи могут приходить, например, каждые несколько десятков миллисекунд. Таким образом первая «страница», может быть загружена за приемлемое время порядка секунды, но если в выборке сотни тысяч записей и более, запрос COUNT(*), считающий записи, сам по себе может выполняться минуту и более!
Конечно, большого смысла показывать пользователю более нескольких тысяч записей нет. В случае если выборка очень большая, пользователю нужно увидеть просто фрагмент данных, что бы он мог на глаз проверить, что это вообще то что нужно.
Но иногда заказчик хочет, что бы пользователь всё же мог: (a) узнать общее количество записей в выборке; (b) перейти для просмотра на последнюю страницу выбоки.
Эти условия на мой взгляд, являются вполне обоснованными, т.к. могут реально помочь пользователю соориентироваться в его работе.
Мы не можем гарантировать выдачу результата за приемлемое для интерактивного взаимодействия «запрос-ответ» время, но можем показывать пользователю динамическую оценку результата требования (a).
Я имею в виду следующее: получив в течении секунд после запроса первую «страницу» и переключатель страниц: на 1-10 страницы, пользователь наблюдает бегущее увеличивающееся значение количества записей в выборке: «найденно не менее 200 записей...», «не менее 300...», «не менее 400...» итд. По мере этого может соответствующим образом расти и переключатель страниц. Пока не увидет окончательный результат «в выборке 12345 записей!», и появится возможность переключиться на последнюю «страницу».
Как это сделать?
Ни логика реляционных СУБД (в данном случае MySQL) ни логика WWW и HTTP не была изначально расчитана на такую работу.
MySQL не может информировать о состоянии счётчика COUNT(*), до тех пор, пока не подсчёт не будет полностью завершен, то есть задав такой запрос мы должны ждать пока он не выполнится, не дождавшись полного подсчёта мы не можем получить ответ на вопрос «ну а есть ли там хотя бы 100 записей?».
HTTP не был преспособлен для установки постоянных соединений, так как это делают TCP-socket-ы. Однако тут всё же проще: есть что называется «хак», можно просто не закрывать соединение после отработки основной части PHP (предварительно отключив тайм-аут выполнения скриптов!).
После завершающего </HTML> браузер продолжает воспринимать комманды вызова JavaScript-функции с данными в аргументах вида:
<SCRIPT>counter(200);</SCRIPT>
<SCRIPT>counter(300);</SCRIPT>
…
<SCRIPT>total(12345);</SCRIPT>
Естественно, заранее надо создать и загрузить в блоке HEAD эти JavaScript фунцкии counter(...) и total(...), которые будут утилизировать динамически поступающие значения (в данном случае текущая оценка размера выборки) должным образом (показывать их пользователю в должном месте, перестраивать переключатель «страниц» итд). Это не совсем «по-правилам», но это работает. После каждой такой посылки приходится посылать килобайтик пробелов и переводов строки (не считая уж комманды flush(); в PHP), т.к. полностью отключить кеширование по всей цепочке от серверного скрипта до обработчика JavaScript в браузерах не возможно.
При работе с MySQL, ради подсчёта COUNT(*) остаётся только после посылки нужных записей продолжать читать ненужные записи в холостую, ни куда не отправляя результаты, а только пересчитывая их в цикле и вызывая через каждые, скажем, 100 посчитанных записей вывод новой порции тегов «SCRIPT», а в конце -
<SCRIPT>total(...);</SCRIPT> 


В моей работе данные отправлялись не в браузер, а в специальную программу-клиент в формате XML, где разбирались по мере поступления SAX-парсером. С XML такой подход выглядит более элегантно. Я сразу грузил данные для всех «страниц», при их переключении нового запроса к серверу не создавалось. Мой более сложный алгорим всего этого таков:
Запускается небуферезированный цикл чтения выборки. Записи выдаются в клиент, в атрибуте тега XML записи указывается порядковый номер записи. Программма-клинет показывает пользователю записи по мере поступления, и он сразу может с ними работать.
Когда выведено заданное предельное кол-во страниц P, то есть M*P записей, они вместовыдачи в клиент начинают попадать в FIFO буфер $dlist = new SplDoublyLinkedList(); // Класс SplDoublyLinkedList из SPL есть в PHP начинаяя с версии 5.3.0
Этот буфер растёт до предела равного максимальному возможному размеру страницы M:

    $dlist
->push($r); // $r - новый элемент XML с очередной записью из выборки
    
if ($dlsize>=$dlmax// буфер заполнен
    
{
      
$dlist->shift();
    }
    else
    {
      
$dlsize++; // $dlsize это размер буфера
    
}
    
Как только выборка завершилась мы выдаём содержимое этого бувера по HTTP клиенту. В моём коде это выглядит так:
 
      fwrite
$f"<lastblock size=\"$dlsize\">\n" );
      
$dlist->setIteratorMode(SplDoublyLinkedList::IT_MODE_FIFO);
      for (
$dlist->rewind(); $dlist->valid(); $dlist->next()) {
        
fwrite$f$dlist->current() );
      }
      
fwrite$f"</lastblock>\n" );
    
Клиент может понять, что это последняя «страница», во-первых, по тегу lastblock либо, во-вторых, по разрыву в порядковых нумерах записей выборки.
Если же по истечению некоторого времени после начала «чистого счёта» или например, после пересчёта заданного количества записей «конца счёту не видно», запускается второй SQL-запрос COUNT(*), выполнение которого происходит параллельно. Т.к. в первом запросе используется не буфферезированное чтение обязательно надо делать второе соединение с MySQL. Хотя второй одновременно работающий в СУБД запрос тормозит первый, такой подход всё же показывает в среднем выйгрыш в скорости обслуживания.
Эти два условных «потока» выполнения начинают работать параллельно, и информация об общем колличестве записей и о содержании последней «страницы» в конце концов берётся из того потока, который выполнился быстрее (первый по любому поставляет «бегущую» информацию о размере выборки, не позволяя пользователя соскучиться).
В ходе второго «потока» после отработки первого SQL-зароса COUNT(*) запускается второй SQL-запрос с LIMIT-ом для получения записей последней «страницы». Отслеживание его выполнения происходит в цикле чтения основного первого «потока» с помощью упомянутой мной в предыдущем посте конструкции

        
// request with LIMIT is already sent
        
$ready $error $reject = array($mysqli2); // $mysqli2 — объект второго соединения.
        // или может так?: $ready[] = $error[] = $reject[] = $mysqli2;
        
mysqli_poll$ready,$error,$reject0,50000); // wait 1/20 sec
        
if (count($ready)>0)
        { 

    
После того как любой из двух условных потоков завершился происходит корректное закрытие XML, завершение PHP и передачи, и программа-клиент информаирует о том что приём данных, наконец, полностью завершён.
Tags: databases, it, mysql, php
Subscribe
Comments for this post were disabled by the author