专栏首页Java进阶指南我去,为什么最左前缀原则失效了?

我去,为什么最左前缀原则失效了?

问题

最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。

表结构如下( Mysql 版本 5.7.22):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO user(`id`, `name`, `age`, `address`) VALUES (1, 'zs', 12, 'beijing');

表中总共有四个字段。id 为主键,还有一个由 name,age,address 组成的联合索引。存储引擎为 InnoDB,并插入一条测试数据。

根据最左前缀原则,以下 sql ,肯定会使索引失效的。(若不懂最左前缀原则,稍后会讲~)

EXPLAIN select * from user where address='beijing';

然而结果,却是让人大失所望。如下,通过查看执行计划,发现它走索引了。

这就让我非常疑惑了,难不成最左前缀原则是错的?又或者,是 Mysql 随着版本升级,已经智能到不需要 care 最左前缀原则了吗?

目录

带着这个疑问,我们一探究竟。在这之前需要了解一些前置知识。本篇文章目录如下:

  • 什么是聚集索引和非聚集索引?
  • 什么是回表查询?
  • 什么是索引覆盖?
  • 最左前缀原则
  • 问题解惑

正文

由于,现在基本上都是用的 InnoDB引擎,所以下面都以 InnoDB为例,MyISAM 顺带提一下。

什么是聚集索引和非聚集索引?

我们知道 Mysql 底层是用 B+ 树来存储索引的,且数据都存在叶子节点。对于 InnoDB 来说,它的主键索引和行记录是存储在一起的,因此叫做聚集索引(clustered index)。

PS:MyISAM 的行记录是单独存储的,不和索引在一起,因此 MyISAM也就没有聚集索引。

除了聚集索引,其它索引都叫做非聚集索引(secondary index)。包括普通索引,唯一索引等。

另外需要注意,在 InnoDB 中有且只有一个聚集索引。它有三种情况:

  1. 若表存在主键,则主键索引就是聚集索引。
  2. 若不存在主键,则会把第一个非空的唯一索引作为聚集索引。
  3. 否则,就会隐式的定义一个 rowid 作为聚集索引。

为了方便理解,下边以 InnoDB 的主键索引和普通索引为例,看下它们的存储结构。

创建一张表,结构如下,并添加几条记录(张三,李四,王五,孙七):

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

insert into student(id,name,age) values(1,'zs',12);
insert into student(id,name,age) values(5,'ls',14);
insert into student(id,name,age) values(9,'ww',12);
insert into student(id,name,age) values(11,'sq',13);

在 InnoDB 中,主键索引的叶子节点存储的是主键和行记录,而普通索引的叶子节点存储的是主键(对于 MyISAM来说主键索引的叶子节点存储的是主键和对应行记录的指针,普通索引的叶子节点存储的是当前索引列和对应行记录的指针)。

因此,id 为聚集索引,name 为非聚集索引。它们对应的 B+ 树结构如下图所示,

clustered index, secondary index

什么是回表查询?

从上边的索引存储结构,我们可以看到,在主键索引树上,通过主键就可以一次性查出来我们所需要的数据,速度非常的快。

因为主键和行记录就存储在一起,定位到了主键,也就定位到了所要找的记录,当前行的所有字段都在这(这也是为什么我们说,在创建表的时候,最好是创建一个主键,查询时也尽量用主键来查询)。

对于普通索引,如例子中的 name,则需要根据 name 的索引树(非聚集索引)找到叶子节点对应的主键,然后再通过主键去主键索引树查询一遍,才可以得到要找的记录。这就叫 回表查询

以如下 sql 为例。

select * from student where name='zs';

它需要查询两遍索引树。

  • 通过非聚集索引定位到主键 id=1。
  • 通过聚集索引定位到主键id为1,对应的行记录。

它的查询过程图如下,

回表查询

什么是索引覆盖?

对于上边的回表查询来说,无疑会降低查询效率。那么,有的童鞋就会问了,有没有什么办法,让它不回表呢?

答案当然是有了,就是索引覆盖

何为索引覆盖,就是在用这个索引查询时,使它的索引树,查询到的叶子节点上的数据可以覆盖到你查询的所有字段,这样就可以避免回表。

还是以上边的表为例,现在 zs 对应的索引树上边,只有它本身和主键的数据,并不能覆盖到 age 字段。那么,我们就可以创建联合索引,如 KEY(name,age)。并且,查询的时候,显式的写出联合索引对应的字段(name和age)。

创建联合索引如下,

KEY `idx_stu` (`name`,`age`)

查询语句修改如下,

-- 覆盖联合索引中的字段
select id,name,age from student where name='zs' and age=12; 

这样,当查询索引树的时候,就不用回表,可以一次性查出所有的字段。对应的索引树结构如下:

联合索引

PS:图中,联合索引中的字段(name,age)都应该出现在索引树上的,这里为了画图方便,且因数据量太小,没有画出来。只表现出了:叶子节点存储了所有的联合索引字段。

最左前缀原则

最左前缀原则,顾名思义,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。如上表中,name和age的联合索引,相当于创建了 name 单列索引和 (name,age)联合索引。在查询时,where 条件中若有 name 字段,则会走这个联合索引。

对于多个字段的联合索引,也同理。如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和(a,b,c)联合索引。

为了验证最左前缀原则,我们需要对原来的表结构进行改造。再添加两个字段(address,sex),然后创建三列的联合索引(name,age,address)。

