最左前缀原则

最左前缀原则个人分析

前言

​ 网上看了无数篇有关最左前缀原则的分析了，有结合b+树分析底层数据结构的，也有直接上例子EXPLAIN来分析的。但不管怎么去理解，总是有人会提到一些有争议的问题，比如联合索引(a,b,c)为什么在条件语句为“a=1 and b>2 and c=3”不去走联合索引。。。等等等等。。。

​ 所以呢，本菜鸡就结合实际案例再加上自己对于b+树的一些理解，谈谈我个人的看法吧QAQ~

工具：MySQL 8.0+Navicat 12

基础表结构

借用Navicat建立一个test表结构如下：

`a`是主键，其中有一些冗余字段是我个人用来学习用的，主要关注`a`、`b`、`c`、`name1`这四个字段就好啦

顺带也瞟一眼我那随心所欲的数据~

有了基础的表结构，先直接上手分析一波

实践分析

这里我们主要对以下几个内容进行分析：

• 建立包含主键字段的联合索引(a,b,c)，分析该种情况下的联合索引
• 建立不包含主键字段的联合索引(b,c,name1)，(内心OS：虽然看起来很别扭，但是别扭也是一种加深印象的方式嘛hhh)分析该种情况下的联合索引

联合索引(a,b,c)

字段a：varchar(20)，主键，不为NULL 字段b：varchar(30)，可为NULL 字段c：varchar(40)，可为NULL

#建立索引
ALTER TABLE test ADD KEY idx_key1(a,b,c);

此时，在表的索引结构中可以看到新增了如下一行：

• 首先我们执行SQL语句EXPLAIN SELECT * FROM test WHERE a='1';

  可以理解的是，对于联合索引(a,b,c)，诸如:

  where a = '1'
  where a = '1' and b = '1'
  where a = '1' and b = '1' and c = '1'
  

  这三种条件语句会走联合索引。

  那么问题来了，当我们联合索引(a,b,c)中a字段为主键时，MySQL是会选择采用Primary key的索引方式呢？还是采用我们的联合索引idx_key1的索引方式呢？

  

  分析执行结果可知，在possible_keys中虽然列出了两种索引，但实际采用的key还是我们的Primary key。这是因为，在我们的查询语句中，一旦索引命中了主键，此时仅需要索引一次就能找到，那么MySQL就不会再进行复杂的扫描。而且也可以看到对应的type为const，说明在对主键a进行条件查询时，MySQL已经将其转化成了一个常量，表示此时我们的表中仅有唯一的匹配行，此时，仅需要读取1次就可以查询到，效率是非常高的。

  附：type字段和key_len字段的含义：

  • type：表示访问类型。访问类型有很多，这个建议自己去了解下吧。
  • key_len：索引长度的字节数，常用来分析是否走联合索引。utf8的情况下：char对应×3，var对应可变+2，可以为NULL又需要+1字节的标记。所以这里的key_len=20*3+2+1=63。

• 接下来我们再试试另外两种情况where a = '1' and b = '1'、where a = '1' and b = '1' and c = '1'

  执行where a = '1' and b = '1'之后：

  

  执行where a = '1' and b = '1' and c = '1'之后：

  

  从图中可见：这两种情况完美符合我们之前对于为什么走Primary key的分析。

联合索引(b,c,name1)

字段b：varchar(30)，可为NULL 字段c：varchar(40)，可为NULL 字段name1：varchar(20)，可为NULL

为了避免之前建立的索引的影响，我们将其删除之后重新建立一个联合索引。

#重新建立索引
ALTER TABLE test ADD KEY idx_key1(b,c,name1);

此时，去表中的索引结构中验证一下：

• 为了对应上面的联合索引，我们还是选择先执行SQL语句EXPLAIN SELECT * FROM test WHERE b='1';

  

• 再执行EXPLAIN SELECT * FROM test WHERE b='1' and c='1';

  

• 再执行EXPLAIN SELECT * FROM test WHERE b='1' and c='1' and name1='a';

  

  实验结果都完美符合我们对于联合索引的理解，当然为了加深一下，我们试一下如下几个条件语句的情况：

  where b='1' and name1='a'
  

  

  ​ 这时候发现只会用到b='1'的索引，却没有使用name1='a'的索引。这是因为，在B+树的叶子节点中，根据b='1'索引是可以确定字段c的排序顺序的，但是当我们需要使用c进行索引时，发现缺失了等值条件c = '${value}'那么MySQL在c的排序中就会进行部分全表扫描，所以此时name1='a'也就自然而然无法走索引了。

  where c='1' and name1='a'
  

  

  ​ 这里为什么走全表扫描其实也是上述的原理，只不过缺失的等值条件b = '${value}'直接是联合索引的最左字段罢了。条件where name1 = 'a'的情况也是同理哦~

  #通配符的位置
  where b like '%'
  

  ​ 只要通配符在字符的最前面，都会使索引失效

  where b like '1%'
  

  

  ​ 只要通配符在字符的最前面，都会使索引失效

  where b like '1%'
  

  

  ​ 通配符在后面，索引还是可以生效的~

总结

​ 其实理解了B+树原理之后，对于最左前缀原则的理解是有非常大的帮助的，每次分析效率或者是否走索引的时候，都可以在脑海里建立一颗简易的B+树，模拟一下过程就可以啦~