當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql ---- innodb-1- 体系结构、文件、表

發布時間：2024/9/15 数据库 23 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql ---- innodb-1- 体系结构、文件、表小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

mysql的體系結構

連接池組件
管理服務和工具組件
sql接口組件
查詢分析器組件
優化器組件
緩沖組件
插件式的存儲引擎
物理文件

Mysql 區別預其他數據庫最重要的特點就是插件式的表存儲引擎

1 存儲引擎簡易介紹：

1.1 innoDb存儲引擎

支持事務, 主要面向在線事務處理(OLTP online transaction processing)方面的應用

特點: 行鎖設計, 支持外鍵, 非鎖定讀

innoDb存儲引擎將數據放在一個邏輯的表空間中, 將每個innodb存儲引擎的表單獨放到一個獨立的ibd文件中.

功能:
- next-key locking策略避免幻讀
- 插入緩存insert buffer
- 二次寫double write
- 自適應哈希索引 adaptive hash index
- 預讀 read ahead
- 通過多版本并發控制(MVCC)來獲得高并發性
存儲
- 采用聚集(clustered), 每張表都按照主鍵的順序存儲, 如果沒有顯示的指定主鍵, innodb引擎或為每一行數據生成一個6字節的rowid作為主鍵

1.1.2 MyISAM存儲引擎

官方提供的引擎, 對于一些OLAP( online analytical processing在線分析處理)操作速度快.

特點: 不支持事務,支持表鎖,支持全文索引
構成: MYD和MYI, MYD用來存放數據文件, MYI用來存放索引文件. (可以通過myisampack 來壓縮數據文件, 壓縮后只讀)
數據量支持
- 5.0版本之前, 默認表大小 5G (如果要修改,需要調整: MAX_ROW和AVG_ROW_LENGTH 屬性)
- 5.0后, 默認支持256T

1.3 NDB存儲引擎

集群存儲引擎

特點: 數據全部放在內存中(5.1版本后,可以將非索引數據放在磁盤上), 因此主鍵查找(primary key lookup)的速度快,并通過添加NDB數據存儲節點(data node) 可以線性的提供數據庫性能, 是高可用/高性能的集群系統
注意: NDB存儲引擎的鏈接操作(join)是在mysql數據層完成的, 而不是在存儲引擎層完成的, 這意味著鏈接操作需要巨大的網絡開銷, 因此查詢慢

1.1.4 Memory存儲引擎

特點: 數據在內存中, 非常適合用于存儲臨時數據的臨時表,以及數據倉庫的緯度表, 默認使用hash索引
只支持表鎖,并發性差. 不支持text和blob類型
存儲varchar時按照char定長方式進行

1.5 Archive存儲引擎

非常適合存儲歸檔數據，如：日志信息

特點: 只支持insert和select操作， 5.1版本開始支持索引
使用zlib算法將行數局(row)進行壓縮存儲
使用行鎖來實現高并發插入操作(但本身并不是事務安全的存儲引擎，其設計目標主要是提供高速的插入和壓縮功能)

1.6 Federated存儲引起

表并不存儲數據，只是指向一臺遠程mysql數據庫服務器上的表(不支持異構數據庫表)

1.7 Maria存儲引擎

新開發的引擎，目標時取代原有的MyISM存儲引擎

2 InnoDb體系架構

體系架構：innodb有多個內存塊，這些內存塊組成了一個大的內存池
- 維護所有進程/線程需要訪問的多個內部數據結構
- 緩存磁盤上的數據，方便快速的讀，并且對磁盤文件進行修改之前在這里緩存
- 重做日志(redo log)緩沖
后臺線程的主要作用是負責刷新內存池中的數據，保證緩存池中的內存緩存是最近的數據，此外，將已修改的數據文件刷新到磁盤文件，同時保證在數據庫發生異常下InnoDb能夠恢復到正常運行狀態。

2.1 后臺線程

組成共7個線程
- 4個IO thread (insert buffer thread、 log thread、 read thread、 write thread)
- 1個master thread
- 1個鎖lock監控線程
- 1個錯誤監控線程
IO thread的數量由配置文件中的inoodb_file_io_thread參數控制，默認為4 (linux平臺下不可用)

2.2 內存

InnoDB存儲引擎是基于磁盤存儲的，并將其中的記錄按照頁的方式進行管理

