MySql 索引(2)-优快云博客

1.索引覆盖原理

InnoDB 采用聚簇索引（Clustered Index）作为核心存储结构，所有数据最终都存储在聚簇索引的叶子节点中。其他索引（如单列索引、联合索引）均为非聚簇索引（Secondary Index，二级索引），其叶子节点仅存储 “索引键值” 和 “聚簇索引的主键值”（而非整行数据）。

这就导致了常规查询的 “痛点”——回表（Table Lookup）：当查询使用非聚簇索引时，数据库先通过非聚簇索引找到对应的 “主键值”；

再用 “主键值” 去聚簇索引中查找完整的行数据（即 “回表”）；

最终返回查询所需的列。

示例：假设有一张user表，结构如下（id为主键，聚簇索引；age为非聚簇索引）：

若执行查询：

SELECT name FROM user WHERE age = 25

第一步：通过age的非聚簇索引，找到age=25对应的主键id=1；

第二步：用id=1回表到聚簇索引，找到name=Alice；

第三步：返回name字段

“回表” 本质是两次索引查找，会增加 IO 开销（尤其是数据量大、查询频繁时），降低效率。

索引覆盖的定义索引覆盖是指：查询语句所需的所有列（包括SELECT子句、WHERE条件、ORDER BY/GROUP BY子句中的列），都能直接从某一个（或多个）索引中获取，无需回表到聚簇索引查找完整行数据。

此时，这个能 “覆盖” 查询所有需求的索引，被称为覆盖索引（Covering Index）。

沿用上面的例子：若我们提前创建联合索引idx_age_name (age, name)，

再执行SELECT name FROM user WHERE age = 25;

索引idx_age_name的叶子节点存储(age, name)和主键id；

查询所需的age（条件列）和name（结果列）均在该索引中，直接读取索引即可返回结果，无需回表—— 这就是索引覆盖。

但是要注意

1.WHERE条件、ORDER BY、GROUP BY中的列，需是覆盖索引的一部分，且遵循最左前缀原则（联合索引的匹配从左到右，不能跳过左侧列）。(也就是最左匹配原则)

2.若查询包含LIKE '%xxx'（左模糊）、OR（未覆盖所有条件列）、函数操作（如YEAR(created_at)=2024）等，会导致索引失效，无法触发索引覆盖。

3.SELECT子句中的所有列（包括聚合函数的参数列，如COUNT(age)、MAX(name)），必须是覆盖索引的 “键列” 或 “默认附加列（主键）”。

2.索引最左匹配原则

联合索引在被查询使用时，会从索引的最左侧列开始，依次向右匹配查询条件。

只有当左侧列满足 “有效匹配条件” 时，才会继续匹配右侧列；

若左侧列无匹配条件或条件无效，则后续列的索引无法被利用，索引整体失效（或仅部分生效）。

核心一句话：联合索引的 “生效范围” 取决于从最左列开始的连续匹配列数。

联合索引 B + 树的排序规则联合索引的 B + 树会按照索引列的顺序（a→b→c）逐层排序：

第一层排序：先按a列的值升序排列（若a相同，进入下一层）；

第二层排序：在a值相同的分组内，按b列的值升序排列（若b相同，进入下一层）；

第三层排序：在a和b值均相同的分组内，按c列的值升序排列。

最终，B + 树的叶子节点会形成一个按 “a→b→c” 顺序严格有序的索引键值序列

B + 树的查询效率依赖于 “有序性”—— 通过二分查找快速定位范围。

而联合索引的有序性是 “分层” 的，只有先确定左侧列的范围，才能利用右侧列的有序性：

若查询条件包含a（最左列），则可先通过a的有序性定位到 “a= 某值” 的分组，该分组内的b是有序的，进而可通过b定位；

若查询条件不包含a，则无法确定任何分组，B + 树的有序性完全无法利用，只能遍历全表（或全索引扫描）。

规则 1：从最左列开始，连续匹配才生效只有从a（最左列）开始，依次匹配b、c，才能完全利用索引；若跳过某一列，后续列的索引会失效

规则 2：“范围条件” 会终止后续列的匹配若某一列使用 “范围查询”（如>、<、>=、<=、between），则该列之后的所有列的索引会失效（因为范围查询后的列失去有序性）。

规则 3：查询条件的顺序不影响（优化器会调整） MySQL 的查询优化器会自动调整where子句中条件的顺序，使其符合最左匹配的顺序，因此条件书写顺序不影响索引生效。

规则 4：前缀匹配（like）支持最左，后缀 / 中缀不支持若对最左列使用like前缀匹配（如a like '张%'），索引仍可生效；但后缀匹配（a like '%张'）或中缀匹配（a like '%张%'）会破坏有序性，导致索引失效。

常见误区辨析

“只要包含索引列，就能用索引” 错误。例如idx_a_b_c，查询a=1 and c=3，虽包含a和c，但跳过b，c的索引失效，仅a生效。

“范围条件放在最左列也能全用索引” 错误。例如a>1 and b=2，a是范围条件，b的索引失效，仅a生效。

“查询条件顺序必须和索引列顺序一致” 错误。MySQL 优化器会自动调整条件顺序（如b=2 and a=1→a=1 and b=2），不影响索引生效。

我们可以用下面这些代码区去尝试

EXPLAIN SELECT * FROM test_index WHERE a = 1;
EXPLAIN SELECT * FROM test_index WHERE a = 1 AND b = 2;
EXPLAIN SELECT * FROM test_index WHERE a = 1 AND b = 2 AND c = 3;
EXPLAIN SELECT * FROM test_index WHERE b = 2;
EXPLAIN SELECT * FROM test_index WHERE a = 1 AND c = 3;
EXPLAIN SELECT * FROM test_index WHERE b = 2 AND c = 3;
EXPLAIN SELECT * FROM test_index WHERE a > 1 AND b = 2;
EXPLAIN SELECT * FROM test_index WHERE a = 1 AND b > 2 AND c = 3;
EXPLAIN SELECT * FROM test_index WHERE b = 2 AND a = 1;
EXPLAIN SELECT * FROM test_index WHERE a LIKE '1%';
EXPLAIN SELECT * FROM test_index WHERE a LIKE '%1';

包含a的查询通常会使用索引

不包含a的查询不会使用索引

a+b的查询比仅a的查询使用更长的索引

范围查询（如a > 1）会导致后续列的索引失效

LIKE '1%'会使用索引，而LIKE '%1'不会