drop table student;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu` (`name`,`age`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into student(id,name,age,address,sex) values(1,'zs',12,'beijing',1);
insert into student(id,name,age,address,sex) values(5,'ls',14,'tianjin',0);
insert into student(id,name,age,address,sex) values(9,'ww',12,'shanghai',1);
insert into student(id,name,age,address,sex) values(11,'sq',13,'hebei',1);

查看表数据如下,

表数据

分别用三种方式,使之符合最左前缀原则。

explain select * from student where name='zs';
explain select * from student where name='zs' and age=12;
explain select * from student where name='zs' and age=12 and address='beijing';

然后查看它们的执行计划如下,

可以看到,最终都走了索引。现在,修改 sql 如下,如何?

explain select * from student where address='beijing';

如我们所料,这不符合最左前缀原则,因此索引失效,走了全表扫描。

PS:拓展思考,若 sql 改为如下,会导致全表扫描吗?(自己动手尝试哦)

explain select * from student where name='zs' and address='beijing';

问题解惑

到现在为止,我们发现最左前缀原则一切正常。然后回到最开始抛出的问题,为什么这个原则就不生效了呢?(创建的联合索引,还有 sql 语句都是一样的啊!)

别着急,还记得前面我们说的索引覆盖吗?这次,我们利用索引覆盖原理,只查询特定的字段(只有主键和联合索引字段)。

explain select id,name,age,address from student where address='beijing';

再查看执行计划,

问题来了,此时违反了最左前缀原则,但是符合覆盖索引,为什么就走索引了呢?

我们对比一下,若用最左列,和不用最左列,它们的执行计划有何不同。

会发现,若不符合最左前缀原则,则 type为 index,若符合,则 type 为 ref。

index 代表的是会对整个索引树进行扫描,如例子中的,最右列 address,就会导致扫描整个索引树。

ref 代表 mysql 会根据特定的算法查找索引,这样的效率比 index 全扫描要高一些。但是,它对索引结构有一定的要求,索引字段必须是有序的。而联合索引就符合这样的要求!

联合索引内部就是有序的,我们可以把它理解为类似于 order by name,age,address 这样的排序规则。会先根据 name 排序,若name 相同,再根据 age 排序,依次类推。

所以,这也解释了,为什么我们要遵守最左前缀原则。当最左列有序时,才可以保证右边的索引列有序。

退而求其次,若不符合最左前缀原则,但是符合覆盖索引,就可以扫描整个索引树,从而找到覆盖索引对应的列(避免了回表)。

若不符合最左前缀原则,且也不符合覆盖索引(形同 select *),则需要扫描整个索引树。完成之后,还需要再回表,查询对应的行记录。

此时,查询优化器,就会认为,这样的两次查询索引树,还不如全表扫描来的快(因为联合索引此时不符合最左前缀原则,要比普通单列索引查询慢的多)。因此,此时就会走全表扫描。

有童鞋就要问了,你在这废话了一大堆,还是没有解答最初的疑惑啊 !!!

不然,其实上边的分析就已经解答了。我们仔细观察最开始的 user 表,和此时的 student 表有什么不同。

user 表中,和 student 表相比,少了 sex 字段。但是,它们所建立的联合索引却是一样的 KEY(name,age,address)。

所以,在 user 中,我们最初的 sql 语句就等同于 ,

-- 最初的sql
EXPLAIN select * from user where address='beijing';
-- 等同于
EXPLAIN select id,name,age,address from user where address='beijing';

这个结构就是我们上边讨论的情况:不符合最左前缀原则,但是符合索引覆盖。这种情况,是会走索引的。

结论

那么,结论也就出来了。并不是最左前缀原则失效了,也不是 Mysql 变的更智能了,而是此时创建的表结构,以及查询的 sql 语句恰好符合了索引覆盖而已。真的是虚惊一场 !!

本文分享自微信公众号 - 烟雨星空(mistyskys),作者:烟雨星空

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • linux 连个文件都删除不了,什么鬼!

    最近不是redis 6.0 出了吗,官网介绍最新稳定版本是 6.0.3 。于是,我就准备在自己的破小服务器上安装一下。于是,出现了后续的糟心事 (linux 下...

    烟雨星空
  • 微信又出图文标签功能,带来了哪些强大功能?

    我发现最近微信的动作实在是太频繁了,各种灰度测试。包括前阵子,我也被灰度到个人认证了。也让我无耻的假装一把自己是大 V ,哈哈。

    烟雨星空
  • 面试官看完我手写的单例直接惊呆了!

    单例模式应该算是 23 种设计模式中,最常见最容易考察的知识点了。经常会有面试官让手写单例模式,别到时候傻乎乎的说我不会。

    烟雨星空
  • MySQL优化十大技巧

    转自:https://m.2cto.com/database/201701/557910.html

    阮键
  • 学习数据库必会知识点-索引-以及索引的巧妙用法

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    Java编程指南
  • 数据库索引

    即搜索引导,索引是一个特殊的数据结构,其存储的关键信息与详细信息的位置对应关系,加速索引

    小小咸鱼YwY
  • MySQL索引优化系列(1)—索引介绍

    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高校获取数据的数据结构。

    公众号 IT老哥
  • 玩转Mysql系列 - 第23篇:mysql索引管理详解

    Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

    路人甲Java
  • MySQL 索引

    数据库的索引是一个要点, 无论是面试还是在工作中, 这个知识点都很常会用到, 你可能只是用过索引, 知道加了索引可以提高查询的性能, 但不知道为什么这样, 今天...

    一份执着✘
  • 【Mysql进阶-2】图文并茂说尽Mysql索引

    在关系数据库中,索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据...

    云深i不知处

扫码关注云+社区

领取腾讯云代金券