在 MySQL 中,字串索引與數值索引的效能差異常被忽視,卻可能造成數十倍的查詢速度落差。線上曾出現百萬筆資料、聯集查詢全走索引卻耗時 600+ ms 的慢查詢,將索引欄位從 VARCHAR(50) 改為 BIGINT 後,效能提升 30 倍。根本原因在於 B+ 樹搜尋時需進行大量比較操作,字串比較的 CPU 開銷遠高於整數。
字串比較需逐字元檢查,且受字元集、排序規則影響,若欄位較長(如 URL、長文本),索引樹層級加深、節點變大,導致 I/O 與 CPU 雙重壓力。生產環境應優先考慮數值型主鍵或代理鍵。
ALTER TABLE `t` ADD INDEX `idx_address_prefix` (`address`(8));
N <= 10,平衡選擇性與索引大小。ORDER BY,也無法實現索引覆蓋。ALTER TABLE `t` ADD COLUMN `address_key` BIGINT UNSIGNED NOT NULL DEFAULT 0;
UPDATE `t` SET `address_key` = CRC32(`address`); -- 或自定義雜湊函式
ALTER TABLE `t` ADD INDEX `idx_address_key` (`address_key`);
查詢時雙重驗證:
SELECT * FROM `t`
WHERE `address_key` = CRC32('beijing,china')
AND `address` = 'beijing,china';
發現 city LIKE '任丘%' 與 city LIKE '商丘%' 返回相同資料,同理 臨安市% / 六安市%、臨海市 / 龍海市 等。經排查,原因是 city 欄位建立了前綴索引 INDEX(city(8)),而「任丘」與「商丘」前 8 位元組在 UTF-8 下相同(中文字佔 3 位元組,前 2 個字剛好 6 位元組,補齊 8 位元組導致前綴相同)。
INDEX(city(255)))。= 而非 LIKE 'prefix%'。