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

09:35 pm - Размер медленной выборки и «последняя страница» (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>

<SCRIPT>total(...);</SCRIPT> 

    $dlist->push($r); // $r - новый элемент XML с очередной записью из выборки
    if ($dlsize>=$dlmax) // буфер заполнен
    {
      $dlist->shift();
    }
    else
    {
      $dlsize++; // $dlsize это размер буфера
    }
    

 
      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" );
    

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

Current Mood: satisfied