• 了解筛选索引


    开始

    筛选索引是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次:

                                                                图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

                                                               图2.

    图2. 从表table_a和表table_b的实际执行计划统计信息中,看TotalSubtreeCost(所有子操作的预计开销合计)数据,使用筛选索引的table_b(TotalSubtreeCost=0.05036455)明显低于于使用全表非聚集索引的table_a(TotalSubtreeCost=0.02331454)。也就是使用筛选索引的成本,是使用全表非聚集索引的成本的1/2。

                                                               图3.

    图3.从IO信息收集结果看,针对表table_a进行了35次的逻辑读取,而表table_b 只进行了33次逻辑读取。也就说明使用筛选索引在IO逻辑读取次数少于全表非聚集索引在IO的逻辑读取次数。

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

                                                               图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

                                                               图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

                                                               图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

                                                               图7.

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

    从这里可以判验证筛选索引减少了索引维护开销。

    小结

    上面的例子,说明了使用筛选索引的一些优点。在实际生产环境中,需要检查和分析经常用到的存储过程或程序代码中的SQL语句,是否有必要创建筛选索引来提升性能。虽然筛选索引,在某些情况下会提升查询性能,节省存储空间,但必须要小心使用,不能轻易删除现有的全表索引,使用筛选索引。

    参考资料

    SQL University: Advanced Indexing – Filtered Indexes:

    http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/

    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

  • 相关阅读:
    win10 uwp 如何判断一个对象被移除
    win10 uwp 如何判断一个对象被移除
    上传代码 CodePlex
    上传代码 CodePlex
    如何使用 Q#
    让 AE 输出 MPEG
    让 AE 输出 MPEG
    解决 vs 出现Error MC3000 给定编码中的字符无效
    解决 vs 出现Error MC3000 给定编码中的字符无效
    PHP date_date_set() 函数
  • 原文地址:https://www.cnblogs.com/wghao/p/3070032.html
Copyright © 2020-2023  润新知