MySQL 普通索引和唯一索引的區別

来源:https://www.cnblogs.com/hhhhuanzi/archive/2020/02/16/12318504.html

該文為《 MySQL 實戰 45 講》的學習筆記,感謝查看,如有錯誤,歡迎指正 一、查詢和更新上的區別 這兩類索引在查詢能力上是沒差別的,主要考慮的是對更新性能的影響。建議儘量選擇普通索引。 1.1 MySQL 的查詢操作 普通索引 查找到第一個滿足條件的記錄後,繼續向後遍歷,直到第一個不滿足條件的 ...


該文為《 MySQL 實戰 45 講》的學習筆記,感謝查看,如有錯誤,歡迎指正

一、查詢和更新上的區別

這兩類索引在查詢能力上是沒差別的,主要考慮的是對更新性能的影響。建議儘量選擇普通索引。

1.1 MySQL 的查詢操作
  • 普通索引
    查找到第一個滿足條件的記錄後,繼續向後遍歷,直到第一個不滿足條件的記錄。
  • 唯一索引
    由於索引定義了唯一性,查找到第一個滿足條件的記錄後,直接停止繼續檢索。

普通索引會多檢索一次,幾乎沒有影響。因為 InnoDB 的數據是按照數據頁為單位進行讀寫的,需要讀取數據時,並不是直接從磁碟讀取記錄,而是先把數據頁讀到記憶體,再去數據頁中檢索。

一個數據頁預設 16 KB,對於整型欄位,一個數據頁可以放近千個 key,除非要讀取的數據在數據頁的最後一條記錄,就需要再讀一個數據頁,這種情況很少,對CPU的消耗基本可以忽略了。

因此說,在查詢數據方面,普通索引和唯一索引沒差別。

1.2 MySQL 的更新操作

更新操作並不是直接對磁碟中的數據進行更新,是先把數據頁從磁碟讀入記憶體,再更新數據頁

  • 普通索引
    將數據頁從磁碟讀入記憶體,更新數據頁。
  • 唯一索引
    將數據頁從磁碟讀入記憶體,判斷是否唯一,再更新數據頁。

由於 MySQL 中有個 change buffer 的機制,會導致普通索引唯一索引在更新上有一定的區別。

change buffer的作用是為了降低IO 操作,避免系統負載過高。change buffer將數據寫入數據頁的過程,叫做merge

如果需要更新的數據頁在記憶體中時,會直接更新數據頁;如果數據不在記憶體中,會先將更新操作記入change buffer,當下一次讀取數據頁時,順帶merge到數據頁中,change buffer也有定期merge策略。資料庫正常關閉的過程中,也會觸發merge

對於唯一索引,更新前需要判斷數據是否唯一(不能和表中數據重覆),如果數據頁在記憶體中,就可以直接判斷並且更新,如果不在記憶體中,就需要去磁碟中讀出來,判斷一下是否唯一,是的話就更新。change buffer是用不到的。即使數據頁不在記憶體中,還是要讀出來。

change buffer 用的是 buffer pool 里的記憶體,因此不能無限增大。change buffer 的大小,可以通過參數 innodb_change_buffer_max_size 來動態設置。這個參數設置為 50 的時候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

結論:唯一索引用不了change buffer,只有普通索引可以用。


二、change buffer 和 redo log的區別
2.1 change buffer 的適用場景

change buffer 的作用是降低更新操作的頻率,緩存更新操作。這樣會有一個缺點,就是更新不及時,對於讀操作比較頻繁的表,不建議使用 change buffer。

因為更新操作剛記錄進change buffer中,就讀取了該表,數據頁被讀到了記憶體中,數據馬上就merge到數據頁中了。這樣不僅不會降低性能消耗,反而會增加維護change buffer的成本。

適用於寫多讀少的表。


2.2 change buffer 和 redo log 區別

我們舉一個例子用來理解 redo logchange buffer。我們執行以下 SQL 語句:

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假設,(id1,k1) 在數據頁 Page 1 中,(id2,k2) 在數據頁 Page 2 中。並且 Page 1 在記憶體中,Page 2 不在記憶體中。

執行過程如下:

  1. 直接向 Page 1 中寫入 (id1,k1)
  2. change buffer 中記下"向 Page 2 中寫入(id2,k2)"這條信息;
  3. 將以上兩個動作記入redo log。

