首頁 > PHP > [PHP] mysql_query 的記憶體使用與分頁方式

[PHP] mysql_query 的記憶體使用與分頁方式

2008年1月16日 jaceju 發表評論 閱讀評論

說明

這個實驗主要是探討在 TWPUG 上的這篇 FIEND 寫的： [原創] [分享] 小弟寫的 cakephp 換頁 排序 功能 (第一版) 。

幾個實驗重點如下：

  • FIEND 提到兩次 Query 不是一個好方式，他的做法是用一次 Query 配合 while + mysql_result 就能做到分頁效果。

  • 另外 shirock 從 PHP 原始碼的部份解釋 PHP 和 MySQL 抓資料後的處理方式，但 FIEND 卻說「用屁股想都知道 PHP 不可能把 QUERY 結果 全部拉回 PHP 端記憶體」。

  • 還有 shirock 提到：「參考文章中已經很明白指出 mysql_query 跳過 PHP 內建記憶體配置機制，而直接使用 mysql C library 的函數儲存資料在 PHP 程序這端。而 memory_limit 只會管制到 PHP 內建記憶體配置機制的使用上限。所以 mysql_query 查詢大量資料時，不會受到 memory_limit 的限制。」

基本上我從來不知道屁股可以用來思考，所以我還是要實事求是，用 FIEND 的方法實驗一次。

註：不過我老是在上廁所時想到一些靈感…Orz

環境

  • Windows XP
  • PHP 5.2.5
  • MySQL 5.0.45
  • memory_limit = 16M (in php.ini)

另外我準備了一個資料庫，裡面包含了四個資料表：

資料表 筆數 硬碟空間
r1000 一千筆 75KB
r10000 一萬筆 743KB
r100000 十萬筆 7,422KB
r1000000 一百萬筆 74,219KB

資料表欄位為一個 id 欄位和一個 value 欄位；而 value 欄位為 varchar(64) ，其內容存的是兩個隨機的 md5 函式結果所組合的字串。可以用以下程式產生：

<?php
echo "CREATE DATABASE `page_test`;\n";
echo "USE `page_test`;\n";
 
