讀高性能MySQL(第4版)筆記09_創建高性能索引(下)

来源:https://www.cnblogs.com/lying7/archive/2023/09/15/17699932.html
-Advertisement-
Play Games

1. 覆蓋索引 1.1. 設計優秀的索引應該考慮到整個查詢,而不單是WHERE條件部分 1.2. 如果一個索引包含(或者說覆蓋)所有需要查詢的欄位的值,我們就稱之為覆蓋索引 1.3. 只有B-tree索引可以用於覆蓋索引 1.4. 如果查詢只需要掃描索引而無須回表 1.4.1. 索引條目通常遠小於數 ...


1. 覆蓋索引

1.1. 設計優秀的索引應該考慮到整個查詢,而不單是WHERE條件部分

1.2. 如果一個索引包含(或者說覆蓋)所有需要查詢的欄位的值,我們就稱之為覆蓋索引

1.3. 只有B-tree索引可以用於覆蓋索引

1.4. 如果查詢只需要掃描索引而無須回表

1.4.1. 索引條目通常遠小於數據行大小,所以如果只需要讀取索引,那麼MySQL就會極大地減少數據訪問量

1.4.2. 覆蓋索引對於I/O密集型的應用也有幫助,因為索引比數據更小,更容易全部放入記憶體中

1.4.3. 因為索引是按照列值的順序存儲的(至少在單頁內如此),所以對於I/O密集型的範圍查詢會比隨機從磁碟讀取每一行數據的I/O要少得多

1.4.4. 由於InnoDB的聚簇索引的特點,覆蓋索引對InnoDB表特別有用

1.4.4.1. InnoDB的二級索引在葉子節點中保存了記錄的主鍵值,所以如果二級索引能夠覆蓋查詢,則可以避免對主鍵索引的二次查詢

1.5. 在索引中滿足查詢的成本一般比查詢記錄本身要小得多

2. 使用索引掃描來做排序

2.1. 生成有序的結果

2.1.1. 通過排序操作

2.1.2. 按索引順序掃描

2.2. 如果索引不能覆蓋查詢所需的全部列,那麼就不得不每掃描一條索引記錄都回表查詢一次對應的記錄

2.2.1. 基本上都是隨機I/O

2.2.2. 按索引順序讀取數據的速度通常要比順序地全表掃描慢,尤其是在I/O密集型的應用負載上

2.3. 只有當索引的順序和ORDER BY子句的順序完全一致,並且所有列的排序方向(倒序或正序)都一樣時,MySQL才能使用索引來對結果做排序

2.4. 如果前導列為常量的時候,ORDER BY子句中的列也可以不滿足索引的最左首碼的要求

2.5. 如果在WHERE子句或者JOIN子句中將這些列指定為了常量,就可以“填補”索引欄位的間隙了

2.6. 使用索引做排序的另一個最重要的場景是,查詢語句中同時有ORDERBY和LIMIT子句的情況

3. 重覆索引

3.1. 指在相同的列上按照相同順序創建的相同類型的索引

3.2. MySQL允許在相同列上創建多個相同的索引

3.2.1. MySQL會拋出一個警告,但是並不會阻止你這麼做

3.2.2. MySQL需要單獨維護重覆的索引,優化器在優化查詢的時候也需要逐個地進行評估,這會影響性能,同時也浪費磁碟空間

4. 冗餘索引

4.1. 如果創建了索引(A,B),再創建索引(A)就是冗餘索引

4.1.1. 索引(A,B)也可以當作索引(A)來使用

4.1.2. 前一個索引的首碼索引

4.1.3. 這種冗餘只是對B-tree索引來說的

4.2. 如果再創建索引(B,A),則不是冗餘索引,索引(B)也不是,因為B不是索引(A,B)的最左首碼列

4.3. 將一個索引擴展為(A,ID),其中ID是主鍵,因為主鍵列已經包含在二級索引中了,所以這也是冗餘的

4.4. 冗餘索引通常發生在為表添加新索引的時候

4.5. 大多數情況下都不需要冗餘索引,應該儘量擴展已有的索引而不是創建新的索引

