萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 字串索引效能陷阱與優化實戰

在 MySQL 中,字串索引與數值索引的效能差異常被忽視,卻可能造成數十倍的查詢速度落差。線上曾出現百萬筆資料、聯集查詢全走索引卻耗時 600+ ms 的慢查詢,將索引欄位從 VARCHAR(50) 改為 BIGINT 後,效能提升 30 倍。根本原因在於 B+ 樹搜尋時需進行大量比較操作,字串比較的 CPU 開銷遠高於整數。

為何不建議直接對字串欄位建立索引?

字串比較需逐字元檢查,且受字元集、排序規則影響,若欄位較長(如 URL、長文本),索引樹層級加深、節點變大,導致 I/O 與 CPU 雙重壓力。生產環境應優先考慮數值型主鍵或代理鍵。

若必須使用字串索引,兩大優化策略

1. 前綴索引——只索引最左 N 個字元

ALTER TABLE `t` ADD INDEX `idx_address_prefix` (`address`(8));
  • 建議 N <= 10,平衡選擇性與索引大小。
  • 限制:無法用於 ORDER BY,也無法實現索引覆蓋。

2. 雜湊索引——將字串轉為整數

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';
  • 雜湊衝突極低概率發生,第二條件保證精確性。
  • 適用於高基數、定長或可接受極小誤判的場景。

實戰案例:LIKE 前綴查詢返回相同結果

發現 city LIKE '任丘%' 與 city LIKE '商丘%' 返回相同資料,同理 臨安市% / 六安市%、臨海市 / 龍海市 等。經排查,原因是 city 欄位建立了前綴索引 INDEX(city(8)),而「任丘」與「商丘」前 8 位元組在 UTF-8 下相同(中文字佔 3 位元組,前 2 個字剛好 6 位元組,補齊 8 位元組導致前綴相同)。

解決方案

  1. 增加前綴長度至 12 以上(覆蓋 4 個中文字)。
  2. 改用雜湊索引或完整索引(INDEX(city(255)))。
  3. 若業務允許,改為精確匹配 = 而非 LIKE 'prefix%'。

總結

  • 字串索引慎用,優先用整數代理鍵。
  • 前綴索引需驗證選擇性,避免前綴衝突導致誤查。
  • 雜湊索引適合高基數精確查詢,需應用層配合雙重驗證。
copyright © 萬盛學電腦網 all rights reserved