Mysql索引失效场景

Mysql索引失效场景

联合索引不满足左前缀匹配原则：查询条件必须从联合索引的最左侧列开始连续匹配，跳过中间列或者不从最左侧列开始都会导致索引失效

示例（idx_user_age_name (age, name, status)）

1. 索引生效（匹配最左列 age） SELECT * FROM user WHERE age = 25;
2. 索引生效（连续匹配 左1 age + 左2 name） SELECT * FROM user WHERE age = 25 AND name = '张三';
3. 索引失效（跳过最左列 age，直接查询 name） SELECT * FROM user WHERE name = '张三';
4. 索引失效（列不连续，跳过 name，仅匹配 age，status 无法走索引） SELECT * FROM user WHERE age = 25 AND status = 1;

索引列进行函数/表达式运算：当查询中对索引列直接进行函数调用、算术运算、字符串拼接等表达式操作时，MySQL 无法使用该列的索引，会触发全表扫描

1. 算术运算：索引失效（对id做了+1运算） SELECT * FROM user WHERE id + 1 = 100;
2. 函数调用：索引失效（对create_time使用了DATE_FORMAT函数） SELECT * FROM user WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-14';
3. 字符串拼接：索引失效（对phone做了拼接操作） SELECT * FROM user WHERE CONCAT(phone, '_suffix') = '13800138000_suffix';

模糊查询以 % 开头：模糊查询 LIKE 中，若匹配模式以 % 开头，MySQL 无法利用索引的有序性进行匹配，会触发全表扫描；而 LIKE 'xxx%'（以常量开头）可以正常使用索引 索引列：name，

1. 索引失效（%开头）SELECT * FROM user WHERE name LIKE '%三';
2. 索引生效（常量开头，%结尾）SELECT * FROM user WHERE name LIKE '张%';

隐式类型转换：当索引列是字符串类型（VARCHAR/CHAR 等），但查询条件中传入的是数值类型（未加引号），MySQL 会进行隐式类型转换（相当于对索引列执行 CAST(column AS UNSIGNED) 函数），导致索引失效 索引列：phone（VARCHAR类型）

1. 未加引号，索引失效 SELECT * FROM user WHERE phone = 13800138000;
2. 正确写法：加引号，索引生效 SELECT * FROM user WHERE phone = '13800138000';

OR 连接的条件中存在非索引列：当使用 OR 连接多个查询条件时，若其中有一个条件对应的列没有创建索引，MySQL 会放弃使用所有索引，转而走全表扫描（因为 OR 逻辑需要合并结果集，非索引列会触发全表扫描，优化器认为直接全表扫描更高效） 示例（索引列：id，address 无索引）

1. 索引失效（address 无索引，OR 导致整体索引失效）SELECT * FROM user WHERE id = 100 OR address = '北京市';
2. 索引生效（两个条件对应的列均有索引）SELECT * FROM user WHERE id = 100 OR phone = '13800138000';

使用 != 或 <> 不等于操作符：对索引列使用 !=（不等于）或 <>（不等于）操作符时，MySQL 优化器通常会判断无法有效利用索引，转而走全表扫描（适用于所有数据类型，包括数值、字符串） 索引列：status，索引失效

1. 索引失效 SELECT * FROM user WHERE status != 1;
2. 索引失效 SELECT * FROM user WHERE name <> '张三';