【一】原理概述
【1】最佳左前缀原则
联合索引的生效依赖于查询条件中是否包含索引的 “最左前缀列”（即索引定义中的第一个列、前两个列、前三个列等，按顺序匹配）。当查询条件满足最左前缀时，索引会被部分或全部使用；否则，索引不会生效。
（1）最佳左前缀的核心逻辑
联合索引（如(a, b, c)）的存储结构是按列顺序逐级排序的：
（1）先按a排序排序；
（2）当a的值相同时，再按b排序；
（3）当a和b的值都相同时，最后按c排序。
这种有序性决定了索引的匹配必须从最左列开始：
（1）必须包含最左列：若查询条件中没有最左列（如a），则无法使用该联合索引。
（2）连续匹配：若包含最左列，但中间列缺失（如a和c，缺失b），则只能使用到最左列（a），后续列（c）无法生效。
（3）范围条件阻断后续列：若某列使用范围条件（如a=1 AND b>2 AND c=3），则范围列（b）右侧的列（c）无法使用索引。
（2）原理：联合索引的物理存储结构
联合索引在磁盘上以B + 树形式存储，其排序规则直接决定了 “最佳左前缀” 的必要性：
（1）叶子节点顺序：叶子节点按(a, b, c)的顺序从小到大排列，类似字典的排序逻辑（先按首字母，再按第二个字母，以此类推）。
（2）索引定位方式：查询时，数据库通过最左列快速定位到起始范围，再依次通过后续列缩小范围。若跳过最左列，索引的有序性被打破，无法有效定位（类似查字典跳过首字母，无法快速找到目标单词）。
示例：
联合索引(a, b, c)的部分叶子节点顺序：
(a=1, b=2, c=3) → (a=1, b=2, c=4) → (a=1, b=3, c=1) → (a=2, b=1, c=5) → ...
（1）若查询a=1 AND b=2，可直接定位到a=1的范围，再在其中找到b=2的子范围，高效匹配。
（2）若查询b=2（无a），索引中b=2的记录分散在a=1、a=3等不同范围中，无法通过索引快速定位，只能全表扫描。
（3）最佳左前缀的生效场景（案例说明）
设联合索引为(a, b, c)，以下是不同查询条件的索引使用情况：
（4）“最佳” 的含义：如何设计最优左前缀
“最佳” 左前缀不仅指 “从左到右匹配”，还强调选择过滤性最强的列作为左前缀，以最大化索引效率：
（1）过滤性（基数）优先：左前缀列应选择区分度高（不重复值多）的列。例如，(性别, 年龄)的过滤性远低于(年龄, 性别)，因为 “性别” 只有 2 个值，无法有效缩小范围。
（2）等值条件优先于范围条件：等值条件（=、IN）的过滤性比范围条件（>、<）更强，应放在左前缀。例如，(a, b)中a用=、b用>，比a用>、b用=更高效。
（3）覆盖查询优先：若查询的列（SELECT子句）都包含在联合索引中，即使左前缀过滤性稍弱，也可能因 “覆盖索引”（无需回表）更优。
（5）常见误区与注意事项
（1）条件顺序不影响左前缀匹配：MySQL 优化器会自动调整等值条件的顺序，只要包含最左列即可。例如，WHERE b=2 AND a=1与WHERE a=1 AND b=2等价，均能使用(a, b)索引。
（2）函数 / 运算破坏左前缀：对左前缀列使用函数（如SUBSTR(a, 1, 2)=‘ab’）会导致索引失效，因为函数会破坏索引的有序性。
（3）左前缀并非越长越好：过多的左前缀列会增加索引存储成本和维护开销（如插入 / 更新时的索引重建），需平衡查询需求。
【2】跳跃索引
MySQL 有跳跃索引，即索引跳跃扫描（Index Skip Scan），从 MySQL 8.0.13 版本开始支持。
它主要用于优化不符合组合索引最左前缀原则的查询，使得在某些情况下，即使查询条件中缺失联合索引最左边的字段，也能够使用联合索引，从而减少不必要的扫描。例如，表 t1 有联合索引 (f1,f2)，当查询条件为 f2 > 40 时，在 MySQL 8.0 版本中会使用索引跳跃扫描，对 f1 字段的每个唯一值进行 f2 的范围扫描，而不是像之前的版本那样进行全索引扫描。
不过，索引跳跃扫描有一些使用限制：
（1）查询只能涉及一张表，多表关联无法使用。
（2）查询字段必须是索引中的字段。
（3）组合索引形式为 ((A1, …, Ak,) B1, …, Bm, C (, D1, …, Dn))，A、D 可以为空，但 B、C 不能为空。
（4）查询不使用 GROUP BY 或 DISTINCT。
（5）A1, …, Ak 上的谓词必须是等式谓词并且它们必须是常量，包括 IN () 运算符。
（6）查询必须是联合查询，即由 AND 和 OR 连接的条件：(cond1 (key_part1) OR cond2 (key_part1)) AND (cond1 (key_part2) OR …) AND …
（7）C 上必须有范围条件，D 列上的条件必须与 C 上的范围条件结合使用。
【3】mysql优化器的作用
基本在5.6版本就引入了
（1）最左前缀匹配的 “条件重排” 优化
联合索引遵循 “最左前缀原则”（即索引生效需从左到右匹配列），但优化器会自动调整查询条件的顺序，只要条件组合符合最左前缀，就能利用联合索引。
（1）原理：优化器会忽略查询条件的 “书写顺序”，仅关注条件是否覆盖联合索引的最左连续列。
（2）例子：
联合索引为(a, b, c)，查询条件为where b=2 and a=1。
优化器会自动将条件重排为where a=1 and b=2，符合(a, b)的最左前缀，因此可以使用联合索引(a,b,c)的前两列。
（2）索引选择性优先选择
优化器会评估联合索引的 “选择性”（即索引列区分度，选择性 = 不重复值数量 / 总记录数），优先选择选择性更高的联合索引。
（1）原理：选择性越高的索引，能过滤掉更多无关数据，减少后续扫描 / 回表的行数。
（2）例子：
表中有 100 万条记录，联合索引(a,b)中a的选择性为 90%（90 万不重复值），联合索引(c,d)中c的选择性为 10%（10 万不重复值）。
若查询条件同时匹配两个索引的最左前缀，优化器会优先选择(a,b)，因为其能更快定位目标数据。
（3）覆盖索引扫描（避免回表）
当联合索引包含查询所需的所有列（select 子句 + where 条件列）时，优化器会直接使用索引完成查询，无需回表访问原表数据（“索引覆盖扫描”）。
（1）原理：索引文件通常比原表小，扫描索引的 IO 成本更低。
（2）例子：
联合索引为(a, b, c)，查询为select a, b from table where a=1 and b=2。
由于(a,b)是索引的前两列，且 select 仅需a,b，优化器会直接扫描索引(a,b,c)的前两列，无需回表。
（4）索引下推（Index Condition Pushdown, ICP）
优化器会将部分过滤条件 “下推” 到存储引擎层，在索引扫描时直接过滤数据，减少回表的行数。
（1）原理：避免存储引擎将不符合条件的索引条目返回给服务器层（需回表），提前过滤以降低成本。
（2）例子：
联合索引为(a, b, c)，查询为where a=1 and b>2 and c=3。
优化器会让存储引擎在扫描a=1的索引条目时，同时检查b>2和c=3（而非仅扫描a=1后回表再过滤），只将符合条件的条目回表。
（5）索引合并（针对多索引场景）
当查询条件涉及多个独立索引（包括联合索引的部分列）时，优化器可能 “合并” 多个索引的结果（如intersect交集、union并集）。
（1）原理：当单个索引无法覆盖所有条件，但多个索引的组合能更高效过滤时，会合并使用。
（2）例子：
有联合索引(a,b)和(c,d)，查询为where (a=1 and b=2) or (c=3 and d=4)。
优化器可能分别扫描两个索引，再合并结果（union），比全表扫描更快。
（6）成本估算与 “放弃索引” 的选择
优化器会计算 “使用联合索引的成本”（索引扫描 + 回表）和 “全表扫描的成本”，选择成本更低的方式。
（1）原理：若联合索引匹配的行数过多（如超过表记录的 30%），回表成本可能高于全表扫描，优化器会放弃索引。
（2）例子：
表有 100 万行，联合索引(a,b)中a=1匹配 50 万行（占 50%），查询where a=1 and b=2。
优化器可能选择全表扫描（直接扫描 50 万行），而非先扫索引再回表 50 万行（IO 成本更高）。
（7）范围条件后的列 “部分生效” 优化
联合索引中，若某列是 “范围条件”（如>、<、between），其右侧的列无法使用索引，但优化器会优先利用范围列左侧的等价条件。
（1）原理：等价条件（=）能精确定位，范围条件后的数据无序，无法再用索引过滤。
（2）例子：
联合索引(a,b,c)，查询where a=1 and b>2 and c=3。
优化器会用a=1定位索引范围，再在该范围内扫描b>2的条目（b的范围条件），但c=3无法利用索引（需回表后过滤）。
（8）利用索引优化排序 / 分组
若联合索引的列顺序与ORDER BY/GROUP BY的顺序一致，优化器会直接利用索引排序，避免额外的 “文件排序”（Using filesort）。
（1）原理：联合索引的存储是有序的（按列顺序排序），可直接复用索引的有序性。
（2）例子：
联合索引(a,b,c)，查询select a,b from table where a=1 order by b。
优化器会利用a=1对应的索引条目（已按b排序），无需额外排序。
【二】案例分析
【1】案例一
select * from table where a=111 and b>222 and c=333
（1）如何创建联合索引？
最优联合索引为 (a, b, c)。
原因：查询条件包含a=（等值）、b>（范围）、c=（等值）。根据最左前缀原则，a作为最左列，能快速过滤大部分数据；b作为第二列，范围查询可进一步过滤；c虽在范围条件后无法使用索引，但前两列已能有效缩小查询范围。
（2）联合索引(a,b,c)会不会走索引？
会走索引，但仅使用索引的前两列(a, b)。
原因：a=是等值条件，匹配索引最左前缀，会触发索引；b>是范围条件，能继续使用索引的b部分；但范围条件（>、<、between等）后的列（此处c）无法再使用索引（MySQL 无法利用范围后的列进行索引过滤）。
【2】案例二
select * from table where c=111 and b=222
（1）联合索引(a,b,c)会不会走索引？
不会。
原因：联合索引(a,b,c)的最左前缀是a，但查询条件中没有a，不满足最左前缀匹配原则，因此索引完全失效。
（2）联合索引(b,c)会不会走索引？
会。
原因：联合索引(b,c)的最左前缀是b，查询条件包含b=（等值），满足最左前缀；且c=在b之后，符合索引顺序，因此整个索引(b,c)会被使用（b过滤后，c进一步过滤）。
【3】案例三
select * from table where a=333 and b=222 and c=111
联合索引(a,b)、(b,c)、(c,a)会走哪个索引？
三个索引中，(a,b)和(b,c)可能被使用，(c,a)大概率不被使用，具体由 MySQL 优化器根据索引过滤效率选择。
原因：
（1）(a,b)：最左前缀a在查询条件中（a=333），满足匹配，会被使用（a过滤后，b进一步过滤）；
（2）(b,c)：最左前缀b在查询条件中（b=222），满足匹配，会被使用（b过滤后，c进一步过滤）；
能通过 b=222 和 c=111 的条件过滤数据，只是需要回表验证 a=333。
（3）(c,a)：最左前缀c在查询条件中（c=111），理论上可匹配，但a在c之后，而查询中a是等值条件，不过(c,a)的过滤效率通常低于(a,b)和(b,c)（除非c的基数极高），因此优化器可能优先选择前两者。
（4）(a,c)：
a 列会生效：索引的 a 部分会被用于快速定位匹配 a=333 的记录范围，减少扫描行数。
c 列不会生效：因为查询条件中存在 b=222（a 和 c 之间的列），而联合索引 (a,c) 中没有 b，导致 c 无法作为连续的前缀被使用（中间列缺失会阻断后续索引列的匹配）。
【三】汇总案例
【1】案例1：MySQL 优化器会自动调整查询条件
select * from table where b=222 and a=111（条件顺序为b,a），联合索引(a,b)是否生效？
会生效。
原因：MySQL 优化器会自动调整查询条件的顺序，将a=111提前，匹配联合索引(a,b)的最左前缀，因此索引会被正常使用。
【2】案例2：范围条件在中间，后续列无法使用索引
SQL：select * from table where a=111 and b>222 and c=333 and d=444，联合索引(a,b,c,d)的使用情况？
仅(a,b)部分生效。
原因：a=匹配最左前缀，b>是范围条件，触发索引的b部分，但范围条件后的c和d无法再使用索引（无论是否等值）。
【3】案例3：最左前缀列使用函数 / 运算，导致索引失效
select * from table where SUBSTR(a, 1, 2)='11' and b=222
联合索引(a,b)是否生效？
不生效。
原因：索引列a被函数SUBSTR处理，破坏了最左前缀的连续性，导致整个索引失效（MySQL 无法利用索引进行函数运算后的匹配）。
【4】案例 4：查询条件包含索引的部分前缀
select * from table where a=111
联合索引(a,b,c)是否生效？
会生效（仅a部分）。
原因：满足最左前缀（仅a），索引的a部分会被用于过滤数据，无需扫描全表。
【5】案例 5：联合索引列全为等值条件，顺序不影响生效
SQL：select * from table where c=333 and a=111 and b=222，联合索引(a,b,c)是否生效？
会全量生效。
原因：虽然查询条件顺序是c,a,b，但优化器会调整为a=111 and b=222 and c=333，完全匹配联合索引(a,b,c)的顺序，因此整个索引被使用。
【四】总结
（1）最左前缀原则是核心：联合索引(col1, col2, col3)仅在查询条件包含col1、col1+col2、col1+col2+col3时生效（按顺序）。
（2）范围条件会阻断后续列：col2>等范围条件后，col3及之后的列无法使用索引。
（3）优化器会调整等值条件顺序：等值条件（=、in）的顺序不影响索引生效（优化器会自动对齐索引顺序），但范围条件的位置固定。
（4）索引列避免函数 / 运算：对索引列使用函数或运算会导致索引失效。
（5）创建联合索引时，应将过滤性强（基数高）的列放在前面，优先包含等值条件列，再包含范围条件列，以最大化索引效率。