MySQL-InnoDB聚簇索引与非聚簇索引

MySQL InnoDB存储引擎时，索引类型可分为聚簇索引和非聚簇索引，有时候也通俗的称为主键索引和普通索引。

接下来讨论一下几个问题：

1、什么是聚簇索引和非聚簇索引

2、什么是回表查询

3、什么是索引覆盖以及如何实现实现

-------------------------------------------------

1、关于聚簇索引和非聚簇索引的特点

聚簇索引：

a、一个表有且仅有一个聚簇索引

b、聚簇索引B+树的叶子节点存储的行数据

c、聚簇索引建立的列规则，按照优先级为：主键->第一个非空唯一列->InnoDB创建一个隐式row-id作为聚簇索引

d、聚簇索引的存储顺序与物理数据顺序一致

非聚簇索引：

a、也叫普通索引或者辅助索引，一个表可以有0-n个非聚簇索引

b、索引B+树叶子节点存储的是主键值

举个栗子对比说明二者的联系与区别：

假设数据表如下（其中id为主键，name为普通索引）：

id(主键)	name	age
1	zhangsan	20
2	lisi	21
3	wangwu	22
4	zhaoliu	23

 

 

 

 

 

 

则对应的索引结构如下(这里只做实例用，实际的B+树存储，一个节点会存储0-n个值)：

 

此时，如果我们执行where id=2,则索引如绿色箭头所示，直接通过聚簇索引的B+树，检索到指定位置，并取到行数据记录

如果执行where name='wangwu'，则需要执行的步骤如红色箭头所示，分两步：

第一步：通过辅助索引，检索name=‘wangwu’,并取到叶子节点的主键值为3

第二步：通过主键id=3,到聚簇索引中检索，然后到叶子节点取到id=3的行数据

通过对比我们可以知道，聚簇索引的检索效率明显高于辅助索引的效率，因为辅助索引需要执行两次B+树索引的检索。

 

2、关于回表查询。

类似上面通过name=‘xxx’这种，需要两次检索的过程，第一次通过辅助索引检索得到主键值，然后通过主键值到聚簇索引中检索出实际行记录的过程，称为回表查询。

 

3、关于索引覆盖以及常用实现。

只用在一个索引树上就能得到SQL所需的全部数据而无需回表，称为索引覆盖，索引覆盖的好处是效率快。常见的索引覆盖是通过建立联合索引。

举个栗子：

查询条件select id,name,age from xxx where id=2,此时直接走的聚簇索引,速度是很快的

查询条件select id,name from xxx where name='zhangsan',由于name、id都在辅助索引中存在，无需回表

查询条件select id,name,age from xxx name='zhangsan'，此时由于age不在辅助索引中，必须通过回表才能取到age列，为了实现索引覆盖，可以建立联合索引，将age加入到联合索引中。