MySQL EXPLAIN 实战：6 种索引失效场景验证与优化

二、场景 1：模糊匹配导致索引失效

2.1 问题分析

问题： 左模糊匹配（LIKE '%value'）会导致索引失效

2.2 验证过程

-- 创建 name 列的索引
CREATE INDEX idx_name ON employees(name);

-- 1.1 左模糊匹配（索引失效）
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张';
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 1.2 左右模糊匹配（索引失效）
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张%';
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 1.3 右模糊匹配（索引生效）
EXPLAIN SELECT * FROM employees WHERE name LIKE '张%';
-- 结果：type = range, key = idx_name（使用索引范围扫描）

-- 1.4 精确匹配（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三';
-- 结果：type = ref, key = idx_name（使用索引查找）

2.3 结论

分别从左模糊查询和右模糊查询我们可以看出，左模糊查询会导致索引失效，查询使用的是全盘扫描。右模糊查询则是使用到了索引查询。

• 左模糊（%value）和左右模糊（%value%）会导致索引失效
• 右模糊（value%）和精确匹配可以使用索引

2.4 优化建议

• 避免使用左模糊匹配
• 考虑使用全文索引（FULLTEXT）
• 使用搜索引擎（如 Elasticsearch）

优化示例：

-- 方案 1：使用右模糊
WHERE name LIKE '张%'

-- 方案 2：使用全文索引
ALTER TABLE employees ADD FULLTEXT INDEX ft_name (name);
EXPLAIN SELECT * FROM employees WHERE MATCH(name) AGAINST('张' IN NATURAL LANGUAGE MODE);

-- 方案 3：使用搜索引擎（如 Elasticsearch）
-- 适合复杂的全文搜索场景

全文索引

三、场景 2：函数操作导致索引失效

3.1 问题分析

问题： 对索引列使用函数会导致索引失效

3.2 验证过程

-- 创建 hire_date 列的索引
CREATE INDEX idx_hire_date ON employees(hire_date);

-- 2.1 使用 YEAR 函数（索引失效）
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date)=2020;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 2.2 使用 MONTH 函数（索引失效）
EXPLAIN SELECT * FROM employees WHERE MONTH(hire_date)=6;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 2.3 使用 UPPER 函数（索引失效）
EXPLAIN SELECT * FROM employees WHERE UPPER(name)='张三';
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 2.4 优化：使用范围查询（索引生效）
EXPLAIN SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';
-- 结果：type = range, key = idx_hire_date（使用索引范围扫描）

3.3 结论

对索引使用函数会导致索引失效

• 对索引列使用函数会破坏索引的有序性，导致索引失效
• 应将函数操作移到应用层或使用范围查询替代

3.4 优化建议

• 避免在 WHERE 子句中对索引列使用函数
• 将函数操作移到应用层处理
• 使用计算列或生成列（MySQL 5.7+）

优化示例：

-- 方案 1：使用范围查询
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31'

-- 方案 2：使用计算列（MySQL 5.7+）
ALTER TABLE employees ADD COLUMN hire_year INT GENERATED ALWAYS AS(YEAR(hire_date)) STORED;
CREATE INDEX idx_hire_year ON employees(hire_year);
WHERE hire_year = 2020

-- 方案 3：应用层处理
-- 在应用层计算好年份，然后查询
WHERE hire_date >= '2020-01-01' AND hire_date <= '2020-12-31'

四、场景 3：表达式计算导致索引失效

4.1 问题分析

问题： 对索引列进行表达式计算会破坏索引

4.2 验证过程

-- 创建 salary 和 age 列的索引
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_age ON employees(age);

-- 3.1 索引列参与计算（索引失效）
EXPLAIN SELECT * FROM employees WHERE salary + 1000 > 10000;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 3.2 索引列参与计算（索引失效）
EXPLAIN SELECT * FROM employees WHERE age - 5 > 20;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 3.3 索引列参与计算（索引失效）
EXPLAIN SELECT * FROM employees WHERE salary * 1.1 > 10000;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 3.4 优化：将计算移到右边（索引生效）
EXPLAIN SELECT * FROM employees WHERE salary > 10000 - 1000;
-- 结果：type = range, key = idx_salary（使用索引范围扫描）

-- 3.5 优化：直接使用常量（索引生效）
EXPLAIN SELECT * FROM employees WHERE salary > 9000;
-- 结果：type = range, key = idx_salary（使用索引范围扫描）

4.3 结论

• 索引列参与表达式计算会导致索引失效
• 应将计算移到查询条件的右边或应用层

4.4 优化建议

• 避免在 WHERE 子句中对索引列进行计算
• 将计算移到应用层
• 使用覆盖索引优化

优化示例：

-- 方案 1：将计算移到右边
WHERE salary > 10000 - 1000

