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

INDEX語法線上索引作業(yè)觀察磁碟I/O之差異SQLServer的索引架構(gòu)書籍SQLServer資料庫儲存方式內(nèi)容資料表

(Table)資料頁

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

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

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

時(shí)的排序規(guī)則，適用於查詢資料表部分欄位時(shí)RootNon-LeafLeafBalanced

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

所花費(fèi)之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太大，極易造成查詢逾時(shí)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%，但仍有可能逾時(shí)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覆蓋索引：類似更多資訊的名詞解釋架構(gòu)，除了有名詞的頁次，還包括章、節(jié)、類型等資訊建立索引DEMO維護(hù)索引維護(hù)索引DBCCSHOWCONTIG索引的停用與再度啟用與索引相關(guān)的動態(tài)管理函數(shù)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與索引相關(guān)的動態(tài)管理函數(shù)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');維護(hù)索引DEMO覆蓋索引(COVERINGINDEX)覆蓋索引利用非叢集索引滿足查詢的需求無需使用BookmarkLookup類似更多資訊的名詞解釋架構(gòu)，除了有名詞的頁次，還包括章、節(jié)、類型等資訊書本的名詞解釋通常只會指出名詞位在哪些頁次，無法回答位在哪個(gè)章、節(jié)，因此需要搭配書籤WHERE條件的欄位SELECT的欄位SELECTColumn2,Column3FROMTableAWHEREColumn4=?SQLServer2000覆蓋索引的限制將WHERE條件的欄位與SELECT的欄位，皆包括在非叢集索引之中WHERE條件的欄位在前，再加SELECT的欄位欄位的數(shù)目如果太多，Leaf與Non-Leaf皆有相同的欄位B-Tree過於龐大，導(dǎo)致效率不佳侷限於每個(gè)索引最多16個(gè)欄位和900Bytes的限制SQLServer2005索引效能再提升CREATEINDEX新增的INCLUDE子句將非索引的欄位，儲存於索引的leaflevel無需依靠索引所屬的資料表，減少磁碟I/O，使查詢速度更快不侷限於每個(gè)索引最多16個(gè)欄位和900Bytes的限制CREATENONCLUSTEREDINDEXIX_Address_PostalCodeONPerson.Address(PostalCode)INCLUDE(AddressLine1,AddressLine2,City)

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

ANSI_NULLSON

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

SET

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

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

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

OFF下列則要宣告為ONANSI_NULLS

ONANSI_PADDING

ONANSI_WARNINGS

ONARITHABORT

ONCONCAT_NULL_YIELDS_NULL

ONQUOTED_IDENTIFIER

ON引用的函數(shù)必須是可確定性的如何確認(rèn)計(jì)算欄位／索引可以建立索引COLUMNPROPERTYOBJECTPROPERTY在計(jì)算欄位上建立索引DEMO統(tǒng)計(jì)統(tǒng)計(jì)何謂統(tǒng)計(jì)與統(tǒng)計(jì)相關(guān)的指令DBCCSHOW_STATISTICSCREATESTATISTICS/DROP

STATISTICS如何利用統(tǒng)計(jì)提升效能何謂統(tǒng)計(jì)有關(guān)資料行中值分佈的統(tǒng)計(jì)資訊查詢最佳化工具使用此統(tǒng)計(jì)資訊來決定查詢的最佳查詢計(jì)劃在建立統(tǒng)計(jì)資料時(shí)，會針對建立統(tǒng)計(jì)資料的資料行排序其值，以及根據(jù)最多200個(gè)資料行的值來建立「長條圖」並以間隔分開長條圖可指定有多少資料列完全符合每個(gè)間隔值，有多少資料列落在間隔內(nèi)、值密度的計(jì)算或是在間隔內(nèi)重複值的發(fā)生資料庫選項(xiàng)（統(tǒng)計(jì)）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]可能導(dǎo)致查詢最佳化工具選擇效率較差的執(zhí)行計(jì)畫無法以此指令刪除索引的相關(guān)統(tǒng)計(jì)如何利用統(tǒng)計(jì)提升效能結(jié)合資料行值可以超過加諸於索引鍵值900Bytes的限制SQLServer2005可在下列資料型別建立統(tǒng)計(jì)資料char、varchar、varchar(max)nchar、nvarchar、nvarchar(max)text、ntext可協(xié)助查詢最佳化工具估計(jì)查詢述詞在字串模式上的選擇可提升查詢中有LIKE條件時(shí)的效能，如：WHEREProductNameLIKE'%Bike‘WHERENameLIKE'[CS]heryl‘統(tǒng)計(jì)DEMOQ&AReadinesswithSkillsAssessmentSelf-studylearningtoolfreetoanyone.Determinesskillsgaps.Provideslearningplans.PostyourScore,seehowyoustackup.Visit/assessmentBecomeaMicrosoftCertifiedProfessional

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

針對IT經(jīng)理人(TDM)與IT專業(yè)人員(ITPro)每個(gè)月及每季固定舉辦技術(shù)講座。講座中談?wù)揑T新知、實(shí)務(wù)以及技術(shù)分享。TechEd

臺灣自1995年首辦以來，一直是國內(nèi)最具指標(biāo)性、最大規(guī)模的技術(shù)研討會。MicrosoftTech