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

前置知识：在阅读本文之前，建议先了解EXPLAIN命令的基础知识。如果你还不熟悉EXPLAIN，请先阅读《MySQL EXPLAIN命令详解：SQL查询性能分析与优化指南（基础篇）》。

前言：索引是提升数据库查询性能的重要手段，但并不是所有查询都能有效利用索引。在实际开发中，我们经常遇到索引失效的情况，导致查询性能下降。本文将通过EXPLAIN命令，逐一验证6种常见的索引失效场景，并提供相应的优化建议。

本文将验证以下场景：

• 场景1：模糊匹配导致索引失效
• 场景2：函数操作导致索引失效
• 场景3：表达式计算导致索引失效
• 场景4：隐式类型转换导致索引失效
• 场景5：联合索引非最左匹配导致索引失效
• 场景6：WHERE子句中的OR导致索引失效

一、创建测试表

为了准确验证每个场景，我们采用"按需创建索引"的方式，避免预先创建的索引影响验证结果。

-- 创建员工表（不创建索引，只保留主键）CREATETABLE employees ( id INTPRIMARYKEYAUTO_INCREMENT, name VARCHAR(100), age INT, phone VARCHAR(20), salary DECIMAL(10,2), department_id INT, hire_date DATE);-- 插入测试数据INSERTINTO 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');-- 查看表结构DESC employees;-- 查看索引（此时只有主键索引）SHOWINDEXFROM employees;

主键索引（Primary Key）

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

2.1 问题分析

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

2.2 验证过程

-- 创建name列的索引CREATEINDEX idx_name ON employees(name);-- 1.1 左模糊匹配（索引失效）EXPLAINSELECT*FROM employees WHERE name LIKE'%张';-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 1.2 左右模糊匹配（索引失效）EXPLAINSELECT*FROM employees WHERE name LIKE'%张%';-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 1.3 右模糊匹配（索引生效）EXPLAINSELECT*FROM employees WHERE name LIKE'张%';-- 结果：type = range, key = idx_name（使用索引范围扫描）-- 1.4 精确匹配（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三';-- 结果：type = ref, key = idx_name（使用索引查找）

2.3 结论

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

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

2.4 优化建议

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

优化示例：

-- 方案1：使用右模糊WHERE name LIKE'张%'-- 方案2：使用全文索引ALTERTABLE employees ADD FULLTEXT INDEX ft_name (name);EXPLAINSELECT*FROM employees WHEREMATCH(name) AGAINST('张'INNATURALLANGUAGEMODE)； -- 方案3：使用搜索引擎（如Elasticsearch）-- 适合复杂的全文搜索场景

全文索引

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

3.1 问题分析

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

3.2 验证过程

-- 创建hire_date列的索引CREATEINDEX idx_hire_date ON employees(hire_date);-- 2.1 使用YEAR函数（索引失效）EXPLAINSELECT*FROM employees WHEREYEAR(hire_date)=2020;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 2.2 使用MONTH函数（索引失效）EXPLAINSELECT*FROM employees WHEREMONTH(hire_date)=6;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 2.3 使用UPPER函数（索引失效）EXPLAINSELECT*FROM employees WHERE UPPER(name)='张三';-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 2.4 优化：使用范围查询（索引生效）EXPLAINSELECT*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+）ALTERTABLE employees ADDCOLUMN hire_year INT GENERATED ALWAYS AS(YEAR(hire_date)) STORED;CREATEINDEX 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列的索引CREATEINDEX idx_salary ON employees(salary);CREATEINDEX idx_age ON employees(age);-- 3.1 索引列参与计算（索引失效）EXPLAINSELECT*FROM employees WHERE salary +1000>10000;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 3.2 索引列参与计算（索引失效）EXPLAINSELECT*FROM employees WHERE age -5>20;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 3.3 索引列参与计算（索引失效）EXPLAINSELECT*FROM employees WHERE salary *1.1>10000;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 3.4 优化：将计算移到右边（索引生效）EXPLAINSELECT*FROM employees WHERE salary >10000-1000;-- 结果：type = range, key = idx_salary（使用索引范围扫描）-- 3.5 优化：直接使用常量（索引生效）EXPLAINSELECT*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：使用覆盖索引CREATEINDEX idx_salary_bonus ON employees(salary, bonus);WHERE salary + bonus >100000

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

5.1 问题分析

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

5.2 验证过程