-- 方案 2：应用层计算
-- 在应用层计算好阈值，然后查询
WHERE salary > 9000

-- 方案 3：使用覆盖索引
CREATE INDEX idx_salary_bonus ON employees(salary, bonus);
WHERE salary + bonus > 100000

五、场景 4：隐式类型转换导致索引失效

5.1 问题分析

问题： 当查询中数据类型与索引不匹配时，会导致索引失效

5.2 验证过程

-- 创建 phone 和 department_id 列的索引
CREATE INDEX idx_phone ON employees(phone);
CREATE INDEX idx_dept ON employees(department_id);

-- 4.1 字符串列使用数字查询（索引失效）
-- phone 是 VARCHAR 类型，使用数字查询会导致隐式转换
EXPLAIN SELECT * FROM employees WHERE phone = 13800138001;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 4.2 优化：使用字符串查询（索引生效）
EXPLAIN SELECT * FROM employees WHERE phone = '13800138001';
-- 结果：type = ref, key = idx_phone（使用索引查找）

-- 4.3 数字列使用字符串查询（索引可能生效）
-- department_id 是 INT 类型
EXPLAIN SELECT * FROM employees WHERE department_id = '1';
-- 结果：type = ref, key = idx_dept（MySQL 自动转换，索引可能生效）

-- 4.4 日期列使用字符串查询（索引生效）
EXPLAIN SELECT * FROM employees WHERE hire_date = '2020-01-15';
-- 结果：type = ref, key = idx_hire_date（使用索引查找）

5.3 结论

• 字符串列使用数字查询会导致隐式类型转换，索引失效
• 应确保查询条件的数据类型与字段类型一致

5.4 优化建议

• 确保查询条件的数据类型与字段类型一致
• 使用显式类型转换函数
• 在应用层进行类型转换

优化示例：

-- 方案 1：使用正确的类型
WHERE phone = '13800138001'

-- 方案 2：使用显式类型转换
WHERE phone = CAST(13800138001 AS CHAR)

-- 方案 3：应用层类型转换
-- 在应用层将数字转换为字符串，然后查询
WHERE phone = '13800138001'

六、场景 5：联合索引非最左匹配导致索引失效

6.1 问题分析

问题： 联合索引的查询条件不符合最左前缀匹配原则

6.2 验证过程

-- 删除可能影响验证的单列索引
DROP INDEX IF EXISTS idx_name ON employees;
DROP INDEX IF EXISTS idx_age ON employees;
DROP INDEX IF EXISTS idx_dept ON employees;

-- 创建联合索引：idx_name_age_dept (name, age, department_id)
CREATE INDEX idx_name_age_dept ON employees(name, age, department_id);

-- 5.1 使用最左列（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三';
-- 结果：type = ref, key = idx_name_age_dept, key_len = 403
-- 使用了联合索引的第一列

-- 5.2 使用最左两列（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND age = 25;
-- 结果：type = ref, key = idx_name_age_dept, key_len = 408
-- 使用了联合索引的前两列

-- 5.3 使用全部三列（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND age = 25 AND department_id = 1;
-- 结果：type = ref, key = idx_name_age_dept, key_len = 412
-- 使用了联合索引的全部三列

-- 5.4 跳过最左列（索引失效）
EXPLAIN SELECT * FROM employees WHERE age = 25;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 5.5 跳过最左列（索引失效）
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 5.6 跳过最左列，使用后两列（索引失效）
EXPLAIN SELECT * FROM employees WHERE age = 25 AND department_id = 1;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 5.7 使用最左列和第三列（部分索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND department_id = 1;
-- 结果：type = ref, key = idx_name_age_dept, key_len = 403
-- 只使用了第一列 name，跳过了 age，department_id 未使用

-- 5.8 范围查询后的列不生效
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND age > 25 AND department_id = 1;
-- 结果：type = range, key = idx_name_age_dept, key_len = 408
-- 只使用了前两列，department_id 未使用（因为 age 是范围查询）

6.3 最左前缀原则

• 联合索引必须从最左边开始匹配
• 可以跳过中间的列，但不能跳过最左边的列
• 范围查询（>、<、BETWEEN、LIKE）后的列不生效

6.4 结论

• 跳过最左列会导致索引失效
• 范围查询（>、<、BETWEEN 等）后的列无法使用索引
• 必须从最左边开始匹配

6.5 优化建议

• 创建联合索引时，将选择性高的列放在前面
• 根据查询模式设计索引
• 考虑创建多个单列索引

优化示例：

-- 方案 1：遵循最左前缀原则
WHERE name = '张三' AND age = 25

-- 方案 2：创建单独的索引
CREATE INDEX idx_dept ON employees(department_id);
WHERE department_id = 1

-- 方案 3：根据查询模式设计索引
-- 如果经常按 age 查询，创建单独的 age 索引
CREATE INDEX idx_age ON employees(age);
WHERE age = 25