4.6. 出於性能方面的考慮也需要冗餘索引,因為擴展已有的索引會導致其變得太大,從而影響其他使用該索引的查詢的性能

4.7. 索引越多,插入的速度越慢

4.7.1. 增加新索引會導致INSERT、UPDATE、DELETE等操作的速度變慢,特別是當新增索引後達到了記憶體瓶頸的時候

5. 未使用的索引

5.1. 一些伺服器永遠不用的索引

5.2. 這樣的索引完全是累贅,建議刪除

5.3. 找到未使用索引的最好辦法就是使用系統資料庫performance_schema和sys

5.4. 在sys資料庫中,在table_io_waits_summary_by_index_usage視圖中可以非常簡單地知道哪些索引從來沒有被使用過

6. 解決冗餘索引和重覆索引的方法

6.1. 刪除這些索引就可以了

6.2. 針對INFORMATION_SCHEMA表編寫各種複雜的查詢來識別這類索引

6.3. Percona工具箱中的pt-duplicate-key-checker,該工具通過分析表結構來找出冗餘和重覆索引

6.4. 使用Percona工具箱中的pt-upgrade工具來仔細檢查計劃中的索引變更

6.5. 使用MySQL 8.0的不可見索引特性,而不是直接刪除索引

6.5.1. 可以通過ALTER TABLE語句,改變索引的一個標誌位,使得優化器在確定執行計劃時,忽略該索引

6.5.2. 如果你發現計劃刪除的索引依舊有非常重要的作用,可以直接把索引改成可見,而不需要重新構建該索引

7. 維護索引和表

7.1. 找到並修複損壞的表

7.1.1. 對於數據表來說,最糟糕的情況就是表被損壞了

7.1.2. 損壞的索引會導致查詢返回錯誤的結果或者出現莫須有的主鍵衝突等問題,嚴重時甚至還會導致資料庫的崩潰

7.1.3. 可以嘗試運行CHECK TABLE來檢查是否發生了表損壞

7.1.4. 可以使用REPAIR TABLE命令來修複損壞的表

7.1.5. 如果是InnoDB存儲引擎的表發生了損壞,那麼一定是發生了嚴重的錯誤,需要立刻調查一下原因

7.1.5.1. 常見的類似錯誤通常是由於嘗試使用rsync備份InnoDB導致的

7.1.6. 如果遇到數據損壞,最重要的是找出是什麼導致了損壞,而不只是簡單地修複,否則很有可能還會不斷地出現數據損壞的情況

7.2. 維護準確的索引統計信息

7.2.1. MySQL的優化器使用的是基於成本的模型,而衡量成本的主要指標就是一個查詢需要掃描多少行

7.2.2. 可以使用SHOW INDEX FROM命令來查看索引的基數(cardinality)

7.3. 減少索引和數據的碎片

7.3.1. B-tree索引可能會產生碎片化,這會降低查詢的效率

7.3.2. 碎片化的索引可能會以很差或者無序的方式存儲在磁碟上

7.3.3. 如果葉子頁在物理分佈上是順序且緊密的,那麼查詢的性能就會更好

7.3.3.1. 否則,對於範圍查詢、索引覆蓋掃描等操作來說,速度可能會降低很多

7.3.3.2. 對於索引覆蓋掃描,這一點會表現得更加明顯

7.3.4. 行碎片(Row fragmentation)

7.3.4.1. 數據行被存儲在多個地方的多個片段中

7.3.4.2. 即使查詢只從索引中訪問一行記錄,行碎片也會導致性能下降

7.3.5. 行間碎片(Intra-row fragmentation)

7.3.5.1. 指邏輯上順序的頁或者行,在磁碟上不是順序存儲的

7.3.5.2. 對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響,因為這些操作原本能夠從磁碟上順序存儲的數據中獲益

7.3.6. 剩餘空間碎片(Free space fragmentation)

7.3.6.1. 指數據頁中有大量的空餘空間

7.3.6.2. 導致伺服器讀取大量不需要的數據,從而造成浪費

7.3.6.3. 可以通過執行OPTIMIZE TABLE或者導出再導入的方式來重新整理數據

