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

来源丨经授权转自码哥跳动（ID：MageByte）

作者丨李健青@码哥字节

书接上回，林渊盯着监控屏上跳动的QPS 18500，突然发现商品搜索接口的Handler_read_next计数器每秒暴涨百万次。'

这是全表扫描的死亡信号！'他抓起对讲机：'立刻降级推荐系统！'

技术总监老吴却按住他的手：'活动还有1分钟开始，现在降级等于自杀！'

机房突然陷入黑暗——过载的UPS触发了熔断保护。

陷阱一：类型转换

▎故障现场

SELECT * FROM products 
WHERE category_id = '3'  -- 字段实际类型为INT
ANDstatus = 1           -- 字段类型为ENUM('0','1')

揭示灾难路径：

修复术：

-- 强制类型精确匹配
SELECT * FROM products 
WHERE category_id = CAST('3'AS SIGNED) 
ANDstatus = CAST(


    
1ASCHAR)

陷阱二：函数操作

▎价格区间查询

SELECT * FROM products
WHEREFLOOR(price/100)*100 = 500-- 破坏索引有序性

B+树结构破坏验证：

陷阱三：最左前缀

复合索引idx_cat_status(category,status)失效现场：

SELECT * FROM products WHEREstatus=1

B+树物理扫描路径：

# 执行计划对比
全索引扫描: 230ms  
全表扫描:  380ms  # 因需要回表反而更慢

陷阱四：隐式字符集转换

跨表查询的隐藏炸弹：

SELECT * FROM orders o 
JOINusers u ON o.user_id = u.id 
WHERE u.name='林渊'

字符集差异诊断：

解法：

ALTERTABLEusersCONVERTTOCHARACTERSET utf8;

陷阱五：最左匹配

复合索引idx_time_status(create_time,status)失效案例：

SELECT * FROMlogs
WHEREstatus = 'SUCCESS'

陷阱六：索引选择器

优化器的致命误判：

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

索引选择矩阵：

强制干预方案：

SELECT * FROM products 
FORCEINDEX(idx_category) 
WHERE category_id = 3AND is_hot = 1
ORDERBY 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条件与字段类型绝对匹配
函数绝缘体：禁止在索引列包裹函数
左前缀铁律：复合索引首字段必须参与查询
字符集统一场：全库字符集强制校验
范围右侧禁区：范围查询后字段不进索引
优化器驯化术：FORCE INDEX与覆盖索引联用


1、听说 TypeScript7.0 要用 Go 写？
2、太酷了！专为程序员设计！VSCode 完美使用！
3、MQ的数据一致性，如何保证？
4、操作系统是如何一步步发明中断机制的？
5、Linux 内核“偷吃”了你的内存！