SQL Server的執(zhí)行計(jì)劃及SQL查詢優(yōu)化是本文我們主要要介紹的內(nèi)容，談到優(yōu)化就必然要涉及索引，就像要講鎖必然要說(shuō)事務(wù)一樣，所以你需要了解一下索引，今天來(lái)探索下SQL Server的執(zhí)行計(jì)劃，來(lái)讓大家知道如何查看SQL Server的優(yōu)化機(jī)制，以此來(lái)優(yōu)化SQL查詢。

創(chuàng)新互聯(lián)建站專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、翁源網(wǎng)絡(luò)推廣、微信小程序開(kāi)發(fā)、翁源網(wǎng)絡(luò)營(yíng)銷(xiāo)、翁源企業(yè)策劃、翁源品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)建站為所有大學(xué)生創(chuàng)業(yè)者提供翁源建站搭建服務(wù)，24小時(shí)服務(wù)熱線：028-86922220，官方網(wǎng)址：www.cdcxhl.com

 
 
 

  
  
  
--DROP TABLE T_UserInfo----------------------------------------------------  
  
  
  
--建測(cè)試表  
  
  
  
CREATE TABLE T_UserInfo  
  
  
  
(  
  
  
  
Userid varchar(20), UserName varchar(20),  
  
  
  
RegTime datetime, Tel varchar(20),  
  
  
  
) 
 
 
 

--插入測(cè)試數(shù)據(jù)

 
 
 

  
  
  
DECLARE @I INT  
  
  
  
DECLARE @ENDID INT  
  
  
  
SELECT @I = 1 
  
  
  
SELECT @ENDID = 100 --在此處更改要插入的數(shù)據(jù)，重新插入之前要?jiǎng)h掉所有數(shù)據(jù)  
  
  
  
WHILE @I <= @ENDID  
  
  
  
BEGIN  
  
  
  
INSERT INTO T_UserInfo  
  
  
  
SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','李'+CAST(@I AS VARCHAR(20)),  
  
  
  
GETDATE(),'876543'+CAST(@I AS VARCHAR(20))  
  
  
  
SELECT @I = @I + 1  
  
  
  
END 
 
 
 

 
 
 

  
  
  
--建聚集索引  
  
  
  
CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)  
  
  
  
--建非聚集索引  
  
  
  
CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)  
  
  
  
--刪除索引  
  
  
  
DROP INDEX T_UserInfo.INDEX_Userid  
  
  
  
--顯示有關(guān)由Transact-SQL 語(yǔ)句生成的磁盤(pán)活動(dòng)量的信息  
  
  
  
SET STATISTICS IO ON  
  
  
  
--關(guān)閉有關(guān)由Transact-SQL 語(yǔ)句生成的磁盤(pán)活動(dòng)量的信息  
  
  
  
SET STATISTICS IO OFF  
  
  
  
--顯示[返回有關(guān)語(yǔ)句執(zhí)行情況的詳細(xì)信息，并估計(jì)語(yǔ)句對(duì)資源的需求]  
  
  
  
SET SHOWPLAN_ALL ON  
  
  
  
--關(guān)閉[返回有關(guān)語(yǔ)句執(zhí)行情況的詳細(xì)信息，并估計(jì)語(yǔ)句對(duì)資源的需求]  
  
  
  
SET SHOWPLAN_ALL OFF 
 
 
 

請(qǐng)記?。篠ET STATISTICS IO 和 SET SHOWPLAN_ALL 是互斥的。

OK，現(xiàn)在開(kāi)始：

首先，我們插入100條數(shù)據(jù)，然后我寫(xiě)了一個(gè)查詢語(yǔ)句：SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'，這就是SQL Server的執(zhí)行計(jì)劃。

表掃描：掃描表中的行，然后我們來(lái)看該語(yǔ)句對(duì)IO的讀寫(xiě)，執(zhí)行:SET STATISTICS IO ON，此時(shí)再執(zhí)行該SQL:SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'。

切換到消失欄顯示如下：表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀1 次，物理讀0 次，預(yù)讀0 次。

四個(gè)值分別為：執(zhí)行的掃描次數(shù);從數(shù)據(jù)緩存讀取的頁(yè)數(shù);從磁盤(pán)讀取的頁(yè)數(shù);為進(jìn)行查詢而放入緩存的頁(yè)數(shù)。

注意：如果對(duì)于一個(gè)SQL查詢有多種寫(xiě)法，那么這四個(gè)值中的邏輯讀(logical reads)決定了哪個(gè)是***化的。

接下來(lái)我們?yōu)槠浣ㄒ粋€(gè)聚集索引，執(zhí)行CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)，然后再執(zhí)行SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

切換到消息欄如下顯示：

表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀2 次，物理讀0 次，預(yù)讀0 次。

此時(shí)邏輯讀由原來(lái)的1變成2，說(shuō)明我們又加了一個(gè)索引頁(yè)，現(xiàn)在我們查詢時(shí)，邏輯讀就是要讀兩頁(yè)(1索引頁(yè)+1數(shù)據(jù)頁(yè))，此時(shí)的效率還不如不建索引。

聚集索引查找：掃描聚集索引中特定范圍的行，說(shuō)明，此時(shí)用了索引。OK,到這里你應(yīng)該已經(jīng)知道初步知道MSSQL查詢計(jì)劃和如何查看對(duì)IO的讀取消耗了吧！

接下來(lái)我們繼續(xù)：

現(xiàn)在我再把測(cè)試數(shù)據(jù)改變成1000條，再執(zhí)行SET STATISTICS IO ON,再執(zhí)行，SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

在不加聚集索引的情況下：表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀7 次，物理讀0 次，預(yù)讀0 次。

