面试官:MySQL索引失效有没有遇到过?有哪些解决办法?

2,154 阅读3分钟

血的教训啊,掘友们,连续两次面试问到了,由于第一次没复盘整理,第二次也寄了

MySQL索引是提高查询效率的重要手段之一,但是当索引失效时,查询效率会大幅降低,严重影响应用的性能。本文将介绍一些常见的索引失效场景以及相应的解决方法。

索引失效场景

1. 全表扫描

如果表太小,使用索引查找反而会变慢,因为MySQL需要额外的开销来读取索引,然后再到磁盘上访问表数据。例如:

SELECT * FROM small_table WHERE id = 10;

对于小表small_table,如果它只有几条记录,那么使用索引查找反而会变慢。可以通过适当取消索引或者插入大批数据后再加上索引来解决。

2. 使用复杂表达式作为WHERE条件

如果使用函数、运算符等复杂表达式作为WHERE条件,MySQL无法使用索引来优化查询。例如:

SELECT * FROM user WHERE year(birthday) = 1990;

在上述查询中,year(birthday)是一个复杂表达式,MySQL无法直接使用索引来优化。可以将它们转换为常量或者附加列来解决,例如:

SELECT * FROM user WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

3. 使用LIKE进行模糊匹配

如果使用LIKE进行模糊匹配的WHERE条件,如果LIKE条件的开头是通配符(如“%test”),那么MySQL也无法使用索引。例如:

SELECT * FROM product WHERE name LIKE '%test%';

在上述查询中,MySQL无法使用索引来优化,可以使用全文搜索或者避免使用通配符开头来解决。

4. 使用OR连接的多个WHERE条件

如果使用OR连接的多个WHERE条件,MySQL在某些情况下可能无法使用索引,从而无法进行优化。例如:

SELECT * FROM user WHERE name = 'Jack' OR email = 'jack@example.com';

在上述查询中,MySQL无法同时利用name列和email列上的索引来优化。可以使用UNION或者子查询来替代,例如:

SELECT * FROM user WHERE name = 'Jack'
UNION
SELECT * FROM user WHERE email = 'jack@example.com';

回答整理

MySQL索引失效会导致查询效率下降,常见的失效场景有:

  1. 对于小表进行全表扫描,如果表太小,那么使用索引查找反而会变慢,因为MySQL需要额外的开销来读取索引,然后再到磁盘上访问表数据。
  2. 对于使用函数、运算符等复杂表达式的WHERE条件,MySQL无法使用索引来优化查询。
  3. 对于使用LIKE进行模糊匹配的WHERE条件,如果LIKE条件的开头是通配符(如“%test”),那么MySQL也无法使用索引。
  4. 对于使用OR连接的多个WHERE条件,MySQL在某些情况下可能无法使用索引,从而无法进行优化。

解决方法:

  1. 适当调整表结构,对于小表可以取消索引,插入大批数据后再加上索引。
  2. 尽量避免使用复杂表达式作为WHERE条件,可以将它们转换为常量或者附加列。
  3. 在使用LIKE进行模糊匹配的WHERE条件中,尽量避免以通配符开头,或者使用全文搜索。
  4. 对于使用OR连接的多个WHERE条件,可以使用UNION或者子查询来替代。