当前位置: 澳门新濠3559 > 数据库 > 正文

查询优化器在一定程度上自动优化现有的执行计

时间:2019-11-09 00:17来源:数据库
数据库引擎是高度优化的闭环系统,基于执行计划的反馈,查询优化器在一定程度上自动优化现有的执行计划。查询优化的核心是索引优化,数据库引擎通过计数器统计关于索引操作的

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

澳门新濠3559,在做性能优化时,经常需要创建索引,维护索引,或重建,或重组;在创建索引时,索引的数据页有时需要填充满,有时需要预留一定比例的空闲空间;在分析查询的执行计划之后,推荐创建覆盖索引(covering index),优化查询语句,使用执行计划通过Index Seek来获取少量数据等,这些都是索引优化不得不知的要点。

一,统计索引的使用次数

一,索引的重组(Reorganize)和重建(Rebuild)

在用户成功提交查询语句时,执行计划中每一个单独的索引操作(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 计数器,这说明基础表会执行大量的更新操作,维护索引更新的开销比较大,数据库引擎利用索引提升查询性能的空间有限。 

在SQL Server中,索引(Index)是B-Tree(balance tree)结构,每个Page之间都有双向指针链接在一起。Index是在table结构之外,独立存在的存储结构。Index能使查询性能带来飞跃的主要原因是:Index 结构更小,能够更快加载到内存;Index ey物理顺序和逻辑一致,数据的预读取能够提高数据的加载速度,SQL Server 每次读取操作都会将物理物理相邻的多个Page一起加载到内存。

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

BTree结构决定 Index 的叶子节点,从左到右使依次增大,如图是Index的叶子节点,左边的Index Key最小,右边的Index Key最大:

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

澳门新濠3559 1

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

如果更新数据导致index key变化,例如,将index key 由4变更为9,那么必须将9放置在8之后,10之前,如果8所在的Page有空间容纳9,那么SQL Server只需要将9移动到8之后,原来的4被删除,这会降低原page中数据存储的密度,造成一个碎片(fragment),即:3和5之间存在空闲空间,但是物理顺序和逻辑顺序还是一致的。

Index Seek和Index Scan的区别是:

澳门新濠3559 2

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

如果8和10所在的page不能容纳9,那么 SQL Server 选择最节省,最有效的方式:拆分Page。试想,如果不拆分page,那么,5,6,7,8 这几个数据行都要向前移动,为9腾挪空间。在SQL Server中,数据移动是十分浪费IO,内存和CPU资源的,IO必须在CPU的调控下进行。

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

拆分Page是指分配一个新的Page,将8所在的Page上的数据的一半(后一半,或前一半)移动在新的Page上,如图,将page中的后一半移动在新的page上,通过指针连接在一起,保持逻辑顺序的一致性,但是物理顺序已经不连续了。

澳门新濠3559 3澳门新濠3559 4

澳门新濠3559 5

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

对于Index Key移动之后,其物理顺序和逻辑顺序仍然保持一致,这会导致索引出现碎片,数据存储的密度降低;而拆分page,不仅将page存储数据的密度降低一半,而且数据的物理顺序和逻辑顺序,导致SQL Server的预读取操作效果下降。针对Index的这两种情况,根据Index的碎片率,对Index 进行重组(Reorganize)或重建(Rebuild)。

View Code

1,索引Reorganize 和 Rebuild的过程 

二,统计索引的物理存储

Rebuild 是重新创建,将索引占用的原有存储空间释放,重新申请空间来创建索引结构,这意味着,SQL Server存储引起需要为索引结构分配新的数据页,在重建索引结构时,索引占用的存储空间是其实际大小的2倍还多。

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

Reorganize 是重新组织索引结构的叶子节点。在重组索引时,SQL Server存储引擎首先按照原有的填充因子(Fillfactor),压缩索引结构的叶子节点;然后,使用相同的数据页,把索引结构的叶子节点重新组织,使叶子节点符合索引定义的逻辑顺序。重组索引不会分配新的数据页,只占用索引最初占用的存储空间。

  • avg_fragmentation_in_percent:索引外部碎片的百分比,值越大,说明索引的逻辑顺序和物理顺序差异越大,查找性能越低;
  • fragment_count:分段的数量,表示索引数据的集中/分散程度;
  • avg_fragment_size_in_pages:分段的大小
  • avg_page_space_查询优化器在一定程度上自动优化现有的执行计划,索引的数据页有时需要填充满。used_in_percent:索引内部碎片的百分比,值越大,说明page空间的利用率越高;
ALTER INDEX { index_name | ALL }
ON schema.table
REBUILD | REORGANIZE 

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

2, 重建索引

三,底层操作的计数

在重建索引时,SQL Server 存储引擎使用索引的定义元数据,就是说,按照索引键(index key),索引类型(index type),唯一属性和排序方向重新创建索引。

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

  • 重建索引,将使被disable的索引重新启用;
  • 重建聚集索引时,不会重建与之关联的非聚集索引(nonclustered index),除非指定all关键字;all关键字指定基础表中的所有索引。
  • 如果指定all关键字,而基础表(underlying table)是堆,那么重建索引的操作对基础表没有任何影响;而与基础表相关的所有非聚集索引都将会重建;

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

在重新创建索引(Rebuild)时,如果没有指定索引选项(Index Option),Rebuild操作使用默认的索引选项。在SQL Server 2012版本中,共有11个索引选项,其中5个索引选项的元数据存储在sys.indexes 中,分别是 ignore_dup_key、fill_factor、is_padded、allow_row_locks、allow_page_locks,其他6个索引选项使用默认值,其默认值(Default value)都是“否定的”或0,如下列表所示:

  • 幽灵数据(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数据页,这个过程是数据列的拉回。
SORT_IN_TEMPDB :            Default OFF
STATISTICS_NORECOMPUTE :    Default OFF
DROP_EXISTING:              Default OFF
ONLINE:                     Default OFF
DATA_COMPRESSION :          Default NONE
MAXDOP:                     0

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.

查看 sys.indexes 存储的索引选项:

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

澳门新濠3559 6澳门新濠3559 7

澳门新濠3559 8澳门新濠3559 9

select i.object_id,i.name as IndexName,i.index_id,i.type,i.type_desc,
    i.data_space_id,i.is_disabled,

    --Unique Property
    i.is_unique,

    --Constraint
    i.is_primary_key,
    i.is_unique_constraint,

    --Filter Index
    i.has_filter,
    i.filter_definition,

    --Index Options
    i.ignore_dup_key,
    i.fill_factor,
    i.is_padded,
    i.allow_row_locks,
    i.allow_page_locks
from sys.indexes i
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

View Code

3,重组索引

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

重组索引结构的叶子节点,由于SQL Server默认以联机方式重组索引,这意味着在索引重组事务执行的过程中,SQL Server 存储引擎不会长期阻塞表锁,能够对基础表(underlying table)执行更新操作。如果索引选项ALLOW_PAGE_LOCKS设置为OFF,或索引被禁用,那么不能重组索引。

  • PageLatch是指:在访问数据有关的数据页(Data Page或Index Page)时,如果相应的Page已经存在于Buffer Pool中,那么SQL Server先获取buffer的latch,这个Latch就是 PageLatch,然后读取Buffer中的数据。

    PageLatch是施加在Buffer上的Latch, 用来保护:Data page,Index Page, 系统page(PFS,GAM,SGAM,IAM等)的争用访问;在数据更新时,分配新的page,或拆分 索引页(Index Page),会产生PageLatch 等待。

  • PageIOLatch是指:用于把数据从索引或Heap中加载到内存。当数据页从物理文件中的Page中读取到内存时,申请对内存Buffer施加的Latch是PageIOLatch。当数据页不在内存里时,SQL Server 先在内存中预留一个Page,然后从硬盘读取,加载到内存Buffer中,此时,SQL Server申请并获取的latch类型是PAGEIOLATCH,PageIOLatch表示正在进行IO操作。PageIOLatch_EX表示正在将disk中的数据页加载到内存,PageIOLatch_SH表示在加载数据页到内存期间,试图读取内存中的数据页,此时加载数据页的过程没有完成,处于Loading状态。如果经常出现PageIOLatch_SH,表明Loading数据页的时间太长,可能出现IO bottleneck。

二,索引的填充属性(FillFactor 和 PAD_INDEX)

分析查询结果,根据计数器的数值,调整数据库,使系统达到最优状态:

在Create Index时,必须考虑属性FillFactor 和 PAD_INDEX的设置,这两个属性只在create index 或 rebuild index时起作用,表示索引页的填充程度,在索引结构中,按照结点在BTree结构中的位置,索引页分为:叶级结点,中间结点。一个数据页(Page)的大小是8KB。

  • 如果发现字段leaf_ghost_count的数值特别大,说明索引中存储很多幽灵数据,可以通过重建索引(Rebuild)清理幽灵数据行:

    alter index index_name on table_name rebuild

  • 如果PageIOLatch等待较多,说明数据库频繁的执行硬盘IO操作,可能的原因是内存不足,或者数据文件没有分散到多个物理硬盘上

  • 如果PageLatch等待较多,说明数据库存在IO热点,可以通过增加数据文件ndf,把数据库分散到不同的物理硬盘上,以减少IO热点
  • FillFactor 属性指定索引叶级结点的填充百分比,微软建议设置FillFactor=90;
  • Pad_index 属性是boolean 类型,指定是否使用 FillFactor 来填充索引的中间节点;默认值是OFF;
  • 在插入数据时,SQL Server 尽可能使用Page全部的空间,而不会考虑填充属性;填充属性只影响索引的创建;

    PAD_INDEX = { ON | OFF } FILLFACTOR = fillfactor

四,缺失索引

在创建索引时,设置FillFactor=90,这意味着,SQL Server 在create index 或 rebuild index时,不是将索引页的全部空间用完,而是使用Page空间的90%,预留10%的空闲空间,预留的Page内部的空间叫做内部碎片(Internal Fragmentation)。预留一定比例的Fragmentation的作用是:减少Page拆分。预留比例需要折衷查询和更新操作:预留空间太多,能够减少Page Split,提高数据更新速度,但预留空间过多导致索引碎片(Index Fragmentation)太多,降低查询性能;预留空间太少,能够最大限度减少Index fragmentation,提高查询查询性能,但是一旦数据更新,会导致Page Split,产生外部碎片,降低查询性能。建议预留10% 的空间,这样既能提高数据更新的速度,也能兼顾数据的更新。

查询优化器(Query Optimizer)在执行查询时,如果检测到执行计划缺失索引,会把缺失索引的相关信息存储在缓存中,通过  sys.dm_db_missing_index_details 可以检测查询优化器建议创建的缺失索引。

1,索引中间节点的填充属性PAD_INDEX

该视图返回的缺失索引的索引键及包含列信息,在索引列的顺序上,通常来说,相等列(equality)应该排在不等列(inequality)之前,用户需要根据查询的条件来调整相等列和不等列的顺序,包含列(Included)应该添加到INCLUDE子句中,但是,该视图不会标识出相等列(equality)的排列顺序,需要根据查询语句和选择性来设置,索引键的第一列至关重要。而不等列(inequality)是指除等号(=)之外的比较符号,例如,table.cloumn>value。

PAD_INDEX属性表示中间节点填满的程度,100减去该属性值,就是索引页预留的空闲空间的百分比,默认值是OFF;如果设置为ON,那么在索引 create 或 rebuild时,SQL Server 将使用FillFactor百分比来填充中间节点,因此,需要指定FILLFACTOR。

澳门新濠3559 10澳门新濠3559 11

2,索引叶子节点的填充属性 FILLFACTOR

select mid.index_handle
    ,db_name(mid.database_id) as db_name
    ,mid.object_id
    ,object_name(mid.object_id,mid.database_id) as object_name
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,mid.statement as underlying_table
    ,mic.column_id
    ,mic.column_name
    ,mic.column_usage    
from sys.dm_db_missing_index_details as mid
cross apply sys.dm_db_missing_index_columns(mid.index_handle) as mic
order by mid.object_id
    ,mid.index_handle

FillFactor选项是一个整数值,有效值是从1到100,该属性表示叶子节点填满的程度,该属性的默认值是0,和FillFactor=100行为相同,表示在创建索引或重建索引时,数据页不会预留空闲空间。

View Code

3,推荐在创建索引时,显式指定填充属性

statement字段是缺失索引的表的名称,object_id字段是缺失索引的表的id,index_handle用于标识缺失的索引。

填充属性只在创建索引(create),或重建索引(rebuild)时起作用。如果填充因子在1和100之间,那么创建聚集索引将分配更多的数据页,带来的好处是减少了页拆分操作的次数。

缺失的索引都被分组,这意味着每一个缺失索引都被分配到一个特定的分组中,系统根据缺失索引的索引键把缺失索引分配到一个组中。

例如,在创建索引(create),或重建索引(rebuild)时,指定FillFactor=80,表示每个叶子节点留下20%的空闲空间,当新的数据插入到基础表(underlying table)时,SQL Server使用该20%的空闲空间来容纳新的数据。

在实际的数据库系统中,缺失索引可能很多,但是,并不是所有的缺失索引都对查询性能的提升有同等重要的作用,这可以通过系统视图:sys.dm_db_missing_index_group_stats 来度量:

在创建index 或重建 index时,指定 fillfactor 和 pad_index 属性:

select top 111
    g.index_handle
    ,gs.unique_compiles
    ,gs.user_scans
    ,gs.user_seeks
    ,gs.avg_total_user_cost
    ,gs.avg_user_impact
    ,gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) benefit_weight
from sys.dm_db_missing_index_groups g
inner join sys.dm_db_missing_index_group_stats gs
    on g.index_group_handle=gs.group_handle
order by benefit_weight desc

澳门新濠3559 12澳门新濠3559 13

重要的字段注释:

--rebuild index
ALTER INDEX index_name
ON schema.table
REBUILD 
WITH (fillfactor = 90,pad_index=on,data_compression=page);

--create index
CREATE INDEX index_name
ON schema.table
(indexkey) 
WITH (DROP_EXISTING = ON,fillfactor = 90,pad_index=on,data_compression=page);
  • user_scans 和 user_seeks 是指:如果分组中的索引被创建,用户的查询会引用索引做seek或scan操作的次数。
  • avg_total_user_cost 是指:如果分组中的索引被创建,用户的查询能够减少的平均开销。
  • avg_user_impact 是指:如果分组中的索引被创建,用户的查询能够获得的平均收益。

View Code

在实际的数据库系统中,数据库管理员需要监控分组的统计数据,根据开销和收益来创建缺失的索引,以最大程序的提高系统查询性能。

三,索引的查找(Index Seek)和扫描(Index Scan)

五,查看表上创建的所有索引及其定义

索引是B树结构,在执行查询时,Index Seek是指SQL Server从索引结构的根节点(Root Node),逐级向叶节点(Leaf Node)查找;在查找到相应叶子节点后,取出叶子节点的数据。对于聚集索引,叶子节点是整个表的数据,Index Seek能够获取到所有列的数据,而对于非聚集索引,叶子节点存储的是索引列的数据,如果索引有包含列,那么叶子节点中也存储包含列的数据,Index Seek只能获取索引列和包含列的数据;如果查询还需要返回其他列的数据,那么SQL Server必须根据索引叶子节点包含的“行地址”信息到基础表(或聚集索引)中去获取数据,这就是进行书签查找(key lookup)。Index Seek用于从大数据量的表中返回少量记录的查询。

通过视图 sys.indexes 和 sys.index_columns 查看在基础表创建的所有索引:

Index Scan是直接遍历索引树的所有叶子节点,对于包含聚集索引的基础表,只能进行Index Seek或Index Scan,因为,聚集索引的叶子包含所有的数据。对于堆表,当需要返回所有列的数据,SQL Server有时会选择执行Table Scan。Table Scan是对全表进行逐行的扫描,即使数据表中只有一行数据匹配,也会将所有数据匹配一遍,微软建议,始终在数据表上创建聚集索引。

澳门新濠3559 14澳门新濠3559 15

 

select o.name as table_name
    ,i.index_id
    ,i.name as index_name
    ,i.type_desc as index_type
    ,c.name AS index_column_name
    ,ic.key_ordinal as index_key_ordinal
    ,iif(ic.is_descending_key=1,'desc','asc') as sort_direction
    ,ic.index_column_id
    ,ic.is_included_column
    ,i.fill_factor
    ,i.is_padded
    ,i.has_filter
    ,i.filter_definition
    --,ic.partition_ordinal
from sys.objects o
inner join sys.indexes i
    on o.object_id = i.object_id
inner join sys.index_columns ic
    on i.object_id = ic.object_id 
        and i.index_id = ic.index_id
inner join sys.columns c
    on o.object_id = c.object_id 
        and ic.column_id = c.column_id
where o.name = 'table_name'
    --and i.name='index_name'
order by i.index_id,
    ic.index_column_id

参考文档:

View Code

Reorganize and Rebuild Indexes.aspx)

 

Index Seek和Index Scan的区别以及适用情况

参考文档:

SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note.aspx)

An in-depth look at Ghost Records in SQL Server

Index Related Dynamic Management Views and Functions (Transact-SQL).aspx)

编辑:数据库 本文来源:查询优化器在一定程度上自动优化现有的执行计

关键词: