数据库索引技术

转载：

一、 什么是索引和建立索引的好处

1、 什么是索引

在数据库中，索引的含义与日常意义上的“索引”一词并无多大区别，与书中的索引一样，数据库中的索引使您可以快速找到表中的特定信息。索引包含从表中一个或多个列生成的键，以及映射到指定数据的存储位置的指针，也就是说索引由键 和 指针组成。它是用于提高数据库表数据访问速度的数据库对象。

2、 建立索引的好处：

索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。

对于非聚集索引，有些查询甚至可以不访问数据页。如字典的目录就可以查到所有拼音第一字母为z 的所有字。

聚集索引可以避免数据插入操作集中于表的最后一个数据页。

一些情况下，索引还可用于避免排序操作。

3、 索引的存储

索引包含由表中的一列或多列生成的键。这些键存储在一个结构（B 树）中，不同于二叉树。同一个分支下有一个或多个子节点。

B树的简单结构：

    见附件图1

 (从图可见，当我们插入关键字4时，由于原结点已经满了，故进行分裂，基本按一半的原则进行分裂，然后取出中间的关键字2，升级（这里是成为根结点）。其它的依类推，就是这样一个大概的过程。)

一条索引记录中包含的基本信息包括：键值 + 逻辑指针。

   见附件图2

二、 什么是聚集索引

1、 聚集索引定义

聚集索引是根据数据行的键值在表中排序存储数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

2、 聚集索引的结构

对于某个聚集索引，索引指向该聚集索引某个特定分区（数据页）的顶部。SQL Server 将在索引中向下移动以查找与某个聚集索引键对应的行。原因是聚集索引的索引顺序就是数据排列顺序。

   见附件图3

3、 聚集索引与查询操作

如上图，在建立聚集索引后，当需要在根据此字段查找特定的记录时，数据库系统会根据特定的系统表查找的此索引的根，然后根据指针查找下一个，直到找到。数据查询时首先是对索引表查询，如果此时索引表在缓存中可以找到，则可以避免一次IO操作。在索引表中找到所需数据索引值后，就可以确定目标数据行所在的数据位置，从而读取数据。

4、 聚集索引与插入和删除操作

插入数据时，首先根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。

删除数据时将导致其下方的数据行向上移动以填充删除记录造成的空白。

对于数据的删除操作，可能导致索引页中仅有一条记录，这时，该记录可能会被移至邻近的索引页中，原索引页将被回收，即所谓的“索引合并”。同样插入数据页会更改索引。每一次索引更改都是一次IO操作。

聚集索引的建立会降低数据插入和删除的效率。

三、 什么是非聚集索引

1、 非聚集索引定义

非聚集索引并不是在物理上排列数据,即索引中的逻辑顺序并不等同于表中行的物理顺序,索引是指向表中行的位置的指针,这些指针本身是有序的,通过这些指针可以在表中快速定位数据。

 

2、 非聚集索引的结构

由于非聚集索引数据存储时无序的，所以在非聚集索引中指针包含数据行在数据页中的偏移量。即指针由 数据页 + 数据行偏移量 组成。

     见附件图4

3、 非聚集索引的查询

如上图，在建立非聚集索引后，当需要在根据此字段查找特定的记录时，数据库系统会根据特定的系统表查找的此索引的根，然后根据指针查找，直到找到。数据查询时首先是对索引表查询，如果此时索引表在缓存中可以找到，则可以避免一次IO操作。在索引表中找到所需数据索引值后，就可以确定目标数据行所在的数据位置，从而读取数据。

4、 非聚集索引的插入删除

如果一张表包含一个非聚集索引但没有聚集索引，则新的数据将被插入到最末一个数据页中，然后非聚集索引将被更新。如果也包含聚集索引，该聚集索引将被用于查找新行将要处于什么位置，随后，聚集索引、以及非聚集索引将被更新。

如果在删除命令的Where子句中包含的列上，建有非聚集索引，那么该非聚集索引将被用于查找数据行的位置，数据删除之后，位于索引叶子上的对应记录也将被删除。如果该表上有其它非聚集索引，则它们叶子结点上的相应数据也要删除。

四、 聚集索引和非聚集的区别

聚集索引和非聚集索引的根本区别是数据记录的排列顺序和索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后，从而缩小了搜索范围，对于返回某一范围的数据效果最好。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

　　非聚集索引指定了表中记录的逻辑顺序，数据记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B树的结构，但非聚集索引的叶子层顺序并不与实际的数据页相同，而采用指向表中的记录在数据页中位置的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。在有大量不同数据的列上建立非聚集索引，可以提高数据的查询和修改速度。

在对聚集索引列查询时，聚集索引的速度要比非聚集索引速度快。

在对聚集索引列排序时，聚集索引的速度要比非聚集索引速度快。但是如果数据量比较大时，如10万以上，则二者的速度差别不明显。

 

五、 聚集索引和非聚集的建立原则

