4.1 索引組織表
在 innodb 引擎中， 表都是根據(jù)主鍵順序組織存放的。 每張表都有個主鍵（primary key）， 如果在創(chuàng)建表時沒有顯示地定義主鍵， 則 innodb 會按一下方式：
1 首先判斷表中是否有非空的唯一索引（unique not null）, 如果有， 則該列為主鍵。多個時， 選擇第一個， 順序為定義索引順序。
2 如果不符合上述條件， innodb 會自動創(chuàng)建一個 6 字節(jié)大小的指針。
mysql>select a,b,c,d,_rowid from z;?
_rowid 可以顯示表的主鍵，但只能是單列為主鍵情況， 對于多列組成組件就顯得無能為力了。
4.2 Innodb 邏輯存儲結(jié)構(gòu)
4.2.1 表空間
啟用了 innodb_file_per_table 參數(shù)， 需要注意的是每張表空間內(nèi)存放的只是數(shù)據(jù)， 索引和插入緩沖 bitmap 頁， 其他類的數(shù)據(jù)，如 回滾（undo）信息， 插入緩沖索引頁，系統(tǒng)事務(wù)信息，二次寫緩沖（double write buffer）等還是存放在原來的共享表空間內(nèi)。
innodb 不會再執(zhí)行 rollback 時去回收共享表空間。 雖然 innodb 不會回收這些空間， 但是會自動判斷這些 undo 信息是否還需要， 如果不需要， 則將這些空間標(biāo)記為可用空間， 供下次 undo 使用。 master thread 每 10 秒會執(zhí)行一次 full purge 操作， 有可能用戶執(zhí)行update 語句后， 會發(fā)現(xiàn) ibdata1 不會再增大了， 就是這個原因。
4.2.2 段
數(shù)據(jù)段為 B+ 數(shù)的葉子節(jié)點（leaf ?node segment）, 索引段為 B+ 數(shù)的非索引節(jié)點（non-leaf node segment）.
4.2.3 區(qū)
區(qū)是由連續(xù)頁組成的空間， 在任何情況下每個區(qū)的大小都是 1MB。 為了保證區(qū)中頁的連續(xù)性， Innodb 一次從磁盤申請 4~5 個區(qū)。 默認(rèn)情況下， Innodb 頁的大小為 16KB， 即一個區(qū)中一共有 64 個連續(xù)的頁。在用戶啟用了 innodb_file_per_table 后， 創(chuàng)建的表默認(rèn)大小為 96KB。 區(qū)中是 64 個連續(xù)的頁， 創(chuàng)建的表的大小至少是 1MB 才對?。?其實 這是因為在每個段開始時， 先用 32 個頁大小的碎片頁（fragment page）來存放數(shù)據(jù)，在使用完這些頁之后才是 64 個連續(xù)頁的申請。 這樣做的目的是， 對于一些小表， 或者是 undo 這類的段， 可以在開始申請較少的空間， 節(jié)省磁盤容量的開銷。
舉例：工具 py_innodb_page_info?
mysql>insert t1 select null, repeat('a', 7000);
mysql>insert t1 select null, repeat('a', 7000);
4.2.4 頁
頁是 innodb 磁盤管理的最小單位。 默認(rèn)大小 16Kb。 從 innodb 1.2.x 開始， 可通過 innodb_page_size 將頁的大小設(shè)置為 4K， 8K， 16K。 設(shè)置完成， 所有野大小都為 innodb_page_size, 不可以對其再次進(jìn)行修改。除非通過 mysqldump 導(dǎo)入和導(dǎo)出操作來產(chǎn)生新的庫。
4.2.5 行
Innodb 是面向列的(row-oriented), 也就是數(shù)據(jù)是按行進(jìn)行存放的。?
4.3 Innodb 行記錄格式
在 mysql 5.1 版本中， 默認(rèn)設(shè)置 compact 行格式。 用戶可以通過 show table status like 'table' 來查看當(dāng)前使用的行格式， 其中 row_format 屬性表示當(dāng)前使用的行記錄結(jié)構(gòu)類型。 數(shù)據(jù)庫實例的作用之一就是讀取頁中存放的行記錄。
4.3.1 ?Compact 行記錄格式
hexdump -C -v xxx.ibd > xxx.txt
4.3.2 Redundant 行記錄格式
4.3.3 行溢出數(shù)據(jù)
在字符類型為 latin1 下，理論值為 65535， 實測 varchar 類型的最大長度為 65532, 因為有其他開銷。 創(chuàng)建時，大于這個值時， 若設(shè)置 SQL_MODE 為嚴(yán)格模式，則會報錯， 若 不是嚴(yán)格模式， 則允許創(chuàng)建表。 數(shù)據(jù)庫拋出一個 warning。 varchar 類型轉(zhuǎn)換成了 text 類型。
若字符類型為 gbk (max=32767), utf8 (max=21845), 可知 varchar(N) 中的 N 指的是字符的長度，而文檔中 varchar 最大支持 65535 ，字節(jié)。注：該長度是指所有的 varchar 列的長度總和， 如果列的長度總和超過這個長度， 依然無法創(chuàng)建。
Innodb 的頁為 16KB， 即 16384 字節(jié)， 卻能存放 65532 字節(jié)。 原因是 一般情況下， 數(shù)據(jù)都是存放在頁類型為 B-tree node 中。 當(dāng)發(fā)生行溢出時， 數(shù)據(jù)存放在頁類型為 uncompress blob 頁中。
數(shù)據(jù)節(jié)點 B-tree Node 保存了varchar(65532) 的前 768 字節(jié)的前綴（prefix）數(shù)據(jù)， 之后是偏移量，指向行溢出頁， 也就是 uncompressed blob page.
如果頁中只能存放下一條記錄，那么 innodb 存儲會自動將行數(shù)據(jù)存放到溢出頁中。因為 innodb 是索引組織的， 即 B+ tree 結(jié)構(gòu)， 這樣每個頁中至少應(yīng)該有兩條記錄，否則失去了 B+ tree 的意義，變成鏈表了。
經(jīng)過多次測試， 發(fā)現(xiàn)閾值長度為 8098. 即能保證插入 2 條記錄。
對于 text 或 blob 數(shù)據(jù)類型， 用戶不總是存放在 uncompressed blob page 中。是放在數(shù)據(jù)頁中還是 blob 頁中， 取決于一個頁中是否至少存兩條記錄。
用戶既然使用了 blob 類型， 一般不可能存放長度太小的數(shù)據(jù)。 因此大多數(shù)情況下， blob 的行數(shù)還是會發(fā)生行溢出， 實際數(shù)據(jù)保存在 blob 頁中， 數(shù)據(jù)頁只保存數(shù)據(jù)的 768 字節(jié)。
4.3.4 Compressed 和 Dynamic 行記錄格式
Antelope： compact 和 Redundant
Barracuda： Compressed 和 Dynamic
Barracuda 采用完全的行溢出方式。數(shù)據(jù)頁中只存放 20 個字節(jié)的指針， 實際的數(shù)據(jù)都存放在 off page ?中。
compressed 行記錄的另一個功能就是， 存儲在其中的行記錄會以 zlib 的算法進(jìn)行壓縮， 因此對于 blob，text，varchar 這類大長度類型的數(shù)據(jù)能夠進(jìn)行非常有效的存儲。
4.3.5 Char 的行結(jié)構(gòu)存儲
InnoDB 內(nèi)部 char 類型在多字節(jié)字符集類型存儲。 char 類型被明確視為了變長字符類型， 對于未能占滿長度的字符還是填充 0x20. Innodb 存儲內(nèi)部對字符的存儲和我們用 hex 函數(shù)看到的一致。 因此認(rèn)為在多字節(jié)字符集的情況下， char 和 varchar 的實際行存儲基本沒有區(qū)別。
4.4 Innodb 數(shù)據(jù)頁結(jié)構(gòu)
4.4.1File Header
4.4.2 Page Header
4.4.3 Infimum 和 Supermum Record
4.4.4 User Record 和 Free Space
4.4.5 Page Directory
4.4.6 File Trailer
4.4.7 Innodb 數(shù)據(jù)頁結(jié)構(gòu)示例分析
4.5 Named File Formats 機(jī)制
參數(shù) innodb_file_format 用來指定文件格式。
參數(shù) innodb_file_format_check 用來檢測當(dāng)前 Innodb 引擎文件格式的支持度。
4.6 約束
4.6.1 數(shù)據(jù)完整性
關(guān)系型數(shù)據(jù)庫系統(tǒng)和文件系統(tǒng)的一個不同點事， 關(guān)系數(shù)據(jù)庫本身能保證存儲數(shù)據(jù)的完整性， 不需要應(yīng)用程序控制，而文件系統(tǒng)一般需要再程序端進(jìn)行控制。
innodb 的約束：primary key, unique key, foreign key, default, not null
4.6.2 約束的創(chuàng)建和查找
表建立時就進(jìn)行約束定義
利用 alter table 命令來進(jìn)行創(chuàng)建約束
4.6.3 約束和索引的區(qū)別
約束更是一個邏輯的概念， 用來保證數(shù)據(jù)的完整性； 而索引是一個數(shù)據(jù)結(jié)構(gòu)， 既有邏輯上的概念， 在數(shù)據(jù)庫中還代表著物理存儲的方式。
4.6.4 對錯誤數(shù)據(jù)的約束
默認(rèn)情況下， 向 not null 的列插入一個 null 值， 向 date 列 出入一個非法日期， 如‘’2009-02-30，mysql 不會報錯，而是顯示警告，查詢時 會分別顯示 0 和?0000-00-00 。 通過 show warnings 來顯示警告信息。
通過設(shè)置 sql_mode 來嚴(yán)格審核輸入的參數(shù)， 如?
mysql>set sql_mode='STRICT_TRANS_TABLES';
4.6.5 ENUM 和 SET 約束
規(guī)定域范圍只能是 male 或 female ， 時， 可以用 enum 類型來進(jìn)行約束
mysql->create table a(id int, sex enum('male', 'female'));
4.6.6 觸發(fā)期與約束
最多可以為一個表建立 6 個觸發(fā)器， 分別為 insert , update ， delete 的 before 和 after 各定義一個。 當(dāng)前 mysql 只支持 ?for each row ?的觸發(fā)方式， 即按每行記錄進(jìn)行觸發(fā)， 不支持像 DB2 的 for each statement 的觸發(fā)方式。
例如， 創(chuàng)建 before 觸發(fā)， 判斷新舊值差異， 新值總小于舊值。 非法數(shù)據(jù)則還原， 并記錄，新，舊值到 uercash_err_log 表。
4.6.7 外鍵約束
外鍵定義時的 on delete 和 on update 表示在對父表進(jìn)行 delete 和 update 操作時， 對字表所做的操作， 可定義的字表操作有：
cascade ：對 父表進(jìn)行 delete 或 update 操作時， 對相應(yīng)字表頁驚醒delete 或 update 操作。
set null：對父表 delete 或 update 時， 對相應(yīng)子表中的數(shù)據(jù)更新為 null 值， 單字表相應(yīng)列必須運行為 null 值。
no action 和?restrict：當(dāng)父表 delete 或 update 操作時， 拋出錯誤，不允許這類操作發(fā)生。
目前 mysql 外鍵約束都是 及時檢查，從定義看出， no action 和 restrict 的功能使相同的。
如果沒有指定 on delete 或 on update ， restrict 是默認(rèn)的外鍵設(shè)置。
Innodb 在外鍵建立時會自動地對該列加一個索引。 如 給 parent_id 加索引。
4.7 視圖
與持久表不同的是， 視圖中的數(shù)據(jù)沒有實際的物理存儲。
4.7.1 視圖的作用
一 抽象封裝
程序本身不需要關(guān)心基表（base table）的結(jié)構(gòu)
二 安全層
按照視圖定義來取數(shù)據(jù)或更新數(shù)據(jù)。雖然視圖是基于基表的一個虛擬表， 但是用戶可以對某些視圖進(jìn)行更新操作， 其本質(zhì)是通過視圖的定義來更新基本表。
不加 with check option, 則可以更新不符合的數(shù)據(jù)到基表，但視圖中不顯示， 且不報錯。 加上后則，報錯。
show tables， 該命令會顯示出當(dāng)前數(shù)據(jù)庫下的所有的表， 但因為視圖是虛表， 同樣被作為表顯示出來。 基表可通過 information_schema 下的 table 表來查詢， 并搜索表類型為 base table 的表。要查看視圖的元數(shù)據(jù)（meta data）， 可以訪問 information_schema 下的 views 表。
4.7.2 物化視圖
物化視圖的好處是對于一些復(fù)雜的統(tǒng)計類查詢能直接查處結(jié)構(gòu)。
mysql 數(shù)據(jù)庫本身并不支持物化視圖， 換句話說， mysql 數(shù)據(jù)庫中的視圖總是虛擬的。 但是用戶可以通過一些機(jī)制來實現(xiàn)物化視圖的功能。
4.8 分區(qū)表
4.8.1 分區(qū)概述
幾種分區(qū)類型：
range 分區(qū)，list 分區(qū)，hash 分區(qū)，key 分區(qū)
無論創(chuàng)建何種類型的分區(qū)， 如果表中存在主鍵或唯一索引時， 分區(qū)列必須是唯一索引的一個組成部分。如果建表時沒有指定主鍵， 唯一索引， 可以指定任何一個列為分區(qū)列。
4.8.2 分區(qū)類型
1 range 分區(qū)：行數(shù)據(jù)基于屬于一個給定連續(xù)區(qū)間的列值被放入分區(qū)。 mysql 5.5 開始支持 range columns 的分區(qū)
查詢 information_schema 庫中的 PARTITIONS 表來查看每個分區(qū)的具體信息。PARTITION_METHOD 表示分區(qū)的類型， 這里顯示 range.
maxvalue 表示正無窮。 values less than maxvalue
分區(qū)后的好處：
1 便于管理。 如刪除某一年份的數(shù)據(jù)， 只是刪除該年的分區(qū)即可。
alter table sales drop partition p2008;
2 加快某些查詢操作。 如查詢 2008 年的銷售額。
explain partitions select * from sales where date >='2008-01-01' and date <= '2008-12-31'\G;
可看到 partitions:p2008 ,只搜索了 p2008 這個分區(qū)，而不會所有的分區(qū)。
優(yōu)化器只能對 year(), to_days(), to_seconds(), unix_timestamp(), 這類函數(shù)進(jìn)行優(yōu)化選擇。
2 list 分區(qū)： 和 range 分區(qū)類似， 只是 list 分區(qū)面向的是離散的值。 mysql 5.5 開始支持list columns 的分區(qū)。
在 insert 插入多個行數(shù)據(jù)的過程中遇到分區(qū)未定義的值時， myisam 和 innodb 存儲引擎的處理完全不同。 myisam 會將之前的行數(shù)據(jù)都插入 ，但之后的數(shù)據(jù)不會被插入。 而 innodb 會視其為一個事務(wù)， 因此沒有任何數(shù)據(jù)插入。
3 hash 分區(qū)： 根據(jù)用戶自定義的表達(dá)式的返回值來進(jìn)行分區(qū)， 返回值不能為負(fù)值。
hash 分區(qū)的目的是將數(shù)據(jù)均勻地分布到預(yù)定義的各個分區(qū)中， 保證各分區(qū)的數(shù)據(jù)數(shù)量大致都是一樣的。
mysql 還支持一種稱為 linear hash 的分區(qū)， 它使用一個更加復(fù)雜的算法來確定新行插入到已經(jīng)分區(qū)的表中的位置。 linear hash 分區(qū)的優(yōu)點 在于， 增加、刪除、合并和拆分
4 key 分區(qū)： 根據(jù) mysql 數(shù)據(jù)庫提供的哈希函數(shù)來進(jìn)行分區(qū)。key 分區(qū)和 hash 分區(qū)相似， 不同之處在于 hash 分區(qū)使用用戶定義的函數(shù)進(jìn)行分區(qū)， key 分區(qū)使用 mysql 數(shù)據(jù)庫提供的函數(shù)進(jìn)行分區(qū)。
5 COLUMNS 分區(qū)： columns 分區(qū)可以直接使用非整形的數(shù)據(jù)進(jìn)行分區(qū)， 分區(qū)根據(jù)類型直接比較而得， 不需要轉(zhuǎn)化為整形。
mysql 5.5 開始支持 columns 分區(qū)， 對于之前的 range 和 list 分區(qū)， 用戶可以用 range columns 和list columns 分區(qū)很好的代替。
4.8.3 子分區(qū)
4.8.4 分區(qū)中的 Null 值
range 分區(qū)總是視 Null 值視為 小于任何一個非 null 值， 這和 mysql 數(shù)據(jù)庫中處理 null 值的 order by 操作是一樣的。
在 list 分區(qū)下要使用 null 值， 則必須顯示地指出那個分區(qū)中放入 null 值， 否則報錯。
hash 和 key 分區(qū)對于 null 值的處理方式和 range ，list 分區(qū)不一樣。 任何分區(qū)函數(shù)都將含有 null 值的記錄返回為 0.
4.8.5 分區(qū)和性能
對于 olap (在線分析處理)的應(yīng)用，分區(qū)的確是可以很好地提高查詢的性能，因為 olap 應(yīng)用大多數(shù)查詢需要頻繁地掃描一張很大的表。
對于 oltp （在線事務(wù)處理）， 分區(qū)應(yīng)該非常小心。通常不會獲取一張達(dá)標(biāo)的 10% 的數(shù)據(jù)，大部分都是通過索引返回幾條記錄即可。這時，分區(qū)反而會使性能下降。
4.8.6 在表和分區(qū)間交換數(shù)據(jù)
mysql 5.6 開始支持 alter table .... exchange partition 語法。 該語句允許分區(qū)或子分區(qū)中的數(shù)據(jù)與另一個非分區(qū)的表中的數(shù)據(jù)進(jìn)行交換。
創(chuàng)建一個同樣的表：
create table e2 like e;
