数据库索引

索引是一个独立的物理的数据库结构，它是某个表中的一列或者多列值的集合和相应的指向表中的物理标识

这些值的数据页的逻辑指针清单。索引依赖于表建立的，它提供了数据库中编排表中数据的内部方法。

一个表的存储是由两个部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上

通常，索引页面相对于数据页面小得多，当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接

通过指针从数据页面中读取数据。

访问数据的方式

系统访问数据库中数据时，可以采用两种方法：表扫描和索引查找。

表扫描：指系统将指针放在该表的表头数据所在的数据页上，然后按照数据页的排列顺序，一页一页地从前向后扫描该表数据所占所有的全部数据页，直至扫描完表中的全部记录。在扫描时，如果找到符合查询条件的记录，就将这条记录挑选出来。最后将全部挑选出来符合查询语句条件的记录显示出来。

索引查找：索引是一种树状结构，其中存储了关键字和指向包含关键字所在的记录的数据页的指针。当使用索引查找时，系统沿着索引的树状结构，根据索引中关键字和指针，找到符合查询条件的记录。最后将全部查找到的符合查询语句条件的记录显示出来。

 

 

索引结构

SQL Server索引的结构一般是一个B树， B树是一个多层次自维护的结构。一个B树包含一个顶层（根结点），零到多个中间层，一个底层，底层中包括若干个叶子节点。
B树的深度越深，即层次越多，读取记录所要访问的索引页就越多。也就是说，数据查询的性能将随索引列层次数目的增加而降低。

索引的优点

创建索引可以大大提高系统的性能：
第一，通过创建惟一性索引，可以保证每一行数据的惟一性。
第二，可以大大加快数据的检索速度，这也是索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用ORDER BY和GROUP BY子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 

索引的种类

根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引，另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。

SQL Server索引有4种类型，即聚簇索引、非聚簇索引、惟一索引及全文本索引。
其中，聚簇索引和非聚簇索引是两种主要的索引类型。对于这两种索引类型之中的任何一种索引，你都可以指定该索引是否应该是一个惟一索引(实际上它是一种特殊的聚集或非聚簇索引)。
而全文本索引更像一个目录，它具有与聚簇索引和非聚簇索引不同的特性，是代表它自己的一类索引。

1．聚簇索引（Clustered Index）
聚簇索引（或聚集索引）是一种指明表数据物理存储顺序的索引。在聚簇索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。表数据按照指定作为聚簇索引的一个或多个键列排序并存储。

在某列上建立了索引之后，这列会重新排序，使其按照索引的顺序进行排列；

由于聚簇索引的顺序与数据行存放的物理顺序相同，因此，聚簇索引最适合于范围搜索。因为相邻的行将被物理地存放在相同的页面上或相邻近的页面上，所以当在用于查找一定范围值的列上创建聚簇索引时，聚簇索引非常有效。

例如，在图书馆中存放着很多书，这些书可以按照作者顺序存放，也可以按照书名顺序存放，还可以按照书的出版社排序存放。假设现在这些书是杂乱存放的，并且在书名列上建立了聚簇索引，那么这些书就必须按照书名的顺序重新排放，使得数据（书）按照索引排序。这就是聚簇索引。

创建聚簇索引应该注意以下事项：
1.每张表只能有一个聚簇索引。
2. 聚簇索引的平均大小大约是数据表的5%，但实际的聚簇索引的大小常常根据索引列的大小不同而变化。
3.创建聚簇索引时所需的可用空间应是数据库表中数据量的120%。
4.数据表中行存放的物理顺序与索引中行的物理顺序是相同的。
5.主键是聚簇索引的良好候选者。
6.缺省设置是非聚簇索引。
7. 关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些惟一性标识符是系统自己使用的，用户不能访问。

 

2. 非聚簇索引（Nonclustered Index）
非聚簇索引（或非聚集索引）与聚簇索引不同，它并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序。索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据

 

a. 创建非聚簇索引应该注意以下事项：
b. SQL Server允许一个表上最多可以有249个非聚簇索引。
c. 为一个表建立索引默认都是非聚簇索引。
d. 在一列上设置惟一性约束时也自动在该列上创建非聚簇索引。
e. 索引页的叶级只包含索引的关键字，不包含实际的数据。

 

3.．惟一索引（Unique Index）
在SQL Server 中，当唯一性是数据本身的特点时，或如果要求索引中的字段值不能重复，可创建唯一索引。
惟一索引既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构，被特殊地创建成惟一的。插入到键值列或列组的值必须是惟一的，不允许有重复的键值。对于复合键，这意味着值的组合必须是惟一的。
另外，还可以通过创建UNIQUE约束的方法强制特定列的数据的惟一性。当创建UNIQUE约束时，SQL Server自动地创建一个惟一非聚簇索引以强制该约束。一旦你在一个表上拥有惟一索引，SQL Server会阻止向表中输入相同的数据，并将给出一条错误消息，指出试图插入相同的行。

 

