MySQL存儲引擎的物理結構與表空間

2021-01-10 anyux1

大家好，我是anyux。本文介紹MySQL存儲引擎的物理結構與表空間。

InnoDB物理結構

在5.6版本中，ibdata1包含存儲系統元信息，undo表空間數據和臨時表空間

在5.7版本中，文件結構與作用如下

表空間

在5.7版本中下面兩個參數長的很像，名字叫起來也相似，但完全不一樣，需要重點牢記

ibdata1：整個庫的統計信息和undo信息

ibd:數據行和索引信息

關於ibdata1是如何出現的，又是如何沒落了。

雖然ibdata1和ibd是兩個文件，但是在MySQL系統中將它們兩個稱為表空間(Tablespace)

現在我們使用磁碟，都是將一塊300G的磁碟，格式化並且掛載到/data目錄下，給MySQL服務使用。半年後，磁碟幾乎被佔滿。此時又添加一塊新的600G磁碟，格式化並且掛載到/data目錄下，給MySQL服務使用。又半年後，又需要重新掛載磁碟，並且每次都需要停止資料庫服務，那麼有沒有可能不重啟也能實現數據在磁碟間的轉移呢？

此時就使用到了表空間，表空間這個名詞是來自於Oracle資料庫。Oracle就是將資料庫中的數據存儲到表空間(Tablespace)，而在表空間內存，會分配數據存儲到sdb1,sdc1等磁碟分區位置上，也就是說磁碟空間的增加對於資料庫服務而言是透明的，是不可感知的

而對於MySQL到了5.5這個版本，已經被Oracle收購了。就將表空間的概念引入到MySQL中，MySQL也具備了表空間的管理模式。不足的是MySQL訪問數據是基於文件系統去訪問，表空間被迫改成了共享表空間，分別存儲到各個磁碟下。

最初始時只要是個innodb引擎的表，都需要將數據行，索引，元信息和undo信息都存入到共享表空間，共享表空間再以不可見的形式存入到各大磁碟下，全都存入了ibdata1中。這樣做的好處是可以實現類似Oracle的表空間管理模式，做一些擴容。問題是所有數據全部寫入到幾個有限的文件中，一旦數據多了，處理效率就會出現嚴重下降

所以到了5.6版本就改成了獨立表空間模式，獨立表空間解決了共享表空間數據"扎堆"的問題,解決方式是獨立存儲每一張表，每張表都自己的ibd,frm,見下圖

需要知道現在共離表空間還在，只不在存儲每張表的數據了，而是存儲庫的元信息和undo信息。而獨立表空間存儲數據行和索引信息

對於mariaDB和PerconaDB和5.5版本，需要更新，否則一旦數據量增長到一定量級，會導致性能下降

對於共享表空間(ibdata1~N)，自5.5版本以來，其負責的職責就被不同的程度的剝離

5.6版本以後，共享表空間得以保留，只用來存儲：數字字典信息，undo信息，臨時表

5.7版本以後，臨時表被剝離

8.0版本以後，undo信息被剝離

查看共享表空間路徑

select @@innodb_data_file_path;

下圖表示，第一次啟動時創建共享表空間ibdata1,初始大小為12M,空間不夠時，會自動增長64M的空間

使用du -sh 命令查看文件大小

du -sh /data/mysql/data/ibdata1;

查看共享表空間默認每次自動增長值

show variables like 'innodb_data_file_path'; show variables like '%extend%'

共享表空間參數的配置需要要創建資料庫服務時配置，否則無法修改

獨立表空間

從MySQL5.6版本開始，默認表空間已經變成了獨立表空間，而不再是共享表空間

獨立表空間主要用於存儲用戶數據

每一個表都有一個ibd文件，用於存儲數據及索引信息

基本表結構元數據存儲在frm文件

mysql數據表是由元數據，數據，索引構成

mysql資料庫是由ibdata1~N和frm(元數據)，ibd文件(段區頁)

一張InnoDB表由frm,ibd,ibdata1構成

查看獨立表空間變量

select @@innodb_file_per_table;

如果將innodb_file_per_table設置為0，那麼以後創建的數據表，就沒有了獨立表空間了

修改獨立表空間變量

set global innodb_file_per_table=0;

不建議修改此變量，原因是改為0後，所以表將存儲到共享表空間中，數據量增長後，容易導致資料庫性能瓶頸

MySQL的存儲引擎日誌

Redo Log: ib_logfile0 ib_logfile1 重做日誌

Undo Log: ibdata1 ibdata2(存儲在共享表空間中)，回滾日誌

臨時表:ibtmp1,在做join union操作產生臨時數據，用完就自動清理

表空間遷移

建立與原表一樣的空表discard空表的ibd文件複製原表的ibd文件，修改權限，import原表的ibd文件查詢驗證 alter table city dicard tablespace; alter table city import tablespace;

相關焦點

  • mysql 版本號解釋_mysql workbench查詢mysql版本號 - CSDN
    在設計資料庫結構的時候，要儘量遵守三範式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協資料庫的設計。mysql有關權限的表都有哪幾個MySQL伺服器通過權限表來控制用戶對資料庫的訪問，權限表存放在mysql資料庫裡，由mysql_install_db腳本初始化。
  • 京東面試:說說MySQL的架構體系
    因為在關係資料庫中數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型(Table Type，即存儲和操作此表的類型)。MySQL5.5版本(mysql 版本 < 5.5版本) 以前，默認使用的存儲引擎是MyISAM 。MySQL5.5版本(mysql 版本 >= 5.5版本) 以後，默認使用的存儲引擎是InnoDB 。
  • 圖解MySQL索引——B-Tree(B+Tree)
    非聚簇索引：不是聚簇索引，就是非聚簇索引四、索引的底層實現mysql默認存儲引擎innodb只顯式支持B-Tree( 從技術上來說是B+Tree)索引，對於頻繁訪問的表，innodb會透明建立自適應hash索引，即在B樹索引基礎上建立hash索引，可以顯著提高查找效率，對於客戶端是透明的
  • mysql 矩陣類型專題及常見問題 - CSDN
    表是數據的矩陣。外鍵：外鍵是用於關聯兩個表。ENGINE 設置存儲引擎，CHARSET 設置編碼。LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。RIGHT JOIN（右連接）： 與 LEFT JOIN 相反，用於獲取右表所有記錄，即使左表沒有對應匹配的記錄。
  • 一千行MySQL學習筆記
    -- 清空表數據    TRUNCATE [TABLE] 表名-- 複製表結構    CREATE TABLE 表名 LIKE 要複製的表名-- 複製表結構和數據    CREATE TABLE 表名 [AS] SELECT * FROM 要複製的表名-- 檢查表是否有錯誤    CHECK TABLE tbl_name [, tbl_name
  • MySQL InnoDB 索引原理
    目錄InnoDB表結構B樹與B+樹聚簇索引和二級索引SQL執行順序SQL優化建議一些問題分析參考資料1. InnoDB表結構此小結與索引其實沒有太多的關聯，但是為了便於理解索引的內容，添加此小結作為鋪墊知識。
  • 高性能Mysql主從架構的複製原理及配置詳解
    請注意當你進行複製時，所有對複製中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免用戶對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的衝突。mysql支持的複製類型：基於語句的複製：在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL默認採用基於語句的複製，效率比較高。
  • mysql統計日活、周活、月活
    mysql分組函數、組函數、聚合函數、統計函數直接po代碼，代碼中有注釋#二、分組函數/*功能：用作統計使用，又稱為聚合函數或統計函數或組函數分類：sum 求和、avg 平均值
  • 月薪3W,面試官問:詳細聊聊MySQL中 聚簇、非聚簇索引和覆蓋索引
    如果一張表一旦加了索引，比如加了自增主鍵，那麼表中的數據在磁碟中就不是按順序排列的，而是變成了樹狀結構，也就是我們常說的平衡樹，換句話說就是整個表都變成了一個索引樹，也就是所謂的聚簇索引。這也就說明白了為什麼一個表只能有一個自增主鍵以及只能有一個聚簇索引了，因為自增主鍵的作用根兒上就是根據一定算法把表的數據按照一定格式轉換成平衡樹存放在磁碟的。
  • 如何把Access的數據導入到Mysql中
    使用mysqldump實用程序 下面我們來舉例說明： 假設我們的資料庫中有一個庫為samp_db,一個表為samp_table。現在要把samp_table的數據導出。則我們可以利用以下方法來實現： 在Mysql提示符下打入select * from samp_table into outfile 「file_name」; 在系統命令提示符下打入mysqldump –u root samp_db samp_table >samp.sql (當然mysqldump有很多選項．如 －d 表示只導出表結構；　－t 表示只導入表數據
  • 基於MySQL資料庫應用開發實現嵌入式數控系統的設計
    如果採用直接將數據存放在一個表或字符數組中，數據的修改與維護會相當困難、繁雜，這與數控系統對數據操作要求簡單方便與快捷相悖，因此這種組織管理方式不能滿足數控系統的要求。目前數控加工系統普遍採用的文本文件格式存儲管理數據的方式也存在著實時操作性較差和磁碟空間浪費嚴重的缺點，而實時性和存儲空間兩個要素對於高性能嵌入式數控系統來說卻是至關重要的。
  • (Mysql優化系列3)
    B-樹因此具有以下幾個特點：1）、葉節點具有相同的深度，葉節點的指針為空2）、所有索引元素不重複3）、節點中的數據索引從左到右遞增排列看完數據結構，我們看B樹做索引的結構又如何呢，mysql默認索引節點為16KB，當然可以修改大小。
  • MySQL 數據校驗工具-愛可生|mysql|perl|伺服器|node01_網易訂閱
    它能在非常大的表上工作的一個原因是，它把每個表分成行塊，並檢查每個塊與單個替換。選擇查詢。它改變塊的大小，使校驗和查詢在所需的時間內運行。分塊表的目的是確保校驗和不受幹擾，並且不會在伺服器上造成太多複製延遲或負載，而不是使用單個大查詢處理每個表。這就是為什麼默認情況下每個塊的目標時間是 0.5 秒。　　2.
  • 將mysql數據導入access資料庫
    mysql資料庫表sqltable 欄位id，name，sex，email access資料庫表accesstable id，name，sex，email ?
  • 0064 關係資料庫的概念和安裝使用MySQL
    關係資料庫就是資料庫中的表採用二維表格來存儲數據，是一種按行與列排列的具有相關信息的邏輯組，它類似於Excle工作表。表和表之間可以通過數據關係進行關聯。關係資料庫有以下關鍵名詞：資料庫服務程序：實現資料庫機制並對外提供數據訪問和存儲服務的程序。資料庫客戶端程序：用來訪問和管理資料庫結構和數據的程序。
  • 如何向mysql導入數據
    一、導入.sql文件1.mysql命令導入數據基本語法：mysql -h伺服器地址 -u用戶名 -p 資料庫名< 要導入的資料庫文件路徑例：導入G:2.mysqlimport導入數據基本語法：mysqlimport -h伺服器地址 -u 用戶名 -p [--local] 資料庫名 文件路徑 [options]options常用選項：
  • Mysql(Mariadb)資料庫主從複製
    #設置server-idlog-bin="/var/lib/mysql/" #設定生成的log文件名； 修改後：# systemctl restart mariadb.service2.重啟mysql，創建用於同步的用戶帳號# mysql -hlocalhost -uroot -ppassword創建用戶並授權：用戶
  • MySQL 8.0 正式版 8.0.11 發布:比 MySQL 5.7 快 2 倍
    通用表表達式(Common Table Expressions CTE)：在複雜的查詢中使用嵌入式表時，使用 CTE 使得查詢語句更清晰。7. UTF-8 編碼：從 MySQL 8 開始，使用 utf8mb4 作為 MySQL 的默認字符集。8.
  • MySQL 8.0 正式版 8.0.11 發布:比 MySQL 5.7 快 2 倍 - OS...
    通用表表達式(Common Table Expressions CTE)：在複雜的查詢中使用嵌入式表時，使用 CTE 使得查詢語句更清晰。7. UTF-8 編碼：從 MySQL 8 開始，使用 utf8mb4 作為 MySQL 的默認字符集。8.