做完上面這些,事務就可以完成了。執行這條更新語句的成本很低,就是寫了兩處記憶體,然後寫了一處磁碟(兩次操作合在一起寫了一次磁碟),而且還是順序寫的。

這條更新語句,涉及了四個部分:記憶體、redo log(ib_log_fileX)、 數據表空間(t.ibd)、系統表空間(ibdata1)。
在這裡插入圖片描述
如果要讀數據的話,過程是怎樣的?

mysql> select * from t where k in (k1, k2);

假設讀操作在更新後不久,此時記憶體中還有 Page 1,沒有 Page 2,那麼讀操作就和 redo log 以及 ibdata1 無關了。

  1. 從記憶體中獲取到 Page 1 上的最新數據 (id1,k1)
  2. 將數據頁 Page 2 讀入記憶體,執行merge 操作,此時記憶體中的 Page 2 也有最新數據(id2,k2)
    在這裡插入圖片描述

需要註意的是:

  1. redo log中的數據,可能還沒有 flush 到磁碟,磁碟中的 Page 1 和 Page 2 中並沒有最新數據,但我們依然可以拿到最新數據(記憶體中的 Page 1 就是最新的,Page 2 雖然不是最新的,但是從磁碟讀到記憶體中後,執行了merge操作,記憶體中的 Page 2 就是最新的了。)
  2. 如果此時 MySQL 異常宕機了,比如伺服器異常掉電,change buffer 中的數據會不會丟?

    change buffer 中的數據分為兩部分,一部分是已經mergeibdata1中的數據,這部分數據已經持久化,不會丟失。另一部分數據,還在 change buffer 中,沒有mergeibdata1,分 3 種情況:

    (1)change buffer 寫入數據到記憶體,redo log 也已經寫入(ib-log-filex),但是未 commit,binlog中也沒有fsync到磁碟,這部分數據會丟失;
    (2)change buffer 寫入數據到記憶體,redo log 也已經寫入(ib-log-filex),但是未 commit,binlog 已寫入到磁碟,這部分不會多丟失,異常重啟後會先從 binlog 恢復 redo log,再從 redo log 恢復 change buffer;
    (3)change buffer 寫入數據到記憶體,redo log 和 binlog 都已經fsync,直接從redo log 恢復,不會丟失。

redo log 主要節省的是隨機寫磁碟的 IO 消耗(轉成順序寫),而 change buffer 主要節省的則是隨機讀磁碟的 IO 消耗


感謝閱讀,有興趣的小伙伴可以關註我的微信公眾號DevOps探索之旅,大家一起學習進步


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