foreach (array(1000, 10000, 100000, 1000000) as $r) {
    $sql = <<<SQL
CREATE TABLE IF NOT EXISTS `r$r` (
`id` int(10) unsigned NOT NULL auto_increment,
`value` varchar(64) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
SQL;
    echo $sql, "\n";
    for ($i = 1; $i <= $r; $i ++) {
        $value = md5(rand(0, 9999)) . md5(rand(0, 9999));
        echo "INSERT INTO `r$r` (`value`) VALUES ('$value');\n";
    }
}

程式

程式部份很簡單，就是按照 FIEND 說的步驟來寫的。只是我這裡改用 CLI 模式執行，以避掉 Apache 的影響。 

<?php
echo ini_get('memory_limit'), "\n"; // 16M
echo "\n";
echo "========================\n";
echo "Start.\n";
echo "========================\n";
sleep(10);
 
$link = mysql_connect('localhost', 'username', 'password');
mysql_select_db('page_test', $link);
 
$result = mysql_query('SELECT * FROM r1000000', $link);
 
echo "\n";
echo "========================\n";
echo "mysql_query\n";
echo "========================\n";
sleep(10);
 
$count  = mysql_num_rows($result);
echo 'count: ', $count, "\n";
 
echo "\n";
echo "========================\n";
echo "mysql_num_rows\n";
echo "========================\n";
sleep(10);
 
$start = rand(0, $count - 1);
$end   = $start + 10;
$i     = $start;
 
echo 'start: ' . $start, "\n";
echo 'end: ' . $end, "\n";
 
echo "\n";
while ($id = mysql_result($result, $i, 'id')) {
echo $i, ': ', $id, "\n";
$i ++;
if ($i >= $end || $i >= $count) break;
}
 
echo "\n";
echo "========================\n";
echo "while & mysql_result\n";
echo "========================\n";
sleep(10);
 
mysql_free_result($result);
 
echo "\n";
echo "========================\n";
echo "mysql_free_result\n";
echo "========================\n";
sleep(10);
 
mysql_close($link);
 
echo "\n";
echo "========================\n";
echo "mysql_close\n";
echo "========================\n";
sleep(10);

而記憶體的觀察我是用 Windows 的工作管理員，然後查看 php.exe 所使用的記憶體大小。在什麼都沒有執行的狀況下， php.exe 會佔用掉約 8MB 的記憶體 (在我的環境下) 。

結果如下 (每項執行 5 次後再取平均值) ：

資料表 程式啟動 mysql_query mysql_num_rows while & mysql_result mysql_free_result mysql_close
r1000 8,196KB 8,592KB 8,596KB 8,656KB 8,568KB 8,556KB
r10000 8,196KB 9,496KB 9,500KB 9,560KB 8,592KB 8,580KB
r100000 8,192KB 18,840KB 18,844KB 18,904KB 8,584KB 8,596KB
r1000000 8,192KB 110,684KB 110,688KB 110,748KB 8,796KB 8,784KB

然後我比較 mysql_query 用掉的記憶體和原來資料表使用的硬碟空間：

資料表 mysql_query 硬碟空間
r1000 396KB 75KB
r10000 1,300KB 743KB
r100000 10,648KB 7,422KB
r1000000 102,492KB 74,219KB

很明顯地 mysql_query 所得到的 resource 佔的記憶體空間比 MySQL 使用的硬碟大小還多，這證明了 mysql_query 是有把資料內容抓到 PHP 這邊來；至於為什麼 MySQL 的反而比較小，我想這應該是 MySQL 將資料做壓縮的關係。

補充：我上面在 MySQL 壓縮的部份沒有任何根據，應該是錯誤的推論；因此我想就以 normansu 給我的說明為準：

normansu 提到：

應該不是.
執行 mysql_store_result 的時候,
在 result 和每一筆 record 都會多一個 Header 的空間,
大小不一定(看 field count).

所以使用的記憶體會比實際 mysql table 大.
在 source 中沒有看到任何 compress 的動作.

mysql 的這個流程讓我嚇一跳,
以往大部份用的是 mssql 和 oracle,
在 client 和 server 間的 data cache 機制都做得比較好,
看起來 mysql 像是把結果算出來後就直接全部丟出來.

所以使用 mysql 要比用 mssql 或oracle 要來得更小心一點.

然後由於在上面的實驗裡我已經將 php.ini 的 memory_limit 設為 16M ，也用過 php -i 檢查過了。但在執行一百萬筆測試時，卻沒有受到任何影響，因此也證明了 shirock 說的「所以 mysql_query 查詢大量資料時，不會受到 memory_limit 的限制。」

結論

從上面的實驗可以看到一次 Query 並取得資料總筆數雖是可行的，但這個前提是建立在 mysql_query 已經把資料內容全部放到 php.exe 的記憶體中。除非我誤解了 FIEND 的意思，不然他的作法看起來實在不適用於他所說的「存取大量資料的環境」。

所以一般常見的做法是採用就是 tokimeki 提到的兩次 Query 的方式，第一次先利用 SQL 的 COUNT() 指令取得我們所需要的總筆數，第二次再配合 LIMIT 去取得我們所要的資料。不用 LIMIT 的後果就是每當執行一次 script ，我們就要冒著記憶體使用量爆增的後果。

註：雖然 MySQL 有 Query Cache ，但對 php 端已經爆增的記憶體也於事無補了。

而我也贊成 tokimeki 說的：「另外之二，假設內容資料是非常龐大的，且必須利用查詢內容作某些運算（例如：矩陣運算之類的），那麼這樣的應用不該由PHP程式來完成，應由其他的方式來作計算（例如：資料庫作OLAP或是Server上某個用C/C++寫的程式定期跑），Web這邊只做顯示以及計算排程即可。」

其他不想多說了，被某人看不起也不是一天兩天的事了。我自知自己還有很多東西要學，而這些還有望其他高手前輩們給予我指教。

不過最後這個實驗也證明一件事：屁股不是用來想事情的。

Categories: PHP Tags:
  1. Tsung
    2008年1月17日12:08 | #1
    回覆 | 引用

    石頭大大最近也在寫相關的這類文章, 似乎大家爭議蠻多的…
    標準做法就是查兩次, 一次 count(*), 另一次 limit, 這樣子的做法會比較省兩邊的記憶體, 相互傳送的資料也會少很多不是嗎? :)
    全部拉回來, 光想就知道很慘了.. XDDD

  2. jaceju
    2008年1月17日12:33 | #2
    回覆 | 引用

    To 宗董：

    大家都是這麼想呀 (而且也是這麼做) ，可是就是有某位 F 兄不信邪…

    ====

    另外有興趣的朋友可以再上去看看後續 F 兄的回應，不過不知道是不是我中文程度不好，他的做法及說明我有看沒有懂…

    而且他還說我的「實驗的方式 條件還不夠明朗」？到底不明朗在哪裡？一定要他的方式才是正確的嗎？我不懂其中的差異，還煩請高手指教一下。

  3. Rack Lin
    2008年1月17日15:53 | #3
    回覆 | 引用

    TWPUG 原文看的好累，有一種時空錯亂的感覺。
    pagination 處理問題，不像是在 2008 年的現今討論的課題。
    或許是他貼在 CAKEPHP 分類中，新的框架，所以原來的技能都可以砍掉重練吧. @@
    http://twitter.com/racklin/statuses/603779172

  4. jaceju
    2008年1月17日16:41 | #4
    回覆 | 引用

    To 阿土伯：

    是呀，我一開始還以為我以前是不是忽略了 PHP 在分頁這邊有更好的作法。

    所以我還特地去找了一下國外分頁的作法，還有 MySQL 相關的資料，也沒人提出 F 兄一樣的看法；大部份找到的資料都是目前我們常用的兩次 Query 。

    唉…我有時真不知道到底是誰的觀念有問題，只好有空就實驗一下，以求證各家的說法。感覺自己的自信沒有某人好…

  5. jaceju
    2008年1月17日16:45 | #5
    回覆 | 引用

    自己補充一下，其實用 php mysql paginaion 到 Google 搜一下就有很多資料了，這裡舉第一篇以及 MySQL 裡面的說明為例：

    http://www.tonymarston.net/php-mysql/pagination.html
    http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

  6. mikespook
    2008年1月24日10:24 | #6
    回覆 | 引用

    一次取出，再分页的做法就跟 asp 的 recordset 分页做法一样。几百或者几千数据的时候并不明显，大数据量时就完蛋。几M或者几百M，几G的数据……

    又或者应用服务与数据库服务不在同一台机器上？网络传输这些数据可能就要花费不少时间。

  7. jaceju
    2008年1月25日10:44 | #7
    回覆 | 引用

    To mikespook:

    「一次取出，再分页的做法就跟 asp 的 recordset 分页做法一样。」我以前剛寫 ASP 時也是這麼做的。

    但是後來才發現在資料量一大時，這個做法很明顯地會有問題，就如同你所說的一樣。

    通常我們還是會把數據庫和應用伺服器分離，所以為了避免你所說的大量網路傳輸時間，採用的就是兩次 Query 來節省時間與頻寬。

    ===

    不過這次的經驗讓我更堅信以前學到的兩次 Query 是對的，除非 MySQL 官方有提出更好的解法，不然我實在想不出其他方式了。

  8. 遊手好閒的石頭成
    2008年1月29日18:09 | #8
    回覆 | 引用

    哈哈哈，我邊看邊笑啊 XD

    基本上，那整個系列文章，我回應到一半就想到動漫論壇上常用的對某種人的稱呼: "國中廚"、"小白國中生"。

    Ok, 言歸正傳。關於查詢結果的佔用的memory大小，大於磁碟上的 data table 的事。

    基本上，這是正常的，因為在記憶體中的查詢結果內容，還要再加上 "資料結構(link-node, etc)" 所佔用的記憶體。

    在磁碟上儲存 data table 的都是靜態(static)內容，例如:
    —-
    1,abc
    2,def
    —-
    之類的。

    但當我們將他們載入記憶體後，往往會加上一些資料結構，以利演算法操作。例如:
    struct node {
    struct node *pre,
    struct node *next,
    int key,
    char* data
    }

  9. jaceju
    2008年1月29日18:50 | #9
    回覆 | 引用

    To 石頭成：

    感謝分析，這點是我比較弱的地方；所以後來我也採用 normansu 的解說。

    不過無論如何，我想很多東西還是要先實驗一下或找出證據來得好；不然信心滿滿地用某個部份去相信某件事情的這種方式，這樣好像不是一個 IT 人該有的心態。

  10. jj
    2008年2月25日22:16 | #10
    回覆 | 引用

    如果你要一次抓大量資料, 應該考慮改用 mysql_unbuffered_query()

  11. jaceju
    2008年2月26日10:19 | #11
    回覆 | 引用

    To jj:

    雖然 mysql_unbuffered_query 是一個好方式，但一般來說，我們很少這樣抓資料。很大的原因是它會佔據資料庫連線，使得大量 request 進來時，造成 DB Lock 。

    當然實際應用還是要看專案型態，有可能內部的應用程式可以這麼做也說不定；我碰過的專案不是那麼多，這些意見僅供參考。

  12. jj
    2008年3月3日22:13 | #12
    回覆 | 引用

    在我的工作上, 我倒是常常使用 mysql_unbuffered_query(), 因為 mysql_query() 執行時間比較久, 而且會因為記憶體不足而失敗

  13. mooncake
    2008年3月6日11:25 | #13
    回覆 | 引用

    其實不只MYSQL會這樣,postgreSQL 也會. 7年前我為公司寫了個分頁顯示也面對記憶體的問題, 後來我放棄公司所使用的資料庫函式, 用最原始的方式, PHP 內建的postgreSQL 函式抓取, count(*) 之後計算多少頁…

    也就跟以上提到的一樣, 分成兩次query…

  14. jaceju
    2008年3月6日12:00 | #14
    回覆 | 引用

    To jj:

    如果真的有需要一次取得所有資料 (但不是真的把資料放到 PHP 這邊的記憶體) ，用 mysql_unbuffered_query() 的確是正確的選擇，這點我非常同意。

    To mooncake:

    其實我以前在寫 ASP + MSSQL 時，也是這樣處理的 :)

  15. Citypig
    2008年8月29日15:31 | #15
    回覆 | 引用

    終於把所有相關網相及討論都看完了。

    感謝站長及石頭城。

    對於一個普通、已知、簡單、並且是正確的知識，還肯花那麼多精力去求證，來說服一個 DATABASE 經驗還停留在10幾前的人，真是不容易。

    這種精神比原來要解決的問題還可貴。

Spam Protection by WP-SpamFree