4.1 索引組織表

? ??????在lnnoDB存儲引擎中儒溉， 表都是根據(jù)主鍵順序組織存放的尚镰， 這種存儲方式的表稱為索引組織表(index organized table)抖僵。 在InnoDB存儲引擎表中椎木， 每張表都有個主鍵 (Primary Key), 如果在創(chuàng)建表時沒有顯式地定義主鍵， 則lnnoDB存儲引擎會按如下方式選擇或創(chuàng)建主鍵：

1.首先判斷表中是否有非空的唯一索引(Unique NOT NULL), 如果有朝卒， 則該列即為主鍵证逻。

2.如果不符合上述條件，InnoDB存儲引擎自動創(chuàng)建一個6字節(jié)大小的指針抗斤。

? ??????當表中有多個非空唯一索引時囚企，InnoDB存儲引擎將選擇建表時第一個定義的非空唯 一索引為主鍵。 這里需要非常注意的是瑞眼， 主鍵的選擇根據(jù)的是定義索引的順序龙宏， 而不是建表時列的順序。

4.2 lnnoDB邏輯存儲結構

? ??????從lnnoDB存儲引擎的邏輯存儲結構看伤疙，所有數(shù)據(jù)都被邏輯地存放在一個空間中银酗，稱之為表空間(tablespace)辆影。表空間又由段(segment)、區(qū)(extent)黍特、頁(page)組成蛙讥。頁在一些文檔中有時也稱為塊(block),InnoDB存儲引擎的邏輯存儲結構大致如圖4-1所示。

4.2.1 表空間

? ??????表空間可以看做是InnoDB存儲引擎邏輯結構的最高層灭衷，所有的數(shù)據(jù)都存放在表空間中次慢。第3章中已經(jīng)介紹了在默認情況下InnoDB存儲引擎有一個共享表空間ibdata1,即所有數(shù)據(jù)都存放在這個表空間內(nèi)。如果用戶啟用了參數(shù)innodb_ file _per_ table, 則每張表內(nèi)的數(shù)據(jù)可以單獨放到一個表空間內(nèi)翔曲。

? ??????如果啟用了innodb_file_per_table 的參數(shù)经备，需要注意的是每張表的表空間內(nèi)存放的只是數(shù)據(jù)。索引和插入緩沖Bitmap 頁部默，其他類的數(shù)據(jù)，如回滾(undo) 信息造虎，插入緩沖索引頁傅蹂、系統(tǒng)事務信息，二次寫緩沖(Double write buffer) 等還是存放在原來的共享表空間內(nèi)算凿。這同時也說明了另一個問題：即使在啟用了參數(shù)innodb_ file per_ table 之后份蝴，共享表空間還是會不斷地增加其大小。

4.2.2段

? ??????圖4-1中顯示了表空間是由各個段組成的氓轰，常見的段有數(shù)據(jù)段婚夫、索引段、回滾段等署鸡。 因為前面已經(jīng)介紹過了InnoDB存儲引擎表是索引組織的(indexorganized), 因此數(shù)據(jù)即索引案糙，索引即數(shù)據(jù)。那么數(shù)據(jù)段即為B+樹的葉子節(jié)點（圖4-1的Leafnode segment), 索引段即為B+樹的非索引節(jié)點（圖4-1的Non-leafnode segment)靴庆∈卑疲回滾段較為特殊，將會在后面的章節(jié)進行單獨的介紹炉抒。

????????在lnnoDB存儲引擎中奢讨，對段的管理都是由引擎自身所完成，DBA不能也沒有必要對其進行控制焰薄。這和Oracle數(shù)據(jù)庫中的自動段空間管理(ASSM)類似拿诸，從一定程度上簡化了DBA對于段的管理。

4.2.3區(qū)

? ??????區(qū)是由連續(xù)頁組成的空間塞茅，在任何情況下每個區(qū)的大小都為1MB亩码。為了保證區(qū)中頁的連續(xù)性，InnoDB存儲引擎一次從磁盤申請4~5個區(qū)凡桥。在默認情況下蟀伸，InnoDB存儲引擎頁的大小為16KB,即一個區(qū)中一共有64個連續(xù)的頁。

????????lnnoDB 1.0.x版本開始引入壓縮頁，即每個頁的大小可以通過參數(shù)KEY_BLOCK_SIZE 設置為 2K啊掏、 4K蠢络、 8K, 因此每個區(qū)對應頁的數(shù)批就應該為 512、 256迟蜜、 128刹孔。

? ??????InnoDB 1.2.x版本新增了參數(shù) innodb_page_ size, 通過該參數(shù)可以將默認頁的大小設置為 4K、 8K, 但是頁中的數(shù)據(jù)庫不是壓縮娜睛。這時區(qū)中頁的數(shù)扭同樣也為 256髓霞、 128∑杞洌總之方库，不論頁的大小怎么變化，區(qū)的大小總是為IM障斋。

????????但是纵潦，這里還有這樣一個問題：在用戶啟用了參數(shù) innodb_ file _per_ talbe 后，創(chuàng)建的表默認大小是 96KB垃环。區(qū)中是 64 個連續(xù)的頁邀层，創(chuàng)建的表的大小至少是 1MB 才對啊遂庄？其實這是因為在每個段開始時寥院，先用 32 個頁大小的碎片頁 (fragment page) 來存放數(shù)據(jù)，在使用完這些頁之后才是 64 個連續(xù)頁的申請涛目。這樣做的目的是秸谢，對于一些小表，或者 是 undo 這類的段霹肝，可以在開始時申請較少的空間钮追，節(jié)省磁盤的開容量的開銷。

4.2.4 頁

? ??????同大多數(shù)數(shù)據(jù)庫一樣阿迈，InnoDB有頁(Page)的概念（也可以稱為塊）元媚， 頁是lnnoDB磁盤管理的最小單位。在InnoDB存儲引擎中苗沧， 默認每個頁的大小為16KB刊棕。而從InnoDB 1.2.x版本開始， 可以通過參數(shù)innodb_page_ size將頁的大小設置為4K待逞、8K甥角、16K。若設置完成识樱， 則所有表中頁的大小都為innodb_page_ size, 不可以對其再次進行修改嗤无。除非通過mysqldump導入和導出操作來產(chǎn)生新的庫震束。

