复合索引失效的常见情况

复合索引（也称为联合索引或多列索引）是由多个列组成的索引，当查询条件符合索引的最左前缀原则时，复合索引才能发挥最大作用。以下是复合索引失效的几种常见情况：
1. 不遵循最左前缀原则
复合索引按照定义的列顺序从左到右匹配，如果查询条件没有包含最左边的列，索引将失效。

-- 假设有复合索引 (col1, col2, col3)
-- 生效情况：
SELECT * FROM table WHERE col1 = 'a';
SELECT * FROM table WHERE col1 = 'a' AND col2 = 'b';
SELECT * FROM table WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c';
-- 失效情况：
SELECT * FROM table WHERE col2 = 'b'; -- 缺少col1
SELECT * FROM table WHERE col3 = 'c'; -- 缺少col1和col2

2. 中间列缺失
即使查询条件包含最左列，但如果跳过了中间的列，索引只能部分使用。

-- 索引 (col1, col2, col3)
SELECT * FROM table WHERE col1 = 'a' AND col3 = 'c';
-- 只能用到col1的索引，col3无法使用索引

3. 对索引列使用函数或运算
对索引列进行函数操作、计算或类型转换会导致索引失效。

-- 失效情况：
SELECT * FROM table WHERE YEAR(col1) = 2023;
SELECT * FROM table WHERE col1 + 1 = 10;
SELECT * FROM table WHERE CAST(col1 AS CHAR) = '100';

4. 使用范围查询后的列失效
当对索引列使用范围查询(>, <, LIKE, BETWEEN等)后，其后的索引列无法使用索引。

-- 索引 (col1, col2, col3) 
-- 部分失效情况： SELECT * FROM table WHERE col1 = 'a' AND col2 > 'b' AND col3 = 'c';
-- col1和col2能使用索引，但col3无法使用索引

5. 使用OR条件
如果OR条件中有一个列没有索引，整个查询将不使用索引。

-- 假设col1有索引，col4没有索引
SELECT * FROM table WHERE col1 = 'a' OR col4 = 'd'; -- 索引失效

6. 使用NOT、!=、<>操作符
这些否定操作符通常会导致索引失效。

SELECT * FROM table WHERE col1 != 'a'; -- 索引失效
SELECT * FROM table WHERE col1 NOT IN ('a', 'b'); -- 索引失效

7. LIKE以通配符开头

SELECT * FROM table WHERE col1 LIKE '%abc'; -- 索引失效
SELECT * FROM table WHERE col1 LIKE 'abc%'; -- 可以使用索引

8. 数据类型不匹配
隐式类型转换会导致索引失效。

-- 假设col1是varchar类型
SELECT * FROM table WHERE col1 = 123; -- 数字与字符串比较，索引失效

9. 索引列使用IS NULL或IS NOT NULL

SELECT * FROM table WHERE col1 IS NULL; -- 可能无法使用索引

10. 表数据量很小
当表中数据量很少时，优化器可能选择全表扫描而非使用索引。

优化建议
设计复合索引时，将选择性高的列放在前面

查询语句尽量符合最左前缀原则

避免对索引列进行计算或函数操作

对于范围查询，尽量放在复合索引的最后

考虑使用覆盖索引(查询列都在索引中)提高性能

理解这些索引失效场景可以帮助设计更有效的索引和编写更优化的SQL查询。

不同场景对比
查询条件 复合索引 索引使用情况
A,B,D (A,B,C) 部分使用(A,B)
A,B,D (A,B,D) 完全使用
A,B,D (A,D,B) 部分使用(A)
A,B,D (B,A,D) 可能使用(B,A)或全表扫描