Geekly articles weekly

学说中的数据库游标

图片


使用游标，您可以批量从数据库接收数据并处理大量数据，而不会浪费应用程序内存。 我确信每个Web开发人员都至少经历过一次类似的任务，而不仅仅是在我之前经历过一次。 在本文中，我将告诉您游标在哪些任务中有用，并以PostrgeSQL的示例为例，提供PHP + Doctrine使用游标的现成代码。


问题


假设我们有一个庞大的PHP项目。 当然，它是在某些框架的帮助下编写的。 例如，Symfony。 它还使用数据库（例如PostgreSQL），并且该数据库具有一个用于存储200万条记录的板，其中包含有关订单的信息。 项目本身就是这些订单的界面，可以显示和过滤它们。 而且，我想说，这做得很好。


现在，我们被要求（是否还没有要求您？肯定会要求他们）将筛选订单的结果上传到Excel文件中。 让我们按下带有表格图标的按钮，它将向用户吐出带有命令的文件。


通常如何决定，为什么会变坏？


尚未遇到此类任务的程序员该如何处理？ 他在数据库中进行SELECT，读取查询结果，将响应转换为Excel文件并将其提供给用户的浏览器。 该任务有效，测试完成，但是问题开始于生产中。


当然，我们为每个进程设置了合理的（可能是）1 GB的PHP内存限制，并且一旦这200万行不再适合此千兆字节时，一切都会中断。 PHP崩溃并显示“内存不足”错误，用户抱怨该文件未上传。 发生这种情况是因为我们选择了一种非常幼稚的方式从数据库中卸载数据-所有这些首先都从数据库内存（及其下的磁盘）传输到PHP进程的RAM，然后被处理并上传到浏览器。


为了使数据始终存储在内存中，您需要分批从数据库中获取数据。 例如，读取，处理，写入文件等次数达10,000条记录。


好吧，认为是第一次完成我们任务的程序员。 然后，我将做一个循环，并将查询结果分段压缩，以指示LIMIT和OFFSET。 它可以工作，但是对数据库来说是一个非常昂贵的操作，因此，上载报告的时间不是30秒，而是30分钟（还不错！）。 顺便说一句，如果此时除了OFFSET没什么让程序员想到的，那么仍然有很多方法可以在不强奸数据库的情况下实现相同的功能。


同时，数据库本身具有内建的从线程中读取数据的能力-游标。


游标


游标是指向驻留在数据库中的查询结果中的一行的指针。 使用它们时，我们不能以立即下载数据的方式执行SELECT，而是使用此选择打开光标。 接下来，随着游标向前移动，我们开始从数据库接收数据流。 这给我们带来了相同的结果：我们分批读取数据，但是数据库并没有像查找OFFSET那样做同样的工作来查找需要开始的行。


游标仅在事务内部打开，并且一直存在直到事务有效（存在例外，请参见WITH HOLD ）。 这意味着，如果我们从数据库中缓慢读取大量数据，则将需要很长时间。 有时这很糟糕 ，您需要了解并接受这种风险。


学说中的游标


让我们尝试在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％的问题 ，它只能与本机查询一起使用。 因此，我们称它为PgSqlNativeQueryCursor。


构造函数：


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

在这里，我为将来的游标生成一个名称。


由于该课程中包含该课程的PostgreSQL专用SQL代码，因此最好检查我们的驱动程序是否为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（here_original_query）中。 我执行。


让我们制作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; }

该方法有一个参数-这是包的大小，它将成为此方法返回的请求的一部分。 我对查询克隆应用了相同的技巧，覆盖了其中的参数，并用FETCH ... FROM ...;构造函数替换了SQL。


为了避免在第一次调用getFetchQuery（）之前忘记打开游标（突然我将无法获得足够的睡眠），我将直接在getFetchQuery（）方法中隐式打开它：


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

并且openCursor（）方法本身将被设为私有。 需要显式调用时，根本看不到任何情况。


在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(); } }

这是整个班级的全部 。 让我们尝试一下吗？


我在某些条件XLSX中打开一些条件编写器。


 $writer->openToFile($targetFile);

在这里，我得到NativeQuery来从数据库中提取订单列表。


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

基于此查询，我声明一个游标。


 $cursor = new PgSqlNativeQueryCursor($query);

对于他，我得到了一个请求，以10,000行为单位批量接收数据。


 $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的恒定内存来处理所有数据，并且没有因一系列繁琐的查询而折磨数据库。 而且卸载本身花费的时间比满足请求所需的时间略多。


看看项目中是否有可以使用光标加速/保存内存的地方？

Source: https://habr.com/ru/post/zh-CN455571/

More articles:


All Articles