mysql 的最左前缀原则

在MYSQL InnoDB 中 联合索引的最左原则是一块比较常用的概念，在百度搜索了很多帖子或多或少都和实际情况优先出入，大多是基于《高性能MYSQL》上进行的总结文章，但是时隔多年在实际使用环境下难免有一些版本升级带来的优化问题。带着这些问题进行了下面一波小实验。

联合索引

###优点

• 覆盖索引，这一点是最重要的，重所周知非主键索引会先查到主键索引的值再从主键索引上拿到想要的值，这样多一次查询索引下推。但是覆盖索引可以直接在非主键索引上拿到相应的值，减少一次查询。

• 在一张大表中如果有 (a,b,c)联合索引就等于同时加上了 (a) (ab) (abc) 三个索引减少了存储上的一部分的开销和操作开销

• 梯度漏斗，比如 select *from t where a = 1 and b = 2 and c = 3; 就等于在满足 a = 1 的一部分数据中过滤掉 b = 2 的 再从 a = 1 and b = 2 过滤掉 c = 3 的，越多查询越高效。

最左前缀原则

ps:上图转载极客时间

从上图可以看到联合索引其实是利用的B+树这种数据结构，比如单独查询 “王五” 和 “王%” 其实都可以用到最左边的值。但是直接查询年龄就会用不到这个联合索引。类似于给(a,b,c)加上联合索引就等于同时加上了 (a) (ab) (abc)但是不能丢下a单独来使用。

where条件的顺序

在很多文章，或者在面试中很多候选人对这一块都是没有一个统一答案的。有的认为顺序会有影响因为这是最左原则有的认为顺序没有影响，mysql的查询优化器会来进行排序这件事。下面做个小的实验来证明下上面2个观点

实验

表结构

CREATE TABLE `ttt` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

伪造数据

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into ttt values(i, i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

测试 (以下结果在mysql5.6.43 进行)

mysql> explain select *from ttt where a = 1 ;

mysql> explain select *from ttt where a = 1 and b = 1;

mysql> explain select *from ttt where a = 1 and b = 1 and c =1;

mysql> explain select *from ttt where  b = 1 and c =1;

mysql> explain select *from ttt where  b = 1 and a =1;

explain select *from ttt where  b = 1 and a =1 and c = 1

为排除查询缓存的可能SQL_NO_CACHE 在核对次结果

explain select SQL_NO_CACHE *from ttt where  b = 1 and a =1 and c = 1;

尝试去除中间的索引

explain select SQL_NO_CACHE c from ttt where  a = 1 and c =1;

上述我们主要关注2点第一个是 type 和 一个是ref,可以得到下面的结果

where	是否用到联合索引
(a)	OK
(a,b)	OK
(a,b,c)	OK
(b,c)	NO
(b,a)	OK
(b,a,c)	OK
(a,c)	只命中a

总结

联合查询遵循最左前缀原则，最左前缀原则讲究加索引时候的排序，对于搜索中的where在mysql的后续版本中会有查询优化器进行优化在使用时不必强调顺序（但是在一些特定场景下查询优化器可能存在错误判别，并且笔者未考证最左前缀与版本之间的关系所以建议遵守加索引时的顺序）