4.1 InnoDB存儲引擎表類型

像Oracle 中的索引組織表（index organized table）。每張表都會有主鍵嗤朴，如沒有顯式主鍵贤姆，則
（1）渔欢、表中的非空唯一索引作為主鍵
（2）煎源、自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針

4.2 InnoDB邏輯存儲結(jié)構(gòu)

所有數(shù)據(jù)都被邏輯的存放在一個(gè)空間中色迂，我們稱之為表空間（tablespace）。表空間由段（segment）手销、區(qū)（extent）歇僧、頁（page）組成，頁在一些文檔中有時(shí)也成為塊（block）

InnoDB邏輯存儲結(jié)構(gòu)

4.2.1 表空間

第三章的Innodb_file_per_table參數(shù)想必還記得吧锋拖？設(shè)置為on后每張表將會獨(dú)立存儲诈悍，而不是統(tǒng)一記錄在共享表空間ibdata1中。每張表的表空間中存放的是數(shù)據(jù)兽埃、索引和插入緩存侥钳，其他的如撤銷信息、系統(tǒng)事務(wù)信息柄错、二次寫緩沖還是存放在共享表空間舷夺，所以即使啟用了innodb_file_per_table，共享表空間依然會不斷增大售貌。

4.2.2 段

常見段有數(shù)據(jù)段给猾、索引段、回滾段趁矾。
InnoDB表是索引組織的耙册，數(shù)據(jù)段即為B+樹的葉節(jié)點(diǎn)，索引段即為非葉節(jié)點(diǎn)毫捣。
InnoDB對于段空間管理是自動(dòng)的详拙。

4.2.3 區(qū)

區(qū)是由64個(gè)連續(xù)的頁組成的，每個(gè)頁大小為16KB蔓同，即每個(gè)區(qū)大小為1MB饶辙。
但是當(dāng)我們啟用innodb_file_per_table后，創(chuàng)建的表默認(rèn)大小是96KB斑粱，這是因?yàn)槊總€(gè)段開始有32個(gè)頁的碎片頁（fragment page）弃揽，用來存放數(shù)據(jù)，當(dāng)這些頁用完后才是64個(gè)頁的連續(xù)頁申請则北。

4.2.4 頁

頁是InnoDB磁盤管理的最小單位矿微。InnoDB頁的大小為16KB。
常見頁類型有：

• 數(shù)據(jù)頁（B-tree Node）
• Undo頁 （Undo Log Page）
• 系統(tǒng)頁 （System Page）
• 事務(wù)數(shù)據(jù)頁（Transaction system Page）
• 插入緩沖位圖頁 （Insert Buffer Bitmap）
• 插入緩沖空閑列表頁 （Insert Buffer Free List）
• 未壓縮的二進(jìn)制大對象頁（Uncompressed BLOB Page）
• 壓縮的二進(jìn)制大對象頁（Compressed BLOB Page）

4.2.5 行

InnoDB是面向行的（row-oriented尚揣，相對比是面向列的數(shù)據(jù)庫）涌矢，也就是說數(shù)據(jù)的存放按行進(jìn)行存放。每個(gè)頁最多允許存放16KB / 2 ~ 200行的記錄快骗。

4.3 InnoDB物理存儲結(jié)構(gòu)

InnoDB表物理構(gòu)成

• 共享表空間（或者獨(dú)立的表空間文件）
• 日志文件組（確切說娜庇，redo文件組）
• 表結(jié)構(gòu)定義文件（.frm）

4.4 InnoDB行記錄格式

5.1后有Compact（默認(rèn)）和Redundant兩種格式

4.4.1Compact行記錄格式

設(shè)計(jì)目標(biāo)為高效存放數(shù)據(jù)塔次，行數(shù)據(jù)越多，性能越高名秀。

Compact行記錄格式

• 變長字段長度列表励负，按照列的順序逆序放置。列長度小于255字節(jié)匕得，用1字節(jié)表示继榆，大于255字節(jié)，則用2個(gè)字節(jié)表示
• NULL標(biāo)志位耗跛，一個(gè)字節(jié)裕照，表示對應(yīng)列為NULL
• 記錄頭信息，5個(gè)字節(jié)调塌，含義見下表
• 下面即為數(shù)據(jù)列，NULL不占用存儲空間
• 每行除了用戶定義的列惠猿，還有兩個(gè)隱藏列羔砾，事務(wù)ID列（6字節(jié)）和回滾指針列（7字節(jié)），若沒有定義主鍵偶妖，每行還會有一個(gè)6字節(jié)的RowID列

記錄頭信息1

記錄頭信息2

4.4.2 Redundant行記錄格式

Redundant行記錄格式

