跳到主要内容

mysql索引

mysql索引，联合索引 - 最左前缀匹配原则

最左前缀原则是指：联合索引只有在查询条件包含 “最左连续的字段” 时才会生效。

以索引 (a, b, c) 为例：

查询条件是否使用索引使用的索引字段原因
WHERE a=?✅a包含最左字段 a
WHERE a=? AND b=?✅a, b包含最左连续字段 a, b
WHERE a=? AND b=? AND c=?✅a, b, c包含全部最左连续字段
WHERE b=?❌无缺少最左字段 a
WHERE b=? AND c=?❌无缺少最左字段 a
WHERE a=? AND c=?✅a仅使用最左字段 a，c 无法用（不连续）

注意：MySQL 优化器会自动调整条件顺序，例如 WHERE b=? AND a=? 会被优化为 WHERE a=? AND b=?，仍能使用索引。

索引失效场景分析​

索引失效分为一般索引失效（适用于单字段和联合索引）和联合索引特有失效（与最左前缀相关）两类，以下结合示例详细说明。

1. 一般索引失效场景（适用于所有索引）​

（1）对索引字段使用函数 / 表达式​

原因：MySQL 无法直接利用索引的有序性（函数会改变索引字段的值）。

  • 失效示例：
    -- 索引(a)
    WHERE ABS(a) = 1;  -- 对a用函数
    WHERE a + 1 = 2;   -- 对a用表达式
  • 有效示例：
    WHERE a = ABS(1);  -- 函数用在常量侧
    WHERE a = 2 - 1;   -- 表达式用在常量侧

（2）隐式类型转换​

原因：当索引字段类型与查询条件类型不一致时，MySQL 会对索引字段进行隐式转换，导致索引失效。

  • 关键规则：
    • 索引字段是字符串，查询条件是数字：失效（MySQL 会把字符串转成数字）。
    • 索引字段是数字，查询条件是字符串：有效（MySQL 会把字符串转成数字，不影响索引有序性）。
  • 失效示例：
    -- 索引(a)，a是VARCHAR类型
    WHERE a = 123;  -- 隐式转换：a转成数字
  • 有效示例：
    -- 索引(a)，a是VARCHAR类型
    WHERE a = '123';  -- 类型一致
    
    -- 索引(a)，a是INT类型
    WHERE a = '123';  -- 字符串转数字，不影响索引

（3）LIKE 通配符在开头​

原因：通配符 % 或 _ 在开头时，索引的前缀有序性无法利用。

  • 失效示例：
    -- 索引(name)
    WHERE name LIKE '%张三';  -- 通配符在开头
    WHERE name LIKE '%张三%'; -- 通配符在开头和结尾
  • 有效示例：
   WHERE name LIKE '张三%';  -- 通配符在结尾，前缀固定

（4）OR 连接条件且不全有索引​

原因：如果 OR 两边的条件有一个没有索引，MySQL 无法只走有索引的条件，会选择全表扫描。

  • 失效示例：
    -- 只有a有索引，b无索引
    WHERE a = 1 OR b = 2;
  • 有效示例：
    -- a和b都有索引
    WHERE a = 1 OR b = 2;

（5）数据量过小，优化器选择全表扫描​

原因：如果表数据量很小（如只有几百行），MySQL 认为全表扫描比走索引（需回表）更快，会放弃索引。

2. 联合索引特有失效场景​

（1）不满足最左前缀原则​

原因：查询条件缺少最左连续的字段，索引的有序性无法利用。

  • 失效示例（索引 (a, b, c)）：
    WHERE b = 1;           -- 缺少a
    WHERE b = 1 AND c = 2; -- 缺少a
    WHERE c = 3;           -- 缺少a、b

（2）最左前缀字段使用范围查询，后面的字段失效​

原因：范围查询（>、<、>=、<=、BETWEEN）会导致后面的字段在索引中无序，无法利用。

  • 示例（索引 (a, b, c)）：
    -- 仅使用a的索引，b、c失效
    WHERE a > 1 AND b = 2;
    
    -- 仅使用a、b的索引，c失效（b用了范围）
    WHERE a = 1 AND b > 2 AND c = 3;

优化技巧：将 “精确查询字段” 放在前面，“范围查询字段” 放在后面（如索引 (a, c, b)，如果 a 和 c 是精确查询，b 是范围查询）。

（3）最左前缀字段使用函数 / 表达式 / 隐式转换​

原因：最左前缀字段失效会导致整个联合索引失效。

  • 失效示例（索引 (a, b, c)）：
    -- a用了函数，整个索引失效
    WHERE ABS(a) = 1 AND b = 2;
    
    -- a是VARCHAR，隐式转换，整个索引失效
    WHERE a = 123 AND b = 2;

