Хабрахабр

Курсоры БД в Doctrine

image

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

Проблема

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

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

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

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

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

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

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

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

Курсоры

При их использовании, мы можем выполнить 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;

И, чтобы за 20% времени решить 80% проблемы, работать он будет только с Native Queries. Набросаем класс, совместимый с Doctrine, который будет инкапсулировать работу с курсором. Так его и назовем, 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()
$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', $direction, $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.

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

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 при обработке всех данных и не замучали базу чередой тяжелых запросов. ОТЧЕТ ГОТОВ! А сама выгрузка заняла по времени незначительно больше, чем потребовалось базе на выполнение запроса.

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

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть