一、InnoDB存储引擎索引概述
InnoDB存储引擎支持以下几种常见的索引:
(1)B+树索引
(2)全文索引
(3)哈希索引
InnoDB支持的哈希索引是自适应的,InnoDB会根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成索引。
注:B+树索引并不能找到一个给定键值的具体行。只能找到行所在的页,然后数据库通过把页读入内存,再在内存中查找,最后找到要查找的数据。
二、数据结构与算法
二叉查找树->平衡二叉树(AVL)
平衡二叉树的查询速度很快,但维护代价非常大(左旋、右旋)
三、B+树
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树,所有记录节点都是按照键值的大小顺序放在同一层的叶子节点上,由各叶子节点指针进行连接。
如上图B+树,高度为2,每页可存放4条记录,扇出(fan out)为5(可以理解为每个节点对应的下层节点)。
四、B+树索引
B+索引在数据库中有一个特点就是高扇出性,因此数据库中B+树高度一般为2-4层,即查找某一键值的行记录做多只需要2-4次IO。
B+索引可分为聚集索引(clustered index)和辅助索引(secondary index)/非聚集索引
-
聚集索引
聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。同B+树数据结构一样,每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一颗B+树进行排序,所以每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引,因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,聚集索引能够特别快地访问范围值的查询。 -
辅助索引(非聚集索引)
叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点的索引行中还包含了一个bookmark–即相应行数据的聚集索引键。
由于辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,再通过主键索引来找到一个完整的行记录。
五、Cardinality值
六、B+树索引的使用
6.1 不同应用中B+树索引的应用
6.2 联合索引
联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引的创建方法一样,不同之处在于有多个索引列。
如下代码创建一张t表,idx_a_b是联合索引,联合的列为(a, b)
CREATE TABLE t{
a INT,
b INT,
PRIMARY KEY (a),
KEY idx_a_b (a, b)
}ENGINE=INNODB
何时需要使用联合索引呢?先看一下联合索引内部结构:
其实和单个键值的B+树类似,键值都是排序的。因此,对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx,显然是可以使用这个索引的;对于单个的a列查询SELECT * FROM TABLE WHERE a=xxx,也可以使用;但对于SELECT * FROM TABLE WHERE b=xxx则不可以,因为叶节点上的b值并不是排序的(只是a优先排序)。
联合索引的另一个优点是,对于值相等的a,排序的过程中已经对b排序(可以类比为查询中分组查询并排序)。不用联合索引则需要多一次的排序操作。
6.3 覆盖索引
InnoDB存储引擎支持覆盖索引(covering index),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。另一个好处是对于某些统计问题而言的,例如count(*),InnoDB不会选择通过查询聚集索引来进行统计,由于有辅助索引,故优化器会选择查询辅助索引。