概述

 

索引设计是数据库设计中比较重要的一个环节,对数据库的性能起着至关重要的作用,但是索引的设计却又不是那么容易的事情,性能也不是那么轻易就获取到的,很多的技术人员因为不恰当的创建索引,最后使得其效果适得其反,可以说“成也索引,败也索引”。在我经历过的,众多的数据库性能问题案例中,80%
系统都存在索引不合理的问题.

 

 

数据库引擎是高度优化的闭环系统,基于执行计划的反馈,查询优化器在一定程度上自动优化现有的执行计划。查询优化的核心是索引优化,数据库引擎通过计数器统计关于索引操作的数据,统计的信息包括:使用次数、物理存储、底层操作的计数,以及缺失索引等,这些统计数据存储在内存中,是数据库引擎执行情况的真实反馈,高度概括了索引的执行情况,有意识地利用索引的统计信息,有针对性地优化现有的业务逻辑代码,调整查询的执行计划,能够提高数据库的查询性能。

为什么需要索引

 

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性,访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似,即它们都包含一个数据段和一个指针,指针指向下一个节点(数据块)的内存地址,而且它们都不需要连续存储(即逻辑上相邻的数据块在物理上可以相隔很远)。

鉴于很多记录只能做到按一个字段排序,所以要查询某个未经排序的字段,就需要使用线性查找,即要访问N/2个数据块,其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段(也就是说,不包含唯一值),那么就要搜索整个表空间,即要访问全部N个数据块。

然而,对于经过排序的字段,可以使用二分查找,因此只要访问log2
N个数据块。同样,对于已经排过序的非键字段,只要找到更大的值,也就不用再搜索表中的其他数据块了。这样一来,性能就会有实质性的提升。

举个例子,我们使用微软的示例数据库,
对于下面的查询:

select SalesOrderDetailID
FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID=61026

1.SalesOrderDetailID
列没有索引

查询会进行全表扫描。逻辑读如下图:

 

图片 1

表的数据页数如下所示,两者是几乎相等的。说明他对每个数据页都进行需要读取一次。

图片 2

 

2.SalesOrderDetailID 建立索引,逻辑读取仅2次。

因为log2 1246 是等于10.28. 最大的逻辑读取次数是11.

 图片 3

 

一,统计索引的使用次数

利用EXPERT找到缺失索引

 

既然索引对我们如此重要,那么我们如何去快速,方便的找到需要的索引呢。利用体检专家可以轻松做到。

在用户成功提交查询语句时,执行计划中每一个单独的索引操作(Seek,Scan,Lookup或Update)都会被统计到sys.dm_db_index_usage_stats
中,例如,user_updates 计数器统计索引执行Insert,Update或Delete操作的次数,查找计数器(user_seeks,
user_scans,
user_lookups
)统计在索引上执行的seek,scan和lookup操作的次数,如果查找计数器远远小于user_updates 计数器,这说明基础表会执行大量的更新操作,维护索引更新的开销比较大,数据库引擎利用索引提升查询性能的空间有限。 

1.通过执行计划

在执行计划选项卡,对于查询语句,如果存在缺失索引,体检专家会在缺失索引列上打钩,如图所示。

同时列出SQL了 语句执行次数和执行时间。

 图片 4

 

在计数时,每一个单独的seek、scan、lookup或update操作都被计算为对该索引的一次使用,并使该视图中的相应计数器加1。

2.通过数据库

在数据库选项卡中,选中缺失索引,体检专家会自动列出数据库中所有的缺失索引。

 

 图片 5

 

 

索引的Seek,Scan,Lookup和Update的含义是:

生成脚本

在找到缺失索引后可以通过体检专家自动生成脚本。执行计划中也可以类似的生成脚本。

 

 图片 6

 

图片 7

 

  • Seek是Index Seek:通过该索引进行查找的次数
  • Scan是Index Scan:通过该索引执行扫描查找的次数
  • Lookup是Key
    Lookup:通过该索引查找到数据后,再到源数据表进行键值查找的次数,Key
    Lookup是非聚集索引特有的,查询性能低下,应避免这种查找方法;
  • Update是Index Update:由于源表数据更新导致索引页更新的次数

如何建立

 

通过体检专家可以轻松找到系统中缺失的索引。但对于系统中大量的缺失索引,我们如何去选择呢?

Index Seek和Index Scan的区别是:

1.执行计划中的缺失索引

具体的值,根据业务不同可能有些不同。但是执行次数越多,执行时间越长,影响百分比越大,就越要建立索引。我的建议是:执行次数每天超过200,执行时间超过1000ms,影响百分比超过50%。

 图片 8

  • Index Seek是从BTree的根节点开始,向子节点查找,直到叶子节点;
  • Index
    Scan
    是在Index的叶子节点上,从左到右,把整个BTree的叶子节点遍历一遍,类似于Table
    Scan。

2.数据库中缺失索引

平均影响百分比越大,平均用户开销越大,用户查找次数越大,那么就越需要建立。

同样给出我的建议值:影响百分比50%,用户开销1,用户查找次数每天200

图片 9

 

如果索引的Seek,Scan,Lookup的计数值较多,那么说明索引被引用的次数多;如果查找计数器数值较小,但是Update数值较多,说明维护Index的开销高于查询带来的性能提升,应该考虑修改索引的结构,或者直接把索引删除。

总结

贴图是来自北京某银行系统。系统长时间语句非常多,严重影响前端用户体验。对系统第一轮优化,通过体检专家建立合适的索引后,系统的整体性能提升超过50%以上。

 

 

 文章用到的 Expert FOR SQLSERVER
