Курсоры БД в Doctrine +29



image


Используя курсоры, вы сможете порционно получить из БД и обработать большое количество данных, не расходуя при этом память приложения. Уверен, перед каждым веб-разработчиком хотя бы раз вставала подобная задача, передо мной тоже — и не раз. В этой статье я расскажу, в каких задачах курсоры могут быть полезны, и дам готовый код по работе с ними из PHP + Doctrine на примере PostrgeSQL.


Проблема


Давайте представим, что у нас есть проект на PHP, большой и тяжелый. Наверняка, он написан у нас с помощью какого-нибудь фреймворка. Например, Symfony. Еще в нем используется база данных, например, PostgreSQL, а в базе данных есть табличка на 2 000 000 записей с информацией о заказах. А сам проект — это интерфейс к этим заказам, который умеет их отображать и фильтровать. И, позвольте заметить, справляется с этим весьма неплохо.


Теперь нас попросили (вас еще не просили? Обязательно попросят) сделать выгрузку результата фильтрации заказов в Excel-файл. Давайте на скорую руку добавим кнопку со значком таблицы, которая будет выплевывать пользователю файл с заказами.


Как обычно решают, и чем это плохо?


Как делает программист, которому еще не встречалась такая задача? Он делает SELECT в базу, вычитывает результаты запроса, конвертирует ответ в Excel-файл и отдает его в браузер пользователя. Задача работает, тестирование пройдено, но в продакшине начинаются проблемы.


Наверняка, у нас для PHP установлено ограничение памяти в какой-нибудь разумный (спорно) 1 Гб на процесс, и как только эти 2 млн строк перестают помещаться в этот гигабайт, все ломается. PHP падает с ошибкой “закончилась память”, а пользователи жалуются, что файл не выгружается. Происходит это потому, что мы выбрали довольно наивный способ выгрузить данные из базы — они все сначала перекладываются из памяти базы (и диска под ней) в оперативную память процесса PHP, потом обрабатываются и выгружаются в бразуер.


Чтобы данные всегда помещались в память, нужно брать их из базы по кусочкам. Например, 10 000 записей вычитали, обработали, записали в файл, и так много раз.


Хорошо, думает программист, которому наша задача встретилась в первый раз. Тогда я сделаю цикл и выкачаю результаты запроса кусками, указывая LIMIT и OFFSET. Срабатывает, но это очень дорогие операции для базы, и поэтому выгрузка отчета начинает занимать не 30 секунд, а 30 минут (еще не так уж и плохо!). Кстати, если кроме OFFSET в этот момент программисту ничего больше в голову не приходит, то вот еще много способов добиться того же, не насилуя базу данных.


При этом у самой БД есть встроенная возможность поточно вычитывать из нее данные — курсоры.


Курсоры


Курсор — это указатель на строку в результатах выполнения запроса, который живет в базе. При их использовании, мы можем выполнить SELECT не в режиме немедленного выкачивания данных, а открыть курсор с этим селектом. Далее мы начинаем получать из БД поток данных по мере продвижения курсора вперед. Это дает нам тот же результат: мы вычитываем данные порционно, но база не делает одну и ту же работу по поиску строки, с которой ей нужно начать, как в случае с OFFSET.


Курсор открывается только внутри транзакции и живет до тех, пока транзакция жива (есть исключение, смотрите WITH HOLD). Это значит, что если мы медленно вычитываем много данных из базы, то у нас будет долгая транзакция. Это иногда плохо, надо понимать и принимать этот риск.


Курсоры в Doctrine


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


BEGIN;

DECLARE mycursor1 CURSOR FOR (
    SELECT * FROM huge_table
);

DECLARE создает и открывает курсор для заданного запроса SELECT. После создания курсора, из него можно начать читать данные:


FETCH FORWARD 10000 FROM mycursor1;
<получили 10 000 строк>

FETCH FORWARD 10000 FROM mycursor1;
<получили еще 10 000 строк>
...

И так далее, пока FETCH не возвратит пустой список. Это будет означать, что проскроллили до конца.


COMMIT;

