数据库索引知识

1      索引概念

1.1  定义

索引是对数据库管理系统中一列或多列的值排序数据结构。更容易理解的比喻是它好比一本书前面的目录。

2      技术原理

要完全理解索引原理需要了解大量知识，这包括B树，堆，数据库页，分区，填充因子，碎片，文件组等等，这些知识结构体庞大，本方案不深入讨论。其简单来说就是在数据库创建一列或多列索引，实际上是数据库管理系统在该列数据值上创建了优化的排序数据结构，而这些数据结构以物理特征保存在数据库系统磁盘上，从而达到快速的数据检索目的。

3      索引种类

3.1  类型分类

1) 聚簇索引

按照数据存放物理位置为顺序。是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是记录指针。

2) 非聚簇索引

表数据的存储顺序和索引顺序无关。是一种密集索引，在数据页上一级索引页为每一个数据行存储一条索引记录。

3.2  功能分类

1) 唯一索引

不允许其中任何两行具有相同索引值的索引。

[数据中存在重复的键值时，大多数数据库系统不允许新创建的唯一索引和表保存，数据库中为此防止了重复数据添加。例如:在职工表姓氏上创建唯一索引，则任何两个员工都不能同姓。]

2) 主键索引

主键索引是唯一索引的特定类型。

[数据库表创建主键后将自动生成主键索引，该索引要求主键中的每一个值都具有唯一性]

3) 聚集索引

表中行的物理顺序与键值的逻辑顺序相同，且一个数据表只能包含一个聚集索引。

[与非聚集索引相比，聚集索引具有更快的访问速度]

4      索引特点

4.1  目的

创建数据表索引可以加快对表进行查找和排序。可以大大提高系统性能。

4.2  优点

1.  唯一索引可以保证数据表中的每一行具有唯一性。

2.  加快数据库表检索数据的速度。

3.  加快数据库表和表之间的连接速度。

4.  加快数据库表分组和排序的速度。

4.3  缺点

1.  索引的创建需要占用一定磁盘物理空间。

2.  在维护索引（插入和修改）的时候需要更多的时间[这种时间随着数据增加而增加]。

5      索引创建

5.1  适宜创建索引列

1.  经常需要搜索的列。

2.  作为主键的列[强调该列唯一性]。

3.  作为外键的列[可以加快表的连接速度]。

4.  经常按照范围搜索的列[因为索引已经排序]。

5.  经常排序的列[因为索引已经排序]。

6.  在Where条件子句上的列[加快条件判断速度]。

5.2  不宜创建索引列

1.  查询中很少使用的列。

2.  数据列上的值很少的列。

3.  定义为text,image,bit字段类型的列。

6      创建方案

6.1  语法

 

 

6.2  约定

索引名称命名规则：

数据表名_数据索引列名1[_数据索引列名2][_数据索引列名3][…]

6.3  VBMS创建索引

创建索引应根据具体数据库使用和索引支持情况而定，而我们VBMS系统使用的MYSQL数据库，而MYSQL数据库有多种存储引擎，推荐博文一篇，有助于了解MYSQL创建索引方案。

浅谈MySQL索引背后的数据结构及算法 :http://blog.linezing.com/?p=798#nav-1

 

提炼出精华部分如下：

Ø MyISAM存储引擎——索引实现原理

    

1.  MyISAM采用B+Tree作为索引的结构。

2.  索引文件和数据文件分离的结构，叶子节点存储的是数据存放的地址。

3.  两张图分别为MyISAM主索引和辅助索引结构原理图。

4.  主索引的KEY不可以重复，辅助索引的KEY可以重复。

5.  MyISAM这种索引结构叫做非聚集索引。

MyISAM中索引检索的算法首先按照B+Tree搜索算法搜索索引，如果指定的KEY存在，则获取data域的值，在以data域为地址，获取到相应记录。

Ø InnoDB存储引擎——索引实现原理

 

1.  InnoDB也采用B+Tree作为索引的结构。

2.  InnoDB结构文件就是索引文件，叶子节点存储的事数据文件。

3.  两张图分别为InnoDB主索引和辅助索引结构原理图。

4.  采用InnoDB存储引擎存储数据要求数据表必须有主键，没有指定则数据库系统自动生成。

5.  InnoDB辅助索引中data域存放的值是相应记录的主键。

6.  InnoDB这种索引结构叫做聚集索引。

InnoDB辅助索引检索的算法首先根据辅助角色检索到主键，然后在根据主键索引检索到数据记录。