SQL 最佳化–索引

Sys.dm_exec_requests是檢查SQL Server效能瓶頸的有力工具

update statistics t_goods with fullscan---更新表的統計資訊
聚簇索引的索引葉子就是資料本身。
非聚簇索引的索引葉子節點包含的是指向資料的書籤（即資料行號或叢集索引的key）
 

索引最佳化原則：
主要是減少邏輯讀的次數。邏輯讀中包含從記憶體資料緩衝中訪問的頁數和從物理磁碟中讀取的頁數。
而物理讀表示那些沒有駐留在記憶體緩衝區中需要從磁碟讀取的資料頁數。
最佳化主要是針對 邏輯讀的次數。

在執行查詢時，SQL Server動態選擇使用哪個索引。為此，SQL Server根據每個索引上分布在該關鍵字上的統計量來決定使用哪個索引。值得注意的是，經過日常的資料庫活動（如插入、刪除和更新表格），SQL Server用到的這些統計量可能已經“到期”了，需要更新。你可以通過執行DBCC SHOWCONTIG來查看統計量的狀態。當你認為統計量已經“到期”時，你可以執行該表格的UPDATE STATISTICS命令，這樣SQL Server就重新整理了關於該索引的資訊了。

根據使用頻率決定哪些欄位需要建立索引，選擇經常作為串連條件、篩選條件、彙總查詢、排序的欄位作為索引的候選欄位
把經常一起出現的欄位組合在一起，組成複合索引，複合索引的欄位順序與主鍵一樣，也需要把最常用的欄位放在前面，把重複率低的欄位放在前面。

主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結構的層次更少。
主鍵的選擇還要注意複合主鍵的欄位次序，對於複合主鍵來說，不同的欄位次序的主鍵的效能差別可能會很大，一般應該選擇重複率低、單獨或者組合查詢可能性大的欄位放在前面

在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會 被使用，並且應儘可能的讓欄位順序與索引順序相一致。

--//////////
.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然 而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。

解決方式：
可以改為強制查詢使用索引：
select id from usertable with(index(索引名)) where code=@code 或 select * from usertable with(index=index_name) wherecode=@code--強制按照索引尋找

--//////////

應儘可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的實體儲存體順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。

聚簇索引(clustered indexes)的使用

　　聚簇索引是一種對磁碟上實際資料重新組織以按指定的一個或多個列的值排序。由於聚簇索引的索引頁面指標指向資料頁面，所以使用聚簇索引尋找資料幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，並且建聚簇索引需要至少相當該表120%的附加空間，以存放該表的副本和索引中間頁。聚簇索引的葉子頁面和表的資料頁面相同。聚簇索引列的順序不僅是聚簇索引行的順序還是資料行的物理順序。因為表只能有一種儲存物理順序。所以一個表只能有一個聚簇索引。聚簇索引不需要像非聚簇索引那樣從索引行跳到基本行。

在聚簇索引下，資料在物理上按順序排在資料頁上，重複值也排在一起，因而在那些包含範圍檢查(between、<、<=、>、&gt; =)或使用group by或order by的查詢時，一旦找到具有範圍中第一個索引值的行，具有後續索引值的行保證物理上毗連在一起而不必進一步搜尋，避免了大範圍掃描，可以大大提高查詢速度。

在一個頻繁發生插入操作的表上建立聚簇索引時，不要建在具有單調上升值的列(如 IDENTITY 的自增列)上，否則會經常引起封鎖衝突。
在聚簇索引中不要包含經常修改的列，因為碼值修改後，資料行必須移動到新的位置。
 選擇聚簇索引應基於where子句和串連操作的類型。聚簇索引的侯選列是：

　　● 主鍵列,該列在where子句中使用並且插入是隨機的。

　　● 按範圍存取的列，如p_order > 100 and p_order < 1000。

　　● 在group by或order by中使用的列。

　　● 不經常修改的列。

　　● 在串連操作中使用的列。

　註：由於聚簇索引上非聚簇索引的依賴性，用單獨的 drop index 和 create index 重建聚簇索引將導致所有非聚簇索引被建立兩次。可以使用  CREATE CLUSTERED INDEX IX_TB(CODE) WITH( DROP_EXISTING=ON) 通過一步方式來實現。
　同理也可以在非聚簇索引上使用 CREATE NONCLUSTERED INDEX IX_TB(CODE) WITH(DROP_EXISTING=ON)

CREATE NONCLUSTERED INDEX IX_TB ON TABLE(C1,code)
with (data_compression=row) ----建立索引時指定 索引壓縮(行壓縮或頁壓縮)
CREATE NONCLUSTERED INDEX IX_TB ON TABLE(C1,code)
with (data_compression=page) ----建立索引時指定 索引壓縮(行壓縮或頁壓縮)

