跳到主要內容

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 上使用自行編譯的第三方函式庫

      以使用 LevelDB 為例。 抓好並編好相關檔案,編譯方式見第三方函式庫附的說明:$ ls include/ # header files leveldb/ $ ls out-shared/libleveldb.so* # shared library out-shared/libleveldb.so@ out-shared/libleveldb.so.1@ out-shared/libleveldb.so.1.20* 下面的例子用 clang++ 編譯,這裡用到的參數和 g++ 一樣。 問題一:找不到 header$ clang++ sample.cpp sample.cpp:5:10: fatal error: 'leveldb/db.h' file not found #include "leveldb/db.h" ^ 1 error generated. 解法:用 -I 指定 header 位置 問題二:找不到 shared library$ clang++ sample.cpp -I include/ /tmp/sample-2e7dd8.o: In function `main': sample.cpp:(.text+0x1e): undefined reference to `leveldb::Options::Options()' sample.cpp:(.text+0x6f): undefined reference to `leveldb::DB::Open(leveldb::Options const&, std::string const&, leveldb::DB**)' sample.cpp:(.text+0x10c): undefined reference to `leveldb::Status::ToString() const' sample.cpp:(.text+0x7d0): undefined reference to `leveldb::Status::ToString() const' clang: error: linker command failed with exit code 1 (u…

      熟悉系統工具好處多多

      記一下以前很困擾, 現在秒殺的小事。 更新這篇的時候, 忘了函式庫用的 man page 裝在那個 package。以前就會想辦法 google, 運氣好一下會找到, 運氣不好會多找一會兒。 這回我想到新作法:$ strace -e open man 3 printf > /dev/null # 發現是讀 /usr/share/man/man3/printf.3.gz $ dpkg --search /usr/share/man/man3/printf.3.gz # 找到套件名稱 manpages-dev $ aptitude show manpages-dev # 確認描述符合, 收工

      virtualbox 使用 USB 裝置

      2012-12-16 更新 現在 (4.x 版) 似乎無需做任何設定, 只要有裝 Oracle VM VirtualBox Extension Pack, 在 VirtualBox 視窗右下角按 USB 的圖示, 再點目標裝置, 即可加入或移除該裝置 同一時間只有 host 或 guest 可擁有該裝置, 所以從 guest OS 移除, 相當於接回 host OS 目前 VirtualBox 只支援 USB 2.0 的插槽, 若偵測不到時, 注意一下是否為這個問題 有時拔拔插插, VirtualBox 會進入奇怪的狀態, 接上去 guest OS 無法連接且跳出 device is busy 的錯誤訊息。試看看拔除該裝置, 重開 guest OS (續上則) 若重開 guest OS 無效, 並且 host OS 已移除該裝置, VirtualBox 的 USB 清單卻仍顯示 "captured", 試看看拔除該裝置, 重開 host OS原文網路上搜一下, 比較多是 Ubuntu 當 host 的解法, 我的情況是 Win7 當 host, Ubuntu 當 guest。 這兩篇說明很詳細《Learn How to Set Up USB and Networking Options in VirtualBox》《幻影千瞳的部落格: VirtualBox 使用筆記(二):使用 USB 裝置》 現在的版本圖形介面很好用了, 不用像第二篇說的那樣用指令操作。這裡記下我的操作步驟: 關掉 guest OS 在 VirtualBox 選單, 選擇 guest OS -> Settings -> USB -> Enable USB 2.0 會出現訊息框, 說明要安裝 Oracle VM VirtualBox Extension Pack。下載後安裝它 host OS 插入 USB 隨身碟 在 VirtualBox 選單, 選擇 guest OS -> Settings -> USB, 點右邊有綠色 "+" 的 USB 頭的圖示, 選擇該 USB 隨身碟, 加入它的 filter 從 host OS 移除 USB 隨身碟 開啟 guest OS 插入 USB 隨身碟, 於是 guest OS 會自動偵測…