Набросаем класс, совместимый с Doctrine, который будет инкапсулировать работу с курсором. И, чтобы за 20% времени решить 80% проблемы, работать он будет только с Native Queries. Так его и назовем, PgSqlNativeQueryCursor.


Конструктор:


public function __construct(NativeQuery $query)
{
    $this->query = $query;
    $this->connection = $query->getEntityManager()->getConnection();
    $this->cursorName = uniqid('cursor_');

    assert($this->connection->getDriver() instanceof PDOPgSqlDriver);
}

Здесь же я генерирую имя для будущего курсора.


Так как в классе есть SQL-код, специфичный для PostgreSQL, то лучше поставить проверку на то, что наш драйвер — это именно PG.


От класса нам нужно три вещи:


  1. Уметь открывать курсор.
  2. Уметь возвращать нам данные.
  3. Уметь закрывать курсор.

Открываем курсор:


public function openCursor()
{
    if ($this->connection->getTransactionNestingLevel() === 0) {
        throw new \BadMethodCallException('Cursor must be used inside a transaction');
    }

    $query = clone $this->query;
    $query->setSQL(sprintf(
        'DECLARE %s CURSOR FOR (%s)',
        $this->connection->quoteIdentifier($this->cursorName),
        $this->query->getSQL()
    ));
    $query->execute($this->query->getParameters());

    $this->isOpen = true;
}

Как я и говорил, курсоры открываются в транзакции. Поэтому здесь я проверяю, что мы не забыли разместить вызов этого метода внутри уже открытой транзакции. (Слава богу, прошло то время, когда меня потянуло бы открыть транзакцию прямо здесь!)


Чтобы упростить себе задачу по созданию и инициализации нового NativeQuery, я просто клонирую тот, что был скормлен в конструктор, и оборачиваю его в DECLARE … CURSOR FOR (здесь_оригинальный_запрос). Выполняю его.


Сделаем метод getFetchQuery. Он будет возвращать не данные, а еще один запрос, который можно использовать как угодно, чтобы получить искомые данные заданными пачками. Это дает вызывающему коду больше свободы.


public function getFetchQuery(int $count = 1): NativeQuery
{
    $query = clone $this->query;
    $query->setParameters([]);

    $query->setSQL(sprintf(
        'FETCH FORWARD %d FROM %s',
        $count,
        $this->connection->quoteIdentifier($this->cursorName)
    ));

    return $query;
}

У метода один параметр — это размер пачки, который станет частью запроса, возвращаемого этим методом. Применяю тот же трюк с клонированием запроса, затираю в нем параметры и заменяю SQL на конструкцию FETCH … FROM …;.


Чтобы не забыть открыть курсор перед первым вызовом getFetchQuery() (вдруг я буду не выспавшийся), я сделаю неявное его открытие прямо в методе getFetchQuery():


public function getFetchQuery(int $count = 1): NativeQuery
{
    if (!$this->isOpen) {
        $this->openCursor();
    }
…

А сам метод openCursor() сделаю private. Вообще не вижу кейсов, когда его нужно вызывать явно.


Внутри getFetchQuery() я захардкодил FORWARD для движения курсора вперед на заданное количество строк. Но режимов вызова FETCH много разных. Давайте их тоже добавим?


const DIRECTION_NEXT         = 'NEXT';
const DIRECTION_PRIOR        = 'PRIOR';
const DIRECTION_FIRST        = 'FIRST';
const DIRECTION_LAST         = 'LAST';
const DIRECTION_ABSOLUTE     = 'ABSOLUTE'; // with count
const DIRECTION_RELATIVE     = 'RELATIVE'; // with count
const DIRECTION_FORWARD      = 'FORWARD'; // with count
const DIRECTION_FORWARD_ALL  = 'FORWARD ALL';
const DIRECTION_BACKWARD     = 'BACKWARD'; // with count
const DIRECTION_BACKWARD_ALL = 'BACKWARD ALL';

Половина из них принимают кол-во строк в параметре, а другая половина — нет. Вот, что у меня получилось:


public function getFetchQuery(int $count = 1, string $direction = self::DIRECTION_FORWARD): NativeQuery
{
    if (!$this->isOpen) {
        $this->openCursor();
    }

    $query = clone $this->query;
    $query->setParameters([]);
    if (
        $direction == self::DIRECTION_ABSOLUTE
        || $direction == self::DIRECTION_RELATIVE
        || $direction == self::DIRECTION_FORWARD
        || $direction == self::DIRECTION_BACKWARD
    ) {
        $query->setSQL(sprintf(
            'FETCH %s %d FROM %s',
            $direction,
            $count,
            $this->connection->quoteIdentifier($this->cursorName)
        ));
    } else {
        $query->setSQL(sprintf(
            'FETCH %s FROM %s',
            $direction,
            $this->connection->quoteIdentifier($this->cursorName)
        ));
    }

    return $query;
}

Закрываем курсор с помощью CLOSE, не обязательно дожидаться завершения транзакции:


public function close()
{
    if (!$this->isOpen) {
        return;
    }

    $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName));
    $this->isOpen = false;
}