工具下载链接:

 

 

图片 10图片 11

select db_name(us.database_id) as db_name
    ,object_schema_name(us.object_id)+'.'+object_name(us.object_id) as table_name
    ,i.name as index_name
    ,i.type_desc as index_type_desc
    ,us.user_seeks
    ,us.user_scans
    ,us.user_lookups
    ,us.user_updates
from sys.dm_db_index_usage_stats us 
inner join sys.indexes i 
    on us.object_id=i.object_id and us.index_id=i.index_id
where us.database_id=db_id()
    --us.database_id=db_id('database_name')
    --and us.object_id=object_id('schema_name.table_name')
order by us.user_seeks desc

View Code

二,统计索引的物理存储

使用 sys.dm_db_index_physical_stats
函数统计索引的物理存储,例如,碎片的百分比,数据存储的集中和分散程度,以及page空间的利用率等:

  • avg_fragmentation_in_percent:索引外部碎片的百分比,值越大,说明索引的逻辑顺序和物理顺序差异越大,查找性能越低;
  • fragment_count:分段的数量,表示索引数据的集中/分散程度;
  • avg_fragment_size_in_pages:分段的大小
  • avg_page_space_used_in_percent:索引内部碎片的百分比,值越大,说明page空间的利用率越高;

请阅读《索引碎片的检测和整理》,以了解更多。

三,底层操作的计数

使用 sys.dm_db_index_operational_stats
函数统计底层IO、加锁(Locking)、Latch和数据访问模式的计数,通过这些数据,用户能够追踪到查询请求必须等待多长时间才能完成数据的读写、标识索引是否存在IO热点。

在统计索引的底层操作之前,先了解跟数据的物理存储相关的术语:

  • 幽灵数据(ghost)是指:在索引的叶子节点中,数据行被标记为删除,但是还没有从索引结构中物理删除,幽灵数据只存在于索引的叶子节点中,幽灵数据由后台进程定期执行物理删除。
  • 转发数据(forwarding):需要两次IO操作才能获取到指定的数据,转发操作只发生于堆表(Heap)中;当数据行被更新,导致行的Size增大,以致于该行无法存储在当前的page中,为了避免相关索引的更新,数据库引擎会把该数据行转存到一个新的Page中,并在新旧
    Page中分别添加一个Pointer:在原Page中,Pointer指向新Page,该Pointer称作Forwarder
    Pointer;在新page中,Pointer指向原Page,称作Back
    Pointer。在读取数据时,数据库引擎首先从Forwarder
    Pointer中读取数据存储的指针,然后,根据指针到相应的地址空间中读取真正的数据。
  • 获取(Fetch)数据:用于从LOB或Row_Overflow的分配单元(Allocation
    Unit)中取回(Retrive)数据,大字段数据存储在特定的LOB或Row_Overflow类型的数据页中。
  • 剥离(Push
    Off)数据列:用于统计数据库引擎把LOB或Row-Overflow数据从原有的In-Row
    数据页剥离的次数。在执行Insert或Update操作之后,数据行的Size增长,不能存储在当前的Page中,必须把大数据字段的数据从原来的数据行中分离,存储在指定的分配单元中,这个过程就是数据列的剥离。
  • 拉回(Pull In)数据行:是Push
    Off的逆过程,用于统计数据库引擎把数据从LOB或Row-Overflow数据页拉入到In-Row数据页的次数,拉入数据行一般发生在更新数据之后,数据行的Size减小,数据行在释放存储空间之后,能够存储在In-Row
    Page中,数据引擎把数据从LOB或Row-Overflow数据页拉入到In-Row数据页,这个过程是数据列的拉回。

This (pulled in-row) occurs when an
update operation frees up space in a record and provides an
opportunity to pull in one or more off-row values from the LOB_DATA
or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA
allocation unit.

以下脚本用于统计索引底层的存储动作和锁/Latch的争用:

图片 12图片 13

select db_name(ops.database_id) as db_name
    ,object_schema_name(ops.object_id)+'.'+object_name(ops.object_id) as table_name
    ,i.name as index_name
    ,ops.partition_number
    ,ops.leaf_insert_count
    ,ops.leaf_delete_count
    ,ops.leaf_update_count
    ,ops.leaf_ghost_count
    ,ops.nonleaf_insert_count
    ,ops.nonleaf_delete_count
    ,ops.nonleaf_update_count
    ,ops.range_scan_count
    ,ops.singleton_lookup_count
    ,ops.forwarded_fetch_count

    ,iif(ops.row_lock_wait_count=0,0,ops.row_lock_wait_in_ms/ops.row_lock_wait_count) as avg_row_lock_wait_ms
    ,iif(ops.page_lock_wait_count=0,0,ops.page_lock_wait_in_ms/ops.page_lock_wait_count) as avg_page_lock_wait_ms
    ,iif(ops.page_latch_wait_count=0,0,ops.page_latch_wait_in_ms/ops.page_latch_wait_count) as avg_page_latch_wait_ms
    ,iif(ops.page_io_latch_wait_count=0,0,ops.page_io_latch_wait_in_ms/ops.page_io_latch_wait_count) as avg_page_io_latch_wait_ms
from sys.dm_db_index_operational_stats(db_id(),object_id('dbo.FactThread'),null,null) as ops
inner join sys.indexes i 
    on ops.object_id=i.object_id
        and ops.index_id=i.index_id
order by index_name

View Code

该函数统计的Latch征用数据主要分为PageLatch和PageIOLatch,其区别是:

发表评论

电子邮件地址不会被公开。 必填项已用*标注