SQL語法提示工具SQL Prompt使用教程：表沒有聚簇索引（BP021）

---

SQL Prompt是一款實用的SQL語法提示工具。SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt正式版

如果SQL Prompt提醒您注意沒有聚集索引的表，請仔細調查其不存在的原因。確實很少有一個表可以在沒有表的情況下更快地進行數據檢索。

除少數例外，每個表都應具有聚集索引。但是，它們并非始終對性能至關重要。聚集索引的值取決于表的使用方式，查詢的典型模式以及表的更新方式。對于表更重要的是它應該具有適當的主鍵。如果您不能解釋避免在表上使用聚集索引的充分理由，那么擁有一個索引要安全得多。除非您確切知道該表的使用方式，否則很難找到充分的理由。

堆和SQL Server

堆是沒有聚簇索引的表，在SQL Server中被視為表的頑皮姐妹，并且在過去，它們通常都能達到其聲譽。例如，在以前的SQL Server版本中，無法重建索引。由于轉發指針，表的插入和刪除操作會增加查詢響應時間。

創建堆時，各個記錄沒有任何邏輯順序。因此，要查找特定記錄，SQL Server將需要對行的引用（物理RID），或者需要全表掃描才能找到該記錄。要獲取該RID，查詢必須使用非聚集索引。非聚集索引存儲堆中每個記錄的物理RID。

通過重復更新，您會因碎片而導致性能損失。如果堆需要進行碎片整理，這很好地表明應通過添加聚簇索引將其轉換為表。

SQL Server中的聚集索引

關系理論中沒有聚集索引之類的東西。但是，任何主要的RDBMS（例如SQL Server或Oracle）都將擁有它們。聚集索引在SQL Server中特別重要。從技術上講，沒有聚集索引的表不是表，而是“堆”。未索引的堆僅對我們幾乎不需要讀取的日志有效。一個索引良好的堆可以像表一樣執行。

對于一個使用率很高的OLTP數據庫系統來說，它發生了很多變化，并且進行了許多快速，簡單的查詢，因此聚集索引成為顯而易見的選擇。聚簇索引用于組織表，而非聚簇索引用于支持查詢。聚集索引鍵應為“窄”，“唯一”，“靜態”和“不斷增加”（NUSE）。這樣，我們的意思是所選列的各個行應占用盡可能少的存儲空間，因為聚集索引也用于非聚集索引查找中。每行都必須是唯一的或盡可能接近。列中的值不應該更改。如果聚簇索引不斷增加，這將有很大幫助，因此行以聚簇索引的升序排列。如果新行在聚簇索引鍵中始終具有較高的值，則無需在序列中插入行，只需在末尾添加即可。插入在存儲中以邏輯順序發生，并且將避免頁面拆分。

盡管遞增IDENTITY列通常作為密鑰，但這并不總是最佳選擇。例如，太多的交易數據是基于日期和時間的，因此date列成為更自然的選擇，尤其是因為日期通常用于過濾數據時。但是，聚類索引的選擇很大程度上取決于使用模式和需要快速插入的“熱點”的出現。

不要混淆主鍵和聚集索引

主鍵是邏輯構造，聚簇索引是物理上存儲數據的特殊方式。通常選擇聚集索引來體現主鍵。通過為密鑰指定聚簇索引，可以確定密鑰的實現方式。主鍵很可能可以通過聚簇索引鍵最有效地實現，但不是必須的。

當您通常基于索引值唯一的主鍵選擇行時（例如，基于值選擇行時），聚集索引對于主鍵效果很好。它對于一系列主鍵值也很有效，因為表行在存儲中將彼此相鄰。可能一起查詢的行存儲在一起

一個表只能有一個聚集索引，因此您需要仔細選擇。具有邏輯意義的候選鍵作為主鍵并不一定具有性能良好的聚集索引所需的特征。

比較堆和表的靜態數據

聚集索引最適用于可能選擇主要是單個值的查詢，或者需要從不屬于主鍵的列中返回數據的查詢。如果類別列具有聚集索引，則它們對于通常在類別中選擇未排序或已排序范圍的查詢非常有效。

