索引失效详解

索引失效详解

索引失效详解

一、索引列被函数 / 表达式操作

原因：当索引列作为函数参数或参与表达式计算时，MySQL 无法直接使用索引的有序性进行快速定位，必须逐行计算后比较，导致索引失效。

-- 索引列name被函数SUBSTR操作，索引失效
SELECT * FROM user WHERE SUBSTR(name, 1, 3) = '张';
-- 索引列price参与表达式计算，索引失效
SELECT * FROM goods WHERE price * 2 > 1000;
-- 隐式类型转换（相当于对索引列使用函数），索引失效
-- 假设phone是varchar类型，查询时用数字匹配，触发转换：CAST(phone AS UNSIGNED) = 13800138000
SELECT * FROM user WHERE phone = 13800138000;

例外：若使用表达式索引（MySQL 8.0 + 支持），对函数 / 表达式本身建立索引，则不会失效：

-- 创建表达式索引
CREATE INDEX idx_name_sub ON user (SUBSTR(name, 1, 3));
-- 此时查询会使用索引
SELECT * FROM user WHERE SUBSTR(name, 1, 3) = '张';

二、模糊查询以%开头

原因：LIKE '%xxx'或LIKE '%xxx%'会导致索引无法利用前缀匹配特性（索引是有序的，需从首字符开始匹配），只能全表扫描。

-- 索引列name上的索引失效
SELECT * FROM user WHERE name LIKE '%三'; -- 以%开头
SELECT * FROM user WHERE name LIKE '%三%'; -- 包含%在中间

-- 例外：前缀匹配（%在末尾），索引有效
SELECT * FROM user WHERE name LIKE '张%'; -- 从首字符开始匹配，可使用索引

三、使用否定操作符

原因：否定操作（如!=、<>, NOT IN, NOT EXISTS等）会破坏索引的范围查询特性，优化器可能认为全表扫描更高效（尤其当不满足条件的数据占比高时）。

-- 索引列age上的索引失效
SELECT * FROM user WHERE age != 20;
SELECT * FROM user WHERE age <> 20;

-- NOT IN导致索引失效
SELECT * FROM user WHERE age NOT IN (20, 30);

-- NOT EXISTS可能导致关联表的索引失效
SELECT * FROM user u WHERE NOT EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

查看没有利用到索引的库表：

SELECT * FROM sys.schema_unused_indexes;