数据库索引原理

摘要
本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree索引的查询流程简介。
此文是我对学习InnoDB索引的一个总结,内容主要参考MySQL技术内幕 InnoDB存储引擎一书,及网上一些博客(参考文献会给出)
一、先从B+Tree入手
B+树的特性
因作者文笔有限,B+树的定义如果在这里重复列出的话,应该只会让大家更困惑,同时相信任何一本数据结构书中都能找到其复杂的定义。但是为了便于读者理解接下来的内容,下面只是简单的介绍一下B+树的几个本文中会用到的特性。
B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树(如果不知道平衡查找树,请自行google),在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶节点中,各叶节点指针进行连接。
下图是在网上找的一张B+树示意图

二、InnoDB数据页结构
1.页介绍
页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-Tree node的页,存放的即是表中行的实际数据了。
InnoDB中的页大小为16KB,且不可以更改
InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外,即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节,但实际只能存储65532个。同时InnoDB是B+树结构的,因此每个页中至少应该有两个行记录,否则失去了B+树的意义,变成了链表,所以一行记录最大长度的阈值是8098,如果大于这个值就会将其存到溢出行中。

2.InnoDB数据页组成部分
File Header(文件头)
Page Header(页头)
Infimun + Supremum Records
User Records(用户记录,即行记录)
Free Space(空闲空间)
Page Directory(页目录)
File Trailer(文件结尾信息)
这也是我摘抄的书上的内容,下面我只介绍一下会帮助理解底层原理的部分。

1.在File header中,FIL+PAGE_PREV,FIL_PAGE_NEXT两个表示当前页的上一页和下一页,由此可以看出叶子节点是双向链表串起来的。如下图

2.Infimum和Supremum记录
在InnoDB存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值,Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立,并且在任何情况下不会被删除。

由上图可以看出,行记录是记录在页中的,同时是在页内行记录之间也是双向链表链接的(在网上有看到说是单链表的)
3.Page Directory
页目录中存放了记录的相对位置,有些时候这些记录指针称为Slots(槽)或者目录槽,与其他数据库不同的是,InnoDB并不是每个记录拥有一个槽,InnoDB中的槽是一个稀疏目录,即一个槽中可能属于多个记录,最少属于4个目录,最多属于8个目录。槽中记录按照键顺序存放,这样可以利用二叉查找迅速找到记录的指针。但是由于InnoDB中的Slots是稀疏目录,二叉查找的结果只是一个粗略的结果,所以InnoDB必须通过recorder header中的next_record来继续查找相关记录。同时slots很好的解释了recorder header中的n_owned值的含义,即还有多少记录需要查找,因为这些记录并不包括在slots中。

三、查询B+树索引的流程
首先通过B+树索引找到叶节点,再找到对应的数据页,然后将数据页加载到内存中,通过二分查找Page Directory中的槽,查找出一个粗略的目录,然后根据槽的指针指向链表中的行记录,之后在链表中依次查找。
需要注意的地方是,B+树索引不能找到具体的一条记录,而是只能找到对应的页。把页从磁盘装入到内存中,再通过Page Directory进行二分查找,同时此二分查找也可能找不到具体的行记录(有可能会找到),只是能找到一个接近的链表中的点,再从此点开始遍历链表进行查找。

四、聚簇索引与非聚簇索引
B+树索引可以分为聚集索引和辅助索引,他们不同点是,聚集索引的行数据和主键B+树存储在一起,辅助索引只存储辅助键和主键。
1.聚集索引
聚集索引是按每张表的主键构造的一颗B+树,并且叶节点中存放着整张表的行记录数据,因此也让聚集索引的节点成为数据页,这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序,所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引,因为其直接存储行数据,所以主键的排序查询和范围查找速度非常快。
不是物理上的连续,而是逻辑上的,不过在刚开始时数据是顺序插入的所以是物理上的连续,随着数据增删,物理上不再连续。
2.辅助索引
辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外,每个叶级别中的索引行中还包含了一个书签,该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。
辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值,那么只需要查找辅助索引就可以查询出索要的数据,就不用再去查主键索引了。

五、索引的管理
索引在创建或者删除时,MySQL会先创建一个新的临时表,然后把数据导入临时表,删除原表,再把临时表更名为原表名称。
但是在InnoDB Plugin版本开始,支持快速创建索引。其原理是先在InnoDB上加一个s锁,在创建过程中不需要建表,所以速度会很快。创建过程中由于加了s锁,所以只能进行读操作,不能写操作。
show index form table;是查看表中索引的信息的。
Table:索引所在的表名
Non_unique:非唯一的索引,可以看到primary key 是0,因为必须是唯一的
Key_name:索引名称
Seq_in_index:索引中该列的位置
Column_name:索引的列
Collation:列以什么方式存储在索引中。可以是A或者NULL,B+树索引总是A,即排序的。
Cardinality:表示索引中唯一值的数目的估计值。如果非常小,那么需要考虑是否还需要建立这个索引了。优化器也会根据这个值来判断是否使用这个索引。
Sub_part:是否是列的部分被索引。100表示只索引列的前100个字符。
Packed:关键字如果被压缩。
Null:是否索引的列含有NULL值。
Index_type:索引的类型。InnoDB只支持B+树索引,所以显示BTREE

