重生之 MySQL 索引失效六大陷阱
作者：李健青
書接上回，林淵盯著監(jiān)控屏上跳動的QPS 18500，突然發(fā)現(xiàn)商品搜索接口的???Handler_read_next???計數(shù)器每秒暴漲百萬次。"
這是全表掃描的死亡信號！"他抓起對講機："立刻降級推薦系統(tǒng)！"
書接上回，林淵盯著監(jiān)控屏上跳動的QPS 18500，突然發(fā)現(xiàn)商品搜索接口的Handler_read_next計數(shù)器每秒暴漲百萬次。"
這是全表掃描的死亡信號！"他抓起對講機："立刻降級推薦系統(tǒng)！"
技術總監(jiān)老吳卻按住他的手："活動還有1分鐘開始，現(xiàn)在降級等于自殺！"
機房突然陷入黑暗——過載的UPS觸發(fā)了熔斷保護。
陷阱一：類型轉換
故障現(xiàn)場
SELECT * FROM products
WHERE category_id = '3' -- 字段實際類型為INT
AND status = 1 -- 字段類型為ENUM('0','1')揭示災難路徑：
圖片
修復術：
-- 強制類型精確匹配
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+樹結構破壞驗證：
圖片
陷阱三：最左前綴
復合索引idx_cat_status(category,status)失效現(xiàn)場：
SELECT * FROM products WHERE status=1B+樹物理掃描路徑：
圖片
# 執(zhí)行計劃對比
全索引掃描: 230ms
全表掃描: 380ms # 因需要回表反而更慢陷阱四：隱式字符集轉換
跨表查詢的隱藏炸彈：
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.name='林淵'字符集差異診斷：
圖片
解法：
ALTER TABLE users CONVERT TO CHARACTER SET utf8;陷阱五：最左匹配
復合索引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索引選擇矩陣：
圖片
強制干預方案：
SELECT * FROM products
FORCE INDEX(idx_category)
WHERE category_id = 3 AND is_hot = 1
ORDER BY price DESC索引檢驗工具包
# 索引有效性核驗套件
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%';
# 隱式轉換檢測
mysql> EXPLAIN EXTENDED SELECT ...;
mysql> SHOW WARNINGS; # 查看轉換痕跡總結
林淵在2003年的技術局限下，留下六大防御法則：
- 類型精確律：WHERE條件與字段類型絕對匹配
- 函數(shù)絕緣體：禁止在索引列包裹函數(shù)
- 左前綴鐵律：復合索引首字段必須參與查詢
- 字符集統(tǒng)一場：全庫字符集強制校驗
- 范圍右側禁區(qū)：范圍查詢后字段不進索引
- 優(yōu)化器馴化術：FORCE INDEX與覆蓋索引聯(lián)用
責任編輯：武曉燕
來源：
碼哥跳動
