MySQL 组合索引失效及"SELECT *"问题分析

组合索引失效的常见原因

组合索引（复合索引）在以下情况下可能会失效：

1. 不遵循最左前缀原则

   • 组合索引(A,B,C)，查询条件中必须包含A才能使用索引
   • 例如：WHERE B=1 AND C=2 无法使用该组合索引

2. 使用了范围查询后的列

   • WHERE A=1 AND B>2 AND C=3 - 只有A和B能使用索引，C无法使用

3. 使用了函数或表达式

   • WHERE UPPER(A)='VALUE' 或 WHERE A+1=5

4. 使用了不等于(!=或<>)或NOT IN

   • WHERE A!=1 通常无法使用索引

5. 使用了OR条件

   • WHERE A=1 OR B=2 除非A和B都有独立索引

6. 数据类型不匹配

   • 例如索引列是字符串类型，但查询使用数字：WHERE A=123（A是VARCHAR）

"SELECT *" 为何会阻碍索引使用

1. 覆盖索引失效

   • 当查询只需要索引列时，MySQL可以只读取索引而不访问表数据（覆盖索引）
   • SELECT * 需要所有列数据，迫使MySQL回表查询

2. 优化器选择全表扫描

   • 当需要返回大量数据时，优化器可能认为全表扫描比索引+回表更高效

3. 索引列排序问题

   • 即使WHERE条件使用了索引，SELECT * 可能导致排序不使用索引

优化建议

1. **避免使用SELECT ***

   • 只查询需要的列，特别是当这些列都在索引中时

2. 合理设计组合索引

   • 将高频查询条件放在最左
   • 考虑查询顺序和排序需求

3. 使用EXPLAIN分析

   • 通过EXPLAIN SELECT...查看索引使用情况

4. 考虑索引合并

   • 对于OR条件，可以考虑使用UNION替代

5. 使用FORCE INDEX提示

   • 在特定情况下可以强制使用某个索引

示例

-- 组合索引(A,B,C)

-- 有效使用索引
SELECT A, B FROM table WHERE A=1 AND B=2;  -- 覆盖索引

-- 索引部分有效
SELECT * FROM table WHERE A=1 AND B>2;     -- 只用到A和B

-- 索引失效
SELECT * FROM table WHERE B=2;             -- 不满足最左前缀
SELECT * FROM table WHERE A=1 OR C=3;      -- OR条件

通过合理设计索引和查询语句，可以显著提高MySQL查询性能。