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  ASBEGIN    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 datetimeSET @d=getdate()print '開始執行預存程序...'EXEC pro_Count_Test; SELECT [預存程序執行花費時間(毫秒)]=datediff(ms,@d,getdate())

 

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

 

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

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

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

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

 

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

 

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

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

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

DECLARE @d datetimeSET @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