MySQL 索引失效的几个场景

文章目录[隐藏]

场景
例子

MySQL 索引失效通常发生在一些特定的查询场景中，这些场景包括但不限于对索引列使用函数或运算表达式、LIKE 通配符左侧开放、OR 条件筛选、类型不匹配或隐式转换等。

场景

以下是一些导致 MySQL 索引失效的常见场景：

函数操作：如果查询条件中对索引列使用了函数（如 ABS、YEAR 等），那么索引将无法被利用。
运算操作：在查询条件中对索引列进行算术运算（加、减、乘、除）也会导致索引失效。
LIKE 通配符左侧开放：使用 LIKE 模式匹配时，如果以通配符开头（即'%xx'），则索引不会被使用。
OR 条件筛选：当使用 OR 连接多个条件时，如果至少有一个条件未使用索引，可能会导致整个查询的索引失效。
类型不匹配：如果查询中的列与索引列数据类型不一致，或者进行了隐式类型转换，索引同样会失效。
联合索引非最左匹配：对于组合索引，如果查询没有包含组合索引的最左侧列，那么索引将不会被使用。
数据分布不均匀：当索引列的数据分布极度不均匀时，MySQL 可能会选择全表扫描而不是使用索引，比如索引列重复值太多。
IS NULL 和 IS NOT NULL：在索引列上使用 IS NULL 可能导致索引失效，因为索引不存储 null 值。而 IS NOT NULL 则可能走索引，但这取决于具体情况。

例子

以下是一些具体的例子，展示了 MySQL 索引失效的常见场景：

函数操作导致索引失效：
```
SELECT * FROM users WHERE YEAR(birthdate) = 1990;
```
在这个例子中，使用了 YEAR 函数对 birthdate 列进行操作，导致无法使用索引。
运算操作导致索引失效：
```
SELECT * FROM products WHERE price * 0.9 < 50;
```
在这个例子中，对 price 列进行了乘法运算，导致无法使用索引。
LIKE 通配符左侧开放导致索引失效：
```
SELECT * FROM customers WHERE name LIKE '%Smith';
```
在这个例子中，LIKE 模式以通配符开头，导致无法使用索引。
OR 条件筛选导致索引失效：
```
SELECT * FROM orders WHERE status = 'pending' OR customer_id > 1000;
```
在这个例子中，OR 条件筛选了 status 和 customer_id 两个列，如果其中一个条件未使用索引，则整个查询的索引可能失效。
类型不匹配导致索引失效：
```
SELECT * FROM employees WHERE salary = '5000';
```
在这个例子中，salary 列是整数类型，但查询中的值是字符串类型，导致索引失效。
联合索引非最左匹配导致索引失效：
```
SELECT * FROM orders WHERE order_date > '2022-01-01' AND total_amount > 1000;
```
假设 orders 表有一个组合索引(order_date, total_amount)，但查询没有包含 order_date 列，导致索引失效。
数据分布不均匀导致索引失效：
```
SELECT * FROM employees WHERE department_id = 100;
```
如果 employees 表中 department_id 列的数据分布极度不均匀，且大部分员工都属于一个部门，那么即使有索引，MySQL 也可能选择全表扫描而不是使用索引。