以索引 (a, b, c) 为例​

1、联合索引 ABC，查询条件是 a = xx and c < xx，索引会怎么走？
2、联合索引 ABC，查询条件是 a = 2 and b > 1 and c != 2，索引会怎么走？
3、联合索引 ABC，查询条件是 b = null、a = xx and b = xx or c = xx，索引会怎么走？
4、联合索引 ABC，查询条件是 a = 1 and b > 1 and c = 1，索引会怎么走？
5、联合索引 ABC，查询条件是 b > xxx and a = x，索引会怎么走？
6、联合索引 ABC，查询条件是 a = 2 and c = 1，索引会怎么走？
7、联合索引 ABC，查询条件是 a = 1 order by b，索引会怎么走？
8、联合索引 ABC，查询条件是 b is null，索引会怎么走？
9、联合索引 ABC，查询条件是 a = 1 or c = 2，索引会怎么走？
10、联合索引 ABC，查询条件是 a = 1 and b like '%test%'，索引会怎么走？
11、联合索引 AB，查询条件是 a >= 1 and b = 2，索引会怎么走？
12、联合索引 AB，查询条件是 a between 2 and 8 and b = 2，索引会怎么走？
13、联合索引 AB，查询条件是 a like 'j%' and b = 22，索引会怎么走？
14、联合索引 AB，查询条件是 a is null and b is not null，索引会怎么走？

规则回顾​

  • 最左前缀原则：索引仅在查询包含「最左连续字段」时生效，且只能用到最左连续的部分。
  • 范围查询阻断：若最左前缀中某字段用了范围查询（>、<、>=、<=、BETWEEN、LIKE 'j%'），则范围查询后的字段无法用到索引。
  • OR 条件失效风险：若 OR 两边有一边无法用到索引，整个索引可能失效（MySQL 通常选择全表扫描）。
  • ORDER BY 利用有序性：若 ORDER BY 字段符合最左前缀，且前面是精确查询，可直接利用索引的有序性，避免filesort。

1. 联合索引 ABC，查询条件 a = xx AND c < xx​

  • 结论：索引仅用到a字段。
  • 原因：
    • a是最左字段，精确查询，能用到索引；
    • c在a之后，但中间隔了b（无b的条件），不满足「最左连续」，故c无法用到索引；
    • 执行流程：先通过索引扫描a=xx的行，再在这些行中过滤c < xx。

2. 联合索引 ABC，查询条件 a = 2 AND b > 1 AND c != 2​

  • 结论：索引用到a和b字段。
  • 原因：
    • a精确查询，用到；
    • b是范围查询，用到；
    • c在b的范围查询之后，被「阻断」，无法用到索引；
    • 执行流程：扫描a=2 AND b>1的行，再过滤c != 2。

3. 联合索引 ABC，查询条件 (a = xx AND b = xx) OR c = xx​

  • 结论：索引失效，全表扫描。
  • 原因：
    • 左边(a=xx AND b=xx)符合最左前缀，能用到索引；
    • 右边c=xx无最左字段a，无法用到索引；
    • OR 条件中只要有一边无法用到索引，MySQL 通常选择全表扫描（避免索引合并的额外开销）。

在WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR 前后的两个条件中的列都是索引时，查询中才使用索引。 因为 OR 的含义就是两个只要满足一个即可，因此 只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

4. 联合索引 ABC，查询条件 a = 1 AND b > 1 AND c = 1​

  • 结论：索引用到a和b字段。
  • 原因：同场景 2，b是范围查询，阻断了后面的c，故仅用到a和b，扫描后过滤c=1。

5. 联合索引 ABC，查询条件 b > xxx AND a = x​

  • 结论：索引用到a和b字段。
  • 原因：MySQL 优化器会自动调整条件顺序为a=x AND b>xxx，符合最左前缀（a精确 +b范围），故用到a和b。

6. 联合索引 ABC，查询条件 a = 2 AND c = 1​

  • 结论：索引仅用到a字段。
  • 原因：a精确查询，用到；c在a之后但隔了b（无b条件），不满足最左连续，故c无法用到，扫描a=2的行后过滤c=1。

7. 联合索引 ABC，查询条件 a = 1 ORDER BY b​

  • 结论：索引用到a，且利用索引的有序性避免filesort。
  • 原因：
    • a精确查询，符合最左前缀；
    • 索引中a=1的行，b是天然有序的（联合索引的排序规则），故可直接按索引顺序返回，无需额外排序。

8. 联合索引 ABC，查询条件 b IS NULL​

  • 结论：索引失效，全表扫描。
  • 原因：无最左字段a的条件，不满足最左前缀原则，索引无法生效。

