索引失效场景

索引失效场景

目录

• 索引失效场景
  • 对索引列进行函数或运算操作
  • 隐式类型转换
  • 使用 !=、<>、NOT IN、NOT EXISTS
  • 使用 LIKE 左模糊匹配
  • 索引列参与 OR 条件
  • 复合索引未遵循最左前缀原则
  • 使用 IN 参数过多

索引是建立在列“原始值”上的有序结构。只要你的操作没有改变这个“原始值”本身（无论是计算、函数还是类型转换），索引就能正常工作；一旦改变了，索引就帮不上忙了。

对索引列进行函数或运算操作

-- 失效：对索引列使用函数
WHERE YEAR(create_time) = 2024

-- 有效：改写为范围查询
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'

-- 失效：对索引列进行运算
WHERE id + 1 = 100

-- 有效：将运算移到右侧
WHERE id = 100 - 1

隐式类型转换

-- 假设 phone 是 varchar 类型，传入数字会导致隐式转换
WHERE phone = 13800138000    -- 失效
WHERE phone = '13800138000'  -- 有效

使用 !=、<>、NOT IN、NOT EXISTS

-- 这些操作通常会导致全表扫描
WHERE status != 1
WHERE id NOT IN (1, 2, 3)

使用 LIKE 左模糊匹配

WHERE name LIKE '%张三'   -- 失效（以%开头）
WHERE name LIKE '张三%'   -- 有效（以%结尾）
WHERE name LIKE '%张三%'  -- 失效

索引列参与 OR 条件

-- 如果 OR 两侧条件不都使用索引，可能导致失效
WHERE id = 1 OR name = '张三'  -- 如果 name 无索引，id 索引可能失效

复合索引未遵循最左前缀原则

-- 假设索引为 (a, b, c)
WHERE b = 1 AND c = 2       -- 失效，缺少 a
WHERE a = 1 AND c = 2       -- 部分有效，只用到了 a
WHERE c = 1                 -- 失效

使用 IN 参数过多

-- IN 列表过长可能导致优化器放弃索引
WHERE id IN (1, 2, 3, ..., 10000)  -- 大量值