7.3.6.4. 對多數存儲引擎都是有效的

8. 原則

8.1. 單行訪問是很慢的,特別是在機械硬碟中存儲

8.1.1. 儘可能選擇合適的索引以避免單行查找

8.1.2. SSD的隨機I/O要快很多,不過這一點仍然成立

8.2. 按順序訪問範圍數據是很快的

8.2.1. 順序I/O不需要多次磁碟尋道,所以比隨機I/O要快很多(特別是對於機械硬碟)

8.2.2. 如果伺服器能夠按需順序讀取數據,那麼就不再需要額外的排序操作,並且GROUP BY查詢也無須再做排序和將行按組進行聚合計算了

8.3. 索引覆蓋查詢是很快的

8.3.1. 如果一個索引包含了查詢需要的所有列,那麼存儲引擎就不需要再回表查找行

8.4. 建議按響應時間來對查詢進行分析

8.4.1. 如果一個查詢無法從所有可能的索引中獲益,則應該看看是否可以創建一個更合適的索引來提升性能


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

-Advertisement-
Play Games
更多相關文章
  • 以下內容均來自Gitee的開源倉庫,具體的使用請移步Gitee:https://gitee.com/pojianbing/lazy-captcha 以下是我自己使用的具體方式 首先安裝NuGet包: Microsoft.Extensions.Caching.StackExchangeRedis La ...
  • 首先需要安裝的NuGet包有: Microsoft.AspNetCore.Authentication.JwtBearer Swashbuckle.AspNetCore Swashbuckle.AspNetCore.Filters jose-jwt 大致是這些代碼放到項目中如果有報錯信息再去具體解決 ...
  • 在我們創建界面元素的時候,不管在Vue3+ElementPlus的前端上,還是Winform桌面端上,都是會利用自定義用戶控制項來快速重用一些自定義的界面內容,對自定義用戶控制項的封裝處理,也是我們開發WPF應用需要熟悉的一環。本篇隨筆繼續深入介紹介紹基於CommunityToolkit.Mvvm 和H... ...
  • 開發中偶爾會用到使用c#代碼給控制項賦值,這裡需要通過F12找到控制項的賦值類型,然後在看控制項可以使用那種方式賦值,花點時間研究一下,基本都可以通過c#代碼給控制項賦值的。 頁面代碼: <StackPanel> <Path Data="M0 0 10 10" Stroke="Red" StrokeThic ...
  • 最近項目中需要對接釘釘,有些釘釘 API 的訪問需要使用舊版服務端 SDK 才能搞定,但是這個 SDK 使用的還是 .NET Framework 2.0 框架,不能跨平臺部署,也不支持 async\await 的非同步操作方法,Nuget 上也有其它用戶改造的 .NET Core 版本,但是都不支持異 ...
  • 第1個虛擬項目 1. 前言 點燈開啟了我們的FPGA之路,那麼我們來繼續沙盤演練。 用一個虛擬項目,來入門練習,以此步入數字邏輯的大門。 Key Words:FIFO 、SOF 、EOF、計數器、緩存、時序圖、方案設計 2. 項目要求 1) 輸入報文長度64~2048位元組; 2) 輸入報文之間最小間 ...
  • 在本次討論中,我們確實只是提到了DMA技術在文件傳輸過程中的重要作用,並對零拷貝技術進行了簡要介紹。然而,網路傳輸中存在的問題和優化方法是一個龐大的話題,涉及到諸多方面。因此,我決定將這些問題的詳細討論留到下一篇文章中,以便更全面地探討網路傳輸的優化。我希望通過這樣的討論,能夠為讀者提供有益的信息和... ...
  • 哈嘍大家好,我是鹹魚 最近我們需要把 Nginx 的日誌接入到自研的日誌採集平臺上,但是這個平臺只支持 JSON 格式,所以需要把 Nginx 日誌格式改成 JSON 格式 例如下麵這樣的效果 剛開始在主配置文件 nginx.conf 中定義了一個名叫 json 的日誌格式欄位 驗證的時候其他內容沒 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...