9. 联合索引 ABC，查询条件 a = 1 OR c = 2​

  • 结论：索引失效，全表扫描。
  • 原因：同场景 3，左边a=1能用到索引，右边c=2无法用到，OR 条件导致索引失效。

10. 联合索引 ABC，查询条件 a = 1 AND b LIKE '%test%'​

  • 结论：索引仅用到a字段。
  • 原因：
    • a精确查询，用到；
    • b的 LIKE 通配符%在开头，无法利用索引的有序性，故b无法用到；
    • 执行流程：扫描a=1的行，再过滤b LIKE '%test%'。

11. 联合索引 AB，查询条件 a >= 1 AND b = 2​

  • 结论：索引仅用到a字段。
  • 原因：a是范围查询，阻断了后面的b，故仅用到a，扫描a>=1的行后过滤b=2。

12. 联合索引 AB，查询条件 a BETWEEN 2 AND 8 AND b = 2​

  • 结论：索引仅用到a字段。
  • 原因：BETWEEN属于范围查询，阻断了后面的b，故仅用到a，扫描a BETWEEN 2 AND 8的行后过滤b=2。

13. 联合索引 AB，查询条件 a LIKE 'j%' AND b = 22​

  • 结论：索引仅用到a字段。
  • 原因：
    • a LIKE 'j%'是前缀匹配，能利用索引的有序性（j开头的值在索引中连续），故a能用到；
    • 但前缀匹配本质是「范围查询」（所有j开头的值），会阻断后面的b，故b无法用到；
    • 执行流程：扫描a LIKE 'j%'的行，再过滤b=22。

14. 联合索引 AB，查询条件 a is null and b is not null​

  • 结论：索引仅用到a字段，b不会用到索引。
  • 原因：
    • is null 支持索引，is not null 不支持索引。

索引举例2​

select * from table where a = 1 and b = 2 and c = 3 如果a是状态，b是时间，c是用户id，怎么建索引？ 

如果是 select * from table where a = 1 or b = 2 or c = 3怎么建索引？

一、第一个查询：AND 条件（a = 1 AND b = 2 AND c = 3）​

核心思路：利用联合索引的「最左前缀」+「高区分度字段优先」 由于三个条件都是等值查询，MySQL 优化器会自动调整条件顺序以匹配索引，因此索引顺序的核心是 「高区分度字段优先」（快速缩小数据范围）

优先推荐 (c, a, b) 或 (c, b, a)​
  • 理由：
    • c（用户 ID）区分度最高，放在索引最左侧，能在第一层就过滤掉 99% 以上的无关数据；
    • 后续的 a 和 b 都是等值查询，可继续利用索引过滤，无需回表（如果是覆盖索引则更好）；
    • 若后续有仅查 c 或 c+a 的查询，也能复用该索引的最左前缀。

二、第二个查询：OR 条件（a = 1 OR b = 2 OR c = 3）​

核心思路：OR 条件无法直接用联合索引，需依赖「单字段索引 + 索引合并」

1. 为什么联合索引无效？​

联合索引的最左前缀原则要求查询条件包含「最左连续字段」，而 OR 是分离的条件（三个条件只需满足一个），无法匹配联合索引的前缀。

2. 索引设计推荐：分别给 a、b、c 建单字段索引​

即建三个独立索引：(a)、(b)、(c)。

  • 原理：MySQL 会使用索引合并（Index Merge） 优化，分别用三个索引查询满足条件的行，再合并结果集（取并集）。
  • 执行流程：
    1. 用 (a) 索引查 a = 1 的行；
    2. 用 (b) 索引查 b = 2 的行；
    3. 用 (c) 索引查 c = 3 的行；
    4. 合并三个结果集，去重后返回。

注意事项​

  • 索引合并仅适用于单字段索引，联合索引无法参与；
  • 若某个条件的区分度极低（如 a = 1 返回 50% 数据），优化器可能放弃索引合并，选择全表扫描；
  • 索引合并的效率通常不如联合索引，因此优先考虑用联合索引替代多个单字段索引。

MySQL 优化器何时会自动调整条件顺序以匹配索引？​

场景是否调整顺序原因
所有条件都是AND连接的等值查询✅ 是调整后可匹配联合索引的最左前缀，且语义不变。
含范围查询的AND条件✅ 部分调整等值条件调整到范围之前，范围之后的条件不调整（范围阻断最左前缀）。
条件含函数 / 表达式 / 隐式类型转换❌ 否调整后仍无法使用索引，无意义。
条件用OR连接❌ 否调整对索引使用帮助不大，优先考虑索引合并。
缺少联合索引的最左字段❌ 否调整后仍无法匹配最左前缀，索引失效。

参考：

https://developer.aliyun.com/article/1111767