處理大量資料時, 發覺資料愈大, 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 裡邊收邊寫回資料, 會有錯誤訊息, 後來就改成收完全部資料才開始運算和寫回資料。