• 字段長度偏移列表姜凄，按照列的順序逆序放置。列長度小于255字節(jié)趾访，用1字節(jié)表示态秧，大于255字節(jié)，則用2個(gè)字節(jié)表示
• 記錄頭信息扼鞋，固定占用6個(gè)字節(jié)申鱼，含義見下表。n_fields云头、1byte_offs_flag兩個(gè)值值得注意

• 數(shù)據(jù)列捐友，varchar的NULL值不占用存儲空間，但是char值需要占用空間

  
  
  記錄頭信息

4.4.3 行溢出數(shù)據(jù)

將一條記錄中的某些數(shù)據(jù)存儲在真正的數(shù)據(jù)頁面之外溃槐，即為溢出數(shù)據(jù)匣砖。
不單單BLOB、LOB這類大對象列類型昏滴，varchar也可以存放為行溢出數(shù)據(jù)猴鲫。varchar理論可以放65535個(gè)字節(jié)，減去別的開銷谣殊，實(shí)際為65532個(gè)字節(jié)拂共。

行溢出

4.4.4 Compressed 與 Dynamic行記錄格式

InnoDB Plugin 引入了新的文件格式，之前的Compact和Redundant稱為Antelope蟹倾，新的稱為Barracuda匣缘。
新文件格式下分為兩種新的行記錄格式猖闪，Compressed 和 Dynamic兩種，采用完全的行溢出方式肌厨。

Barracuda行溢出

Compressed 行記錄格式的另一個(gè)功能就是存儲在其中的行數(shù)據(jù)會以zlib的算法進(jìn)行壓縮培慌。

4.4.5 char的行結(jié)構(gòu)存儲

通常理解，varchar存儲變長長度的字符類型柑爸，char存儲定長長度的字符類型吵护。然而在多字節(jié)字符集的情況下，char和varchar的行存儲基本是沒有區(qū)別的表鳍。

4.5 InnoDB數(shù)據(jù)頁結(jié)構(gòu)

頁是InnoDB管理數(shù)據(jù)庫的最小磁盤單位馅而，類型為B-tree node的頁，存放的就是表中行的實(shí)際數(shù)據(jù)了譬圣。
InnoDB頁分為以下七個(gè)部分：

• File Header
• Page Header
• Infimum + Supremum Records
• User Records
• Free Space
• Page Directory

• File Trailer

  
  
  InnoDB數(shù)據(jù)頁結(jié)構(gòu)

4.5.1 File Header

File Header

Page類型1

Page類型2

4.5.2 Page Header

Page Header

4.5.3 Infimum 和 Supremum 記錄

infimum指比任何主鍵值都要小的值
Supremum指比任何可能大的值都要大的值瓮恭，這兩個(gè)值在頁創(chuàng)建時(shí)建立，并且任何情況下不會刪除厘熟。

Infimum 和 Supremum

4.5.4 User Records 與 Free Space

User Records 即實(shí)際存儲行記錄的內(nèi)容屯蹦。再次強(qiáng)調(diào)，InnoDB存儲引擎表總是B+樹索引組織的绳姨。
Free Space值得就是空閑空間登澜，是鏈表數(shù)據(jù)結(jié)構(gòu)，當(dāng)記錄被刪除時(shí)飘庄，該空間就會被加入空閑鏈表中脑蠕。

4.5.5 Page Directory

Page Directory 中存放了記錄的相對位置，有些時(shí)候這些記錄指針被稱為槽slots跪削，或者目錄槽谴仙。InnoDB并不是每條記錄都有一個(gè)槽，一個(gè)槽可能指向4-8個(gè)記錄切揭，記錄都是按照鍵順序存放的狞甚。所以slots是稀疏目錄，二叉查找到的廓旬，只是個(gè)粗略的結(jié)果哼审，還需要recorder header中的next_record來繼續(xù)查找相關(guān)記錄。
需要牢記孕豹，B+樹索引本身并不能找到具體的一條記錄涩盾，B+樹索引找到的只是該記錄所在的頁。

4.5.6 File Trailer

File Trailer 是為了保證頁完整的寫入磁盤励背。
只有一個(gè)FIL_PAGE_END_LSN部分春霍，8個(gè)字節(jié)，前4代表該頁的checksum值叶眉，后4與File Header中的FIL_PAGE_LSN相同址儒，通過比較是否一致保證頁的完整性

4.6 Named File Formats

InnoDB通過Named File Formats機(jī)制來解決不同版本下頁結(jié)構(gòu)兼容性問題

InnoDB 文件格式

參數(shù)Innodb_file_format_check 用來檢測當(dāng)前InnoDB存儲引擎文件格式的支持度芹枷，該值默認(rèn)為ON

4.7 約束