在创建索引时要做到三个适当，即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话来概括优化的索引的基本准则，但是要做到这一点的话，需要做出很大的努力。具体的来说，要做到这个三个适当有如下几个要求。

1、 根据表的大小来创建索引。

虽然给表创建索引，可以提高查询的效率。但是需要注意的是，索引也需要一定的开销的。为此并不是说给所有的表都创建索引，那么就可以提高数据库的性能。这个认识是错误的。给所有的表都创建了索引，那么其反而会给数据库的性能造成负面的影响。因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益。所以，数据库管理员首先需要做到，为合适的表来建立索引，而不是为所有的表建立索引。

一般来说，不需要为比较小的表创建索引。因为即使建立了索引，其性能也不会得到很大的改善。相反索引建立的开销，如维护成本等等，要比这个要大。也就是说，付出的要比得到的多，显然违反常理。

另外，就是对于超大的表，也不一定要建立索引。有些表虽然比较大，记录数量非常的多。但是此时为这个表建立索引并一定的合适。对于一些超大的表，建立索引有时候往往不能够达到预计的效果。而且在大表上建立索引，其索引的开销要比普通的表大的多。那么到底是否给大表建立索引呢？主要是看两个方面的内容。首先是需要关注一下，在这张大表中经常需要查询的记录数量。一般来说，如果经常需要查询的数据不超过10%到15%的话，那就没有必要为其建立索引的必要。因为此时建立索引的开销可能要比性能的改善大的多。如果数据库管理员需要得出一个比较精确的结论，那么就需要进行测试分析。

2、 根据列的特征来创建索引

列的特点不同，索引创建的效果也不同。需要了解为哪些列创建索引可以起到事半功倍的效果。同时也需要了解为哪些列创建索引反而起到的是事倍功半的效果。

索引设置的是否恰当，不仅跟数据库设计架构有关，而且还跟企业的经济业务相关。虽然一开始已经做了索引的优化工作。但是随着后来经济数据的增加，这个索引的效果会越来越打折扣。所以需要隔一段时间，对数据库的索引进行优化。该去掉的去掉，该调整的调整，以提高数据库的性能。

 

3、 在一个表上创建多少索引合适

通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新同时也是索引的更新。到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。如对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候导入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。由于更新操作比较频繁，所以对其的负面影响，要比查询效率提升要大的多。此时就需要限制索引的数量，只在一些必要的字段上建立索引。

总之，在适当的表、适当的列上建立适当的索引。具体的索引优化内容还是需要在日常工作中继续体会与总结。

下面的表总结了何时使用聚集索引或非聚集索引：

 

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

六、 什么是复合索引

1、 复合索引定义

 

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

如：建立 姓名、年龄、性别的复合索引。

起作用的复合索引查询：

  见附件图5

 

 

2、 复合索引的建立原则：

如果您很可能仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。

如上图所示，如果查询中需要对年龄和性别做查询，则应当再新建一个包含年龄和性别的复合索引。

包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索，确定哪一列应该排在最前面。

请注意，创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

3、 复合索引对排序的优化：

复合索引只对和索引中排序相同或相反的order by 语句优化。

在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引：

CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

其中 有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询

1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC 和索引顺序相同

2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC 和索引顺序相反

查询1，2 都可以别复合索引优化。

如果查询为：

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC 排序结果和索引完全不同时，此时的查询不会被复合索引优化。

 

查询优化器在在where查询中的作用：

如果一个多列索引存在于 列 Col1 和 Col2 上，

则以下语句：Select   * from table where   col1=val1 AND col2=val2 

查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。

如果存在一个多列索引，任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面。

如：一个多列索引为 (col1 ，col2， col3)

    那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。

SELECT * FROM tb WHERE  col1 = val1

SELECT * FROM tb WHERE  col1 = val1 and col2 = val2

SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  AND col3 = val3

如果列不构成索引的最左面前缀，则建立的索引将不起作用。

 如：

SELECT * FROM  tb WHERE  col3 = val3

SELECT * FROM  tb  WHERE  col2 = val2

SELECT * FROM  tb  WHERE  col2 = val2  and  col3=val3

如果一个 Like 语句的查询条件不以通配符起始则使用索引。

如：%车 或 %车%   不使用索引。

    车%              使用索引。

索引的缺点：

占用磁盘空间。

增加了插入和删除的操作时间。一个表拥有的索引越多，插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

 

七、 下面是一些常见的索引限制问题

1、 使用不等于操作符(<>, !=)

下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描 

select * from dept where staff_num <> 1000; 

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？ 

有！ 

通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。 

select * from dept shere staff_num < 1000 or dept_id > 1000; 

 

2、 使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。 

解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

 

3、 使用函数

如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引： 

select * from staff where trunc(birthdate) = '01-MAY-82'; 

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。 

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 

 

4、 比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。

下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。 

select * from dept where dept_id = 900198; 

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。

把SQL语句改为如下形式就可以使用索引 

select * from dept where dept_id = '900198';