在数据库调优这条路上，索引绝对是绕不开的核心话题。搭建索引的门槛不高，但真正用好它，需要对MySQL的索引机制和优化器行为有比较清晰的认识。这篇文章主要想系统整理一下：联合索引的匹配规则、哪些SQL写法会导致索引失效、覆盖索引为什么能减少回表，以及实际建索引时应遵循哪些原则。

前言

索引能提升查询效率，这不假，但建了索引不等于它一定会被用上。MySQL最终能不能走索引，还要看查询条件怎么写的、返回哪些字段、排序分组方式，以及优化器对成本的综合判断。换句话说，索引是工具，能不能用好，取决于写SQL的人。

一、联合索引的核心：最左前缀法则

联合索引也叫复合索引，一个索引里包含多个列。比如：

create index idx_user_pro_age on tb_user(profession, age);

这个索引的列顺序是profession、age。使用时要遵守最左前缀法则，简单来说，查询条件必须从索引最左边的列开始，并且不能跳过中间列。

explain select * from tb_user where profession = '软件工程';
explain select * from tb_user where profession = '软件工程' and age = 25;
explain select * from tb_user where age = 25;

前两个查询都能从profession开始匹配联合索引。第三个查询只用了age，跳过了最左边的profession，通常无法按这个联合索引进行高效查找。MySQL官方文档也有类似的说明：如果有一个三列索引col1、col2、col3，那么它可以用于col1、col1+col2、col1+col2+col3这种左侧连续组合；只查col2或col3，就不符合左侧前缀。

最左前缀不是”用了联合索引“这么简单，而是”从联合索引最左列开始连续匹配“。

二、范围查询会影响右侧列继续匹配

联合索引中，如果某一列使用了范围查询，右侧列可能无法继续作为高效定位条件。

例如有以下联合索引：

create index idx_user_pro_age_status on tb_user(profession, age, status);

查询语句如下：

explain select * from tb_user where profession = '软件工程' and age > 25 and status = '启用';

profession是等值查询，可以先使用。age是范围查询，MySQL会在age范围内查找数据。status虽然也写在查询条件中，但它在范围条件右侧，通常不能继续参与完整的索引定位。有些资料提到把大于、小于改成大于等于、小于等于，可能在部分场景中让执行计划表现不同，但不能当成固定优化公式。大于等于、小于等于本质上也属于范围条件，最终还是要看EXPLAIN的结果。

范围条件后面的列，不一定还能继续作为高效定位条件。

三、常见索引失效情况

索引失效并不是索引被删除了，而是这条SQL无法按预期利用索引。常见情况有以下几类。

1. 在索引列上进行运算或函数处理

explain select * from tb_user where substring(phone, 10, 2) = '15';

phone字段如果有索引，这里很难直接利用。因为MySQL不是拿原始phone值去匹配，而是先对phone做substring处理，再比较结果。写SQL时要尽量让索引列保持原样，不要在索引列外面套函数、运算表达式。

2. 字符串类型字段不加引号

explain select * from tb_user where phone = 17799990015;

如果phone是字符串类型，这里没有加引号，可能触发隐式类型转换，一旦发生，就可能让索引无法按原本的字符串规则使用。正确写法应该是：

explain select * from tb_user where phone = '17799990015';

3. 头部模糊查询

explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工程%';

这两种写法都在前面加了百分号，MySQL无法从索引的起点开始匹配，索引效果明显变差。如果只是尾部模糊匹配，通常更容易利用索引：

explain select * from tb_user where profession like '软件%';

4. or条件中有一侧没有索引

explain select * from tb_user where phone = '17799990015' or address = '北京';

如果phone有索引，但address没有索引，优化器可能认为走索引意义不大，最后选全表扫描。or查询不是一定不能用索引，关键要看or两侧字段是否都有合适索引，以及优化器评估后的成本。

5. 优化器认为全表扫描更快

有时候SQL写法没有明显问题，但MySQL仍然不走索引。原因可能是数据量太小、条件区分度太低，或者优化器认为全表扫描比走索引再回表更快。判断索引是否生效，不能只看“建没建索引”，还要看执行计划里的key、rows、Extra等信息。

四、SQL提示：use、ignore、force index

当一个字段既有单列索引，又在联合索引里出现时，MySQL会根据优化器成本选择索引。如果确实想影响优化器的选择，可以使用索引提示。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

use index是建议使用某个索引，但优化器仍然可能选择别的方案。ignore index是告诉优化器不要考虑某个索引。force index是更强的提示，表示强制优先使用指定索引。不过，强制使用索引不等于一定更快，如果数据区分度很低，或者回表成本很高，强行走索引反而可能变慢。实际使用时要结合执行计划和查询耗时一起判断。

五、覆盖索引：为什么尽量少写select *

覆盖索引，指的是查询使用了索引，并且需要返回的字段都能从索引中拿到，不需要再回到表里查询完整行数据。

例如表中有id、username、password、status四个字段，现在要优化这条SQL：

select id, username, password from tb_user where username = 'itcast';

可以考虑建立联合索引：

create index idx_user_name_pwd on tb_user(username, password);

如果id是主键，在InnoDB的二级索引中会保存主键值。这样查询username、password、id时，就可能直接从索引中拿到结果，不需要回表查询status等其他字段。这也是为什么不建议随手写select *。返回字段越多，越容易超出索引本身能提供的范围，最后就需要回表。

