MySQL最左匹配原则

雨点打透心脏的1/2处 2023-10-07 21:13 7阅读 0赞

#### 阅读目录 ####

*  阐述
 *  一、最左匹配原则的原理
 *  二、违背最左原则导致索引失效的情况
 *  三、查询优化器偷偷干了哪些事儿
 *  四、知识点
 *  五、MySQL 为什么是最左匹配原则而不是最右匹配原则

## 阐述 ##

通常我们在建立联合索引的时候，相信建立过索引的同学们会发现，无论是Oracle 还是 MySQL 都会让我们选择索引的顺序，比如我们想在 `a,b,c` 三个字段上建立一个联合索引，我们可以选择自己想要的优先级，`(a、b、c)`，或是 `(b、a、c)` 或者是 `(c、a、b)` 等顺序。

![在这里插入图片描述][6b2e61e9543945c9a61e45e885eaf377.png]  
为什么数据库会让我们选择字段的顺序呢？  
不都是三个字段的联合索引么？

这里就引出了数据库索引的最重要的原则之一，最左匹配原则。

在我们开发中经常会遇到这种问题，明明这个字段建了联合索引，但是SQL查询该字段时却不会使用这个索引。难道这索引是假的？

比如索引 `abc_index:(a,b,c)` 是 `a,b,c` 三个字段的联合索引，下列 sql 执行时都无法命中索引 `abc_index`；

select * from table where c = '1';
    
    select * from table where b ='1' and c ='2';

以下三种情况却会走索引:

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='3';

从上面两个例子大家有木有看出点眉目呢？

是的，索引 `abc_index:(a,b,c)`，只会在 where 条件中带有 `(a)、(a,b)、(a,b,c)` 的三种类型的查询中使用。

其实这里说的有一点歧义，其实当 where 条件只有 `(a,c)` 时也会走，但是只走a字段索引，不会走 c 字段。

那么这都是为什么呢？  
我们一起来看看其原理吧。

## 一、最左匹配原则的原理 ##

> MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：  
> 如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；  
> 如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

假设数据表 `LOL (id,sex,price,name)` 的物理位置（表中的无序数据）如下：

（注：下面数据是测试少量数据选用的，只为了方便大家看清楚。实际操作中，应按照使用频率、数据区分度来综合设定索引顺序喔~）

主键id  sex(a)   price(b)      name(c)    
    (1)     1         1350         AAA安妮
    (2)     2         6300         MMM盲僧
    (3)     1         3150         NNN奈德丽
    (4)     2         6300         CCC锤石
    (5)     1         6300         LLL龙女
    (6)     2         3150         EEE伊泽瑞尔
    (7)     2         6300         III艾克
    (8)     1         6300         BBB暴走萝莉
    (9)     1         4800         FFF发条魔灵
    (10)    2         3150         KKK卡牌大师
    (11)    1         450          HHH寒冰射手
    (12)    2         450          GGG盖伦
    (13)    2         3150         OOO小提莫
    (14)    2         3150         DDD刀锋之影
    (15)    2         6300         JJJ疾风剑豪
    (16)    2         450          JJJ剑圣

当你在 LOL 表创建一个联合索引 `abc_index:(sex,price,name)` 时，生成的 `索引文件逻辑上等同于下表内容（分级排序）`：

sex(a)   price(b)       name(c)         主键id
    1        450            HHH寒冰射手      (11)
    1        1350           AAA安妮          (1)
    1        3150           NNN奈德丽        (3)
    1        4800           FFF发条魔灵       (9)
    1        6300           BBB暴走萝莉       (8)
    1        6300           LLL龙女          (5)
    2        450            GGG盖伦          (12)
    2        450            JJJ剑圣          (16)
    2        3150           DDD刀锋之影       (14)
    2        3150           EEE伊泽瑞尔       (6)
    2        3150           KKK卡牌大师       (10)
    2        3150           OOO小提莫         (13)
    2        6300           CCC锤石          (4)
    2        6300           III艾克          (7)
    2        6300           JJJ疾风剑豪       (15)
    2        6300           MMM盲僧          (2)

小伙伴儿们有没有发现B+树联合索引的规律？

感觉还有点模糊的话，那咱们再来看一张索引存储数据的结构图，或许更明了一些。

![在这里插入图片描述][f731169265384038973c6658a76f949c.png]  
B+树中的联合索引，每级索引都是排好序的。

联合索引 bcd\_index:(b,c,d) , 在索引树中的样子如图 ， 在比较的过程中 ，先判断 b 再判断 c 然后是 d 。

由上图可以看出，B+ 树的数据项是复合的数据结构，同样，对于我们这张表的联合索引 (sex,price,name)来说 ，B+ 树也是按照从左到右的顺序来建立搜索树的，当SQL如下时：

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

B+ 树会优先比较 sex 来确定下一步的指针所搜方向，如果 sex 相同再依次比较 price 和 name，最后得到检索的数据；

## 二、违背最左原则导致索引失效的情况 ##

（下面以联合索引 abc\_index:(a,b,c) 来进行讲解，便于理解）

1、查询条件中，缺失优先级最高的索引 “a”

当 where b = 6300 and c = ‘JJJ疾风剑豪’ 这种没有以 a 为条件来检索时；B+树就不知道第一步该查哪个节点，从而需要去全表扫描了（即不走索引）。

