sqlserver索引优化,索引如何优化

SQLServer求优化

我一不太会优化，提供你一些优化的方法吧

操作符优化

in 操作符

用in写出来的sql的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用in的sql性能总是比较低的，从oracle执行的步骤来分析用in的sql与不用in的sql有以下区别：

oracle试图将其转换成多个表的连接，如果转换不成功则先执行in里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用in的sql至少多了一个转换的过程。一般的sql都可以转换成功，但对于含有分组统计等方面的sql就不能转换了。

推荐方案：在业务密集的sql当中尽量不采用in操作符。

not in操作符

此操作是强列推荐不使用的，因为它不能应用表的索引。

推荐方案：用not exists 或（外连接+判断为空）方案代替

操作符（不等于）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如

a0 改为 a0 or a0

a’’ 改为 a’’

is null 或is not null操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为b树索引是不索引空值的。

推荐方案：用其它相同功能的操作运算代替，如

a is not null 改为 a0 或a’’等。

不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

及 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段a，30万记录的a=0，30万记录的a=1，39万记录的a=2，1万记录的a=3。那么执行a2与a=3的效果就有很大的区别了，因为a2时oracle会先找出为2的记录索引再进行比较，而a=3时oracle则直接找到=3的记录索引。

like操作符

like操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如like ‘%5400%’ 这种查询不会引用索引，而like ‘x5400%’则会引用范围索引。一个实际例子：用yw_yhjbqk表中营业编号后面的户标识号可来查询营业编号 yy_bh like ‘%5400%’ 这个条件会产生全表扫描，如果改成yy_bh like ’x5400%’ or yy_bh like ’b5400%’ 则会利用yy_bh的索引进行两个范围的查询，性能肯定大大提高。

union操作符

union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表union。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个sql在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用union all操作符替代union，因为union all操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

sql语句索引的利用

对条件字段的一些优化

采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)， 优化处理：

sk_rq=trunc(sysdate) and sk_rq

进行了显式或隐式的运算的字段不能进行索引，如：

ss_df+2050，优化处理：ss_df30

‘x’||hbs_bh’x5400021452’，优化处理：hbs_bh’5400021542’

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如：

ys_dfcx_df，无法进行优化

qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

应用oracle的hint（提示）处理

提示处理是在oracle产生的sql分析执行路径不满意的情况下要用到的。它可以对sql进行以下方面的提示

目标方面的提示：

cost（按成本优化）

rule（按规则优化）

choose（缺省）（oracle自动选择成本或规则进行优化）

all_rows（所有的行尽快返回）

first_rows（第一行数据尽快返回）

执行方法的提示：

use_nl（使用nested loops方式联合）

use_merge（使用merge join方式联合）

use_hash（使用hash join方式联合）

索引提示：

index（table index）（使用提示的表索引进行查询）

其它高级提示（如并行处理等等）

oracle的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给oracle执行的一个建议，有时如果出于成本方面的考虑oracle也可能不会按提示进行。根据实践应用，一般不建议开发人员应用oracle提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，oracle在sql执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。

相关问答

Q1: 如何做SqlServer 数据查询优化！

一、建立索引

二、建立存储过程

三、只查询您所需要的数据，不要把所有数据都查询出来，防止数据冗余。

四、对于大量及海量数据一般还要建立分区

Q2: 如何利用索引提高SQLServer数据处理的效率

在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。

所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。本文就SQL Server索引的性能问题进行了一些分析和实践。

一、聚簇索引(clustered indexes)的使用

聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是：

1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。

2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、、=、、=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。

4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。

5、选择聚簇索引应基于where子句和连接操作的类型。

聚簇索引的侯选列是：

1、主键列,该列在where子句中使用并且插入是随机的。

2、按范围存取的列，如pri_order 100 and pri_order 200。

3、在group by或order by中使用的列。

4、不经常修改的列。

5、在连接操作中使用的列。

二、非聚簇索引(nonclustered indexes)的使用

SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：

1、索引需要使用多少空间。

2、合适的列是否稳定。

3、索引键是如何选择的，扫描效果是否更佳。

4、是否有许多重复值。

对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况：

1、某列常用于集合函数(如Sum,....)。

2、某列常用于join,order by,group by。

3、查寻出的数据不超过表中数据量的20%。

三、覆盖索引(covering indexes)的使用

覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。

但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

四、索引的选择技术

p_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBM PII350(内存64M),服务器是DEC Alpha1000A(内存128M),数据库为SYBASE11.0.3。

1、 select count(*) from p_detail where

op_date’19990101’ and op_date’

19991231’ and pri_surplus1300

2、 select count(*),sum(pri_surplus1) from p_detail

where op_date’19990101’ and

pay_month between‘199908’ and’199912’

不建任何索引查询1 1分15秒

查询2 1分7秒

在op_date上建非聚簇索引查询1 57秒

查询2 57秒

在op_date上建聚簇索引查询1 1秒

查询2 52秒

在pay_month、op_date、pri_surplus1上建索引查询1 34秒

查询2 1秒

在op_date、pay_month、pri_surplus1上建索引查询1 1秒

查询2 1秒

从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是：

(1)主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。

(2)有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。

(3)经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

(4)如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

(5)在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。

(6)在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

五、索引的维护

上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括：

1、重建索引

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：

(1)数据和使用模式大幅度变化。

(2)排序的顺序发生改变。

(3)要进行大量插入操作或已经完成。

(4)使用大块I/O的查询的磁盘读次数比预料的要多。

(5)由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。

(6)dbcc检查出索引有问题。

当重建聚簇索引时，这张表的所有非聚簇索引将被重建。

2、索引统计信息的更新

当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：

(1)数据行的插入和删除修改了数据的分布。

(2)对用truncate table删除数据的表上增加数据行。

(3)修改索引列的值。

六、结束语

实践表明，不恰当的索引不但于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的：

1、在查询中很少或从不引用的列不会受益于索引，因为索引很少或从来不必搜索基于这些列的行。

2、只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。

另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。