SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

作者: 時間: 2014-10-13 分類: 技術文章, 新聞資訊 | 0條評論 |

今天,在測試一個SQL語句,是EF自動生成的,發現很奇怪的事情:SQL 加了TOP (20)之后,速度慢了很多,變成36秒,如果沒有TOP (20),只需要2秒,查看執行計劃,發現變成了全表掃描,但是索引都建立了啊,為什么會這樣呢?帶著這個問題,就開始分析這個問題吧!

原標題:程序員眼中的 SQL Server-執行計劃教會我如何創建索引?來自……

先說點廢話

以前有 DBA 在身邊的時候,從來不曾考慮過數據庫性能的問題,但是,當一個應用程序從頭到腳都由自己完成,而且數據庫面對的是接近百萬的數據,看著一個頁面加載速度像烏龜一樣,自己心里真是有種挫敗感。代碼的優化問題,這是屬于程序員的職責范圍之內,對于我來說,這一方面比較好探查些,因為都是自己熟悉的,用 EF 或 SQL Server Profiler 跟蹤一下程序代碼產生的 SQL,如果有問題,直接優化程序代碼就可以了,如果 SQL 沒問題,那就得優化數據庫了,對于我來說,這是一個無人區。

前兩天,自己瞎搞了一個測試:程序員眼中的 SQL Server-非聚集索引能給我們帶來什么?,因為對索引不是很熟悉,所以測試得到結果沒有任何價值,甚至有些誤導人,這邊說聲抱歉,在哪跌倒在哪爬起來。

應用場景

還是用商品表(Product)作為示例,表結構如下:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

存在這樣一種業務場景:獲取某個供應商(ProviderID),狀態為已售(State 為 1)的商品列表,排序方式為生產日期(ProduceTime)降序,有可能我們應用程序在顯示數據的時候用到分頁,這邊我們查詢前 100 行。翻譯為 SQL 代碼:

SELECT TOP 100
[ID],
[Name],
[Remarks],
[ProviderID],
[ProduceTime],
[State]
FROM [TestDB].[dbo].[Product]
WHERE [ProviderID]=1 AND [State]=1
ORDER BY [ProduceTime] DESC

上面這個業務場景,在我們一般的應用程序中基本上都會遇到,有時候數據量不是很大的時候,我們一般不會做任何數據庫優化,但是你看了下面的實踐,你是否應該考慮下,為你現在的數據庫加個索引呢?

SQL Server 執行計劃

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

SQL Server 執行計劃,是我們分析 SQL 執行情況的一大利器,通過它,我們也可以很方面的查看索引的執行,在實踐之前,需要了解一些必備技能,以下知識點摘自-看懂 SqlServer 查詢計劃。

SQL Server 有二種索引:聚集索引和非聚集索引。二者的差別在于:【聚集索引】直接決定了記錄的存放位置, 或者說:根據聚集索引可以直接獲取到記錄。【非聚集索引】保存了二個信息:1.相應索引字段的值,2.記錄對應聚集索引的位置(如果表沒有聚集索引則保存記錄指針)。 因此,如果能通過【聚集索引】來查找記錄,顯然也是最快的。

SQL Server 會有以下方法來查找您需要的數據記錄:

【Table Scan】:遍歷整個表,查找所有匹配的記錄行。這個操作將會一行一行的檢查,當然,效率也是最差的。
【Index Scan】:根據索引,從表中過濾出來一部分記錄,再查找所有匹配的記錄行,顯然比第一種方式的查找范圍要小,因此比【Table Scan】要快。
【Index Seek】:根據索引,定位(獲取)記錄的存放位置,然后取得記錄,因此,比起前二種方式會更快。
【Clustered Index Scan】:和【Table Scan】一樣。注意:不要以為這里有個Index,就認為不一樣了。 其實它的意思是說:按聚集索引來逐行掃描每一行記錄,因為記錄就是按聚集索引來順序存放的。 而【Table Scan】只是說:要掃描的表沒有聚集索引而已,因此這二個操作本質上也是一樣的。
【Clustered Index Seek】:直接根據聚集索引獲取記錄,最快!
所以,當發現某個查詢比較慢時,可以首先檢查哪些操作的成本比較高,再看看那些操作在查找記錄時, 是不是【Table Scan】或者【Clustered Index Scan】,如果確實和這二種操作類型有關,則要考慮增加索引來解決了。 不過,增加索引后,也會影響數據表的修改動作,因為修改數據表時,要更新相應字段的索引。所以索引過多,也會影響性能。 還有一種情況是不適合增加索引的:某個字段用0或1表示的狀態。例如可能有絕大多數是1,那么此時加索引根本就沒有意義。 這時只能考慮為0或者1這二種情況分開來保存了,分表或者分區都是不錯的選擇。