????????在InnoDB存儲引擎中， 常見的頁類型有：

數(shù)據(jù)頁(B-tree Node)

undo頁(undo Log Page)

系統(tǒng)頁(System Page)

事務數(shù)據(jù)頁(Transaction system Page)

插人緩沖位圖頁(Insert Buffer Bitmap)

插入緩沖空閑列表頁(Insert Buffer Free List)

未壓縮的二進制大對象頁（Uncompressed BLOG Page）

壓縮的二進制大對象頁（compressed BLOB Page）

4.2.5 行

? ??????InnoDB存儲引擎是面向列的(row-oriented), 也就說數(shù)據(jù)是按行進行存放的当犯。每個頁存放的行記錄也是有硬性定義的垢村， 最多允許存放16KB / 2~200行的記錄， 即7992行記錄嚎卫。這里提到了row-oriented的數(shù)據(jù)庫嘉栓， 也就是說， 存在有column-oriented的數(shù)據(jù)庫拓诸。MySQL infobright存儲引擎就是按列來存放數(shù)據(jù)的侵佃， 這對于數(shù)據(jù)倉庫下的分析類SQL語句的執(zhí)行及數(shù)據(jù)壓縮非常有幫助。類似的數(shù)據(jù)庫還有SybaseIQ奠支、Google Big Table馋辈。面向列的數(shù)據(jù)庫是當前數(shù)據(jù)庫發(fā)展的一個方向， 但這超出了本書涵蓋的內(nèi)容倍谜， 有興趣的讀者可以在網(wǎng)上尋找相關資料首有。

4.3?lnnoDB 行記錄格式

? ??????InnoDB存儲引擎和大多數(shù)數(shù)據(jù)庫一樣（如Oracle和 Microsoft SQL Server數(shù)據(jù)庫），記錄是以行的形式存儲的枢劝。 這意味著頁中保存著表中一行行的數(shù)據(jù)。 在InnoDB 1.0.x版本之前卜壕，InnoDB存儲引擎提供了Compact和Redundant兩種格式來存放行記錄數(shù)據(jù)您旁， 這也是目前使用最多的一種格式。 Redundant格式是為兼容之前版本而保留的轴捎，如果閱讀過InnoDB的源代碼鹤盒，用戶會發(fā)現(xiàn)源代碼中是用PHYSICAL RECORD (NEW STYLE) 和PHYSICAL RECORD (OLD STYLE)來區(qū)分兩種格式的。 在MySQL5.1版本中侦副， 默認設置為Compact行格式侦锯。 用戶可以通過命令SHOW TABLE STATUS LIKE'table_ name'來查看當前表使用的行格式，其中 row_format屬性表示當前所使用的行記錄結構類型秦驯。

4.3.1 Compact 行記錄格式

????????Compact行記錄是在MySQL5.0中引入的尺碰， 其設計目標是高效地存儲數(shù)據(jù)。 簡單來說译隘， 一個頁中存放的行數(shù)據(jù)越多亲桥， 其性能就越高。 圖4-2顯示了Compact行記錄的存儲方式：

? ??????從圖4-2可以觀察到固耘， Compact行記錄格式的首部是一個非NULL變長字段長度列表题篷， 并且其是按照列的順序逆序放置的， 其長度為：

1.若列的長度小于255字節(jié)厅目， 用1 字節(jié)表示番枚；

2.若大于255個字節(jié)法严， 用2字節(jié)表示。

