善用索引提升查询之效能-MicrosoftDownloadCenter课件

善用索引提升查詢之效能許致學胡百敬(二)善用索引提升查詢之效能建立索引維護索引覆蓋索引索引檢視在計算欄位上建立索引統計顯示執行計畫的SQL指令顯示查詢的執行計畫SETSHOWPLAN_TEXTONSETSHOWPLAN_ALLON利用STATISTICS陳述式SETSTATISTICSTIMEONSETSTATISTICSIOONSETSTATISTICSPROFILEONSETSTATISTICSXML不會真正執行查詢內容，顯示評估的執行計劃會真正執行查詢內容，顯示實際的執行計劃圖形化查詢執行計畫如何提昇效能減少TableScan減少ClusteredIndexScan減少Sort磁碟I/O效能NonclusteredIndexSeekClusteredIndexSeekNonclusteredIndexScan執行計畫DEMO建立索引建立索引SQLServer的索引架構CREATE

INDEX語法線上索引作業觀察磁碟I/O之差異SQLServer的索引架構書籍SQLServer資料庫儲存方式內容資料表

(Table)資料頁

(DataPages)目錄叢集索引(ClusteredIndex)索引頁+資料頁名詞解釋非叢集索引(NonclusteredIndex)索引頁

(IndexPages)每個資料表只能有1個叢集索引每個資料表最多可以有249個非叢集索引非叢集索引對應至RowID或ClusteringKey每個索引最多可以包括16個欄位，不得超過900Bytes叢集索引與非叢集索引B-TreeNon-LeafLeaf叢集索引索引頁資料頁非叢集索引索引頁索引頁叢集索引（目錄）會影響資料表實際儲存資料時的排序

規則，適用於經常ORDERBY、GROUPBY等查詢非叢集索引（名詞解釋）不會影響資料表實際儲存資料

時的排序規則，適用於查詢資料表部分欄位時RootNon-LeafLeafBalanced

