在MySQL数据库的实际使用中，组合索引是常见的优化手段，但不少开发者会遇到组合索引不生效的情况，同时SELECT *查询也经常被提及会影响索引的使用效率，这两个问题本质上都和MySQL的索引执行机制有关。
组合索引的生效规则
组合索引是指基于表中多个字段创建的索引，它的生效核心遵循最左前缀原则，也就是索引会按照创建时的字段顺序依次生效，只有查询条件中包含最左前缀的字段时，索引才会被使用。
假设我们创建如下组合索引：
-- 创建用户表
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_name_age_city (name, age, city)
);
上述组合索引的字段顺序是name、age、city，以下是几种常见的索引生效和失效场景：
| 查询条件 | 索引是否生效 | 说明 |
|---|---|---|
| WHERE name = '张三' | 是 | 命中最左前缀字段name |
| WHERE name = '张三' AND age = 20 | 是 | 命中name和age两个前缀字段 |
| WHERE age = 20 | 否 | 缺少最左前缀字段name |
| WHERE name = '张三' AND city = '北京' | 是（部分生效） | 只命中name字段，city无法使用索引 |
组合索引失效的其他常见场景
除了不满足最左前缀原则，以下情况也会导致组合索引失效：
- 查询条件中对索引字段使用函数或运算，比如
WHERE YEAR(create_time) = 2023，会导致索引失效 - 查询条件中使用不等于、IS NULL、IS NOT NULL，比如
WHERE name != '张三'，可能导致索引失效 - 查询条件中使用LIKE以通配符开头，比如
WHERE name LIKE '%三'，索引无法生效 - 查询条件中字段类型不匹配，比如name是字符串类型，查询时写成
WHERE name = 123，会发生隐式转换导致索引失效
SELECT *查询为何会阻碍索引使用
SELECT *查询会返回表中的所有字段，这会影响索引覆盖特性的发挥。索引覆盖是指查询的字段全部包含在索引中时，数据库不需要回表查询数据行，直接通过索引就能返回结果，大幅提升查询效率。
还是以上面的user表为例，组合索引idx_name_age_city包含了name、age、city三个字段，如果我们执行以下查询：
-- 查询字段都在索引中，走索引覆盖，不需要回表 SELECT name, age, city FROM user WHERE name = '张三'; -- 使用SELECT *，会返回id、name、age、city等所有字段，id不在索引中，需要回表查询 SELECT * FROM user WHERE name = '张三';
在上面的例子中，第一个查询的字段都在组合索引中，数据库可以直接从索引中获取所有需要的数据，不需要访问主键索引对应的数据行。而第二个查询使用SELECT *，需要返回id字段，id不在组合索引中，所以数据库在通过组合索引找到符合条件的记录后，还需要根据主键id去主键索引中查询完整的行数据，这个过程就是回表。
回表操作会增加IO开销，当查询的数据量较大时，会明显降低查询效率，相当于阻碍了索引的充分利用。另外如果查询需要的字段很少，使用SELECT *会额外传输不需要的字段数据，也会浪费网络带宽。
优化建议
针对组合索引失效和SELECT *的问题，我们可以采取以下优化措施：
- 创建组合索引时，将查询频率高、区分度高的字段放在最左边，遵循最左前缀原则设计查询条件
- 避免使用SELECT *，只查询需要的字段，尽量让查询字段覆盖在索引中，利用索引覆盖特性
- 检查查询条件，避免出现函数运算、隐式转换、通配符开头等导致索引失效的写法
- 可以通过
EXPLAIN命令分析查询语句的执行计划，查看索引的使用情况，针对性调整查询或索引
以下是使用EXPLAIN分析查询的示例：
-- 分析SELECT *查询的执行计划 EXPLAIN SELECT * FROM user WHERE name = '张三'; -- 分析指定字段查询的执行计划 EXPLAIN SELECT name, age, city FROM user WHERE name = '张三';
执行EXPLAIN后，我们可以查看key字段是否使用了目标索引，Extra字段是否出现Using index（表示使用了索引覆盖），以此判断索引的使用是否符合预期。