猜想
MySQL哪种索引快?聚簇索引 or 非聚簇索引?
试验用表
# 这是一个普通的表小t
CREATE TABLE `t` (
`id` int(10) NOT NULL COMMENT '大家好,我是主键',
`a` int(11) DEFAULT NULL COMMENT '大家好,我是字段a,我是一个普通索引',
`b` int(11) DEFAULT NULL COMMENT '我是一个字段,我没有索引',
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大家好,我是表`t`'
起因
在某个风和日丽的下午,大家伙正在沟通业务的时候,引发了一场“辩论”:主键查询会比普通索引查询快吗?
正方:以primary key为条件查询快
反方:效率一样
浅谈索引
MySQL的索引主要两类,聚簇索引(也叫主键索引、聚集索引)和非聚簇索引(也叫非聚集索引、二级索引、普通索引)。目前MySQL innoDB引擎的索引,主要使用B+树结构,B+树的深度低,叶子节点多,可以有效减少磁盘IO,提升性能。
MySQL的索引结构
MySQL中每张表的数据,也以B+树的结构保存。同为B+树结构,它与索引的区别是:叶子节点保存的是主键 + 数据,而普通索引保存的是索引值 + 主键,如下图所示。
主键索引数据结构(下图)
image.png
普通索引(下图)
image.png
查询与回表
那么MySQL查询是如何工作的呢?我想聪明的你已经知道了答案。
如果我们通过主键查询(比如下面SQL语句),那么innoDB引擎会在保存聚簇索引的这颗B+树中,寻找id = 4的节点,然后通过指针直接获取行数据。
# 根据聚集索引查询数据,一枪头
select * from t where id = 4;
如果我们通过普通查询(比如下面SQL语句),那么innoDB引擎会在保存普通索引的这颗B+树中,寻找a = 'd'的节点,然后在叶子节点中获取到主键值,再通过主键去查询真实数据,这个过程就叫做回表,显然它比通过聚簇索引去查询,多一步。
# 根据普通索引查询数据,回表一次
select * from t where a = 'd';
这里有个小知识点,如果我们只需要查询a字段的值,那么普通索引的叶子节点已经包含了结果,就不需要回表了。学名叫做——索引覆盖。
实操
鉴于前排的理论知识&#x