sql索引

学习自：http://blog.csdn.net/dinglang_2009/article/details/5951428

参照原文，自己整理了一遍

一：系统访问数据

1:表扫描

　　从数据表表头开始，系统指针按照数据页的排列顺序，逐页从前向后扫描该表全部数据页。

2:索引查找

　　索引是一种树状结构，存储了关键字and包含关键字所在记录的数据页的指针。

note：

sqlserver 先确认是否有索引，没有则表扫描。

查询处理器根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为目标，确定是使用表扫描还是使用索引。

二：建立索引的优点（提高系统性能）

1:唯一性索引保证表中每行数据的唯一性

2:加快数据检索速度

3:加速表之间的连接，有助于实现数据的参考完整性

4:提高带有 分组和排序 的效率

三：建立索引的缺点

1:索引 需要物理空间，聚簇索引所需空间更大

2:创建和维护索引耗时，而且跟数据量直接相关

3:索引需要动态维护，操作表中数据增删改时，索引也会动态调整

四：建立索引的准则

一般，应该在这些列上创建索引：

1:经常有搜索的操作，索引加快搜索

2:主键列，保证唯一性和表中数据的排列结构

3:常做  连接 操作的列（外健），加快连接

4:范围搜索，索引排序的基础帮助提高搜索效率

5:经常需要排序的列；

6:经常在where后边出现的列，加快条件判断速度

不适合：数据量不大；修改性能远远大于检索性能，因为互相矛盾

五：如何创建索引

1:直接

--CREATE INDEX indextest ON student(Student_Id);

(具有柔性，可以使用选项，比如数据页的充满度、排序、整理统计信息等，指定索引类型、唯一性等；可以创建出符合自己需求的索引，达到优化)

(既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引)

--使用创建索引向导

2:间接

在表中定义主键（自动创建聚簇索引）或者唯一性约束（非聚簇索引）同时创建了索引(完整性)

在物理结构上，与主键约束对应的结构时唯一性的聚簇索引；

当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。

主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

六：索引的类型（数据表的物理顺序与索引顺序是否相同）

1:聚簇索引（相同）

2:非聚簇索引

七：索引的特征

1:唯一性索引

保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。

2:复合索引

复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。

当创建复合索引时，应该考虑这些规则：

--最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节；

--在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；

--在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；

--为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；

--当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。

 

八：聚簇索引的体系结构

索引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。

在聚簇索引中同理。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数据值的顺序总是按照升序排列。

应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。

当创建聚簇索引时，应该考虑这些因素：

1:每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个；

2:表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序；

3:关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些唯一性标识符是系统自己使用的，用户不能访问；

4:聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变化；

5:在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要保证有足够的空间来创建聚簇索引。

搜索过程：

当系统访问表中的数据时，首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义，那么系统使用该索引访问表中的记录。系统从索引开始浏览到数据，索引浏览则从树状索引的根部开始。从根部开始，搜索值与每一个关键值相比较，确定搜索值是否大于或者等于关键值。这一步重复进行，直到碰上一个比搜索值大的关键值，或者该搜索值大于或者等于索引页上所有的关键值为止。

九：聚簇索引和非 聚簇索引

聚簇索引的顺序就是数据的物理存储顺序；

非聚簇索引：索引顺序与数据物理排列顺序无关。

在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。http://www.jb51.net/article/29693.htm