SQL Server查詢優化

從上至下優化

看過一篇文章，印象深刻，裡面將資料庫查詢優化分為四個大的方向

• 使用鈔能力——給DB伺服器加物理配置，記憶體啊，CPU啊，硬碟啊，全上頂配
• 替換存儲系統——根據實際的業務情況選擇不同的存儲資料庫，比如用ES做全文檢索
• 優化存儲結構——比如採用分庫分表，CQRS（命令查詢職責分離），分散式緩存，歷史數據歸檔，數據序列化等
• 查詢語句的優化——增加資料庫索引命中率，定期清理資料庫索引碎片等
  從上到下成本依次遞減，性價比依次升高，今天咱們聊聊Sql Server中基於索引的“查詢語句的優化”

索引數據結構

談到索引，咱們避免不了會想到索引的存儲數據結構，目前大多數RDBS（關係型資料庫系統）採用B+樹來存儲索引數據，如果還不是特別清楚啥是B+樹的話，這裡有傳送門點擊這裡。
這裡簡單概括一下B+樹的幾個特點：

• 每個節點可以存儲多個元素
• 所有的非葉子節點只存儲關鍵字信息
• 所有具體數據都存在葉子結點中
• 所有的葉子結點中包含了全部元素的信息
• 所有葉子節點之間都有一個鏈指針

索引分類

聚集索引

• 聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行。 索引定義中包含聚集索引列。 每個表只能有一個聚集索引，因為數據行本身只能按一個順序存儲。
• 只有當表包含聚集索引時，表中的數據行才按排序順序存儲。 如果表具有聚集索引，則該表稱為聚集表。 如果表沒有聚集索引，則其數據行存儲在一個稱為堆的無序結構中。

可以簡單理解為數據表中的數據按照既定的順序進行存儲，而這個用來排序的欄位就是聚集索引。也可以理解為一個個由Key-Value組成的元素分佈在一棵B+樹上，Key對應的就是索引，Value對應的就是具體的數據行。

非聚集索引

• 非聚集索引具有獨立於數據行的結構。 非聚集索引包含非聚集索引鍵值，並且每個鍵值項都有指向包含該鍵值的數據行的指針
• 從非聚集索引中的索引行指向數據行的指針稱為行定位器。 行定位器的結構取決於數據頁是存儲在堆中還是聚集表中。 對於堆，行定位器是指向行的指針。 對於聚集表，行定位器是聚集索引鍵。

大白話就是非聚集索引中存儲的Key-Value，其中Key跟聚集索引一樣是索引列，Value根據表是否存在聚集索引來進行區分，如果存在則Value為指向聚集索引鍵(也就是聚集索引的Key)的指針，不存在，則Value為指向表中數據行的指針。

查詢優化

索引命中規則之最左匹配原則

眾所周知，我們通常會在高頻的where條件所用的欄位上建立相關索引，那麼我們建立索引以後我們的where查詢條件是否命中索引呢？

CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC);

如上，在表DEMOTABLE中用A,B,C,D四個欄位創建了非聚集索引，首先列A必須出現在查詢條件中即（A組合），剩下的依次可以為，A,B組合，A,B,C組合，A,B,C,D組合，類似下麵這樣：

SELECT E,F,G FROM DEMOTABLE WHERE A=1
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4
//不會命中索引
SELECT E,F,G FROM DEMOTABLE WHERE B=2 AND C=3 AND D=4
//部分命中索引，只有條件A=1會命中
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND C=3 AND D=4

索引之覆蓋索引

何為覆蓋索引？

CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC) INCLUDE(E,F,G);

上面所建的非聚集索引以上一個創建語句後面多了一個INCLUDE語句，這樣做可以減少索引命中以後查詢相關列時的回表操作，何謂回表？之前我們講過在非聚集索引的葉子節點上存放了對應聚集索引的指針，查詢在命中非聚集索引的以後要查詢非索引列時會根據這個指針去聚集索引上查找相關列，這個動作就是回表；如果我們的非聚集索引上INCLUDE了要查詢的列，就可以減少相關查詢的回表操作，從而提高查詢性能。像下麵這條語句就可以完美的規避回表查詢。

SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4

索引碎片

索引在建立過程中隨著數據量的增加，索引碎片也會越來越多，從而導致即使在索引命中的情況下查詢性能可能也不是特別理想，那這些碎片是怎麼產生的呢？

• 外部碎片

新的索引在插入的時候與舊的索引在物理存儲位置上不連續，這就產生了外部碎片。

• 內部碎片

新的索引在插入的時候導致因為索引所占空間大小的變化導致同一頁上本可以存儲3個索引，現在只能存下2個索引，存儲2個索引以後剩下的空間就是內部碎片。

如何處理索引碎片呢？

• 索引碎片已經很多的情況下
  這種情況我們可以採用索引重新生成或索引重新組織，當然一般來說線上環境都有專門的DBA負責這些事宜，我們只需要知道有這些處理方式就好。
• 在創建索引的時候
  創建索引時我們可以根據實際的業務場景和索引欄位所存信息的大小來適當的添加填充因數（0-100），也可以一定程度上減少索引碎片的產生。如果你還不清楚填充因數的話，可以看看這個。

文章就到這裡，如有不對的地方，歡迎評論區留言指正，感謝！！