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

概述

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

環境介紹

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

我使用的SQL版本是SQL 2012，機器是CPU I7-2670，內存16G，SSD硬盤。

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

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

SELECT TOP 1 * FROM SMS_SHORTNO_ASSIGN WHERE APP_CODE = 'SMSNotice'AND IS_DYNAMIC_ASSIGN = 'N'AND SMS_TYPE_CODE = 'Mas'

有數據和無數據的性能對比

?在上面的查詢中，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的問題是沒辦法使用索引的。

希望有高人指點，謝謝。

總結

以上是生活随笔為你收集整理的记一次T-SQL查询优化 索引的重要性的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。