六、Hash索引
InnoDB中自适应哈希索引使用的是散列表的数据结构,并且DBA无法干预。
其实这一部分的原理,非常简单,在此就不做过多介绍了


索引类型:
  根据数据库的功能,可以在数据库设计器中创建索引:唯一索引、主键索引和聚集索引。 尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束。  

唯一索引:   UNIQUE     例如:create unique index stusno on student(sno);
表明此索引的每一个索引值只对应唯一的数据记录,对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

主键索引:   primary key
数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。   在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。 

聚集索引(也叫聚簇索引):cluster  
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。   如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

通常情况下,建立索引是加快查询速度的有效手段。但索引不是万能的,靠索引并不能实现对所有数据的快速存取。事实上,如果索引策略和数据检索需求严重不符的话,建立索引反而会降低查询性能。因此在实际使用当中,应该充分考虑到索引的开销,包括磁盘空间的开销及处理开销(如资源竞争和加锁)。例如,如果数据频繁的更新或删加,就不宜建立索引。

    本文简要讨论一下聚簇索引的特点及其与非聚簇索引的区别。
  • 建立索引:
在SQL语言中,建立聚簇索引使用CREATE INDEX语句,格式为:CREATE CLUSTER INDEX index_name ON table_name(column_name1,column_name2,...);
  • 存储特点:
  1. 聚集索引。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
  2. 非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。
    总结一下:聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。
  • 更新表数据
1、向表中插入新数据行
    如果一张表没有聚集索引,那么它被称为“堆集”(Heap)。这样的表中的数据行没有特定的顺序,所有的新行将被添加到表的末尾位置。而建立了聚簇索引的数据表则不同:最简单的情况下,插入操作根据索引找到对应的数据页,然后通过挪动已有的记录为新数据腾出空间,最后插入数据。如果数据页已满,则需要拆分数据页,调整索引指针(且如果表还有非聚集索引,还需要更新这些索引指向新的数据页)。而类似于自增列为聚集索引的,数据库系统可能并不拆分数据页,而只是简单的新添数据页。

2、从表中删除数据行

    对删除数据行来说:删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。如果删除的行是该数据页中的最后一行,那么该数据页将被回收,相应的索引页中的记录将被删除。对于数据的删除操作,可能导致索引页中仅有一条记录,这时,该记录可能会被移至邻近的索引页中,原索引页将被回收,即所谓的“索引合并”。

 

 

 

聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿,后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存 储顺序,因此一个表只能包含一个聚簇索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用, 在汉语字典里,索引项是字母+声调,字典正文也是按照先字母再声调的顺序排列。

 

聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚簇(物理排序),避免每次查询该列时都进行排序,从而节省成本。
 

建立聚簇索引的思想

1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争,在一个高事务的环境中,对最后一页的封锁严重影响系统的吞吐量。
2、在聚簇索引下,数据在物理上按顺序排在数据页上,重复值也排在一起,因而在那些包含范围检查 (between、<、<=、>、>=)或使用group by或orderby的查询时,一旦找到具有范围中第一个键值的行,具有后续索引值的行保证物理上毗连在一起而不必进一步搜索,避免了大范围扫描,可以大 大提高查询速度。
3、在一个频繁发生插入操作的表上建立聚簇索引时,不要建在具有单调上升值的列(如IDENTITY)上,否则会经常引起封锁冲突。
4、在聚簇索引中不要包含经常修改的列,因为码值修改后,数据行必须移动到新的位置。
5、选择聚簇索引应基于where子句和连接操作的类型。
 
不知从什么角度来对比,只能说说各自的特点,希望对你有用。
1、聚簇索引
a) 一个索引项直接对应实际数据记录的存储页,可谓“直达”
b) 主键缺省使用它
c) 索引项的排序和数据行的存储排序完全一致,利用这一点,想修改数据的存储顺序,可以通过改变主键的方法(撤销原有主键,另找也能满足主键要求的一个字段或一组字段,重建主键)
d) 一个表只能有一个聚簇索引(理由:数据一旦存储,顺序只能有一种)

2、非聚簇索引
a) 不能“直达”,可能链式地访问多级页表后,才能定位到数据页
b) 一个表可以有多个非聚簇索引

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页