MySQL

再來還些債…

前陣子我們家在看一些 PHP 的版本差異，也順便拆一些地雷。
先說記憶體耗用，我們用下面這段程式在一些 PHP 版本上執行，觀察記憶體耗用：

<?php
$a = memory_get_usage();
echo "begin:\t$a" . PHP_EOL;

$arr = array();
for ( $i = 0; $i < 100000; $i++ ) {
    array_push($arr, $i);
}

$b = memory_get_usage();
echo "end:\t$b" . PHP_EOL;

echo "diff:\t" . ($b - $a) . PHP_EOL;

在 PHP 5.1 ( on 32-bit Linux ) 觀察到的記憶體差距值大約是 5.8 MB，在 PHP 5.3 ~ 5.6 ( on 64-bit Linux ) 觀察到的記憶體差距值大約是 14 MB，而 PHP 7.1 ~ 7.2 ( on 64-bit Linux ) 觀察到的記憶體差距值大約是 4 MB。

至於 PHP 的 MySQL driver/library 方面…
參考官方提供的 Overview of the MySQL PHP drivers : Buffered and Unbuffered queries ；PHP 的所有的 MySQL extensions 預設使用 buffered mode，所以在撈取大量資料時，PHP 執行完 SQL statement 就會耗用大量記憶體。
另外，在官方提供的 Overview of the MySQL PHP drivers : Choosing a library 可以看到，PHP 5.3 以前的預設 MySQL driver ( libmysqlclient ) 沒使用 PHP 的原生記憶體管理，PHP 5.3 之後的預設 MySQL driver ( mysqlnd; MySQL Native Driver) 才開始用。
對這些行為，我們另外生了一段測試程式：

<?php
$a = memory_get_usage();
echo "begin:\t\t$a" . PHP_EOL;

$dbConn = new PDO(.....);
$b = memory_get_usage();
echo "new PdoMySQL:\t$b\t\tDiff: " . ($b - $a) . PHP_EOL;

$st = $dbConn->query("SELECT * FROM testTable LIMIT 30000");

$b = memory_get_usage();
echo "get Stmt:\t$b\tDiff: " . ($b - $a) . PHP_EOL;

$row1 = $st->fetch();

$b = memory_get_usage();
echo "fetch once:\t$b\tDiff: " . ($b - $a) . PHP_EOL;

$row2 = $st->fetch();

$b = memory_get_usage();
echo "fetch twice:\t$b\tDiff: " . ($b - $a) . PHP_EOL;

$st->closeCursor();

$b = memory_get_usage();
echo "closeCursor:\t$b\t\tDiff: " . ($b - $a) . PHP_EOL;

$row3 = $st->fetch();
$b = memory_get_usage();
echo "fetch:\t\t$b\t\tDiff: " . ($b - $a) . PHP_EOL;

var_dump($row3);

在 PHP 7.1 ( with mysqlnd , 64-bit Linux ) 的數據如下：

begin:          355552
new PdoMySQL:   378784          Diff: 23232
get Stmt:       25866944        Diff: 25511392
fetch once:     25868752        Diff: 25513200
fetch twice:    25870568        Diff: 25515016
closeCursor:    384104          Diff: 28552
fetch:          384104          Diff: 28552
bool(false)

在 PHP 5.1 ( with libmysqlclient , 32-bit Linux ) 的數據則是：

begin:          50432
new PdoMySQL:   85752           Diff: 35320
get Stmt:       92184   Diff: 41752
fetch once:     93584   Diff: 43152
fetch twice:    95248   Diff: 44816
closeCursor:    95312           Diff: 44880
fetch:          95400           Diff: 44968
bool(false)

應該有為數不少的 PHP developers 曾經踩過這種記憶體相關的地雷…