mysql索引失效场景

MySQL 索引在特定查询场景下可能失效，导致 MySQL 不使用索引，进而增加查询的时间和资源消耗。理解索引失效的场景和原因，有助于避免不必要的全表扫描并提升查询性能。下面详细讲解 MySQL 索引失效的常见场景及具体原因。

1. 不符合最左前缀原则

MySQL 在使用复合索引（即包含多个列的索引）时，必须遵循最左前缀原则，即查询条件必须从索引的最左列开始并按顺序进行匹配。如果查询跳过了最左列，或者查询中间的列缺失，索引会失效。

示例

假设我们有如下表结构和复合索引：

sqlCopy codeCREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    INDEX idx_name_age_department (first_name, age, department)
);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE first_name = 'Alice'; -- 使用索引 SELECT * FROM employees WHERE first_name = 'Alice' AND age = 30; -- 使用索引 SELECT * FROM employees WHERE first_name = 'Alice' AND age = 30 AND department = 'HR'; -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE age = 30; -- 跳过了最左列 first_name，索引失效 SELECT * FROM employees WHERE age = 30 AND department = 'HR'; -- 跳过了最左列 first_name，索引失效

原因：复合索引必须从最左列开始依次匹配才能生效。未按照顺序使用索引列时，MySQL 无法按顺序扫描索引，导致索引失效。

2. 在索引列上使用函数或表达式

如果在查询条件中对索引列使用了函数、运算符或表达式，索引会失效，因为 MySQL 无法对索引列的变换结果进行优化。

示例

假设我们有一个表 employees，索引创建如下：

sqlCopy codeCREATE INDEX idx_age ON employees(age);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE age = 30; -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE YEAR(birth_date) = 1990; -- 假设 age 是通过 birth_date 计算得来 SELECT * FROM employees WHERE age + 1 = 31; -- age 列使用了表达式，索引失效

原因：MySQL 索引结构只存储字段原始值，如果在条件中使用函数或表达式，优化器无法直接定位到符合条件的索引项，必须进行全表扫描。

3. 对字符串使用不带通配符前缀的 LIKE 查询

在字符串类型的索引列上使用 LIKE 模糊匹配时，如果查询模式以通配符 % 开头，则 MySQL 无法利用索引。

示例

假设有如下索引：

sqlCopy codeCREATE INDEX idx_firstname ON employees(first_name);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE first_name LIKE 'Ali%'; -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE first_name LIKE '%ice'; -- 索引失效 SELECT * FROM employees WHERE first_name LIKE '%li%'; -- 索引失效

原因：MySQL 索引是按顺序存储的，当 LIKE 模式以 % 开头时，无法确定字符串的起始部分，从而无法使用索引加速查询。

4. 对索引列使用不等于（!= 或 <>）或 NOT IN

在使用不等于（!= 或 <>）或 NOT IN 运算符时，MySQL 可能会放弃使用索引，因为这些运算符会导致匹配结果不连续，无法通过索引快速筛选出符合条件的行。

示例

假设有如下索引：

sqlCopy codeCREATE INDEX idx_age ON employees(age);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE age = 30; -- 使用索引 SELECT * FROM employees WHERE age IN (25, 30, 35); -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE age != 30; -- 索引失效 SELECT * FROM employees WHERE age NOT IN (25, 30, 35); -- 索引失效

原因：不等于或 NOT IN 会导致 MySQL 需要检查大量不连续的值，索引扫描的效率低，因此放弃索引。

5. 在索引列上使用 IS NULL 或 IS NOT NULL

在某些版本的 MySQL 中（尤其是较旧的版本），对索引列进行 IS NULL 或 IS NOT NULL 判断可能会导致索引失效，特别是在复合索引的非最左列上。

示例

假设有如下索引：

sqlCopy codeCREATE INDEX idx_department_age ON employees(department, age);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE department = 'HR' AND age IS NULL; -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE age IS NULL; -- 索引失效

原因：在一些情况下，MySQL 对 IS NULL 或 IS NOT NULL 判断不支持索引优化，必须对每行进行扫描。

6. 范围条件后面的列索引失效

在复合索引中，当 WHERE 子句包含范围条件（如 >, <, BETWEEN）时，索引会在范围条件后中断，导致后续列无法使用索引。

示例

假设有如下索引：

sqlCopy codeCREATE INDEX idx_firstname_age_department ON employees(first_name, age, department);

查询示例：

• 索引有效：sqlCopy codeSELECT * FROM employees WHERE first_name = 'Alice' AND age = 30 AND department = 'HR'; -- 使用索引
• 索引失效：sqlCopy codeSELECT * FROM employees WHERE first_name = 'Alice' AND age > 30 AND department = 'HR'; -- department 列无法使用索引

原因：范围条件会导致索引扫描停止在该列，后续列无法再使用索引优化。

7. 数据分布不均，使用索引反而影响性能

如果某列的数据分布极不均匀，例如在布尔型或状态型字段中，大部分行的值相同（如 status 字段值 90% 都是 0），使用索引反而可能导致 MySQL 优化器放弃索引。

示例

假设有一个布尔型字段 status，其值在大多数行中都为 0，很少为 1。此时即使为 status 建立了索引，查询条件如下：

sqlCopy codeSELECT * FROM employees WHERE status = 0;

原因：如果索引中包含大量重复值，查询引擎使用索引可能需要扫描大量记录，不如直接进行全表扫描效率高。在这种情况下，优化器会选择全表扫描而非使用索引。

8. 隐式类型转换

当查询条件的数据类型与索引列的数据类型不一致时，MySQL 会进行隐式类型转换。此转换可能导致索引失效。

示例

假设 order_id 是字符串字段

CREATE TABLE fiat_payment_order (
  order_id VARCHAR(64) NOT NULL,
  ...
  KEY idx_order_id(order_id)
);

查询语句分析

SELECT * FROM fiat_payment_order WHERE order_id = 123;

你可能觉得：

“123 是个很小的值，order_id 是字符串，它完全可以匹配 '123' 吧？应该能走索引吧？”

但实际上，MySQL 很可能不会用到索引，走的是全表扫描！

MySQL 的隐式类型转换规则：

在 WHERE order_id = 123 中：

• order_id 是 VARCHAR
• 123 是 数值型字面量（INT）

所以 MySQL 会尝试 将字符串字段 order_id 转换为数值再比较，这才是关键！

🚨 不是把 123 转成 '123'，而是把 order_id 这一列的值都转成数字再对比！

为什么索引会失效？

因为列的类型被转换了（从 VARCHAR 转成了 INT）！

这导致：

• B-Tree 索引按字符串构建
• 但比较时变成了数值比较
• 索引无法使用，必须对每一行做 函数运算后比较
• 所以变成了 全表扫描（type = ALL）

总结

MySQL 索引失效的原因主要包括：不符合最左前缀原则、对索引列使用函数或表达式、LIKE 模糊查询、使用不等于、范围条件、隐式类型转换等情况。理解这些场景有助于合理编写 SQL 查询和设计索引结构，以充分发挥 MySQL 索引的性能。