七、场景 6：WHERE 子句中的 OR 导致索引失效

7.1 问题分析

问题： 当查询中存在 OR 连接的条件时，可能影响索引利用

7.2 验证过程

-- 重新创建单列索引（场景 5 已删除）
CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_dept ON employees(department_id);

-- 6.1 OR 连接不同索引列（索引失效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' OR age = 25;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 6.2 OR 连接不同索引列（索引失效）
EXPLAIN SELECT * FROM employees WHERE salary > 10000 OR department_id = 1;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）

-- 6.3 OR 连接同一索引列（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' OR name = '李四';
-- 结果：type = range, key = idx_name（使用索引范围扫描）

-- 6.4 优化：使用 IN 代替 OR（索引生效）
EXPLAIN SELECT * FROM employees WHERE name IN('张三','李四');
-- 结果：type = range, key = idx_name（使用索引范围扫描）

-- 6.5 优化：使用 UNION 代替 OR（索引生效）
EXPLAIN SELECT * FROM employees WHERE name = '张三' UNION SELECT * FROM employees WHERE age = 25;
-- 结果：两个查询都使用索引
-- 第一个查询：type = ref, key = idx_name
-- 第二个查询：type = ref, key = idx_age

-- 6.6 OR 连接有索引列和无索引列（索引失效）
-- 删除 salary 索引
DROP INDEX idx_salary ON employees;
EXPLAIN SELECT * FROM employees WHERE name = '张三' OR salary > 10000;
-- 结果：type = ALL, key = NULL（全表扫描，索引失效）
-- 重新创建索引
CREATE INDEX idx_salary ON employees(salary);

7.3 结论

• OR 连接不同索引列会导致索引失效
• OR 连接同一索引列可以使用索引
• 可以使用 IN 或 UNION 代替 OR

7.4 优化建议

• 尽量使用 IN 代替 OR
• 使用 UNION ALL 代替 OR（如果确定无重复）
• 确保 OR 条件使用同一索引

优化示例：

-- 方案 1：使用 IN 代替 OR
WHERE name IN('张三','李四')

-- 方案 2：使用 UNION 代替 OR
SELECT * FROM employees WHERE name = '张三' UNION SELECT * FROM employees WHERE age = 25

-- 方案 3：使用 UNION ALL（如果确定无重复）
SELECT * FROM employees WHERE name = '张三' UNION ALL SELECT * FROM employees WHERE age = 25

八、索引失效场景总结表

九、完整验证脚本

-- ========================================
-- 完整验证脚本：从头开始验证所有场景
-- ========================================
-- 1. 删除已存在的表（如果存在）
DROP TABLE IF EXISTS employees;

-- 2. 创建员工表（不创建索引）
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    phone VARCHAR(20),
    salary DECIMAL(10,2),
    department_id INT,
    hire_date DATE
);

-- 3. 插入测试数据
INSERT INTO employees (name, age, phone, salary, department_id, hire_date)
VALUES('张三',25,'13800138001',8000.00,1,'2020-01-15'),
      ('李四',30,'13800138002',12000.00,2,'2019-06-20'),
      ('王五',28,'13800138003',9500.00,1,'2021-03-10'),
      ('赵六',35,'13800138004',15000.00,3,'2018-11-05'),
      ('钱七',27,'13800138005',10000.00,2,'2020-08-22'),
      ('张伟',32,'13800138006',13000.00,1,'2019-04-18'),
      ('李娜',29,'13800138007',11000.00,3,'2021-01-30'),
      ('王芳',26,'13800138008',8500.00,2,'2020-12-01'),
      ('赵强',31,'13800138009',14000.00,1,'2018-07-15'),
      ('钱龙',33,'13800138010',16000.00,3,'2017-09-20');

-- ========================================
-- 场景 1：模糊匹配
-- ========================================
SELECT '========== 场景 1：模糊匹配 ==========' AS test_case;
CREATE INDEX idx_name ON employees(name);
SELECT '1.1 左模糊匹配（索引失效）';
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张';
SELECT '1.2 左右模糊匹配（索引失效）';
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张%';
SELECT '1.3 右模糊匹配（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name LIKE '张%';
SELECT '1.4 精确匹配（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name = '张三';

-- ========================================
-- 场景 2：函数操作
-- ========================================
SELECT '========== 场景 2：函数操作 ==========' AS test_case;
CREATE INDEX idx_hire_date ON employees(hire_date);
SELECT '2.1 使用 YEAR 函数（索引失效）';
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date)=2020;
SELECT '2.2 使用 MONTH 函数（索引失效）';
EXPLAIN SELECT * FROM employees WHERE MONTH(hire_date)=6;
SELECT '2.3 优化：使用范围查询（索引生效）';
EXPLAIN SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