-- 创建phone和department_id列的索引CREATEINDEX idx_phone ON employees(phone);CREATEINDEX idx_dept ON employees(department_id);-- 4.1 字符串列使用数字查询（索引失效）-- phone是VARCHAR类型，使用数字查询会导致隐式转换EXPLAINSELECT*FROM employees WHERE phone =13800138001;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 4.2 优化：使用字符串查询（索引生效）EXPLAINSELECT*FROM employees WHERE phone ='13800138001';-- 结果：type = ref, key = idx_phone（使用索引查找）-- 4.3 数字列使用字符串查询（索引可能生效）-- department_id是INT类型EXPLAINSELECT*FROM employees WHERE department_id ='1';-- 结果：type = ref, key = idx_dept（MySQL自动转换，索引可能生效）-- 4.4 日期列使用字符串查询（索引生效）EXPLAINSELECT*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(13800138001ASCHAR)-- 方案3：应用层类型转换-- 在应用层将数字转换为字符串，然后查询WHERE phone ='13800138001'

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

6.1 问题分析

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

6.2 验证过程

-- 删除可能影响验证的单列索引DROPINDEXIFEXISTS idx_name ON employees;DROPINDEXIFEXISTS idx_age ON employees;DROPINDEXIFEXISTS idx_dept ON employees;-- 创建联合索引：idx_name_age_dept (name, age, department_id)CREATEINDEX idx_name_age_dept ON employees(name, age, department_id);-- 5.1 使用最左列（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三';-- 结果：type = ref, key = idx_name_age_dept, key_len = 403-- 使用了联合索引的第一列-- 5.2 使用最左两列（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三'AND age =25;-- 结果：type = ref, key = idx_name_age_dept, key_len = 408-- 使用了联合索引的前两列-- 5.3 使用全部三列（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三'AND age =25AND department_id =1;-- 结果：type = ref, key = idx_name_age_dept, key_len = 412-- 使用了联合索引的全部三列-- 5.4 跳过最左列（索引失效）EXPLAINSELECT*FROM employees WHERE age =25;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 5.5 跳过最左列（索引失效）EXPLAINSELECT*FROM employees WHERE department_id =1;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 5.6 跳过最左列，使用后两列（索引失效）EXPLAINSELECT*FROM employees WHERE age =25AND department_id =1;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 5.7 使用最左列和第三列（部分索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三'AND department_id =1;-- 结果：type = ref, key = idx_name_age_dept, key_len = 403-- 只使用了第一列name，跳过了age，department_id未使用-- 5.8 范围查询后的列不生效EXPLAINSELECT*FROM employees WHERE name ='张三'AND age >25AND 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：创建单独的索引CREATEINDEX idx_dept ON employees(department_id);WHERE department_id =1-- 方案3：根据查询模式设计索引-- 如果经常按age查询，创建单独的age索引CREATEINDEX idx_age ON employees(age);WHERE age =25

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

7.1 问题分析

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

7.2 验证过程

-- 重新创建单列索引（场景5已删除）CREATEINDEX idx_name ON employees(name);CREATEINDEX idx_age ON employees(age);CREATEINDEX idx_salary ON employees(salary);CREATEINDEX idx_dept ON employees(department_id);-- 6.1 OR连接不同索引列（索引失效）EXPLAINSELECT*FROM employees WHERE name ='张三'OR age =25;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 6.2 OR连接不同索引列（索引失效）EXPLAINSELECT*FROM employees WHERE salary >10000OR department_id =1;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 6.3 OR连接同一索引列（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三'OR name ='李四';-- 结果：type = range, key = idx_name（使用索引范围扫描）-- 6.4 优化：使用IN代替OR（索引生效）EXPLAINSELECT*FROM employees WHERE name IN('张三','李四');-- 结果：type = range, key = idx_name（使用索引范围扫描）-- 6.5 优化：使用UNION代替OR（索引生效）EXPLAINSELECT*FROM employees WHERE name ='张三'UNIONSELECT*FROM employees WHERE age =25;-- 结果：两个查询都使用索引-- 第一个查询：type = ref, key = idx_name-- 第二个查询：type = ref, key = idx_age-- 6.6 OR连接有索引列和无索引列（索引失效）-- 删除salary索引DROPINDEX idx_salary ON employees;EXPLAINSELECT*FROM employees WHERE name ='张三'OR salary >10000;-- 结果：type = ALL, key = NULL（全表扫描，索引失效）-- 重新创建索引CREATEINDEX idx_salary ON employees(salary);

7.3 结论

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

7.4 优化建议

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

优化示例：

-- 方案1：使用IN代替ORWHERE name IN('张三','李四')-- 方案2：使用UNION代替ORSELECT*FROM employees WHERE name ='张三'UNIONSELECT*FROM employees WHERE age =25-- 方案3：使用UNION ALL（如果确定无重复）SELECT*FROM employees WHERE name ='张三'UNIONALLSELECT*FROM employees WHERE age =25

八、索引失效场景总结表

九、完整验证脚本

