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），也可以一定程度上減少索引碎片的產生。如果你還不清楚填充因子的話，可以看看這個。

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