我们知道Mysql底层使用的B+树来存储索引的,而且数据都存在叶子节点上。对于innodb来说,它的主键索引和行记录是存储在一起的,因此叫做聚集索引。
ps:MyISAM的行记录是单独存储的,不和索引在一起,因此MYISAM是没有聚集索引的。
除了聚集索引,其他的索引都叫非聚集索引。(普通索引,唯一索引等)
另外需要注意的,在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_bininsert 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来说主键索引的叶子节点存储的是主键和对应行记录的指针,普通索引的叶子节点存储的是当前索引列和对应行记录的指针)。
什么是回表查询?
从上面的索引存储结构,我们可以看到,在主键索引树上,通过主键就可以一次性查出我们所需要的数据,速度非常快。
因为主键和行记录就存储在一起,定位到了主键,也就定位到了所要找的记录,当前行的所有字段都在这(这也是我们为什么说,在创建表的时候,最好是创建一个主键,查询时也尽量用主键来查询)。
对于普通索引,如例子中的name,则需要根据name的索引树(非聚集索引)找到叶子节点对应的主键,然后在通过主键索引树查询一遍,才可以得到要找的记录,这就是回表查询。
什么是覆盖索引?
对于回表查询来说,无疑会降低查询的效率。那么有什么办法让他不回表呢?
那就是索引覆盖
什么索引覆盖,就是在用这个索引查询的时候,使他的索引树查询到的叶子节点的数据可以覆盖你查询的所有字段,这样就可以避免回表。
还是以上边的表为例,现在 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)都应该出现在索引树上的,这里为了画图方便,且因数据量太小,没有画出来。只表现出了:叶子节点存储了所有的联合索引字段。
学习的过程是一件痛苦的事,一但学习有了结果,那就变成了快乐既兴奋的事!
让我们红尘作伴、活的潇潇洒洒,对酒当歌、学习快乐!