-- ========================================-- 完整验证脚本：从头开始验证所有场景-- ========================================-- 1. 删除已存在的表（如果存在）DROPTABLEIFEXISTS employees;-- 2. 创建员工表（不创建索引）CREATETABLE employees ( id INTPRIMARYKEYAUTO_INCREMENT, name VARCHAR(100), age INT, phone VARCHAR(20), salary DECIMAL(10,2), department_id INT, hire_date DATE);-- 3. 插入测试数据INSERTINTO 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;CREATEINDEX idx_name ON employees(name);SELECT'1.1 左模糊匹配（索引失效）';EXPLAINSELECT*FROM employees WHERE name LIKE'%张';SELECT'1.2 左右模糊匹配（索引失效）';EXPLAINSELECT*FROM employees WHERE name LIKE'%张%';SELECT'1.3 右模糊匹配（索引生效）';EXPLAINSELECT*FROM employees WHERE name LIKE'张%';SELECT'1.4 精确匹配（索引生效）';EXPLAINSELECT*FROM employees WHERE name ='张三';-- ========================================-- 场景2：函数操作-- ========================================SELECT'========== 场景2：函数操作 =========='AS test_case;CREATEINDEX idx_hire_date ON employees(hire_date);SELECT'2.1 使用YEAR函数（索引失效）';EXPLAINSELECT*FROM employees WHEREYEAR(hire_date)=2020;SELECT'2.2 使用MONTH函数（索引失效）';EXPLAINSELECT*FROM employees WHEREMONTH(hire_date)=6;SELECT'2.3 优化：使用范围查询（索引生效）';EXPLAINSELECT*FROM employees WHERE hire_date BETWEEN'2020-01-01'AND'2020-12-31';-- ========================================-- 场景3：表达式计算-- ========================================SELECT'========== 场景3：表达式计算 =========='AS test_case;CREATEINDEX idx_salary ON employees(salary);CREATEINDEX idx_age ON employees(age);SELECT'3.1 索引列参与计算（索引失效）';EXPLAINSELECT*FROM employees WHERE salary +1000>10000;SELECT'3.2 索引列参与计算（索引失效）';EXPLAINSELECT*FROM employees WHERE age -5>20;SELECT'3.3 优化：将计算移到右边（索引生效）';EXPLAINSELECT*FROM employees WHERE salary >9000;-- ========================================-- 场景4：隐式类型转换-- ========================================SELECT'========== 场景4：隐式类型转换 =========='AS test_case;CREATEINDEX idx_phone ON employees(phone);CREATEINDEX idx_dept ON employees(department_id);SELECT'4.1 字符串列使用数字查询（索引失效）';EXPLAINSELECT*FROM employees WHERE phone =13800138001;SELECT'4.2 优化：使用字符串查询（索引生效）';EXPLAINSELECT*FROM employees WHERE phone ='13800138001';-- ========================================-- 场景5：联合索引非最左匹配-- ========================================SELECT'========== 场景5：联合索引非最左匹配 =========='AS test_case;-- 删除可能影响验证的单列索引DROPINDEX idx_name ON employees;DROPINDEX idx_age ON employees;DROPINDEX idx_dept ON employees;-- 创建联合索引CREATEINDEX idx_name_age_dept ON employees(name, age, department_id);SELECT'5.1 使用最左列（索引生效）';EXPLAINSELECT*FROM employees WHERE name ='张三';SELECT'5.2 使用最左两列（索引生效）';EXPLAINSELECT*FROM employees WHERE name ='张三'AND age =25;SELECT'5.3 跳过最左列（索引失效）';EXPLAINSELECT*FROM employees WHERE age =25;SELECT'5.4 跳过最左列，使用后两列（索引失效）';EXPLAINSELECT*FROM employees WHERE age =25AND department_id =1;-- ========================================-- 场景6：OR条件-- ========================================SELECT'========== 场景6：OR条件 =========='AS test_case;-- 重新创建单列索引CREATEINDEX idx_name ON employees(name);CREATEINDEX idx_age ON employees(age);CREATEINDEX idx_salary ON employees(salary);CREATEINDEX idx_dept ON employees(department_id);SELECT'6.1 OR连接不同索引列（索引失效）';EXPLAINSELECT*FROM employees WHERE name ='张三'OR age =25;SELECT'6.2 OR连接同一索引列（索引生效）';EXPLAINSELECT*FROM employees WHERE name ='张三'OR name ='李四';SELECT'6.3 优化：使用IN代替OR（索引生效）';EXPLAINSELECT*FROM employees WHERE name IN('张三','李四');-- ========================================-- 验证完成-- ========================================SELECT'========== 验证完成 =========='AS message;SHOWINDEXFROM employees;

十、优化建议总结

10.1 模糊查询优化

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

10.2 函数操作优化

-- 避免WHEREYEAR(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 ='张三'UNIONSELECT*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，你可以对查询进行深入分析，找出性能瓶颈，从而提升数据库性能。

延伸阅读：
如果你想了解EXPLAIN命令的基础知识和12个输出字段的详细解释，请阅读我的另一篇文章《MySQL EXPLAIN命令详解：SQL查询性能分析与优化指南（基础篇）》。