MySQL varchar 長度和效能問題

看《High Performance MySQL 2e》 p242 才發現, 可怕的效能問題。varchar 在 disk 上只存必要的空間, 但丟到記憶體後卻當作 char 來存。MySQL 內部處理字串操作時, 是用固定大小的 buffer, 所以 utf-8 會強迫占用 3 bytes, char(10) 會占用 30 bytes。雖然 varchar 在硬碟上只存必要的空間, 但在記憶體裡卻會被當作 char 看待。原文如下:

MySQL uses fixed-size buffers internally for many string operations, so it must allocate enough space to accommodate the maximum possible lenght. For example, a CHAR(10) encoded with UTF-8 requires 30 bytes to store, even if the actual string contains no so-called wide characters. Variable-length fields (VARCHAR, TEXT) do not suffer from this on disk, but in-memory temporary tables used for processing and sorting queries will always allocate the maximum length needed.

MEMORY engine 用 varchar 等同於 char, MySQL 暫存表格似乎是用 MEMORY engine, 無法用 MEMORY engine 時會改用 MyISAM 存在硬碟上。若有某個 column 或 select 結果用超過 512 bytes, 該 table 或 select 結果不能存在 memory 裡。

綜合以上所言, 結果就是 varchar + utf-8 長度設超過 170 的話, 就無法存在 memory 裡。別以為用 varchar 就可以亂設最長長度啊。

另外, 即使 varchar 可以設長度到 65536, index 時只能取前 1000 bytes (不是 chars), varchar 過長時, 建 index 會出現錯誤, 而要求明確指示 index length。若自己建 index 到也還好, 但用 Django 還是 South 時會噴掉, 沒辦法自動產生正確的 SQL 建過長 varchar 的 index。

補充一下不能存在 memory 裡有多糟, 這表示 SQL 有用到 order by 或 group by 時, MySQL 得不斷用硬碟暫存結果。以 order by 為例, 假設有 column A, B, 其中 A 能放在 memory, B 不行。即使用 column A 排序, 排完後還得一筆筆依排序結果的順序從硬碟取出 column B 的資料, 那會是非常的慢。詳細的說明見《ORDER BY Optimization》裡 "The original filesort" 和  "The modified filesort" 的說明。