從上至下優化
看過一篇文章，印象深刻，里面將資料庫查詢優化分為四個大的方向
- 使用鈔能力——給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），也可以一定程度上減少索引碎片的產生，如果你還不清楚填充因子的話，可以看看這個，
文章就到這里，如有不對的地方，歡迎評論區留言指正，感謝！！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/501414.html
標籤：SQL Server
下一篇：SQL Server查詢優化