PDO MYSQL_ATTR_USE_BUFFERED_QUERY 不生效

Question

我有如下粗略的代码（完整代码146行，其中90行是字符串解析，需要的可以补充）：

ini_set('memory_limit', '7G');

$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));

$db_ub->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$stmt = $db->prepare('select columns from stats where timestamp between ? and ?');

$stmt->execute(array('2020-04-25', '2020-05-25'));

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

      echo memory_get_usage() .PHP_EOL;

      echo $row['id'] . PHP_EOL;

      $stmt2 = $db_ub->prepare('select somedata from users limit 1');

      $stmt2->execute();

      $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

      $type = !empty($row2['somedate']) ? 5 : 4;

      $result = $db_ub->prepare('insert ignore into newtable (old, type) values (?, ?)');

      $result->execute(array($row['id'], $type));

}

在$stmt->execute(array('2020-04-25', '2020-05-25'));我的内存消耗期间.34GB（用于ps aux | grep 'php ' | awk '{$5=int(100 * $5/1024/1024)/100"GB";}{ print;}'监视消耗select和show full processlistSQL 端验证）。一旦脚本进入，while它就会跳转到 +5 GB。

测试setattribute

var_dump($db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false));

好像已经生效了：

bool(true)

但是当我切换缓冲或无缓冲时，行为不会改变。

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false)

和

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true)

使用echo $db->getAttribute(constant('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY'));还显示设置更改。

将设置移动到语句而不是https://www.php.net/manual/en/ref.pdo-mysql.php建议的连接也没有用。

$stmt = $db->prepare('select columns from stats where timestamp between ? and ?', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));

我也试过将缓冲区设置移动到连接而没有影响：

$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));

Answer 1

该PDO::ATTR_PERSISTENT值不是布尔值。它标识正在使用的连接，对多个连接使用唯一值。就我而言：$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'unbuff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'buff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));

Answer 2

难道你不能通过简单地运行一个查询来摆脱大部分代码： INSERT IGNORE INTO newtable     SELECT  ...,             IF(..., 5, 4)         FROM oldtable WHERE ...;这样，您就可以摆脱 7G 内存问题。如果结果证明一次做太多，那就把它分成块。另一个话题：为什么select somedata from users limit 1在循环内执行？似乎每次都得到相同的数据。此外，如果没有ORDER BY，您将无法预测limit 1您将获得哪一行。

Answer 3

您实际上是在进行 135000000 次查询，而不是迭代 135000000 个对象。将代码更改为仅执行一个查询，但对元素进行排序，就好像它们在您的 for 循环中一样。$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));$stmt = $db->prepare('SELECT * FROM stats ORDER BY id ASC');$stmt->execute();while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {    // ...}你甚至不需要这个if，它是数据库本身可以更快使用的逻辑：如果(!empty($row['id'])) {反而：SELECT * FROM stats WHERE id IS NOT NULL ORDER BY id ASC我有一段时间没有研究 PDO/MySQL，但我假设 unbuffered 允许你使用游标：$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);考虑到每个连接只能激活一个查询。您基本上是在使用连接的缓冲区。更好的选择是在 map reduce 中只加载小块。SELECT * FROM stats LIMIT 100, 0使用结果，然后SELECT * FROM stats LIMIT 100, 100