重生之 MySQL 索引失效六大陷阱

作者：李健青 2025-04-02 00:00:04

書接上回，林淵盯著監(jiān)控屏上跳動的QPS 18500，突然發(fā)現(xiàn)商品搜索接口的???Handler_read_next???計數(shù)器每秒暴漲百萬次。" 這是全表掃描的死亡信號！"他抓起對講機(jī)："立刻降級推薦系統(tǒng)！"

書接上回，林淵盯著監(jiān)控屏上跳動的QPS 18500，突然發(fā)現(xiàn)商品搜索接口的Handler_read_next計數(shù)器每秒暴漲百萬次。"

這是全表掃描的死亡信號！"他抓起對講機(jī)："立刻降級推薦系統(tǒng)！"

技術(shù)總監(jiān)老吳卻按住他的手："活動還有1分鐘開始，現(xiàn)在降級等于自殺！"

機(jī)房突然陷入黑暗——過載的UPS觸發(fā)了熔斷保護(hù)。

陷阱一：類型轉(zhuǎn)換

故障現(xiàn)場

SELECT * FROM products 
WHERE category_id = '3'  -- 字段實(shí)際類型為INT
AND status = 1           -- 字段類型為ENUM('0','1')

揭示災(zāi)難路徑：

圖片

修復(fù)術(shù)：

-- 強(qiáng)制類型精確匹配
SELECT * FROM products 
WHERE category_id = CAST('3' AS SIGNED) 
AND status = CAST(1 AS CHAR)

陷阱二：函數(shù)操作

價格區(qū)間查詢

SELECT * FROM products
WHERE FLOOR(price/100)*100 = 500 -- 破壞索引有序性

B+樹結(jié)構(gòu)破壞驗(yàn)證：

圖片

陷阱三：最左前綴

復(fù)合索引idx_cat_status(category,status)失效現(xiàn)場：

SELECT * FROM products WHERE status=1

B+樹物理掃描路徑：

圖片

# 執(zhí)行計劃對比
全索引掃描: 230ms  
全表掃描:  380ms  # 因需要回表反而更慢

陷阱四：隱式字符集轉(zhuǎn)換

跨表查詢的隱藏炸彈：

SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE u.name='林淵'

字符集差異診斷：

圖片

解法：

ALTER TABLE users CONVERT TO CHARACTER SET utf8;

陷阱五：最左匹配

復(fù)合索引idx_time_status(create_time,status)失效案例：

SELECT * FROM logs 
WHERE status = 'SUCCESS'

陷阱六：索引選擇器

優(yōu)化器的致命誤判：

SELECT * FROM products 
WHERE category_id = 3 
AND is_hot = 1 
ORDER BY price DESC

索引選擇矩陣：

圖片

強(qiáng)制干預(yù)方案：

SELECT * FROM products 
FORCE INDEX(idx_category) 
WHERE category_id = 3 AND is_hot = 1 
ORDER BY price DESC

索引檢驗(yàn)工具包

# 索引有效性核驗(yàn)套件
mysql> SHOW INDEX FROM products WHERE Seq_in_index=1;
# 字符集沖突檢測
mysql> SELECT TABLE_NAME,COLUMN_NAME,COLLATION_NAME 
       FROM information_schema.COLUMNS 
       WHERE COLLATION_NAME NOT LIKE 'utf8%';
# 隱式轉(zhuǎn)換檢測
mysql> EXPLAIN EXTENDED SELECT ...;
mysql> SHOW WARNINGS;  # 查看轉(zhuǎn)換痕跡

總結(jié)

林淵在2003年的技術(shù)局限下，留下六大防御法則：

類型精確律：WHERE條件與字段類型絕對匹配
函數(shù)絕緣體：禁止在索引列包裹函數(shù)
左前綴鐵律：復(fù)合索引首字段必須參與查詢
字符集統(tǒng)一場：全庫字符集強(qiáng)制校驗(yàn)
范圍右側(cè)禁區(qū)：范圍查詢后字段不進(jìn)索引
優(yōu)化器馴化術(shù)：FORCE INDEX與覆蓋索引聯(lián)用

責(zé)任編輯：武曉燕來源：碼哥跳動

MySQL 索引失效