Python 從 MySQL 資料庫取出整個表格的快速作法

處理大量資料時, 發覺資料愈大, python 取出 mysql 資料愈慢, 變慢的比例並非線性的, 推測是 client 吃掉太大的記憶體而拖慢速度。

但是用 mysql client 直接寫入檔案, 以及 python 讀檔的速度都不慢。另外, mysql 有提供兩種方式從 server 端取回查詢結果, 有機會用 sequential 讀資料的方式加速。想說來比看看這三種寫法。

我從一個有近三千萬筆資料的表格取出單欄資訊, 型別為 varchar。各個方法只測一次, 記憶體用量是用 htop 大概看的, 別太在意。結果如下:

method	time	memory (G)
mysql client + read file (all)	1m8s	~0.64
mysql client + read file (sequential)	0m57s	~0
python api (store)	13m40s	~3.7
python api (use + fetchall)	>30m	>0.9
python api (use + fetchmany)	1m9s	~0

上欄各個方法說明如下, 所有 python 操作都有轉 unicode:

• mysql client: 直接用 mysql client (系統指令) 寫入檔案 。
• read file (all): 從檔案讀入全部內容到一個 list。
• read file (sequential): 從檔案一行一行讀入內容。
• python api (store): 用 MySQLdb 預設的連線方式, 會用 mysql server 提供的 api mysql_store_result() 取回全部內容到 client 端。
• python api (use + fetchall): 改用 mysql server 提供的 api mysql_use_result() 一筆筆取回到 client 端 (透過 SSCursor 使用 mysql_use_result() )。
• python api (use + fetchmany): 同上, 改設 size = 10000, 一次取回 10000 筆以減少取資料次數。

從結果來看, 先用 mysql client 取出資料寫到檔案, 再用 python 讀檔案比 python api 預設的方式快了十倍左右。其中 mysql client 花了 20 秒, 兩個讀檔方法分別用了 48 秒和 37 秒。

用 mysql_use_result() 配合 fetchmany 會變快不少, 如同文件所言, 處理大量資料時要改用 SSCursor。速度和先用 mysql client 寫入檔案再讀檔相當。但要配合 fetchmany() 設大一點的 size 才有用, 用 fetchall() 會一筆筆取資料, 反而因連線次數過多而更慢。附帶一提, Django 的 connection 是用預設的 cursor, 也就是採用 mysql_store_result() 取資料, 若想用 mysql_use_result() 只好自己另開 connection 了。

ps. 以前有比較「預設 cursor 的呼叫一次 fetchall()」和「多次 fetchmany()」。結果用 fetchmany() 不會變快, 原因是資料已取回到 client 端, 再來用 fetchall() 或 fetchmany() 也只是從 python list 取出 list 或 sub-list 而已。 用 fetchmany 搭配 mysql_use_result() 才會真的變快。

SSCursor + fetchmany 的參考程式如下:

import MySQLdb
from MySQLdb.cursors import SSCursor

if __name__ == '__main__':
    conn = MySQLdb.connect(host='localhost',
                           db='mydb',
                           user='fcamel',
                           passwd='*********',
                           charset='utf8')
    cursor = conn.cursor(cursorclass=SSCursor)

    sql = 'SELECT some_column FROM some_table'
    cursor.execute(sql)

    try:
        while True:
            rows = cursor.fetchmany(size=10000)
            if not rows:
                break
            # process your rows here.
    finally:  
        # 要記得關 cursor、connection, 
        # 不然用 SSCursor 時會出現 warning
        cursor.close()
        conn.close()

2010-09-11 更新

看了 High Performance MySQL 2e 後才知道, 原來 MySQL 的協定是半雙工, 也就是說, 同一時間 server 和 client 只能有一方送資料。使用 mysql_use_result 時在收完全部資料前, client 不能送資料給 server。難怪之前我在一個 process 裡邊收邊寫回資料, 會有錯誤訊息, 後來就改成收完全部資料才開始運算和寫回資料。