MySQL索引失效场景


MySQL 索引优化是提升查询性能的关键手段之一,但有时使用不当会导致索引失效。今天我们一起来看看哪些情况下索引会失效。

1、联合索引没有使用最左前缀

  • 失效示例:联合索引 (a,b,c)

    SELECT * FROM table WHERE b=1 AND c=2;  --  索引失效
  • 正确写法: 

    WHERE a = ?  -- 
    WHERE a = ? AND b = ?  -- 
    WHERE a = ? AND b = ? AND c = ?  -- 
    -- ps:MySQL 对 = 条件的列,优化器会按索引顺序重组 WHERE 条件,比如:
    WHERE b = ? AND a = ? AND c = ?  --  也是会走索引的

2、在索引列上使用函数或运算

  • 失效示例

    SELECT * FROM orders WHERE YEAR(create_time) = 2025;  --   索引失效
  • 正确写法: 

    -- 改为范围查询
    SELECT * FROM orders 
    WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31';  --

3、隐式类型转换

  • 失效示例:字段类型与查询值类型不一致

    -- user_id 是 VARCHAR 类型
    SELECT * FROM users WHERE user_id = 1001;  --  索引失效(数字转字符串,MySQL 需将列值转为数字再比较,无法走索引)
  • 正确写法: 

    SELECT * FROM users WHERE user_id = '1001';  --  保持类型一致

4、LIKE查询左边加了通配符%

  • 失效示例:

    SELECT * FROM users WHERE name LIKE '%王';  --  索引失效
  • 正确写法:

    SELECT * FROM users WHERE name LIKE '王%';  --  可以使用索引

5、OR 连接非索引列

  • 失效示例:

    -- age 有索引,address 无索引
    SELECT * FROM users WHERE age > 25 OR address = '北京';  --  索引失效
  • 正确写法:

    -- 拆分成 UNION
    SELECT * FROM users WHERE age > 25 
    UNION
    SELECT * FROM users WHERE address = '北京';  --

6、使用 IS NULL / IS NOT NULL

SELECT * FROM users WHERE name IS NULL;  --  通常能用索引
SELECT * FROM users WHERE name IS NOT NULL;  --  索引不一定用,通常不能用

7、NOT IN / NOT EXISTS

  • 失效示例:

    SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); --  索引失效
  • 正确写法:

    -- 改用 LEFT JOIN
    SELECT u.* FROM users u
    LEFT JOIN blacklist b ON u.id = b.user_id
    WHERE b.user_id IS NULL;  --

有时我们会发现,明明是正确的使用方法,但是看执行计划还是没走索引。有可能是数据量比较少时,MySQL自带的优化器认为全表扫描更快。当然,索引失效的情况,我只是列举了几种常见的。还有 重复索引、索引统计信息过期、范围查询中断联合索引 等等,也会导致索引失效。我们可以根据具体情况进行分析,对于执行计划的解释的,大家可以参考另一篇博文 MySQL EXPLAIN 关键字详解

资本低潮终究会过去。– 烟沙九洲

  ◀   ◀   ◀