SQL Server查詢優化

来源:https://www.cnblogs.com/clue-yang/archive/2022/08/09/16526295.html
-Advertisement-
Play Games

定義: 刪除數據表就是將資料庫中已經存在的表從資料庫中刪除。註意,在刪除表的同時,表的定義和表中所有的數據均會被刪除。因此,在進行刪除操作前,最好對錶中的數據做一個備份,以免造成無法輓回的後果。本節將詳細講解資料庫表的刪除方法。 1 刪除一個或多個沒有被其他表關聯的數據表 如果一個數據表沒有和其它表 ...


從上至下優化

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

  • 使用鈔能力——給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),也可以一定程度上減少索引碎片的產生。如果你還不清楚填充因數的話,可以看看這個

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 你是否還不知道Mac電腦微信多開及防撤回功能怎麼使用呢?WeChatTweak for mac是一款僅限mac平臺的微信客戶端插件,這款插件擁有防撤回和微信多開的功能。需要的朋友不要錯過哦~ 詳情:微信微調助手WeChatTweak for mac(微信多開和防撤回工具) 功能介紹 1、阻止消息撤回 ...
  • 2.ETCD安裝 etcd 安裝可以通過源碼構建也可以使用官方構建的二進位文件進行安裝。我們以二進位文件為例,系統為CentOS 7.9,操作步驟如下所示: 2.1 Linux ETCD_VER=v3.5.4 # choose either URL GOOGLE_URL=https://storag ...
  • 1、redis 大數據時代三V:海量Volume、多樣Variety、實時Velocity 大數據時代三高:高併發、高可用(無限套娃+彼此監控)、高性能 - Redis(Remote Dictionary Server ),即遠程字典服務,是一個開源的使用ANSI C語言編寫、支持網路、可基於記憶體亦 ...
  • 攜程酒店訂單系統的存儲設計從1999年收錄第一單以來,已經完成了從單一SQLServer資料庫到多IDC容災、完成分庫分表等多個階段,在見證了大量業務奇跡的同時,也開始逐漸暴露出老驥伏櫪的心有餘而力不足之態。基於更高穩定性與高效成本控制而設計的訂單存儲系統,已經是攜程在疫情後恢復業務的必然訴求。 目... ...
  • 7月28日,以“數智進化,現在即未來”為主題的袋鼠雲2022產品發佈會於線上正式開幕。發佈會上,袋鼠雲宣佈將集團進行全新升級:從“數字化基礎設施供應商”,升級為“全鏈路數字化技術與服務提供商”,併發布了全新的四大產品體系:數據智能分析與洞察平臺“數雁EasyDigit”、低代碼數字孿生平臺EasyV ...
  • 前言: 今天有個業務需求,需要將用戶的密碼統一進行設置,現在只有用戶的昵稱(nickname), 用戶的username跟password欄位為空。遂就用到了中文轉拼音~ 1、先將nickname轉拼音並賦值給username欄位 2、使用username欄位配合加密函數,對該用戶的密碼進行賦值 - ...
  • 好消息!國際權威行業研究與咨詢機構Forrester發佈全球Translytical數據平臺廠商選型報告《The Translytical Data Platforms Landscape, Q3 2022》,騰訊雲資料庫成功入選。 Forrester是全球最具影響力的獨立第三方研究咨詢公司之一,提 ...
  • 有讀者可能會一臉懵逼? 啥是索引潛水? 你給起的名字的嗎?有沒有索引蛙泳? 這個名字還真不是我起的,今天要講的知識點就叫索引潛水(Index dive)。 先要從一件怪事說起: ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...