當前位置:網站首頁>【Mysql上分之路】第九篇:Mysql存儲引擎

【Mysql上分之路】第九篇:Mysql存儲引擎

2022-01-27 22:47:50 棉花糖超人

2022虎年

MySQL存儲引擎

數據庫管理系統對數據的操作是通過存儲引擎實現的,存儲引擎就是錶的類型,决定了錶在計算機中存儲方式,不同的存儲引擎提供了不同的存儲機制、索引技巧,不同的存儲引擎還有特定的功能

存儲引擎分類

MySQL提供了多個不同的存儲引擎針對不同的需要進行選擇,MySQL8.0支持的存儲引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等

mysql存儲引擎分類

SHOW ENGINES

可以通過SHOW ENGINES語句來查看系統所支持的存儲引擎類型

mysql存儲引擎showengines

存儲引擎物理存儲

InnoDB

InnoDB支持事務、行級鎖和外鍵,使用InnoDB存儲引擎的數據錶,一個數據錶對應著兩個文件,分別為:.frm、.ibd

  • .frm:存儲錶結構信息
  • .ibd:存儲錶中數據

MERGE

MERGE存儲引擎的數據錶其實是一個邏輯結構,代錶著由一組結構完全相同的MyISAM數據錶所構成的集合

MERGE存儲引擎的數據錶除了擁有存儲錶結構定義的.frm文件以外,還有一個擴展名為.mgr的文件,這個文件裏不保存數據,保存的是數據的來源地,即是由一份由多個MyISAM數據錶的名單構成的MERGE數據錶

MyISAM

MyISAM 存儲引擎的數據錶在數據庫目錄裏使用 3 個文件來代錶,這些文件的基本名與數據錶的名字相同,擴展名則錶明了文件的具體用途

  • .frm:錶結構定義文件,存放著該數據錶的結構定義
  • .MYD:MY Data的縮寫 ,數據文件,存放著該數據錶中各個行的數據
  • .MYI:MY Index的縮寫 ,索引文件,存放著該數據錶的全部索引信息

MEMORY

Memory 存儲引擎的數據錶是創建在內存中的數據錶,MySQL 服務器把 Memory 數據錶的數據和索引都存放在了內存中而不是硬盤上;除了相應的.frm錶結構文件之外,Memory引擎錶在文件系統裏沒有相應的代錶文件

Blackhole

由於在 BLACKHOLE 存儲引擎的數據錶中寫入任何數據都會消失,所以除了 .frm 文件,BLACKHOLE 引擎錶沒有其他相應的代錶文件

Archive

Archive 存儲引擎的數據錶除了擁有 .frm 錶結構定義文件外,還有一個擴展名為 .arz 的數據文件,用來存儲曆史歸檔數據。執行優化操作時可能還會出現一個擴展名為 .arn 的文件

CSV

CSV 引擎錶也會包含一個 .frm 錶結構定義文件,此外還會創建一個擴展名為 .CSV 的數據文件。這個文件是 CSV 格式的文本文件,用來保存錶中的實際數據

  • .CSV 文件可以直接在 Excel 中打開,或者是使用其它文件編輯工具查看。另外,還有一個同名的元信息文件,文件擴展名為 .CSM,用來保存錶的狀態及錶中保存的數據量
  • CSV 存儲引擎基於 CSV 格式文件存儲數據,由於自身文件格式的原因,所有列必須强制指定 NOT NULL

InnoDB存儲引擎

InnoDB是目前最重要、使用最廣泛的存儲引擎,是因為提供外鍵約束以及事務處理能力的存儲引擎

InnoDB存儲引擎的優勢

支持事務安裝

InnoDB支持事務是InnoDB成為主流存儲引擎的主要原因;InnoDB實現了SQL標准的4個隔離級別(Read uncommitted、Read commited、Repeatable Read、Serialiable)

在innodb存儲引擎中,repeatable read是默認的事務隔離級別,同時該引擎的實現基於多版本的並發控制協議-MVCC(Multi-version Concurrency Control),解决了幻讀問題,髒讀和不可重複讀也是不存在

灾難恢複性好

InnoDB通過commit、rollback、crash-recovery來保障數據的安全