如果總是通過非聚集索引訪問數據，有時最好避免使用聚集索引。這通常是因為對堆中實際行的引用（RID或行標識符）可能小于聚集索引鍵。通常為幾乎從未讀取，必須快速寫入且永不更新的日志表選擇堆。

索引堆的另一個明顯用途是將表中的數據很少進行增量更改，例如目錄。沒有“自然”順序并經常更新的表有時也可以作為堆更好地工作。

為了說明這一點，我創建了一個包含業務目錄的四百萬行表，并將其存儲在堆和表中。如果要重現測試，我已經為bigdirectory表提供了構建腳本和一個SQL Data Generator項目XML文件，以填充400萬行（可以根據需要降低它）。它實際上是.sqlgen文件類型，但已重命名為.xml擴展名。您需要編輯DataSourceXML項目文件以提供正確的連接和數據庫詳細信息，然后將其另存為.sqlgen文件。

<DataSource version="4" type="LiveDatabaseSource">
 <ServerName>MyServerName</ServerName>
 <DatabaseName>MyDatabase</DatabaseName>
 <Username />
 <SavePassword>False</SavePassword>
 <Password />
 <ScriptFolderLocation />
 <MigrationsFolderLocation />
 <IntegratedSecurity>True</IntegratedSecurity>
 </DataSource>

這兩個表都給出了合適的覆蓋索引：

CREATE NONCLUSTERED INDEX CountyBusinessTypeCovering
  ON dbo.BigDirectory (county, BusinessType)
  INCLUDE
    (Name, Address1, Address2, town, city, Postcode, Region,
    Leads, Phone, Fax, Website );

我們要從這400萬行表BigDirectory及其邪惡的堆雙胞胎HeapBigDirectory中提取埃塞克斯郡所有行的所有列，其業務范圍以“R”開頭。

DECLARE  @bucket INT
  SELECT --count of filtered result from heap
    @bucket = Count(*)
    FROM Heapbigdirectory
    WHERE county = 'essex' AND businessType LIKE 'r%';
  SELECT --count of filtered result from table
    @bucket = Count(*)
    FROM bigdirectory
    WHERE county = 'essex' AND businessType LIKE 'r%';
  SELECT --heap, get all columns
    *
    INTO #sometempTable
    FROM Heapbigdirectory
    WHERE county = 'essex' AND businessType LIKE 'r%';
  SELECT --table, get all columns
    *
    INTO #othertempTable
    FROM bigdirectory
    WHERE county = 'essex' AND businessType LIKE 'r%';
  DROP TABLE #sometempTable
  DROP TABLE #othertempTable

我們使用SQL Prompt chk片段將其放入測試工具。

當堆設法在七分之一的時間內將數據寫入臨時表時，其性能優于表，盡管實際的行標識花費了相同的時間。當然，在此示例中，從理論上講，該查詢應該不需要去表以獲取數據，因為可以從索引中獲取結果。

因此，讓我們從表中刪除非聚集索引，然后在每個不再覆蓋的堆上創建一個新索引。

CREATE NONCLUSTERED INDEX CountyBusinessType ON dbo.BigDirectory
  (county, BusinessType);
  CREATE NONCLUSTERED INDEX CountyBusinessType ON dbo.HeapBigDirectory
  (county, BusinessType);

我們使用僅支持過濾器的查詢，而保留通過RID或聚集索引來完成數據收集

既然堆必須通過RID查找從行中獲取數據，它的優點已經減少，但仍然比表快三倍。

總結

真正的代碼味道不是缺少聚集索引，而是查詢中引用的列上根本沒有任何索引。如果您可以擁有一個具有Narrow，Unique，Static和Ever-Increating鍵的不錯的聚集索引，那么您可以合理地確信該表可以處理使用非聚集索引列進行過濾的任何查詢，尤其是當索引覆蓋。

為了使某個堆在任何特定查詢中的性能都更高，非聚集索引必須覆蓋在JOIN或WHEREfilter子句中使用的所有列，并且該表本質上必須是靜態的。

相關內容推薦：

試用下載>>>

SQL Prompt使用教程>>>

---

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