数据库索引方法知识点笔记

InnoDB使用聚集索引

MyISAM使用非聚集索引

MySql的索引实现

表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

叶子结点并非数据结点

叶子结点存储一个指针偏移量，根据页指针及指针偏移量可以定位到数据行。

叶子结点为每一真正的数据行存储一个“键-指针”对

叶子节点包括：
 1.索引字段值；
 2.RowId;

非叶子节点：
 1.索引字段值；
 2.RowId;
 3.下一级索引页指针

内部节点指针指向的是索引页，叶子节点指向数据行

聚集索引是稀疏索引，叶子节点指向数据页；非聚集索引是密集索引，叶子节点指向数据行

与聚集索引的对比

n-1次索引页的查询；1次数据页查询

访问频率较高时，缓存会保存高层索引

查询有可能是从磁盘读取，也可能是缓存读取

查询

聚集索引用于查找新数据行插入位置

聚集索引和非聚集索引更新

表包含聚集索引

插入到最末的数据页中

非聚集索引将被更新

表不包含聚集索引

插入

Where子句中包含的列上，建有非聚集索引，那么该非聚集索引将被用于查找数据行的位置

位于索引叶子上的对应记录也将被删除

如果该表上有其它非聚集索引，则它们叶子结点上的相应数据也要删除。

如果删除的数据是该数所页中的唯一一条，则该页也被回收，同时需要更新各个索引树上的指针。

由于没有自动的合并功能，如果应用程序中有频繁的随机删除操作，最后可能导致表包含多个数据页，但每个页中只有少量数据。

删除

非聚集索引

定义：表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
 在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。

m次数据页查询

在该使用的数据段（extent）上分配新的数据页，如果数据段已满，分配新段。

调整索引指针，这需要将相应的索引页读入内存并加锁。

大约有一半的数据行被归入新的数据页中。

如果表还有非聚集索引，则需要更新这些索引指向新的数据页。

一般情况

插入的记录很大，分配两个数据页，(1)存储新纪录，(2)存储拆分出来的数据

通常数据库系统中会将重复的数据记录存储于相同的页中

类似于自增列为聚集索引的，可能并不拆分数据页，只是简单的新添数据页。

特殊情况

数据页已满，则需要新增和拆分数据页

插入操作根据索引找到对应的数据页，然后挪动已有记录，最后插入数据。

下方的数据行向上移动以填充删除记录造成的空白

无其他数据行，那么该数据页将被回收，相应的索引页中的记录将被删除

所在数据段有其他数据页，之后会被利用

所在数据段无其他数据页，该数据段会被回收

可能导致索引页中仅有一条记录，这时，该记录可能会被移至邻近的索引页中，原索引页将被回收，即所谓的“索引合并”。

说明

索引合并

聚集索引

多叉树(B数的衍生结构，B最可能代表平衡的意思)

常见于数据库与档案系统之中

拥有均匀的对数处理时间的插入和删除动作

最大化在每个内部节点内的子节点的数目减少树的高度

节点数目上的低和高边界是固定的

B+Tree

包含键值和逻辑指针(指向数据页或另一个索引页)

以B+Tree为数据结构，每个节点是一个索引页

由于存储密集，索引中查找时在I/O上占很大的优势

分为聚集索引和非聚集索引

索引定义

定义：SQL所涉及的字段在复合非聚集索引所包含范围内时，由于非聚集索引的叶子节点包含所有数据行的索引列值，使用这些节点即可返回真正数据。

匹配索引扫描:Where子句包含引导列

非匹配索引扫描:Where子句不包含引导列

索引覆盖情况下，包含两种索引扫描

索引覆盖

数据库索引文章详解.doc

数据库索引方法知识点笔记