- SQL Server之旅（1）：那些給我們帶來福利的系統檢視
- SQL Server之旅（2）：理解萬惡的表掃描
- SQL Server之旅（3）：解惑那些背了多年聚集索引的人
- SQL Server之旅（4）：你必須知道的非聚集索引掃描
- SQL Server之旅（5）：確實不得不說的DBCC命令
- SQL Server之旅（6）：使用winHex利器加深理解資料頁
- SQL Server之旅（7）：為什麼都說狀態少的欄位不能建索引
- SQL Server之旅（8）：複合索引和include索引到底有多大區別？
- SQL Server之旅（9）：看公司這些DBA們設計的這些複合索引
- SQL Server之旅（10）：看看DML操作對索引的影響
- SQL Server之旅（11）：簡單說說sqlserver的執行計劃
說到sql的引數化處理，我也是醉了，因為sql引擎真的是一個無比強大的系統，我們平時做系統的時候都會加上快取，我想如果沒有快取，就不會有什麼大網站能跑的起來，而且大公司一般會在一個東西上做的比較用心，比較細，sqlserver同樣也使用了快取，其中就包括Data cache 和Plan cache兩個大頭。
現在我們也知道了Plan cache包括上一篇生成的xml結構和sql text，更有趣的是，sql text 還可以做到引數化。。。也就是模板化了。。
一：Sql引數化
<1> 先來做一個Person表，插入1000條資料，然後清空下快取，再select出一個資料，如圖：
|
1 2 3 4 5 6 7 |
DROP TABLE dbo.Person CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(5) DEFAULT 'aaaaa') INSERT INTO dbo.Person DEFAULT VALUES go 1000 DBCC freeproccache SELECT * FROM dbo.Person WHERE ID=100 |
<2> 資料已經查詢出來了，下面我們看下dm_exec_sql_text中的sql會是怎樣？
|
1 2 3 4 |
SELECT usecounts,objtype,cacheobjtype, plan_handle,query_plan,text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%Person%' |
從上面的圖中可以看到，當我select一下後，出現了兩個sql text，第一個叫Adhoc（即時查詢），一個叫Prepared（引數化），然後我點選第二個記錄的query_plan，會出現圖形化的執行計劃，如下圖：
跟著好奇心，我繼續點選第三個記錄的query_plan會是怎樣？？？
通過這兩個sql text的執行計劃，不知道你觀察出來下面四點了沒有：
（1）我的sql是執行表掃描的，這個沒有問題，問題在於我的兩個sql text中，第一個plan居然沒有完整的執行計劃，而僅僅是一個圖形化的select，第二個引數化sql，它的plan是一個完整的執行計劃。。。那這說明什麼呢？？？既然Prepared是完整的執行計劃，那幹嘛還要把adhoc這個sql快取起來呢？？？其實這個我也不清楚。。。我猜測肯定是讓引擎快速的找到prepared這個完整的執行計劃吧。。。
（2）就是想為什麼sqltext要做引數化，仔細想想應該明白引數化的目的就是為了重用執行計劃，因為這時候的xml已經生成好了，不然的話，你每次執行的sql中只要引數不同都要生成一次query_plan的xml，是不是會拉查詢速度的後腿呢？？？
(3) 你有沒有關注到引數化的型別是tinyint，看到這個tinyint我馬上就想破它了，我們知道tinyint就是byte型別，表示的範圍也就到256…也許引擎看我where 100才覺得我好欺負。。。那我現在想法就是where 500，看看會是什麼效果？？？
|
1 2 3 4 5 6 |
SELECT * FROM dbo.Person WHERE ID=500 SELECT usecounts,objtype,cacheobjtype, plan_handle,query_plan,text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%Person%' |
從圖中可以看到，當我where 500的時候，引擎會再次生成一個prepared的sqltext，這樣就有兩個prepared了，那我在想，為什麼不直接
給一個(@1 int)呢？？？像目前這樣sql引擎的處理方式，會有幾條prepared記錄的xml和sqltext的，是不是有點浪費記憶體呢？
(4) 仔細想想你會知道，sql引擎還是挺色膽包天的，因為prepared的記錄已生成，執行計劃也就生成了。。。。那說明什麼呢？？？說明這時候的xml已經是死的了，也就說明執行計劃也是定死的了，難道@1引數的不同不會導致執行計劃有變更麼？？？如果有變更難道還讓我執行原來這個表掃描執行計劃麼？？？有點奇葩，好了，我準備在下面仔細說說。
二：引數的變化對prepared的影響
如果你看過之前的博文，你應該明白有一個叫做書籤查詢的玩意。。。它的原理是在非聚集索引上通過B樹查詢，當查詢到目標鍵的時候拿到這個鍵的聚集索引key，然後通過key來取資料的記錄，如果你的非聚集索引的鍵值的唯一性比較高，這時候sql引擎會走書籤查詢，但是如果你的鍵值唯一性比較低或者在資料量比較小的情況，sql引擎就不會走書籤查詢，而轉向聚集索引掃描。。。那這說明什麼呢？說明執行計劃在有些時候會跟(@1 int)這個值有關係。。。那這樣的話貌似就不能重用執行計劃了，對吧。。。。為了驗證sql引擎怎麼處理的，我們來做一個測試。
1.先清空快取，再在Name列上建索引，然後我們select下，如下圖：
|
1 2 3 |
DBCC freeproccache CREATE INDEX idx_Name ON dbo.Person(NAME) SELECT * FROM dbo.Person WHERE NAME='aaaaa' |
2. 然後還是繼續看看xml和sqltext
你有什麼發現嗎？在記錄中並沒有發現什麼prepared記錄，這說明什麼。。。說明sqlserver很聰明，它知道Name可能會有 “表掃描”到
“書籤掃描”的來回切換，為了驗證問題，我繼續向Person表插入1w條資料，然後再插入一個唯一性資料。如下圖：
|
1 2 3 |
INSERT INTO dbo.Person DEFAULT VALUES go 10000 INSERT INTO dbo.Person(NAME ) VALUES ('ccccc') |
確實，如我猜想的一樣，sqlserver很聰明的。。。如果它覺得這個Name不靠譜的話，它是絕對不敢給這條sqltext生成prepared的。。。轉過頭來再想想第一條為什麼會有sqltext，那是因為a列不管取值多少，都改變不了走表掃描的現實，所以sql引擎才敢這麼大膽。。。突然覺得人生不就是這樣嘛？？？？很多人都是不把穩的事情是絕對不敢做的。。。