Деструктор:


public function __destruct()
{
    if ($this->isOpen) {
        $this->close();
    }
}

Вот весь класс полностью. Попробуем в действии?


Я открываю какой-нибудь условный Writer в какой-нибудь условный XLSX.


$writer->openToFile($targetFile);

Здесь я получаю NativeQuery на вытаскивание списка заказов из базы.


/** @var NativeQuery $query */
$query = $this->getOrdersRepository($em)
   ->getOrdersFiltered($dateFrom, $dateTo, $filters);

На основе этого запроса я объявляю курсор.


$cursor = new PgSqlNativeQueryCursor($query);

И для него получаю запрос на получение данных пачками по 10000 строк.


$fetchQuery = $cursor->getFetchQuery(10000);

Итерирую, пока не получу пустой результат. В каждой итерации выполняю FETCH, обрабатываю результат и записываю в файл.


do {
   $result = $fetchQuery->getArrayResult();
   foreach ($result as $row) {
       $writer->addRow($this->toXlsxRow($row));
   }
} while ($result);

Закрываю курсор и Writer.


$cursor->close();
$writer->close();

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


ОТЧЕТ ГОТОВ! Мы использовали константное количество памяти из PHP при обработке всех данных и не замучали базу чередой тяжелых запросов. А сама выгрузка заняла по времени незначительно больше, чем потребовалось базе на выполнение запроса.


Посмотрите, нет ли в ваших проектах мест, которые можно ускорить/сэкономить память при помощи курсора?

Вы можете помочь и перевести немного средств на развитие сайта



