MySQL 大小寫敏感查詢高性能方案：BINARY 陷阱與 COLLATE utf8_bin 最佳實踐

MySQL 默認大小寫不敏感（utf8_general_ci）。需區分大小寫時，常見 BINARY col = 'value' 雖能生效，但導致索引失效、全表掃描。本文對比 BINARY、列級 BINARY 屬性、COLLATE utf8_bin 三種方案，通過 EXPLAIN 實測證明 COLLATE utf8_bin 可走索引、性能優異，是生產環境首選。

一、默認行為：大小寫不敏感

SELECT * FROM t WHERE col LIKE 'a%';  -- 等價於 'A%'

二、三種區分大小寫方案對比

1. 查詢級 BINARY 關鍵字（不推薦）

SELECT * FROM t WHERE BINARY col = 'value';

缺陷：BINARY 將列值轉為二進制字符串比較，**導致索引失效**，EXPLAIN 顯示 type=ALL 全表掃描。30 萬行測試耗時秒級。

2. 列定義級 BINARY 屬性（不推薦）

CREATE TABLE t (col VARCHAR(20) BINARY);

缺陷：同樣導致該列索引無法用於範圍/排序/Join，僅支援精確全值匹配，通用性極差。

3. COLLATE utf8_bin 指定校對規則（推薦）

SELECT * FROM t WHERE col = 'value' COLLATE utf8_bin LIMIT 1;

優勢：保持索引可用，EXPLAIN 顯示 type=ref/const、key=idx_col，毫秒級返回。

三、原理解析

• BINARY 強制按字節比較，破壞索引的字符序邏輯，優化器放棄索引。
• COLLATE utf8_bin 僅改變比較規則（按字節碼點序），索引的 B+ 樹結構順序與之兼容，優化器可正常使用索引。
• utf8_bin 區分大小寫、區分重音、不做字符展開，嚴格按 Unicode 碼點排序。

四、最佳實踐建議

1. 列級默認校對：建表時指定 CHARSET=utf8mb4 COLLATE=utf8mb4_bin，全表天然大小寫敏感，無需查詢級干預。
2. 查詢級顯式指定：既有表無法改結構時，用 WHERE col = 'val' COLLATE utf8mb4_bin（注意用 utf8mb4 兼容 Emoji）。
3. 避免函數包裹列：UPPER(col) = 'VAL' 同樣導致索引失效，改用 col = 'VAL' COLLATE utf8mb4_bin。
4. Join/排序一致性：關聯條件、ORDER BY 同樣加 COLLATE 保持一致，避免隱式轉換導致 filesort。

五、總結

大小寫敏感查詢請堅決使用 COLLATE utf8mb4_bin，拒絕 BINARY 關鍵字與列屬性。索引可用性是高性能查詢的生命線，COLLATE 在語義正確與索引友好間達成了完美平衡。