MySQL 資料型別選擇對效能的影響：VARCHAR(255) 陷阱與隱含轉換案例

表設計時習慣性將所有字串欄位設為 VARCHAR(255) 或 VARCHAR(64)，實際存儲卻僅 5-15 字節，這類「隨意定義」會導致嚴重效能問題。本文以真實案例剖析資料型別不匹配導致的隱含轉換與索引失效。

案例背景

• 查詢：SELECT ... FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel
• 組合索引：(client_id, channel)
• client_id 定義為 VARCHAR(255)，實際存純數字。

問題現象

查詢 1：數字字面值（觸發隱含轉換）

mysql> EXPLAIN SELECT ... WHERE client_id = 1301 ...;
+----+------+-------+---------+------+----------+------+-------+------+
| key_len | ref  | rows | Extra |
+----+------+-------+---------+------+----------+------+-------+------+
| 1032 | NULL | 20207319 | Using where; Using index |

實際執行：11.69 秒

key_len=1032、ref=NULL、rows=2000萬，顯示為全索引掃描。

查詢 2：字串字面值（型別匹配）

mysql> EXPLAIN SELECT ... WHERE client_id = '1301' ...;
+----+------+-------+---------+------+----------+------+-------+------+
| key_len | ref  | rows | Extra |
+----+------+-------+---------+------+----------+------+-------+------+
| 258  | const| 457184 | Using where; Using index |

實際執行：0.25 秒

key_len=258、ref=const、rows=45萬，走索引等值查找，效能提升 46 倍。

根因分析

1. 隱含型別轉換：VARCHAR 欄位比較數字字面值，MySQL 將欄位值逐行轉為數字再比較，導致無法使用索引定位（同理：WHERE int_col = '123' 也會轉換）。
2. 索引長度膨脹：VARCHAR(255) 實際索引長度遠超所需，增加 I/O 與記憶體壓力。
3. 成本優化器誤判：轉換成本導致優化器選擇全掃描而非範圍查找。

進一步優化：改用 INT UNSIGNED

若 client_id 純數字，定義為 INT UNSIGNED（4 字節），索引長度從 258 降至 4，rows 估算進一步下降，I/O 極大幅降低。

最佳實務清單

1. 依業務語義選型：純數字用整數型（TINYINT/SMALLINT/INT/BIGINT），固定長度用 CHAR，變長用 VARCHAR(實際最大長度)。
2. 拒絕通用 VARCHAR(255)：除非真有 255 字元需求。
3. 應用層統一字面值型別：SQL 參數化綁定時確保型別一致（Prepared Statements 自動處理）。
4. 小心 FLOAT/DOUBLE 索引：精度問題導致比較異常，貨幣金額務必用 DECIMAL。
5. 定期審查 SHOW CREATE TABLE：發現過度定義立即變更（配合 pt-online-schema-change 線上變更）。

結論：資料型別選擇是「贏在起跑點」的關鍵，隨意定義會將優化空間壓縮至零。從設計階段嚴格把關，事半功倍。