Mysql 的Innodb引擎和Myisam數據結構和區別

来源:https://www.cnblogs.com/incognitor/archive/2022/06/23/16403979.html
-Advertisement-
Play Games

E-R圖也稱實體-聯繫圖(Entity Relationship Diagram),它提供了表示實體類型、屬性和聯繫的方法,用來描述現實世界的概念模型。 ...


先大體看一下MySQL的SQL layer層的一個架構流程:

 

 

 

 

 

對一些關鍵模塊做一下簡單的描述:

1. 初始模塊:初始一些參數,比如初始myinit配置文件(在安裝的根目錄下)里的一些參數。

2. 連接管理模塊:啟動監聽,監聽連接請求

3. 連接進程模塊:可以理解為線程池

4. 用戶模塊:檢驗用戶,令牌和許可權

5. 命令分發器:處理不同類型的請求

6. 查詢緩存模塊:做緩存處理,做查詢的cache,可以理解為一個Map,查詢語句為key,結果值為value

7. 日誌記錄模塊

8. 命令解析器(parser):對於不同類型的sql語句進行處理分發,select:查詢優化器,dml:表變更模塊,ddl:表維護模塊,rep:複製模塊(主從複製),status:狀態模塊。

9. 訪問控制:

10. 表管理模塊:

11. 存儲引擎介面:和Storage Engines打交道

 

直接來到Storage Engines,這講的最多的就是引擎了,如:

Myisam、Innodb、Faicon、Memory、Archive等等引擎

 

而我們常用的是Innodb和Myisam這兩種,這兩種索引最直接的區別,可以從採用對應引擎而生成的數據表文件,進行對比。比如:採用Innodb引擎生成表A,採用Myisam索引生成表B。

現在可以從Mysql安裝目錄/data/資料庫名 ,這個目錄下看到有5個文件:

A.frm : 表定義文件

A.ibd :數據和索引都存儲在這個文件

 

B.frm :表定義文件

B.myd :數據文件

B.myi :索引文件

 

顯然,兩者從文件上體驗出來的區別就是,Myisam是把數據和索引分開存儲在兩個不同的文件里。(那其本質里的區別是什麼?急啥)

 

且看,衡量一個索引的標準是什麼?

就是,IO漸進複雜度,翻譯成人話就是:當數據越來越多的時候,索引是否依然高效,即查詢依然那麼快。

 

一個索引是否高效在某種程度上取決於其採用的索引結構。

Hash索引:

對索引欄位做Hash計算,落到不同槽裡面,有個明顯的缺點是,無法做範圍查詢,例如 select * from data where id >1

Fulltext索引:

全文搜索索引,比如欄位的值是:abcdefghijk,它就會再生成一列abcde* ,用於首碼的模糊全文搜索。

R-Tree索引:

引用的場景主要是 空間索引,比如說,美團上訂電影票,就可以選擇3km範圍內的影院,結果就搜出來了。

 

B-Tree索引:

這個索引就是我要重點講的索引,因為Innodb和Myisam採用的是B+ Tree 索引,而B+ Tree是從B-Tree基礎上演變而來。

 

那B-Tree是怎麼樣的呢?如圖:

 

 

 

 

 

每個節點上有一個索引(上方的數字,id的值),和索引對應的數據(下方藍色:id, name)

 

而B+Tree:

 

 

 

 

 