應用分析

我們先不建任何索引(除了主鍵 ID 的聚集索引),來看一下上面 SQL 代碼,在 SQL Server 執行計劃中的執行情況:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

可以看到,查詢開銷基本上被 SORT 霸占了,看到這種情況,按照正常的思維,我們首先考慮的是為 ProduceTime 創建一個非聚集索引,然后按照 DESC 排序,但有時候我們要沉下心思考一下,是不是用 ID 排序會更好呢?因為在 Product 表中,ID 為自增字段,ProduceTime 在添加的時候獲取的是當前時間,在 SQL 排序中,其實 ID 和 ProduceTime 的排序效果是一樣的,但是執行性能方面確實天壤之別,我們看一下執行計劃就知道了:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

從上面的執行計劃中,我們可以很直觀的看出差別,所以在寫 SQL 的時候,一定要慎重啊,這邊為了方便展示,我們還是以 ProduceTime 字段進行排序,按照 ID 排序,雖然沒有了 SORT 性能開銷,但是發現查詢記錄為“Clustered Index Scan”,這是全表查詢的意思,我們理想的應該是“Index Seek”或者“Clustered Index Seek”,因為這種是按照索引查詢,速度最快。按照我們程序員的理解,應該創建一個非聚集索引,比如下面 IX_Product_Provider_State 索引:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

創建好之后,我們再來執行一下 SQL 代碼:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

“Key Lookup(Clustered)”記錄,其實還是全表進行查找,默認通過聚集索引(PK_Product),我們可能會有疑問,索引就是按照查詢及排序方式創建的啊,為什么還是這種情況?這時候我們看一下 SELECT 后面的字段就知道了,我們查詢顯示的是 Product 表中所有字段,但是 IX_Product_Provider_State 非聚集索引,只是針對的查詢條件字段,并沒有吧查詢顯示字段包含進來,在創建索引窗口中,“索引鍵 列” TAB 的旁邊有個“包含性 列”,我們把其他顯示字段加進來,看下執行效果:

SQL Server:執行計劃教會我如何創建索引?(解決SQL加了TOP之后變很慢的問題)

“Index Seek”,這就是我們想要的效果,其實關于索引的創建有很多的現實問題,比如組合字段索引和單個字段索引有何不同?就像上面示例中的查詢用例,如果 ProduceTime 排序在其他查詢條件中也存在,是不是應該拉出來創建一個索引?還是像上面一樣,和查詢條件一起創建一個組合字段索引?還有一種情況就是,在一個應用程序查詢中,存在單個字段的查詢,也存在組合字段的查詢,那這時候我們是創建單個字段索引?還是創建組合字段索引呢?這幾個問題,你創建一下索引,然后用“ SQL 執行計劃”試試就知道了。

總結

針對上面的查詢用例,我個人覺得,最好的方案是:排序字段使用 ID,按照實際應用場景,提取出需要查詢的字段,避免 SELECT *,這樣會減少在添加“包含性 列”的字段,創建 IX_Product_Provider_State 非聚集索引,索引字段為:ProviderID 和 State,如果 State 的值不是多變的(比如值為 1 和 0),盡量不要創建 State 字段的非聚集索引。

做完這些,你會發現,你的應用程序像飛的一樣。

ps:我要飛得更高。。。

參考資料:

How to reduce clustered index scan cost by using SQL query
SQL Primary key sort order
Index Sort Order
請問一下order by與索引的問題
寫寫如果SELECT列表中,使用和不使用的索引使用情況

補充說明:

本文只是提供一個解決問題的方向,并不保證100%可以解決你的TOP (20)變卡問題,但可以肯定的是絕對跟SQL里面的索引有關系,如果上述操作無法解決大家的問題。建議從SQL語句入手,調整一下寫法,例如:排序的時候換成主鍵排序,也可以適當緩存問題……

本文采用 CC協議 發布,轉載請注明:轉載自 吾樂吧軟件站

本文鏈接地址:http://www.pllkp.tw/?p=26168

發表評論

?
微軟MSDN資源免費訂閱,MSDN 我告訴你 越南美女捕鱼捕走光视频 彩经网3d 天津快乐十分基本 性感沙滩排球 江西时时10分钟更新 幸运赛马pk10计划数据 超级大乐透历史比较器 山东时时网 100期陈华今晚就赌一个胆码 山东时时五运 河南481今天开奖 竞猜篮彩让分胜负 易算赛车pk10技巧 平特乾坤卦图库期 广东快乐十分基本走势图百度 老时时彩360开奖历史记录 广西快3万能码走势图