MySQL索引失效的十种常见写法与Explain执行计划分析

为了深入理解MySQL索引失效的原因，我们需要构建一个标准的测试环境，并通过 EXPLAIN 命令观察执行计划的变化。以下指南将直接展示具体的SQL写法及其背后的逻辑。

准备测试环境

首先，创建一张名为 user 的表，包含 id、name、age、phone 和 position 字段，并建立一个联合索引 idx_name_age_position。

CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  phone VARCHAR(20),
  position VARCHAR(50),
  KEY idx_name_age_position (name, age, position)
);

-- 插入若干测试数据
INSERT INTO user (name, age, phone, position) VALUES 
('Alice', 25, '13800138000', 'Developer'),
('Bob', 30, '13800138001', 'Manager'),
('Charlie', 28, '13800138002', 'Designer'),
('David', 35, '13800138003', 'Tester');

在开始分析前，了解 EXPLAIN 输出结果中的关键列：

• type：访问类型，ALL 代表全表扫描，ref 或 range 代表使用了索引。
• key：实际使用的索引列名，为 NULL 表示未使用索引。
• rows：预估扫描的行数，数值越小越好。
• Extra：额外信息，如 Using index（覆盖索引）、Using where（回表过滤）、Using filesort（文件排序）。

1. 违反最左前缀原则

联合索引 (name, age, position) 遵循“最左前缀”原则，即查询必须从索引的最左边（name）开始。

执行以下跳过了 name 字段的查询：

EXPLAIN SELECT * FROM user WHERE age = 25;

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：SQL语句直接跳过了联合索引的第一列 name，导致索引无法生效，数据库执行了全表扫描。

2. 在索引列上做运算

直接在索引列上进行加、减、乘、除等运算会导致索引失效。

执行带有运算的查询：

EXPLAIN SELECT * FROM user WHERE age + 1 = 26;

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：索引树中存储的是 age 的原始值，MySQL必须遍历每一行数据计算 age + 1 后才能判断，因此无法直接利用索引树。

3. 对索引列使用函数

在 WHERE 子句中对索引列使用函数（如 LEFT、SUBSTRING、YEAR）会使索引失效。

执行使用了函数的查询：

EXPLAIN SELECT * FROM user WHERE LEFT(name, 3) = 'Ali';

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：存储引擎保存的是 name 的完整值，使用函数破坏了列的原始值，B+树无法直接定位。

4. 类型转换（隐式转换）

当查询条件中的字段类型与表定义的类型不一致时，MySQL会尝试进行隐式类型转换。

假设 phone 字段是 VARCHAR 类型，执行以下用数字查询的语句：

EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：MySQL尝试将字符串 phone 转换为数字进行比较，这相当于在列上使用了函数，导致索引失效。修正方法是将数字加上引号：WHERE phone = '13800138000'。

5. Like 以通配符开头

在使用 LIKE 进行模糊查询时，如果通配符 % 位于字符串的最左侧，索引将失效。

执行以下查询：

EXPLAIN SELECT * FROM user WHERE name LIKE '%ice';

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：%ice 表示字符串前面可以是任意字符，这导致无法利用索引树的有序性进行定位，必须全表扫描。如果是 name LIKE 'Ali%'，索引则有效。

6. 范围查询后的列失效

在联合索引中，如果一个列进行了范围查询（>, <, between），则该列之后的所有索引列都无法被使用。

执行以下查询，对 age 使用范围查询，并试图利用 position：

EXPLAIN SELECT * FROM user WHERE name = 'Alice' AND age > 25 AND position = 'Developer';

分析结果：

• key 显示使用了 idx_name_age_position，但 key_len（索引长度）只覆盖到了 age。
• Extra 可能包含 Using index condition 或 Using where，但 position 没有参与索引定位。

原因：联合索引是先按 name 排序，name 相同时按 age 排序。一旦 age 进入范围查找（多个值），position 就不再是有序的，因此无法继续使用索引。

7. 使用不等于（!= 或 <>）运算符

使用 != 或 <> 通常会导致索引失效，转而执行全表扫描。

执行以下查询：

EXPLAIN SELECT * FROM user WHERE name != 'Alice';

分析结果：

• type 通常为 ALL（在数据量大且优化器判断扫描索引成本高时）。
• key 为 NULL。

原因：不等于操作意味着扫描除了该值以外的所有数据，对于索引树来说，范围太广，优化器通常认为全表扫描效率更高。

8. IS NULL 或 IS NOT NULL 判断

如果索引列允许为 NULL，使用 IS NOT NULL 可能会导致索引失效（取决于优化器成本计算）。

执行以下查询：

EXPLAIN SELECT * FROM user WHERE name IS NOT NULL;

分析结果：

• 在某些MySQL版本或数据分布下，type 为 ALL。

原因：如果表中大部分数据都不为 NULL，优化器会认为回表读取数据的成本比直接全表扫描更高，从而放弃索引。

9. OR 连接的非索引列

当 OR 两端的列中有一个没有索引时，整个查询通常不会走索引。

假设 phone 字段有索引，但 position 字段没有独立索引，执行：

EXPLAIN SELECT * FROM user WHERE phone = '13800138000' OR position = 'Manager';

分析结果：

• type 为 ALL。
• key 为 NULL。

原因：要使用索引，OR 前后的条件都必须能命中索引。由于 position 没有索引，MySQL只能全表扫描以满足 OR 的逻辑。

10. Order By 导致索引失效（Using filesort）

虽然查询条件命中了索引，但排序操作若不符合索引的顺序，会导致 Using filesort（文件排序），性能大幅下降。

执行以下查询，查询 name 为 'Alice'，但按 position 排序（跳过了 age）：

EXPLAIN SELECT * FROM user WHERE name = 'Alice' ORDER BY position;

分析结果：

• Extra 显示 Using filesort。
• type 为 ref。

原因：联合索引的顺序是 (name, age, position)。当 WHERE 指定了 name，但 ORDER BY 直接使用 position 时，由于缺少中间的 age（或者说 position 相对于 name 是无序的），MySQL必须额外在内存或磁盘中进行排序。

Explain 执行计划关键指标速查表

在排查索引问题时，关注以下指标的变化：

索引失效决策逻辑

为了更直观地理解索引是如何失效的，可以参考以下的决策流程：

通过掌握上述十种常见写法及 EXPLAIN 分析方法，可以快速定位SQL语句中的性能瓶颈，并进行针对性的优化。