在大厂后端开发中，MySQL 作为最常用的关系型数据库，其性能直接影响系统的响应速度和用户体验。然而，很多开发者在实际工作中发现，明明为表添加了索引，查询性能却依然低下。问题的根源往往在于索引失效或者对 SQL 执行计划的理解不足。

今天，我们深入探讨 MySQL 索引失效的常见场景，并通过EXPLAIN执行计划解读如何定位和优化这些问题。结合实际案例，帮助你彻底掌握 MySQL 查询优化的核心技能！

一、为什么索引会失效？

索引是提升查询性能的重要手段，但在某些情况下，MySQL 会选择忽略索引，直接进行全表扫描。这种现象被称为“索引失效”。以下是常见的索引失效场景：

1. 数据类型不匹配

当查询条件中的数据类型与索引列的数据类型不一致时，MySQL 无法使用索引。

案例分析

假设有一张用户表users，其中id字段是INT类型，并为其创建了索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX (age)
);

如果执行以下查询：

SELECT * FROM users WHERE age = '30';

虽然age字段有索引，但查询条件中的'30'是字符串类型，而age是整数类型。MySQL 会将字符串隐式转换为整数，导致索引失效。

解决方案

确保查询条件的数据类型与索引列一致：

SELECT * FROM users WHERE age = 30;

2. 使用函数或表达式

在查询条件中对索引列使用函数或表达式时，索引也会失效。

案例分析

继续使用users表，假设我们想查询年龄大于等于 30 的用户：

SELECT * FROM users WHERE ABS(age) >= 30;

由于ABS(age)是一个表达式，MySQL 无法直接利用age字段上的索引。

解决方案

尽量避免对索引列使用函数或表达式，改写查询逻辑：

SELECT * FROM users WHERE age >= 30 OR age <= -30;

3. LIKE 前缀通配符

使用LIKE查询时，如果模式以%开头，索引将失效。

案例分析

假设users表中有name字段，并为其创建了索引：

CREATE INDEX idx_name ON users(name);

执行以下查询：

SELECT * FROM users WHERE name LIKE '%John%';

由于%出现在开头，MySQL 无法利用索引。

解决方案

尽量避免%开头的模糊查询。如果必须使用，可以考虑全文索引（FULLTEXT）或其他搜索引擎（如 Elasticsearch）。

二、Explain 执行计划解读

EXPLAIN是分析 SQL 查询性能的强大工具，它可以帮助我们了解 MySQL 是如何执行查询的，包括是否使用了索引、扫描了多少行数据等。

1. 常见字段含义

以下是EXPLAIN输出中几个关键字段的解释：

• type：查询类型，表示访问数据的方式。常见的值包括：

• ALL：全表扫描。
• index：索引扫描。
• range：范围扫描。
• ref：基于非唯一索引的等值匹配。
• eq_ref：基于主键或唯一索引的等值匹配。
• const：常量查询，通常是最优的查询方式。

• key：实际使用的索引名称。如果为NULL，说明没有使用索引。

• rows：扫描的行数，数值越小越好。

• Extra：额外信息，常见的值包括：

• Using where：需要回表过滤数据。
• Using index：覆盖索引，无需回表。
• Using filesort：需要额外排序。
• Using temporary：需要创建临时表。

2. 实际案例分析

以下是一个典型的查询优化案例：

原始查询

SELECT * FROM users WHERE age > 30 AND name LIKE '%John%';

执行EXPLAIN：

EXPLAIN SELECT * FROM users WHERE age > 30 AND name LIKE '%John%';

输出结果如下：

从结果可以看出：

• type为ALL，说明进行了全表扫描。
• key为NULL，说明没有使用索引。
• rows为 1000，扫描了所有行。

优化方案

1. 避免LIKE前缀通配符，改用全文索引或搜索引擎。
2. 如果必须保留LIKE查询，可以拆分为两个独立查询：

SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE name LIKE '%John%';

然后在应用层合并结果。

三、总结与建议

通过以上分析，我们可以得出以下结论：

1. 避免索引失效的关键点：

• 确保数据类型一致。
• 避免对索引列使用函数或表达式。
• 尽量避免LIKE前缀通配符。

• 熟练掌握EXPLAIN：

• 关注type、key、rows等关键字段。
• 结合Extra信息，定位性能瓶颈。

• 优化建议：

• 定期检查慢查询日志，发现问题及时优化。
• 在设计阶段合理规划索引，避免冗余索引。

希望这篇文章能帮助你更好地理解 MySQL 查询优化的核心技巧！如果你有任何疑问或经验分享，欢迎在评论区留言！