MySQL聚集索引和二级索引

2020/10/21 posted in  MySQL

数据库中B+树索引分为聚集索引(clustered index)和辅助索引(secondary index,也被称作二级索引、非聚集索引),其内部都是B+树,即高度平衡的,叶子节点存放所有数据,不同的是叶子节点存放的是否是一整行的数据。


聚集索引:InnoDB存储引擎中,按照每张表的主键构建的一棵B+树,叶子节点存放的即为行记录数据。
InnoDB存储引擎中是必须要有一个聚集索引的,如果没有显示指定哪一字段是主键,则选择表中第一个不允许为NULL的唯一索引当作是聚集索引,如果还是没有,就采用InnoDB为每行数据内置的6字节的rowid作为聚集索引。
一般聚集索引是自增主键。


辅助索引:叶子节点并不包含行记录的全部数据,叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark),该书签用来告诉InnoDB存储引擎哪里可以找多与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表,因此 辅助索引的书签就是相应行数据的聚集索引键。

当通过辅助索引来寻找数据时,先查到主键,然后通过主键索引来找到一个完整的行记录。


回表问题:
当通过二级索引去查询数据,先查询辅助索引树,得到主键值,再通过聚集索引树查询,得到最终结果。

回到主键索引树搜索的过程,称为回表。

如果在一棵高度为3的辅助索引树中查找数据,那需要对这棵辅助索引遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终得到一个完整的行数据所在的页,一共需要6次逻辑IO访问得到最终的数据页。

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `age` int(11) NOT NULL,
  `sex` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB  

例如,这么一个表结构,两个查询语句

select id,name from user where name = 'zhangsan1';
select id,name,age from user where name = 'zhangsan1';

用EXPLAIN看一下执行计划,


可以看到,第一个查询语句用到了索引,而第二个查询语句只是多了1个字段,

Extra参数中Using index,表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的。那么回表问题的解决方案就是 将所用到的字段建立联合索引,覆盖索引。


B+ 树 与 B树的区别?
区别在 B 树的节点,无论是不是叶子几点,都存有 key 和 data,而 B+ 树的中间节点只有 key,data 存在叶子节点上。这样 B+ 树的中间节点就能存更多的 key,使得树更矮,减少 IO 次数。

B+ 树的叶子几点构成一个有序链表,方便区间查找和搜索。而 B 树需要一次次的递归遍历查找。