-- ========================================
-- 场景 3：表达式计算
-- ========================================
SELECT '========== 场景 3：表达式计算 ==========' AS test_case;
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_age ON employees(age);
SELECT '3.1 索引列参与计算（索引失效）';
EXPLAIN SELECT * FROM employees WHERE salary + 1000 > 10000;
SELECT '3.2 索引列参与计算（索引失效）';
EXPLAIN SELECT * FROM employees WHERE age - 5 > 20;
SELECT '3.3 优化：将计算移到右边（索引生效）';
EXPLAIN SELECT * FROM employees WHERE salary > 9000;

-- ========================================
-- 场景 4：隐式类型转换
-- ========================================
SELECT '========== 场景 4：隐式类型转换 ==========' AS test_case;
CREATE INDEX idx_phone ON employees(phone);
CREATE INDEX idx_dept ON employees(department_id);
SELECT '4.1 字符串列使用数字查询（索引失效）';
EXPLAIN SELECT * FROM employees WHERE phone = 13800138001;
SELECT '4.2 优化：使用字符串查询（索引生效）';
EXPLAIN SELECT * FROM employees WHERE phone = '13800138001';

-- ========================================
-- 场景 5：联合索引非最左匹配
-- ========================================
SELECT '========== 场景 5：联合索引非最左匹配 ==========' AS test_case;
-- 删除可能影响验证的单列索引
DROP INDEX idx_name ON employees;
DROP INDEX idx_age ON employees;
DROP INDEX idx_dept ON employees;
-- 创建联合索引
CREATE INDEX idx_name_age_dept ON employees(name, age, department_id);
SELECT '5.1 使用最左列（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name = '张三';
SELECT '5.2 使用最左两列（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND age = 25;
SELECT '5.3 跳过最左列（索引失效）';
EXPLAIN SELECT * FROM employees WHERE age = 25;
SELECT '5.4 跳过最左列，使用后两列（索引失效）';
EXPLAIN SELECT * FROM employees WHERE age = 25 AND department_id = 1;

-- ========================================
-- 场景 6：OR 条件
-- ========================================
SELECT '========== 场景 6：OR 条件 ==========' AS test_case;
-- 重新创建单列索引
CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_dept ON employees(department_id);
SELECT '6.1 OR 连接不同索引列（索引失效）';
EXPLAIN SELECT * FROM employees WHERE name = '张三' OR age = 25;
SELECT '6.2 OR 连接同一索引列（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name = '张三' OR name = '李四';
SELECT '6.3 优化：使用 IN 代替 OR（索引生效）';
EXPLAIN SELECT * FROM employees WHERE name IN('张三','李四');

-- ========================================
-- 验证完成
-- ========================================
SELECT '========== 验证完成 ==========' AS message;
SHOW INDEX FROM employees;

十、优化建议总结

10.1 模糊查询优化

-- 避免 WHERE name LIKE '%张%'
-- 推荐 WHERE name LIKE '张%'
-- 或使用全文索引 WHERE MATCH(name) AGAINST('张')

10.2 函数操作优化

-- 避免 WHERE YEAR(hire_date)=2020
-- 推荐 WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31'

10.3 表达式计算优化

-- 避免 WHERE salary + 1000 > 10000
-- 推荐 WHERE salary > 9000

10.4 类型转换优化

-- 避免（phone 是 VARCHAR 类型）WHERE phone = 13800138001
-- 推荐 WHERE phone = '13800138001'

10.5 联合索引优化

-- 联合索引：(name, age, department_id)
-- 避免 WHERE age = 25
-- 推荐 WHERE name = '张三' AND age = 25

10.6 OR 条件优化

-- 避免 WHERE name = '张三' OR age = 25
-- 推荐 WHERE name IN('张三','李四')
-- 或 SELECT * FROM employees WHERE name = '张三' UNION SELECT * FROM employees WHERE age = 25

十一、总结

通过 EXPLAIN 命令验证，我们确认了以下 6 种常见的索引失效场景：

1. 模糊匹配：左模糊和左右模糊导致索引失效
2. 函数操作：对索引列使用函数破坏索引有序性
3. 表达式计算：索引列参与计算导致索引失效
4. 隐式类型转换：数据类型不匹配导致索引失效
5. 联合索引非最左匹配：跳过最左列导致索引失效
6. OR 条件：OR 连接不同索引列导致索引失效

关键指标：

• type = ALL：全表扫描，索引失效
• key = NULL：未使用索引
• type = ref/range：使用索引查找/范围扫描
• key = idx_xxx：使用了指定索引

验证方法：

• 采用"按需创建索引"的方式，避免预先创建的索引影响验证结果
• 每个场景独立验证，确保结果准确
• 提供完整的验证脚本，可以一键运行

通过合理使用 EXPLAIN，你可以对查询进行深入分析，找出性能瓶颈，从而提升数据库性能。