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
  • 用例演示 - 創建實體 本節將演示一些示例用例並討論可選場景。 創建實體 從實體/聚合根類創建對象是實體生命周期的第一步。聚合/聚合根規則和最佳實踐部分 建議為Entity類創建一個主構造函數,以保證創建一個有效的實體。因此,無論何時我們需要創建實體的實例,我們都應該使用那個構造函數 參見下麵的問題 ...
  • 領域邏輯 & 應用邏輯 如前所述,領域驅動設計中的業務邏輯分為兩部分(層):領域邏輯和應用邏輯: 領域邏輯由系統的核心領域規則組成,應用邏輯實現應用特定的用例 雖然定義很明確,但實現起來可能並不容易。您可能無法決定哪些代碼應該位於應用程式層,哪些代碼應該位於領域層。本節試圖解釋其中的差異 多個應用程 ...
  • 表弟大學快畢業了,學了一個學期Python居然還不會寫學生管理系統,真的給我丟臉啊,教他又不肯學,還讓我直接給他寫,我真想兩巴掌上去,最終還是寫了給他,誰讓他是我表弟呢,關鍵時候還是得幫他一把! 寫完了放在那也是放著,所以今天分享給大家吧! 話不多說,咱們直接開始吧! 代碼解析 一、登錄頁面 1、定 ...
  • Zookeeper3.7源碼剖析 能力目標 掌握Zookeeper中Session的管理機制 能基於Client進行Debug測試Session創建/刷新操作 能搭建Zookeeper集群源碼配置 掌握集群環境下Leader選舉啟動過程 能說出Zookeeper選舉過程中的概念 能說出Zookeep ...
  • 前言 今天給大家分享一下我自己寫的筆記,純純的都是乾貨,關於字好像也能看。這是我學python整理出來的一些資料,希望對大家 有用。想要更多的資料那就的給一個關註了… python學習交流Q群:903971231### #導入Counter from collections import Count ...
  • Hi,大家好,我是Mic 一個工作5年的粉絲找到我。 他說: “Mic老師,你要是能回答出這個問題,我就佩服你” 我當場就懵了,現在打賭都這麼隨意了嗎? 我問他問題是什麼,他說“Kafka如何避免重覆消費的問題!” 下麵看看普通人和高手的回答! 普通人: Kafka怎麼避免重覆消費就是我們可以通過 ...
  • 前言 Steam是由美國電子游戲商Valve於2003年9月12日推出的數字發行平臺,被認為是電腦游戲界最大的數位發行平臺之一,Steam平臺是全球最大的綜合性數字發行平臺之一。玩家可以在該平臺購買、下載、討論、上傳和分享游戲和軟體。 而每周的steam會開啟了一輪特惠,可以讓游戲打折,而玩家就會 ...
  • 本篇內容將在上一篇已有的內容基礎上,進一步的聊一下項目中使用JPA的一些高階複雜場景的實踐指導,覆蓋了主要核心的JPA使用場景,可以讓你在需求開發的時候對JPA的使用更加的游刃有餘。 ...
  • 1.路徑處理 1.找模塊:sys.path import sys print(sys.path) - 1.理解 - 1.是python去查找包或模塊 - 2.項目開始根目錄,python內置的目錄 - 3.雖然說python的安裝目錄下也可以存放我們寫的模塊,但是不建議(太多了,不大好找) - 4. ...
  • Go 語言入門練手項目系列 01 基於命令行的圖書的增刪查改 02 文件管理 持續更新中... > 本文來自博客園,作者:Arway,轉載請註明原文鏈接:https://www.cnblogs.com/cenjw/p/gobeginner-proj-bookstore-cli.html 介紹 這是一 ...