mysql 导致二级索引失效的场景 和 union all 和union的区别

一、索引结构相关场景

1. ‌违反最左前缀原则‌
   复合索引未从最左列开始查询，如索引 (a,b,c) 但条件为 b=1 AND c=2，导致无法通过 B+ 树定位数据区间16。

2. ‌隐式类型转换‌
   索引列与查询值类型不一致（如字符串列使用数字查询），导致 MySQL 隐式转换索引字段，使索引失效56。
   ‌示例‌：phone 字段为 varchar 类型时，查询 WHERE phone=13800138000 需改写为 WHERE phone='13800138000'。

---

二、查询条件操作相关场景

3. ‌对索引列使用函数或计算‌
   对索引列进行运算（如 YEAR(create_time)）或函数调用，导致无法匹配索引原始值56。
   ‌优化‌：改写为范围查询，如 WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。

4. ‌LIKE 以通配符开头‌
   模糊查询使用 % 开头（如 name LIKE '%John%'），导致 B+ 树无法定位前缀67。
   ‌替代方案‌：使用覆盖索引或全文索引（FULLTEXT）。

5. ‌范围查询后的索引列失效‌
   复合索引中，若某列使用范围查询（如 a>1），其后的索引列无法继续使用索引68。
   ‌示例‌：索引 (a,b,c) 时，WHERE a>1 AND b=2 中 b 可能失效。

6. ‌使用 OR 连接非索引列‌
   OR 条件中若存在非索引列（如 a=1 OR d=2 且 d 无索引），优化器会转向全表扫描56。
   ‌优化‌：拆分为 UNION 查询或为 d 添加索引。

---

三、数据特性与优化器决策相关场景

7. ‌不等操作符（!= 或 <>）‌
   未使用覆盖索引时，WHERE a != 1 可能导致索引失效58。

8. ‌IS NOT NULL 条件‌
   未覆盖索引时，WHERE a IS NOT NULL 可能无法使用索引58。

9. ‌小表查询优化器决策‌
   当表数据量较小时，优化器可能直接选择全表扫描而非索引5。

---

四、索引设计相关场景

10. ‌联合索引中间列条件缺失‌
    若跳过中间列直接使用后续列，如索引 (code,age,name) 但查询仅包含 code 和 name，则 name 无法使用索引27。

11. ‌字符集或排序规则不一致‌
    不同表或字段的字符集/排序规则差异可能导致索引失效8。

---

总结

二级索引失效的核心原因可归纳为：

• ‌索引结构破坏‌（如未遵循最左前缀、类型转换）；
• ‌查询操作干扰‌（如函数、范围查询、OR 条件）；
• ‌优化器策略调整‌（如小表全扫描、隐式转换）。
  通过合理设计索引、规范查询条件及监控执行计划（EXPLAIN），可有效规避这些问题26。

然后效率方面，UNION需要去重和排序，所以效率低，而UNION ALL直接合并，效率更高。像搜索结果[6]和[7]都提到这一点，比如[6]说UNION ALL效率高于UNION，[7]也提到UNION ALL不需要排序，所以更快。