GoGoCoder底层原理分析--聚集索引和辅助索引查询过程
经典问题
Q:说一下聚集索引和辅助索引
A:聚集索引和辅助索引都是InnoDB存储引擎索引组织表的实现形式。
-
聚集索引:
索引键为单一主键
叶子节点存储:记录的所有列信息,包含整张表的行记录数据
相当于按照表的主键构造一棵B+树。
-
辅助索引:
索引键为非单一主键(除了聚集索引外,都是辅助索引)
叶子节点存储:索引键值+主键值,不包含整张表的全部数据。
查询辅助索引,在叶子节点获取索引键值+主键值后,判断是否满足查询所需列信息,不满足则需用刚获取的主键值,再查询聚集索引,获取该记录的全部信息,所以辅助索引又名为二级索引。
追根溯源
一、InnoDB逻辑存储结构
-
表空间:
InnoDB存储引擎的最高层,所有的数据都存放在表空间中。默认所有数据均存放在ibdata1共享表空间。如果启用了参数innodb_file_per_table,则每张表内的数据单独存放到一个表空间。
-
段:
表空间由数据段组成,常见的段有数据段、索引段、回滚段等。InnoDB 对段的管理都是由引擎自身完成的,对DBA来说是透明的。因此,不需对其过多关心。
-
区:
区是由连续页组成的,且区大小一般固定为1MB。InnoDB存储引擎默认页大小为16KB,也即一个区为连续的64个页。由于压缩页的引入,页大小可以设置为2KB、4KB、8KB,但是区大小还是固定1MB,只是区内对应的页大小不同。
-
页:
页是InnoDB磁盘管理的最小单位。而且InnoDB存储引擎表是索引组织的,索引数据结构采用B+ 树,实际存储过程中,无论叶子节点还是非叶子节点都是对应磁盘中的一个页。(注:表如果没有设置主键索引,InnoDB会自动给表建立一个隐式主键,并为其添加聚集索引。)
叶子节点(Level=0)
聚集索引中存储行记录所有列值
辅助索引中存储索引列值+主键值
非叶子节点(Level>=1)
聚集索引中存储主键值+(Level-1)索引所在页偏移地址
辅助索引中存储索引列值+(Level-1)索引所在页偏移地址
补充说明页结构中的Page Directory(页目录):
页中的Page Directory(页目录),存放了记录的相对位置,这些指向记录的指针称为Slot槽,槽倒序排列,同时一个槽中可能包含多条记录,所以这些槽构成稀疏目录。
如上图,要在页内查询索引值为"c"的记录,首先在Page Directory 中进行二分查找定位c所在的粗略位置,然后在槽内进行顺序查找访问到索引值“c”的内容。
注:页内还存在其他File Header、Page Header、Infimum、Supremum Records 等,详细请参考《MySql技术内幕》第二版4.4节。
行:
1. 首先会获取索引ID,然后根据索引ID,遍历表空间中的索引页,先找到该索引Level最高的非叶子节点(页)
2. 在索引页中查询定位到下一级(Level-1)非叶子节点所在页地址,循环1,2步骤,最后到达Level=0的数据页。
3. 在Level=0的数据页中,聚集索引存储行记录的所有列值,选择查询语句相关的列返回结果即可。而辅助索引存储当前索引列值+主键值,如果存储的信息无法覆盖查询语句相关信息列,则需根据新得到的主键值,再通过聚集索引树重新查询。
二、聚集索引与非聚集索引查询过程详细分析
1. 表信息如下:CREATE TABLE t ( id INT NOT NULL, name VARCHAR(7000), age INT, PRIMARY KEY (ID),/*主键索引(聚集索引)*/ KEY idx_name(NAME)/*辅助索引*/ )ENGINE=INNODB;
2. 聚集索引查询过程
从聚簇索引树查询ID = 4 的记录(select * from t where id = 4):
1、InnoDB首先会获取到聚集索引的ID,此处假设为idx;
2、然后去磁盘查询page_index_id = idx且level最高的索引页(此处Level=2,Page Offset:00 03)
3、在该索引页内结合Page Directory中的slots进行二分查找,然后在Slot内顺序查找后,获得ID=4所需查询的下一级索引页(Level = 1)的页偏移Page Offset : 00 05
4、同样到Level=1的索引页,同样通过二分+顺序查找的方法,获得ID=4所需查询的数据页(Level = 0)偏移地址 Page Offset :00 0C
5、在Level = 0 的数据页中,继续二分+顺序查找,定位ID=4的行记录,读取出来ID= 4 name = "d"
总的来说,相当于对聚集索引树进行了三次页级的遍历查找。
3. 辅助索引查询过程
从idx_name辅助索引树查name = “d” 记录(select * from t where name = "d"):
1、InnoDB首先会获取到辅助索引idx_name的ID。
2、然后去磁盘查询page_index_id = idx_name且level最高的索引页(此处Level=2,Page Offset:00 04)
3、在该索引页内,二分+顺序查找,获得name = “d” 所需查询的下一级索引页(Level = 1)的页偏移Page Offset : 00 07
4、同样在Level=1的索引页内,二分+顺序查找,获得ID=4所需查询的数据页(Level = 0)偏移地址 Page Offset :00 0A
5、在Level = 0 的数据页中,二分+顺序查找,定位name = “d” 的行记录,获取主键值ID= 4,此时发现 select * 为获取记录的所有列,当前索引中只有ID 与 name 信息,不满足,因此,开始所谓的“回表”查的阶段,通过ID = 4 去聚簇索引位置去查询,最终拿到name = “d”,ID=4的所有列值。
总的来说,相当于对首先对索引树进行了三次页级查找,再对聚集索引树进行了三次页级查找,共计6次查找。
总结:
带索引的查询可以通过查询索引树,快速定位到记录所在磁盘位置,进而减少查询到结果记录的时间。
带索引查询分为:聚集索引查询与辅助索引查询
聚集索引:直接从聚集索引的Level最高索引节点开始查询,最终即可在Level=0的数据页直接获取到所有的行记录结果。
非聚集索引:根据非聚集索引的索引ID,去查询辅助索引的Level最高层根节点,然后在非聚集索引的Level = 0数据页,最多只能获取到索引列+主键列信息,如果能够覆盖查询语句所需的查询结果,则直接返回即可,如果不能满足,则需要拿着主键值,去聚簇索引表再次查询,最终获取到记录所有列信息,从而返回结果集。
三、思考延伸:
针对上述InnoDB逻辑存储结构,以及聚簇索引与非聚簇索引的查找规则,在建立、选择索引方面梳理了以下注意点:
1. 主键避免使用很长字段
主键尽量避免使用很长字段。因为聚集索引中的非叶子节点主要为主键值,如果主键字段很大,节点(索引页)容纳主键个数会变少,增加树深,增加查询所需磁盘IO次数。 同样,也会使非聚簇索引的叶子节点数变多,间接增加辅助索引的树深,增加辅助索引查询的开销。3、注意索引区分度
1、2均说明应当避免使用长字段,但是也不能盲目的缩小索引字段的长度,还要注意索引的区分度(0,1],越趋近于1,该列做索引越有价值。如果区分度为0,查询退化为顺序查询,索引便成了累赘,而且还额外添加了查询索引表的过程,降低增删改的效率。 感兴趣可以了解InnoDB中的cardinality,代表区分度,但是该值并不是代表实时的区分度,具体规则可自行百度。4、尽量减少回表次数(查完辅助索引,再查聚集索引) 通过借助联合索引,增加索引列数,实现索引覆盖(索引列能够满足业务常用查询需求),进而尽量避免回表查询。5、不要建立太多的索引
增删改过程中也会更新索引表,因此,不必要的索引将会降低这些操作的执行时间,降低增删改的效率。小问题:
如果索引叶子节点,内容为ID: 1,2,3,4,5,6,7 进行分裂,则会分裂为 123 和 4567 两个节点。如果主键是自增主键,则继续插入数据,ID为123所在的索引页,另外一半的内存一直是空闲的,后续 4567 所在索引页也会继续分裂,进而造成更大的内存浪费,请问InnoDB是如何处理的?
注:该问题会在回复中进行解答。