4. 全文本索引（Full-text Index）
全文本索引实际上就像一个目录，它不是简单地在有序B树中进行查找，而是允许你使用关键字组进行查找。全文本索引是微软查找服务的一部分，它被广泛地应用在Web站点搜索引擎和其他基于文本的操作中。与B树索引不同，这种索引存储在数据库的外部，但仍由数据库来维护。通过这种方式，索引可以保持它自己的结构。
需要说明的是，如果使用全文本索引，你必须安装SQL Server标准版或企业版提供的全文本搜索服务。要选择这个选项，就必须在安装时选择自定义安装。

 

 

                               创建索引的列

1、创建索引的理由
对表中的列是否创建索引，以及创建何种索引，对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。因为列上未创建索引，在用该列为条件查询时，SQL Server就要一行一行地去查看整个表，这种扫描所耗费的时间直接同表中的数据量成正比。所以，创建索引的一个最大优点就是能够加速数据检索。
另外，创建索引还能加速连接、ORDER BY和GROUP BY语句的执行。除此之外，通过创建惟一索引，还可以保证表中的数据不重复，强制实施行的惟一性。

2、不在每一列上建索引的理由
由于索引的创建通常有助于改进大型的或频繁更新的数据库的性能，因而每个索引必须保持是最新的。因此对一个表所进行的每个插入、更新和删除操作，如果所涉及的列也包含在索引定义中，都需要对索引进行更新。
当我们在创建聚簇索引时，索引所需要的可用空间应该是数据库表中数据量的120 % ,该空间不包括现存表已经占用的空间。在建立索引时，数据被复制以便建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除，而且创建索引也需要时间。所以，创建索引要花费时间和占用存储空间。

此外，建立索引加快了检索速度，却减慢了数据修改速度。因为每当执行一次数据修改（包括插入、删除和更新），就要维护索引，修改的数据越多，涉及维护索引的开销也就越大。所以修改数据时要动态维护其索引，对建立了索引的列执行修改操作要比未建立索引的列执行修改操作所花的时间长。
也就是说，索引虽然可以加快数据查询的速度，但是会减慢数据插入的速度。并且，如果将一些数据行插入到一个已经放满行的数据页面上，就必须将这个数据页面中最后一些数据移到下一个页面中去。这样，你必须改变索引页中的内容，以保持数据顺序的正确性，这就是对索引的维护。它必须花费代价，而且减慢了数据插入的速度。
因此，虽说创建索引有很多优点，但也并不是在每一列上都要创建索引。此外，一个索引的最大字节为900字节，在char等类型的大列上创建索引时应考虑这一限制，在多列上创建索引时也要注意字节总和不要超过这个数。一个索引中最多包含的列数为16。

 

3、考虑创建索引的列
从上所述可以看出，有了索引的缺点是：创建索引和维护索引是要耗费时间的，并且索引占用磁盘物理空间，如果建立聚簇索引，那么需要的空间更大；并且在每次给表添加一行时，都必须修改这个索引；每次修改现有行中的一个已经被索引的列时，也必须修改这个索引。（当对表中的数据进行增加、修改和删除的时候，索引也要动态的维护，这样就降低了数据的维护速度）。这样就会使表的INSERT和UPDATE语句承担额外的开销，因此，没有必要对表中的所有列都建立索引。

 

 

如下情况的列考虑创建索引：
主键。
连接中频繁使用的列（外键）。
在某一范围内频繁搜索的列和按排序顺序频繁检索的列。
在经常用在WHERE子句中的列上创建索引。

 

如下情况的列不考虑建立索引：
很少或从来不在查询中引用的列。
只有两个或若干个值的列，也得不到建立索引的好处。
小表（行数很少的表）一般也没有必要创建索引。
对于那些定义为text、image和bit数据类型的列。
当UPDATE性能远远大于SELECT性能时，不应该创建索引。

 

 

 

 

 

 

 

 

 

 

 

小结：

1.可以加快查询速度，但是，是以牺牲删除，修改，添加操作为代价的，比如，你往表格里面插入数据的时候，所以为了维护索引，需要操作索引

2.需要存储空间

 

索引的分类：

1，主索引，主键自动的为主索引（类型 primary)

2.唯一索引（unique）

3.普通索引（index)

4.全文索引（fulltext)(只有myisam引擎支持，sphinx+中文分词  coreseek）

5.复合索引（多列合在一起）

 

查看索引的使用情况：

show  status like 'Header_read%';

handler_read_key：这个值越高，表示使用索引查询的次数

handler_read_rnd_next: 这个值越高，说明查询低效

 

mysql> show status like 'handler_read%';

+-----------------------+--------+

| Variable_name         | Value  |

+-----------------------+--------+

| Handler_read_first    | 2      |

| Handler_read_key      | 4      |

| Handler_read_last     | 0      |

| Handler_read_next     | 212826 |

| Handler_read_prev     | 0      |

| Handler_read_rnd      | 0      |

| Handler_read_rnd_next | 183    |

+-----------------------+--------+