4.7.1 數(shù)據(jù)完整性

關(guān)系型數(shù)據(jù)庫系統(tǒng)和文件系統(tǒng)的不同點(diǎn)是，關(guān)系型數(shù)據(jù)庫本身能夠保證存儲數(shù)據(jù)的完整性莲趣，不需要應(yīng)用程序的控制鸳慈，而文件系統(tǒng)一般需要在程序端進(jìn)行控制。幾乎所有的關(guān)系型數(shù)據(jù)庫都提供了約束（constraint）機(jī)制喧伞，約束提供了一條強(qiáng)大而簡易的途徑來保證數(shù)據(jù)庫中的數(shù)據(jù)完整性走芋，數(shù)據(jù)完整性有三種形式：

• 實(shí)體完整性，保證表中有一個(gè)主鍵
• 域完整性潘鲫，保證數(shù)據(jù)的值滿足特定的條件
• 參照完整性翁逞，保證兩張表之間的關(guān)系

對于InnoDB而言，提供了4種約束：

• Primary Key
• Unique Key
• Foreign Key
• Default
• NOT NULL

4.7.2 約束的創(chuàng)建和查找

可以創(chuàng)建表時(shí)候直接寫出來溉仑，也可以表建立后用sql語句創(chuàng)建

4.7.3 約束和索引的區(qū)別

• 約束更是一個(gè)邏輯的概念挖函，用來保證數(shù)據(jù)完整性。
• 索引是一個(gè)數(shù)據(jù)結(jié)構(gòu)浊竟，有邏輯上的概念挪圾，在數(shù)據(jù)庫中更是一個(gè)物理存儲的方式。

4.7.4 對于錯(cuò)誤數(shù)據(jù)的約束

mysql允許非法或錯(cuò)誤數(shù)據(jù)的插入逐沙，會默認(rèn)將其轉(zhuǎn)化為一個(gè)合法的值，然后報(bào)出warning洼畅，如需要讓其插入失敗吩案，報(bào)error，則需要設(shè)定sql_mode帝簇，用來嚴(yán)格審核輸入的參數(shù)徘郭。

4.7.5 ENUM和SET約束

set sql_mode = 'STRICT_TRANS_TABLES';

再配合枚舉或集合，可以實(shí)現(xiàn)check約束丧肴，但也只限于這種對于離散數(shù)據(jù)的約束残揉，對連續(xù)值的范圍約束和更復(fù)雜的約束還是無能為力。

觸發(fā)器與約束

• 觸發(fā)器的作用是在INSERT芋浮、DELETE抱环、UPDATE前后自動(dòng)調(diào)用SQL命令或者存儲過程

• 觸發(fā)器創(chuàng)建命令是CREATE TRIGGER，只有Super權(quán)限的MySQL用戶才可以執(zhí)行

  
  
  創(chuàng)建觸發(fā)器
  
  

  *最多可以為一個(gè)表建立6個(gè)觸發(fā)器纸巷，insert镇草、update、delete前后各一個(gè)瘤旨。

• 當(dāng)前只支持FOR EACH ROW 的觸發(fā)方式

應(yīng)用：
可以在修改值之前判斷是否符合業(yè)務(wù)邏輯梯啤，如取款業(yè)務(wù)取負(fù)數(shù)款項(xiàng)等。

4.7.7 外鍵

外鍵創(chuàng)建實(shí)例

外鍵刪除時(shí)的子表操作

• oracle中可以延遲檢查外鍵約束存哲，mysql都是即時(shí)檢查
• oracle中不要忘了給外鍵加索引因宇，防止死鎖七婴；InnoDB會自動(dòng)加索引

• 對于數(shù)據(jù)導(dǎo)入，可能因?yàn)橥怄I而花費(fèi)很多時(shí)間察滑，可以這樣避免：

  
  
  避免因外鍵而浪費(fèi)檢查時(shí)間

4.8 視圖

視圖（View）是一個(gè)命名的虛表打厘，它有一個(gè)查詢來定義，可以當(dāng)做表使用杭棵。與持久表（permanent table）不同的是婚惫，視圖中的數(shù)據(jù)沒有物理表現(xiàn)形式。

4.8.1 視圖的作用

主要用途之一是被用作一個(gè)抽象裝置魂爪，特別是對于一些應(yīng)用程序先舷，程序本身不需要關(guān)心基表的結(jié)構(gòu)，只需要按照視圖定義來獲取或者更新數(shù)據(jù)滓侍，因此蒋川，視圖同時(shí)在一定程度上起到一個(gè)安全層的作用。

alter view v_t as select * from t where id < 10 with check option;

with check option 表示更新視圖時(shí)對插入數(shù)據(jù)進(jìn)行檢查

