mysql的最左匹配原则

最新推荐文章于 2025-06-27 10:00:11 发布

原创最新推荐文章于 2025-06-27 10:00:11 发布 · 3.4k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

本文详细介绍了数据库索引的工作原理，特别是最左匹配原则及其在查询中的应用。通过实例解析了索引失效的各种情况，如缺失首个字段、中间字段、范围查询等，并提供了优化查询的建议，如避免列计算、考虑索引与NULL的关系以及利用覆盖索引提升性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原理

二、不符合最左原则导致索引失效的情况

1.查询条件中没有第一个字段（优先级最高）

2.查询条件中，缺少第二个字段（优先级居中）

主流索引结构为B+树。B+树的节点存储引擎顺序是从左到右存储，在检索匹配的时候也要满足从左到右匹配。

一、原理

比如一个表有a,b,c 三个字段，然后建立联合索引 index(a,b,c) 注意这里索引字段的顺序

select * from table where a = "1"; //会走索引

select * from table where a = "1" and b = "2"; //会走索引

select * from table where a = "1" and b = "2" and c = "2" //会走索引

select * from table where b = "2" and a = "1"; //会走索引



select * from table where c = "3"; //不会走索引

select * from table where b = 2 and  c = "3"; //不会走索引

通过上面的例子，我们可以看出命中索引必须得有a字段（最左匹配，建立索引的时候第一个字段）。

假如有一个LOL的表，数据如下：

然后创建了一个联合索引（sex_price_name），生成的索引文件逻辑上等于下面的内容：

我们可以发现先通过第一个索引字段sex 排序然后在基础上进行price name排序

比如下面的这个sql语句：

select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪';

B+树会先比较sex来确定大致方向，然后根据价格 name 来得到数据

二、不符合最左原则导致索引失效的情况

还是index(a,b,c)的例子

1.查询条件中没有第一个字段（优先级最高）

比如 where b = 2 。因为建立索引树的时候,a是第一个，就好像树干一样。没有最左边的字段，即使后面的字段建立了索引，也无法命中。

2.查询条件中，缺少第二个字段（优先级居中）

比如where a= 1 and c = 2。通过a 字段可以匹配出一部分数据，但是没有b字段，就无法向下进行匹配。

3.索引顺序（查询优化器）

如果索引顺序是a,b 但是查询语句是 where b=2 and a = 1,这时候索引也能命中。

这是由于mysql查询优化器会自动调整where 的条件顺序。

4.特殊查询

select * from LOL where a = 2 and b > 1000  and c='3';

上面类型的语句 a b会走索引，c 不会走

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。like 要注意一下：如果通配符% 不出现在开头，则可以走索引。“value%” 可以使用索引，但是 like “%value%” 违背了最左匹配原则，不会使用索引，走的是全表扫描。

三、特殊注意

1.不要在列上进行计算

如果查询条件中含有函数或者表达式，这会导致索引失效

比如：

select * from table where YEAR(birthday) <1990  //索引失效

可以改成：

select * from table where birthday < '1990-01-01'

2.索引和null 的特殊化

在数据库设计的时候不建议让索引字段默认值为 null ，可以设置默认值为0和''空字符串等。

3.覆盖索引

如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量，这也是不建议使用Select * 的原因。