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 都是必要的。



      留言

      這個網誌中的熱門文章

      (C/C++ ) 如何在 Linux 上使用自行編譯的第三方函式庫

      熟悉系統工具好處多多

      virtualbox 使用 USB 裝置