組成
- 緩沖池(buffer pool) --- innodb_buffer_pool_size (從InnoDB1.0.x版本開始，允許設置多個緩沖池實例，即參數innodb_buffer_pool_instances)
  
  緩沖池是占最大塊內存的部分，工作方式：將數據庫文件按頁(每頁16k)讀到緩沖池，然后按照最少使用(LRU)的算法來保留其中的數據
  
  如果要修改數據，總是先修改緩沖池中的頁(修改后即為臟頁)，即緩沖池中的數據和磁盤上的數據不一致，這時數據庫會通過checkpoint機制將臟頁刷新回磁盤，而Flush列表中的數據即為臟頁列表(臟頁既存在與LRU列表，也存在與Flush列表，LRU列表用來管理緩沖池中頁的可用性，Flush列表用來管理將頁刷新回磁盤，二者不影響)
  - 包含的數據頁類型
    - 索引頁
    - 數據頁
    - undo頁
    - 插入緩存(insert buffer)
    - 自適應hash索引(adaptive hash index)
    - Innodb存儲的鎖信息(lock info)
    - 數據字典信息(data dictionary)
    - 等
  - LRU List/ Free List / Flush List
    緩沖池占很大的一片內存區域，那么如何管理呢？通常來說，數據庫中的緩沖池是通過LRU算法來管理的，即頻繁使用的頁在LRU列表的前端，最少使用的頁在LRU列表的尾部。當緩沖池不能存放新讀取到的頁時，將首先釋放LRU列表中尾端的頁。
    在InnoDB引擎中，緩沖池中頁的大小為16kb，同樣使用LRU算法對緩沖池進行管理。稍有不同的是對LRU算法進行了優化。即在LRU列表中加入了midpoint位置，即新讀取到的頁并不是直接放入到LRU列表的首部，而是放入到midpoint位置(在默認配置下，midpoint在LRU列表的5/8處。該大小由參數innodb_old_blocks_pct控制，該值默認為37，即尾部的3/8處)。
    在InnoDB引擎中，把midpoint之后的列表稱為old列表，之前的成new列表(可以簡單的理解new列表中的數據為熱點數據)
    InnoDB引擎從1.0.x版本開始支持壓縮頁功能，即將原本16kb的頁壓縮成1kb、2kb、4kb、8kb。由于頁的大小發生了變化，LRU列表頁發生了變化，對于非16kb的頁，通過unzip_LRU列表來進行管理，通過show engine innodb status來觀察(LRU的數量包含unzip_LRU的數量)。
    unzip_LRU是如何分配內存的呢？(錄入申請4kb的內存)
  - 檢查4kb的unzip_LRU列表是否有空閑頁，若有則直接使用
  - 沒有則檢查8kb的unzip_LRU列表
  - 若能得到空閑頁，則將之分成兩個4kb，并將其加入到4kb的unzip_LRU列表
  - 若不能得到，則從LRU列表中申請一個16kb的頁，將其分成1個8kb的頁、2個4kb的頁，分別存入對應的unzip_LRU列表
- 重做日志緩沖池(redo log buffer) --- innodb_log_buffer_size
  InnoDB引擎首先將重做日志信息放入這個緩沖區，然后以一定的頻率將其刷新到重做日志文件。重做日志緩沖一般不需要多大，因為一般情況下每秒都會講重做日志緩沖刷新到日志文件。(默認為8MB)
  以下三種情況會刷新到文件：
- Master Thread每一秒將重做日志緩沖刷新到文件
- 每個事務提交
- 當重做日志緩沖池剩余空間小于1/2時
- 額外的內存池(additional memory pool)--- innodb_additional_men_pool_size

checkpoint 技術
目的是為了解決以下幾個問題：
縮短數據庫的回復時間
緩沖池不夠用時，刷新臟頁
重做日志不可用時，刷新臟頁

2.3 master thread

innodb存儲引擎的主要工作都是在一個單獨的線程master thread 中完成的。

master thread 優先級最高

