Используя курсоры, вы сможете порционно получить из БД и обработать большое количество данных, не расходуя при этом память приложения. Уверен, перед каждым веб-разработчиком хотя бы раз вставала подобная задача, передо мной тоже — и не раз. В этой статье я расскажу, в каких задачах курсоры могут быть полезны, и дам готовый код по работе с ними из 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.
От класса нам нужно три вещи:
- Уметь открывать курсор.
- Уметь возвращать нам данные.
- Уметь закрывать курсор.
Открываем курсор:
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',
$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. Вообще не вижу кейсов, когда его нужно вызывать явно.
Внутри 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 при обработке всех данных и не замучали базу чередой тяжелых запросов. А сама выгрузка заняла по времени незначительно больше, чем потребовалось базе на выполнение запроса.
Посмотрите, нет ли в ваших проектах мест, которые можно ускорить/сэкономить память при помощи курсора?
Автор: Денис Гирько
