記一次 T-SQL 查詢優化 索引的重要性

概述

在一次調優一個專案元件的效能問題時，發現SQL的設計真的是非常的重要，所以寫一篇博文來記錄總結一下。

 

環境介紹

這個專案元件是一個Window服務，內部在使用輪循機會在處理一個事件表中的事件，將其轉換在對應的任務。效能問題在於，統計下來，這個服務一秒的時間內只能處理完成12條左右。這個效能是非常的差。

我使用的SQL版本是SQL 2012，機器是CPU I7-2670，記憶體16G，SSD硬碟。

在這個資料庫中有一個表的資料量大概30萬條資料，並不是很多, 事先沒有建立任何索引，只有一個主鍵的索引。

那麼在這其中有一條非常簡單的查詢語句：

有資料和無資料的效能對比

在上面的查詢中，IS_DYNAMIC_ASSIGN = ‘N’是查詢不到任何資料的，IS_DYNAMIC_ASSIGN = ‘Y’是有資料的，對比一下，在沒有任何資料的情況下，查詢是非常的慢，但是有資料的情況下，就不同了。

首先來看一下這個SQL的查詢計劃是什麼樣子：

下面是更清晰的執行查詢計劃:

可以看到，在沒有索引的情況下，會執行表掃描。

來看一下各自的執行時間：

可以查詢到資料：

不能查詢到資料：

可以看到，在沒有查詢到資料的情況下，總共需要耗時89ms. 不要覺得89ms才只有0.1s都不到，但是想一想之前上面說的1S鍾才處理12條記錄，就可以想像到和這個89ms有相當大的關係，如果只執行這一條SQL，那麼1S鍾也只能執行12條左右。

在這種情況下，我們來優化一下這條SQL語句。首先這句SQL本身已經是最簡單的，不能再簡化，那麼只有在索引上下功夫。

 

聚集索引和非聚集索引

兩者之間有什麼區別呢？大家可以參考一篇部落格圓另一博主的博文 聚集索引和非聚集索引（整理）。

首先我們按照我們一般沒有深入研究過索引童鞋們的思路，就是把WHERE後面條件的欄位加起來建一個索引。

根據WHERE 條件欄位建立非聚集索引

建立後好，我們來看看上面的語句的查詢計劃：

咦，為什麼還是使用了表掃描呢，而不用使用索引呢？

在這裡貼上一篇博文 Select * 一定不走索引是否正確？ 這篇博文分析了SELECT * 和各種索引的關係，但是這個博文裡面分析的和我得出的結論不一樣，我也在作者的評論留言了，同時我找到別一篇博文 SELECT * 的真相: 索引覆蓋(index coverage) 來解釋我現在的現象。因為我不怎麼研究SQL，所以我不清楚到底是什麼原因，望有知者，可以告知一下。關於索引覆蓋也可以參考這篇博文 SQL Server 查詢效能優化——覆蓋索引（二）。

那麼我現在將SELECT * 改成 SELECT 欄位後，索引才真正的應用了。

可以看到如果SELECT中的欄位包含在索引中，將可以利用到索引。

但是這樣的話，改變了我原來程式的用意，這是不能接受的。那有什麼別的辦法可以解決嗎？這個時候我想到了聚集索引。

建立聚集索引

預設情況下，在使用表設計器的建立表的時候，會預設建立一個主鍵的聚集索引。根據主鍵建立聚集索引，並不一定是最優的選擇。關於聚集索引 可以參考下 索引優化（2）聚集索引 。我觀察了一下我的表結構，我根據可能使用的列頻率最高的兩個欄位上建立了聚集索引，這兩個欄位包含在上述語句的WHERE語句中。這兩個欄位並不是主鍵。

建立好後，我們再來看一下查詢計劃和查詢的時間：

（檢視大圖）

查詢時間：

可以看到，查詢速度已經0ms了，非常的快速了。到這裡面，其實問題關於這一條SQL優化應該是已經結束了。

聚集索引很重要並且一個表只能建一條聚集索引，不能根據某一條SQL的WHERE來建立，而是要考慮到各種不同的WHERE條件才確定這樣建立聚集索引是不是最優的，我根據這兩個欄位建立好聚集索引後，我使用別的WHERE來查詢，速度也是非常的快，所以最後才確認使用這兩個欄位建聚集索引。

當然我的專案中還是有很多的語句可以優化，以及程式C#程式碼本身也可以優化，經過我的優化後，處理速度可以達到1秒處理130條左右了。

 

題外篇

在學習這個優化過程中，還有一些別的心得和疑問的，也在此記錄一下。

根據上面我建立一條聚集索引就解決了問題，並且也建立了非聚集索引，非聚集索引反而沒有用上，那麼是不是說非聚集索引就沒有用呢？並不是這樣的，非聚集索引是SQL優化的很大的一部分。

之前上面說道SELECT中只包含索引列的情況下會使用到非聚焦索引。那麼下面再說一個例子來說明非聚集索引的用途。

我們們將之前建立的三個欄位的非聚集索引刪除，使用統計函式來統計一下符合條件的條數：

（檢視大圖）

查詢時間：

可以看到耗時還是很久28ms的. 大家不用關注COUNT(*)可以使用COUNT（1）或Count（主鍵），這個討論網上也很多，我自己切換三種寫法也沒有什麼本質的不同。

這時，我們將之前刪除的非聚集索引加回來，再來檢視查詢計劃和時間：

（檢視大圖）

 

可以看到查詢計劃中，這個時候優先使用了非聚集索引，並且統計的速度是要快過使用聚集索引的。

疑問（求答疑）

在別一個SQL中，也是很簡單的SQL，使用了LEFT JOIN後，會導致查詢的效能不高，在這種情況下，該如何來優化呢，我使用了not Exists，子查詢來各種替換並不能減少這個SQL的查詢時間。

業務場景是這樣的，SQL還是和之前的一樣，SMS_SHORTNO_LOCKED表裡面會存入SMS_SHORTNO_ASSIGN表裡面的記錄，鎖定的時候會增加一條，解鎖的時候會將這條記錄刪除，所以在此使用LEFT JOIN來取出一條沒有鎖定的記錄。

下面是它的查詢語句和查詢計劃和響應時間：

（檢視大圖）

這個26ms最主要是在SHORTNO_LOCKED IS NULL這條判斷上，如果不是使用IS NULL，而是使用 SHORTNO_LOCKED = 1或=0這種方法來判斷的話，查詢是非常的快。

那麼在此，請問一下大家，相信很多人都使用LEFT JOIN，然後使用IS NULL來判斷別一個表沒有的資料。但是這樣的效能並不是很高，有什麼辦法可以解決LEFT JOIN的問題，或者可以改成別的寫法，我嘗試了很多種都沒有改善。

所以我想難道以後在設計表的時候，是不是儘量使用 INNER JOIN ，然後根據某一個欄位判斷特定的值，這樣的話，這個欄位可以使用索引來優化，像上面就因為IS NULL的問題是沒辦法使用索引的。