索引失效的场景

数据类型不匹配

 

- 示例：在一个存储员工信息的 employees 表中， employee_id 列是 int 类型且有索引，若执行查询 SELECT * FROM employees WHERE employee_id = '1001' ，将数字类型的 employee_id 与字符串 '1001' 进行比较，索引会失效。

- 原因：就好比你在一个按数字顺序排列的文件柜中找文件，每个文件都标有数字编号。现在你却拿着一个写有数字的纸条，纸条上的数字是文本格式，文件柜的“检索系统”就会懵圈，不知道该怎么用这个文本数字去匹配数字编号的文件，所以索引就没办法发挥作用了。

 

使用函数或表达式

 

- 示例：在 books 表中， book_title 列有索引，执行 SELECT * FROM books WHERE SUBSTRING(book_title, 1, 5) = 'Java ' ，对 book_title 使用了 SUBSTRING 函数，索引将无法有效利用。

- 原因：可以把索引想象成一本字典的目录，它是按照原始的单词顺序来编排的。现在你要用字典里单词的一部分去查找，就好比打乱了目录的顺序，字典就不知道该怎么通过原来的目录快速找到你要的内容了，所以索引就失效了。

 

范围查询中使用 OR 

 

- 示例：在 sales 表中， amount 列有索引，执行 SELECT * FROM sales WHERE amount > 1000 OR amount < 500 ，这种 OR 连接的范围查询可能使索引失效。

- 原因：索引就像一条有方向的路，它可以快速带你找到大于某个值或者小于某个值的记录。但当你用 OR 连接两个范围时，就好像让这条路同时有了两个相反的方向，数据库不知道该优先走哪条路，所以就很难利用索引来快速查询了。

 

数据分布不均匀

 

- 示例：在 classes 表中， class_type 列有索引，大部分学生都在 '普通班' ，只有少数学生在其他类型班级。执行 SELECT * FROM classes WHERE class_type = '普通班' 时，索引效果不佳，甚至可能在某些查询优化策略下索引失效。

- 原因：想象索引是一个分层的书架，每层放不同类型的书。如果大部分书都放在同一层，其他层只有很少的书，那么当你要找那层放满书的书时，索引这个书架的分层结构就不太起作用了，因为你还是得在那一层里一本一本地找，就像没有索引一样。

 

模糊查询以通配符开头

 

- 示例：在 addresses 表中， street_name 列有索引，执行 SELECT * FROM addresses WHERE street_name LIKE '%大道' ，由于 % 在开头，索引会失效。

- 原因：索引就像是一个按照字母顺序排列的通讯录，你要找名字以某个字开头的人很容易。但现在你要找名字最后几个字是特定内容的人，通讯录就没办法按照原来的顺序帮你快速找到了，因为它不知道该从哪里开始找起，所以索引就用不上了。

 

连接条件未使用索引

 

- 示例：有 orders 表和 customers 表，通过 customer_id 连接，若这两表的 customer_id 列都没有建立索引，执行 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id 时，连接操作可能无法利用索引。

- 原因：可以把两个表想象成两个独立的图书馆，每个图书馆都有自己的图书编号系统。现在要把两个图书馆中编号相同的书找出来放在一起，如果这两个编号系统都没有建立索引，就相当于没有一个快速查找的方法，只能一本一本地比对，效率就会很低，索引也就无法发挥作用了。

 

索引维护不当

 

- 示例：在 products 表中， product_name 列的索引长期未维护，碎片过多。执行 SELECT * FROM products WHERE product_name LIKE '电%' 时，可能会因为索引结构混乱，导致数据库无法有效使用索引来查询数据。

- 原因：索引就像一个整理得井井有条的仓库货架，每个货物都有固定的位置，方便快速找到。但如果长期不整理，货架变得乱七八糟，货物摆放混乱，当你要找特定货物时，就很难通过原来的货架布局快速找到了，索引也就失去了它的高效查找作用。