? ??????變長字段的長度最大不可以超過2字節(jié)葫笼， 這是因在MySQL數(shù)據(jù)庫中VARCHAR_類型的最大長度限制為65535深啤。變長字段之后的第二個部分是NULL標志位， 該位指示了該行數(shù)據(jù)中是否有NULL值渔欢， 有則用1 表示墓塌。該部分所占的字節(jié)應該為1字節(jié)。接下來的部分是記錄頭信息(record head er), 固定占用5字節(jié)(40位）奥额， 每位的含義見表4-1苫幢。

????????最后的部分就是實際存儲每個列的數(shù)據(jù)。 需要特別注意的是垫挨， NULL不占該部分任何空間韩肝， 即NULL除了占有NULL標志位， 實際存儲不占有任何空間九榔。 另外有一點需要注意的是哀峻， 每行數(shù)據(jù)除了用戶定義的列外， 還有兩個隱藏列哲泊， 事務 ID 列和回滾指針列剩蟀， 分別為 6 字節(jié)和 7 字節(jié)的大小。 若 InnoDB 表沒有定義主鍵切威， 每行還會增加一個 6 字節(jié)的rowid列育特。

4.3.2 Redundant行記錄格式

? ??????Redundant是MySQL5.0 版本之 前InnoDB的 行記錄 存儲 方式MySQL5.0支持Redundant是為了兼容之前版本的頁格式。Redundant行記錄采用如圖4-3所示的方式存儲 先朦。

? ??????從圖 4-3 可以看到缰冤， 不同于 Compact 行記錄格式， Redundant 行記錄格式的首部是一個字段長度偏移列表喳魏， 同樣是按照列的順序逆序放置的棉浸。若列的長度小于 255 字節(jié)， 用 1 字節(jié)表示 刺彩； 若大于 255 字節(jié)迷郑， 用 2 字節(jié)表示。 第二個部分為記錄頭信息 (record header), 不同于 Compact 行記錄格式创倔， Redundant 行記錄格式的記錄頭占用 6字節(jié) (48位）三热， 每位的含義見表 4-2。從表 4-2 中可以發(fā)現(xiàn)三幻， n_fields 值代表一行中列的數(shù)扯就漾， 占用 10 位。 同時這也很好地解釋了為什么 MySQL 數(shù)據(jù)庫一行支持最多的列為 1023念搬。另一個需要注意的值為 Ibyte_ offs_ flags, 該值定義了偏移列表占用 1 字節(jié)還是 2 字節(jié)抑堡。 而最后的部分就是實際存儲的每個列的數(shù)據(jù)了摆出。

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

? ??????InnoDB存儲引擎可以將一條記錄中的某些數(shù)據(jù)存儲在真正的數(shù)據(jù)頁面之外。一般認為BLOB首妖、LOB 這類的大對象列類型的存儲會把數(shù)據(jù)存放在數(shù)據(jù)頁面之外偎漫。但是，這個理解有點偏差有缆，BLOB可以不將數(shù)據(jù)放在溢出頁面象踊，而且即便是VARCHAR列數(shù)據(jù)類型，依然有可能被存放為行溢出數(shù)據(jù) 棚壁。

? ??????首先對VARCHAR數(shù)據(jù)類型進行研究杯矩。很多DBA喜歡MySQL數(shù)據(jù)庫提供的 VARCHAR類型，因為相對于 OracleVARCHAR2最大存放 4000字節(jié)袖外，SQL Se rver最大存放8000字節(jié)史隆，MySQL數(shù)據(jù)庫的VARCHAR類型可以存放65535字節(jié)。但是曼验，lnnoDB存儲引擎并不支持65535長度的VARCHAR泌射。這是因 為還有別的開銷，

? ??????此外需要注意的是鬓照，MySQL 官方手冊中定義的 65535 長度是指所有VARCHAR列的長度總和熔酷，如果列的長度總和超出這個長度，依然無法創(chuàng)建豺裆。

4.3.4 Compressed和Dynamic行記錄格式

? ??????InnoDB 1.0.x版本開始引入了新的文件格式(fileformat, 用戶可以理解為新的頁格式）拒秘， 以前支持的Compact和Redundant格式稱為Antelope文件格式，新的文件格式稱為Barracuda文件格式留储。Barracuda文件格式下?lián)碛袃煞N新的行記錄格式：Compressed和Dynamic。新的兩種記錄格式對于存放在BLOB中的數(shù)據(jù)采用了完全的行溢出的方式咙轩，如圖4-5所示获讳，在數(shù)據(jù)頁中只存放20個字節(jié)的指針，實際的數(shù)據(jù)都存放在OffPage中活喊， 而之前的Compact和Redundant兩種格式會存放768個前綴字節(jié)丐膝。

????????Compressed行記錄格式的另一個功能就是，存儲在其中的行數(shù)據(jù)會以zlib的算法進 行壓縮钾菊，因此對于BLOB帅矗、TEXT、VARCHAR這類大長度類型的數(shù)據(jù)能夠進行非常有效的存儲煞烫。

4.3.5 CHAR的行結構存儲

????????通常理解VARCHAR是存儲變長長度的字符類型浑此，CHAR是存儲固定長度的字符類型。而在前面的小節(jié)中滞详，用戶已經(jīng)了解行結構的內(nèi)部的存儲凛俱，并可以發(fā)現(xiàn)每行的變長字段長度的列表都沒有存儲CHAR類型的長度紊馏。

? ??????然而,值得注意的是之前給出的兩個例子中的字符集都是單字節(jié)的latin1格式。從MySQL4.1版本開始蒲犬，CHR(N)中的N指的是字符的長度朱监，而不是之前版本的字節(jié)長度。也就說在不同的字符集下原叮，CHAR類型列內(nèi)部存儲的可能不是定長的數(shù)據(jù)赫编。

4.4?lnnoDB數(shù)據(jù)頁結構

????????相信通過前面幾個小節(jié)的介紹，讀者已經(jīng)知道頁是InnoDB存儲引擎管理數(shù)據(jù)庫的 最小磁盤單位奋隶。頁類型為B-treeNode的頁存放的即是表中行的實際數(shù)據(jù)了擂送。在這一節(jié)中，我們將從底層具體地介紹InnoDB數(shù)據(jù)頁的內(nèi)部存儲結構达布。

InnoDB數(shù)據(jù)頁由以下7個部分組成团甲，如圖4-6所示。

?File Header (文件頭）

?Page Header (頁頭）

?Infimun和Supremum Records

?User Records (用戶記錄黍聂，即行記錄）

?Free Space (空閑空間）

?Page Directory (頁目錄）

?File Trailer (文件結尾信息）

? ??????其中FileHeader躺苦、PageHeader、FileTrailer的大小是固定的产还，分別為38匹厘、56、8字節(jié)脐区，這些空間用來標記該頁的些信息愈诚，如Checksum,數(shù)據(jù)頁所在B+樹索引的層數(shù)等。UserRecords牛隅、FreeSpace炕柔、PageDirectory這些部分為實際的行記錄存儲空間，因此大小是動態(tài)的媒佣。在接下來的各小節(jié)中將具體分析各組成部分匕累。

4.4.1?File Header

? ??????File Header 用來記錄頁的一些頭信息， 由表 4-3 中 8 個部分組成默伍， 共占用 38 字節(jié)欢嘿。

4.4.2 Page Header

? ??????接著File Header部分的是Page Header, 該部分用來記錄數(shù)據(jù)頁的狀態(tài)信息， 由14個部分組成也糊， 共占用56字節(jié)炼蹦， 如表4-5所示。

4.4.3 lnfimum和Supremum Record

????????在InnoDB存儲引擎中狸剃，每個數(shù)據(jù)頁中有兩個虛擬的行記錄掐隐，用來限定記錄的邊界。Infimum記錄是比該頁中任何主鍵值都要小的值钞馁，Supremum指比任何可能大的值還要大的值瑟枫。這兩個值在頁創(chuàng)建時被建立斗搞，并且在任何情況下不會被刪除。在Compact 行格式和Redundant行格式下慷妙，兩者占用的字節(jié)數(shù)各不相同僻焚。圖4-7顯示了Infimum和 Supremum記錄。

4.4.4 User Record和Free Space

? ??????User Record就是之前討論過的部分膝擂， 即實際存儲行記錄的內(nèi)容虑啤。再次強調，InnoDB存儲引擎表總是B+樹索引組織的架馋。

????????Free Space很明顯指的就是空閑空間狞山， 同樣也是個鏈表數(shù)據(jù)結構。在一條記錄被刪除后叉寂， 該空間會被加人到空閑鏈表中萍启。

4.4.5 Page Directory

????????Page Directory (頁目錄）中存放 了記錄的相對位置 （注意， 這里存放的是 頁相對位置屏鳍，而不是偏移量）勘纯， 有些 時候這些記錄 指針稱為Slots(槽） 或目錄槽(Directory Slots)。 與其他數(shù)據(jù)庫系統(tǒng)不同的是钓瞭，在lnnoDB中并不是每個記錄擁有一個槽驳遵，InnoDB存儲引擎的槽 是 一個稀疏目錄(sparse directory), 即一個槽中可能包含多個記錄。 偽記錄 lnfimum的n_owned值總是為l, 記錄 Supremum的n_owned的取值范圍為[l, 8], 其他用戶記錄 n_owned的取值范圍為[4, 8]山涡。 當記錄被插入或刪除時需要對槽進行分裂或平衡的維護操作堤结。

????????在Slots中 記錄按照索引鍵值順序存放， 這樣可以利用二叉查找迅速找到記錄的指針鸭丛。

? ??????由于在InnoDB存儲引擎中 Page Direcotry 是稀疏目錄竞穷， 二叉查找的結果只是 一個粗略的結果，因此 InnoDB存儲引擎必須通過recorder header中的next_record來繼續(xù)查找相關記錄鳞溉。 同時瘾带，Page Directory很好地解釋了recorder header中的n_owned值的含義，因為這些記錄并不包括在Page Directory中穿挨。

????????需要牢記的是月弛，B+樹索引本身并不能找到具體的一條記錄肴盏， 能找到只是該記錄所在的頁科盛。 數(shù)據(jù)庫把頁載入到 內(nèi)存，然后通過Page Directory再進行二叉查找 菜皂。 只不過二 叉查找的時間復雜度很低贞绵， 同時在內(nèi)存中的查找很快， 因此通常忽略 這部分 查找所用的時間恍飘。

4.4.6 File Trailer

????????為了檢測頁 是否已經(jīng)完整地寫入磁盤（如可能發(fā)生的寫人過程中磁盤損壞榨崩、 機器關機等）谴垫，InnoDB存儲引擎的頁中設置 了File Trailer部分。

????????File Trailer 只有一個 FIL_PAGE_END_LSN部分母蛛， 占用8字節(jié)翩剪。 前4字節(jié)代表該 頁的checksum值，最后4字節(jié)和File Header中的FIL_PAGE_ LSN相 同彩郊。 將這兩個值與 File Header中的FIL_PAGE_SPACE_OR_ CHKSUM和FIL_PAGE_LSN值進行比較前弯，看是否一致(checksum的比較需要通過InnoDB的checksum函數(shù)來進行比較， 不是簡單的等值 比較）秫逝， 以此來保證頁的完整性(not corrupted)恕出。

????????在默認配置下，InnoDB存儲引擎每次從磁盤讀取一個頁就會檢測該頁的完整性违帆，即頁是否發(fā)生Corrupt, 這就是通過File Trailer 部分進行檢測浙巫，而該部分的檢測會有一定的開銷。 用戶可以通過參數(shù)innodb_c heckus ms 來開啟或關閉對這個頁完整性的檢查刷后。

????????MySQL 5.6.6 版本開始新增了參數(shù)innodb_checksum_ algorithm, 該參數(shù)用來控制檢測checksum函數(shù)的算法的畴， 默認值為crc32, 可設置的值有： innodb 、crc32 惠险、none 苗傅、strict_innodb、strict_crc32班巩、strict_none渣慕。

????????innodb為兼容之前版本InnoDB頁的checksum檢測方式，crc32為MySQL5.6.6 版本引進的新的checksum算法抱慌， 該算法較之前的innodb有著較高的性能脚乡。但是若表中所有頁的checksum 值都以strict算法保存， 那么低版本的MySQL數(shù)據(jù)庫將不能讀取這些頁钮孵。none 表示不對頁啟用checksum檢查南片。

????????strict_* 正如其名， 表示嚴格地按照設置的checksum 算法進行頁的檢測寺渗。因此若低版本MySQL數(shù)據(jù)庫升級到MySQL 5.6.6 或之后的版本匿情， 啟用strict_crc32 將導致不能讀取表中的頁。啟用strict_crc32 方式是最快的方式信殊， 因為其不再對innodb和crc32算法進行兩次檢測炬称。故推薦使用該設置。若數(shù)據(jù)庫從低版本升級而來涡拘， 則需要進行mysql_upgrade操作玲躯。

4.5?Named File Formats 機制

????????隨著 InnoDB 存儲引擎的發(fā)展，新的頁數(shù)據(jù)結構有時用來支持新的功能特性。比如前面提到的 InnoDB 1.0.x 版本提供了新的頁數(shù)據(jù)結構來支持表壓縮功能跷车，完全的溢出(Off page) 大變長字符類型字段的存儲棘利。這些新的頁數(shù)據(jù)結構和之前版本的頁并不兼容，因此從 lnnoDB 1.0.x 版本開始朽缴，InnoDB 存儲引通過 Named File Formats 機制來解決不同版本下頁結構兼容性的問題善玫。

????????InnoDB 存儲引擎將 1.0.x 版本之前的文件格式 (file format) 定義為 Antelope, 將這個版本支持的文件格式定義為 Barracuda。新的文件格式總是包含于之前的版本的頁格 式密强。圖 4-8 顯示了 Barracuda 文件格式和 Antelope 文件格式之間的關系蝌焚，Antelope 文件 格式有 Compact 和 Redudant 的行格式，Barracuda 文件格式既包括了 Antelope 所有的文件格式誓斥，另外新加入了之前已經(jīng)提到過的 Compressed 和 Dynamic 行格式只洒。

4.6 約束

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

? ??????關系型數(shù)據(jù)庫系統(tǒng)和文件系統(tǒng)的一個不同點是，關系數(shù)據(jù)庫本身能保證存儲數(shù)據(jù)的完整性劳坑，不需要應用程序的控制毕谴，而文件系統(tǒng)一般需要在程序端進行控制。當前幾乎所有的關系型數(shù)據(jù)庫都提供了約束(constraint)機制距芬，該機制提供了一條強大而簡易的途徑來保證數(shù)據(jù)庫中數(shù)據(jù)的完整性涝开。一般來說，數(shù)據(jù)完整性有以下三種形式：

????????實體完整性保證表中有一個主鍵框仔。在InnoDB存儲引擎表中舀武，用戶可以通過定義Primary Key或Unique Key約束來保證實體的完整性。用戶還可以通過編寫一個觸發(fā)器來保證數(shù)據(jù)完整性离斩。

????????域完整性保證數(shù)據(jù)每列的值滿足特定的條件银舱。在InnoDB存儲引擎表中，域完整性可以通過以下幾種途徑來保證：

1.選擇合適的數(shù)據(jù)類型確保一個數(shù)據(jù)值滿足特定條件跛梗。? ??????

2.外鍵(Foreign Key)約束寻馏。

3.編寫觸發(fā)器。

4.還可以考慮用DEFAULT約束作為強制域完整性的一個方面核偿。

????????參照完整性保證兩張表之間的關系诚欠。InnoDB存儲引擎支持外鍵，因此允許用戶定義外鍵以強制參照完整性漾岳，也可以通過編寫觸發(fā)器以強制執(zhí)行轰绵。

????????對于InnoDB存儲引擎本身而言，提供了以下幾種約束：

Primary Key

Unique Key

Foreign Key

Default

NOTNULL

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

? ??????約束的創(chuàng)建可以采用以下兩種方式：

1.表建立時就進行約束定義

2.利用ALTER TABLE 命令來進行創(chuàng)建約束

????????對Unique Key (唯一索引）的約束尼荆， 用戶還可以通過命令CREATE UNIQUE INDEX來建立左腔。 對于主鍵約束而言， 其默認約束名為PRIMARY耀找。而對于Unique Key約束而言翔悠，默認約束名和列名一樣， 當然也可以人為指定Unique Key約束的名字野芒。Foreign Key約束 似乎會有一個比較神秘的默認名稱蓄愁。

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

????????的確， 當用戶創(chuàng)建了一個唯一索引就創(chuàng)建了一個唯一的約束狞悲。但是約束和索引的概念還是有所不同的撮抓， 約束更是一個邏輯的概念， 用來保證數(shù)據(jù)的完整性摇锋， 而索引是一個數(shù)據(jù)結構丹拯， 既有邏輯上的概念， 在數(shù)據(jù)庫中還代表著物理存儲的方式荸恕。

4.6.4 對錯誤數(shù)據(jù)的約束

????????在某些默認設置下乖酬， MySQL數(shù)據(jù)庫允許非法的或不正確的數(shù)據(jù)的插入或更新， 又或者可以在數(shù)據(jù)庫內(nèi)部將其轉化為一個合法的值融求， 如向NOT NULL的字段插入一個NULL 值MySQL數(shù)據(jù)庫會將其更改為0再進行插入咬像， 因此數(shù)據(jù)庫本身沒有對數(shù)據(jù)的正確性進行約束。

4.6.5 ENUM和SET約束

? ??????MySQL數(shù)據(jù)庫不支持傳統(tǒng)的CHECK約束生宛， 但是通過ENUM和SET類型可以解決部分這樣的約束需求县昂。 例如表上有一個性別類型， 規(guī)定域的范圍只能是male 或female,在這種情況下用戶可以通過ENUM類型來進行約束陷舅。

4.6.6 觸發(fā)器與約束

? ??????通過前面小節(jié)的介紹倒彰， 用戶已經(jīng)知道完整性約束通常也可以使用觸發(fā)器來實現(xiàn)， 因此在了解數(shù)據(jù)完整性前先對觸發(fā)器來做一個了解莱睁。

? ??????觸發(fā)器的作用是在執(zhí)行INSERT待讳、DELETE和UPDATE命令之前或之后自動調用SQL命令或存儲過程。MySQL5.0對觸發(fā)器的實現(xiàn)還不是非常完善仰剿，限制比較多耙箍，而從MySQL5.1開始觸發(fā)器已經(jīng)相對穩(wěn)定， 功能也較之前有了大幅的提高酥馍。

? ??????最多可以為一個表建立6個觸發(fā)器辩昆， 即分別為INSERT、UPDATE旨袒、DELETE 的BEFORE和AFTER各定義一個汁针。BEFORE和AFTER代表觸發(fā)器發(fā)生的時間， 表示是在每行操作的之前發(fā)生還是之后發(fā)生砚尽。 當前MySQL數(shù)據(jù)庫只支持FOR EACH ROW的觸發(fā)方式施无， 即按每行記錄進行觸發(fā)， 不支持像DB2的FOR EACH STATEMENT 的觸發(fā)方式必孤。

????????通過觸發(fā)器猾骡， 用戶可以實現(xiàn)MySQL數(shù)據(jù)庫本身并不支持的一些特性瑞躺， 如對于傳統(tǒng)CHECK約束的支持， 物化視圖兴想、 高級復制幢哨、 審計等特性。 這里先關注觸發(fā)器對于約束的支持嫂便。

?4.6.7 外鍵約束

????????外鍵用來保證參照完整性捞镰， MySQL數(shù)據(jù)庫的MyISAM存儲引擎本身并不支持外鍵，對于外鍵的定義只是起到 個注釋的作用毙替。 而InnoDB存儲引擎則完整支持外鍵約束岸售。

? ? ? ??一般來說， 稱被引用的表為父表厂画， 引用的表稱為子表凸丸。 外鍵定義時的 ON DELETE 和ON UPDATE表示在對父表進行DELETE和UPDATE操作時， 對子表所做的操作袱院， 可定義的子表操作有：

CASCADE

SET NULL

NO ACTION

RESTRICT

? ??????CASCADE表示當父表發(fā)生DELETE或UPDATE操作時甲雅， 對相應的子表中的數(shù)據(jù)也進行DELETE或UPDATE操作 。SET NULL表示當父表發(fā)生DELETE或UPDATE操作時坑填， 相應的子表中的數(shù)據(jù)被更新為 NULL值抛人， 但是子表中相 對應的列必須允許為NULL值。NO ACTION表示當父表發(fā)生DELETE或UPDATE操作時脐瑰， 拋出錯誤妖枚， 不允許這類操作發(fā)生。RESTRICT表示當父表發(fā)生DELETE或UPDATE操作時苍在， 拋出 錯誤绝页， 不允許這類操作發(fā)生。 如果定義外鍵時沒有指定ON DELETE或ON UPDATE, RESTRICT就是默認的外鍵設置寂恬。

????????在其他數(shù)據(jù)庫中续誉， 如Oracle數(shù)據(jù)庫， 有一種稱為延時檢查(deferred check)的外鍵約束初肉， 即檢查在SQL語句運行完成后再進行酷鸦。而目前MySQL數(shù)據(jù)庫的外鍵約束都是即時檢查(immediate check), 因此從上面的定義可以看出， 在MySQL數(shù)據(jù)庫中 NO ACTION和RESTRICT的功能是相同的 牙咏。

? ??????InnoDB存儲引擎在外鍵建立時會自動地對該列加一個索引臼隔， 這和Microsoft SQL Server 數(shù)據(jù)庫的做法 樣。因此可以很好地避免外鍵列上無索引而導致的死鎖問題的產(chǎn)生妄壶。

4.7 視圖

????????在MySQL數(shù)據(jù)庫中摔握，視圖 (View) 是一個命名的虛表，它由一個SQL查詢來定義丁寄，可以當做表使用氨淌。與持久表 (permanent table) 不同的是泊愧，視圖中的數(shù)據(jù)沒有實際的物理存儲。

4.7.1 視圖的作用

????????視圖在數(shù)據(jù)庫中發(fā)揮著重要的作用盛正。視圖的主要用途之一是被用做一個抽象裝置删咱，特別是對于一些應用程序，程序本身不需要關心基表(base table) 的結構蛮艰，只需要按照視圖定義來取數(shù)據(jù)或更新數(shù)據(jù)，因此雀彼，視圖同時在一定程度上起到一個安全層的作用壤蚜。

? ??????雖然視圖是基于基表的一個虛擬表， 但是用戶可以對某些視圖進行更新操作徊哑， 其本質就是通過視圖的定義來更新基本表袜刷。 一般稱可以進行更新操作的視圖為可更新視圖 (updatable view)。 視圖定義中的 WITH CHECK OPTION 就是針對于可更新的視圖的莺丑， 即更新的值是否需要檢查著蟹。

4.7.2 物化視圖

? ??????MySQL數(shù)據(jù)庫本身并不支持物化視圖， 換句話說梢莽， MySQL數(shù)據(jù)庫中的視圖總是虛擬的萧豆。 但是用戶可以通過一些機制來實現(xiàn)物化視圖的功能。 例如要創(chuàng)建一個ON DEMAND的物化視圖還是比較簡單的昏名， 用戶只需定時把數(shù)據(jù)導人到另一張表涮雷。

4.8 分區(qū)表

4.8.1 分區(qū)概述

? ??????分區(qū)功能并不是在存儲引擎層完成的， 因此 不是只有l(wèi)nnoDB存儲引擎支持分區(qū)轻局，常見的存儲引擎 MylSAM洪鸭、NDB等都支持。 但也并不是所有的存儲引擎都支持仑扑，如csv览爵、FEDORATED、 MERGE等就不支持镇饮。 在使用分區(qū)功能前蜓竹， 應該對選擇的存儲引 擎對分區(qū)的支持有所了解。

? ??????MySQL數(shù)據(jù)庫在5.1版本時添加了對分區(qū)的支持储藐。分區(qū)的過程是將一個表或索引分解為多個更小梅肤、更可管理的部分。就訪間數(shù)據(jù)庫的應用而言邑茄， 從邏輯上講姨蝴，只有一個表或一個索引， 但是在物理上這個表或索引可能由數(shù)十個物理分區(qū)組成肺缕。每個分區(qū)都是獨立的對象左医， 可以獨自處理授帕，也可以作為—個更大對象的一部分進行處理。

????????MySQL數(shù)據(jù)庫支持的分區(qū)類型為水平分區(qū)并不支持垂直分區(qū)此外浮梢，MySQL數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引跛十， 一個分區(qū)中既存放了數(shù)據(jù)又存放了索引。水平分區(qū)秕硝， 指將同一表中不同行的記錄分配到不同的物理文件中芥映。眶直分區(qū)远豺， 指將同一表中不同列的記錄分配到不同的物理文件中奈偏。而全局分區(qū)是指， 數(shù)據(jù)存放在各個分區(qū)中躯护，但是所有數(shù)據(jù)的索引放在一個對象中惊来。目前， MySQL數(shù)據(jù)庫還不支持全局分區(qū)棺滞。

????????大多數(shù)DBA 會有這樣一個誤區(qū) ： 只要啟用了分區(qū)裁蚁， 數(shù)據(jù)庫就會運行得更快。 這個結論是存在很多問題的继准。 就我的經(jīng)驗看來枉证， 分區(qū)可能會給某些SQL語句性能帶來提高， 但是分區(qū)主要用于數(shù)據(jù)庫高可用性的管理移必。 在OLTP 應用中刽严， 對于分區(qū)的使用應該非常小心。 總之避凝， 如果只是一味地使用分區(qū)舞萄， 而不理解分區(qū)是如何工作的， 也不清楚你的應 用如何使用分區(qū)管削， 那么分區(qū)極有可能會對性能產(chǎn)生負面的影響倒脓。

當前MySQL數(shù)據(jù)庫支持以下幾種類型的分區(qū)。

ORANGE分區(qū)：行數(shù)據(jù)基于屬于 個給定連續(xù)區(qū)間的列值被放入分區(qū)含思。 MySQL5.5開始支待RANGECOLUMNS的分區(qū)崎弃。

LIST分區(qū)：和RANGE分區(qū)類型，只是LIST分區(qū)面向的是離散的值含潘。 MySQL5.5開始支持LISTCOLUMNS的分區(qū)饲做。

HASH分區(qū)：根據(jù)用戶自定義的表達式的返回值來進行分區(qū)， 返回值不能為負數(shù)遏弱。 OKEY分區(qū)：根據(jù)MySQL數(shù)據(jù)庫提供的哈希函數(shù)來進行分區(qū)盆均。

????????不論創(chuàng)建何種類型的分區(qū)， 如果表中存在主鍵或唯一索引時漱逸， 分區(qū)列必須是唯一索引的一個組成部分泪姨。

4.8.2 分區(qū)類型

? ??????RANGE分區(qū)主要用于日期列的分區(qū)游沿。LIST分區(qū)和RANGE分區(qū)非常相似， 只是分區(qū)列的值是離散的肮砾， 而非連續(xù)的诀黍。不同于RANGE分區(qū)中定義的VALUES LESS THAN語句，LIST分區(qū)使用VALUES IN仗处。 因為每個分區(qū)的值是離散的眯勾，因此只能定義值。

? ??????HASH分區(qū)的目的是將數(shù)據(jù)均勻地分布到預先定義的各個分區(qū)中婆誓， 保證各分區(qū)的數(shù)據(jù)數(shù)顯大致都是一樣的吃环。 在RANGE和LIST分區(qū)中， 必須明確指定一個給定的列值或列值集合應該保存在哪個分區(qū)中旷档；而在HASH分區(qū)中模叙， MySQL自動完成這些工作歇拆， 用 戶所要做的只是基于將要進行哈希分區(qū)的列值指定一個列值或表達式鞋屈， 以及指定被分區(qū) 的表將要被分割成的分區(qū)數(shù)獄。

????????MySQL數(shù)據(jù)庫還支持一種稱為LINEARHASH的分區(qū)故觅， 它使用一個更加復雜的算法來確定新行插人到已經(jīng)分區(qū)的表中的位置厂庇。 它的語法和HASH分區(qū)的語法相似， 只是 將關鍵字HASH改為LINEARHASH输吏。LINEAR HASH分區(qū)的優(yōu)點在于权旷，增加、刪除贯溅、合并和拆分分區(qū)將變得更加快捷拄氯，這有利于處理含有大扯數(shù)據(jù)的表。它的缺點在千它浅，與使用HASH分區(qū)得到的數(shù)據(jù)分布相比译柏，各個分區(qū)間數(shù)據(jù)的分布可能不大均衡。

4.KEY分區(qū)

????????KEY分區(qū)和HASH分區(qū)相似姐霍，不同之處在于HASH分區(qū)使用用戶定義的函數(shù)進行分區(qū)鄙麦，KEY分區(qū)使用MySQL數(shù)據(jù)庫提供的函數(shù)進行分區(qū)。對于NDBCluster引擎镊折， MySQL數(shù)據(jù)庫使用MD5函數(shù)來分區(qū)胯府；對于其他存儲引擎，MySQL數(shù)據(jù)庫使用其內(nèi)部的哈希函數(shù)恨胚，這些函數(shù)基于與PASSWORD()一樣的運算法則

5.COLUMNS分區(qū)

? ??????在前面介紹的RANGE骂因、LIST、HASH和KEY這四種分區(qū)中赃泡，分區(qū)的條件是：數(shù)據(jù)必須是整型(interger), 如果不是整型侣签，那應該需要通過函數(shù)將其轉化為整型塘装，如 YEARO, TO_DAYSO, MONTHO等函數(shù)。MySQL5.5版本開始支持COLUMNS分 區(qū)影所，可視為RANGE分區(qū)和LIST分區(qū)的一種進化蹦肴。COLUMNS分區(qū)可以直接使用非整型的數(shù)據(jù)進行分區(qū)，分區(qū)根據(jù)類型直接比較而得猴娩，不需要轉化為整型阴幌。此外，RANGEOLUMNS分區(qū)可以對多個列的值進行分區(qū)卷中。

COLUMNS分區(qū)支持以下的數(shù)據(jù)類型：

所有的整型類型矛双，如INT、SMALLINT蟆豫、TINYINT议忽、BIGINT。FLOAT和DECIMAL則不予支持十减。

日期類型栈幸，如DATE和DATETIME。其余的日期類型不予支持帮辟。

字符串類型速址，如CHAR、VARClfAR由驹、BINARY和VARBINARY芍锚。BLOB和TEXT類型不予支持。

4.8.3 子分區(qū)

? ??????子分區(qū)(subpartitioning)是在分區(qū)的基礎上再進行分區(qū)蔓榄， 有時也稱這種分區(qū)為復合分區(qū)(composite partitioning)并炮。 MySQL數(shù)據(jù)庫允許在RANGE和LIST的分區(qū)上再進行HASH或KEY的子分區(qū)。

子分區(qū)的建立需要注意以下幾個問題：?

1.每個子分區(qū)的數(shù)批必須相同甥郑。

2.要在一個分區(qū)表的任何分區(qū)上使用SUBPARTITION來明確定義任何子分區(qū)逃魄， 就必須定義所有的子分區(qū)

3.每個SUBPARTITION子句必須包括子分區(qū)的一個名字。

4.子分區(qū)的名字必須是唯一的

4.8.4 分區(qū)中的NULL值

????????MySQL數(shù)據(jù)庫允許對NULL值做分區(qū)壹若， 但是處理的方法與其他數(shù)據(jù)庫可能完全不同嗅钻。 MYSQL數(shù)據(jù)庫的分區(qū)總是視NULL值視小于任何的一個非NULL值， 這和MySQL數(shù)據(jù)庫中處理NULL值的ORDER BY操作是一樣的店展。 因此對于不同的分區(qū)類型养篓， MySQL數(shù)據(jù)庫對于NULL值的處理也是各不相同。

????????對于RANGE分區(qū)赂蕴， 如果向分區(qū)列插入了NULL值柳弄， 則MySQL數(shù)據(jù)庫會將該值放入最左邊的分區(qū)。

4.8.5 分區(qū)和性能

? ??????我常聽到開發(fā)人員說 “對表做個分區(qū)”，然后數(shù)據(jù)庫的查詢就會快了碧注。 這是真的嗎嚣伐？實際上可能根本感覺不到查詢速度的提升， 甚至會發(fā)現(xiàn)查詢速度急劇下降萍丐。 因此轩端，在合理使用分區(qū)之前， 必須了解分區(qū)的使用環(huán)境逝变。

????????數(shù)據(jù)庫的應用分為兩類： 一類是OLTP (在線事務處理）基茵， 如Blog、 電子商務壳影、 網(wǎng)絡游戲等拱层； 另一類是OLAP (在線分析處理）， 如數(shù)據(jù)倉庫宴咧、 數(shù)據(jù)集市根灯。 在一個實際的應用環(huán)境中， 可能既有OLTP的應用掺栅， 也有OLAP的應用烙肺。如網(wǎng)絡游戲中， 玩家操作的游戲數(shù)據(jù)庫應用就是OLTP的柿冲， 但是游戲廠商可能需要對游戲產(chǎn)生的日志進行分析茬高， 通過分 析得到的結果來更好地服務千游戲兆旬， 預測玩家的行為等假抄， 而這卻是OLAP的應用。

????????對于OLAP的應用丽猬， 分區(qū)的確是可以很好地提高查詢的性能宿饱， 因為OLAP應用大多數(shù)查詢需要頻繁地掃描一張很大的表。假設有一張1億行的表脚祟， 其中有一個時間戳屈性 列谬以。用戶的查詢需要從這張表中獲取一年的數(shù)據(jù)。 如果按時間戳進行分區(qū)由桌， 則只需要掃描相應的分區(qū)即可为黎。 這就是前面介紹的PartitionPruning技術。

????????然而對于OLTP的應用行您， 分區(qū)應該非常小心铭乾。 在這種應用下， 通常不可能會獲取一張大表中10%的數(shù)據(jù)娃循， 大部分都是通過索引返回幾條記錄即可炕檩。而根據(jù)B+樹索引的原理可知， 對于一張大表捌斧， 一般的B+樹需要2,...,3次的磁盤IO笛质。因此B+樹可以很好地完成操作泉沾， 不需要分區(qū)的幫助， 并且設計不好的分區(qū)會帶來嚴重的性能問題妇押。

? ??????我發(fā)現(xiàn)很多開發(fā)團隊會認為含有1000W行的表是一張非常巨大的表跷究， 所以他們往往會選擇采用分區(qū)， 如對主鍵做 10個 HAS H 的分區(qū)敲霍， 這樣每個分區(qū)就只有100W的數(shù)據(jù)了揭朝， 因此查詢應該變得更快了， 如SELECT * FROM TABLE WHERE PK =@pk色冀。 但是有沒有考慮過這樣一 種情況： IOOW和1000W行的數(shù)據(jù)本身構成的B+樹的層次都是一樣的潭袱， 可能都是2層 。那么上述走主鍵分區(qū)的索引并不會帶來性能的提高锋恬。好的屯换， 如果IOOOW的B+樹的高度是3, IOOW的B+樹的高度是2, 那么上述按主鍵分區(qū)的索引可以避免1次IO, 從而提高查詢的效率。 這沒問題与学， 但是這張表只有主鍵索引彤悔， 沒有任何其他的列需要查詢的。 如果還有類似如下的SQL語句： SELECT* FROM TABLE WHERE KEY=@key, 這時對于 KEY的查詢需要掃描所有的10個分區(qū)索守， 即使每個分區(qū)的查詢開銷為2次IO, 則一共需要20次IO晕窑。 而對于原來單表的設計， 對于KEY的查詢只需要2~3次IO卵佛。

4.8.6 在表和分區(qū)間交換數(shù)據(jù)

????????MySQL5.6 開始支持ALTER TABLE … EXCHANGE PARTITION語法杨赤。該語句允許分區(qū)或子分區(qū)中的數(shù)據(jù)與另一個非分區(qū)的表中的數(shù)據(jù)進行交換。如果非分區(qū)表中的數(shù)據(jù)為空截汪， 那么相當千將分區(qū)中的數(shù)據(jù)移動到非分區(qū)表中疾牲。若分區(qū)表中的數(shù)據(jù)為空， 則相當于將外部表中的數(shù)據(jù)導入到分區(qū)中衙解。

????????要使用ALTER TABLE … EXCHANGE PARTITION語句阳柔， 必須滿足下面的條件：

要交換的表需和分區(qū)表有著相同的表結構， 但是表不能含有分區(qū)

在非分區(qū)表中的數(shù)據(jù)必須在交換的分區(qū)定義內(nèi)

被交換的表中不能含有外鍵蚓峦， 或者其他的表含有對該表的外鍵引用

用戶除了需要ALTER舌剂、INSERT 和CREATE權限外， 還需要DROP的權限

此外暑椰， 有兩個小的細節(jié)需要注意：

使用該語句時霍转， 不會觸發(fā)交換表和被交換表上的觸發(fā)器

AUTO_INCREMENT列將被重置