MySQL索引失效场景

常见的导致索引失效的场景：

1、不使用索引列进行过滤：当查询中没有使用索引列作为过滤条件时，索引将无法发挥作用。例如，对于一个有索引的name列，如果查询中没有使用name作为过滤条件，而是使用其他列进行过滤，那么索引将无法生效。

-- 索引失效的情况
SELECT name FROM customers WHERE age > 30;

-- 索引生效的情况
SELECT name FROM customers WHERE name = 'John' AND age > 30;

2、列值使用函数或表达式进行操作：当查询中对索引列的值进行函数调用、类型转换或表达式计算时，索引可能无法被利用。因为索引是建立在原始列值上的，如果查询中对列值进行了操作，就无法直接使用索引进行匹配。

-- 索引失效的情况
SELECT name FROM customers WHERE UPPER(name) = 'JOHN';

-- 索引生效的情况
SELECT name FROM customers WHERE name = 'John';

3、数据表太小：当数据表中的记录非常少时，使用索引的效益可能会降低。对于小型表来说，全表扫描可能比使用索引更快速。

-- 索引失效的情况
SELECT name FROM small_table;

-- 索引生效的情况
SELECT name FROM large_table;

4、使用通配符或模糊查询：对于以通配符开头的查询模式（如LIKE ‘%keyword’）或模糊查询，索引通常无法被充分利用。因为通配符的存在，数据库需要扫描更多的索引项来匹配模式，导致索引失效。

-- 索引失效的情况
SELECT name FROM customers WHERE name LIKE '%John%';

-- 索引生效的情况
SELECT name FROM customers WHERE name LIKE 'John%';

5、OR操作符：当查询中包含OR操作符连接多个条件时，如果这些条件涉及到不同的列，索引可能无法被充分利用。对于OR操作符，数据库通常只能使用一个索引，而无法同时利用多个索引。

-- 索引失效的情况
SELECT name FROM customers WHERE age > 30 OR city = 'New York';

-- 索引生效的情况
SELECT name FROM customers WHERE age > 30 AND city = 'New York';

6、高基数列：当列中有大量重复值（低基数）时，索引的选择性下降，可能导致索引失效。索引的选择性指的是索引列不重复值的比例，选择性越高，索引的效果越好。

7、数据表过度索引：当数据表上有过多的索引时，维护这些索引的开销会增加，并且可能导致数据库选择不合适的索引而影响查询性能。