非聚簇索引(nonclustered indexes)的使用

     非聚簇索引在其索引行上儲存聚簇索引鍵(表包含聚簇索引)。非聚簇索引都將聚簇索引索引值做為其的行定位器。不包含聚簇索引的表叫 堆表。非聚簇索引儲存的是一個指標(行定位器)，指標從索引行定位到資料行。

　　SQL Server預設情況下建立的索引是非聚簇索引，由於非聚簇索引不重新組織表中的資料，而是對每一行儲存索引列值並用一個指標指向資料所在的頁面。換句話說非聚簇索引具有在索引結構和資料本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問資料的不同排序次序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：

　　● 索引需要使用多少空間。

　　● 合適的列是否穩定。

　　● 索引鍵是如何選擇的，掃描效果是否更佳。

　　● 是否有許多重複值。

　　對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該資料的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除資料的進程也會有類似的開銷，另外，刪除進程還必須把資料移到頁面上部，以保證資料的連續性。所以，建立非聚簇索引要非常謹慎。非聚簇索引常被用在以下情況:

　　● 某列常用於集合函數(如Sum,....)。

　　● 某列常用於join,order by,group by。

　　● 查尋出的資料不超過表中資料量的20%。

非叢集索引的頁級頁中包括的指標類型將取決於非叢集索引是構建在堆上還是叢集索引上。、
  建立在堆上的非叢集索引時，指標是由 檔案ID 、頁ID 和資料所在的頁槽號組成的。即（1:85691:10）第一個檔案，85691頁第10條記錄。
  建立在叢集索引上的非叢集索引時，指標值就是資料行的叢集索引索引值。即使用聚集鍵導航叢集索引來檢索索引需要的列。 

覆蓋索引( covering indexes)的使用

　　覆蓋索引是在所有滿足SQL查詢不用到達基本表所需的列上建立的非聚簇索引。即 如果查詢遇到一個索引並且完全不需要引用底層基本表，那麼該索引就是覆蓋索引。
 也可以這樣理解：SQL查詢中不包含任何從非聚簇索引頁面跳到基本表的資料頁面的列，也就是說非聚簇索引頁麵包含了所有要查詢的資料，不必再到資料頁面中尋找資料。 如果非聚簇索引中包含結果資料,那麼它的查詢速度將快於聚簇索引。

　　但是由於覆蓋索引的索引項目比較多,要佔用比較大的空間。而且update 操 作會引起索引值改變。所以如果潛在的覆蓋查詢並不常用或不太關鍵，則覆蓋索引的增加反而會降低效能。

 也可以這樣理解覆蓋索引： 非叢集索引頁中包含了叢集索引索引值和其他列（比如說要查詢列）的值。這一查詢的速度就比在用外鍵索引（或索引）查詢來的更快。因為它會直接從非叢集索引頁中取出這些列值。
 實際上是減少了兩個步驟(我個人的理解)：
 1、因為根據索引找到索引頁，而該索引頁中包含了叢集索引鍵。然後根據叢集索引鍵（每一個主鍵）尋找真實的行所在的頁面位置。
 2、然後從頁面的對應位置中取出藥查詢的列。
 覆蓋索引的案例如：
  create index fugai_index on mytable(ProductID) INCLUDE (COLUMN1,COLUMN2)
 覆蓋索引列的值是存放在記憶體中的,如果覆蓋索引中包括過多的列會消耗記憶體.這是其不足點.

 覆蓋索引：覆蓋索引能夠使得語句不需要訪問表僅僅訪問索引就能夠得到所有需要的資料。
  因為叢集索引葉子節點就是資料所以無所謂覆蓋與否，所以覆蓋索引主要是針對非叢集索引而言。
  覆蓋索引要求包含SELECT 子句和where子句做所有的欄位。Where語句中的欄位在前面，select中的在後面。

 

