MySQL 查询不走索引的常见陷阱与优化方法

前言

建立索引是提升 MySQL 查询性能的常用手段，但并非在所有情况下索引都能生效。在实际开发中，如果 SQL 语句写法不当，很容易无意中“避开”了索引的使用，导致查询效率低下。了解哪些情况会导致索引失效，对于编写高效的数据库查询至关重要。

MySQL 查询不走索引的常见情况

第1步：检查索引列是否参与计算

目的：避免在 WHERE 子句中对索引列进行算术运算，确保索引列保持原始状态以便利用 B+ 树结构查找。

错误示例：当索引列 `age` 参与计算时，MySQL 无法直接使用索引。

SELECT username FROM t_user WHERE age+10=30; -- 不会使用索引

正确做法：将运算符移至等号右侧，保持索引列独立。

SELECT username FROM t_user WHERE age=30-10; -- 会使用索引

第2步：评估索引列上使用的函数

目的：防止因对索引列应用函数而导致索引失效。

原理：当函数作用于索引列时，MySQL 需要先计算每行的函数值，再进行比较，这破坏了索引的有序性。

SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; -- 可能不走索引
-- 推荐写法：将参数放入函数内部或移到等号一侧
SELECT username FROM t_user WHERE username = concat('admin','1'); -- 走索引

第3步：确认 LIKE 模糊匹配的语法位置

目的：掌握通配符在 LIKE 语句中的位置对索引的影响。

规则：前缀匹配（% 在后）可以使用索引，前后模糊匹配（% 在前或在后）通常导致索引失效。

SELECT * FROM USER WHERE username LIKE 'mysql测试%'   -- 走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试'   -- 不走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试%'  -- 不走索引

第4步：排查数据类型隐式转换

目的：避免因查询条件类型与列类型不匹配导致的隐式转换，从而触发全表扫描。

场景：字符串类型的字段若与数字直接比较，MySQL 会隐式转换字符串为数字。

-- stock_code 为字符串类型且有索引
SELECT * FROM stock_data WHERE stock_code = '600538'; -- 走索引
SELECT * FROM stock_data WHERE stock_code = 600538;   -- 不走索引，发生隐式转换

第5步：优化 OR 条件的索引覆盖

目的：确保 OR 连接的所有字段均具有索引，否则可能导致整个查询放弃索引。

风险：只要 OR 操作中有一个字段没有索引，优化器通常会放弃使用该查询的索引。

-- open 字段无索引
SELECT * FROM stock_data WHERE stock_code = '600538' OR open = 6.62; -- 不走索引
-- stock_code 和 up_down_pre 均有索引
SELECT * FROM stock_data WHERE stock_code = '600538' OR up_down_pre = 5.1; -- 走索引

建议：复杂 OR 条件可考虑改写为 UNION ALL 以分别走索引。

第6步：处理不等值查询

目的：理解不等于操作符（!= 或 <>）对辅助索引的使用限制。

现象：在某些场景下，!= 会导致索引失效。

SELECT * FROM t_user WHERE username <> 'mysql测试'; -- 不走索引

第7步：审视 IS NULL 与 IS NOT NULL

目的：注意空值判断可能在特定条件下不利用索引。

SELECT * FROM t_user WHERE username IS NULL;      -- 不走索引
SELECT * FROM t_user WHERE username IS NOT NULL;  -- 不走索引

第8步：规范 IN 列表的使用

目的：控制 IN 列表中数据的数量和类型一致性。

注意：列表过大且数据量远超阈值时，MySQL 可能认为全表扫描更高效而不走索引；同时需警惕隐式类型转换问题。

-- varchar 类型字段
SELECT * FROM stock_data WHERE stock_code IN ('600538');             -- 走索引
SELECT * FROM stock_data WHERE stock_code IN (大量不相关数据);       -- 可能不走索引
SELECT * FROM stock_data WHERE stock_code IN (600538);              -- 隐式转换，不走索引

其他影响索引的因素与优化建议

缺失查询条件

如果查询中没有 WHERE 子句，或者查询的字段完全没有建立索引，数据库必然进行全表扫描。建议：尽可能带上索引列作为查询条件，或将高频查询字段建立索引。

结果集占比过高

当查询返回的数据超过全表数据的 25% 左右时，优化器倾向于认为全表扫描比通过索引回表更高效。建议：业务允许时配合 LIMIT 限制返回条数；若数据量大且需频繁访问，考虑缓存至 Redis 或使用搜索引擎。

统计信息过期

频繁的大规模增删改可能导致表的统计信息不真实，进而导致索引失效。建议：定期执行 `ANALYZE TABLE` 更新统计信息，或在极端情况下重建索引。

替代搜索方案

面对复杂的包含百分号的模糊查询需求（如 `%linux%`），MySQL 原生支持较弱。建议：引入 Elasticsearch + MongoDB 等专业搜索引擎产品进行全文检索。

总结

为了避免 MySQL 查询性能下降，开发人员需注意在 SQL 书写过程中遵循索引设计原则，避免在索引列上进行计算、使用函数、忽略隐式转换以及滥用 OR 条件。以上就是 MySQL 查询不走索引的详细情况与优化方法的详细内容，更多关于 MySQL 查询优化的资料请关注本站其它相关文章！