22,209
社区成员
发帖
与我相关
我的任务
分享如何加快多条件查询速度
sqlserver2000 数据库如一个表table有id,a,b,c,d,e,f,g,h字段,id为自增id,a,b,c,d字段有字符型也可能有数字型
现在要根据a,b,c,d来查询,查询条件可能是:
1,只查询a,b,c,d每个字段的单独查询。如：
select id,a,b,c,d,e,f,g,h from table where a='aaa'
select id,a,b,c,d,e,f,g,h from table where b='bbb'
select id,a,b,c,d,e,f,g,h from table where c='ccc'
select id,a,b,c,d,e,f,g,h from table where d='ddd'
2，也可能组合a和b,或c和d来查询，如：
select id,a,b,c,d,e,f,g,h from table where a='aaa' and b='bbb'
select id,a,b,c,d,e,f,g,h from table where c='ccc' and d='ddd'
如何加快查询速度，有的说用组合索引,如a字段和b字段组合在一起，并且a字段放在前面，但单独查b字段时则慢了啊。
id是否建聚集索引，不建的话，如果查询单条数据是否会变慢,如:
select id,a,b,c,d,e,f,g,h from table id=10
怎样建立合理的聚集索引和非聚集索引和组合索引呢?
现在要根据a,b,c,d来查询,查询条件可能是:
1,只查询a,b,c,d每个字段的单独查询。如：
select id,a,b,c,d,e,f,g,h from table where a='aaa'
select id,a,b,c,d,e,f,g,h from table where b='bbb'
select id,a,b,c,d,e,f,g,h from table where c='ccc'
select id,a,b,c,d,e,f,g,h from table where d='ddd'
2，也可能组合a和b,或c和d来查询，如：
select id,a,b,c,d,e,f,g,h from table where a='aaa' and b='bbb'
select id,a,b,c,d,e,f,g,h from table where c='ccc' and d='ddd'
如何加快查询速度，有的说用组合索引,如a字段和b字段组合在一起，并且a字段放在前面，但单独查b字段时则慢了啊。
id是否建聚集索引，不建的话，如果查询单条数据是否会变慢,如:
select id,a,b,c,d,e,f,g,h from table id=10
怎样建立合理的聚集索引和非聚集索引和组合索引呢?
...全文
请发表友善的回复…
发表回复
dawugui 2012-03-24
- 打赏
- 举报
综上所述，我们总结了如下索引使用原则：
1．逻辑主键使用惟一的成组索引，对系统键（作为存储过程）采用惟一的非成组索引，对任何外键列采用非成组索引。考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写；
2．不要索引memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间；
3．不要索引常用的小型表；
4．一般不要为小型数据表设置过多的索引，如果经常有插入和删除操作就更不要设置索引，因为SQLS对插入和删除操作提供的索引维护可能比扫描表空间消耗的时间更多。
查询是一个物理过程，表面上是SQLS在东跑西跑，其实真正大部分压马路的工作是由磁盘输入输出系统(I/O)完成，全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则I/O读几次磁盘就可以了。但是，在随时发生的增、删、改操作中，索引的存在会大大增加工作量，因此，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。
SQLS是一个很复杂的系统，让索引以及查询背后的东西真相大白，可以帮助我们更为深刻的了解我们的系统。一句话，索引就像盐，少则无味多则咸。
DBCC DBREINDEX重建索引提高SQL Server性能
大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引，SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，而非簇索引则通过维护表中的数据指针来提高数据的索引。
1. 索引的体系结构
为什么要不断的维护表的索引？首先，简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序，就要生成索引。生成索引后，就有了索引页和数据页，数据页保存用户写入的数据信息。索引页存放用于检索列的数据值清单（关键字）和索引表中该值所在纪录的地址指针。索引分为簇索引和非簇索引，簇索引实质上是将表中的数据排序，就好像是字典的索引目录。非簇索引不对数据排序，它只保存了数据的指针地址。向一个带簇索引的表中插入数据，当数据页达到100%时，由于页面没有空间插入新的的纪录，这时就会发生分页，SQL Server 将大约一半的数据从满页中移到空页中，从而生成两个半的满页。这样就有大量的数据空间。簇索引是双向链表，在每一页的头部保存了前一页、后一页地址以及分页后数据移动的地址，由于新页可能在数据库文件中的任何地方，因此页面的链接不一定指向磁盘的下一个物理页，链接可能指向了另一个区域，这就形成了分块，从而减慢了系统的速度。对于带簇索引和非簇索引的表来说，非簇索引的关键字是指向簇索引的，而不是指向数据页的本身。
为了克服数据分块带来的负面影响，需要重构表的索引，这是非常费时的，因此只能在需要时进行。可以通过DBCC SHOWCONTIG来确定是否需要重构表的索引。
2. DBCC SHOWCONTIG用法
下面举例来说明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以应用程序中的Employee数据表作为例子，在 SQL Server的Query analyzer输入命令：
use database_name
declare @table_id int
set @table_id=object_id('Employee')
dbcc showcontig(@table_id)
输出结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 179
- Extents Scanned..............................: 24
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 92.00% [23:25]
- Logical Scan Fragmentation ..................: 0.56%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 552.3
- Avg. Page Density (full).....................: 93.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过分析这些结果可以知道该表的索引是否需要重构。如下描述了每一行的意义：
信息 描述
Pages Scanned 表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches DBCC遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent 相关区域中的页数
Scan Density[Best Count:Actual Count]
Best Count是连续链接时的理想区域改变数，Actual Count是实际区域改变数，Scan Density为100%表示没有分块。
Logical Scan Fragmentation 扫描索引页中失序页的百分比
Extent Scan Fragmentation 不实际相邻和包含链路中所有链接页的区域数
Avg. Bytes Free per Page 扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度，表示页有多满
从上面命令的执行结果可以看的出来，Best count为23 而Actual Count为25这表明orders表有分块需要重构表索引。下面通过DBCC DBREINDEX来重构表的簇索引。
3. DBCC DBREINDEX 用法
重建指定数据库中表的一个或多个索引。
语法
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
)
参数
'database.owner.table_name'
是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息，请参见使用标识符。如果提供 database 或 owner 部分，则必须使用单引号 (') 将整个 database.owner.table_name 括起来。如果只指定 table_name，则不需要单引号。
index_name
是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 ' '，就要对表的所有索引进行重建。
fillfactor
是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor 替换起始填充因子以作为索引或任何其它重建的非聚集索引（因为已重建聚集索引）的新默认值。如果 fillfactor 为 0，DBCC DBREINDEX 在创建索引时将使用指定的起始 fillfactor。
同样在Query Analyzer中输入命令：
dbcc dbreindex('database_name.dbo.Employee','',90)
然后再用DBCC SHOWCONTIG查看重构索引后的结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 178
- Extents Scanned..............................: 23
- Extent Switches..............................: 22
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 100.00% [23:23]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 509.5
- Avg. Page Density (full).....................: 93.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过结果我们可以看到Scan Denity为100%。
1．逻辑主键使用惟一的成组索引，对系统键（作为存储过程）采用惟一的非成组索引，对任何外键列采用非成组索引。考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写；
2．不要索引memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间；
3．不要索引常用的小型表；
4．一般不要为小型数据表设置过多的索引，如果经常有插入和删除操作就更不要设置索引，因为SQLS对插入和删除操作提供的索引维护可能比扫描表空间消耗的时间更多。
查询是一个物理过程，表面上是SQLS在东跑西跑，其实真正大部分压马路的工作是由磁盘输入输出系统(I/O)完成，全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则I/O读几次磁盘就可以了。但是，在随时发生的增、删、改操作中，索引的存在会大大增加工作量，因此，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。
SQLS是一个很复杂的系统，让索引以及查询背后的东西真相大白，可以帮助我们更为深刻的了解我们的系统。一句话，索引就像盐，少则无味多则咸。
DBCC DBREINDEX重建索引提高SQL Server性能
大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引，SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，而非簇索引则通过维护表中的数据指针来提高数据的索引。
1. 索引的体系结构
为什么要不断的维护表的索引？首先，简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序，就要生成索引。生成索引后，就有了索引页和数据页，数据页保存用户写入的数据信息。索引页存放用于检索列的数据值清单（关键字）和索引表中该值所在纪录的地址指针。索引分为簇索引和非簇索引，簇索引实质上是将表中的数据排序，就好像是字典的索引目录。非簇索引不对数据排序，它只保存了数据的指针地址。向一个带簇索引的表中插入数据，当数据页达到100%时，由于页面没有空间插入新的的纪录，这时就会发生分页，SQL Server 将大约一半的数据从满页中移到空页中，从而生成两个半的满页。这样就有大量的数据空间。簇索引是双向链表，在每一页的头部保存了前一页、后一页地址以及分页后数据移动的地址，由于新页可能在数据库文件中的任何地方，因此页面的链接不一定指向磁盘的下一个物理页，链接可能指向了另一个区域，这就形成了分块，从而减慢了系统的速度。对于带簇索引和非簇索引的表来说，非簇索引的关键字是指向簇索引的，而不是指向数据页的本身。
为了克服数据分块带来的负面影响，需要重构表的索引，这是非常费时的，因此只能在需要时进行。可以通过DBCC SHOWCONTIG来确定是否需要重构表的索引。
2. DBCC SHOWCONTIG用法
下面举例来说明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以应用程序中的Employee数据表作为例子，在 SQL Server的Query analyzer输入命令：
use database_name
declare @table_id int
set @table_id=object_id('Employee')
dbcc showcontig(@table_id)
输出结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 179
- Extents Scanned..............................: 24
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 92.00% [23:25]
- Logical Scan Fragmentation ..................: 0.56%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 552.3
- Avg. Page Density (full).....................: 93.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过分析这些结果可以知道该表的索引是否需要重构。如下描述了每一行的意义：
信息 描述
Pages Scanned 表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches DBCC遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent 相关区域中的页数
Scan Density[Best Count:Actual Count]
Best Count是连续链接时的理想区域改变数，Actual Count是实际区域改变数，Scan Density为100%表示没有分块。
Logical Scan Fragmentation 扫描索引页中失序页的百分比
Extent Scan Fragmentation 不实际相邻和包含链路中所有链接页的区域数
Avg. Bytes Free per Page 扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度，表示页有多满
从上面命令的执行结果可以看的出来，Best count为23 而Actual Count为25这表明orders表有分块需要重构表索引。下面通过DBCC DBREINDEX来重构表的簇索引。
3. DBCC DBREINDEX 用法
重建指定数据库中表的一个或多个索引。
语法
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
)
参数
'database.owner.table_name'
是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息，请参见使用标识符。如果提供 database 或 owner 部分，则必须使用单引号 (') 将整个 database.owner.table_name 括起来。如果只指定 table_name，则不需要单引号。
index_name
是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 ' '，就要对表的所有索引进行重建。
fillfactor
是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor 替换起始填充因子以作为索引或任何其它重建的非聚集索引（因为已重建聚集索引）的新默认值。如果 fillfactor 为 0，DBCC DBREINDEX 在创建索引时将使用指定的起始 fillfactor。
同样在Query Analyzer中输入命令：
dbcc dbreindex('database_name.dbo.Employee','',90)
然后再用DBCC SHOWCONTIG查看重构索引后的结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 178
- Extents Scanned..............................: 23
- Extent Switches..............................: 22
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 100.00% [23:23]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 509.5
- Avg. Page Density (full).....................: 93.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过结果我们可以看到Scan Denity为100%。
dawugui 2012-03-24
- 打赏
- 举报
索引的基本原理，以及数据是如何被访问的
（一）SQLS如何访问没有建立索引的数据表
Heap译成汉语叫做“堆”，其本义暗含杂乱无章、无序的意思，前面提到数据值被写进数据页时，由于每一行记录之间并没有特定的排列顺序，所以行与行的顺序就是随机无序的，当然表中的数据页也就是无序的了，而表中所有数据页就形成了“堆”。可以说，一张没有索引的数据表，就像一个只有书柜而没有索引卡片柜的图书馆，书库里面塞满了一堆乱七八糟的图书。当读者对管理员提交查询请求后，管理员就一头钻进书库，对照查找内容从头开始一架一柜的逐本查找。运气好的话，在第一个书架的第一本书就 找到了，运气不好的话，要到最后一个书架的最后一本书才找到。
SQLS在接到查询请求时，首先会分析sysindexes表中一个叫做索引标志符(INDID: Index ID)的字段的值，如果该值为0，表示这是一张数据表而不是索引表，SQLS就会使用sysindexes表的另一个字段——也就是在前面提到过的FirstIAM值中找到该表的IAM页链，也就是所有数据页集合。
这就是对一个没有建立索引的数据表进行数据查找的方式，是不是很没效率？对于没有索引的表，对于一“堆”这样的记录，SQLS也只能这样做，而且更没劲的是，即使在第一行就找到了被查询的记录，SQLS仍然要从头到尾的将表扫描一次。这种查询称为“遍历”，又叫“表扫描”。
可见没有建立索引的数据表照样可以运行，不过这种方法对于小规模的表来说没有什么太大的问题，但要查询海量的数据效率就太低了。
（二）SQLS如何访问建立了非聚集索引的数据表
如前所述，非聚集索引可以建多个，具有B树结构，其叶级节点不包含数据页，只包含索引行。假定一个表中只有非聚集索引，则每个索引行包含了非聚集索引键值以及行定位符（ROW ID,RID），他们指向具有该键值的数据行，每一个RID由文件ID、页编号和在页中行的编号组成。
当INDID的值在2至250之间时，意味着表中存在非聚集索引页。此时，SQLS调用ROOT字段的值指向非聚集索引B树的ROOT，在其中查找与被查询最相近的值，根据这个值找到在非叶级节点中的页号，然后顺藤摸瓜，在叶级节点相应的页面中找到该值的RID，最后根据这个RID在Heap中定位所在的页和行并返回到查询端。
例如：假定在Lastname上建立了非聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
①SQLS查询INDID值为2；
②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第61页；
③仅在叶级页面的第61页的Martin下搜寻Ota的RID，其RID显示为N∶706∶4，表示Lastname字段中名 为Ota的记录位于堆的第706页的第4行，N表示文件的ID值，与数据无关；
④根据上述信息，SQLS立刻在堆的第706页第4行将该记录“揪”出来并显示于前台（客户端）。视表的数据量大小，整个查询过程费时从百分之几毫秒到数毫秒不等。
在谈到索引基本概念的时候，我们就提到了这种方式：图书馆的前台有很多索引卡片柜，里面分了若干的类别，诸如按照书名笔画或拼音顺序、作者笔画或拼音顺序等，但有两点不同之处：
① 索引卡片上记录了每本书摆放的具体位置——位于某柜某架的第几本——而不是“特殊编号”；
② 书脊上并没有那个“特殊编号”。管理员在索引柜中查到所需图书的具体位置（RID）后，根据RID直接在书库中的具体位置将书提出来。
显然，这种查询方式效率很高，但资源占用极大，因为书库中书的位置随时在发生变化，必然要求管理员花费额外的精力和时间随时做好索引更新。
（三）SQLS如何访问建立聚集索引的数据表
在聚集索引中，数据所在的数据页是叶级，索引数据所在的索引页是非叶级。
查询原理和上述对非聚集索引的查询相似，但由于记录是按照聚集索引中索引键值进行排序，换句话说，聚集索引的索引键值也就是具体的数据页。
这就好比书库中的书就是按照书名的拼音在排序，而且也只按照这一种排序方式建立相应的索引卡片，于是查询起来要比上述只建立非聚集索引的方式要简单得多。仍以上面的查询为例：
假定在Lastname字段上建立了聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
①SQLS查询INDID值为1，这是在系统中只建立了聚集索引的标志；
②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第120页；
③在位于叶级页面第120页的Martin下搜寻到Ota条目，而这一条目已是数据记录本身；
④将该记录返回客户端。
这一次的效率比第二种方法更高，以致于看起来更美，然而它最大的优点也恰好是它最大的缺点——由于同一张表中同时只能按照一种顺序排列，所以在任何一种数据表中的聚集索引只能建立一个；并且建立聚集索引需要至少相当于源表120%的附加空间，以存放源表的副本和索引中间页。
难道鱼和熊掌就不能兼顾了吗？办法是有的。
（四）SQLS如何访问既有聚集索引、又有非聚集索引的数据表
如果我们在建立非聚集索引之前先建立了聚集索引的话，那么非聚集索引就可以使用聚集索引的关键字进行检索。就像在图书馆中，前台卡片柜中可以有不同类别的图书索引卡，然而每张卡片上都载明了那个特殊编号——并不是书籍存放的具体位置。这样在最大程度上既照顾了数据检索的快捷性，又使索引的日常维护变得更加可行，这是最为科学的检索方法。
也就是说，在只建立了非聚集索引的情况下，每个叶级节点指明了记录的行定位符（RID）；而在既有聚集索引又有非聚集索引的情况下，每个叶级节点所指向的是该聚集索引的索引键值，即数据记录本身。
假设聚集索引建立在Lastname上，而非聚集索引建立在Firstname上，当执行Select * From Member Where Firstname=’Mike’时，查询过程是：
①SQLS查询INDID值为2；
②立即从根出发，在Firstname的非聚集索引的非叶级节点中定位最接近Mike的值“Jose”条目；
③从Jose条目下的叶级页面中查到Mike逻辑位置——不是RID而是聚集索引的指针；
④根据这一指针所指示位置，直接进入位于Lastname的聚集索引中的叶级页面中到达Mike数据记录本身；
⑤将该记录返回客户端。
这就完全和我们在“索引的基本概念”中讲到的现实场景完全一样了，当数据发生更新的时候，SQLS只负责对聚集索引的键值加以维护，而不必考虑非聚集索引。只要我们在ID类的字段上建立聚集索引，而在其它经常需要查询的字段上建立非聚集索引，通过这种科学的、有针对性的在一张表上分别建立聚集索引和非聚集索引的方法，我们既享受了索引带来的灵活与快捷，又相对避免了维护索引所导致的大量的额外资源消耗。
索引的优点和不足
索引有一些先天不足
1、系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；
2、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
当然建立索引的优点也是显而易见的，在海量数据的情况下，如果合理的建立了索引，则会大大加强SQLS执行查询、对结果进行排序、分组的操作效率。
实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引要花费更多的系统时间。
在如下字段建立索引应该是不恰当的：
1、很少或从不引用的字段；
2、逻辑型的字段，如男或女(是或否)等。
综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，必须要有统筹的规划，一定要在“加快查询速度”与“降低修改速度”之间做好平衡。有得必有失，此消则彼长，这是考验一个DBA是否优秀的很重要的指标
建立索引时一定要在“加快查询速度”与“降低修改速度”之间做好平衡，有得必有失，此消则彼长。那么，SQLS维护索引时究竟怎样消耗资源？应该从哪些方面对索引进行管理与优化？以下从六个方面来回答这些问题。
一．页分裂
微软MOC教导我们：当一个数据页达到了8K容量，如果此时发生插入或更新数据的操作，将导致页的分裂(又名页拆分)：
1．有聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定；
2．只有堆的情况下：只要有空间就可以插入新的行，但是如果我们对行数据的更新需要更多的空间，以致大于当前页的可用空间，行就被移到新的页中，并且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置；
3．如果堆中有非聚集索引，那么尽管插入和更新操作在堆中不会发生页分裂，但是在非聚集索引上仍然产生页分裂。
无论有无索引，大约一半的数据将保留在老页面，而另一半将放入新页面，并且新页面可能被分配到任何可用的页。所以，频繁页分裂，后果很严重，将使物理表产生大量数据碎片，导致直接造成I/O效率的急剧下降，最后，不得不停止SQLS的运行并重建索引。
二．填充因子
然而在“混沌之初”，就可以在一定程度上避免不愉快出现，在创建索引时，可以为这个索引指定一个填充因子，以便在索引的每个叶级页面上保留一定百分比的空间，将来数据可以进行扩充和减少页分裂。填充因子是从0到100的百分比数值，设为100时表示将数据页填满，只有当不会对数据进行更改时(例如只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间。
填充因子只在创建索引时执行，索引创建以后，当表中进行数据的添加、删除或更新时，是不会保持填充因子的，如果想在数据页上保持额外的空间，则有悖于使用填充因子的本意，因为随着数据的输入，SQLS必须在每个页上进行页拆分，以保持填充因子指定的空闲空间。因此，只有在表中的数据进行了较大的变动，才可以填充数据页的空闲空间。这时，可以从容的重建索引，重新指定填充因子，重新分布数据。
反之，填充因子指定不当，就会降低数据库的读取性能，其降低量与填充因子设置值成反比。例如，当填充因子的值为50时，数据库的读取性能会降低两倍。所以，只有在表中根据现有数据创建新索引，并且可以预见将来会对这些数据进行哪些更改时，设置填充因子才有意义。
三．两道数学题
假定数据库设计没有问题，那么是否像上篇分析的那样，当你建立了众多的索引，在查询工作中SQLS就只能按照“最高指示”用索引处理每一个提交的查询呢？答案是否定的。
实际上，SQLS几乎完全是“自主”的决定是否使用索引或使用哪一个索引。
这是怎么回事呢？
让我们先来算一道题：如果某表的一条记录在磁盘上占用1000字节(1K)的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引大小只有10字节(0.01K)。上篇说过，SQLS的最小空间分配单元是“页（Page）”，一个页面在磁盘上占用8K空间，所以一页只能存储8条“记录”，但可以存储800条“索引”。现在我们要从一个有8000条记录的表中检索符合某个条件的记录(有Where子句)，如果没有索引的话，我们需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样I/O访问量肯定要少得多。
然而有时用索引比不用索引还快。
同上，如果要无条件检索全部记录(不用Where子句)，不用索引的话，需要访问8000条×1000字节/8K字节=1000个页面；而使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索全部数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。
SQLS内部有一套完整的数据索引优化技术，在上述情况下，SQLS会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQLS是怎么知道什么时候用索引，什么时候不用索引的呢？因为SQLS除了维护数据信息外，还维护着数据统计信息。
四．统计信息
打开企业管理器，单击“Database”节点，右击Northwind数据库→单击“属性”→选择“Options”选项卡，观察“Settings”下的各项复选项，你发现了什么？
从Settings中我们可以看到，在数据库中，SQLS将默认的自动创建和更新统计信息，这些统计信息包括数据密度和分布信息，正是它们帮助SQLS确定最佳的查询策略：建立查询计划和是否使用索引以及使用什么样的索引。
在创建索引时，SQLS会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。查询优化器使用这些统计信息估算使用该索引进行查询的成本(Cost)，并在此基础上判断该索引对某个特定查询是否有用。
随着表中的数据发生变化，SQLS自动定期更新这些统计信息。采样是在各个数据页上随机进行。从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。统计信息更新的频率取决于字段或索引中的数据量以及数据更改量。比如，对于有一万条记录的表，当1000个索引键值发生改变时，该表的统计信息便可能需要更新，因为1000 个值在该表中占了10%，这是一个很大的比例。而对于有1千万条记录的表来说，1000个索引值发生更改的意义则可以忽略不计，因此统计信息就不会自动更新。
五．索引的人工维护
上面讲到,某些不合适的索引将影响到SQLS的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。
随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大量I/O的时候，重建非聚聚集索引可以维护I/O的效率。重建索引实质上是重新组织B树。需要重建索引的情况有：
1．数据和使用模式大幅度变化；
2．排序的顺序发生改变；
3．要进行大量插入操作或已经完成；
4．使用I/O查询的磁盘读次数比预料的要多；
5．由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算；
6．dbcc检查出索引有问题。
六．索引的使用原则
接近尾声的时候，让我们再从另一个角度认识索引的两个重要属性----惟一性索引和复合性索引。
惟一性索引保证在索引列中的全部数据是惟一的，不会包含冗余数据。如果表中已经有一个主键约束或者惟一性约束，那么当创建表或者修改表时，SQLS自动创建一个惟一性索引。但出于必须保证惟一性，那么应该创建主键约束或者惟一性键约束，而不是创建一个惟一性索引。
复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，原则上，应该首先定义最惟一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列。
（一）SQLS如何访问没有建立索引的数据表
Heap译成汉语叫做“堆”，其本义暗含杂乱无章、无序的意思，前面提到数据值被写进数据页时，由于每一行记录之间并没有特定的排列顺序，所以行与行的顺序就是随机无序的，当然表中的数据页也就是无序的了，而表中所有数据页就形成了“堆”。可以说，一张没有索引的数据表，就像一个只有书柜而没有索引卡片柜的图书馆，书库里面塞满了一堆乱七八糟的图书。当读者对管理员提交查询请求后，管理员就一头钻进书库，对照查找内容从头开始一架一柜的逐本查找。运气好的话，在第一个书架的第一本书就 找到了，运气不好的话，要到最后一个书架的最后一本书才找到。
SQLS在接到查询请求时，首先会分析sysindexes表中一个叫做索引标志符(INDID: Index ID)的字段的值，如果该值为0，表示这是一张数据表而不是索引表，SQLS就会使用sysindexes表的另一个字段——也就是在前面提到过的FirstIAM值中找到该表的IAM页链，也就是所有数据页集合。
这就是对一个没有建立索引的数据表进行数据查找的方式，是不是很没效率？对于没有索引的表，对于一“堆”这样的记录，SQLS也只能这样做，而且更没劲的是，即使在第一行就找到了被查询的记录，SQLS仍然要从头到尾的将表扫描一次。这种查询称为“遍历”，又叫“表扫描”。
可见没有建立索引的数据表照样可以运行，不过这种方法对于小规模的表来说没有什么太大的问题，但要查询海量的数据效率就太低了。
（二）SQLS如何访问建立了非聚集索引的数据表
如前所述，非聚集索引可以建多个，具有B树结构，其叶级节点不包含数据页，只包含索引行。假定一个表中只有非聚集索引，则每个索引行包含了非聚集索引键值以及行定位符（ROW ID,RID），他们指向具有该键值的数据行，每一个RID由文件ID、页编号和在页中行的编号组成。
当INDID的值在2至250之间时，意味着表中存在非聚集索引页。此时，SQLS调用ROOT字段的值指向非聚集索引B树的ROOT，在其中查找与被查询最相近的值，根据这个值找到在非叶级节点中的页号，然后顺藤摸瓜，在叶级节点相应的页面中找到该值的RID，最后根据这个RID在Heap中定位所在的页和行并返回到查询端。
例如：假定在Lastname上建立了非聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
①SQLS查询INDID值为2；
②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第61页；
③仅在叶级页面的第61页的Martin下搜寻Ota的RID，其RID显示为N∶706∶4，表示Lastname字段中名 为Ota的记录位于堆的第706页的第4行，N表示文件的ID值，与数据无关；
④根据上述信息，SQLS立刻在堆的第706页第4行将该记录“揪”出来并显示于前台（客户端）。视表的数据量大小，整个查询过程费时从百分之几毫秒到数毫秒不等。
在谈到索引基本概念的时候，我们就提到了这种方式：图书馆的前台有很多索引卡片柜，里面分了若干的类别，诸如按照书名笔画或拼音顺序、作者笔画或拼音顺序等，但有两点不同之处：
① 索引卡片上记录了每本书摆放的具体位置——位于某柜某架的第几本——而不是“特殊编号”；
② 书脊上并没有那个“特殊编号”。管理员在索引柜中查到所需图书的具体位置（RID）后，根据RID直接在书库中的具体位置将书提出来。
显然，这种查询方式效率很高，但资源占用极大，因为书库中书的位置随时在发生变化，必然要求管理员花费额外的精力和时间随时做好索引更新。
（三）SQLS如何访问建立聚集索引的数据表
在聚集索引中，数据所在的数据页是叶级，索引数据所在的索引页是非叶级。
查询原理和上述对非聚集索引的查询相似，但由于记录是按照聚集索引中索引键值进行排序，换句话说，聚集索引的索引键值也就是具体的数据页。
这就好比书库中的书就是按照书名的拼音在排序，而且也只按照这一种排序方式建立相应的索引卡片，于是查询起来要比上述只建立非聚集索引的方式要简单得多。仍以上面的查询为例：
假定在Lastname字段上建立了聚集索引，则执行Select * From Member Where Lastname=’Ota’时，查询过程是：
①SQLS查询INDID值为1，这是在系统中只建立了聚集索引的标志；
②立即从根出发，在非叶级节点中定位最接近Ota的值“Martin”，并查到其位于叶级页面的第120页；
③在位于叶级页面第120页的Martin下搜寻到Ota条目，而这一条目已是数据记录本身；
④将该记录返回客户端。
这一次的效率比第二种方法更高，以致于看起来更美，然而它最大的优点也恰好是它最大的缺点——由于同一张表中同时只能按照一种顺序排列，所以在任何一种数据表中的聚集索引只能建立一个；并且建立聚集索引需要至少相当于源表120%的附加空间，以存放源表的副本和索引中间页。
难道鱼和熊掌就不能兼顾了吗？办法是有的。
（四）SQLS如何访问既有聚集索引、又有非聚集索引的数据表
如果我们在建立非聚集索引之前先建立了聚集索引的话，那么非聚集索引就可以使用聚集索引的关键字进行检索。就像在图书馆中，前台卡片柜中可以有不同类别的图书索引卡，然而每张卡片上都载明了那个特殊编号——并不是书籍存放的具体位置。这样在最大程度上既照顾了数据检索的快捷性，又使索引的日常维护变得更加可行，这是最为科学的检索方法。
也就是说，在只建立了非聚集索引的情况下，每个叶级节点指明了记录的行定位符（RID）；而在既有聚集索引又有非聚集索引的情况下，每个叶级节点所指向的是该聚集索引的索引键值，即数据记录本身。
假设聚集索引建立在Lastname上，而非聚集索引建立在Firstname上，当执行Select * From Member Where Firstname=’Mike’时，查询过程是：
①SQLS查询INDID值为2；
②立即从根出发，在Firstname的非聚集索引的非叶级节点中定位最接近Mike的值“Jose”条目；
③从Jose条目下的叶级页面中查到Mike逻辑位置——不是RID而是聚集索引的指针；
④根据这一指针所指示位置，直接进入位于Lastname的聚集索引中的叶级页面中到达Mike数据记录本身；
⑤将该记录返回客户端。
这就完全和我们在“索引的基本概念”中讲到的现实场景完全一样了，当数据发生更新的时候，SQLS只负责对聚集索引的键值加以维护，而不必考虑非聚集索引。只要我们在ID类的字段上建立聚集索引，而在其它经常需要查询的字段上建立非聚集索引，通过这种科学的、有针对性的在一张表上分别建立聚集索引和非聚集索引的方法，我们既享受了索引带来的灵活与快捷，又相对避免了维护索引所导致的大量的额外资源消耗。
索引的优点和不足
索引有一些先天不足
1、系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；
2、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
当然建立索引的优点也是显而易见的，在海量数据的情况下，如果合理的建立了索引，则会大大加强SQLS执行查询、对结果进行排序、分组的操作效率。
实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引要花费更多的系统时间。
在如下字段建立索引应该是不恰当的：
1、很少或从不引用的字段；
2、逻辑型的字段，如男或女(是或否)等。
综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，必须要有统筹的规划，一定要在“加快查询速度”与“降低修改速度”之间做好平衡。有得必有失，此消则彼长，这是考验一个DBA是否优秀的很重要的指标
建立索引时一定要在“加快查询速度”与“降低修改速度”之间做好平衡，有得必有失，此消则彼长。那么，SQLS维护索引时究竟怎样消耗资源？应该从哪些方面对索引进行管理与优化？以下从六个方面来回答这些问题。
一．页分裂
微软MOC教导我们：当一个数据页达到了8K容量，如果此时发生插入或更新数据的操作，将导致页的分裂(又名页拆分)：
1．有聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定；
2．只有堆的情况下：只要有空间就可以插入新的行，但是如果我们对行数据的更新需要更多的空间，以致大于当前页的可用空间，行就被移到新的页中，并且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置；
3．如果堆中有非聚集索引，那么尽管插入和更新操作在堆中不会发生页分裂，但是在非聚集索引上仍然产生页分裂。
无论有无索引，大约一半的数据将保留在老页面，而另一半将放入新页面，并且新页面可能被分配到任何可用的页。所以，频繁页分裂，后果很严重，将使物理表产生大量数据碎片，导致直接造成I/O效率的急剧下降，最后，不得不停止SQLS的运行并重建索引。
二．填充因子
然而在“混沌之初”，就可以在一定程度上避免不愉快出现，在创建索引时，可以为这个索引指定一个填充因子，以便在索引的每个叶级页面上保留一定百分比的空间，将来数据可以进行扩充和减少页分裂。填充因子是从0到100的百分比数值，设为100时表示将数据页填满，只有当不会对数据进行更改时(例如只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间。
填充因子只在创建索引时执行，索引创建以后，当表中进行数据的添加、删除或更新时，是不会保持填充因子的，如果想在数据页上保持额外的空间，则有悖于使用填充因子的本意，因为随着数据的输入，SQLS必须在每个页上进行页拆分，以保持填充因子指定的空闲空间。因此，只有在表中的数据进行了较大的变动，才可以填充数据页的空闲空间。这时，可以从容的重建索引，重新指定填充因子，重新分布数据。
反之，填充因子指定不当，就会降低数据库的读取性能，其降低量与填充因子设置值成反比。例如，当填充因子的值为50时，数据库的读取性能会降低两倍。所以，只有在表中根据现有数据创建新索引，并且可以预见将来会对这些数据进行哪些更改时，设置填充因子才有意义。
三．两道数学题
假定数据库设计没有问题，那么是否像上篇分析的那样，当你建立了众多的索引，在查询工作中SQLS就只能按照“最高指示”用索引处理每一个提交的查询呢？答案是否定的。
实际上，SQLS几乎完全是“自主”的决定是否使用索引或使用哪一个索引。
这是怎么回事呢？
让我们先来算一道题：如果某表的一条记录在磁盘上占用1000字节(1K)的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引大小只有10字节(0.01K)。上篇说过，SQLS的最小空间分配单元是“页（Page）”，一个页面在磁盘上占用8K空间，所以一页只能存储8条“记录”，但可以存储800条“索引”。现在我们要从一个有8000条记录的表中检索符合某个条件的记录(有Where子句)，如果没有索引的话，我们需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样I/O访问量肯定要少得多。
然而有时用索引比不用索引还快。
同上，如果要无条件检索全部记录(不用Where子句)，不用索引的话，需要访问8000条×1000字节/8K字节=1000个页面；而使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索全部数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。
SQLS内部有一套完整的数据索引优化技术，在上述情况下，SQLS会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQLS是怎么知道什么时候用索引，什么时候不用索引的呢？因为SQLS除了维护数据信息外，还维护着数据统计信息。
四．统计信息
打开企业管理器，单击“Database”节点，右击Northwind数据库→单击“属性”→选择“Options”选项卡，观察“Settings”下的各项复选项，你发现了什么？
从Settings中我们可以看到，在数据库中，SQLS将默认的自动创建和更新统计信息，这些统计信息包括数据密度和分布信息，正是它们帮助SQLS确定最佳的查询策略：建立查询计划和是否使用索引以及使用什么样的索引。
在创建索引时，SQLS会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。查询优化器使用这些统计信息估算使用该索引进行查询的成本(Cost)，并在此基础上判断该索引对某个特定查询是否有用。
随着表中的数据发生变化，SQLS自动定期更新这些统计信息。采样是在各个数据页上随机进行。从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。统计信息更新的频率取决于字段或索引中的数据量以及数据更改量。比如，对于有一万条记录的表，当1000个索引键值发生改变时，该表的统计信息便可能需要更新，因为1000 个值在该表中占了10%，这是一个很大的比例。而对于有1千万条记录的表来说，1000个索引值发生更改的意义则可以忽略不计，因此统计信息就不会自动更新。
五．索引的人工维护
上面讲到,某些不合适的索引将影响到SQLS的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。
随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大量I/O的时候，重建非聚聚集索引可以维护I/O的效率。重建索引实质上是重新组织B树。需要重建索引的情况有：
1．数据和使用模式大幅度变化；
2．排序的顺序发生改变；
3．要进行大量插入操作或已经完成；
4．使用I/O查询的磁盘读次数比预料的要多；
5．由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算；
6．dbcc检查出索引有问题。
六．索引的使用原则
接近尾声的时候，让我们再从另一个角度认识索引的两个重要属性----惟一性索引和复合性索引。
惟一性索引保证在索引列中的全部数据是惟一的，不会包含冗余数据。如果表中已经有一个主键约束或者惟一性约束，那么当创建表或者修改表时，SQLS自动创建一个惟一性索引。但出于必须保证惟一性，那么应该创建主键约束或者惟一性键约束，而不是创建一个惟一性索引。
复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，原则上，应该首先定义最惟一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列。
dawugui 2012-03-24
- 打赏
- 举报
1.合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
(1)在下面两条select语句中:
select * from table1 where field1<=10000 and field1>=0;
select * from table1 where field1>=0 and field1<=10000;
如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。
第一个原则：在where子句中应把最具限制性的条件放在最前面。
(2)在下面的select语句中:
select * from tab where a=… and b=… and c=…;
若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。
第二个原则：where子句中字段的顺序应和索引中字段顺序一致。
以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。
(3) select field3,field4 from tb where field1='sdf' 快
select * from tb where field1='sdf' 慢，
因为后者在索引扫描后要多一步ROWID表访问。
(4) select field3,field4 from tb where field1>='sdf' 快
select field3,field4 from tb where field1>'sdf' 慢
因为前者可以迅速定位索引。
(5) select field3,field4 from tb where field2 like 'R%' 快
select field3,field4 from tb where field2 like '%R' 慢，
因为后者不使用索引。
(6) 使用函数如：
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。
(7) 空值不在索引中存储，所以
select field3,field4 from tb where field2 is[not] null不使用索引。
(8) 不等式如
select field3,field4 from tb where field2!='TOM'不使用索引。
相似地，
select field3,field4 from tb where field2 not in('M','P')不使用索引。
(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。
(10) MAX，MIN等函数，如
Select max(field2) from tb使用索引。所以，如果需要对字段取max，min，sum等，应该加索引。
一次只使用一个聚集函数，如：
select “min”=min(field1), “max”=max(field1) from tb
不如：select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb)
(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。
(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。
(13) 对于多列索引，order by的顺序必须和索引的字段顺序一致。
(14) 如果order by的字段组成一个簇索引，那么无须做order by。记录的排列顺序是与簇索引一致的。
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
(1)在下面两条select语句中:
select * from table1 where field1<=10000 and field1>=0;
select * from table1 where field1>=0 and field1<=10000;
如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。
第一个原则：在where子句中应把最具限制性的条件放在最前面。
(2)在下面的select语句中:
select * from tab where a=… and b=… and c=…;
若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。
第二个原则：where子句中字段的顺序应和索引中字段顺序一致。
以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。
(3) select field3,field4 from tb where field1='sdf' 快
select * from tb where field1='sdf' 慢，
因为后者在索引扫描后要多一步ROWID表访问。
(4) select field3,field4 from tb where field1>='sdf' 快
select field3,field4 from tb where field1>'sdf' 慢
因为前者可以迅速定位索引。
(5) select field3,field4 from tb where field2 like 'R%' 快
select field3,field4 from tb where field2 like '%R' 慢，
因为后者不使用索引。
(6) 使用函数如：
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。
(7) 空值不在索引中存储，所以
select field3,field4 from tb where field2 is[not] null不使用索引。
(8) 不等式如
select field3,field4 from tb where field2!='TOM'不使用索引。
相似地，
select field3,field4 from tb where field2 not in('M','P')不使用索引。
(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。
(10) MAX，MIN等函数，如
Select max(field2) from tb使用索引。所以，如果需要对字段取max，min，sum等，应该加索引。
一次只使用一个聚集函数，如：
select “min”=min(field1), “max”=max(field1) from tb
不如：select “min”=(select min(field1) from tb) , “max”=(select max(field1) from tb)
(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。
(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。
(13) 对于多列索引，order by的顺序必须和索引的字段顺序一致。
(14) 如果order by的字段组成一个簇索引，那么无须做order by。记录的排列顺序是与簇索引一致的。
开着拖拉机泡妞 2012-03-24
- 打赏
- 举报
使用聚集索引和非聚集索引的区别
使用聚集索引
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。避免每次查询该列时都进行排序，从而节省成本。
注意事项
定义聚集索引键时使用的列越少越好。
1、包含大量非重复值的列。
2、使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。
3、被连续访问的列。
4、返回大型结果集的查询。
5、经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
6、OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。
聚集索引不适用于：
1、频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。
2、宽键 。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。
使用非聚集索引
非聚集索引与课本中的目录类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。
多个非聚集索引
有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。对于非聚集索引也是如此。可以为在表中查找数据时常用的每个列创建一个非聚集索引。
注意事项
在创建非聚集索引之前，应先了解您的数据是如何被访问的。可考虑将非聚集索引用于：
? 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。
? 不返回大型结果集的查询。
? 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。
? 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。
? 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。
索引都是一种排序，只是聚集索引的排序和物理表中的数据排序相同，一致的；而非聚集索引的排序和物理表数据的排序不同。
当我们在更新统计信息的时候，对于那些聚集索引列不但要更新索引页还要同时对数据物理表数据重新排序；而对非聚集索引列则只需要更新索引页
gzlaspnet 2012-03-24
- 打赏
- 举报
网上我也看了很多关于建立聚集索引和非聚集索引的文章，但还是没搞透彻。能对我的具体问题加答吗？
