SQL Server DBA調優日記（一）——大資料量查詢記錄數優化及原理探討

問題描述

 

生產庫中一張表的資料10億級別，另一張表資料100億級別，還有其他表的資料也是相當地龐大。入職之前不知道這些表有那麼大的資料量，於是習慣了使用count(*)來統計表的記錄數。但這一執行就不得了，跑了30多分鐘都沒出結果，最後只有取消查詢。後來採取了另一種辦法查詢記錄數。首先說明下解決的辦法，使用如下SQL：

SELECT object_name(id) as TableName,indid,rows,rowcnt  
FROM sys.sysindexes WHERE id = object_id('TableName') 
and indid in (0,1);

問題模擬

 

接著我做了一個模擬，並且試著從原理的角度分析下使用count(*)和查詢sysindexes檢視為什麼會出現那麼大的差距。

 

我們做模擬之前首先要得測試資料。所以我建立一個了測試表，並且插入測試資料。這裡插入1億條資料。

建立測試表的語句如下：

DROP TABLE count_Test;
CREATE TABLE count_Test
(
       id bigint,
       name VARCHAR(20),
       phoneNo VARCHAR(11)
);

      

由於插入大量資料，我們肯定不能手動來。於是我寫了一個儲存過程，插入1億條資料。為了模擬出資料的複雜性，資料我採用隨機字串的形式。插入測試資料的儲存過程如下：

CREATE PROCEDURE pro_Count_Test  
AS
BEGIN
    SET STATISTICS IO ON;
    SET STATISTICS TIME ON;
    SET NOCOUNT ON;
    WITH Seq(id,name,phoneNo) AS
    (
        SELECT 1,cast('13'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(20)),
        cast('name_'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(40))
        UNION ALL
        SELECT id+1,cast('13'+right('000000000'+ cast(cast(rand(checksum(newid()))*100000000 AS int) 
        AS varchar),9) AS VARCHAR(20)),
        cast('name_'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(40))
        FROM Seq
        WHERE id <= 100000000
    )
    INSERT INTO count_Test(id,name,phoneNo)
    SELECT id,name,phoneNo
    FROM Seq
    OPTION (MAXRECURSION 0)
    SET STATISTICS IO OFF ;
    SET STATISTICS TIME OFF;
END

接著我們執行此儲存過程，插入測試資料。SQL Server Management Studio在輸出視窗的右下角記錄了操作的時間。為了更直觀，我們手動寫了個記錄時間的語句，如下：

DECLARE @d datetime
SET @d=getdate()
print '開始執行儲存過程...'
EXEC pro_Count_Test;
 
SELECT [儲存過程執行花費時間(毫秒)]=datediff(ms,@d,getdate())

 

好了，等待47分29秒，資料插入完畢，插入資料的統計資訊如圖一，佔用資料空間如圖二，我們開始測試count(*)和sysindexes在效率上的差別。

 

圖一 插入1億行資料統計資訊

圖二 插入1億行資料佔用空間

在沒有任何索引的情況下使用count(*)測試，語句如下：

