我去，为什么最左前缀原则失效了？

最近，在 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 中有且只有一个聚集索引。它有三种情况：

若表存在主键，则主键索引就是聚集索引。

若不存在主键，则会把第一个非空的唯一索引作为聚集索引。

否则，就会隐式的定义一个 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+ 树结构如下图所示，

什么是回表查询？

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

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

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

以如下 sql 为例。

select * from student where;

它需要查询两遍索引树。

通过非聚集索引定位到主键 id=1。

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

它的查询过程图如下，

什么是索引覆盖？

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

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

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