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

---

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

點擊下載SQL Prompt正式版

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

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

堆和SQL Server

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

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

通過重復(fù)更新，您會因碎片而導(dǎo)致性能損失。如果堆需要進行碎片整理，這很好地表明應(yīng)通過添加聚簇索引將其轉(zhuǎn)換為表。

SQL Server中的聚集索引

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

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

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

不要混淆主鍵和聚集索引

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

當(dāng)您通?；谒饕滴ㄒ坏闹麈I選擇行時（例如，基于值選擇行時），聚集索引對于主鍵效果很好。它對于一系列主鍵值也很有效，因為表行在存儲中將彼此相鄰?？赡芤黄鸩樵兊男写鎯υ谝黄?/span>

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

比較堆和表的靜態(tài)數(shù)據(jù)

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

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

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

為了說明這一點，我創(chuàng)建了一個包含業(yè)務(wù)目錄的四百萬行表，并將其存儲在堆和表中。如果要重現(xiàn)測試，我已經(jīng)為bigdirectory表提供了構(gòu)建腳本和一個SQL Data Generator項目XML文件，以填充400萬行（可以根據(jù)需要降低它）。它實際上是.sqlgen文件類型，但已重命名為.xml擴展名。您需要編輯DataSourceXML項目文件以提供正確的連接和數(shù)據(jù)庫詳細(xì)信息，然后將其另存為.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中提取埃塞克斯郡所有行的所有列，其業(yè)務(wù)范圍以“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片段將其放入測試工具。

當(dāng)堆設(shè)法在七分之一的時間內(nèi)將數(shù)據(jù)寫入臨時表時，其性能優(yōu)于表，盡管實際的行標(biāo)識花費了相同的時間。當(dāng)然，在此示例中，從理論上講，該查詢應(yīng)該不需要去表以獲取數(shù)據(jù)，因為可以從索引中獲取結(jié)果。

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

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

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

既然堆必須通過RID查找從行中獲取數(shù)據(jù)，它的優(yōu)點已經(jīng)減少，但仍然比表快三倍。

總結(jié)

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

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

相關(guān)內(nèi)容推薦：

試用下載>>>

SQL Prompt使用教程>>>

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊【咨詢在線客服】