执行计划Extra字段里常见两个信息：Using index condition表示使用了索引条件下推，但仍可能需要读取完整行；Using where; Using index通常表示查询需要的字段可以从索引中拿到，不需要回表。

覆盖索引的价值，就是少一次回表。

六、前缀索引：长字符串字段怎么建索引

如果字段是varchar、text这类字符串类型，而且内容比较长，直接给整列建索引会占用更多空间，也会增加磁盘IO。这时可以使用前缀索引，只取字段前N个字符建立索引。

create index idx_email on tb_user(email(5));

前缀长度不是随便写的，要看区分度。可以先计算完整字段的选择性：

select count(distinct email) / count(*) from tb_user;

再计算不同前缀长度的选择性：

select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
select count(distinct substring(email, 1, 8)) / count(*) from tb_user;
select count(distinct substring(email, 1, 10)) / count(*) from tb_user;

选择性越接近完整字段，说明这个前缀长度越能区分数据。前缀太短，重复值多，过滤效果差；前缀太长，索引空间节省不明显。创建后可以通过show index查看sub_part，确认前缀索引截取的长度。

七、不完全满足最左前缀时，为什么有时看起来还走了索引

最左前缀法则是联合索引用于高效查找的基本规则，但实际执行计划里，有时即使SQL没有完全满足最左前缀，也可能看到MySQL使用了某个索引。这不代表最左前缀法则失效了，而是优化器可能在其他角度利用索引。

第一种情况是覆盖索引。如果查询字段都在联合索引中，即使where条件没有从最左列开始，MySQL也可能扫描整个索引来返回数据。因为扫描索引比扫描整张表更轻。

create index idx_abc on tb_demo(a, b, c);
select b, c from tb_demo where b = 10;

这里where条件没有使用a，不满足最左前缀。但如果只返回b、c，优化器可能选择扫描idx_abc，因为索引本身已经包含需要的字段。

第二种情况是索引下推（ICP）。MySQL 5.6之后支持ICP，它可以把一部分索引列上的过滤条件下推到存储引擎层，先在索引层过滤一批数据，减少回表次数。

select * from tb_demo where a = 1 and c = 3;

如果索引是a、b、c，a可以按最左前缀使用，c虽然跳过了b，但仍可能通过索引下推参与过滤。开启状态可以关注optimizer_switch=index_condition_pushdown=on。

第三种情况是排序或分组。如果order by、group by的字段顺序和联合索引顺序匹配，优化器可能利用索引顺序减少额外排序。不过这类优化对字段顺序、排序方向、where条件都有要求，不能只看“字段在索引里”就认为一定能避免排序。

所以，看到执行计划里使用了索引时，还要继续看type、key_len、rows和Extra。它可能是高效定位，也可能只是全索引扫描。

八、单列索引和联合索引怎么选

单列索引是一个索引只包含一个字段，联合索引是一个索引包含多个字段。如果业务里经常按多个条件组合查询，通常优先考虑联合索引，而不是给每个字段都单独建一个索引。

例如经常按职业、年龄、状态查询：

select * from tb_user where profession = '软件工程' and age = 25 and status = '启用';

比起分别给profession、age、status建三个单列索引，更常见的做法是根据查询频率和区分度建立一个联合索引：

create index idx_user_pro_age_status on tb_user(profession, age, status);

联合索引的好处是可以同时服务多条件过滤，并且在返回字段合适时形成覆盖索引，减少回表。但联合索引也不是越长越好，索引列越多，维护成本越高，插入、更新、删除数据时都要维护对应索引结构。

九、索引设计原则

索引设计可以按下面几条来判断：

1. 数据量较大，并且查询比较频繁的表，才更有必要建立索引。
2. 经常出现在where、order by、group by后面的字段，优先考虑索引。
3. 尽量选择区分度高的列，例如手机号、用户名这类重复率低的字段。
4. 字符串字段较长时，可以考虑前缀索引。
5. 多条件查询优先考虑联合索引，减少多个单列索引堆叠。
6. 控制索引数量，索引会提升查询，但也会降低增删改效率。
7. 如果索引列业务上不允许为空，建表时可以声明NOT NULL。

索引设计不是“给字段都建上”，而是围绕查询场景选择最少、最有效的索引。

十、实际排查时怎么判断索引用得好不好

平时排查SQL时，可以先看四个点。

第一，看possible_keys和key。possible_keys表示可能用到的索引，key表示最终实际选择的索引。

第二，看key_len。它可以帮助判断联合索引大概使用到了哪些列。尤其是联合索引中间出现范围查询、跳过字段时，key_len很有参考价值。

第三，看rows。rows越大，说明MySQL预计要扫描的数据越多。即使走了索引，如果rows很大，查询也不一定快。

第四，看Extra。Extra里如果出现覆盖索引、索引条件下推、临时表、文件排序等信息，都能帮助判断这条SQL还有没有优化空间。

总结

MySQL索引使用规则可以压缩成一句话：先看查询条件是否满足最左前缀，再看有没有函数、隐式转换、头部模糊、or条件这些失效写法，最后结合返回字段判断能不能形成覆盖索引。

建索引时不要只盯着某一个字段，而要把where条件、返回字段、排序分组和字段区分度放在一起看。真正好用的索引，通常不是数量最多的索引，而是刚好匹配高频查询场景、维护成本又可控的索引。

参考资料：

• MySQL 8.0 Reference Manual: Multiple-Column Indexes
• MySQL 8.0 Reference Manual: Index Hints
• MySQL 8.0 Reference Manual: Index Condition Pushdown Optimization