避免全表扫描！5种MySQL索引失效场景与实战解决方案

目录

一、联合索引未遵循最左前缀原则

问题描述

典型案例

解决方案

二、索引列参与表达式计算

问题描述

典型案例

解决方案

三、隐式类型转换导致失效

问题描述

典型案例

解决方案

四、OR连接非索引字段

问题描述

典型案例

解决方案

五、低选择性索引被优化器放弃

问题描述

典型案例

解决方案

最佳实践总结

作为数据库核心优化手段，索引设计直接影响查询性能。但在实际场景中，即使创建了索引，仍可能因设计不当导致全表扫描。今天小编通过真实示例解析5种典型索引失效场景，并提供可靠的优化方案。

一、联合索引未遵循最左前缀原则

问题描述

当使用联合索引时，查询条件未包含最左列或未按顺序使用索引列时，将无法触发索引。

典型案例

某电商用户表结构：

CREATE TABLE users ( id INT PRIMARY KEY, 
name VARCHAR(50), age INT, city VARCHAR(20), 
KEY idx_name_age (name,age) );

执行范围查询：

SELECT * FROM users WHERE age > 25;

执行计划显示type=ALL（全表扫描）

解决方案

调整查询顺序包含最左列：

SELECT * FROM users WHERE name = '张三' AND age > 25;

修改索引顺序（根据业务频率）：

ALTER TABLE users ADD INDEX idx_age_name(age,name);

二、索引列参与表达式计算

问题描述

对索引列使用函数、算术运算或类型转换时，将导致索引失效。

典型案例

订单表时间查询：

CREATE TABLE orders ( id INT PRIMARY KEY, amount DECIMAL(10,2),
 create_time DATETIME, KEY idx_create_time(create_time) ); 
-- 错误写法 
SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = '2023-08-01';

执行计划显示type=ALL