過濾(篩選)索引

 例如：  CRATE NONCLUSTERED INDEX IX_WHERE_QTY ON Production.product(qty) where qty is null; --where是必須的。建立過濾索引的標準格式。

 為了能夠索引具有零散資料分布的欄位或稀疏的列，在索引上引用過濾，這樣就只搜尋一些資料。即 使用過濾器的非聚簇索引，用來在可能沒有很好選擇性的一個或多個列上建立一個高選擇性的關鍵字組。
 例如：某個列上有很多NULL值。 為了調高查詢效率 則可以在該列上建立 索引，使查詢形成 覆蓋索引。雖然會 執行 掃描操作，但是會減少邏輯讀、CPU時間、I/O競爭。這也是一種提高查詢效能的方法。
 建立過濾索引的前提條件是 ：
  ANSI_NULLS=ON,ANSI_PADDING=ON,ANSI_WARNINGS=ON,ARITHABORT=ON,CONCAT_NULL_YIELDS_NULL=ON,QUOTED_IDENTIFIER=ON
  NUMERIC_ROUNDABORT=OFF

 
 由於表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引片段，如果索引片段嚴重，那掃描索引的時間就會變長，甚至導致索引不可用，因此資料檢索操作就慢下來了。

什麼是索引片段

　　有兩種類型的索引片段：內部片段和外部片段。

　　內部片段：為了有效利用記憶體，使記憶體產生更少的片段，要對記憶體分頁，記憶體以頁為單位來使用，最後一頁往往裝不滿，於是形成了內部片段。

　　外部片段：為了共用要分段，在段的換入換出時形成外部片段，比如5K的段換出後，有一個4k的段進來放到原來5k的地方，於是形成1k的外部片段。

如何知道是否發生了索引片段?
 SELECT object_name(dt.object_id) Tablename,si.name as IndexName,
  dt.avg_fragmentation_in_percent AS ExternalFragmentation,
  dt.avg_page_space_used_in_percent AS InternalFragmentation
　　FROM
　　( SELECT object_id,index_id,avg_fragmentation_in_percent,
　　   avg_page_space_used_in_percent
　　  FROM sys.dm_db_index_physical_stats (db_id(N'Test'),NULL,NULL,NULL,NULL)
　　   WHERE index_id <> 0) AS dt
　　 INNER JOIN sys.indexes si ON si.object_id=dt.object_id
　　   AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
　　   AND dt.avg_page_space_used_in_percent<75
　　   ORDER BY avg_fragmentation_in_percent DESC

ExternalFragmentation的值>10表示對應的索引發生了外部片段.
InternalFragmentation的值<75表示對應的索引發生了內部片段。

如何整理索引片段?

　　有兩種整理索引片段的方法：

　　1)重組有片段的索引：執行下面的命令

　　ALTER INDEX ALL ON TableName REORGANIZE

　　2)重建索引：執行下面的命令

　　ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=100,ONLINE=ON)

　　也可以使用索引名代替這裡的“ALL”關鍵字重組或重建單個索引，也可以使用SQL Server管理工作台進行索引片段的整理。即 選擇對應的索引 ,點滑鼠右鍵選擇 重建或重新組織。DBCC INDEXDEFRAG 和 DBCC DBREINDEX 是清除clustered和nonculstered索引片段的兩個命令。INDEXDEFRAG是一種線上操作（也就是說，它不會阻塞其它表格動作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數情況下，重建索引可以更好的消除片段，但是這個優點是以阻塞當前發生在該索引所在表格上其它動作為代價換取來得。當出現較大的片段索引時，INDEXDEFRAG會花上一段比較長的時間，這是因為該命令的運行是基於小的互動塊（transactional
block）。

什麼時候用重組，什麼時候用重建呢?

　　當對應索引的外部片段值介於10-15之間，內部片段值介於60-75之間時使用重組，其它情況就應該使用重建。

　　值得注意的是重建索引時，索引對應的表會被鎖定，但重組不會鎖表，因此在生產系統中，對大表重建索引要謹慎，因為在大表上建立索引可能會花幾個小時，幸運的是，從SQL Server 2005開始，微軟提出了一個解決辦法，在重建索引時，將ONLINE選項設定為ON，這樣可以保證重建索引時表仍然可以正常使用。

　　雖然索引可以提高查詢速度，但如果你的資料庫是一個事務型資料庫，大多數時候都是更新操作，更新資料也就意味著要更新索引，這個時候就要兼顧查詢和更新操作了，因為在OLTP資料庫表上建立過多的索引會降低整體資料庫效能。

　　如果你的資料庫是事務型的，平均每個表上不能超過5個索引，如果你的資料庫是資料倉儲型，平均每個表可以建立10個索引都沒問題。
　　

索引的選擇

 選擇什麼樣的索引基於使用者對資料的查詢條件,這些條件體現於where從句和join運算式中。一般來說建立索引的思路是