4.8.2 物化視圖

用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作結(jié)果撩笆，這樣捺球，在執(zhí)行復(fù)雜查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作夕冲，從而快速得到結(jié)果氮兵。
MySQL本身不支持物化視圖，但是可以通過觸發(fā)器插入一個(gè)新的表實(shí)現(xiàn)歹鱼。

4.9 分區(qū)表

4.9.1 分區(qū)概述

MySQL支持水平分區(qū)泣栈，不支持垂直分區(qū)。此外弥姻，MySQL的分區(qū)是局部分區(qū)索引南片，一個(gè)分區(qū)中既有數(shù)據(jù)也有索引。
通過下面命令來查看是否啟用了分區(qū)

show variables like '%partition%';

當(dāng)前MySQL支持以下幾種類型的分區(qū)

• RANGE分區(qū)：行數(shù)據(jù)基于屬于一個(gè)給定連續(xù)區(qū)間的列值放入分區(qū)庭敦。
• LIST分區(qū)：和RANGE分區(qū)類似疼进，只是LIST分區(qū)面向的是離散的值。
• HASH分區(qū)：根據(jù)用戶自定義的表達(dá)式的返回值來進(jìn)行分區(qū)秧廉，返回值不能為負(fù)數(shù)伞广。
• KEY分區(qū)：根據(jù)MySQL數(shù)據(jù)庫提供的哈希函數(shù)來進(jìn)行分區(qū)。
  不論哪種類型的分區(qū)定血，當(dāng)表中存在主鍵或者唯一索引時(shí)赔癌，分區(qū)列必須是唯一索引的一個(gè)組成部分。

4.9.2 RANGE分區(qū)

RANGE分區(qū)示例1

如果此時(shí)插入30

RANGE分區(qū)示例2

RANGE分區(qū)主要用于日期列的分區(qū)澜沟，比如可以方便按年份灾票，日期查看數(shù)據(jù)

下面是兩則使用注意：

注意事項(xiàng)1

注意事項(xiàng)2 - 1

注意事項(xiàng)2 - 2

4.9.3 LIST分區(qū)

LIST分區(qū)示例

當(dāng)然，如果插入的數(shù)據(jù)不在定義中時(shí)茫虽，一樣會報(bào)錯(cuò)刊苍。
但是既们，在插入多組數(shù)據(jù)，其中有錯(cuò)誤數(shù)據(jù)時(shí)正什，MyISAM引擎會將出錯(cuò)數(shù)據(jù)之前的數(shù)據(jù)插入啥纸；
InnoDB引擎則會將整句插入語句無效，當(dāng)做事務(wù)來處理婴氮。

4.9.4 HASH分區(qū)

hash分區(qū)的目的是將數(shù)據(jù)均勻分布到各個(gè)分區(qū)斯棒。

HASH分區(qū)示例

數(shù)據(jù)庫還支持一種稱為LINEAR HASH的分區(qū)

• 優(yōu)點(diǎn)，增主经、刪荣暮、合并、拆分將變得更加快捷罩驻，有利于處理含有更大量數(shù)據(jù)的表
• 缺點(diǎn)穗酥，與使用HASH分區(qū)得到的數(shù)據(jù)分布相比，各個(gè)分區(qū)間數(shù)據(jù)的分布可能不大均衡

4.9.5 KEY分區(qū)

與HASH分區(qū)類似惠遏，只是這次不適用用戶提供的函數(shù)砾跃，而是用數(shù)據(jù)庫內(nèi)部的哈希函數(shù)。

KEY分區(qū)示例

4.9.6 COLUMNS分區(qū)

相當(dāng)于RANGE分區(qū)和LIST分區(qū)的進(jìn)化版本节吮，直接使用某一列分區(qū)抽高，而不需要將此列轉(zhuǎn)換為整形，如用YEAR()函數(shù)處理日期列透绩。

COLUMNS分區(qū)

4.9.7 子分區(qū)

在分區(qū)基礎(chǔ)上再分區(qū)厨内，也成為復(fù)合分區(qū)。
MySQL支持在RANGE或LIST分區(qū)上再進(jìn)行HASH或KEY分區(qū)

子分區(qū)示例

4.9.8 分區(qū)中的NULL值

• RANGE中使用將會將NULL放入最左邊分區(qū)
• LIST分區(qū)渺贤，必須顯示指出放入哪個(gè)分區(qū)
• HASH和KEY分區(qū)將會NULL返回0

4.9.9 分區(qū)和性能

分區(qū)并不適用OLTP應(yīng)用
請對本節(jié)進(jìn)行細(xì)致閱讀

歡迎大家關(guān)注我的公眾號

半畝房頂