因为建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询b 和 c，这点我们通过上面的存储结构图可以看明白。

2、查询条件中，缺失优先级居中的索引 “b”

当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时；B+ 树可以用 a 来指定第一步搜索方向，但由于下一个字段 b 的缺失，所以只能把 a = 1 的数据主键ID都找到，通过查到的主键ID回表查询相关行，再去匹配 c = ‘JJJ疾风剑豪’ 的数据了，当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了。

这就是MySQL非常重要的原则，即索引的最左匹配原则。

## 三、查询优化器偷偷干了哪些事儿 ##

当对索引中所有列通过 “=” 或 “IN” 进行精确匹配时，索引都可以被用到。

1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘aaa’; 为什么还能利用到索引？

理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了，SQL书写的好习惯要保持，这也能让其他同事更好地理解你的SQL。

2、还有一个特殊情况说明下，下面这种类型的SQL， a 与 b 会走索引，c不会走。

select * from LOL 
    where a = 2 and b > 1000  
    and c='JJJ疾风剑豪';

对于上面这种类型的sql语句；  
mysql会一直向右匹配直到遇到范围查询 `(>、<、between、like)` 就停止匹配（包括like '陈%'这种）。

在a、b走完索引后，c已经是无序了，所以c就没法走索引，优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引，影响了执行效率。

其实，这种场景可以通过修改索引顺序为 abc\_index:(a,c,b)，就可以使三个索引字段都用到索引，建议小伙伴们不要有问题就想着新增索引哦，浪费资源还增加服务器压力。

综上，如果通过调整顺序，就可以解决问题或少维护一个索引，那么这个顺序往往就是我们DBA人员需要优先考虑采用的。

## 四、知识点 ##

1、如何通过有序索引排序，避免冗余执行 order by  
order by 用在 select 语句中，具备排序功能。如：

SELECT sex, price, name FROM LOL ORDER BY sex;

是将表 LOL 中的数据按 “sex” 一列排序。

而只有当 order by 与 where 语句同时出现，order by 的排序功能无效。

换句话说，order by 中的字段在执行计划中利用了索引时，不用排序操作。如下SQL时，不会按 sex 一列排序，因为 sex 本身已经是有序的了。

SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;

所以，只有 order by 字段出现在 where 条件中时,才会利用该字段的索引而避免排序。

对于上面的语句，数据库的处理顺序是：

第一步：根据 where 条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看 order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。

第三步：返回排序后的数据。

2、like 语句的索引问题

如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀，在 like “value%” 可以使用索引，但是 like “%value%” 违背了最左匹配原则，不会使用索引，走的是全表扫描。

3、不要在列上进行运算

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描

例如 ：

select * from user where YEAR(birthday) < 1990

可以改造成：

select * from users where birthday <’1990-01-01′

4、索引不会包含有 NULL 值的列

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL

5、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

6、覆盖索引的好处

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

## 五、MySQL 为什么是最左匹配原则而不是最右匹配原则 ##

最硬的回答：

首先最右匹配原则索引就失效了。

联合索引的索引树是以左边第一个字段作为非叶子节点，按照顺序进行放置。

对于相同的字段如 student\_id,它会按照 score 来排序，如果还有第三个字段，那么 score 相同数据，就会再按照第三个字段排序，以此类推。

--------------------

MySQL组合索引“最左前缀”原则，指的是MySQL只能高效地使用索引的最左前缀列。

所以，在多列索引中索引列的顺序至关重要。

如果不是按照索引的最左列开始查找，则无法使用索引。

例如，你在用户表上创建了一个多列索引`t_index(sex,age)`，如果你这个时候没有查找某个特定的性别，这个索引时无法使用的。

为什么不是最右或者是其他方向的匹配原则呢？

这还是要从我们的索引树说起。

我们先来明确一个概念，那就是联合索引。

联合索引就是指，由两个或以上的字段共同构成一个索引。

之前我们举的例子都是单个字段的索引，那么多个字段的联合索引是什么样的，也就是联合索引的索引树是什么样的呢？

是一棵树还是多棵树呢？

答案是一棵B+Tree。

联合索引的索引树是以左边第一个字段作为非叶子节点，按照顺序进行放置，与单字段的非叶子几点是一致的，只是在叶子节点上有区别：

CREATE TABLE `sutdent_score` (
      `id` bigint(20) NOT NULL,
      `student_id` int(11) DEFAULT '0' COMMENT '学生id',
      `score` int(11) DEFAULT '0' COMMENT '成绩',
      PRIMARY KEY (`id`),
      KEY `index_student_and_score` (`student_id`,`score`)
    )

对于相同的 student\_id,它会按照 score 来排序，如果还有第三个字段，那么 score 相同数据，就会再按照第三个字段排序，以此类推。

所以不管是在非叶子节点还是叶子节点（页）上，都是按照从左边到右边的大小顺序来排列的。

[6b2e61e9543945c9a61e45e885eaf377.png]: https://img-blog.csdnimg.cn/6b2e61e9543945c9a61e45e885eaf377.png
[f731169265384038973c6658a76f949c.png]: https://img-blog.csdnimg.cn/f731169265384038973c6658a76f949c.png