前言
最近，公司的后台页面有一个接口查询太慢，让调查修复。我看了一下，是把日期作为查询条件时，写sql时将日期进行了格式化，导致了索引失效。
因此，这次想跟大家聊聊mysql索引失效的一些场景，并结合我工作中实际遇到的情况进行说明，希望能对大家有一点帮助
一、什么是索引失效？
定义： 索引失效 就是指mysql 在实际执行中没有扫描索引而是直接扫描全表。一般会导致查询变慢。
二、如何判断索引是否失效？
- 使用
EXPLAIN：EXPLAIN SELECT ...;
• type: 从好到差依次是：system > const > eq_ref > ref > range > index > ALL（性能排序）
• key: 实际使用的索引，如果为NULL则表示未使用索引
- 查看索引使用情况：
SHOW STATUS LIKE 'Handler_read_rnd_%'; SHOW STATUS LIKE 'Handler_read_key';
三、MySQL索引失效的常见场景
场景一：索引列上使用函数或表达式
- 原因：因为索引上保存的是原始的值，当对列进行运算后，mysql 就无法直接通过索引来匹配数据；从而导致索引失效。
这也是我开头提到的场景
解决方法就是避免在索引列上添加函数或者表达式，有需求的话可以在业务代码层面做处理
示例：
-- ❌ 索引失效
WHERE DATE(order_date) = '2024-04-21'
-- ✅ 改写方式
WHERE order_date BETWEEN '2024-04-21' AND '2024-04-22'
场景二：数据类型不一致或不匹配
- 原因: 当数据类型不匹配时，数据库会执行隐式类型转换。这相当于在列上使用了一个转换函数，就又回到了场景一的情况，不过这次的函数时mysql替你加的。
示例：
-- 索引字段为INT，但查询条件用字符串
-- ❌ 索引失效（字符串类型）
SELECT * FROM users WHERE id='123';
-- ✅ 索引有效（int 类型）
SELECT * FROM users WHERE id=123;
场景三： 使用不等于（<> 或 !=）操作符
- 原因：不等于的话mysql要找到除了后面特定值的所有数据，此时mysql大概率会认为我直接全部扫描比先找到这条记录，再找它前面和后面的要更快些，从而选择执行全表扫描
- 注意：这里说的是大概率，并不是mysql 100% 不走索引，具体如何执行要看mysql内部的优化器如何决定
示例：
-- ❌ 索引大概率失效
SELECT * FROM products WHERE price <> 100
场景四： 使用not in或`NOT EXISTS
- 原因：基本和场景三是一样的，都看你排除数据的多少
示例:
-- ❌ 索引大概率失效
SELECT * FROM products WHERE price not in (100,200)
场景五：使用OR连接条件
原因：如果OR条件的列都在索引的前缀中，优化器可能会尝试使用索引，否则难以利用索引，可能会退化为全表扫描
示例：
-- ❌ 索引大概率失效
SELECT * FROM users WHERE username = 'morty' OR age = 18;
-- 情况1: 只有 idx_username 存在，email 无索引 -> 索引 idx_username 对 OR 条件整体失效（需全表扫）。
-- 情况2: 如果同时存在 idx_username 和 idx_age -> 优化器有可能使用 Index Merge 策略，此时两个索引都可能会生效。
优化策略：
使用`UNION ALL：
-- ✅ 索引有效
SELECT * FROM users WHERE username='morty';
union all
SELECT * FROM users WHERE age=18;
场景六：未遵循 最左匹配原则/ 前缀匹配
我感觉这两个的原理讲起来类似，因此放到一块说了
原理： 原理其实很简单，就是mysql的索引都是有序的，你查询的条件要确定是有序的才可以通过索引优化，违背最左匹配原则就代表了查询的条件已经是无序的了，从而导致索引失效
示例：
-- 索引 为联合索引idx_country_city 先country 再city
-- ❌ 索引失效，因为跳过了最左列 `country`，coutry的数据是无序的
SELECT * FROM users WHERE city = 'New York';
-- ✅ 索引有效
SELECT * FROM users WHERE country = 'beijing';
-- ❌ 索引失效 以doe结尾的数据不是有序的
SELECT * FROM users WHERE username LIKE '%doe';
-- ✅ 索引有效
SELECT * FROM users WHERE username LIKE 'doe%';
场景七：排序不当，导致索引失效
-- 1. ORDER BY 与 WHERE 子句不匹配：
-- 两个独立的索引 name 和height。 ❌ 索引失效
select height from users where name like 'a%' order by height;
-- 建立联合索引（name，height）。 ✅ 索引有效
select height from users where name like 'a%' order by height;
-- 2.范围条件导致索引失效
-- ❌ 索引失效
SELECT * FROM products WHERE price > 100 ORDER BY price LIMIT 10;
-- 3. 使用 `ORDER BY` 的列与索引排序方式不一致:
-- 索引 (country, city ASC) 可能用于 WHERE ❌ 索引失效
SELECT * FROM users WHERE country = 'US' ORDER BY city DESC;
-- 创建索引 (country, city DESC) 可以优化这个查询。 ✅ 索引有效
SELECT * FROM users ORDER BY country ASC, city DESC;
场景八：JOIN 字段字符集/排序规则不一致
场景：跨表 JOIN 时字段字符集不同
这个场景是我实际遇到但很少看到有资料介绍到的，当时排查了好多方向，就是没想到是字符集的问题，希望能帮大家避坑
table1.utf8_col (utf8mb4) JOIN table2.latin1_col (latin1)
原理：隐式转换导致索引失效
解决：统一字符集或显式转换
ON CONVERT(utf8_col USING latin1) = latin1_col
四、应对策略：避免索引失效
- 避免在列上使用函数或表达式
- 保持数据类型一致
- 按筛选条件字段顺序建立复合索引
- 优化
OR条件，将其拆分为多个查询 - 监控查询计划，结合
EXPLAIN分析调优 - 合理使用覆盖索引，减少回表
- 避免在索引列上进行模糊匹配（LIKE '%abc%'）
五、总结
索引是提升查询性能的关键，但并非万能。理解索引失效场景，合理设计索引和优化SQL，才是真正的“杀手锏”。做好索引优化，你的MySQL性能会有质的飞跃！
