MySQL 索引失效场景

1. 索引列参与计算或进行函数操作
2. 使用OR，并且OR的两边存在< 或者 > 的时候
3. 使用like操作，但是不满足左匹配，例如：”%java”
4. 隐式类型转换，比如一个string类型列，使用数字来查询。这种情况有一个特列，如果字段类型为int类型，而查询条件添加了单引号或者双引号，则Mysql会参数转化为int类型，这种情况也可以走索引。
5. 不等于比较。这种情况也是有可能会走索引的，比如用id进行!=，是可能走索引的。
6. 使用is not null时不走索引，使用 is null 走索引
7. order by。如果order by的时候数据量很小，数据库可能直接在内存中进行排序。
8. in。一般在in中的值比较少的时候可能会走索引优化，但如果选项比较多，可能不走索引。
9. 联合索引失效。比如联合索引（a，b，c），进行查询时没有满足最左匹配（查b，查c，查b c）
10. 存储引擎不能使用索引范围条件右边的列
11. 两列做比较。如果两个列数据都有索引，但是在查询条件中对两列数据进行了对比操作，则会导致索引失效。
12. 查询条件是用no in时，如果是主键则走索引。如果是普通索引，则失效
13. not exists 不走索引，exists 可能走索引

---

1. 索引列参与计算或函数操作

• 正常判断：索引列直接使用原始值。
• 失效原因：索引存储的是列原始值，计算或函数操作后生成的值无法匹配索引结构。
• 具体原理：B+ 树索引基于原始值构建，计算或函数会破坏值与索引的映射关系，导致无法通过索引树快速定位。
• 示例 SQL：

-- 失效
SELECT * FROM table WHERE YEAR(create_time) = 2023;
SELECT * FROM table WHERE a + 10 = 20;
-- 正常
SELECT * FROM table WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

---

2. 使用 OR 且两边存在范围查询

• 正常判断：OR 两侧均有索引且逻辑简单。
• 失效原因：OR 要求同时满足多个条件，若任意一侧无索引或涉及范围查询，优化器可能放弃索引。
• 具体原理：MySQL 对 OR 的优化能力有限，若无法合并索引范围，则选择全表扫描。
• 示例 SQL：

-- 失效（假设 d 列无索引）
SELECT * FROM table WHERE a > 10 OR d = 5;
-- 优化方法
SELECT * FROM table WHERE a > 10 
UNION 
SELECT * FROM table WHERE d = 5;

---

3. LIKE 不满足左匹配

• 正常判断：LIKE 使用前缀匹配（如 'abc%'）。
• 失效原因：以通配符开头（% 或 _）破坏索引前缀有序性。
• 具体原理：B+ 树索引按前缀排序，无法反向或中间模糊匹配。
• 示例 SQL：

-- 失效
SELECT * FROM table WHERE name LIKE '%java';
-- 正常
SELECT * FROM table WHERE name LIKE 'java%';

---

4. 隐式类型转换

• 正常判断：查询值与列类型严格一致。
• 失效原因：类型不匹配导致 MySQL 隐式转换，破坏索引匹配。
• 具体原理：索引存储的是列定义的类型，隐式转换相当于对列使用函数（如 CAST）。
• 示例 SQL：

-- 失效（假设 a 是 VARCHAR）
SELECT * FROM table WHERE a = 123;  -- MySQL 执行 CAST(a AS INT)
-- 正常（特例：字段为 INT，查询值带引号）
SELECT * FROM table WHERE id = '123';  -- id 是 INT 类型

---

5. 不等于比较（!= 或 <>）

• 正常判断：主键或唯一索引可能走索引。
• 失效原因：非主键的不等于操作需扫描大部分数据，优化器认为全表更快。
• 具体原理：索引适合定位少量数据，不等于操作需遍历索引树大部分节点。
• 示例 SQL：

-- 失效（普通索引）
SELECT * FROM table WHERE a != 5;
-- 正常（主键或覆盖索引）
SELECT * FROM table WHERE id != 5;  -- id 是主键

---

6. IS NOT NULL 与 IS NULL

• 正常判断：IS NULL 可走索引，IS NOT NULL 可能失效。
• 失效原因：IS NOT NULL 需遍历所有非空值，成本高。
• 具体原理：索引中 NULL 值集中存储（InnoDB），IS NULL 可快速定位，而 IS NOT NULL 需扫描全索引。
• 示例 SQL：

-- 失效
SELECT * FROM table WHERE a IS NOT NULL;
-- 正常
SELECT * FROM table WHERE a IS NULL;

---

7. ORDER BY 排序

• 正常判断：排序字段有索引且顺序一致。
• 失效原因：小数据量直接在内存排序；排序字段无索引或顺序不匹配。
• 具体原理：索引本身有序，若 ORDER BY 字段与索引顺序一致，可避免 filesort 操作。
• 示例 SQL：

-- 失效（无索引）
SELECT * FROM table ORDER BY a;
-- 正常（索引支持排序）
SELECT * FROM table ORDER BY a, b;  -- 索引 (a, b)

---

8. IN 条件

• 正常判断：IN 列表较短且选择性高。
• 失效原因：长列表导致优化器认为全表扫描更快。
• 具体原理：IN 本质是多个 OR，列表过长时索引检索成本超过全表扫描。
• 示例 SQL：

-- 失效（列表过长）
SELECT * FROM table WHERE a IN (1,2,3,...,1000);
-- 正常（主键或短列表）
SELECT * FROM table WHERE id IN (1,2,3);

---

9. 联合索引未遵循最左前缀

• 正常判断：查询条件包含最左列且顺序合理。
• 失效原因：未包含最左列，或中间列被范围查询中断。
• 具体原理：联合索引按 (a, b, c) 顺序构建 B+ 树，缺少中间列导致后续列无序。
• 示例 SQL：

-- 失效（缺少 a）
SELECT * FROM table WHERE b=2 AND c=3;
-- 部分失效（c 无法直接利用索引）
SELECT * FROM table WHERE a=1 AND c=3;

---

10. 范围查询阻断后续列

• 正常判断：范围查询列在联合索引末尾。
• 失效原因：范围查询（如 >、BETWEEN）后，后续列无法使用索引。
• 具体原理：范围查询导致索引后续列无序，无法通过 B+ 树快速定位。
• 示例 SQL：

-- 仅 a 和 b 走索引，c 失效
SELECT * FROM table WHERE a=1 AND b>10 AND c=2;

---

11. 两列比较操作

• 正常判断：单列条件使用索引。
• 失效原因：索引不支持列间比较。
• 具体原理：索引存储列值与行位置的映射，无法直接比较两列值。
• 示例 SQL：

-- 失效
SELECT * FROM table WHERE a = b;

---

12. NOT IN 条件

• 正常判断：主键 NOT IN 可能走索引。
• 失效原因：普通索引需回表验证，优化器认为全表扫描更快。
• 具体原理：主键索引包含完整数据，普通索引需回表检查是否满足 NOT IN。
• 示例 SQL：

-- 失效（普通索引）
SELECT * FROM table WHERE a NOT IN (1,2);
-- 正常（主键）
SELECT * FROM table WHERE id NOT IN (1,2);

---

13. NOT EXISTS 子查询

• 正常判断：EXISTS 可能走索引，NOT EXISTS 通常失效。
• 失效原因：NOT EXISTS 需逐行验证子查询，无法利用索引。
• 具体原理：子查询需全表扫描或全索引扫描，成本较高。
• 示例 SQL：

-- 失效
SELECT * FROM table t1 
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t1.id = t2.id);

---