在MySQL中，组合索引失效的常见场景及原理

一、‌未遵循最左前缀原则‌
失效原理‌：组合索引使用时必须包含最左侧列，且条件顺序需与索引定义一致。
示例‌：
索引为(a, b, c)，以下场景会导致失效：
sql
Copy Code
WHERE b=1; -- 未包含最左列a
WHERE a=1 AND c=3; -- 跳过中间列b
WHERE b=1 AND c=3; -- 完全未包含a

二、‌范围查询后的列无法使用索引‌
失效原理‌：若组合索引中的某列使用范围查询（如>、<、BETWEEN），其右侧的列无法继续使用索引。
示例‌：
sql
Copy Code
WHERE a=1 AND b>10 AND c=2; -- 仅a和b使用索引，c失效

三、‌对索引列使用函数或表达式‌
失效原理‌：对组合索引中的任意列进行函数运算或表达式计算，会导致该列及其右侧列索引失效。
示例‌：
sql
Copy Code
WHERE UPPER(a)='ABC'; -- 对a使用函数
WHERE a+1=5; -- 对a进行运算
WHERE CONCAT(a,b)='ab'; -- 组合列参与运算

四、‌隐式数据类型转换‌
失效原理‌：查询条件的数据类型与索引列定义不一致时，触发隐式转换，导致索引失效。
示例‌：
sql
Copy Code
-- 假设a是VARCHAR类型且有索引
WHERE a=10086; -- 数值被隐式转为字符串，索引失效

五、‌使用OR连接非索引列‌
失效原理‌：OR连接的条件中若包含未索引的列，优化器可能放弃使用索引。
示例‌：
sql
Copy Code
WHERE a=1 OR d=2; -- 若d无索引，触发全表扫描

六、‌以通配符开头的LIKE模糊查询‌
失效原理‌：左模糊（LIKE '%abc'）无法利用索引树定位，导致组合索引失效。
例外‌：右模糊（LIKE 'abc%'）仍可使用索引。
七、‌数据量过少或全表扫描更优‌
失效原理‌：当表数据量极小时，优化器可能认为全表扫描成本低于索引查询。
阈值‌：通常数据量低于几百行时易触发。
八、‌联合索引列顺序与查询条件不匹配‌
失效原理‌：即使包含所有索引列，但条件顺序与索引定义不同，可能导致索引未被充分利用。
示例‌：
索引为(a, b, c)，但查询条件为WHERE b=1 AND a=2 AND c=3，仍可命中索引（优化器自动调整顺序），但若跳过中间列则失效。
九、‌使用NOT或IS NULL/IS NOT NULL‌
失效原理‌：否定条件（如NOT、IS NULL）可能导致优化器放弃使用索引。
示例‌：
sql
Copy Code
WHERE a IS NULL; -- 可能触发全表扫描
WHERE NOT a=1; -- 否定条件导致失效

总结优化建议
优先高频查询条件‌：将高频查询的列放在组合索引左侧。
避免范围查询后置‌：范围查询列尽量放在组合索引末尾。
覆盖索引优化‌：若查询仅需索引列，可避免回表提升性能。
强制索引提示‌：通过FORCE INDEX强制使用索引（需谨慎）。

通过EXPLAIN命令验证索引实际使用情况，结合数据分布和查询模式综合优化。