前言:索引能提升查询效率但不是建了索引就一定会用上。MySQL 最终是否走索引还要看查询条件、返回字段、排序分组方式以及优化器对成本的判断。这篇主要整理 MySQL 索引使用规则联合索引怎么匹配、哪些写法会导致索引失效、覆盖索引为什么能减少回表以及实际建索引时应该遵守哪些原则。一、联合索引的核心最左前缀法则联合索引也叫复合索引一个索引里包含多个列。例如createindexidx_user_pro_ageontb_user(profession,age);这个索引的顺序是 profession、age。使用时要遵守最左前缀法则也就是查询条件要从索引最左边的列开始并且不能跳过中间列。explainselect*fromtb_userwhereprofession软件工程;explainselect*fromtb_userwhereprofession软件工程andage25;explainselect*fromtb_userwhereage25;前两个查询都能从 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软件工程andage25andstatus启用;profession 是等值查询可以先使用。age 是范围查询MySQL 会在 age 范围内查找数据。status 虽然也写在查询条件中但它在范围条件右侧可能不能继续参与完整的索引定位。有些资料会提到把大于、小于改成大于等于、小于等于可能在部分场景中让执行计划表现不同。但这个不能当成固定优化公式。大于等于、小于等于本质上也属于范围条件最终还是要看EXPLAIN的结果。范围条件后面的列不一定还能继续作为高效定位条件。三、常见索引失效情况索引失效并不代表索引被删除了而是这条 SQL 没有办法按预期利用索引。常见情况有下面几类。1. 在索引列上进行运算或函数处理explainselect*fromtb_userwheresubstring(phone,10,2)15;phone 字段如果有索引这里也很难直接利用。因为 MySQL 不是拿原始 phone 值去匹配而是先对 phone 做 substring 处理再比较结果。写 SQL 时要尽量让索引列保持原样不要在索引列外面套函数、运算表达式。2. 字符串类型字段不加引号explainselect*fromtb_userwherephone17799990015;如果 phone 是字符串类型这里没有加引号可能触发隐式类型转换。类型转换一旦发生就可能让索引无法按原本的字符串规则使用。正确写法应该是explainselect*fromtb_userwherephone17799990015;3. 头部模糊查询explainselect*fromtb_userwhereprofessionlike%工程;explainselect*fromtb_userwhereprofessionlike%工程%;这两种写法都在前面加了百分号MySQL 无法从索引的起点开始匹配索引效果会明显变差。如果只是尾部模糊匹配通常更容易利用索引explainselect*fromtb_userwhereprofessionlike软件%;4. or 条件中有一侧没有索引explainselect*fromtb_userwherephone17799990015oraddress北京;如果 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 四个字段现在要优化这条 SQLselectid,username,passwordfromtb_userwhereusernameitcast;可以考虑建立联合索引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_demowhereb10;这里 where 条件没有使用 a不满足最左前缀。但如果只返回 b、c优化器可能选择扫描 idx_abc因为索引本身已经包含需要的字段。第二种情况是索引下推。MySQL 5.6 之后支持 ICP。它可以把一部分索引列上的过滤条件下推到存储引擎层先在索引层过滤一批数据减少回表次数。select*fromtb_demowherea1andc3;如果索引是 a、b、ca 可以按最左前缀使用c 虽然跳过了 b但仍可能通过索引下推参与过滤。开启状态可以关注optimizer_switchindex_condition_pushdownon。第三种情况是排序或分组。如果 order by、group by 的字段顺序和联合索引顺序匹配优化器可能利用索引顺序减少额外排序。不过这类优化对字段顺序、排序方向、where 条件都有要求不能只看“字段在索引里”就认为一定能避免排序。所以看到执行计划里使用了索引时还要继续看 type、key_len、rows 和 Extra。它可能是高效定位也可能只是全索引扫描。八、单列索引和联合索引怎么选单列索引是一个索引只包含一个字段。联合索引是一个索引包含多个字段。如果业务里经常按多个条件组合查询通常优先考虑联合索引而不是给每个字段都单独建一个索引。例如经常按职业、年龄、状态查询select*fromtb_userwhereprofession软件工程andage25andstatus启用;比起分别给 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 IndexesMySQL 8.0 Reference Manual: Index HintsMySQL 8.0 Reference Manual: Index Condition Pushdown Optimization