更多相關文章
  • 一、環境 VMWare+Centos7 二、寫在前面的話 安裝GCC最簡單的方式當然是【yum -y install gcc】但是我的機器上安裝下來後,其版本是4.8.5,感覺有點低,所以想升級一下(7.2.0, 8.2.0之類的版本)。於是需要手動安裝。 三、吃過的坑 1. 本地沒有GCC導致編譯 ...
  • rpm可以實現程式的快速,簡單安裝(跟編譯安裝比),但是rpm自己不能解決依賴,所以很多工具為了自動解決依賴應運而生,其中yum就是其中之一。 yum解決依賴的辦法: 必須有個文件伺服器,裡面放置所以的rpm包,以及包的相關元數據( qi, qR得到的數據)。元數據可以使用 來創建。並放置在repo ...
  • 1 DHCP伺服器簡介 DHCP(Dynamic Host Configuration Protocol),動態主機配置協議,DHCP 協議主要是用來自動為區域網中的客戶機分配TCP/IP 信息的網路協議,並完成每台客戶機的TCP/IP 協議配置。當我們將區域網中客戶機IP地址設置為動態獲取方式時, ...
  • 實驗環境:centos7 註:因為本次實驗在同一臺伺服器上,Apache與Nginx同為80埠,所以改Apache埠為60 1 配置Nginx伺服器: 編輯Nginx配置文件,寫入以下內容 location ~ \.php$ { 所有以.php結尾的文件,前面\代表轉義 proxy_pass h ...
  • 很多人都有寫博客的習慣,奈何國內的博客網站正在一家家地關閉與重整,部分博客網站也充斥著太多的廣告,使用體驗非常不好。對於愛寫博客的朋友來說,其實還有一個更好的選擇,那就是自己搭建一個博客。 ...
  • 前言 年過30惶惶不安,又逢疫情,還是不斷學習,強化自己的能力。hadoop的視頻和書籍在15年的時候就看過,但是一直沒動手實踐過,要知道技術不經過實戰,一點提升也沒有。因此下定決心邊學邊做,希望能有所收穫。 軟體版本介紹 virtualbox 6.1 centos7 hadoop 3.2.1 jd ...
  • 1 查詢指定欄位 在 employee 表找出所有員工的姓名、性別和電子郵箱。 SELECT 表示查詢,隨後列出需要返回的欄位,欄位間逗號分隔 FROM 表示要從哪個表中進行查詢 分號為語句結束符 這種查詢表中指定欄位的操作在關係運算中被稱為投影(Projection) 使用 SELECT 子句進行 ...
  • 1、概述 (1)鎖的定義 鎖是電腦協調多個進程或線程併發訪問某一資源的機制。 在資料庫中,除了傳統的計算資源(如CPU、RAM、IO等)的爭用以外,數據也是一種供需要用戶共用的資源。如何保證數據併發訪問的一致性、有效性是所有資料庫必須解決的一個問題,鎖衝突也是影響資料庫併發訪問性能的一個重要因素。 ...
一周排行
  • 前幾天發佈了 "抄抄《CSS 故障藝術》的動畫" 這篇文章,在這篇文章里介紹瞭如何使用Win2D繪製文字然後配合BlendEffect製作故障藝術的動畫。本來打算就這樣收手不玩這個動畫了,但後來又發現性能不符合理想。明明只是做做Resize動畫和用BlendEffect混合,為什麼性能會這麼差呢? ...
  • 控制條控制項: progressBar 不能按照你程式的進程自動變化,需認為計算,調整變化量 private void progressBar1_Click(object sender, EventArgs e) { this.progressBar1.Maximum = 100;//設置進度條最大長 ...
  • 首先創建一個asp.net core web應用程式 第二步 目前官方預置了7種模板項目供我們選擇。從中我們可以看出,既有我們熟悉的MVC、WebAPI,又新添加了Razor Page,以及結合比較流行的Angular、React前端框架的模板項目。 空項目模板 Program.cs using S ...
  • 對閉包的理解 1.對於成員變數和局部變數:成員變數就是方法外部,類的內部定義的變數;局部變數就是方法或語句塊內部定義的變數。局部變數必須初始化。 形式參數是局部變數,局部變數的數據存在於棧記憶體中。棧記憶體中的局部變數隨著方法的消失而消失。成員變數存儲在堆中的對象裡面,由垃圾回收器負責回收。 成員變數它 ...
  • Xamarin.Forms讀取並展示Android和iOS通訊錄 TerminalMACS客戶端 本文同步更新地址: https://dotnet9.com/11520.html https://terminalmacs.com/861.html 閱讀導航: 一、功能說明 二、代碼實現 三、源碼獲取 ...
  • 做下對文件複製操作相關的筆記: /// <summary> /// 文件幫助類 /// </summary> public class FileHelper { /// <summary> /// 複製一個目錄下所有文件到一個新目錄下 /// </summary> /// <param name=" ...
  • 前言 有一個東西叫做鴨子類型,所謂鴨子類型就是,只要一個東西表現得像鴨子那麼就能推出這玩意就是鴨子。 C 裡面其實也暗藏了很多類似鴨子類型的東西,但是很多開發者並不知道,因此也就沒法好好利用這些東西,那麼今天我細數一下這些藏在編譯器中的細節。 不是只有 和 才能 在 C 中編寫非同步代碼的時候,我們經 ...
  • [toc] 1.應用背景 底端設備有大量網路報文(位元組數組):心跳報文,數據採集報文,告警報文上報。需要有對應的報文結構去解析這些位元組流數據。 2.結構體解析 由此,我第一點就想到了用結構體去解析。原因有以下兩點: 2.1.結構體存在棧中 類屬於引用類型,存在堆中;結構體屬於值類型,存在棧中,在一個 ...
  • 《深入淺出 C#》 (第3版) [作者] (美) Andrew Stellman (美) Jennifer Greene[譯者] (中) 徐陽 丁小峰 等譯[出版] 中國電力出版社[版次] 2016年08月 第1版[印次] 2018年04月 第4次 印刷[定價] 148.00元 【引子】 要學習編程 ...
  • 記錄使用對象初始值設定項初始化對象。 using System; using System.Collections.Generic; namespace ConsoleApp2 { class Program { static void Main(string[] args) { // 使用構造函數 ...
x