【SQL进阶】03.执行计划之旅1 - 初探
聽到大牛們說執行計劃，總是很惶恐，是對知識的缺乏的惶恐，所以必須得學習執行計劃，以減少對這一塊知識的惶恐，下面是對執行計劃的第一講-理解執行計劃。
本系列【T-SQL】主要是針對T-SQL的總結。
SQL基礎
【T-SQL基礎】01.單表查詢-幾道sql查詢題
【T-SQL基礎】02.聯接查詢
【T-SQL基礎】03.子查詢
【T-SQL基礎】04.表表達式-上篇
【T-SQL基礎】04.表表達式-下篇
【T-SQL基礎】05.集合運算
【T-SQL基礎】06.透視、逆透視、分組集
【T-SQL基礎】07.數據修改
【T-SQL基礎】08.30分鐘全面解析-SQL事務+隔離級別+阻塞+死鎖（推薦 +165）
【T-SQL基礎】09.可編程對象
SQL進階
【SQL進階】01.好用的SQL TVP~~獨家贈送[增-刪-改-查]的例子
【SQL進階】02.理解SQL查詢的底層原理（推薦 +5）
【SQL進階】03.執行計劃之旅1 - 初探?（最新）
SQL性能調優
【SQL性能調優】01.TempDB的使用和性能問題
?
持續更新......歡迎關注我！
?
?
一、為什么需要執行計劃？
（1）幫助分析
當我們想要去分析SQL語句存在很慢時，需要有一個分析工具幫助我們分析SQL語句中哪些地方存在性能問題，而這個分析工具就是執行計劃，看懂執行計劃就能知道哪些地方有性能問題，然后結合自己已有的SQL知識分析為什么這些地方有性能問題，進而嘗試提出解決方案，并測試自己的方案是否能提高性能，以及方案是否合理。
（2）獲取其他信息
? ? ? ?a.哪些索引被用在查詢中
? ? ? ?b.數據是怎樣關聯起來的
? ? ? ?c.數據是怎樣檢索的
? ? ? ?d.為什么SQL Server沒有使用這些索引
? ? ? ?e.SQL語句的執行順序
二、什么是執行計劃？
SQL語句執行之前，需要有一個執行的方案，而這個方案是由查詢優化器（查詢分析器）產生的，并且是高效的、開銷最小的方案，這就是執行計劃。不知道查詢優化器的可以看我寫之前寫的一篇博客：
【T-SQL進階】02.理解SQL查詢的底層原理
三、如何顯示執行計劃？
執行計劃有三種格式：圖形化執行計劃，文本化執行計劃，XML格式的執行計劃。
（1）圖形化執行計劃
?
優點：可視性好。
a.估計的執行計劃
可以通過鼠標點擊圖標顯示估計的執行計劃或者通過快捷鍵Ctrl+L顯示估計執行計劃。預估執行計劃不會真正執行，只是預估出來的執行計劃。
b.實際的執行計劃
單擊實際的執行計劃圖標，該圖標處于選中狀態，然后執行SQL語句，將會顯示實際執行的執行計劃。
（2）文本化執行計劃
?
?
用獨立的行來代表每一個迭代器。使用豎線（符號“|”）來代表查詢樹中迭代器之間的父子關系。數據都是從子迭代器流向父迭代器。
優點：和圖形計劃比較，文本執行計劃更容易保存、處理、搜索和比較。
--顯示完整的預估執行計劃信息 SET SHOWPLAN_TEXT ON GO --顯示預估執行計劃的有限信息，可以用osql.exe等工具分析 SET SHOWPLAN_ALL ON GO --顯示完整的實際執行計劃信息 SET STATISTICS PROFILE ON GO總結：
?
（3）XML執行計劃
優點：三種執行計劃中最詳細的。圖形執行計劃可以保存為擴展名為.sqlplan的XML格式的計劃文件，打開此文件將會以圖形化的執行計劃展示。
?
--顯示預估執行計劃 SET SHOWPLAN_XML ON GO --顯示實際計劃的XML格式數據 SET STATISTICS XML ON GO?總結
四、如何分析執行計劃？
下面分析三種情況的執行計劃：
1.堆表
2.聚集索引
3.非聚集索引
| 結構 | 掃描 | 查找 | 書簽查找 |
| 堆表 | 表掃描 | 沒有這種情況 | RID 查找 |
| 聚集索引 | 聚集索引掃描 | 聚集索引查找 | 沒有這種情況 |
| 非聚集索引 | 如果用到了索引，則是索引掃描 | 索引查找 | Key 查找 |
?
?
?
?
關于表掃描的那些事：
- 沒有索引的表稱作堆表，查找匹配行用的是表掃描。
- 如果出現表掃描操作，則證明這個表上一定沒有聚集索引。
關于索引查找的那些事：
假設[列1]上有一個單列索引，可以使用這個索引查找下面這些謂詞：
? 1.[列1] = 1.23
? 2.[列1] > 1.23
? 3.[列1] BETWEEN 1 AND 100
? 4.[列1] LIKE 'abc%'
? 5.[列1] IN (1,3,7,10)
? 不能使用這個索引對下列這些謂詞進行查找：
? 1.ABS[列1] = 1
? 2.[列1] + 10 > 12
? 3.[列1] LIKE '%abc'
關于非聚集索引的那些事：
關于聚集索引的那些事：
1. 如果表上有聚集索引，則掃描稱作聚集索引掃描，查找稱作聚集索引查找；
2. 聚集索引掃描和表掃描的性能沒多大差異；
3.聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行。
4.索引定義中包含聚集索引列。
5.每個表只能有一個聚集索引，因為數據行本身只能按一個順序排序。
6.只有當表包含聚集索引時，表中的數據行才按排序順序存儲。 如果表具有聚集索引，則該表稱為聚集表。如果表沒有聚集索引，則其數據行存儲在一個稱為堆的無序結構中。
7.加了聚集索引不一定能提高性能，有些情況下，性能可能不如表掃描；
8.聚集索引就是表本身。表有多少行和多少列，聚集索引就有多少行和和多少列。
9.單表查詢中，過濾條件中有聚集索引列，且能用這個索引查找過濾條件中的謂詞，則是聚集索引查找，過濾條件中沒有聚集索引列則是聚集索引掃描。
（1）沒有索引的情況
創建myOrder表
USE [Test] GOCREATE TABLE [dbo].[myOrder]([id] [int] NOT NULL,[customer] [nvarchar](100) NOT NULL ) ON [PRIMARY]GOmyOrder只有兩列id,customer,這兩列上面都沒有索引。
SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'下面是執行計劃：
?
customer列上面沒有索引，SQL Server需要讀取myOrder表的每一行來判斷customer='ddd',如果結果為true,則返回此行。
查詢的示例圖如下，customer=ddd 存在三條記錄。
注意：
1.掃描及查找是SQL Server用來從表和索引中讀取數據的迭代器；
2.掃描用來處理整個表或索引的全部分支；
3.查找是在謂詞基礎上有效返回索引中一個或多個范圍中的行。
?
（2）有非聚集索引的情況
在id上創建非聚集索引
USE [Test] GO--刪除索引dbo.myOrder.ID_NON_INDEX DROP INDEX dbo.myOrder.ID_NON_INDEXCREATE NONCLUSTERED INDEX [ID_NON_INDEX] ON [dbo].[myOrder] ([id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO1.查詢條件的列上沒有非聚集索引，查詢列上沒有非聚集索引->表掃描
--id列上有索引,customer列上沒有索引,查詢條件中用的是customer='ddd'進行過濾。 --由于customer列上沒有索引，所以需要進行表掃描來找到符合customer='ddd'的行。 SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'SELECT [customer]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [id] = 2 AND [customer] ='ddd'SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2 AND [customer] ='ddd'?
2.查詢條件的列上有非聚集索引，查詢列上沒有非聚集索引->表掃描
--id列上有索引,customer列上沒有索引,查詢條件中用的是id=2進行過濾。 --SELECT查詢需要返回customer列，由于customer列上沒有索引，且索引[ID_NON_INDEX]不包含customer列， --即使用非聚集索引掃描找到了符合過濾條件id=2的索引分支，但是只能在該索引分支上面拿到id列的值，因為該索引分支只包含了id列，其他列的值拿不到。 --所以還是需要進行表掃描來找到符合條件的行,然后獲取該行的customer列的值。 --這里有個疑問：為什么找到索引分支后，不能繼續找到對應的行，然后拿到這行的customer列？？ SELECT [customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2?
3.查詢條件的列上有非聚集索引，查詢列上有非聚集索引->索引查找
--id列上有索引,customer列上沒有索引,查詢條件中用的是id=2進行過濾。 --SELECT查詢需要返回id列，使用非聚集索引掃描找到了符合過濾條件id=2的索引分支，在找到的索引分支上拿到id列的值。 SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [id] = 2?
（3）有聚集索引的情況
1.查詢條件的列上沒有聚集索引->聚集索引掃描
-- --id列上有聚集索引,customer列上沒有索引,查詢條件中用的是customer='ddd'進行過濾。 --由于customer列上沒有索引，所以需要進行掃描來找到符合customer='ddd'的行。 --只要有聚集索引，則掃描就是聚集索引掃描。聚集索引和表掃描的性能基本上一樣。 SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'SELECT [customer]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [customer] = 'ddd'
2.查詢條件的列上有聚集索引->聚集索引查找
--id列上有索引,customer列上沒有索引,查詢條件中用的是id=2進行過濾。 --用聚集索引查找到了id=2的行，由于表中的行就是按照id列來排序的，所以找到了這一行， --也就能找到這一行的所有列，所以能夠拿到customer列。所以是聚集索引掃描。SELECT [customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [id] = 2SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2
3.查詢條件中，有一列有聚集索引，另一列沒有聚集索引->聚集索引查找
--id列上有索引,customer列上沒有索引,查詢條件中用的是[id] = 2,[customer] ='ddd'進行過濾。 --當過濾條件中有一個可以根據聚集索引來查找時,先用聚集索引來找到匹配的行（[id] = 2）,然后再在過濾出來的行中篩選處符合[customer] ='ddd'的行。 --所以是聚集索引查找。 --疑問：為什么第二步篩選操作在執行計劃圖中沒有體現？？這個地方我想到的是拿到id=2的匹配行后，直接舍棄掉不符合條件[customer] ='ddd'的行，這個舍棄動作就沒有直接體現出來。SELECT [id]FROM [Test].[dbo].[myOrder]WHERE [id] = 2 AND [customer] ='ddd'SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [id] = 2 AND [customer] ='ddd'SELECT [id],[customer]FROM [Test].[dbo].[myOrder]WHERE [customer] ='3333' AND [id] = 2
?
->>【T-SQL】系列文章全文目錄（2017-06-26更新）
?
作 者： Jackson0714
出 處：http://www.cnblogs.com/jackson0714/
關于作者：專注于微軟平臺的項目開發。如有問題或建議，請多多賜教！
版權聲明：本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文鏈接。
特此聲明：所有評論和私信都會在第一時間回復。也歡迎園子的大大們指正錯誤，共同進步。或者直接私信我
聲援博主：如果您覺得文章對您有幫助，可以點擊文章右下角【推薦】一下。您的鼓勵是作者堅持原創和持續寫作的最大動力！
?
轉載于:https://www.cnblogs.com/jackson0714/p/SQL_ExecutePlan1.html
總結
以上是生活随笔為你收集整理的【SQL进阶】03.执行计划之旅1 - 初探的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 排序箭头，升序，降序简单实现
- 下一篇： JVM调优总结（二）-一些概念