sql創(chuàng)建和優(yōu)化索引

1、創(chuàng)建和優(yōu)化索引創(chuàng)建和優(yōu)化索引 SQL Server 訪問(wèn)數(shù)據(jù)的方式訪問(wèn)數(shù)據(jù)的方式 表掃描表掃描 SQL Server 掃描表的所有頁(yè)索引索引 SQL Server 使用索引頁(yè)找到行沒(méi)有聚集索引的表沒(méi)有聚集索引的表不按特定順序存儲(chǔ)的頁(yè)不按特定順序存儲(chǔ)的頁(yè)堆堆堆堆IAM 頁(yè)頁(yè)數(shù)據(jù)頁(yè)數(shù)據(jù)頁(yè)idindex_id = 0first_iam_pagesys.partitions聚集索引聚集索引索引頁(yè)葉節(jié)點(diǎn)索引頁(yè)葉節(jié)點(diǎn)一張表只能有一個(gè)聚集索引一張表只能有一個(gè)聚集索引B-樹(shù)按索引鍵的順序存儲(chǔ)數(shù)據(jù)頁(yè)樹(shù)按索引鍵的順序存儲(chǔ)數(shù)據(jù)頁(yè)idindex_id = 1root_pagesys.partitions索引頁(yè)中間級(jí)索

2、引頁(yè)中間級(jí)根索引頁(yè)根索引頁(yè)B樹(shù)參考下面的堆和聚集索引樹(shù)參考下面的堆和聚集索引每張表最多每張表最多249個(gè)非聚集索引個(gè)非聚集索引非聚集索引非聚集索引idindex_id 1root_pagesys.partitions數(shù)據(jù)頁(yè)堆或者數(shù)據(jù)頁(yè)堆或者聚集索引聚集索引索引頁(yè)葉節(jié)點(diǎn)索引頁(yè)葉節(jié)點(diǎn)根索引頁(yè)根索引頁(yè)創(chuàng)建索引時(shí)遵循一般原則創(chuàng)建索引時(shí)遵循一般原則在需要經(jīng)常搜索的列上面創(chuàng)建索引在主鍵上創(chuàng)建索引在外鍵上創(chuàng)建索引在經(jīng)常用于Where子句的列上創(chuàng)建索引在經(jīng)常需要排序的列上創(chuàng)建索引對(duì)于數(shù)據(jù)類型為text，image，bit的列不應(yīng)該增加索引當(dāng)需要Update性能遠(yuǎn)高于Select性能時(shí)不應(yīng)該增加索引WITH 選

3、項(xiàng)目的ALLOW_ROW_LOCKS在索引上允許/不允許行級(jí)鎖ALLOW_PAGE_LOCKS在索引上允許/不允許頁(yè)級(jí)鎖ONLINE在創(chuàng)建的時(shí)候允許/不允許訪問(wèn)索引FILLFACTOR控制在葉級(jí)頁(yè)上釋放空間PAD_INDEX控制在非葉級(jí)頁(yè)上釋放空間CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n )INCLUDE ( column ,.n )WITH option ,.n ON partition_scheme (column) | filegroup

4、| default CREATE INDEX語(yǔ)句語(yǔ)句惟一索引惟一索引 確保在索引鍵上不存在相同的值確保在索引鍵上不存在相同的值CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC)EmployeeIDLoginIDGenderMaritalStatus216mike0MS231fukiko0MM242pat0MS291pat0FS不允許存在相同值不允許存在相同值創(chuàng)建多列索引創(chuàng)建多列索引復(fù)合索引復(fù)合索引 最多可組合 16 個(gè)列以及鍵的長(zhǎng)度不超過(guò)900字節(jié) 首先定義

5、惟一性最高的列 包含性列包含性列 帶包含性列的索引 提高查詢“范圍”，提高查詢性能CREATE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC)INCLUDE ( ContactID, NationalIDNumber) CREATE NONCLUSTERED INDEX K_Contact_LastName_FirstName ON Person.Contact ( LastName ASC, FirstName ASC)非鍵列非鍵列 可以是不允許作為索引鍵列的數(shù)據(jù)類型 在計(jì)算索引

6、鍵列的數(shù)目或索引鍵大小時(shí)，不會(huì)被考慮在內(nèi)USE AdventureWorks;GOCREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName); 使用使用Transact-SQL創(chuàng)建具有包含性列的索引創(chuàng)建具有包含性列的索引對(duì)計(jì)算列創(chuàng)建索引的場(chǎng)合對(duì)計(jì)算列創(chuàng)建索引的場(chǎng)合 在以下情況下，可對(duì)計(jì)算列創(chuàng)建索引在以下情況下，可對(duì)計(jì)算列創(chuàng)建索引 ：計(jì)算列表達(dá)式是確定性的和精確的計(jì)算列表達(dá)式是確定性的和精確的 ANSI_NULLS 連接級(jí)選項(xiàng)為連接級(jí)選項(xiàng)為 ON 求值結(jié)果不為求值結(jié)果不為te

7、xt, ntext或者或者image 數(shù)據(jù)類型的列數(shù)據(jù)類型的列當(dāng)創(chuàng)建索引和修改索引的時(shí)候要求的選項(xiàng)設(shè)置為當(dāng)創(chuàng)建索引和修改索引的時(shí)候要求的選項(xiàng)設(shè)置為ONNUMERIC_ROUNDABORT 選項(xiàng)設(shè)置為選項(xiàng)設(shè)置為OFF查詢優(yōu)化器可能忽略計(jì)算列上的索引查詢優(yōu)化器可能忽略計(jì)算列上的索引 2005與分區(qū)表類似，根據(jù)值范圍將索引進(jìn)行水平分區(qū)與分區(qū)表類似，根據(jù)值范圍將索引進(jìn)行水平分區(qū)idindex_idpartition_numberhobt_id1216756756765722275675676665sys.partitions考慮下面表中的列以及考慮索引鍵中的分區(qū)鍵（唯一考慮下面表中的列以及考慮索引鍵中

8、的分區(qū)鍵（唯一鍵）鍵）已分區(qū)索引已分區(qū)索引 獲得索引信息的方法獲得索引信息的方法 SQL Server Management Studio 對(duì)象資源管理器 索引“屬性”窗口 報(bào)告 系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程 sp_help sp_helpindex目錄視圖目錄視圖系統(tǒng)函數(shù)系統(tǒng)函數(shù)分析數(shù)據(jù)庫(kù)工作負(fù)荷下的性能分析數(shù)據(jù)庫(kù)工作負(fù)荷下的性能圖形化和命令行界面圖形化和命令行界面數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)報(bào)告和建議報(bào)告和建議工作負(fù)荷工作負(fù)荷數(shù)據(jù)庫(kù)引擎數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)優(yōu)化顧問(wèn)數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象索引碎片索引碎片索引碎片發(fā)生索引碎片發(fā)生 當(dāng)數(shù)據(jù)被更改的時(shí)候，SQL Server重新整理索引頁(yè)，對(duì)索引頁(yè)進(jìn)行拆分。碎片類型碎片類型 內(nèi)部 頁(yè)使用不充分 外部 頁(yè)的邏輯順序錯(cuò)誤檢測(cè)碎片檢測(cè)碎片 SQL Server Management Studio 索引屬性窗口 系統(tǒng)函數(shù) sys.dm_db_index_physical_stats 索引碎片整理選項(xiàng)索引碎片整理選項(xiàng) 3