SQL Server打探筛选索引

开始

淘索引是SQL Server 2008饱受之一模一样栽新职能,它是针对表中的一部分实行开展索引。

中心语法:

create nonclustered index index_name on <object> (columns) where <filter_predicate>

每当一些一定的应用环境下,筛选索引与俗的全表非聚集索引相比,具有以下优点。

  • 提高了询问性能与计划质量

  • 减了目录存储开销

  • 抽了目录维护开销

 

对接下去,我为例子来说明及时三方面的优点。

提高了查询性能和计划质量

每当数据库TestDB上开创两独说明(table_a &
table_b),而且每一个申明还起同一之记录行(各100W行记录)。可以参见下的测试脚论SQL:

use TestDB

go

if object_id(‘table_a’) is not null drop table table_a

if object_id(‘table_b’) is not null drop table table_b

go

create table table_a (id int identity,col1 int,col2 nvarchar(128),constraint pk_table_a primary key(id))

create table table_b (id int identity,col1 int,col2 nvarchar(128),constraint pk_table_b primary key(id))

go

insert into table_a(col1,col2)

    select top(1000000) a.object_id as col1,b.name as col2

        from sys.all_objects a,

            sys.all_columns b

go

insert into table_b(col1,col2)

    select col1,col2 from table_a

 

go

在Microsoft SQL Server Management Studio
新建一个查询,并施行方的SQL语句。

  1. 尚无索引情况:

使我如果翻看,条件相当”col1 between -200 and 10″的id &
col1笔录,那么对诺SQL语句是:

select id,col1 from table_a a where a.col1 between -200 and 10

为了能跟到执行计划情况以及IO信息,我此装了”set statistics
profile,io on”:

use TestDB

go

set statistics profile,io on

select id,col1 from table_a a where a.col1 between -200 and 10

set statistics profile,io off

go

实践结果回到17540执记录,在执行计划经过,采用聚集索引围观(pk_table_a),IO逻辑读取4311不好:

SQL Server 1

SQL Server 2

                                                            图1.

  1. 筛选索引 Vs. 全表非聚集索引:

为提升查询性能,通常会于字段col1臻开创一个非聚集索引,如(ix_table_a_col1):

create nonclustered index ix_table_a_col1 on dbo.table_a(col1)

而且,为了给罗索引和全表非聚集索引进行比较,我在表table_b上创设了一个罗索引,如(ix_table_b_col1_Filtered):

create nonclustered index ix_table_b_col1_Filtered on dbo.table_b(col1) where col1>=-200

通下,要查询两单表中”col1 between -200 and 10″的id & col1记录:

use TestDB

go

set statistics profile,io on

select id,col1 from table_a a where a.col1 between -200 and 10

select id,col1 from table_b a where a.col1 between -200 and 10

set statistics profile,io off

go

 

SQL Server 3

                                                           图2.

图2.
从表table_a和表table_b的骨子里施行计划统计信息中,看TotalSubtreeCost(所有子操作的预测开销合计)数据,使用筛选索引的table_b(TotalSubtreeCost=0.05036455)明显低于于以全表非聚集索引的table_a(TotalSubtreeCost=0.02331454)。也尽管是采取筛选索引的老本,是使用全表非聚集索引的血本的1/2。

 

SQL Server 4

                                                           图3.

 

祈求3.由IO信息征集结果看,针对表table_a进行了35蹩脚的逻辑读取,而表table_b
只进行了33不成逻辑读取。也即说明以筛选索引在IO逻辑读取次数少全表非聚集索引在IO的逻辑读取次数。

 

下我还从客户端统计信息来分析,使用筛选索引和全表非聚集索引的施行时别:

SQL Server 5

                                                           图4.

贪图4.足以看来,使用筛选索引,在客户端处理时、总执行时间、服务器等时之平均值,都较全表非聚集索引的没有。

 

减少了索引存储开销

得采用下的SQL语句来查看筛选索引(ix_table_b_col1_Filtered)和全表非聚集索引(ix_table_a_col1)的积存大小:

use TestDB

go

select object_name(b.object_id) as TableName,

        b.name as IndexName ,

sum(a.used_page_count) * 8 as IndexSizeKB

    from sys.dm_db_partition_stats as a

join sys.indexes as b on a.object_id = b.object_id

and a.index_id = b.index_id

    where b.object_id in ( object_id(‘table_a’), object_id(‘table_b’) )

        and b.name in(‘ix_table_a_col1′,’ix_table_b_col1_Filtered’)

    group by b.object_id,

            b.name

    order by b.name

SQL Server 6

                                                           图5.

图5,可以看出筛选索引使用的囤积空间明显低于全表非聚集索引。

 

 

 

减少了索引维护开销

只在数量操作语言 (DML)
语句对索引中之数有震慑时,才对索引进行保护。与全表非聚集索引相比,筛选索引减少了目录维护开销,因为她更小而且就在对索引中的多寡来潜移默化时才开展保障。

推个例证,先翻表table_a和表table_b中,ID=10的数据:

use TestDB

go

select * from dbo.table_a where id=10

select * from dbo.table_b where id=10

SQL Server 7

                                                           图6.

如今一经者的col1= -1068265529 改化col1=-123456
,看更新过程对个别表索引(ix_table_a_col1 &
ix_table_b_col1_Filtered)产生的熏陶事态 :

use TestDB

go

checkpoint

go

update table_a

set col1=-123456 — -1068265529

where id=10

 

update table_b

set col1=-123456 — -1068265529

where id=10

 

go

select Operation ,

Context ,

AllocUnitName ,

[Transaction Name] ,

Description

    from fn_dblog(null, null) as a

go

SQL Server 8

                                                           图7.

贪图7.
可以看出,前面的换代语句,针对被表table_a,有护到索引ix_table_a_col1,而在表table_b,没找到维护ix_table_b_col1_Filtered的记录。

从此间可以判验证筛选索引减少了目录维护开销。

 

小结

面的例证,说明了使用筛选索引的局部长。在实际生产条件受到,需要检讨及分析时用的囤过程或者程序代码中之SQL语句,是否出必要创立筛选索引来提升性。虽然筛选索引,在好几情况下会提升查询性能,节省存储空间,但不能不要小心使用,不可知轻易去现有的全表索引,使用筛选索引。

 

 

参考资料

SQL University: Advanced Indexing – Filtered Indexes:

SQL University: Advanced Indexing – Filtered Indexes

The Joys of Filtered Indexes:

http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-joys-of-filtered-indexes.aspx

Filtered Indexes: What You Need To Know :

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/

创建筛选索引:

http://msdn.microsoft.com/zh-cn/library/cc280372(v=sql.100).aspx

CREATE INDEX:

http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

切莫聚集索引设计指南:

http://msdn.microsoft.com/zh-cn/library/ms179325(v=sql.100).aspx

 

 

 

相关文章