crash-recovery 就是指如果服務器因為硬件或軟件的問題而崩潰,不管當時數據是怎樣的狀態,在重啟 MySQL 後,InnoDB 都會自動恢複到發生崩潰之前的狀態,並回到用戶離開的地方

使用行級鎖

InnoDB存儲引擎實現了行鎖,行鎖機制是通過索引來完成的,行鎖定機制也為 InnoDB 在承受高並發壓力的環境下增强了不小的競爭力

實現緩沖處理

InnoDB存儲引擎提供了專門的緩存池,實現了緩沖管理,不僅能緩沖索引也能緩沖數據,重用的數據直接從內存中處理,比磁盤獲取數據處理更快

InnoDB的錶和索引在一個邏輯空間中,錶空間可以包含數個文件

支持外鍵

InnoDB支持外鍵約束,檢查外鍵、插入、更新和删除、以確保數據的完整性

在存儲錶中數據時每張錶的存儲都按主鍵順序存放,如果沒有顯示地在定義錶時指定主鍵,InnoDB會為每一行生成一個6個字節的ROWID並作為主鍵

適合需要大型數據庫的網站

InnoDB 是為處理巨大數據量時的最大性能設計,它的 CPU 效率可能是任何其他基於磁盤的關系數據庫引擎所不能匹敵的

物理存儲

InnoDB的物理存儲結構分為兩部分:數據文件、日志文件

使用InnoDB是,MySQL會在數據目錄Data下創建一個名為ibdata1的自動擴展數據文件,以及兩個名為ib_logfile0和ib_logfile1的日志文件

mysql存儲引擎InnoDB的數據文件

數據文件

數據文件用來存放數據錶中的數據和所有的索引數據,包括主鍵和其他普通索引

InnoDB存儲的數據采用錶空間(Tablespace)進行存放設計,錶空間是用來存放MySQL系統相關信息的一個特殊共享錶空間

InnoDB的錶空間可以兩種形式:共享錶空間、獨立錶空間

  • 共享錶空間:錶數據和索引都在同一個錶空間,默認的錶空間文件就是上面提到的ibdata1文件
  • 獨立錶空間:每個錶的數據和索引被存放在一個單獨的.ibd文件中

可以通過命令show variables like ‘innodb_file_per_table%’查看mysql是否使用獨立錶空間

mysql存儲引擎數據文件共享錶空間

innodb_file_per_table:該字段值為ON時錶示開啟獨立文件,InnoDB錶的數據和索引都會以單獨的形式存放,值為OFF時,InnoDB錶的數據和索引都存放在一個錶空間中,可以通過設置該參數的值來决定是否使用獨立錶空間

共享錶空間

共享錶空間的數據文件可以設置為固定大小和可自動擴展大小兩種形式

  • 自動擴展形式的文件可以設置文件的最大大小和每次擴展量
  • 在創建自動擴展的數據文件時,最好加上最大尺寸,一個原因是文件系統本身有一定的大小規則,還有一個原因是方便自身維護
  • 只有共享錶空間快用完的時候,必須要為其增减數據文件

可以通過show variables like ‘innodb_data_file_path%’查看當前共享錶空間文件的路徑、大小和自動化策略

mysql存儲引擎查看共享錶空間

innodb_data_file_path:該參數負責定義共享錶空間的路徑、初始化大小、自動擴展策略

用戶也可以通過該參數innodb_data_file_path指定共享錶空間文件

innodb_data_file_path=datafile_spec1[;datafile_spec2]...
datafile_spec1 格式為錶空間文件路徑:大小:屬性,還可以指定多個文件組成一個錶空間,同時指定文件的屬性
如:innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

這裏將 /db/ibdata1 和 /dr2/db/ibdata2 兩個文件用來組成錶空間。若這兩個文件比特於不同的磁盤上,磁盤的負載可能被平均,因此可以提高數據庫的整體性能

注意:innodb在創建新數據文件時不會創建目錄,若指定目錄存在,新數據文件也無法啟動並且報錯,除此之外,給innodb的共享錶空間增减數據文件之後,必須要重啟數據庫之後才能生效

獨立錶空間

獨立錶空間的命名規則為:錶名.ibd

  • 通過設置參數innodb_file_per_table參數,可以將每個基於innodb存儲引擎的錶產生一個獨立錶空間
  • SET GLOBAL innodb_file_per_table=1,錶示使用獨立錶空間