Комментарии (19):

  1. m0rtis
    /#20269284

    $writer->addRow($this->toXlsxRow($row));

    Предполагается, что этот метод пишет сразу на диск, а не накапливает строки где-то в памяти для последующей записи, я правильно понимаю?


    Ну и это… Хорошо бы все же иметь возможность расширябельности на другие СУБД стандартными средствами Доктрины.


    А так — отличная идея, приятная реализация. Если бы мог — обязательно влепил бы плюсик:))

    • livinbelievin
      /#20269360

      Спасибо за отзыв!

      > Предполагается, что этот метод пишет сразу на диск, а не накапливает строки где-то в памяти для последующей записи, я правильно понимаю?

      Да, это очень важно, чтобы при записи файла тоже не было буферизации. Как вот здесь, например.

      • m0rtis
        /#20269408

        Какая интересная библиотека! Надо будет присмотреться к ней. Большое спасибо за наводку!

  2. mokhovcom
    /#20269460 / +1

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

    • livinbelievin
      /#20269640 / +1

      Штатные средства — это выкачать в память и перебрать? Норм, если у вас много ненужной памяти.

      Про дополнительную нагрузку на базу с удовольствием бы почитал. Дадите ссылку?

      • mokhovcom
        /#20269790

        к примеру:
        www.php.net/manual/ru/pdostatement.fetch.php
        извлекает одну строку, а не все данные.

        результат работы запроса (что с курсорами, что без них) храниться на сервере базы данных, какие при этом выбирать средства для извлечения данных сугубо дело каждого, но открывать ещё и курсор на результат выборки это уже через чур (уж точно не экономичнее обычного извлечения)

        • livinbelievin
          /#20269918 / +1

          Чтобы сделать fetch() в PDO, данные уже должны быть в памяти приложения. Именно так PDO работает по умолчанию. То есть, fetch() извлекает одну строку не из сервера, а из собственного буфера.

          Можно перед fetch() сделать prepare() с атрибутом ATTR_CURSOR, тогда будет использоваться серверный курсор (ему и посвящена статья), и только тогда данные действительно будут выкачиваться с сервера строка-за-строкой.

  3. cross_join
    /#20269506

    Метод серверных курсоров также описан в книге "СУБД для программиста" в сравнении с другими способами пакетного извлечения данных из больших таблиц (глава «Постраничые выборки»).

  4. EvgeniiR
    /#20269524

    А чем не устраивал просто PDO для выгрузки данных для отчётов?

  5. livinbelievin
    /#20270274

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

    www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/batch-processing.html#iterating-large-results-for-data-processing

    • livinbelievin
      /#20270278

      Там есть существенная разница.

      Какие шаги предпринимает Doctrine, чтобы достать из базы, например, 5000 объектов заказа?

      1. Делает запрос в базу
      2. Выкачивает 5000 строк
      3. Делает Hydration — то есть раскладывание сырых данных в объекты (или массивы, есть разные режимы гидрации)
      4. Отдает тебе массив из 5000 объектов заказа

      Какой шаг позволяет оптимизировать использование iterate()? Только третий — гидрацию. Он позволяет не гидрировать 5000 объектов за раз, а делать это один за другим. Да, это дает значительную экономию памяти. Но Doctrine при этом все равно выкачивает все 5000 строк в память приложения, прежде чем начать их итерировать.

      Описанный в статье подход позволяет оптимизировать не третий шаг, а второй. То есть получать данные из базы постепенно. И, само собой, гидрация в таком подходе тоже будет постепенной — будут обрабатываться только скачанные в эту итерацию строчки.

  6. alxsad
    /#20271188

    А зачем выбирать из базы все заказы, а не конкретного пользователя? Если памяти хватило чтобы сгенерировать HTML страницу, то ее должно хватить чтобы выгрузить данные в excel файл. Плюс для такого рода отчетов и выгрузок можно не использовать гидрацию.

    • livinbelievin
      /#20271346

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

      • Ru6aKa
        /#20272668

        Как вариант решения данной задачи использовать

        copy (select * from huge_table) to '/tmp/huge_table.csv' with CSV DELIMITER ',';

        или
        psql -d dbname -t -A -F"," -c "select * from huge_table" > /tmp/huge_table.csv

        и дальше потом преобразовывать полученный файл в то что надо

    • VlastV
      /#20272670 / +1

      На странице может использоваться постраничная навигация, что не требует выкачки всех заказов даже по конкретному пользователю. При экспорте, необходимо выгрузить все заказы конкретного пользователя.


      Очень часто встречаюсь с данной задаче, при этом в качеств БД выступает MySQL

  7. Shakhmin
    /#20271666

    Спасибо и за понятный пример и за курсоры
    Ещё бы можно было "обновлять" выборку без больших расходов

    • EvgeniiR
      /#20271696

      Ещё бы можно было «обновлять» выборку без больших расходов

      А в чём проблема?

      Если обновить данные в таблице(без объектов) то просто запрос через тот же SQL/DQL на обновление.

      Если в цикле объекты обновлять — Query::iterate() и EntityManager::clear() чтобы IM/UoW почистить — manual

  8. L0NGMAN
    /#20272020

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

  9. untilx
    /#20272544

    Когда я в прошлый раз переписывал такую ядрёную выгрузку, база не была узким местом (всего 200к строк, но с кучей связей, так что в результате мало не было). Им, внезапно, оказался twig, а все запросы суммарно отрабатывали за пренебрежимо малое время. Решалось заменой на libxml или шаблон на чистом php, но делать всё равно не стали, потому что на этом этапе (та самая выборка пачками в генераторе) скорость и потребление памяти уже всех устраивали. Ну, и использование курсоров мне там на стареньком mysql всё равно не светило. Но идея хороша.