DECLARE @d datetime
SET @d=getdate()
SELECT COUNT(*) FROM count_Test;
SELECT [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

 

測試時記憶體使用率一度飆到96%，可見效率是極低的。測試結果用時1分42秒，如圖三，我們檢視此時的執行計劃，如圖四。可以清晰地看到此時走的是全表掃描，並且絕大多數的開銷都花銷在這上面。

 

圖三 無索引使用count(*)執行時間

圖四 無索引使用count(*)執行計劃

在沒有任何索引的情況下使用sysindexes測試，語句如下：

DECLARE @d datetime
SET @d=getdate()
SELECT object_name(id) as TableName,indid,rows,rowcnt 
FROM sys.sysindexes WHERE id = object_id('count_Test') 
and indid in(0,1);
SELECT [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

測試結果用時450毫秒，如圖五。我們檢視此時的執行計劃，如圖六。可以看到此時走的是聚集索引掃描，並且全部的開銷都在此。

 

圖五 無索引使用使用sysindexes執行時間

圖六 無索引使用使用sysindexes執行計劃

在沒有索引的情況下測試完畢，我們開始測試有索引的情況。首先，我們在ID列上建立普通索引。語句如下：

CREATE INDEX idx_nor_count_test_id ON count_Test(id);

建立普通索引時記憶體使用率、CPU利用率都相當地高，一讀達到97%。建立普通索引用時34分58秒，資料檔案磁碟佔用空間為6.71G (7046208K),日誌檔案無變化。執行計劃如圖七：

 

圖七 建立普通索引執行計劃

在有普通索引的情況下使用count(*)測試，語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時1分09秒，比沒有使用索引速度要快。我們檢視此時的執行計劃，如圖八。可以看到此時走非聚集索引掃描，開銷主要在此。

 

圖八 普通索引使用count(*)執行計劃

在有普通索引的情況下使用sysindexes測試，語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時290毫秒，也比沒有索引時用時少。我們檢視此時的執行計劃，如圖九，可以看到執行計劃未變。

 

圖九 普通索引使用sysindexes執行計劃

普通索引測試完畢，現在我們測試聚集索引。刪除普通索引，在id列上建立聚集索引，語句如下：

DROP INDEX idx_nor_count_test_id ON count_Test;
CREATE CLUSTERED INDEX idx_clu_count_test_id ON count_Test(id);

 

建立聚集索引用時25分53秒。資料檔案佔用9.38G（9839680K）。

 

在有聚集索引的情況下，使用count(*)測試，語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時4分08秒，我們檢視此時的執行計劃，如圖十。可以看到此時走聚集索引，開銷主要花銷在此。

 

圖十 聚集索引使用count(*)測試

在有聚集索引的情況下，使用sysindexes測試。語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時790毫秒，我們檢視此時的執行計劃，如圖十一。執行計劃不變。

 

圖十一 聚集索引使用sysindexes測試

聚集索引測試完畢，現在我們開始測試非聚集索引。刪除聚集索引，建立非聚集索引，語句如下：

DROP INDEX idx_clu_count_test_id ON count_Test.id;
CREATE NONCLUSTERED INDEX idx_nonclu_count_test ON count_Test(id);

 

刪除聚集索引用時16分37秒。建立非聚集索引用時時40分20秒，資料檔案佔用空間9.38G （9839680K）。

 

在有非聚集索引的情況下，使用count(*)測試。語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時6分59秒，我們檢視此時的執行計劃，如圖十二。此時走非聚集索引，開銷主要在此。

 

圖十二 非聚集索引使用count(*)測試

在有非聚集索引的情況下，使用sysindexes測試。語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時413毫秒，我們檢視此時的執行計劃，如圖十三。執行計劃不變。

 

圖十三 非聚集索引使用sysindexes測試

接著我們做一個組合測試，包括有普通索引和聚集索引的情況、有普通索引和非聚集索引的情況、有普通索引、聚集索引和非聚集索引的情況。首先測試有普通索引和聚集索引的情況，我們首先刪除非聚集索引，然後建立普通索引和聚集索引，語句如下：

DROP INDEX idx_nonclu_count_test ON count_Test.id;
CREATE INDEX idx_nor_count_test_id ON count_Test(id);
CREATE CLUSTERED INDEX idx_clu_count_test_id ON count_Test(id);

刪除用時1秒，空間不變。建立聚集索引和普通索引索引用時1:57:27，資料檔案佔用空間12.9G （13541440 ）。

 

在有普通索引和聚集索引的情況下，使用count(*)測試。語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時5分27秒，我們檢視此時的執行計劃，如圖十四。此時走普通索引，開銷主要在此。

 

圖十四 聚集索引、普通索引使用count(*)測試

在有普通索引和聚集索引的情況下，使用sysindexes測試。語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時200毫秒，我們檢視此時的執行計劃，如圖十五，執行計劃不變。

 

圖十五 聚集索引、普通索引使用sysindexes測試

接著測試有普通索引和非聚集索引的情況，我們刪除聚集索引，建立非聚集索引，語句如下：

DROP INDEX idx_clu_count_test_id ON count_Test.id;
CREATE NONCLUSTERED INDEX idx_nonclu_count_test ON count_Test(id);

刪除普通索引用時1:23:10，建立非聚集索引用時6分50秒，資料檔案空間佔用12.9G。

 

在有普通索引和非聚集索引的情況下，使用count(*)測試。語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時52秒，我們檢視此時的執行計劃，如圖十六。此時走非聚集索引，開銷主要在此。

 

圖十六 非聚集索引、普通索引使用count(*)測試

在有普通索引和非聚集索引的情況下，使用sysindexes測試。語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時203毫秒，我們檢視此時的執行計劃，如圖十七。執行計劃不變。

 

圖十七 非聚集索引、普通索引使用sysindexes測試

最後，測試有普通索引、聚集索引和非聚集索引的情況。我們建立普通索引，語句如下：

CREATE NONCLUSTERED INDEX idx_nonclu_count_test ON count_Test(id);

 

建立普通索引用時1:11:21，資料檔案佔用空間16.3G（17116224KB）。

 

在有普通索引、聚集索引和非聚集索引的情況下，使用count(*)測試。語句和沒有任何索引的情況下使用count(*)測試相同。測試結果用時2分51秒，我們檢視此時的執行計劃，如圖十八。此時走非聚集索引，開銷主要在此。

 

圖十八 普通索引、聚集索引、非聚集索引使用count(*)測試

在有普通索引、聚集索引和非聚集索引的情況下，使用sysindexes測試。語句和沒有任何索引的情況下使用sysindexes測試相同。測試結果用時203毫秒，我們檢視此時的執行計劃，如圖十九。執行計劃不變。

 

圖十九 普通索引、聚集索引、非聚集索引使用sysindexes測試

加入indid大於1用時86毫秒，執行計劃如圖二十；加入indid等於1用時23毫秒，執行計劃如圖二十一。所有的測試完成後，資料檔案和日誌檔案佔用空間如圖二十二。

圖二十 加入indid大於1執行時間

圖二十一 加入indid等於1執行時間

圖二十二 所有的測試完成後，資料檔案和日誌檔案佔用空間

我們可以看出以上幾種方式在效率上簡直是天壤之別。count(*)不管在有什麼索引的情況下都較慢，而sysindexes相對快多了。

 

原理分析

 

好了，接著我試著分析count(*)和sysindexes為什麼會有那麼大的差距。首先我查了下幫助文件，裡面對sys.indexes 是這樣介紹的：Contains one row for eachindex and table in the current database. XML indexes are not supported in thisview. Partitioned tables and indexes are not fully supported in this view; usethe sys.indexes catalog view instead.（當前資料庫中的每個索引和表各對應一行。此檢視不支援 XML 索引。此檢視不完全支援分割槽表和索引；請改用 sys.indexes 目錄檢視）。在MS SSQL資料庫中，每個資料表都在sys.sysindexes系統表中擁有至少一條記錄，記錄中的rows 或rowcnt欄位會定時記錄表的記錄總數。請注意是定時，而不是實時，這說明了用這個方法得到的總記錄數並不是一個精確值，原因是MS SQL並不是實時更新該欄位的值，而是定時更新，但從實踐來看該值和精確值誤差不大，如果你希望快速粗略估算表的記錄總數，建議你採用該方法。如果您希望檢視實時的記錄數，可以先執行DBCC UpdateUSAGE(DatabaseName,[TABLENAME])WITH ROW_COUNTS 強制更新該欄位的值，再使用該SQL進行查詢，這樣得到的值就是實時的記錄數。

 

在CBO的基礎上，count(*)統計記錄數是這樣的：當對資料進行查詢時，得到一條資料則對應的記錄數加1，直到返回總共的記錄數。在沒有索引的情況下，count(*)則是Table Full Scan，也就是全表掃描，對於資料量大的表，全表掃描速度肯定慢，這一點是毋庸置疑的。如果有索引，那麼會使用INDEX SCAN，速度相對較快。那如果使用count(*)統計記錄數並且想使返回記錄的時間變短，我們可以在表上建立聚集索引。普通索引可以在多個欄位上建立，但是聚集索引一張表中只能建立一個，顯然我們不能輕率地使用聚集索引。聚集索引怎麼理解呢？我們可以把聚集索引想象成字典的拼音索引，這樣查詢單詞的速度就會快很多。那問題來了，如果建立聚集索引，什麼時候走索引，什麼時候不走呢？如果單獨的沒有WHERE條件的SELECT count(*)語句想要用上索引，那麼必須滿足以下兩個條件，第一個是CBO，第二個是存在NOT NULL屬性的列。如果WHERE條件裡面僅存在索引列，而不使用一些資料庫內建函式或者其他連線條件，一般都會走索引。還有一個問題，為什麼聚集索引快？索引是通過平衡樹的結構進行描述，聚集索引的葉節點就是最終的資料節點，而非聚集索引的葉節仍然是索引節點，但它有一個指向最終資料的指標。在有聚集索引的情況下，非聚集索引的葉子節點存放的是聚集索引的鍵。在沒有聚集索引的情況下，存放的是一個bookmark，結構是：File ID:Page ID:Row ID。所以，當一張表有聚集索引時，查詢的速度會變得很快。綜上，在沒有索引的情況下count(*)走的是全表掃描，速度慢。

 

現在問題又來了，為什麼使用sysindexes速度會很快？索引是為檢索而存在的，就是說索引並不是一個表必須的。表索引由多個頁面組成，這些頁面一起組成了一個樹形結構，即我們通常說的B樹（平衡樹），首先來看下錶索引的組成部分：根極節點，root，它指向另外兩個頁，把一個表的記錄從邏輯上分成非葉級節點Non-Leaf Level（枝），它指向了更加小的葉級節點Leaf Level（葉）。根節點、非葉級節點和葉級節點都位於索引頁中，統稱為索引葉節點，屬於索引頁的範籌。這些“枝”、“葉”最終指向資料頁Page。根級節點和葉級節點之間的葉又叫資料中間頁。根節點對應了sysindexes表的root欄位，記載了非葉級節點的物理位置（即指標）；非葉級節點位於根節點和葉節點之間，記載了指向葉級節點的指標；而葉級節點則最終指向資料頁，這就是最後的B樹。sysindexes中我們需要關注root欄位和indid欄位。我們看下官方文件中對這兩個欄位的解釋，如圖二十三：

 

圖二十三 官方文件對root欄位和indid欄位的解釋

從上圖中我們知道，索引ID為0表示堆，也就是在沒有索引下所做的全表掃描；為1是表示聚集索引，大於1表示非聚集索引。root欄位在全表掃描時是不會使用到的，而只有在有索引的情況下才使用。聚集索引中，資料所在的資料頁是葉級，索引資料所在的索引頁是非葉級。由於記錄是按聚集索引鍵值進行排序，即聚集索引的索引鍵值也就是具體的資料頁。訪問有聚集索引的表，步驟是這樣的：首先在sysindexes表查詢INDID值為1，說明表中建立了聚集索；然後從根出發，在非葉級節點中定位最接近1的值，也就是枝節點，再查到其位於葉級頁面的第n頁；在葉級頁面第n頁下搜尋值為1的條目，而這一條目就是資料記錄本身；將該記錄返回客戶端。同樣，我們查詢某張表有多少記錄數，我們使用到的刪選條件是indid in (0,1)，也就是把普通表（這裡指沒有聚集索引的表）和有聚集索引的表都查詢到。由於sysindexes記錄了每張表的記錄數，無論該表是普通表還是有聚集索引的表，都可以很快地把返回結果。如上所述，這個數值並不一定準確，至於你想獲得真實記錄數，還是初略記錄數，這就看你獲得記錄數的需求是什麼。

 

綜上所述，count(*)在沒有索引的情況下速度慢的原因是走的全表掃描，使用sysindexes速度快的原因是直接從該檢視中得到記錄數。

 

說點題外話，在插入資料時，最開始我採用了WHILE迴圈插入10億條資料，等了兩個多小時還沒插入完，只好停掉，改用CTE插入資料。CTE插入資料的效率很高，資料檔案大小以近2M/s的速度遞增，但是由於資料量太大，也只好停掉，把10億改成1000萬。插入1000萬資料用時4分52秒，資料檔案佔用磁碟空間470M，日誌檔案佔用磁碟空間2.3G，但做統計記錄數時看不到效果，所以改成插入1億條資料。插入1億條資料用時47分29秒，資料檔案佔用磁碟空間4.54G，日誌檔案佔用磁碟空間33.28G。從插入資料的資料量級別我們知道，每多一個數量級，插入資料的時間會成倍地增長，具體多少倍有很多因素影響，比如系統空閒率、機器CPU和IO負載、插入的資料每行佔用空間是否一致等等。這裡還需要搞明白一個問題，那就是為什麼CTE法那麼快？首先我們瞭解下CTE。公用表表示式（Common Table Expression)是SQL SERVER 2005版本之後引入的一個特性。CTE可以看作是一個臨時的結果集，可以在接下來的一個SELECT，INSERT，UPDATE，DELETE，MERGE語句中被多次引用。使用公用表示式可以讓語句更加清晰簡練。本文中的插入示例使用了CTE遞迴查詢。CTE遞迴查詢原理是這樣的：第一步，將CTE表達示拆分為“定位點成員”和“遞迴成員”；第二步，執行定位點成員，執行建立第一個結果集R0；第三步，執行遞迴成員時，將前一個結果集作為輸入(Ri)，將Ri+1作為輸出；第四步，重複第三步，直到返回空集；第五步，返回結果集，通過UNION ALL合併R0 到 Rn的結果。熟知程式設計的讀者清楚，遞迴在程式設計中效率也是極高的。同樣，CTE採用遞迴後插入資料會變得相當得高，從資料檔案的增長速率就可以看出，使用CTE之前資料檔案增長以幾K每秒的速度增長，使用CTE之後，資料檔案以近2M每秒的速度增長。搞清楚CTE為什麼那麼快後，這裡還說下清空日誌檔案的小技巧。我們使用DROP TABLE count_Test後，資料檔案和日誌檔案的空間並不會真正清空，這時如果我們執行DBCC  SHRINKDATABASE(db_test_wgb)（注：db_test_wgb為資料庫名）後，你會發覺資料檔案和日誌檔案從數十G一下變成幾M。這和Oracle中的SHRINK TABLE有幾絲類似。這裡還得著重強調下，不要在生產庫中執行此語句，否則會讓你後悔莫及！切記！

 

最後說明下，本文參考了姜敏前輩的這兩篇文章，軟體開發人員真的瞭解SQL索引嗎(聚集索引)和軟體開發人員真的瞭解SQL索引嗎(索引原理)，還參考了宋沄劍前輩的文章：T-SQL查詢進階--詳解公用表表示式(CTE)。如果想了解索引原理，強烈建議閱讀姜敏前輩的這篇文章：軟體開發人員真的瞭解SQL索引嗎(索引原理)。對於什麼是IAM，讀者可以看下微軟的官方文件，管理物件使用的空間。

  我的郵箱：wgbno27@gmail.com
  新浪微博：@jutdb         
  微信公眾平臺：JustOracle（微訊號：justoracle）
  資料庫技術交流群：336882565（加群時驗證 From CSDN XXX，XXX表示您的CSDN使用者名稱）
  All is well
  2014年4月6日
  By Robin Wen