上方白色的數字都為索引的key,而data全放在下麵。(居於此結構,如果想知道它是怎麼根據key從上到下找到對應的data的,我提供一個非常好玩的web工具,去玩一下琢磨一下。https://www.cs.usfca.edu/~galles/visualization/Algorithms.html)

 

雖然Innodb和Myisam都是採用B+Tree索引,但是它們是有區別的,如圖:

 

 

 

圖片

前面已經說了,Myisam引擎方式的,會把索引和數據分開存儲在兩個文件中,一個文件負責數據的插入、更新等,另一個負責索引的維護。如圖,索引中白色的key為索引的值,下麵淺綠色為data:對應數據的地址。如果多個索引就,就多個這種模式,如圖,以name為索引,也是一樣。

 

 

而Innodb:如圖:

 

 

 

 

 

圖片

上面也提到,Innodb是把數據和索引都放在同一個文件里的,那索引和數據共存的形式是:如圖,白色中的為key,即使索引,當從上到下找到對應的key之後,key下麵存放的就是整條對應的數據了,而不是想Myisam那樣存放的是數據對應的地址了。

 

那麼問題來了,Innodb多個索引的話,是一種什麼樣的存在呢,還是如上圖右方,增加欄位name為索引:它就會建立一個副索引樹,同樣的結構,白色key索引存放的是name欄位對應的值,而藍色方的data存放的是主索引樹的key。

 

舉個例子,當id和name都作為索引的時候,執行select * from table where name = ‘james’ ,

 

第一步會先在副索引找到key為james的data為1,1是主索引的key。

第二步再到主索引找到key為對1的數據。(select * from table where id= 1)

 

多提一句,像這種索引的方式叫“聚集索引”,理解為key和data都綁定在一起了。

而Myisam的那種索引的方式叫做“非聚集索引”。

 

對於B+Tree這種索引,討論一下uuid和自增id。

Uuid是32位的,毋庸置疑,相對於自增id,uuid的存儲空間是較大的。而且uuid為索引時,其要進行較複雜的運算,最終確定索引的key要插入到哪個位置。

 

而自增id,它符合每次加1的規則,而且規矩已經確定了,它不要做過多的運算,直接從左到右進行橫向的擴展(插入),這樣性能就有了差別,如圖(B+Tree樹的高度是固定的,為4層):

 

圖片

 

 

 

 

 

即使是這樣,uuid依然在一些項目依然有它市場:

自增id雖然有多個優點,但實際大型項目中卻很少採用自增長id的,這是為什麼呢?因為uuid幾乎保證了不同資料庫的不同表的id唯一,可以進行數據切分合併,而自增長id只能保證一個資料庫中的一張表的id唯一,進行資料庫合併的話並然會因主鍵衝突而失敗,這是一個硬傷。

 

而且有博主說:分散式架構,意味著需要多個實例中保持一個表的主鍵的唯一性。這個時候普通的單表自增ID主鍵就不太合適,因為多個mysql實例上會遇到主鍵全局唯一性問題

 

 

再談談組合索引的一些小問題

組合索引是有順序的,叫做最左原則。什麼意思呢。舉個例子:

name、age、weight 這3個欄位組成組合索引,name排在第1位,那麼查詢語句where 後面必須要有name=”value”這個條件,否則此索引是不起作用的,這叫最左原則

再引用網上的一個例子:

 

 

 

 

索引的好處就是:

1. 提高檢索效率

2. 降低排序成本,索引對應的欄位是會有一個自動排序功能的,預設是升序asc。

 

Every coin has two sides,它缺點是

1. 更新索引的IO量。

就是說,當插入一條數據的時候,除了插入數據的本身,還要插入該數據對應索引信息的節點,如果對應的表是多個索引的,就插入多個數據對應的索引信息節點。而且這些都是以文件類型存儲在硬碟上的。

 

2. 調整索引所致的計算量,

 這個又是怎麼理解呢,舉個例子,像B-tree索引,在插入索引之前,都要進行計算,該索引要申請多少的空間,插入到哪個位置。

 

3. 占用存儲空間。

 

 

竟然索引有壞有好,什麼時候需要索引,什麼時候不需要?

適合:

1. 較頻繁的作為查詢條件的欄位應該創建索引

不適合:

1. 欄位值的唯一性太差不適合單獨做索引

2. 更新非常頻繁的欄位不是

3. 不會出現在where句中的欄位不適合。

 

 

 


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

-Advertisement-
Play Games
更多相關文章
  • 環境:CentOS/Linux FFmpeg官網:http://www.ffmpeg.org 將安裝到目錄/usr/local/下 mkdir /usr/local/ffmpeg cd /usr/local/ffmpeg wget http://ffmpeg.org/releases/ffmpeg- ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 準備 作為一個經常在伺服器上游走的後端,需要熟悉不少命令行操作。其中,grep、sed、awk號稱"linux三劍客",使用頻繁,功能強大,本文通過一個實例演示下基本用法。首先準備一個文本文件,命名為text.txt,內容如下: cat text ...
  • 1.官方SDK(帶Freertos)無法編譯和下載解決方案: (1)無法編譯通過:(會報一下奇奇怪怪的錯誤) 32bit MCU官方freertos的SDK編譯不通過可能是基於不同的C語言版本編寫,需要修改配置如下: (2)無法下載:本人遇到的錯誤是這個 “No Algorithm found fo ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一、什麼是MQ? 消息隊列(Message Queue,簡稱MQ),從字面意思上看,本質是個隊列,FIFO先入先出,只不過隊列中存放的內容是message而已。 其主要用途:不同進程Process/線程Thread之間通信。 隊列是一種先進先出的 ...
  • 使用yum或dnf解決rpm包的依賴關係。 YUM:Yellowdog Update Modifier。是rpm的前端程式 作用:解決軟體包之間的依賴關係 yum工作原理: yum 伺服器存放rpm包和相關rpm包的元資料庫(一個叫做repodata的文件夾--存放包的列表、依賴關係等信息) yum ...
  • 問題導入 在之前項目的基礎功能實現中,後臺管理和移動端在進行數據訪問的時候,都是直接操作資料庫MySQL。此時的系統有且僅有一臺MySQL伺服器,則可能會出現如下問題 ①、讀和寫所有壓力都由一臺資料庫承擔,壓力大 ②、資料庫伺服器磁碟損壞導致數據丟失,單點故障 解決方案 很簡單,一臺伺服器撐不住,那 ...
  • 資料庫設計的設計內容包括:需求分析、概念結構設計、邏輯結構設計、物理結構設計、資料庫的實施和資料庫的運行和維護。 ...
  • 原文鏈接:走好數據中台最後一公裡,數據服務API是數據中台的標配 視頻回顧:點擊這裡 課件獲取:點擊這裡 一、數據服務API建設背景 在數字化轉型的時代背景下,新需求的大量增長、新技術的不斷迭代,“互聯網化、數字化”進程的不斷深入,越來越多的業務被遷移到互聯網上,產生大量的業務交互和對外服務需求,對 ...
一周排行
    -Advertisement-
    Play Games
  • Github / Gitee QQ群(1群) : 813100564 / QQ群(2群) : 579033769 視頻教學 介紹 MiniWord .NET Word模板引擎,藉由Word模板和數據簡單、快速生成文件。 Getting Started 安裝 nuget link : https:// ...
  • Array.Sort Array類中相當實用的我認為是Sort方法,相比起冗長的冒泡排序,它的出現讓排序更加的簡化 結果如下: 還可以聲明一個靜態方法用來專門調用指定數組排序,從名為 array 的一維數組中 a 索引處開始,到 b 元素 從小到大排序。 註意: a + b 不能大於 array 的 ...
  • 前言 在上一篇文章CLR類型系統概述里提到,當運行時掛起時, 垃圾回收會執行堆棧遍歷器(stack walker)去拿到堆棧上值類型的大小和堆棧根。這裡我們來翻譯BotR里一篇專門介紹Stackwalking的文章,希望能加深理解。 順便說一句,StackWalker在中文里似乎還沒有統一的翻譯,J ...
  • 使用過 nginx 的小伙伴應該都知道,這個中間件是可以設置跨域的,作為今天的主角,同樣的 反向代理中間件的 YARP 毫無意外也支持了跨域請求設置。 有些小伙伴可能會問了,怎樣才算是跨域呢? 在 HTML 中,一些標簽,例如 img、a 等,還有我們非常熟悉的 Ajax,都是可以指向非本站的資源的 ...
  • 什麼是Git Git 是一個開源的分散式版本控制系統,用於敏捷高效地處理任何或小或大的項目。 Git 是 Linus Torvalds 為了幫助管理 Linux 內核開發而開發的一個開放源碼的版本控制軟體。 Git 與常用的版本控制工具 CVS, Subversion 等不同,它採用了分散式版本庫的 ...
  • 首先CR3是什麼,CR3是一個寄存器,該寄存器內保存有頁目錄表物理地址(PDBR地址),其實CR3內部存放的就是頁目錄表的記憶體基地址,運用CR3切換可實現對特定進程記憶體地址的強制讀寫操作,此類讀寫屬於有痕讀寫,多數驅動保護都會將這個地址改為無效,此時CR3讀寫就失效了,當然如果能找到CR3的正確地址... ...
  • 說明 onlyoffice為一款開源的office線上編輯組件,提供word/excel/ppt編輯保存操作 以下操作均基於centos8系統,officeonly鏡像版本7.1.2.23 鏡像下載地址:https://yunpan.360.cn/surl_y87CKKcPdY4 (提取碼:1f92 ...
  • 二叉樹查找指定的節點 前序查找的思路 1.先判斷當前節點的no是否等於要查找的 2.如果是相等,則返回當前節點 3.如果不等,則判斷當前節點的左子節點是否為空,如果不為空,則遞歸前序查找 4.如果左遞歸前序查找,找到節點,則返回,否繼續判斷,當前的節點的右子節點是否為空,如果不為空,則繼續向右遞歸前 ...
  • ##Invalid bound statement (not found)出現原因和解決方法 ###前言: 想必各位小伙伴在碼路上經常會碰到奇奇怪怪的事情,比如出現Invalid bound statement (not found),那今天我就來分析以下出現此問題的原因。 其實出現這個問題實質就是 ...
  • ###一、背景知識 爬蟲的本質就是一個socket客戶端與服務端的通信過程,如果我們有多個url待爬取,只用一個線程且採用串列的方式執行,那隻能等待爬取一個結束後才能繼續下一個,效率會非常低。 需要強調的是:對於單線程下串列N個任務,並不完全等同於低效,如果這N個任務都是純計算的任務,那麼該線程對c ...