眾所周知，SQL Server執(zhí)行SQL語句的性能判定標(biāo)準(zhǔn)主要是IO讀取數(shù)大小。本文在不違反這一原則情況下，同時(shí)來分析一下部分SQL語句執(zhí)行時(shí)，SQL Server內(nèi)存的變化情況。
首先簡述一下SQL Server內(nèi)存占用的特點(diǎn)。SQL Server所占用的內(nèi)存除程序(即SQL Server引擎)外，主要包括緩存的數(shù)據(jù)(Buffer)和執(zhí)行計(jì)劃(Cache)。SQL Server以8KB大小的頁為單位存儲(chǔ)數(shù)據(jù)。這個(gè)和SQL Server數(shù)據(jù)在磁盤上的存儲(chǔ)頁大小相同。當(dāng)SQL Server執(zhí)行SQL 語句時(shí)，如果需要的數(shù)據(jù)已經(jīng)在其內(nèi)存中，則直接從內(nèi)存緩沖區(qū)讀取并進(jìn)行必要的運(yùn)算然后輸出執(zhí)行結(jié)果。如果數(shù)據(jù)還未在內(nèi)存中，則首先將數(shù)據(jù)從磁盤上讀入內(nèi)存Buffer中。而我們通常評(píng)價(jià)SQL性能指標(biāo)中的IO邏輯讀取數(shù)對(duì)應(yīng)的正是從內(nèi)存緩沖區(qū)讀取的頁數(shù)，而IO物理讀取數(shù)則對(duì)應(yīng)數(shù)據(jù)從磁盤讀取的頁數(shù)。
注：以下的試驗(yàn)在多人共享的開發(fā)測試服務(wù)器上也可以進(jìn)行，因?yàn)閷?shí)際上可以分別看到某個(gè)表所占用的內(nèi)存情況。但為了方便，筆者在做此試驗(yàn)時(shí)，在一個(gè)單獨(dú)的、確認(rèn)沒有其它并發(fā)任務(wù)的數(shù)據(jù)庫上進(jìn)行，因此所看到的內(nèi)存變化正是每一次所執(zhí)行的SQL語句引起的。
我們首先來看一個(gè)簡單的實(shí)例。創(chuàng)建下表：
以下是引用片段：
Create Table P_User
( UserMobileStatus int NOT NULL,
MobileNo int NOT NULL,
LastOpTime DateTime Not NULL
)
然后為該表插入一定的數(shù)據(jù)：
以下是引用片段：
Declare @i int
Set @i=28000
WHILE @i<29000
BEGIN
Insert Into P_User
Select @i % 2,@i,GetUTCDate()
Set @i=@i+1
END
然后我們?cè)诓樵兎治銎髦惺紫葓?zhí)行:
以下是引用片段：
Set Statistics IO ON
并按下Ctrl+M以顯示實(shí)際的執(zhí)行計(jì)劃。
此時(shí)，可以開始進(jìn)行我們的試驗(yàn)了。為了準(zhǔn)確觀察每一次SQL語句變化情況，在執(zhí)行第一條SQL語句以前，我們首先清空SQL Server所占用的數(shù)據(jù)內(nèi)存：
以下是引用片段：
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
這將清空SQL Server所占用的數(shù)據(jù)緩沖區(qū)(此語句在生產(chǎn)服務(wù)器上慎用，因?yàn)閷?dǎo)致一段時(shí)間內(nèi)后續(xù)的SQL語句執(zhí)行變慢)。
測試1：在沒有索引的表上執(zhí)行SQL語句
1.1 執(zhí)行全表選取或者低選擇性選取
Select * From P_User
從SQL執(zhí)行計(jì)劃可以看到，由于此時(shí)表中沒有任何索引，因此將產(chǎn)生Table Scan。而IO統(tǒng)計(jì)結(jié)果如下：
(1000 row(s) affected)
表'P_User'。掃描計(jì)數(shù)1，邏輯讀取4 次，物理讀取4 次，預(yù)讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預(yù)讀0 次。
我們看一下數(shù)據(jù)庫內(nèi)存中的情況。
首先查詢到我們所操作database的database_id：
以下是引用片段：
Select database_id From sys.databases Where name='TestGDB'
然后使用該database_id從表中查看內(nèi)存情況：
以下是引用片段：
SELECT * FROM sys.dm_os_buffer_descriptors bd
WHERE database_id=5
order by allocation_unit_id,page_id
得到結(jié)果如下：
得到的結(jié)果中可以看到，除了必要的管理頁(一個(gè)PFS_Page和一個(gè)IAM_Page)外，內(nèi)存中總共出現(xiàn)了4個(gè)Data_Page頁。這和剛才IO統(tǒng)計(jì)中看到的結(jié)果：邏輯讀為4，物理讀為4相同。由于是全表讀取，表明P_User表全部數(shù)據(jù)所占用的數(shù)據(jù)頁數(shù)也正是4，將這4個(gè)數(shù)據(jù)頁的row_count數(shù)加起來也可以驗(yàn)證其總數(shù)據(jù)行=1000。
在上例中，如果不清空數(shù)據(jù)緩沖區(qū)，再執(zhí)行一遍SQL，可以看到內(nèi)存毫無變化，而邏輯讀也不變，只是物理讀變?yōu)?，因?yàn)橐呀?jīng)不需要再從磁盤讀入數(shù)據(jù)。
1.2 執(zhí)行高選擇性選取
另外，在沒有索引的情況下，如果將上例修改為：
以下是引用片段：
Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28502
可以看到，系統(tǒng)同樣要讀取全部的數(shù)據(jù)頁到內(nèi)存。
如果使用Select Top 1 * From P_Order Where MobileNo=28502這樣的選取方式，有可能會(huì)出現(xiàn)只讀取部分?jǐn)?shù)據(jù)頁到內(nèi)存的情況。但由于在沒有索引情況下，數(shù)據(jù)實(shí)際上是無序存放在堆上，所以結(jié)果很不穩(wěn)定，也有可能發(fā)生讀取所有的數(shù)據(jù)頁到內(nèi)存。
測試2：建立聚集索引情況下，執(zhí)行SQL語句
2.1 執(zhí)行全表選取或者低選擇性選取
修改表結(jié)構(gòu)，在MobileNo字段上建立聚集索引。然后再次執(zhí)行剛才的SQL語句。得到的執(zhí)行計(jì)劃變?yōu)榫奂饕龗呙?。IO統(tǒng)計(jì)消息為：
(1000 row(s) affected)
表'P_User'。掃描計(jì)數(shù)1，邏輯讀取6 次，物理讀取1 次，預(yù)讀4 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預(yù)讀0 次。
這里的邏輯讀取變?yōu)?次。
內(nèi)存情況如下：
內(nèi)存中的變化是增加了一個(gè)非葉級(jí)的聚集索引頁，而葉級(jí)的聚集索引則會(huì)和數(shù)據(jù)放在一起。
另外，可以查看該表索引的級(jí)別：
以下是引用片段：
SELECT database_id,object_id,index_id,index_level,page_count,record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'TestGDB'), OBJECT_ID(N'dbo.P_User'), NULL, NULL , 'DETAILED');
從結(jié)果可以看到該表的聚集索引總共分2級(jí)。
因而邏輯讀增加了2——(由于發(fā)生Clustered Index Scan，除了根級(jí)別的聚集索引頁占用1次外，從根級(jí)別聚集索引定位到葉級(jí)別的聚集索引也將額外占用1次邏輯讀)。
另外一個(gè)變化是只發(fā)生了一次物理讀，即讀取根級(jí)別的聚集索引頁，另外4個(gè)數(shù)據(jù)頁則通過預(yù)讀方式而不是物理讀從磁盤裝入內(nèi)存Buffer。這使得有聚集索引的情況下，執(zhí)行SQL所直接花費(fèi)的代價(jià)實(shí)際上更小。
2.2 執(zhí)行高選擇性選取
在建立聚集索引情況下，對(duì)性能有益的變化是：
對(duì)于Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28702這樣的語句，在有聚集索引情況下，只會(huì)將最終記錄所在的頁讀入內(nèi)存。
測試3：建立非聚集索引情況下，執(zhí)行SQL語句
3.1 執(zhí)行全表選取或者低選擇性選取
如果將表中同一字段的聚集索引換成非聚集索引，則可以看到如下特點(diǎn)：
執(zhí)行全表掃描將和沒有任何索引的情況相似，將讀取所有的數(shù)據(jù)頁到內(nèi)存。此時(shí),SQL Server的查詢引擎實(shí)際上無法使用非聚集索引。
3.2 執(zhí)行高選擇性選取
將只讀取最終數(shù)據(jù)所在的頁到內(nèi)存。通過查詢計(jì)劃可以看到，SQL Server在非聚集索引上使用INDEX SEEK，然后通過lookup 得到數(shù)據(jù)實(shí)際所在行(索引覆蓋情況下例外，因?yàn)椴恍枰ㄎ坏綄?shí)際數(shù)據(jù)行)。
測試4：執(zhí)行Nested Loop Join
在進(jìn)行測試前,我們先準(zhǔn)備另外一張表和數(shù)據(jù)。
以下是引用片段：
Create Table P_Order
( UserStatus int NOT NULL,
MobileNo int NOT NULL,
Sid int Not NULL,
LastSubTime DateTime
)
插入數(shù)據(jù)：
以下是引用片段：
Declare @i int
Set @i=20000
WHILE @i<30000
BEGIN
Insert Into P_Order
Select @i % 2,@i,@i-19999,GetUTCDate()
set @i=@i+1
END
可以看到，在執(zhí)行全表掃描情況下，該表10000條數(shù)據(jù)總共占用38個(gè)內(nèi)存數(shù)據(jù)頁。
4.1 執(zhí)行全表選取或者低選擇性選取
以下是引用片段：
Select * From P_Order A
Inner Loop JOIN P_User B ON A.MobileNo=B.MobileNo
對(duì)于此種高選擇性選擇，默認(rèn)情況下SQL Server不會(huì)執(zhí)行Loop Join。因此，使用了強(qiáng)制性的聯(lián)接提示。
在兩個(gè)表都沒有任何索引的情況下，可以看到：
兩個(gè)表所有的數(shù)據(jù)頁都將被加載到內(nèi)存。邏輯讀取代價(jià)高達(dá)6萬多次——對(duì)于P_Order表中的每一條記錄，都將在P_User表中進(jìn)行遍歷。
在其中一個(gè)表有聚集索引情況下，盡管邏輯讀取相比剛才的6萬多次已經(jīng)大大下降，但仍然達(dá)到2萬次。而且聯(lián)接的次序?qū)Σ樵冃阅苡绊懞艽蟆Ｒ驗(yàn)槠鋵?shí)際執(zhí)行的是將SQL語句中前面的表作為聯(lián)接的外部輸入，而后面的表作為聯(lián)接的內(nèi)部輸入。
在兩個(gè)表都有聚集索引情況下，相比較而言，邏輯讀仍然達(dá)到數(shù)千次(取決于最終輸出的數(shù)據(jù)大小)，但相比較已經(jīng)大大改善。而且表中的數(shù)據(jù)只有最終需要輸出的部分才會(huì)被讀入內(nèi)存Buffer中。
4.2 執(zhí)行高選擇性選取
執(zhí)行如下的SQL語句：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
在兩個(gè)表都沒有任何索引情況下，兩張表都將執(zhí)行全表掃描。要讀入所有的數(shù)據(jù)頁到內(nèi)存?？傮w邏輯讀取決于兩表的數(shù)據(jù)頁數(shù)。
在一個(gè)表有聚集索引或者非聚集索引情況下，該表將執(zhí)行Index Seek，另一個(gè)表將出現(xiàn)全表掃描。內(nèi)存數(shù)據(jù)緩沖區(qū)中，將有一張表只讀入最終數(shù)據(jù)所在的數(shù)據(jù)頁、一張表讀入全部數(shù)據(jù)頁。邏輯讀數(shù)取決于表在聯(lián)接中的秩序、以及無索引表的數(shù)據(jù)頁數(shù)。
在兩個(gè)表都有聚集索引情況下，邏輯讀最小，每個(gè)表只有2到3次。而且只有實(shí)際需要輸出的數(shù)據(jù)才會(huì)被讀入內(nèi)存頁。兩個(gè)表都有非聚集索引情況下，消耗的邏輯讀和內(nèi)存資源近似。
測試5：執(zhí)行Merge Join
5.1 執(zhí)行全表選取或者低選擇性選取
執(zhí)行SQL:
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
如果兩張表都沒有任何索引，則兩張表都要進(jìn)行全表掃描。所有的數(shù)據(jù)都要讀入內(nèi)存頁。
邏輯讀數(shù)近似等于兩張表的數(shù)據(jù)頁總和。SQL Server處理過程中將使用到臨時(shí)表。
只有一張表有聚集索引的情形類似，SQL Server處理過程中將使用到臨時(shí)表。并且讀入所有的數(shù)據(jù)頁到內(nèi)存。
如果兩張表都有聚集索引，盡管兩表的數(shù)據(jù)都會(huì)被讀入內(nèi)存頁，但邏輯讀數(shù)已經(jīng)大大減少，等于其中一張表總數(shù)據(jù)內(nèi)存頁數(shù)加上最終輸出的數(shù)據(jù)頁數(shù)。而且SQL Server處理過程中將不需要再使用臨時(shí)表。
5.2 執(zhí)行高選擇性選取
對(duì)于這樣的高選擇性SQL語句，SQL Server 將提示無法生成執(zhí)行計(jì)劃。
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
但可以執(zhí)行：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo<=28001 (注：最終結(jié)果只有2條)
這樣的屬于低選擇性語句，但最終結(jié)果也很少的語句。如前面所述，這種情況下，采用netsted loop聯(lián)接效率可能更高。
測試6：執(zhí)行Hash Join
6.1 執(zhí)行全表選取或者低選擇性選取
對(duì)于兩表聯(lián)接，如果兩張表都沒有索引，不寫明聯(lián)接提示的情況下，SQL Server默認(rèn)使用hash join。而對(duì)于兩表聯(lián)接，如果兩張表都有聚集索引，則SQL Server默認(rèn)使用Merge Join。
執(zhí)行SQL:
以下是引用片段：
Select * From P_Order A
Inner hash JOIN P_User B ON A.MobileNo=B.MobileNo
在使用hash join情況下，無論兩張表有無索引，都將讀取所有的數(shù)據(jù)頁到內(nèi)存，SQL Server將使用臨時(shí)表進(jìn)行處理。邏輯讀數(shù)近似等于兩張表的數(shù)據(jù)頁總和。
6.2 執(zhí)行高選擇性選取
和merge join執(zhí)行高選擇性選取情況類似，也無法直接執(zhí)行：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
但可以執(zhí)行這樣的結(jié)果很少的低選擇性腳本：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo<=28001 (注：最終結(jié)果只有2條)
但此情況下，采用netsted loop聯(lián)接效率更高。
測試總結(jié)
本次測試的主要意義在于，通過分析具體的內(nèi)存變化結(jié)合執(zhí)行計(jì)劃、IO讀取等信息，可以更清楚地了解SQL Server執(zhí)行SQL 語句過程。
另外，也驗(yàn)證了一些通過分析SQL 語句的IO讀取、執(zhí)行計(jì)劃曾經(jīng)得到的經(jīng)驗(yàn)：
(1) 在執(zhí)行單表查詢時(shí)，如果是高選擇查詢，要建立非聚集索引或者聚集索引(推薦非聚集索引，是獨(dú)立于數(shù)據(jù)存放的)。如果是低選擇性查詢，則需要建立聚集索引。
(2) 在執(zhí)行聯(lián)接查詢時(shí)，如果最終輸出結(jié)果很少，則適宜使用nested loop join;如果輸出結(jié)果較多，則通過建立聚集索引，而以merge join方式查詢能得到好的性能。對(duì)于性能較低的hash join，通過轉(zhuǎn)換成merge join或者nested loop join方式提高查詢性能。
首先簡述一下SQL Server內(nèi)存占用的特點(diǎn)。SQL Server所占用的內(nèi)存除程序(即SQL Server引擎)外，主要包括緩存的數(shù)據(jù)(Buffer)和執(zhí)行計(jì)劃(Cache)。SQL Server以8KB大小的頁為單位存儲(chǔ)數(shù)據(jù)。這個(gè)和SQL Server數(shù)據(jù)在磁盤上的存儲(chǔ)頁大小相同。當(dāng)SQL Server執(zhí)行SQL 語句時(shí)，如果需要的數(shù)據(jù)已經(jīng)在其內(nèi)存中，則直接從內(nèi)存緩沖區(qū)讀取并進(jìn)行必要的運(yùn)算然后輸出執(zhí)行結(jié)果。如果數(shù)據(jù)還未在內(nèi)存中，則首先將數(shù)據(jù)從磁盤上讀入內(nèi)存Buffer中。而我們通常評(píng)價(jià)SQL性能指標(biāo)中的IO邏輯讀取數(shù)對(duì)應(yīng)的正是從內(nèi)存緩沖區(qū)讀取的頁數(shù)，而IO物理讀取數(shù)則對(duì)應(yīng)數(shù)據(jù)從磁盤讀取的頁數(shù)。
注：以下的試驗(yàn)在多人共享的開發(fā)測試服務(wù)器上也可以進(jìn)行，因?yàn)閷?shí)際上可以分別看到某個(gè)表所占用的內(nèi)存情況。但為了方便，筆者在做此試驗(yàn)時(shí)，在一個(gè)單獨(dú)的、確認(rèn)沒有其它并發(fā)任務(wù)的數(shù)據(jù)庫上進(jìn)行，因此所看到的內(nèi)存變化正是每一次所執(zhí)行的SQL語句引起的。
我們首先來看一個(gè)簡單的實(shí)例。創(chuàng)建下表：
以下是引用片段：
Create Table P_User
( UserMobileStatus int NOT NULL,
MobileNo int NOT NULL,
LastOpTime DateTime Not NULL
)
然后為該表插入一定的數(shù)據(jù)：
以下是引用片段：
Declare @i int
Set @i=28000
WHILE @i<29000
BEGIN
Insert Into P_User
Select @i % 2,@i,GetUTCDate()
Set @i=@i+1
END
然后我們?cè)诓樵兎治銎髦惺紫葓?zhí)行:
以下是引用片段：
Set Statistics IO ON
并按下Ctrl+M以顯示實(shí)際的執(zhí)行計(jì)劃。
此時(shí)，可以開始進(jìn)行我們的試驗(yàn)了。為了準(zhǔn)確觀察每一次SQL語句變化情況，在執(zhí)行第一條SQL語句以前，我們首先清空SQL Server所占用的數(shù)據(jù)內(nèi)存：
以下是引用片段：
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
這將清空SQL Server所占用的數(shù)據(jù)緩沖區(qū)(此語句在生產(chǎn)服務(wù)器上慎用，因?yàn)閷?dǎo)致一段時(shí)間內(nèi)后續(xù)的SQL語句執(zhí)行變慢)。
測試1：在沒有索引的表上執(zhí)行SQL語句
1.1 執(zhí)行全表選取或者低選擇性選取
Select * From P_User
從SQL執(zhí)行計(jì)劃可以看到，由于此時(shí)表中沒有任何索引，因此將產(chǎn)生Table Scan。而IO統(tǒng)計(jì)結(jié)果如下：
(1000 row(s) affected)
表'P_User'。掃描計(jì)數(shù)1，邏輯讀取4 次，物理讀取4 次，預(yù)讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預(yù)讀0 次。
我們看一下數(shù)據(jù)庫內(nèi)存中的情況。
首先查詢到我們所操作database的database_id：
以下是引用片段：
Select database_id From sys.databases Where name='TestGDB'
然后使用該database_id從表中查看內(nèi)存情況：
以下是引用片段：
SELECT * FROM sys.dm_os_buffer_descriptors bd
WHERE database_id=5
order by allocation_unit_id,page_id
得到結(jié)果如下：
得到的結(jié)果中可以看到，除了必要的管理頁(一個(gè)PFS_Page和一個(gè)IAM_Page)外，內(nèi)存中總共出現(xiàn)了4個(gè)Data_Page頁。這和剛才IO統(tǒng)計(jì)中看到的結(jié)果：邏輯讀為4，物理讀為4相同。由于是全表讀取，表明P_User表全部數(shù)據(jù)所占用的數(shù)據(jù)頁數(shù)也正是4，將這4個(gè)數(shù)據(jù)頁的row_count數(shù)加起來也可以驗(yàn)證其總數(shù)據(jù)行=1000。
在上例中，如果不清空數(shù)據(jù)緩沖區(qū)，再執(zhí)行一遍SQL，可以看到內(nèi)存毫無變化，而邏輯讀也不變，只是物理讀變?yōu)?，因?yàn)橐呀?jīng)不需要再從磁盤讀入數(shù)據(jù)。
1.2 執(zhí)行高選擇性選取
另外，在沒有索引的情況下，如果將上例修改為：
以下是引用片段：
Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28502
可以看到，系統(tǒng)同樣要讀取全部的數(shù)據(jù)頁到內(nèi)存。
如果使用Select Top 1 * From P_Order Where MobileNo=28502這樣的選取方式，有可能會(huì)出現(xiàn)只讀取部分?jǐn)?shù)據(jù)頁到內(nèi)存的情況。但由于在沒有索引情況下，數(shù)據(jù)實(shí)際上是無序存放在堆上，所以結(jié)果很不穩(wěn)定，也有可能發(fā)生讀取所有的數(shù)據(jù)頁到內(nèi)存。
測試2：建立聚集索引情況下，執(zhí)行SQL語句
2.1 執(zhí)行全表選取或者低選擇性選取
修改表結(jié)構(gòu)，在MobileNo字段上建立聚集索引。然后再次執(zhí)行剛才的SQL語句。得到的執(zhí)行計(jì)劃變?yōu)榫奂饕龗呙?。IO統(tǒng)計(jì)消息為：
(1000 row(s) affected)
表'P_User'。掃描計(jì)數(shù)1，邏輯讀取6 次，物理讀取1 次，預(yù)讀4 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預(yù)讀0 次。
這里的邏輯讀取變?yōu)?次。
內(nèi)存情況如下：
內(nèi)存中的變化是增加了一個(gè)非葉級(jí)的聚集索引頁，而葉級(jí)的聚集索引則會(huì)和數(shù)據(jù)放在一起。
另外，可以查看該表索引的級(jí)別：
以下是引用片段：
SELECT database_id,object_id,index_id,index_level,page_count,record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'TestGDB'), OBJECT_ID(N'dbo.P_User'), NULL, NULL , 'DETAILED');
從結(jié)果可以看到該表的聚集索引總共分2級(jí)。
因而邏輯讀增加了2——(由于發(fā)生Clustered Index Scan，除了根級(jí)別的聚集索引頁占用1次外，從根級(jí)別聚集索引定位到葉級(jí)別的聚集索引也將額外占用1次邏輯讀)。
另外一個(gè)變化是只發(fā)生了一次物理讀，即讀取根級(jí)別的聚集索引頁，另外4個(gè)數(shù)據(jù)頁則通過預(yù)讀方式而不是物理讀從磁盤裝入內(nèi)存Buffer。這使得有聚集索引的情況下，執(zhí)行SQL所直接花費(fèi)的代價(jià)實(shí)際上更小。
2.2 執(zhí)行高選擇性選取
在建立聚集索引情況下，對(duì)性能有益的變化是：
對(duì)于Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28702這樣的語句，在有聚集索引情況下，只會(huì)將最終記錄所在的頁讀入內(nèi)存。
測試3：建立非聚集索引情況下，執(zhí)行SQL語句
3.1 執(zhí)行全表選取或者低選擇性選取
如果將表中同一字段的聚集索引換成非聚集索引，則可以看到如下特點(diǎn)：
執(zhí)行全表掃描將和沒有任何索引的情況相似，將讀取所有的數(shù)據(jù)頁到內(nèi)存。此時(shí),SQL Server的查詢引擎實(shí)際上無法使用非聚集索引。
3.2 執(zhí)行高選擇性選取
將只讀取最終數(shù)據(jù)所在的頁到內(nèi)存。通過查詢計(jì)劃可以看到，SQL Server在非聚集索引上使用INDEX SEEK，然后通過lookup 得到數(shù)據(jù)實(shí)際所在行(索引覆蓋情況下例外，因?yàn)椴恍枰ㄎ坏綄?shí)際數(shù)據(jù)行)。
測試4：執(zhí)行Nested Loop Join
在進(jìn)行測試前,我們先準(zhǔn)備另外一張表和數(shù)據(jù)。
以下是引用片段：
Create Table P_Order
( UserStatus int NOT NULL,
MobileNo int NOT NULL,
Sid int Not NULL,
LastSubTime DateTime
)
插入數(shù)據(jù)：
以下是引用片段：
Declare @i int
Set @i=20000
WHILE @i<30000
BEGIN
Insert Into P_Order
Select @i % 2,@i,@i-19999,GetUTCDate()
set @i=@i+1
END
可以看到，在執(zhí)行全表掃描情況下，該表10000條數(shù)據(jù)總共占用38個(gè)內(nèi)存數(shù)據(jù)頁。
4.1 執(zhí)行全表選取或者低選擇性選取
以下是引用片段：
Select * From P_Order A
Inner Loop JOIN P_User B ON A.MobileNo=B.MobileNo
對(duì)于此種高選擇性選擇，默認(rèn)情況下SQL Server不會(huì)執(zhí)行Loop Join。因此，使用了強(qiáng)制性的聯(lián)接提示。
在兩個(gè)表都沒有任何索引的情況下，可以看到：
兩個(gè)表所有的數(shù)據(jù)頁都將被加載到內(nèi)存。邏輯讀取代價(jià)高達(dá)6萬多次——對(duì)于P_Order表中的每一條記錄，都將在P_User表中進(jìn)行遍歷。
在其中一個(gè)表有聚集索引情況下，盡管邏輯讀取相比剛才的6萬多次已經(jīng)大大下降，但仍然達(dá)到2萬次。而且聯(lián)接的次序?qū)Σ樵冃阅苡绊懞艽蟆Ｒ驗(yàn)槠鋵?shí)際執(zhí)行的是將SQL語句中前面的表作為聯(lián)接的外部輸入，而后面的表作為聯(lián)接的內(nèi)部輸入。
在兩個(gè)表都有聚集索引情況下，相比較而言，邏輯讀仍然達(dá)到數(shù)千次(取決于最終輸出的數(shù)據(jù)大小)，但相比較已經(jīng)大大改善。而且表中的數(shù)據(jù)只有最終需要輸出的部分才會(huì)被讀入內(nèi)存Buffer中。
4.2 執(zhí)行高選擇性選取
執(zhí)行如下的SQL語句：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
在兩個(gè)表都沒有任何索引情況下，兩張表都將執(zhí)行全表掃描。要讀入所有的數(shù)據(jù)頁到內(nèi)存?？傮w邏輯讀取決于兩表的數(shù)據(jù)頁數(shù)。
在一個(gè)表有聚集索引或者非聚集索引情況下，該表將執(zhí)行Index Seek，另一個(gè)表將出現(xiàn)全表掃描。內(nèi)存數(shù)據(jù)緩沖區(qū)中，將有一張表只讀入最終數(shù)據(jù)所在的數(shù)據(jù)頁、一張表讀入全部數(shù)據(jù)頁。邏輯讀數(shù)取決于表在聯(lián)接中的秩序、以及無索引表的數(shù)據(jù)頁數(shù)。
在兩個(gè)表都有聚集索引情況下，邏輯讀最小，每個(gè)表只有2到3次。而且只有實(shí)際需要輸出的數(shù)據(jù)才會(huì)被讀入內(nèi)存頁。兩個(gè)表都有非聚集索引情況下，消耗的邏輯讀和內(nèi)存資源近似。
測試5：執(zhí)行Merge Join
5.1 執(zhí)行全表選取或者低選擇性選取
執(zhí)行SQL:
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
如果兩張表都沒有任何索引，則兩張表都要進(jìn)行全表掃描。所有的數(shù)據(jù)都要讀入內(nèi)存頁。
邏輯讀數(shù)近似等于兩張表的數(shù)據(jù)頁總和。SQL Server處理過程中將使用到臨時(shí)表。
只有一張表有聚集索引的情形類似，SQL Server處理過程中將使用到臨時(shí)表。并且讀入所有的數(shù)據(jù)頁到內(nèi)存。
如果兩張表都有聚集索引，盡管兩表的數(shù)據(jù)都會(huì)被讀入內(nèi)存頁，但邏輯讀數(shù)已經(jīng)大大減少，等于其中一張表總數(shù)據(jù)內(nèi)存頁數(shù)加上最終輸出的數(shù)據(jù)頁數(shù)。而且SQL Server處理過程中將不需要再使用臨時(shí)表。
5.2 執(zhí)行高選擇性選取
對(duì)于這樣的高選擇性SQL語句，SQL Server 將提示無法生成執(zhí)行計(jì)劃。
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
但可以執(zhí)行：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo<=28001 (注：最終結(jié)果只有2條)
這樣的屬于低選擇性語句，但最終結(jié)果也很少的語句。如前面所述，這種情況下，采用netsted loop聯(lián)接效率可能更高。
測試6：執(zhí)行Hash Join
6.1 執(zhí)行全表選取或者低選擇性選取
對(duì)于兩表聯(lián)接，如果兩張表都沒有索引，不寫明聯(lián)接提示的情況下，SQL Server默認(rèn)使用hash join。而對(duì)于兩表聯(lián)接，如果兩張表都有聚集索引，則SQL Server默認(rèn)使用Merge Join。
執(zhí)行SQL:
以下是引用片段：
Select * From P_Order A
Inner hash JOIN P_User B ON A.MobileNo=B.MobileNo
在使用hash join情況下，無論兩張表有無索引，都將讀取所有的數(shù)據(jù)頁到內(nèi)存，SQL Server將使用臨時(shí)表進(jìn)行處理。邏輯讀數(shù)近似等于兩張表的數(shù)據(jù)頁總和。
6.2 執(zhí)行高選擇性選取
和merge join執(zhí)行高選擇性選取情況類似，也無法直接執(zhí)行：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo=28913
但可以執(zhí)行這樣的結(jié)果很少的低選擇性腳本：
以下是引用片段：
Select * From P_Order A
Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo
Where A.MobileNo<=28001 (注：最終結(jié)果只有2條)
但此情況下，采用netsted loop聯(lián)接效率更高。
測試總結(jié)
本次測試的主要意義在于，通過分析具體的內(nèi)存變化結(jié)合執(zhí)行計(jì)劃、IO讀取等信息，可以更清楚地了解SQL Server執(zhí)行SQL 語句過程。
另外，也驗(yàn)證了一些通過分析SQL 語句的IO讀取、執(zhí)行計(jì)劃曾經(jīng)得到的經(jīng)驗(yàn)：
(1) 在執(zhí)行單表查詢時(shí)，如果是高選擇查詢，要建立非聚集索引或者聚集索引(推薦非聚集索引，是獨(dú)立于數(shù)據(jù)存放的)。如果是低選擇性查詢，則需要建立聚集索引。
(2) 在執(zhí)行聯(lián)接查詢時(shí)，如果最終輸出結(jié)果很少，則適宜使用nested loop join;如果輸出結(jié)果較多，則通過建立聚集索引，而以merge join方式查詢能得到好的性能。對(duì)于性能較低的hash join，通過轉(zhuǎn)換成merge join或者nested loop join方式提高查詢性能。