【MySQL | 第七篇】 索引使用规则
前言:
索引能提升查询效率，但不是建了索引就一定会用上。MySQL 最终是否走索引，还要看查询条件、返回字段、排序分组方式，以及优化器对成本的判断。
这篇主要整理 MySQL 索引使用规则：联合索引怎么匹配、哪些写法会导致索引失效、覆盖索引为什么能减少回表，以及实际建索引时应该遵守哪些原则。
一、联合索引的核心：最左前缀法则
联合索引也叫复合索引，一个索引里包含多个列。例如：
createindexidx_user_pro_ageontb_user(profession,age);这个索引的顺序是 profession、age。使用时要遵守最左前缀法则，也就是查询条件要从索引最左边的列开始，并且不能跳过中间列。
explainselect*fromtb_userwhereprofession='软件工程';explainselect*fromtb_userwhereprofession='软件工程'andage=25;explainselect*fromtb_userwhereage=25;前两个查询都能从 profession 开始匹配联合索引。第三个查询只使用 age，跳过了最左边的 profession，通常不能按这个联合索引进行高效查找。
MySQL 官方文档中也有类似说明：如果有一个三列索引 col1、col2、col3，那么它可以用于 col1、col1 + col2、col1 + col2 + col3 这样的左侧连续组合；如果只查 col2 或 col3，就不符合左侧前缀。
最左前缀不是“用了联合索引”这么简单，而是“从联合索引最左列开始连续匹配”。
二、范围查询会影响右侧列继续匹配
联合索引中，如果某一列使用了范围查询，右侧列可能无法继续作为高效定位条件。
例如有一个联合索引：
createindexidx_user_pro_age_statusontb_user(profession,age,status);查询语句如下：
explainselect*fromtb_userwhereprofession='软件工程'andage>25andstatus='启用';profession 是等值查询，可以先使用。age 是范围查询，MySQL 会在 age 范围内查找数据。status 虽然也写在查询条件中，但它在范围条件右侧，可能不能继续参与完整的索引定位。
有些资料会提到把大于、小于改成大于等于、小于等于，可能在部分场景中让执行计划表现不同。但这个不能当成固定优化公式。大于等于、小于等于本质上也属于范围条件，最终还是要看EXPLAIN的结果。
范围条件后面的列，不一定还能继续作为高效定位条件。
三、常见索引失效情况
索引失效并不代表索引被删除了，而是这条 SQL 没有办法按预期利用索引。常见情况有下面几类。
1. 在索引列上进行运算或函数处理
explainselect*fromtb_userwheresubstring(phone,10,2)='15';phone 字段如果有索引，这里也很难直接利用。因为 MySQL 不是拿原始 phone 值去匹配，而是先对 phone 做 substring 处理，再比较结果。
写 SQL 时要尽量让索引列保持原样，不要在索引列外面套函数、运算表达式。
2. 字符串类型字段不加引号
explainselect*fromtb_userwherephone=17799990015;如果 phone 是字符串类型，这里没有加引号，可能触发隐式类型转换。类型转换一旦发生，就可能让索引无法按原本的字符串规则使用。
正确写法应该是：
explainselect*fromtb_userwherephone='17799990015';3. 头部模糊查询
explainselect*fromtb_userwhereprofessionlike'%工程';explainselect*fromtb_userwhereprofessionlike'%工程%';这两种写法都在前面加了百分号，MySQL 无法从索引的起点开始匹配，索引效果会明显变差。
如果只是尾部模糊匹配，通常更容易利用索引：
explainselect*fromtb_userwhereprofessionlike'软件%';4. or 条件中有一侧没有索引
explainselect*fromtb_userwherephone='17799990015'oraddress='北京';如果 phone 有索引，但是 address 没有索引，优化器可能认为走索引意义不大，最后选择全表扫描。
or 查询不是一定不能用索引，关键要看 or 两侧字段是否都有合适索引，以及优化器评估后的成本。
5. 优化器认为全表扫描更快
有时候 SQL 写法没有明显问题，但 MySQL 仍然不走索引。原因可能是数据量太小、条件区分度太低，或者优化器认为全表扫描比走索引再回表更快。
所以判断索引是否生效，不能只看“建没建索引”，还要看执行计划里的 key、rows、Extra 等信息。
四、SQL 提示：use、ignore、force index
当一个字段既有单列索引，又在联合索引里出现时，MySQL 会根据优化器成本选择索引。如果想影响优化器选择，可以使用索引提示。
explainselect*fromtb_useruseindex(idx_user_pro)whereprofession='软件工程';explainselect*fromtb_userignoreindex(idx_user_pro)whereprofession='软件工程';explainselect*fromtb_userforceindex(idx_user_pro)whereprofession='软件工程';use index是建议使用某个索引，但优化器仍然可能选择别的方案。
ignore index是告诉优化器不要考虑某个索引。
force index是更强的提示，表示强制优先使用指定索引。
不过，强制使用索引不等于一定更快。如果数据区分度很低，或者回表成本很高，强行走索引反而可能变慢。实际使用时要结合执行计划和查询耗时一起判断。
五、覆盖索引：为什么尽量少写 select *
覆盖索引指的是：查询使用了索引，并且需要返回的字段都能从索引中拿到，不需要再回到表里查询完整行数据。
例如表中有 id、username、password、status 四个字段，现在要优化这条 SQL：
selectid,username,passwordfromtb_userwhereusername='itcast';可以考虑建立联合索引：
createindexidx_user_name_pwdontb_user(username,password);如果 id 是主键，在 InnoDB 的二级索引中会保存主键值。这样查询 username、password、id 时，就可能直接从索引中拿到结果，不需要回表查询 status 等其他字段。
这也是为什么不建议随手写select *。因为返回字段越多，越容易超出索引本身能提供的范围，最后就需要回表。
执行计划 Extra 字段里常见两个信息：
Using index condition表示使用了索引条件下推，但仍可能需要读取完整行。
Using where; Using index通常表示查询需要的字段可以从索引中拿到，不需要回表。
覆盖索引的价值，就是少一次回表。
六、前缀索引：长字符串字段怎么建索引
如果字段是 varchar、text 这类字符串类型，而且内容比较长，直接给整列建索引会占用更多空间，也会增加磁盘 IO。
这时可以使用前缀索引，只取字段前 N 个字符建立索引。
createindexidx_emailontb_user(email(5));前缀长度不是随便写的，要看区分度。可以先计算完整字段的选择性：
selectcount(distinctemail)/count(*)fromtb_user;再计算不同前缀长度的选择性：
selectcount(distinctsubstring(email,1,5))/count(*)fromtb_user;selectcount(distinctsubstring(email,1,8))/count(*)fromtb_user;selectcount(distinctsubstring(email,1,10))/count(*)fromtb_user;选择性越接近完整字段，说明这个前缀长度越能区分数据。前缀太短，重复值多，过滤效果差；前缀太长，索引空间节省不明显。
创建后可以通过 show index 查看 sub_part，确认前缀索引截取的长度。
七、不完全满足最左前缀时，为什么有时看起来还走了索引
最左前缀法则是联合索引用于高效查找的基本规则，但实际执行计划里，有时即使 SQL 没有完全满足最左前缀，也可能看到 MySQL 使用了某个索引。
这不代表最左前缀法则失效了，而是优化器可能在其他角度利用索引。
第一种情况是覆盖索引。
如果查询字段都在联合索引中，即使 where 条件没有从最左列开始，MySQL 也可能扫描整个索引来返回数据。因为扫描索引比扫描整张表更轻。
createindexidx_abcontb_demo(a,b,c);selectb,cfromtb_demowhereb=10;这里 where 条件没有使用 a，不满足最左前缀。但如果只返回 b、c，优化器可能选择扫描 idx_abc，因为索引本身已经包含需要的字段。
第二种情况是索引下推。
MySQL 5.6 之后支持 ICP。它可以把一部分索引列上的过滤条件下推到存储引擎层，先在索引层过滤一批数据，减少回表次数。
select*fromtb_demowherea=1andc=3;如果索引是 a、b、c，a 可以按最左前缀使用，c 虽然跳过了 b，但仍可能通过索引下推参与过滤。开启状态可以关注optimizer_switch=index_condition_pushdown=on。
第三种情况是排序或分组。
如果 order by、group by 的字段顺序和联合索引顺序匹配，优化器可能利用索引顺序减少额外排序。不过这类优化对字段顺序、排序方向、where 条件都有要求，不能只看“字段在索引里”就认为一定能避免排序。
所以看到执行计划里使用了索引时，还要继续看 type、key_len、rows 和 Extra。它可能是高效定位，也可能只是全索引扫描。
八、单列索引和联合索引怎么选
单列索引是一个索引只包含一个字段。联合索引是一个索引包含多个字段。
如果业务里经常按多个条件组合查询，通常优先考虑联合索引，而不是给每个字段都单独建一个索引。
例如经常按职业、年龄、状态查询：
select*fromtb_userwhereprofession='软件工程'andage=25andstatus='启用';比起分别给 profession、age、status 建三个单列索引，更常见的做法是根据查询频率和区分度建立一个联合索引：
createindexidx_user_pro_age_statusontb_user(profession,age,status);联合索引的好处是可以同时服务多条件过滤，并且在返回字段合适时形成覆盖索引，减少回表。
但联合索引也不是越长越好。索引列越多，维护成本越高，插入、更新、删除数据时都要维护对应索引结构。
九、索引设计原则
索引设计可以按下面几条来判断：
- 数据量较大，并且查询比较频繁的表，才更有必要建立索引。
- 经常出现在 where、order by、group by 后面的字段，优先考虑索引。
- 尽量选择区分度高的列，例如手机号、用户名这类重复率低的字段。
- 字符串字段较长时，可以考虑前缀索引。
- 多条件查询优先考虑联合索引，减少多个单列索引堆叠。
- 控制索引数量，索引会提升查询，但也会降低增删改效率。
- 如果索引列业务上不允许为空，建表时可以声明
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