　　(1)、主鍵時常作為where子句的條件，應在表的主鍵列上建立聚簇索引，尤其當經常用它作為串連的時候。
　　(2)、有大量重複值且經常有範圍查詢和排序、分組發生的列，或者非常頻繁地被訪問的列，可考慮建立聚簇索引。
　　(3)、經常同時存取多列，且每列都含有重複值可考慮建立複合索引來覆蓋一個或一組查詢，並把查詢引用最頻繁的列作為前置列，如果可能盡量使關鍵查詢形成覆蓋查詢。
　　(4)、如果知道索引鍵的所有值都是唯一的，那麼確保把索引定義成唯一索引。
　　(5)、在一個經常做插入操作的表上建索引時，使用fillfactor(填滿因數)來減少頁分裂，同時提高並發度降低死結的發生。如果在唯讀表上建索引，則可以把fillfactor置為100。
　　(6)、在選擇索引鍵時，設法選擇那些採用小資料類型的列作為鍵以使每個索

　　引頁能夠容納儘可能多的索引鍵和指標，通過這種方式，可使一個查詢必須遍曆的索引頁面降到最小。此外，儘可能地使用整數為索引值，因為它能夠提供比任何資料類型都快的訪問速度。

 重建索引

　　隨著資料行的插入、刪除和資料頁的分裂，有些索引頁可能只包含幾頁資料，另外應用在執行大塊I/O的時候，重建非聚簇索引可以降低分區，維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引：

　　(1)、資料和使用模式大幅度變化。
　　(2)、排序的順序發生改變。
　　(3)、要進行大量插入操作或已經完成。
　　(4)、使用大塊I/O的查詢的磁碟讀次數比預料的要多。
　　(5)、由於大量資料修改，使得資料頁和索引頁沒有充分使用而導致空間的使用超出估算。
　　(6)、dbcc檢查出索引有問題。

　　當重建聚簇索引時,這張表的所有非聚簇索引將被重

索引統計資訊的更新

　　當在一個包含資料的表上建立索引的時候，SQL Server會建立分布資料頁來存放有關索引的兩種統計資訊：分布表和密度表。最佳化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統計資訊並不動態地重新計算。這意味著，當表的資料改變之後，統計資訊有可能是過時的，從而影響最佳化器追求最有工作的目標。因此，在下面情況下應該運行update statistics命令：

　　(1)、資料行的插入和刪除修改了資料的分布。
　　(2)、對用truncate table刪除資料的表上增加資料行。
　　(3)、修改索引列的值。

使用系統產生的主鍵 作為叢集索引或需要作為常用查詢條件的列作為叢集索引列

小型表不要設定索引值(索引鍵)

使用彙總索引內的時間段，搜尋時間會按資料占整個資料表的百分比成比例減少，而無論彙總索引使用了多少個

用彙總索引比用一般的主鍵作order by時速度快，特別是在小資料量情況下

用彙總索引比用不是彙總索引的主鍵速度快

把所有需要提高查詢速度的欄位都加進叢集索引，以提高查詢速度

將彙總索引建立在日期列上.

每個表最好不要建超過5個索引。

對 ORDER BY 或 GROUP BY 子句中指定的列進行索引

頻繁更改的列 不要建索引，因為這樣會增加SQLSERVER的維護工作量和增加磁碟空間。

經常被分組排序的列、外鍵列、主鍵列、聯結操作所涉及到的列、返回某個範圍內的資料列 都應該建立叢集索引或非叢集索引。

當插入的資料為資料表中的記錄數量的10%以上，首先需要刪除該表的索引來提高資料的插入效率，當資料插入後，再建立索引。

避免在索引列上使用函數或計算，在where子句中，如果索引是函數的一部分，最佳化器將不再使用索引而使用全表掃描.例如:
低效：select * from dept where sal*12 >2500;
高效：select * from dept where sal>2500/12;

避免在索引列上使用not和 “!=”，索引只能告訴什麼存在於表中，而不能告訴什麼不存在於表中，當資料庫遇到not 和 “!=”時，就會停止使用索引而去執行全表掃描。

索引列上>=代替>
 低效：select * from t_a  where deptno > 3
 高效：select * from t_a where deptno >=4
兩者的區別在於，前者dbms將直接跳到第一個deptno等於4的記錄，而後者將首先定位到deptno等於3的記錄並且向前掃描到第一個deptno大於3的。

不使用索引的情況：NOT IN、NOT BETWEEN、LIKE（第一個字元非%號除外，如name like '趙%'）、<>、IS NULL/IS NOT、NULL、查詢的欄位加函數

有大量重複值、且經常有範圍查詢（between, >,< ，>=,< =）和order by、group by發生的列，考慮建立叢集索引；

經常同時存取多列，且每列都含有重複值可考慮建立複合式索引；

索引中不包括一個或幾個待排序的列；
group by或order by子句中列的次序與索引的次序不一樣；