联合索引（复合索引）是提高数据库查询性能的强大工具，但使用不当会导致其失效，引发全表扫描，严重拖慢查询速度。下面我将为你详细解析联合索引失效的常见场景、背后的原因，以及如何避免和优化。

为了让你快速建立整体认知，我先用一个表格总结最常见的联合索引失效场景：

失效场景	关键特征	示例（假设联合索引为 (a, b, c)）
​​违反最左前缀原则​​	查询条件未包含最左列	WHERE b = ? AND c = ?
​​范围查询阻断​​	某一列使用了范围查询（>, <, BETWEEN）	WHERE a = ? AND b > ? AND c = ?（c列失效）
​​对索引列运算或使用函数​​	在索引列上进行了计算或函数处理	WHERE a + 1 = ?或 WHERE UPPER(b) = ?
​​隐式类型转换​​	查询值的类型与索引列类型不匹配	WHERE a = '123'（但a是数值类型）
​​OR 连接非索引字段​​	使用OR连接条件，且其中有字段无索引	WHERE a = ? OR non_indexed_column = ?
​​LIKE 左模糊匹配​​	以通配符 %开头的LIKE查询	WHERE b LIKE '%pattern'
​​数据分布影响成本​​	优化器估算使用索引比全表扫描更慢	查询结果集超过表总行数的约30%

---

⚠️ 详解联合索引失效场景

1. 违反最左前缀原则

这是联合索引失效​​最常见​​的原因。联合索引的物理存储是按照定义时的列顺序排序的（例如 (a, b, c)是先按 a排序，a相同再按 b排序，以此类推）。因此，查询必须从最左边的列开始，才能利用这种有序性。

•  

  ​​失效示例​​：

  -- 联合索引 (a, b, c) SELECT * FROM table WHERE b = 2 AND c = 3; /* 缺少最左列a */ SELECT * FROM table WHERE a = 1 AND c = 3; /* 跳过了中间列b */

•  

  ​​背后原理​​：索引像电话簿，先按姓排序，再按名排序。如果你只知道名而不知道姓，就无法快速定位。

•  

  ​​特例（索引跳跃扫描）​​：在 ​​MySQL 8.0.13+​​，当最左列的选择性（区分度）非常高时，即使查询条件缺少最左列，优化器也可能对最左列的每个唯一值执行一次子查询，从而使用到索引。但这并非总能触发，不应依赖。

2. 范围查询后的列失效

在联合索引中，如果某一列使用了范围查询（>, <, BETWEEN），那么​​该列之后的所有索引列都将无法再使用索引​​进行快速定位。

•  

  ​​失效示例​​：

  -- 联合索引 (a, b, c) SELECT * FROM table WHERE a = 1 AND b > 10 AND c = 20; /* 只有a和b能用索引，c需回表后过滤 */

•  

  ​​优化建议​​：尽量使用 >=或 <=来替代 >或 <，有时可以让范围查询后的等值条件继续使用索引。但更根本的方法是​​在设计索引时，将等值查询的列放在范围查询的列之前​​。

3. 对索引列进行运算或使用函数

任何对索引列的计算、函数调用或表达式都会导致索引失效，因为索引存储的是原始值，无法与计算后的值进行匹配。

•  

  ​​失效示例​​：

  SELECT * FROM table WHERE YEAR(create_time) = 2023; /* 对索引列使用函数 */ SELECT * FROM table WHERE a * 2 = 10; /* 对索引列进行运算 */

•  

  ​​解决方案​​：将操作转移到等号的另一侧，使用原始列进行比较。

  -- 优化后 SELECT * FROM table WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; SELECT * FROM table WHERE a = 5; /* 10 / 2 = 5 */

4. 隐式类型转换

如果应用程序传入的参数类型与数据库表中索引列的定义类型不一致，MySQL 会进行隐式类型转换。​​如果转换是作用于索引列上（而非参数上），就相当于对列使用了函数，导致索引失效​​。

•  

  ​​失效示例​​：

  -- user_id 字段是 VARCHAR 类型，但传入数字 SELECT * FROM users WHERE user_id = 10086; /* 等效于 WHERE CAST(user_id AS SIGNED) = 10086 */

•  

  ​​解决方案​​：确保应用程序传入的参数类型与数据库列类型完全一致。

  SELECT * FROM users WHERE user_id = '10086'; /* 传入字符串 */

5. 使用 OR连接非索引字段

如果 OR条件连接的两个字段中，有一个字段没有索引，那么数据库优化器通常会选择放弃使用索引，直接进行全表扫描。

•  

  ​​失效示例​​：

  -- a 有索引，age 无索引 SELECT * FROM table WHERE a = 1 OR age = 25; /* 索引失效 */

•  

  ​​解决方案​​：

  1.  

     为 OR条件中的所有字段建立索引。

  2.  

     使用 UNION或 UNION ALL将查询拆分。

  SELECT * FROM table WHERE a = 1 UNION ALL SELECT * FROM table WHERE age = 25; /* 前提是age最好也有索引 */

6. 模糊查询使用左模糊 (LIKE '%xxx')

LIKE查询只有在模式串是​​右模糊​​（'xxx%'）时才能利用索引的有序性。左模糊（'%xxx'）或全模糊（'%xxx%'）无法利用索引进行定位，只能全表扫描。

•  

  ​​失效示例​​：

  SELECT * FROM users WHERE name LIKE '%明'; /* 无法使用 name 字段上的索引 */

•  

  ​​特殊技巧（覆盖索引）​​：如果查询的字段全部包含在某个联合索引中，即使使用左模糊，该索引也可能以​​覆盖索引​​的形式被使用，以避免回表。但这并非用于定位，而是用于减少IO。

7. 数据分布导致优化器放弃索引

数据库优化器是基于成本的。如果它估算发现需要回表查询的数据行数超过了表总行数的一个较大比例（例如 ​​20%-30%​​），它会认为使用索引的成本（索引扫描 + 大量回表随机IO）高于直接顺序扫描全表的成本，从而主动放弃使用索引。

•  

  ​​场景示例​​：在一个几乎所有人都是男性的用户表中，查询 WHERE gender = 'M'，即使 gender字段有索引，优化器也很可能选择全表扫描。

---

🛠️ 如何诊断与避免索引失效

1.  

   ​​使用 EXPLAIN命令​​：这是诊断索引问题的​​首选工具​​。在执行你的 SQL 语句前加上 EXPLAIN，查看执行计划。重点关注：

   •  

     type列：如果出现 ALL，表示全表扫描。

   •  

     key列：显示实际使用的索引，如果为 NULL则未使用索引。

   •  

     Extra列：如果出现 Using index，表示使用了覆盖索引，是好事；如果出现 Using filesort或 Using temporary，则需要警惕。

2.  

   ​​索引设计原则​​：

   •  

     ​​选择性高的列放左边​​：将区分度高（唯一值多）的列放在联合索引的左侧。

   •  

     ​​兼顾查询与排序/分组​​：如果查询中经常包含 ORDER BY或 GROUP BY，考虑将这些字段纳入索引设计，并注意顺序。

   •  

     ​​避免过多索引​​：索引虽好，但每个索引都会增加写操作的开销和存储空间。权衡利弊，优先设计高效的联合索引。

3.  

   ​​查询编写建议​​：

   •  

     ​​禁止 SELECT *​​：明确指定需要的列。这不仅能减少网络传输，更重要的是增加了查询走​​覆盖索引​​的几率，避免回表。

   •  

     ​​注意数据类型​​：确保应用程序传入的参数类型与数据库列类型一致，避免隐式类型转换。