varchar 與 text 的效能差異, 以及 order by 的運算方式

今天和同事 S 說明 MEMORY engine 512 bytes 限制時, 被問到用 varchar 超過 512 bytes 的話, 和 text 有何差別。想了一會兒才想到使用 text 會讓 mysql 用比較慢的方式排序, 在這裡小記一下。

在官網 7.3.1.11. ORDER BY Optimization 裡寫得很清楚, 使用 order by 或 group by 時, MySQL 可能會用到 filesort (可用 explain 看 extra 欄位確認)。filesort 有兩種版本:

• original filesort: 取出符合條件的 rows, 只留 order by 裡的欄位和 row position。排好後再回頭一個個依 row position 取回需要的欄位。換句話說, 會從硬碟讀兩次同樣的資料。
• modified filesort: 取出符合條件的 rows, 將需要的欄位和 order by 裡的欄位一起排。只會從硬碟讀一次資料。

舉例來說, 若是 select a from table order by b:

• original filesort: 先用 b 排序, 再依排好的結果取出各 row 的 a, 所以會有一堆 random access。
• modified filesort: 排序 (b, a ), 排好後就是最後結果。

mysql 會盡量用 modified filesort, 以減少重覆讀硬碟 (更何況第二次還是 random access。) 但發生以下其中一個情況, mysql 會選用 original filesort:

• 用到 text 或 blob 時。
• 單筆資料 (即上面範例的 (b, a)) 超過 max_length_for_sort_data, 單位為 bytes。

除了避免使用第一種的 filesort, 說明文件最後有提供幾個最佳化的作法, 可以調一些參數。

2010-12-16 更新

original filesort 不見得會比 modified filesort 慢, 今天就踏到雷, 調大 max_length_for_sort_data 讓 mysql 使用 modified filesort 反而慢兩倍多, 原因如文件上所言, 單一 row 變大, buffer 能放 row 的數量變少, 增加排序的次數 (show status like 'Sort_merge_passes')。用 profiling 看則發現 Sorting result 的執行時間變長。

由於我用來排序的欄位就超過 512 bytes, 一定得將暫存 table 寫到硬碟。順便試了用 ram disk 上的 tmpdir, 結果的確省了一半多的時間, 原以為會會瞬殺的說。

另外 How fast can you sort data with MySQL ? 和 Impact of the sort buffer size in MySQL 指出 sort_buffer_size 不是愈大愈好, 使用時記得測看看。結論是, 無論如何, benchmark 和 profiling 都是必要的。