MySQL索引系列--索引失效的原因/解决方案

女爷i 2023-10-02 14:33 24阅读 0赞

原文网址：[MySQL索引系列--索引失效的原因/解决方案\_IT利刃出鞘的博客-CSDN博客][MySQL_--_IT_-CSDN]

## 简介 ##

本文介绍数据库什么时候会索引失效以及如何避免索引失效。

这个问题也是Java后端面试中常见的问题。

## 失效的场景 ##

1.  LIKE 以%或者\_开头
    
    1.  %和\_这两个是模糊匹配，如果放在开头则不走索引。
    2.  详见：[MySQL--索引的优化--LIKE模糊查询\_IT利刃出鞘的博客-CSDN博客][MySQL--_--LIKE_IT_-CSDN]
2.  OR 语句前后没有同时使用索引
    
    1.  当OR 左右查询字段只有一个是索引，该索引失效，只有当OR 左右查询字段均为索引时，才会生效。
3.  联合索引没遵循最左前缀原则
    
    1.  见：[MySQL--联合索引--使用/原理/优化\_IT利刃出鞘的博客-CSDN博客][MySQL--_--_IT_-CSDN]
4.  索引列数据类型出现隐式转化
    
    1.  varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。  
        例如：tb1表中name字段是字符串类型
        
        1.  索引失效：SELECT \* FROM tb1 WHERE name = 123
        2.  索引有效：SELECT \* FROM tb1 WHERE name = '123'
5.  对索引列进行计算或使用函数
    
    1.  原因：索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了。
    2.  示例：
        
        1.  SELECT \* FROM t WHERE id\*3=3000
        2.  SELECT \* FROM t WHERE ABS(id)=3000
6.  ORDER BY使用错误
    
    1.  见：[MySQL--索引的优化--ORDER BY\_IT利刃出鞘的博客-CSDN博客][MySQL--_--ORDER BY_IT_-CSDN]
7.  全表扫描速度比索引速度快
    
    1.  此时mysql会使用全表扫描，索引失效。
    2.  例如：
        
        1.  数据量极少的表
        2.  表中索引字段数据重复率太高
8.  WHERE 子句中使用参数（可能失效）
    
    1.  可能会导致索引失效，与版本有关。
    2.  原因：SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。此语句将进行全表扫描（可能）：SELECT id FROM t WHERE num = @num
    3.  解决方案：强制查询使用索引：SELECT id FROM t WITH(INDEX(索引名)) WHERE num = @num

## 与版本有关的情况 ##

上边只是部分内容，为便于维护，本文已迁移到此地址：[MySQL-索引失效的原因/解决方案 - 自学精灵][MySQL-_ -]

[MySQL_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/121239921
[MySQL--_--LIKE_IT_-CSDN]: https://knife.blog.csdn.net/article/details/122823454
[MySQL--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/122829813
[MySQL--_--ORDER BY_IT_-CSDN]: https://knife.blog.csdn.net/article/details/122823127
[MySQL-_ -]: https://learn.skyofit.com/archives/610