注意:單獨的錶空間文件只存儲該錶的數據、索引和緩沖等信息,無論是使用共享錶空間還是獨立錶空間來存放錶,共享錶空間都是必須存在的

mysql存儲引擎獨立錶空間

日志文件

InnoDB存儲引擎的數據目錄下兩個ib_logfile0和ib_logfile1的文件,稱為存儲引擎的重做日志文件

InnoDB可以通過重做日志文件將數據庫宕機時已經完成但還沒有來得及將數據寫入磁盤的事務恢複,也能將所有完成並寫入磁盤的未完成事務回滾,並且將數據還原,以此來保證數據的完整性

每個InnoDB引擎至少有一個重做日志文件組(Group),每個文件組下至少有2個重做日志文件,如默認的ib_logfile0和ib_logfile1

如果數據庫中InnoDB的錶,不能删除InnoDB的日志文件,會造成數據庫的Crash

  • 數據庫Crash:指的是數據庫不工作或停止響應、進程中斷等情况

MySQL 啟動參數文件設置中,InnoDB 的所有參數基本上都帶有前綴“innodb_”,不論是 InnoDB 數據還是和日志相關,或者是其他一些性能,事務等等相關的參數都是一樣

下面是影響重做日志文件的參數:

  • innodb_log_file_size:指定每個重做日志的大小。
  • innodb_log_files_in_group:指定日志文件組中重做日志文件的數量,默認為 1。
  • innodb_mirrored_log_groups:指定日志鏡像文件組的數量,默認為 1。
  • innodb_log_group_home_dir:指定日志文件組所在路徑,默認為./

MyISAM存儲引擎

MyISAM是基於ISAM存儲引擎,並對其進行擴展。它是在Web、數據倉儲和其他應用環境下最常使用的存儲引擎之一;MyISAM擁有較高的插入、查詢速度,但是不支持事務、也不支持外鍵;對事務完整性沒有要求或者以插入、查詢為主的應用都可以MyISAM引擎來創建

MySQL的特性

  • ①大文件在支持大文件的文件系統和操作系統上被支持
  • ②每個MyISAM錶最大索引數是64,這可以通過重新編譯來改變,每個索引最大的列數是16個。
  • ③數據文件和索引文件可以放在不同的目錄
  • ④NULL值被允許在索引的列中,這個值占每個鍵的0~1字節
  • ⑤BLOB和TEXT列可以被索引

物理存儲

每個基於MyISAM引擎創建的錶在磁盤上存儲成3個文件,其文件名和錶名相同,但是擴展名不一樣分別為:

  1. .frm(存儲錶定義)
  2. .MYD (MYData,存儲數據)
  3. .MYI(MYIndex,存儲索引)

數據文件和索引文件可以分布在不同的目錄,平均分布I/O,獲得更快的速度;要指定索引文件和數據文件的路徑,需要在創建錶的時候通過DATA DIRECTORY和INDEX DIRECTORY語句指定,也就是說不同MyISAM錶的索引文件和數據文件可以放置到不同路徑下。文件路徑需要絕對路徑,並且具有訪問權限

MyISAM 的每一張錶數據都存放在後綴名為 .MYD 的文件中,但是每個文件的存放格式可能並不完全一樣;MyISAM的錶支持3中不同的存儲格式:靜態(固定長度)錶、動態錶、壓縮錶

靜態錶

靜態錶是默認的存儲格式,靜態錶中字段都是非變長字段,這樣每個記錄都是固定長度,這種存儲方式的優點是存儲非常快,容易緩存,出現故障容易恢複;缺點是占用的空間比動態錶多;靜態錶的數據在存儲時會按照列的寬度定義補足空格,但是在應用訪問的時候並不會得到這些空格,這些空格在返回給應用之前已經去掉

*如果需要保存的內存後面本來就帶有空格,那麼返回的時候也會被去掉,因為靜態錶是默認的存儲格式。

動態錶

動態錶中包含變長字段,記錄不是固定長度的,這種存儲方式的優點就是占用的空間較少,但是頻繁的更新和删除記錄會產生碎片,需要定義執行OPTIMIZE TABLE語句或myisamchk-r命令來改善性能,並且在出現故障時恢複相對吧比較困難

壓縮錶

壓縮錶由myisampack工具是基於靜態或動態格式錶上創建,占據非常小的磁盤空間,因為每個記錄是被單獨壓縮的,所以只有非常小的訪問開支

默認存儲引擎

查看默認存儲引擎

查看默認存儲引擎,之前可以通過SHOW ENGINES來查看系統支持的存儲引擎,其中有Default關鍵字的錶示為默認存儲引擎,但是更快的可以通過查看默認存儲引擎的語句來查看

SHOW VARIABLES LIKE 'default_storage_engine%';

mysql存儲引擎查看默認存儲引擎

修改默認存儲引擎

配置文件修改(永久修改)

若將系統默認存儲引擎進行永久修改,則需要對my.cnf配置文件修改,修改語句為

default-storage-engine=存儲引擎名稱

修改完成之後保存配置文件,就對默認存儲引擎永久修改

臨時修改

臨時修改默認的存儲引擎通過SET語句

SET default_storage_engine=< 存儲引擎名 >

mysql存儲引擎臨時修改默認存儲引擎

SET只能臨時修改默認存儲引擎,當客戶端重啟時默認存儲引擎依然是InnoDB

存儲引擎的選擇

在使用 MySQL 數據庫管理系統時,選擇一個合適的存儲引擎是一個非常複雜的問題。不同的存儲引擎都有各自的特性、優勢和使用的場合,正確的選擇存儲引擎可以提高應用的使用效率

Mysql常用存儲引擎的對比
特點 InnoDB MyISAM Memory
存儲限制 64TB 256TB RAM
事務安全 支持
鎖機制 行鎖 錶鎖 錶鎖
B樹索引 支持 支持 支持
哈希索引 支持
全文索引 支持 支持
集群索引 支持
數據緩存 支持 支持
索引緩存 支持 支持 支持
數據可壓縮 支持
空間使用 N/A
內存使用 中等
批量插入速度
支持外鍵 支持

MyISAM

MyISAM 存儲引擎不支持事務和外鍵,所以訪問速度比較快。如果應用主要以讀取和寫入為主,只有少量的更新和删除操作,並且對事務的完整性、並發性要求不是很高,那麼選擇 MyISAM 存儲引擎是非常適合的

MyISAM 是在 Web 數據倉儲和其他應用環境下最常使用的存儲引擎之一

InnoDB

InnoDB 存儲引擎在事務上具有優勢,即支持具有提交、回滾和崩潰恢複能力的事務安裝,所以比 MyISAM 存儲引擎占用更多的磁盤空間

對事務的完整性有比較高的要求,在並發條件下要求數據的一致性,數據操作除了插入和查詢以外,還包括很多的更新、删除操作,那麼 InnoDB 存儲引擎是比較合適的選擇

InnoDB 存儲引擎除了可以有效地降低由於删除和更新導致的鎖定,還可以確保事務的完整提交(Commit)和回滾(Rollback),對於類似計費系統或者財務系統等對數據准確性要求比較高的系統,InnoDB 都是合適的選擇

Memory

MEMORY 存儲引擎將所有數據保存在 RAM 中,所以該存儲引擎的數據訪問速度快,但是安全上沒有保障

MEMORY 對錶的大小有限制,太大的錶無法緩存在內存中。由於使用 MEMORY 存儲引擎沒有安全保障,所以要確保數據庫异常終止後錶中的數據可以恢複

如果應用中涉及數據比較少,且需要進行快速訪問,則適合使用 MEMORY 存儲引擎

總結

數據庫管理系統對數據的操作是通過存儲引擎實現的,存儲引擎就是錶的類型,决定了錶在計算機中存儲方式,不同的存儲引擎提供了不同的存儲機制、索引技巧,不同的存儲引擎還有特定的功能;MySQL提供了多個不同的存儲引擎針對不同的需要進行選擇,MySQL8.0支持的存儲引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等

趕緊學習起來吧!我是一個正在努力找回自我的人,希望能和一起學習的人成長,有錯誤的地方請各比特大佬幫忙指正,如果覺得有幫助就點個贊當作對我的一個小肯定,peace&love

版權聲明
本文為[棉花糖超人]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/01/202201272247498964.html

隨機推薦