在加聚集索引的情況下：CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)，表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀2 次，物理讀0 次，預(yù)讀0 次。(其實(shí)也就是說(shuō)此時(shí)是讀了一個(gè)索引頁(yè)，一個(gè)數(shù)據(jù)頁(yè))如此，在數(shù)據(jù)量稍大時(shí)，索引的查詢優(yōu)勢(shì)就顯示出來(lái)了。

總結(jié)：

當(dāng)你構(gòu)建SQL語(yǔ)句時(shí)，按Ctrl+L就可以看到語(yǔ)句是如何執(zhí)行，是用索引掃描還是表掃描？

通過(guò)SET STATISTICS IO ON 來(lái)查看邏輯讀，完成同一功能的不同SQL語(yǔ)句，邏輯讀越小查詢速度越快(當(dāng)然不要找那個(gè)只有幾百條記錄的例子來(lái)反我)。

我們?cè)倮^續(xù)深入：

OK，現(xiàn)在我們?cè)賮?lái)看一次，我們換個(gè)SQL語(yǔ)句，來(lái)看下MSSQL如何來(lái)執(zhí)行的此SQL呢？

現(xiàn)在去掉索引：DROP INDEX T_UserInfo.INDEX_Userid，現(xiàn)在打開(kāi)[顯示語(yǔ)句執(zhí)行情況的詳細(xì)信息]：SET SHOWPLAN_ALL ON，然后再執(zhí)行：SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

看結(jié)果欄：結(jié)果中有些具體參數(shù)，比如IO的消耗，CPU的消耗。

在這里我們只看StmtText:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'|--Table Scan(OBJECT:([student].[dbo].[T_UserInfo]), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)))

我再加上索引：

先關(guān)閉：SET SHOWPLAN_ALL OFF

再執(zhí)行：CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)

再開(kāi)啟：SET SHOWPLAN_ALL ON

再執(zhí)行：SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

查看StmtText:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'|--Clustered Index Seek(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), SEEK:([T_UserInfo].[Userid] >= 'ABCDE8' AND [T_UserInfo].[Userid] < 'ABCDE9'), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)) ORDERED FORWARD)

在有索引的情況下，我們?cè)賹?xiě)一個(gè)SQL：

 
 
 

  
  
  
SET SHOWPLAN_ALL ON  
  
  
  
SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%' 
 
 
 

查看StmtText:SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%'|--Clustered Index Scan(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), WHERE:(substring([T_UserInfo].[Userid], 1, 4)='ABCDE8%'))

我們?cè)俜謩e看一下三種情況下對(duì)IO的操作

分別如下：

***種情況：表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀7 次，物理讀0 次，預(yù)讀0 次。

第二種情況：表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀3 次，物理讀0 次，預(yù)讀0 次。

第三種情況：表'T_UserInfo'。掃描計(jì)數(shù)1，邏輯讀8 次，物理讀0 次，預(yù)讀0 次。

這說(shuō)明:

***次是表掃描，掃了7頁(yè)，也就是全表掃描

第二次是索引掃描，掃了1頁(yè)索引，2頁(yè)數(shù)據(jù)頁(yè)

第三次是索引掃描+表掃描，掃了1頁(yè)索引，7頁(yè)數(shù)據(jù)頁(yè)。

[圖形界面也有對(duì)CPU和IO的消耗，也可以看出來(lái)哪個(gè)***!]

通過(guò)比較，很容易的看出：第二種第三種寫(xiě)法在都有索引的情況下，like有效的使用索引，而left則不能，這樣一個(gè)最簡(jiǎn)單的優(yōu)化的例子就出來(lái)了。

在上面的例子中，用的是聚集索引掃描，字段是字母加數(shù)字，大家可以試試看純數(shù)字的、字母的、漢字的等等，了解下SQL Server會(huì)如何改變SQL語(yǔ)句來(lái)利用索引。然后再試試非聚集索引是什么情況？用不用索引和什么有關(guān)？子查詢MSSQL是如何執(zhí)行？IN用不用索引，LIKE用不用索引？函數(shù)用不用索引？OR、AND、UNION？子查詢呢？在這里我不一一去試給大家看了，只要知道了如何去看MSSQL的執(zhí)行計(jì)劃（圖形和文本)，很多事情就很明朗了。

總結(jié)：

實(shí)現(xiàn)同一查詢功能的SQL寫(xiě)法可能會(huì)有多種，如果判斷哪種***化，如果僅僅是從時(shí)間上來(lái)測(cè)，會(huì)受很多外界因素的影響，而我們明白了MSSQL如何去執(zhí)行，通過(guò)IO邏輯讀、通過(guò)查看圖示的查詢計(jì)劃、通過(guò)其優(yōu)化后而執(zhí)行的SQL語(yǔ)句，才是優(yōu)化SQL的真正途徑。

另外提醒下：數(shù)據(jù)量的多少有時(shí)會(huì)影響MSSQL對(duì)同一種查詢寫(xiě)法語(yǔ)句的執(zhí)行計(jì)劃，這一點(diǎn)在非聚集索引上特別明顯，還有就是在多CPU與單CPU下，在多用戶并發(fā)情況下，同一寫(xiě)法的查詢語(yǔ)句執(zhí)行計(jì)劃會(huì)有所不同，這個(gè)就需要大家有機(jī)會(huì)去試驗(yàn)。

關(guān)于SQL Server執(zhí)行計(jì)劃及SQL查詢優(yōu)化的實(shí)例分析就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

標(biāo)題名稱：SQLServer的執(zhí)行計(jì)劃及SQL查詢優(yōu)化實(shí)例分析與總結(jié)
鏈接分享：http://uogjgqi.cn/article/dpiogip.html