バッファクエリと非バッファクエリ
クエリは、デフォルトではバッファモードで実行されます。 つまり、クエリの結果がすぐに MySQL サーバーから PHP に転送され、 PHP プロセスのメモリ内に結果を保持し続けるということです。 これで、その後で行数を数えたり結果ポインタを移動 (シーク) したりといった操作ができるようになります。 また、同じ接続上でさらに別のクエリを発行しつつ、 現在の結果セットを使った作業をすることもできます。 バッファモードの弱点は、結果セットが大きくなると大量にメモリを消費するということです。 結果セットへの参照がなくなるか、結果セットを明示的に解放する (リクエストを終了すると、自動的に解放されます) まではメモリ上に残り続けます。 バッファモードは「結果を格納する」クエリとも呼ばれます。 ここでは、結果セット全体が一度に格納されます。
注意:
ライブラリとして libmysqlclient を使っている場合は、結果セットのデータを PHP の変数に代入するまで結果セットのメモリ利用量が PHP のメモリ制限にカウントされません。 mysqlnd の場合は、結果セットのメモリがすべて PHP のメモリ制限にもカウントされます。
非バッファクエリは、クエリを実行してMySQLサーバーからデータを取得するのを待ちます。 PHP 側でのメモリ消費が少なくなりますが、サーバーへの負荷は高くなります。 サーバー上の結果セットからすべての結果を取得するまで、 同じ接続上で別のクエリを実行することはできません。 非バッファクエリは「結果を使う」クエリとも呼ばれます。 結果セットから全ての行が取得されると、 結果セットは削除されるので再度ループさせることはできません。
こういった特性を考慮すると、非バッファクエリを使うのは、 結果セットの量が巨大な場合や、順番に結果を処理する場合だけに留めるべきでしょう。 非バッファクエリには、たくさんの落とし穴があり、 そのため使い方が難しいです。 たとえば、結果セットの行数が最後の行を取得するまで不明な点が挙げられます。 バッファクエリは使い方が簡単で、 結果セットをより柔軟に処理できます。
デフォルトはバッファモードなので、以下の例では それぞれの API で非バッファクエリを実行する方法を示します。
例1 非バッファクエリの例: mysqli
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$unbufferedResult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
foreach ($unbufferedResult as $row) {
echo $row['Name'] . PHP_EOL;
}
?>例2 非バッファクエリの例: pdo_mysql
<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_password');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$unbufferedResult = $pdo->query("SELECT Name FROM City");
foreach ($unbufferedResult as $row) {
echo $row['Name'] . PHP_EOL;
}
?>User Contributed Notes 1 note
The bufferred and unbuffered queries can be used for a limited amount of records.
For example; while implementing download CSV for a query using buffered way, memory limit issues comes up above 30,000 records to be buffered.
Similarly, For unbuffered the load switched to database server.
This load on both the web (buffered) and MySQL (unbuffered) servers can be reduced as below supporting download CSV for 30,000+ records.
<?php
// Shell command.
$shellCommand = 'mysql '
. '--host='.escapeshellarg($hostname).' '
. '--user='.escapeshellarg($username).' '
. '--password='.escapeshellarg($password).' '
. '--database='.escapeshellarg($database).' '
. '--execute='.escapeshellarg($sql).' '
. '| sed -e \'s/"/""/g ; s/\t/","/g ; s/^/"/g ; s/$/"/g\'';
// CSV headers
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$csvFilename}");
header("Pragma: no-cache");
header("Expires: 0");
// Execute command via shell and echo the complete output as a string
echo shell_exec($shellCommand);
?>
There will be a bit of CPU consumption for the sed regex.