Пагинация на PHP для большого количества записей

10.06.2021 в 15:49
206
+2

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

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

Чтобы обозначить проблемы, давайте напишем скрипт, который добавит в нашу базу 1 млн статей.

create_many_articles.php

<?php

spl_autoload_register(function (string $className) {
    require_once __DIR__ . '/src/' . str_replace('\\', '/', $className) . '.php';
});

$db = \MyProject\Services\Db::getInstance();

$query = '';
for ($i = 1; $i <= 1000000; $i++) {
    $query .= sprintf(
        'INSERT INTO articles (`author_id`, `name`, `text`) VALUES (1, \'%s\', \'%s\');',
        'Статья #' . $i,
        'Текст статьи ' . $i
    );

    if ($i % 1000 === 0) {
        $db->query($query);
        $query = '';
        echo $i . PHP_EOL;
    }
}

Запускаем его командой:

php create_many_articles.php

И идём пить кофе. После завершения работы скрипта заходим на главную страницу нашего блога http://myproject.loc/

Как видим, у нас появилось довольно большое число страниц, которое просто разрывает наш шаблончик. Не знаю как у вас, а у меня вкладка в браузере просто-напросто зависла.

Давайте сделаем пагинацию в стиле туда-сюда. Для этого в экшене \MyProject\Controllers\MainController::page() передадим в шаблон 2 ссылки на предыдущую и следующую страницы.

public function page(int $pageNum)
{
    $pagesCount = Article::getPagesCount(5);
    $this->view->renderHtml('main/main.php', [
        'articles' => Article::getPage($pageNum, 5),
        'previousPageLink' => $pageNum > 1
            ? '/' . ($pageNum - 1)
            : null,
        'nextPageLink' => $pageNum < $pagesCount
            ? '/' . ($pageNum + 1)
            : null
    ]);
}

А в шаблоне выведем эти ссылки.

myproject.loc/templates/main/main.php

<div style="text-align: center">
    <?php if ($previousPageLink !== null): ?>
        <a href="<?= $previousPageLink ?>">&lt; Туда</a>
    <?php else: ?>
        <span style="color: grey">&lt; Туда</span>
    <?php endif; ?>
    &nbsp;&nbsp;&nbsp;
    <?php if ($nextPageLink !== null): ?>
        <a href="<?= $nextPageLink ?>">Сюда &gt;</a>
    <?php else: ?>
        <span style="color: grey">Сюда &gt;</span>
    <?php endif; ?>
</div>

Любуемся аккуратным пагинатором.

Давайте теперь посмотрим на время генерации страницы. Для этого обернем код в index.php в следующие строки:

<?php
$startTime = microtime(true);

// тут весь остальной код

printf('<div style="text-align: center; padding: 5px">Время генерации страницы: %f</div>', $endTime - $startTime );

Обновим первую страницу блога. У меня время генерации заняло 0.258032 секунды.

А теперь давайте откроем последнюю страницу блога - http://myproject.loc/200000
Время генерации страницы: 1.045724
Вот это да! Разница почти в 4 раза!

Давайте посмотрим, какие запросы у нас выполняются во время загрузки страницы. Для этого в коде \MyProject\Services\Db::query добавим var_dump:

public function query(string $sql, array $params = [], string $className = 'stdClass'): ?array
{
    var_dump($sql);

Обновим страничку и увидим, что у нас выполнилось 2 запроса:

Так как при загрузке первой страницы запрос:

SELECT COUNT(*) AS cnt FROM articles; 

остаётся неизменным, мы понимаем, что в случае последней страницы работу существенно замедляет второй запрос:

SELECT * FROM `articles` ORDER BY id DESC LIMIT 5 OFFSET 999995;

В случае загрузки первой страницы он будет выглядеть следующим образом:

SELECT * FROM `articles` ORDER BY id DESC LIMIT 5 OFFSET 0;

Давайте сделаем ANALYZE (я использую mariadb, для mysql будет EXPLAIN ANALYZE) для обоих запросов.

Как видим, в случае первой страницы базке пришлось пройти 5 строк (r_rows), а в случае последней страницы 1000000 строк! Дело в том, что OFFSET N работает таким образом, что он вычитывает строки, подходящие под условие запроса до тех пор, пока не дойдёт до N-ой строки. Всё что до N будет пропущено, а далее будут выгружены строки после N-ой, до необходимого значения LIMIT. То есть, к примеру, в нашем случае при получении 200й страницы блога база будет прогонять при запросе следующие строки:

Id -> 1000000
Id -> 999999
…// Тут ещё очень много строк
Id -> 6
Id -> 5 // Все строки, что выше, пропускаем. Их там 999995. Начиная с этой выгребаем ещё X строк, где X – значение, указанное в LIMIT (в нашем случае = 5)
Id -> 4
Id -> 3
Id -> 2
Id -> 1 // Выгрузили 5 строк, на этом всё

Как видим, базе пришлось сделать много лишней работы. Так уж работает OFFSET. Какие есть альтернативы? Давайте для начала посмотрим на предпоследнюю страницу блога.
http://myproject.loc/199999

Как мы можем получить следующие 5 записей, чтобы сформировать следующую страницу?
Зная, что текущая страница заканчивается на id = 6, нам можно получить следующие 5 записей, сформировав следующий запрос:

SELECT * FROM `articles` WHERE id < 6 ORDER BY id DESC LIMIT 5;

Как видим, результаты выполнения запросов совпадают. Однако, время их выполнения существенно отличается. Давайте проанализируем запрос.

ANALYZE SELECT * FROM `articles` WHERE id < 6 ORDER BY id DESC LIMIT 5;

Как мы видим, теперь базе пришлось считать всего 5 строк из таблицы. Это произошло благодаря использованию индекса по полю id. Мы просто отфильтровали ненужные нам строки и начиная с нужной получили первые 5.
Теперь давайте напишем запрос, который позволит получить записи с предыдущей страницы. Для этого нам нужно получить 5 статей, которые идут до первой статьи на текущей странице. В нашем случае это записи с id > 10:

SELECT * FROM `articles` WHERE id > 10 ORDER BY id ASC LIMIT 5;

Как видим, запрос тоже выполнился быстро. Давайте проанализируем и его.

Видим r_rows = 5, всё в порядке.
Однако, как вы могли заметить, записи отсортированы не в том порядке, в котором они должны выдаваться в блоге. Давайте исправим это.

SELECT * FROM (SELECT * FROM `articles` WHERE id > 10 ORDER BY id ASC LIMIT 5) as articles ORDER BY id DESC;

Теперь всё в порядке. Это можно сделать и средствами PHP, вызвав функцию array_reverse() после получения результатов из базы. Как вы это сделаете – не так важно, это дешевая операция, относительно той, что была, когда мы работали через OFFSET.

Что-ж, остаётся только запрограммировать логику. Вместо url-ов вида myproject.loc/777 нам нужно сделать 2 поддерживаемых урла типа:

  • myproject.loc/before/777
  • myproject.loc/after/777

для получения страницы статей до id=777 и после id=777, соответственно.

Добавляем в контроллер 2 новых экшена:

\MyProject\Controllers\MainController

public function before(int $id)
{

}

public function after(int $id)
{

}

и соответствующие им роуты:

myproject.loc/src/routes.php

'~^before/(\d+)$~' => [\MyProject\Controllers\MainController::class, 'before'],
'~^after/(\d+)$~' => [\MyProject\Controllers\MainController::class, 'after'],

Добавим в сущность статьи метод для получения статей для страницы до заданного id:

\MyProject\Models\Articles\Article

/**
 * @return Article[]
 */
public static function getPageBefore(int $id, int $limit): array
{
    $db = Db::getInstance();
    $sql = sprintf('SELECT * FROM (SELECT * FROM '.self::getTableName().' WHERE id > :id ORDER BY id ASC LIMIT %d) as articles ORDER BY id DESC;', $limit);
    return $db->query($sql, ['id' => $id], self::class);
}

И аналогичный метод для получения статей после заданного id:

/**
 * @return Article[]
 */
public static function getPageAfter(int $id, int $limit): array
{
    $db = Db::getInstance();
    $sql = sprintf('SELECT * FROM '.self::getTableName().' WHERE id < :id ORDER BY id DESC LIMIT %d;', $limit);
    return $db->query($sql, ['id' => $id], self::class);
}

Помимо этого, нам также понадобятся ещё 2 метода, которые позволят узнать, есть ли после какого-то (или до какого-то) id ещё записи. Это потребуется, чтобы узнать, есть ли следующая или предыдущая страница, чтобы нарисовать ссылки.

public static function hasNextPage(int $pageLastId): bool
{
    $db = Db::getInstance();
    $sql = 'SELECT id FROM '.self::getTableName().' WHERE id < :id LIMIT 1;';
    $result = $db->query($sql, ['id' => $pageLastId]);
    return !empty($result); 
}   

public static function hasPreviousPage(int $pageFirstId): bool
{
    $db = Db::getInstance();
    $sql = 'SELECT id FROM '.self::getTableName().' WHERE id > :id LIMIT 1;';
    $result = $db->query($sql, ['id' => $pageFirstId]);
    return !empty($result); 
}

Осталось написать логику в экшенах контроллера.

public function before(int $id)
{
    $this->page(Article::getPageBefore($id, 5));
}

public function after(int $id)
{
    $this->page(Article::getPageAfter($id, 5));
}

private function page(array $articles)
{
    if ($articles === []) {
        throw new NotFoundException();
    }

    $firstID = $articles[0]->getId();
    $lastID = $articles[count($articles)-1]->getId();

    $this->view->renderHtml('main/main.php', [
        'articles' => $articles,
        'previousPageLink' => Article::hasPreviousPage($firstID) ? '/before/' . $firstID : null,
        'nextPageLink' => Article::hasNextPage($lastID) ? '/after/' . $lastID : null,
    ]);
}

Пробуем зайти на страницу, на которой будут записи до id=20: http://myproject.loc/before/20

Обратите внимание на время генерации страницы. 0.017035 секунды! То есть произошло ускорение в 1,045724/0,017035 = 61,4 раза! Согласитесь, неплохо!

Осталось сделать главную страницу, которая будет рисоваться при запросе http://myproject.loc/
Для этого нам нужно получить id последней статьи, прибавить к нему 1, и вызвать экшен after() с полученным значением.
Добавим в сущность метод для получения последнего id.

\MyProject\Models\Articles\Article

public static function getLastID(): ?int
{
    $db = Db::getInstance();
    $sql = 'SELECT id FROM '.self::getTableName().' ORDER BY id DESC LIMIT 1;';
    $result = $db->query($sql);
    return !empty($result) ? $result[0]->id : null;
}

И дополним наш контроллер:

public function main()
{
    $lastID = Article::getLastID();
    if ($lastID === null) {
        throw new NotFoundException();
    }
    $this->after($lastID + 1);
}

Проверяем главную страницу http://myproject.loc/

Всё в порядке. Страница сгенерировалась за 0.010365.

Код, расположенный в сущности Article легко выносится в класс ActiveRecordEntity, и масштабируется на все другие возможные модели.

Дифф с изменениями вы найдете здесь - https://github.com/ivashkevitch/oop-v-php-prodvinutyj-kurs/pull/2/files

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

\MyProject\Controllers\MainController

private function page(array $articles)
{
        ...
        'nextPageLink' => $lastID > 1 ? '/after/' . $lastID : null,
        ...
}

А ещё можно закешировать id последней добавленной статьи, и избежать еще одного запроса в базу. Я сделал файловый кеш прямо в контроллере. Так делать не надо :) Это просто для быстрой демонстрации. Выносите такое куда-нибудь на уровень доменного сервиса. Там же при добавлении новых статей сбрасывайте/обновляйте кеш.

    ...
    $this->view->renderHtml('main/main.php', [
        'articles' => $articles,
        'previousPageLink' => $firstID < $this->getLastArticleID() ? '/before/' . $firstID : null,
        'nextPageLink' => $lastID > 1 ? '/after/' . $lastID : null,
    ]);
}

private function getLastArticleID(): int
{
    $cacheFile = __DIR__ . '/../../../cache/last_article_id';
    $value_from_cache = file_get_contents($cacheFile);
    if (!empty($value_from_cache)) {
        return (int)$value_from_cache;
    }

    $lastID = Article::getLastID();
    file_put_contents($cacheFile, $lastID);
    return $lastID;
}

После этого остаётся единственный запрос в базу, а время генерации страницы уменьшается до 0.005457 секунды.

На этом всё, спасибо что дочитали :)

loader
10.06.2021 в 15:49
206
+2
Комментарии
Новый комментарий

Логические задачи с собеседований