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

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

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

3、項目的ALLOW_ROW_LOCKS在索引上允許/不允許行級鎖ALLOW_PAGE_LOCKS在索引上允許/不允許頁級鎖ONLINE在創(chuàng)建的時候允許/不允許訪問索引FILLFACTOR控制在葉級頁上釋放空間PAD_INDEX控制在非葉級頁上釋放空間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語句語句惟一索引惟一索引 確保在索引鍵上不存在相同的值確保在索引鍵上不存在相同的值CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC)EmployeeIDLoginIDGenderMaritalStatus216mike0MS231fukiko0MM242pat0MS291pat0FS不允許存在相同值不允許存在相同值創(chuàng)建多列索引創(chuàng)建多列索引復(fù)合索引復(fù)合索引 最多可組合 16 個列以及鍵的長度不超過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ù)類型 在計算索引

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

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

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