数据库索引实现原理

索引在数据库中的作用

在数据库系统的使用过程中，数据的查询是使用最频繁的一种数据操作。
最基本的查询算法当然是顺序查找（linear search），遍历表然后逐行匹配行值是否等于待查找的关键字，骑士剑复杂度为O（n）。但是时间复杂度O（n）的算法规模小的表，负载轻的数据库，也能有好的性能。但是数据增大的时候，时间复杂度O（n）的算法显然是糟糕的，性能就很快下降了。

计算机科学的发展提供了很多更优秀的查找算法，例如二分法（binary search ）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于而查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算的的数据结构，这些数据节后以某种方式引用（指向）数据，这样就尅在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引是对数据库表中一个或多个列的值进行排序的结构。与在表中搜索所有的行相比，索引用指针指向存储在表中指定列的数据值，然后根据指定的次序排列这些指针，有助于更快的获取信息。通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且影响数据更新的速度。但是在多数情况下，索引所带来的的数据检索速度优势大大超过他的不足之处。

B+树在数据库索引中的应用

目前大部分数据库系统及文件系统都采用B- Tree或其变种B+ Tree 作为索引结构。

1）在数据库索引的应用
在数据库索引的应用中，B+树按照以下方式进行组织：

• 叶结点的组织方式。B+树的查找键是数据文件的主键，且索引是稠密的。也就是说，叶结点中为数据文件的第一个记录设有一个键、指针对，该数据文件可以按主键排序，也可以不安主键排序；数据文件按主键排序，且B+树是稀疏索引，在叶结点中为数据文件的每一个块设有一个键、指针对；数据文件不按键属性排序，且该属性是B+叔的查找键，叶结点中为数据文件里出现的每个属性K设有一个键、指针对，其中指针执行排序键值为K的记录中的第一个。
• 非叶结点的组织方式。B+树中的非叶结点形成了叶结点的一个多级稀疏索引。每个非叶结点中至少有ceil（m/2）个指针，至多有m个指针。

2）B+树索引的插入和删除

• 在数据库中插入新的数据时，同时也需要想数据库索引中插入响应的索引键值，则需要向B+树中插入新的键值。
• 当从数据库中删除数据时，同时也需要从数据库索引中删除响应的索引键值，则需要从B+树中删除该键值。

MyISAM 和InnoDB两个存储引擎的索引实现方式：

MyISAM：应用时以读和插入操作为主，只有少量的更新和删除，并且随事务的完整性，并发行要求不是很高的。
InnoDB：事务处理，以及并发条件下要求数据的一致性。除了插入和差尊外 ，包括很多的更新和删除。
1.MyISAM索引实现

1）主键索引

MyISAM引擎使用B+Tree 作为索引结构，叶结点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以col1为主键，图myisam1 是一个MyISAM 表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM 中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是卫衣的，而辅助索引的key 可以重复。如果二面在Col2 上建立一个辅助索引，则词索引的结构如下图所示：

同样也是一棵B+ Tree ，data域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照B+ Tree 搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data 域的值为地址，读取响应数据记录。

2.InnoDB 索引实现
然InnoDB 也使用B+ Tree 作为索引结构，但具体实现方式却与MyISAM截然不同。

1）主键索引：
MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB 中，表数据文件本身就是按B+ Tree 组织的所有结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB 表示数据文件本身就是主索引。

（图InnoDB主键索引）是InnoDB 主索引（同时也是数据文件）的示意图，可以看出叶结点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB 的数据文件本身要按主键聚集，所以InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显示指定，则MySql 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

2）InnoDB 的辅助索引:
InnoDB 的所有辅助索引都引用主键作为data域。了如，下图为定义在Col3 上的一个辅助索引：

InnoDB表示基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，他的辅助索引（Secondary Index，也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义的小一些。InnoDB不会压缩索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

InnoDB 索引和MyISAM索引的区别：
（1）主索引的区别：InnoDB 的数据文件本身就是索引文件。而MyISAM 的索引个数据是分开的。
（2）复制索引的区别：InnoDB 的复制索引data域存储相应记录主键的值而不是地址。而MyISAM 的辅助索引和主索引没有多大区别。