Tree8KBytes8KBytes線上索引作業索引之建立、重建、刪除皆可採用Online選項不再採用以往SQLServer的獨占式鎖定允許使用者同時存取資料CREATEINDEX…ONMyTable(…)WITH(ONLINE=ON)管理者使用者使用者線上索引作業使用技巧僅企業版支援比Offline索引建立的時間長叢集索引不可使用在包含LOB欄位image/text/xml/max型態，如varchar(max)非叢集索引則無此限制暫存資料表不支援區域性(#)暫存資料表支援全域性(##)暫存資料表進行線上索引作業時，無法同時進行其他索引作業觀察磁碟I/O之差異TableScan的DiskI/ONonclusteredIndexScan的DiskI/OClusteredSeek的DiskI/ONonclusteredIndexSeek的DiskI/OPS.以下說明的計算範例皆為概算值，與實際上SQLServer

所花費之DiskI/O會有些許的差異TableScan的磁碟I/O1DataRow=200Bytes(10Bytes×20Columns)1DataPage=40DataRows(8K÷200Bytes)DiskSpaceforDataPages=25,000DataPages(1,000,000rows÷40rows/pages)

=200MB(8K×25,000)TableScan：tableA沒有任何的索引ClusteredIndexScan：叢集索引是column3

以外的欄位DiskI/O太大，極易造成查詢逾時RootNon-LeafLeafNonclusteredIndexScan的磁碟I/O1IndexRow=20Bytes(10Bytes×2Columns)1IndexPage=400IndexRows(8K÷20Bytes)DiskSpaceforIndexPages=2,500IndexPages(1,000,000rows÷400rows/pages)=20MB(8K×2,500)IndexScan：非叢集索引是

column1,column3雖然DiskI/O只有tablescan的10%，但仍有可能逾時RootNon-LeafLeafClusteredIndexSeek的磁碟I/O1DataRow=200Bytes(10Bytes×20Columns)1DataPage=40DataRows(8K÷200Bytes)DiskSpaceforDataPages=25DataPages(1,000rows÷40rows/pages)

=200KB(8K×25)ClusteredIndexSeek：叢集索引是

column3的欄位DiskI/O只有

tablescan的0.1%，Indexscan的1%RootNon-LeafLeafNonclusteredIndexSeek的磁碟I/O1IndexRow=20Bytes(10Bytes×2Columns)1IndexPage=400IndexRows(8K÷20Bytes)DiskSpaceforIndexPages=3IndexPages(1,000rows÷400rows/pages)=24KB(8K×3)IndexSeek：非叢集索引是column3,column1IndexSeek：非叢集索引是column3

+叢集索引是column1DiskI/O只有

ClusteredIndexseek的12%RootNon-LeafLeaf覆蓋索引：類似更多資訊的名詞解釋架構，除了有名詞的頁次，還包括章、節、類型等資訊建立索引DEMO維護索引維護索引DBCCSHOWCONTIG索引的停用與再度啟用與索引相關的動態管理函數sys.dm_db_index_physical_statsDBCCSHOWCONTIGDBCCSHOWCONTIG[(

{‘table_name’|table_id|‘view_name’|view_id

}

[,'index_name'|index_id])]

[WITH

{

[,[ALL_INDEXES]]

[,[TABLERESULTS]]

[,[FAST]]

[,[ALL_LEVELS]]

[NO_INFOMSGS]

}

]sys.dm_db_index_physical_statsDBCCSHOWCONTIG

(table_name)DBCCSHOWCONTIG

(table_id,index_id)DBCCSHOWCONTIG

WITHFASTDBCCSHOWCONTIG

WITHTABLERESULTS,ALL_INDEXES索引的停用與再度啟用停用索引ALTERINDEX<index_name>

ON<table_name>|<view_name>

DISABLE再度啟用索引ALTERINDEX<index_name>

ON<table_name>|<view_name>

REBUILD與索引相關的動態管理函數sys.dm_db_index_physical_stats

(

{database_id|NULL|0|DEFAULT}

,{object_id|NULL|0|DEFAULT}

,{index_id|NULL|0|-1|DEFAULT}

,{partition_number|NULL|0|DEFAULT}

,{mode|NULL|DEFAULT})SELECT*FROMsys.dm_db_index_physical_stats

(DB_ID(N‘AdventureWorks’),OBJECT_ID(N‘Person.Address’),

NULL,NULL,'DETAILED');維護索引DEMO覆蓋索引(COVERINGINDEX)覆蓋索引利用非叢集索引滿足查詢的需求無需使用BookmarkLookup類似更多資訊的名詞解釋架構，除了有名詞的頁次，還包括章、節、類型等資訊書本的名詞解釋通常只會指出名詞位在哪些頁次，無法回答位在哪個章、節，因此需要搭配書籤WHERE條件的欄位SELECT的欄位SELECTColumn2,Column3FROMTableAWHEREColumn4=?SQLServer2000覆蓋索引的限制將WHERE條件的欄位與SELECT的欄位，皆包括在非叢集索引之中WHERE條件的欄位在前，再加SELECT的欄位欄位的數目如果太多，Leaf與Non-Leaf皆有相同的欄位B-Tree過於龐大，導致效率不佳侷限於每個索引最多16個欄位和900Bytes的限制SQLServer2005索引效能再提升CREATEINDEX新增的INCLUDE子句將非索引的欄位，儲存於索引的leaflevel無需依靠索引所屬的資料表，減少磁碟I/O，使查詢速度更快不侷限於每個索引最多16個欄位和900Bytes的限制CREATENONCLUSTEREDINDEXIX_Address_PostalCodeONPerson.Address(PostalCode)INCLUDE(AddressLine1,AddressLine2,City)

SQLServer2005索引效能再提升分割索引(Partitionedindexes)如同分割資料表一般，將索引指定儲存於多個檔案群組CREATENONCLUSTEREDINDEXIX_TransactionHistory_ReferenceOrderIDONProduction.TransactionHistory(ReferenceOrderID)ONTransactionsPS1(TransactionDate);覆蓋索引DEMO索引檢視建立索引檢視的要件建立時必須宣告SET

ANSI_NULLSON

(包括所有相關的資料表)

SET

QUOTED_IDENTIFIERONWITHSCHEMABINDING不可以參考其他檢視，須直接引用資料表所有相關的資料表必須在同一個資料庫與同一個擁有者檢視中引用的使用者自訂函數也必須宣告

SCHEMABINDING檢視中引用的函數必須是可確定性的

檢視中引用的資料表和使用者自訂函數必須使用two-partnames不可使用One-part,three-part,andfour-partnames索引檢視的適用範圍適用情境重複同一種模式的查詢對於大型資料表作聯結和彙總對於某些欄位重複地作彙總重覆對相同的資料表，相同的鍵值作聯結不適用情境異動量大的資料表索引檢視與原始資料表之資料量相差不多時索引檢視DEMO在計算欄位上建立索引在資料表建立計算欄位的限制建立時必須宣告NUMERIC_ROUNDABORT

OFF下列則要宣告為ONANSI_NULLS

ONANSI_PADDING

ONANSI_WARNINGS

ONARITHABORT

ONCONCAT_NULL_YIELDS_NULL

ONQUOTED_IDENTIFIER

ON引用的函數必須是可確定性的如何確認計算欄位／索引可以建立索引COLUMNPROPERTYOBJECTPROPERTY在計算欄位上建立索引DEMO統計統計何謂統計與統計相關的指令DBCCSHOW_STATISTICSCREATESTATISTICS/DROP

STATISTICS如何利用統計提升效能何謂統計有關資料行中值分佈的統計資訊查詢最佳化工具使用此統計資訊來決定查詢的最佳查詢計劃在建立統計資料時，會針對建立統計資料的資料行排序其值，以及根據最多200個資料行的值來建立「長條圖」並以間隔分開長條圖可指定有多少資料列完全符合每個間隔值，有多少資料列落在間隔內、值密度的計算或是在間隔內重複值的發生資料庫選項（統計）DBCCSHOW_STATISTICSDBCCSHOW_STATISTICS

('table_name'|'view_name'

,target)

[WITH[NO_INFOMSGS]<option>[,n]]target

::

= indexname

|statisticsname|

columnname<option>::=

STAT_HEADER|DENSITY_VECTOR|HISTOGRAMDBCCSHOW_STATISTICSSTAT_HEADERDENSITY_VECTORHISTOGRAMCREATESTATISTICSCREATESTATISTICSstatistics_name

ON{table|view}(column[,...n])

[WITH

[

[FULLSCAN

|SAMPLEnumber{PERCENT|ROWS}

|STATS_STREAM=stats_stream][,]]

[NORECOMPUTE]

];DROPSTATISTICSDROPSTATISTICStable.statistics_name|view.statistics_name[,...n]可能導致查詢最佳化工具選擇效率較差的執行計畫無法以此指令刪除索引的相關統計如何利用統計提升效能結合資料行值可以超過加諸於索引鍵值900Bytes的限制SQLServer2005可在下列資料型別建立統計資料char、varchar、varchar(max)nchar、nvarchar、nvarchar(max)text、ntext可協助查詢最佳化工具估計查詢述詞在字串模式上的選擇可提升查詢中有LIKE條件時的效能，如：WHEREProductNameLIKE'%Bike‘WHERENameLIKE'[CS]heryl‘統計DEMOQ&AReadinesswithSkillsAssessmentSelf-studylearningtoolfreetoanyone.Determinesskillsgaps.Provideslearningplans.PostyourScore,seehowyoustackup.Visit/assessmentBecomeaMicrosoftCertifiedProfessional

WhatareMCPcertifications?ValidationinperformingcriticalITfunctions.WhyCertify?WWrecognitionofskillsgainedviaexperience.MoreeffectivedeploymentswithreducedcostsWhatCertificationsarethereforITPros?MCP,MCSE,MCSA,MCDST,MCDBA./learning/mcpHeardtheNewsaboutTechNet?Softwarewithouttimelimits!Complimentarytechnicalsupport.Themostcurrentresourcesonhand在這裡，您可以找到提升技術能力與解決問題的方法/taiwan/technet1.IT專業人員活動TechNet技術講座&TechNet巡迴講座

針對IT經理人(TDM)與IT專業人員(ITPro)每個月及每季固定舉辦技術講座。講座中談論IT新知、實務以及技術分享。TechEd

台灣自1995年首辦以來，一直是國內最具指標性、最大規模的技術研討會。MicrosoftTech