組成
- 主循環 (loop)
  
  大多數的操作都是在loop中，其操作分為兩大部分：每秒鐘的操作和每十秒鐘的操作
  void master_thread() { loop; for(int i = 0; i < 10; i++){// do thing once per second// sleep 1 second if necessary } // do things once per ten seconds goto loop; }
  loop循環主要通過sleep來實現(每秒和每10秒并不是精確的)
  - 每秒操作：
    - 重做日志緩沖刷新到磁盤，即使這個事務還沒有提交(總是)
    - 合并插入緩沖(可能)
    - 至多刷新100個innodb的緩存池中的臟頁到磁盤(可能)
    - 如果沒有用戶操作，切換到background loop(可能)
  - 每十秒操作：
    - 刷新100個臟頁到磁盤(可能)
    - 合并至多5個插入緩沖(總是)
    - 將日志緩沖刷新到磁盤(總是)
    - 刪除無用的undo頁(總是)
    - 刷新100個或者10個臟頁到磁盤(總是)
    - 產生一個檢查點(總是)
- 后臺循環(background loop)
- 刷新循環(flush loop)
- 暫停循環(suspend loop)

2.4 關鍵特性

包括：插入緩沖、兩次寫(double write)、自適應哈希索引(adaptive hash index)、異步io(Async IO)、刷新鄰接頁(Flush neighbor page)

2.4.1 插入緩沖

2.4.1.1 Insert Buffer

insert buffer和數據頁一樣，也是物理頁的一個組成部分

我們知道，主鍵是行唯一的標識符，在應用程序中行記錄的插入順序是按照主鍵遞增的順序進行插入的。因此，插入聚集索引一般是順序的，不需要磁盤的隨機讀取。比如： >mysql create table t (id int auto_increment, name varchar(20), primary key(id));

id是自增長的，這意味著當執行插入操作時，id列會自動增長，頁中的行記錄按id執行順序存放。

一般情況下不需要隨機讀取另一頁執行記錄的存放。這種情況下插入操作一般很快能完成。

但是，如果表中存在一個非聚集的輔助索引(secondary index)。比如：按照name這個字段查找，并且name不是唯一的，建表語句如下：

>mysql create table t(id int auto_increment, name varchar(20), primary key(id), key(name))

這樣就產生了一個非聚集的并且不是唯一的索引。在插入時，數據頁的存放還是按主鍵id的執行順序存放，但是對于非聚集索引，葉子節點的插入不是順序的了。這時就需要離散的訪問非聚集索引頁，插入性能在這里就變低了。

插入緩存對于非聚集索引的插入Insert和更新update操作，不是一次直接插入到索引頁種，而是先判斷插入的非聚集索引頁是否在緩沖池中，如果存在，則先入InesrtBuffer 對象中，然后再以一定的頻率執行插入緩沖和非聚集索引頁子節點的合并操作，這樣就大大提高了性能。

插入緩沖的使用要滿足兩個條件：

索引是輔助索引

索引不是唯一的

存在的問題： 在寫密集的情況下，會占用大量的緩沖池內存(innodb_buffer_poll)，默認最大可以占用1/2的內存

2.4.1.2 Change Buffer

InnoDB從1.0.x 版本開始引入了Change Buffer，可將其視為Insert Buffer 的升級版，從這個版本開始，InnoDB引擎可以對DML操作--INSERT/DELETE/UPDATE都進行緩沖，即：Insert Buffer、Delete Buffer、Purge Buffer

和Insert Buffer 一樣，ChangeBUffer 適用于非唯一的輔助索引。

對一條 UPDATE 操作可以分為兩個過程：

將記錄標記為刪除(Delete Buffer 對應 UPDATE 操作的第一個過程)

真正將記錄刪除(Purge Buffer 對應 UPDATE 的第二個過程)

2.4.1.3 Insert Buffer 的內部實現

Insert Buffer 使用場景：非唯一索引的插入操作

Insert Buffer 的數據結構是一棵B+樹(在 Mysql4.1版本之前，每個表都有一個對應的B+樹，現在版本中，全局只有1個B+樹，負責對所有的輔助索引的緩存，這個B+樹存放在共享表空間中(ibdata1))

Insert Buffer 是一棵B+樹，因此是由葉子節點和非葉子節點構成。非葉子節點存放的是查詢的 search key(鍵值)：

search key 一共占用9個字節。

space 表示帶插入記錄所在表的表空間 id(在 Innodb 引擎中，每個表都有一個唯一的 spaceid)，占4個字節

marker是用來兼容老版本的 Insert Buffer，占1個字節

offset表示頁所在的偏移量，占4個字節

插入過程：

? 當一個輔助索引要插入到頁(space，offset)時，如果該頁不在緩沖池中，那么 InnoDB 引擎首先更具上述規則構造一個 search key，接下來將這條記錄插入的 Insert Buffert 中(插入并不是直接插入的，需要根據以下規則進行構造)：

space、marker、page_no 字段和之前一樣，占用9個字節。matadata 占用4個字節。因此同樣的記錄，InsertBuffer 中的記錄比原記錄多額外的13字節的開銷。

啟用 Insert Buffer 索引后，輔助索引頁(space，page_no)中的記錄可能被插入到 Insert Buffer 的B+樹中，所以為了保證每次 Merge Insert Buffer成功，需要有一個特殊的頁用來標記每個輔助索引頁的可用空間(也就是 Insert Buffer Bitmap)。

每個 Insert Buffer Bitmap 頁用來跟蹤16384個輔助索引頁，也就是256個區(extent)，每個 Insert Buffer Bitmap 頁都在16384個頁的第二個頁中。

2.4.1.4 Merge Insert Buffer

Merge Insert Buffer 的操作發生的幾種情況：

輔助索引頁被讀取到緩沖池

Insert Buffer Bitmap 頁追蹤到該輔助索引頁已無可用空間

Master Thread

2.4.2 兩次寫

插入緩存帶來的是性能，兩次寫帶來的是可靠性

寫失效(partial page wirte)：數據庫正在寫頁時，數據庫宕機，此時頁(16k)只寫了一部分

double write：在執行重做日志之前，我們需要一個副本，當寫失效發生時，先通過副本來還原頁，然后再進行重做。

由兩部分組成：一部分是內存中的doublewrite buffer，大小為2MB；另一部分時物理磁盤上共享表空間(ib_datafile)中連續的128個頁(128*16k=2MB)，即兩個區(extent)。

當緩沖池中的臟頁刷新時，并不直接寫磁盤，而是會通過memcpy函數將臟頁先拷貝到內存中的doublewrite buffer，之后通過doublewrite buffer再分兩次，每次寫入1MB到共享表空間的物理磁盤上，然后馬上調用fsync函數，同步磁盤，避免緩沖寫帶來的問題。再完成doublewrite頁的寫入后，再將doublewrite buffer中的頁寫入各個表空間文件中，此時寫入是離散的。

通過命令show global status like 'innodb_dblwr_%'可以看到 double write 的運行情況：

可以看到 double write 一共寫了6325194個頁，但實際寫入次數為100399，基本符合64：1.如果系統高峰時 Innodb_dblwr_pages_writtens：innodb_dblwr_writes 遠小于64：1時，說明系統寫入壓力不高。

2.4.3 自適應哈希索引(Adaptive Hash Index， AHI)

hash 是一種查詢非常塊的方法，時間復雜度 O(1)，B+樹的查詢復雜度基于樹的高度，一般生產環境中，B+樹的高度一般為3_{4層，顧需要3}4次查詢

innodb存儲引擎會監控對上索引的查找，如果觀察到建立hash索引可以帶來速度的提升，則建立哈希索引

自適應hash索引通過緩沖池的B+樹構造而來的，因此建立的速度很塊，而且不需要對整個表都建立，InnoDb會自動根據訪問的頻率和模式為某些頁建立hash索引

2.4.4 異步 IO

為了提高磁盤操作性能，當前數據庫系統都采用異步 IO 的方式來處理磁盤操作.

在 Innodb 存儲引擎中，read ahead、臟頁的刷新(即磁盤寫入操作)都是有 AIO 完成。

-- 查看是否開啟native io show variables like 'innodb_use_native_io';

2.4.5 刷新鄰接頁(Flush Neighbor Page)

原理：當刷新一個頁時，Innodb 引擎會檢測該頁所在的區(extent，64個頁)的所有頁，如果是臟頁，那么一起刷新。

好處：通過 AIO 可以將多個 IO 寫入操作合并為一個 IO 操作

參數：innodb_flush_neighbors

注意：對于傳統機械硬盤建議開啟該特性，對于固態硬盤，建議關閉(0)

2.5 啟動、關閉和恢復

參數

innodb_fast_shutdown
- 0: 代表關閉時，innodb需要完成所有的full purge和merge insert buffer(耗時)
- 1:默認值，不需要完成上述的操作，但是在緩沖池的一些數據還是會刷新到磁盤
- 2:不需要完成full purge和merge insert buffer，也不刷緩沖池中的數據，而是將日志都寫入日志文件，這樣不會有任何事務丟失，但是mysql在下次啟動時，回執行恢復操作(recovery)
Innodb_force_recovery，影響恢復行為
- 0：默認，默認會在需要恢復的時候執行恢復行為
- 其他1~6

2.6 InnoDB Plugin = 新版本的InnoDB存儲引擎

3 文件

[ ] 參數文件：mysql實例啟動時在哪里找到數據庫文件，并且指定某些初始化參數
[ ] 日志文件：用來記錄mysql實例對某些條件做出響應時寫入的文件，如：錯誤日志文件、二進制文件、慢查詢文件、查詢日志文件
[ ] socket文件：使用Unix域套接字連接mysql時需要的文件
[ ] pid文件
[ ] 表結構文件： ddl文件
[ ] 存儲引擎文件

3.1 參數文件

3.1.1 參數類型

動態參數

可以在mysql運行期間修改

修改使用set
set [global|session] system_var_name； select @@session.read_buffer_size \G;
靜態參數

在運行期間不可修改

3.2 日志文件

包括：
- 錯誤日志
- 慢查詢日志
- 查詢日志
- 二進制日志

3.2.1 錯誤日志

錯誤日志文件對Mysql的啟動、運行、關閉過程進行了記錄

show variables like 'log_error'; -- 查詢錯誤日志文件的路徑

3.2.2 慢查詢日志

-- 慢查詢時間閾值大于時間閾值的會記錄慢日志 show variables like 'long_query_time';-- 啟動慢查詢日志(默認不啟動) show variables like 'slow_query_log';-- 運行的sql沒有使用索引，開啟改參數后也會記錄慢日志 show variables like 'log_queries_not_using_indexes';

分析慢查詢日志 mysqldumpslow命令
mysql5.1 開始可以將慢查詢日志記錄到一張表中 mysql.slow_log(該表默認使用的引擎時CSV，可以修改為MyISM)
-- 參數log_output指定了慢查詢的輸出格式，默認為FILE, 可以將它設置為TABLE,然后慢查詢日志進入slow_log表 show variables like 'log_output'; set global log_output = 'TABLE';-- 修改引擎 alter table slow_log engine=myisam;

3.2.3 查詢日志

記錄了所有對Mysql數據庫的請求信息，不論這些請求是否正確執行

默認文件名：主機名.log

日志也可以進表：general_log

3.2.4 二進制文件

記錄了對數據庫執行更改的所有操作(不包括select和show這類操作)

默認不啟動

作用
- 恢復(recovery)，某些數據恢復需要二進制文件，如：當一個數據庫全備文件恢復后，可以通過二進制文件日志進行point-in-time的恢復
- 復制(replication)，和遠程數據庫(slave或者standby)進行實時同步
- 審計(audit)
設置
通過配置參數log-bin[=name]可以啟動二進制，如不指定name，則默認二進制文件名為主機名，后綴名為二進制日志的序列號，所在路徑為數據庫所在目錄
-- 數據庫所在目錄 show variables like 'datadir';
相關參數
- max_binlog_size
  
  單個二進制文件的最大值，超過該值，則產生新的二進制日志文件，后綴名+1 默認大小為1 073 741 824(1GB)
- binlog_cache_size
  
  當使用事務存儲引擎(如innoDB)時，所有未提交(uncommitted)的二進制日志會被記錄到一個緩存中，等該事務提交時直接將緩沖中的二進制日志寫入到二進制日志文件，該緩沖的大小有binlog_cache_size控制 (基于session，每個線程都會分配一個二進制日志緩沖)，默認為32kb
  
  可以通過show global status like 'binlog_cache%';查看使用臨時文件寫二進制的次數
- sync_binlog 等于1時，控制同步寫入二進制日志
- binglog_do_db 哪些表寫二進制日志文件
- binglog_ignore_db 哪些表不寫二進制日志文件
- log_slave_update 從庫是否寫二進制文件 (主-> 從 -> 從第一個從需要開啟log_slave_update)
- binlog_format
  - STATEMENT 語句
  - ROW, 同常情況下設置為row，可以為數據庫的恢復和復制帶來更好的可靠性
  - MIXED
查看

binlog_format為statement時：直接使用 mysqlbinlog --start-position=*** test.000004

如果為row時，需要加上參數-vv

3.3 套接字文件

Unix系統下連接mysql可以使用Unix域套接字方式，這種方式需要套接字文件(socket) show variables like 'socket'\G;

3.4 pid文件

mysql實例啟動時，會將自己的進程ID寫入一個文件中---即pid文件，該文件可有參數pid_file控制

默認路徑位于數據庫目錄下，文件名為主機名.pid

show variables like 'pid_file';

3.5 表結構定義文件

因為mysql插件式存儲引擎的體系， mysql對于數據的存儲是按照表的，所以每個表都有有與之對應的文件。

無論采用何種引擎，都會有一個frm為后綴的文件，該文件記錄了表結構定義(如果有視圖，也會有對應的frm文件)

frm文件可以直接cat查看

3.6 Innodb存儲引擎文件

每個存儲引擎都有自己獨有的文件

表空間文件

默認配置下，會有一個初始大小為10M，名為ibdata1的文件。該文件就是默認的表空間文件(tablespace file)，可以通過參數innodb_data_file_path進行設置 innodb_data_file_path=datafile_spec1[;datafile_spec2]

也可以使用多個文件組成一個表空間，同時指定文件的屬性
-- 以下有兩個表空間文件，如果兩個文件位于不同的磁盤上，則可以提升一定的性能 autoextend(自動增長) innode_data_file_path=ibdata1:20M;ibdata2:20M:autoextend
設置innodb_data_file_path后，對于所有基于Innodb存儲引擎的表數據都會記錄到改文件內。

如果設置innodb_file_per_table，可以將每個基于Innodb引擎的表單獨產生一個表空間，文件名為表名.ibd (這些單獨的表空間僅存儲了該表的數據、索引、插入緩沖等信息，其余信息還放在默認表空間中)
重做日志文件

默認情況下會有兩個文件，ib_logfile0和ib_logfile1。稱為Innodb存儲引擎的日志文件(更準確應該校重做日志文件)。記錄了對于innodb存儲引擎的事務日志。

每個innodb存儲引擎至少有一個重做日志文件組(group)，每個文件組下至少有兩個重做日志文件，如默認的ib_logfile0和ib_logfile1.

為了提高可靠性可以設置多個鏡像日志組(mirrored log groups)，將不同的文件組放在不同的磁盤上
- 影響參數
  - Innodb_log_file_size 指定重做日志文件的大小
  - Innodb_log_files_in_group 日志文件組中重做日志文件的個數，默認為2
  - Innodb_mirrored_log_groups 日志鏡像文件組的數量默認為1，表示沒有鏡像組
  - innodb_log_group_home_dir 日志文件組路徑

4 表

分析Innodb存儲引擎的物理存儲特征---數據是如何組織和存放的。 (簡單來說：表就是關于特定實體的數據集合，這也是關系型數據庫的核心)

4.1 InnoDB存儲引擎表類型

在InnoDB存儲引擎表中，如果在創建表時沒有顯式的定義主鍵(primary key)，則InnoDB存儲引擎會按照以下方式選擇或創建主鍵：1. 首先表中是否有非空的唯一索引(Unique not null)，如果有，則該列為主鍵 2. 不符合時，InnoDB存儲引擎會自動創建一個大小為6字節的指針

4.2 InnoDB邏輯存儲結構

InnoDB存儲引擎的表，所有數據都被邏輯的存放在一個空間中，也就是表空間(tablespace)，表空間又由段(segment)、區(extend)、頁(page)組成。頁在有的文檔里邊也稱塊(block)。

4.2.1 表空間

表空間可以看做時InnoDB存儲引擎邏輯結構的最高層，所有數據都存儲在表空間中。默認情況下InnoDB由一個共享表空間ibdata1，即所有數據都放在這個表空間內。如果啟用了參數innodb_file_per_table，則每張表內的數據可以單獨放在一個表空間內(ibd文件，該文件內只是存放數據、索引和插入緩沖)

4.2.2 段

創建的段：數據段、索引段、回滾段等(前邊說過InnoDB存儲引擎時索引組織的，因此數據即索引，索引即數據，那么數據段即為B+樹頁節點，索引段即為B+樹的非索引節點)不是每個對象都有段，表空間是由分散的頁和段組成。

4.2.3 區

區是由64個連續的頁組成，每個頁大小為16kb，即每個區大小為1MB。對于大的數據段，InnoDB存儲引擎最多每次可以申請4個區，以此來保證順序性能。

問題：在啟用參數innodb_file_per_table后，創建的表默認大小為96kb。區是64個連續的頁，那創建的表大小至少應該時1MB才對？

是因為在每個段開始時，現有32個頁大小的碎片頁來存放數據，當這些頁使用完之后，才是64個連續頁的申請。

4.2.4 頁

頁是InnoDB磁盤管理的最小單位。默認每個頁大小為16kb。從 Innodb1.2.x 版本開始，可以通過參數 innodb_page_size 設置頁的大小

常見的頁：
- 數據頁(B-tree node)
- Undo頁 (Undo log page)
- 系統頁(system page)
- 事務數據頁(transaction system page)
- 插入緩沖位圖頁(insert buffer bitMap)
- 插入緩沖空閑列表頁(insert buffer free list)
- 未壓縮的二進制大對象頁(uncompressed blob page)
- 壓縮的二進制大對象頁(compress blob page)

4.2.5 行

InnoDB存儲引擎是面向行的，也就是說數據的存放按行進行存放。每個頁存放的行記錄也是由硬性規定的，最多允許存放16kb / 2 - 200行記錄，即7992行。(16*1024/2 - 200)-- 7992怎么算的每個記錄最少2個字節，每個頁預留200字節(預留為規定) 因此：16*1024/2 - 200 -- 每個頁至少兩行記錄最小記錄和最大記錄，用來區分邊界

Innodb 引擎提供兩種行記錄格式：compact(默認)、redundant

可以通過show table status like 'mytest%'查看和記錄格式

4.3 InnoDB 物理存儲結構

從物理意義上來看，InnoDB表是共享表空間、日志文件組(redo文件組)、表結構定義文件組成。若將innodb_file_per_table設置為on，則每個獨立的產生一個表空間文件，以ibd結尾，數據、索引、表的內部數據字典信息保存在這個獨立的表空間文件中。表結構定義文件以frm結尾，這個是和存儲引擎無關的，任何存儲引擎的表結構定義文件都一樣。

4.4 InnoDB行記錄格式

4.3 InnoDB 行記錄格式

InnoDB存儲引擎數據記錄是以行的形式存儲的(意味著頁中保存著表中一行行的數據)

show table status like 'table_name' -- 查看表記錄的存儲格式

頁的存儲格式
- Compact(目前使用最多的)
- Redundant

4.3.1 Compact行記錄格式

Compact行記錄是在mysql5.0版本之后引入的，目標是高效的存儲數據(簡單來說一個頁中存放的行數據越多，性能也就越高)

從上圖可以看出compact行記錄方式：

變長字段長度列表

Compact行記錄格式的首部是一個非NULL變長字段長度列表，并且是按照列的順序逆序放置的，長度為：
- 若列長度小于255字節(byte)，用1字節表示
- 若大于255字節，用2字節表示
變長字段的長度不能超過2字節，這是因為mysql數據庫中varchar類型的最大長度限制為65535.
NULL標志位

指示了該行數據中是否有NULL值，有則用1表示

該部分所占字節為1字節
記錄頭信息(record header)

固定占用5字節(40位(bit))

列表*數據

NULL列數據不占該部分任何空間。

除了用戶定義的數據列之外還有兩個隱藏列，事務id列和回滾指針列，分別占6字節和7字節大小。若InnoDB沒有定義主鍵，還會有一個6字節的rowid列

4.3.2 Redundant行記錄格式

4.3.3 行溢出數據

InnoDB存儲引擎可以將一條記錄中的某些數據存儲在真正的數據頁面之外。一般認為BLOB、LOB這類的大對象類型的存儲會把數據存放在數據頁面之外。但是BLOB可以不將數據放在溢出頁面，而且即便是VARCHAR列數據類型，依然有可能被存放為行溢出數據。Mysql數據庫的VARCHAR類型可以存放65535字節，但是實際上只能存放65532字節(還有別的開銷)。(如果在建表時沒有將SQL_MODE設置為嚴格模式的話，當創建65535字節的列時，仍然是可以創建成功的，因為mysql會將varchar(65535)轉變為text類型)

需要注意的是：varchar(65532) 默認是IatinL是可以的，但是當字符類型是GBK或UTF-8時，是不能創建的

上邊這個是因為，VARCHAR(N) 中的N指的是字符的長度，而文檔中說VARCHAR類型最大支持的65535，單位是字節。-
注意mysql手冊中定義的65535字節長度，說的是所有varchar列的長度總和，如果列的總和超過，也不能創建
[ ] 即便能夠存儲65532字節， InnoDB引擎的頁為16kb(也就是16384字節)，怎樣存放65535字節呢?

一般情況下，InnoDB引擎的數據都是放在頁類型為B-tree node中，但是，當發生行溢出時，數據存放在頁類型為Uncompress BLOB頁中。(數據頁中只存放了前綴(768字節的前綴)，之后就是偏移量，指向行溢出頁，也就是Uncompress BLOB page)

4.3.4 Compressed 和 Dynamic 行記錄格式

InnoDB 1.0.x版本開始引入了新的文件格式(file format，可以理解為新的頁格式)。以前支持的Compact和Redundant格式稱為Antelope格式，新的文件格式稱為Barracuda文件格式。

新的文件格式對于存放在BLOB中的數據都采用了完全的行溢出的方式，即在數據頁中值存放20字節的指針，實際的數據都存放在Off page中，而舊版本會在數據頁中存放768個前綴字節

Antelope
- Compact
- Redundant
Barracuda
- Compressed
- Dynamic

Compressed行記錄格式的另一個功能：存放在其中的行數據會以zlib的算法進行壓縮

4.3.5 CHAR的行記錄結構

通常理解VARCHAR存儲變長長度的字符類型， CHAR存儲固定長度的字符類型。Mysql4.1 版本之后CHAR(N)中的N指定的是字符長度，而不是之前版本的字節長度，也就是說，在不同的字符集下，CHAR類型列內部存儲的可能不是定長的數據例如： create table a (c CHAR(2) ) engine=innodb charset=gbk; insert into a select '我們'; insert into a select 'ab';-- 字符集是GBK，分別插入了‘我們’和‘ab’，實際存儲的‘我們’占4個字節， ‘ab'占2個字節

對于多字節編碼的CHAR數據類型的存儲，InnoDB引擎在內部會視為變長字符類型，也就是說在變長長度列表中會記錄CHAR數據類型的長度。

4.4 InnoDB數據頁結構

頁是InnoDB存儲引擎管理數據庫的最小磁盤單位。頁類型為B-tree Node的頁存放的即是表中行的實際數據。

InnoDB數據頁由7部分組成：

File Header (文件頭)

Page Header(頁頭)

Infimun和Supermum Records

User Records(用戶記錄，即行記錄)

Free Space(空閑空間)

Page Directory(頁目錄)

File Trailer(文件結尾信息)

其中Filer Header、Page Header、File Trailer大小是固定的，分別為38/56/8字節，這些用來標記該頁的一些信息，如Checksum，數據頁所在的B+數索引的層數。

User Records、Free Space、PageDIrectory這些部分為實際的行記錄粗出空間，大小是動態的

4.4.1 File Header

用來記錄一些數據頁的一些頭信息, 38字節(38B)

記錄數據頁的狀態信息，14部分組成，占56字節

4.4.3 Infimum 和 Supremum Record

在InnoDB引擎中，每個數據頁中有兩個虛擬的行記錄，用來限定記錄的邊界。Infimum記錄是比該頁中任何記錄都小的值Supremum指比任何可能大的值還要大的值這兩個值在頁創建時被建立，并且任何情況下不會被刪除。

4.4.4 User Record 和 Free Space

User Record：實際存儲記錄的內容

Free Space：空閑空間，同樣也是鏈表數據結構(在一條數據被刪除后，該空間會被加入到空閑空間中)

4.4.5 Page Directory

頁目錄中存放了記錄的相對位置，有時候這些記錄指針稱為Slots(槽)或者目錄槽(Directory Slots)。

與其他數據庫系統不同，在InnoDB中并不是每個記錄都擁有一個槽，InnoDB存儲引擎的槽是一個稀疏目錄，即一個槽中可能包含多個記錄。當記錄被插入或刪除時需要對槽進行分裂或平衡的維護操作

B+數索引本省并不能找到具體的一條記錄，能找到的只是該記錄所在的數據頁。數據庫把頁加載到內存，然后通過Page Directory再進行二叉查找

4.4.6 File Trailer

作用：檢驗頁是否已經完整的寫入磁盤

4.6 約束

4.6.1 數據完整性

關系型數據庫系統和文件系統的一個不同點就是，關系型數據庫本身能保證存儲數據的完整性。

數據完整性有以下三證形式：

實體完整性

保證表中有一個主鍵

域完整性

保證每列的值滿足特定的條件，域完整性可以通過以下幾種途徑類保證：

- 選擇合適的數據類型 - 外鍵約束 - 編寫觸發器 - DEFAULT約束

參照完整性

保證兩個表之間的關系

對于InnoDB本身來說，提供了以下幾種約束：

primary key
unique key
foreign key
default
not null

4.6.2 約束的創建和查找

約束的創建：1. 表建立時進行定義2. alter table進行創建

對于Unique key(唯一約束)，可以通過命令CREATE UNIQUE INDEX來建立。

4.6.3 約束和索引的區別

當用戶創建了一個唯一索引就是創建了一個唯一約束。但是約束和索引概念是有所不同的，約束更是一個邏輯概念，用來保證數據的完整性，而索引是一個數據結構，既有邏輯概念，在數據庫中還代表著物理存儲的方式。

4.6.4 對錯誤信息的約束

默認情況下，mysql數據庫允許非法的或不正確的數據插入或更新，又或者可以在數據庫內部將其轉換為一個合法的值，如：向NOT NULL的字段插入一個NULL，mysql數據庫會將其轉換成0再進行插入。

如果想要報錯，而不是警告的話，需要設置sql_mode，用來嚴格審核參數

通過設置sql_mode為STRICT_TRANS_TABLES

4.6.5 ENUM和SET約束

4.6.6 觸發器約束

觸發器作用：在執行INSERT/DELETE/UPDATE命令之前或之后自動調用sql命令或存儲過程。觸發器的創建命令是：CREATE TRIGGER，只有具備Super權限的Mysql數據庫用戶才可以執行： CREATE [DEFINER = [user | CURRENT_USER]] TRIGGER trigger_name BEFORE|ALTER INSERT|UPDATE|DELETE ON tbl_name FOR EACH ROW trigger_stmt

最多可以為一個表創建6個觸發器，即分別是insert、update、delete的before和after各定義一個。

4.6.7 外鍵約束

外鍵用來保證參照完整性。mysql的myisam本身不支持外鍵，對于外鍵的定義只是起到了一個注釋的作用。

4.7 視圖

視圖(VIew)是一個命名的虛表，它是由一個sql查詢來定義，可以當做表使用。

4.8 分區表

mysql支持的幾種分區：

RANGE分區，行記錄基于屬于一個給定連續區間的列值被放入分區
LIST分區，和RANGE分區類似，只是LIST分區面向的是離散的值
HASH分區，根據用戶自定義的表達式的返回值來進行分區
KEY分區，根據mysql數據庫提供的hash函數進行分區

注意：如果表中存在主鍵或唯一索引時，分區列必須是主鍵或唯一索引的一部分。(否則不能創建分區)

分區函數：year() / to_days()/ to_seconds() / unix_timestamp()

4.8.1 RANGE 分區

create table t(id int primary key ) engine=innodb partition by range(id)(partition p0 values less than(10),partition p1 values less than(20),partition p2 values less maxvalue ); -- maxvalue 理解為正無窮 -- 刪除分區 alter table t drop partition p0; -- 會將所有小于10的數據全部刪除

4.8.2 LIST 分區

create table t(id int primary key,b int ) engine=innodb partition by list(id) (partition p0 values in (1,3,5,7,8),partition p1 values in (2,4,6,8,10) );

4.8.3 HASH 分區

create table t (id int,b datetime) engine=innodb partition by hash(year(b)) partitions 4;

4.8.4 KEY 分區

create table t(a int,b datetime ) engine=innodb partition by key(b) partitions 4;

4.8.5 COLUMNS 分區

range、list、hash、key 分區條件：數據必須是整型，如果不是則需要轉化為整型。

mysql5.5版本開始支持 COLUMNS 分區，可以直接使用非整型的數據進行分區

支持的數據類型：

所有的整型類型

日期類型

字符串類型

-- 對于日期類型不需要使用 year()或者 to_days()函數了 create table t(a int,b datetime ) engine=innodb partition by range columns(b) (partition p0 values less than ('2020-01-01'),partition p1 values less than ('2021-01-01') )

總結

以上是生活随笔為你收集整理的mysql ---- innodb-1- 体系结构、文件、表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mysql ---- limit使用方式
下一篇： mysql ---- innodb-2-