表
4.1、innodb儲存引擎表型別
innodb表類似oracle的IOT表(索引聚集表-indexorganized table)，在innodb表中每張表都會有一個主鍵，如果在建立表時沒有顯示的定義主鍵則innodb如按照如下方式選擇或者建立主鍵。首先表中是否有唯一非空索引(unique not null)，如果有則該列即為主鍵。不符合上述條件，innodb儲存引擎會自動建立一個6位元組大小的指標，rowid()。
4.2、innodb邏輯儲存結構
innodb的邏輯儲存單元由大到小分別是 tablespace,segment,extent,page(block)組成
4.2.1、表空間(tablespace)
所有資料都是存放在表空間中的，啟用了引數innodb_file_per_table，則每張表內的資料可以單獨放到一個表空間中，每張表空間記憶體放的只是資料，索引和插入緩衝，其他類的資料，如undo資訊，系統事務資訊，二次寫緩衝等還是存放在原來你的共享表空間。
4.2.2、段(segment)
常見的segment有資料段、索引段、回滾段。innodb是索引聚集表，所以資料就是索引，索引就是資料，那麼資料段即是B+樹的頁節點(leaf node segment)，索引段即為B+樹的非索引節點(non-leaf node segment)。而且段的管理是由引擎本身完成的。
4.2.3、區(extend)
區是由64個連續的頁主成，每個頁大小為16K，即每個區的大小為(64*16K)=1MB,對於大的資料段，mysql每次最多可以申請4個區，以此保證資料的順序效能。
4.2.4、頁(page)
頁是innodb磁碟管理最小的單位，innodb每個頁的大小是16K，且不可更改。常見的型別有：資料頁 B-tree Node；undo頁 Undo Log Page；系統頁 System Page；事務資料頁 Transaction system Page；插入緩衝點陣圖頁 Insert Buffer Bitmap；插入緩衝空閒列表頁 Insert Buffer freeBitmap；未壓縮的二進位制大物件頁Uncompressed BLOB Page；壓縮的二進位制大物件頁 Compressed BLOB Page。
4.2.5、行
innodb儲存引擎是面向行的(row-oriented),也就是說資料的存放按行進行存放。每個頁最多可以存放16K/2～200行,也就是7992個行。
4.3、innodb物理儲存結構
innodb引擎由共享表空間，日誌檔案(redo log)，表結構定義檔案組成。
4.4、innodb行記錄格式
mysql從5.1開始，innodb提供了compact和redundant(為了相容以前版本)兩種格式來存放行記錄資料。
4.4.1、compact行記錄格式
Compact行記錄的設計目標是能高效存放資料。不管是char還是varchar型別，NULL指是不佔用儲存空間的。行記錄中還包括兩個隱藏列 事務ID列(6位元組)和回滾指標列(7位元組) 若沒有定義的PrimaryKey 會增加一個6位元組的RowID列。InnoDB在頁內部是通過一種連結串列方式串聯各個行記錄的。
4.4.2、redundant行記錄格式
Redundant行記錄格式為了相容以前版本。每個行最多有1023個列，因為列的數量佔用了10位。對於varchar的NULL值，它不佔用任何儲存空間，而對於型別char的NULL值需要佔用空間。
4.4.3、行溢位資料
innoDB儲存引擎可以將一條記錄中的某些資料儲存在真正的資料頁面之外，作為行溢位資料。Varchar(N)中的N指的是字元的長度，官方手冊中定義的65535長度是指所有VARCHAR列的長度總合。
資料一般都是存放在B-tree Node的頁型別中，但是發生行溢位的時，存放行溢位的頁型別為Uncompress BLOB Page。如果一個頁中至少放入兩行的資料，那varchar就不會存放到BLOB頁中，閥值長度為8098。對於TEXT或者BLOB的資料型別，我們總是以為它們是放在Uncompressed BLOB Page中的，其實這也是不準確的，放在資料頁還是BLOB頁同樣和前面討論的VARCHAR一樣。
4.4.4、compressed與dynamic記錄格式
InnoDB Plugin引入了新的檔案格式成為Barracuda檔案格式，它擁有兩種新的行記錄格式Compressed和Dynamic兩種，它對於存放BLOB的資料採用了安全的行溢位方式。
4.4.5、char的行結構儲存
從mysql4.1開始CHR(n)，中N指定的是字元的長度，而不是之前版本的位元組長度。也就是說在不同字符集下，CHAR的內部儲存不是定長的資料。可以通過select a,char_length(a),length(a) from t;檢視字元和位元組數。所以在多字符集下，char和varchar佔用a空間是一樣的。
4.5、innodb資料頁結構
InnoDB資料頁由七部分組成：File Header：檔案頭( 38 bytes )Page Header：頁頭( 56 bytes )Infimum + Supremum Records：頁中上/下界記錄Users Records：使用者記錄，即行記錄Free Space：空閒空間Page Directory：葉目錄File Trailer：檔案結尾資訊
4.6、named file formats
innodb儲存引擎通過named file formats機制來解決不同版本下頁結構相容性問題。之前的版本定義為Antelope(包括Compact和Redudant檔案格式)，最新定義為Barracuda(包括Compressed和Dynamic檔案格式)。使用引數innodb_file_format指定檔案格式。
4.7、約束
4.7.1、資料完整性
innodb提供了以下四種約束：Primary key，Unique Key，Foreign Key，Default，Not NULL。
4.7.2、約束的建立和查詢
建立時候定義，或者使用alter table定義。
4.7.3、約束和索引的區別
primary key和unique key既是約束也是主鍵。約束是一個邏輯的概念，用來保證資料完整性，而索引是一個資料結構，有邏輯上的概念，在資料庫中更是一個物理儲存的方式。
4.7.4、對於錯誤資料的約束
可以通過修改sql_mode來保證約束的強制性。
4.7.5、ENUM和SET約束
由於mysql不支援check約束，所以可以通過ENUM和SET來實現部分需求，還可以通過觸發器來實現check約束，注意需要修改sql_mode=’strict_trans_tables’; 只能限於對離散數值的約束，對於ENUM 若插入非法值將插入空字串作為特殊錯誤值。
4.7.6、觸發器與約束
觸發器的作用是在insert,delete和update命令之前或之後自動呼叫sql命令或者儲存過程。所以一個表最多可以建立6個觸發器。
4.7.7、外來鍵
4.8、檢視
4.8.1、檢視的作用
4.8.2、物化檢視
Oracle資料庫支援物化檢視—該檢視不是基於基表的虛表，而是根據基表實際存在的實表，物化檢視可以用於預先計算並儲存錶連結或聚集等耗時較多的操作結果。在MS中，這種檢視為索引檢視。當基表發生了DML操作後，物化檢視採用ON DEMAND和ON COMMIT方式重新整理進行同步。Mysql的檢視不支援物化檢視，都是虛擬的。
4.9、分割槽表
4.9.1、分割槽表的概述
分割槽表不是在儲存引擎曾完成的，所以不止innodb支援分割槽表功能。myisma，ndb等都支援。mysql的分割槽表是水平分割槽，並不是垂直分割槽，mysql的分割槽表是區域性分割槽索引，一個分割槽中既儲存資料又存放索引。當前mysql資料庫支援以下幾種型別的分割槽：Range分割槽，行資料基於屬於一個給定連續區間的列值放入分割槽，這個值只能是整數。VALUE LESS THAN需指定MAXVALUE值的分割槽，主要用於日期列的分割槽。對於RANGE分割槽的查詢，優化器只能對YEAR() TO_DAYS() TO_SECONDS()和UNIX_TIMESTAMP()函式進行優化選擇。LIST分割槽和range類似，只是list分割槽裡面是離散的值，這個值只能是整數。(VALUE IN對於未定義的插入，MySQL會丟擲異常。對於多條記錄同時插入過程中存在未定義的值時，MyISAM分割槽會允許之前的行資料插入，而拒絕之後的行資料插入，但是InnoDB將其視為一個事務從而ROLLBACK整個插入。HASH分割槽，根據使用者自定義的表示式的返回值 返回值不為負(PARTITION BY HASH (expr) 將資料均勻分佈還可按LINEAR HASH分割槽區別在於演算法不同)。hash分割槽的目的是將資料均勻的分佈到預先定義的各個分割槽中，保證各分割槽的資料量大致一致。KEY分割槽，根據mysql資料庫提供的哈西函式進行分割槽。key分割槽和hash分割槽相似，不同在於hash分割槽是使用者自定義函式進行分割槽，key分割槽使用mysql資料庫提供的函式進行分割槽。columns分割槽，mysql-5.5開始支援COLUMNS分割槽，可視為RANGE和LIST分割槽的進化，COLUMNS分割槽可以直接使用非整形資料進行分割槽。RANGE COLUMNS分割槽可對多個列的值進行分割槽。不論什麼型別的分割槽，如果表中存在主鍵和唯一索引，那麼分割槽列必須是主鍵或者唯一索引的一個組成部分。否則回報錯。
4.9.2、子分割槽
mysql允許在RANGE和LIST分割槽上再進行HASH或者key的子分割槽。每個分割槽上的子分割槽數量必須相同。在每個分割槽內，子分割槽的名稱是唯一的，分割槽可以放到不同磁碟上。
4.9.3、分割槽中的NULL值
RANGE,HASH,KEY分割槽如果插入null值，mysql會把它放入最左邊的分割槽，如果刪除最左邊的分割槽，null值不會被刪除，他會記錄到新的最左邊的分割槽。LIST分割槽如果沒有指定NULL值的存放位置，那麼就會報錯。
4.9.4、分割槽的效能
OLTP(線上事務處理，如部落格，電子商務，網路遊戲)系統不適合使用分割槽表，如果磁碟空間和磁碟IO沒出現瓶頸，也不建議使用分割槽表。而OLAP(線上分析處理，如資料倉儲，資料集市)比較適合分割槽操作。
索引和演算法
索引和開銷是需要找一個平衡點，過多或者過少都會影響效能，從而導致負載過高，浪費硬體資源。而且索引應該一開始就需要新增上，事後新增的話需要DBA根據監控大量SQL語句，耗費大量時間。
5.1、innodb儲存引擎概述
innodb支援常見的兩種索引，B+樹索引和hash索引。hash索引是自適應的，不能認為干預。B+樹是由平衡二叉樹演化而來，但是B+樹不是一個二叉樹。B+樹並不能直接找到具體的行，B+樹索引只能找到資料行所在的頁，然後資料庫通過把頁讀入記憶體，再在記憶體中進行查詢。
5.2、二分查詢法
頁中的具體行就是通過二分法查詢的。1946年發明的二分查詢法，直到1962年才出現完整正確的二分查詢法。
5.3、平衡二叉樹
平衡二叉樹(左節點鍵值
5.4、B+樹
B+樹是從B樹和索引順序訪問方法演化而來。在B+樹中，所有記錄節點都是按鍵值的大小順序存放在同一層的葉節點中，各頁節點指標進行連結。同時它們的父節點只是作為索引節點使用。
5.4.1、B+樹的插入操作
B+樹總會保持平衡，但是對於新插入的值可能需要大量拆分，這樣會消耗大量磁碟資源，所以B+樹有了旋轉(rotation)功能，旋轉發生在leat page已經滿了，但是其左右節點沒有滿的情況下，這時B+樹並不會著急去拆分頁的操作，而且是將記錄轉移到所在頁的兄弟節點上，通常左兄弟先被檢查。具體操作看書。
5.4.2、B+樹的刪除操作
B+樹使用填充因子(fill factor)來控制樹的刪除變化，50%是填充因子可設的最小值。B+樹的刪除操作同樣必須保證刪除後頁節點中的記錄依然排序。具體操作看書。
5.5、B+樹索引
B+樹索引在資料庫中有一個特點是高扇出性(fan out),B+樹的高度一般是2-3層。B+樹索引可以分為聚集索引(clustered index)和輔助聚集索引(secondary index)，其內部都是B+樹，葉節點存放著所有的資料。它們不同的是：葉節點存放的是否是一整行的資訊。聚集索引：即表中資料按照主鍵順序存放，而聚集索引就是按照每張表的主鍵構造一顆B+樹，並且葉節點中存放著整張表的行記錄資料。聚集索引的儲存並不是物理上的連續，而是邏輯上的連續。它的另一個好處是：對於主鍵的排序查詢和範圍查詢速度非常快。
輔助索引：也稱為非聚集索引，葉級別不包含行的全部資料，葉節點除了包行鍵值以外，每個葉級別中的索引行中還包含了一個書籤，該書籤就是對應行資料的聚集索引鍵。
5.5.1、B+樹索引的管理
索引可以索引整個列的資料，也可以只索引一個列的開頭部分資料。InnoDB Plugin支援一種稱為快速索引建立方法，這種方法只限定於輔助索引，建立索引會對錶加上一個S鎖，刪除時只需將輔助索引的空間標記為可用，並刪除內部檢視上的對該表的索引定義即可。
5.6、B+樹索引的使用
5.6.1、什麼時候使用B+樹索引
當某個欄位的取值範圍很廣，幾乎沒有重複，即高選擇性，則使用B+樹索引是最適合的。根據筆者經驗，一般取出資料佔整個的20%時，優化器就不會使用索引，而是全表掃描。
5.6.2、順序讀，隨機讀與預讀取
順序讀是指根據索引的葉節點資料就能順序地讀取所需要的行資料，只是邏輯地順序讀在物理磁碟上可能還是隨機讀取。隨機讀是指一般需要根據輔助索引葉節點中的主鍵尋找實際行資料，而輔助索引和主鍵所在的資料段不同，因此訪問方式是隨機的。為提高讀取效能，InnoDB採用預讀取方式將所需資料讀入記憶體，包括隨機預讀取 random read ahead 和線性預讀取 linear read ahead。但是自InnoDB Plugin1.0.4起,隨機訪問的預讀取被取消了,保留了線性預讀取,並加入了innodb_read_ahead_threshold引數。它控制一個區中多少頁被順序訪問時，InnoDB才啟用預讀取，預讀取下一個頁中所有的頁。
5.7、hash索引
innodb儲存引擎中自適應hash索引使用的是雜湊表(hash table)的資料結構。但是雜湊表不只存在於自適應hash中，每個資料庫中都存在，用來加速記憶體中資料的查詢。
5.7.1哈西表(hash table)
hash table又叫雜湊表，由直接定址表改進而來。利用雜湊函式解決了直接定址遇到的問題，同時又使用連結發解決了碰撞問題。
5.7.2自適應哈西索引
它是資料庫系統自己建立並使用的，DBA本身並不能對其進行干預。需要注意的是，雜湊索引只能用來搜素等值的查詢，對於其它的查詢是不能使用雜湊索引的。我們只能通過引數innodb_adaptive_hash_index來禁用或啟動此特性。
鎖
鎖是區別檔案系統和資料庫系統的一個關鍵特性。
6.1、什麼是鎖?
鎖是用來管理對共享檔案的併發訪問。innodb會在行級別上對資料庫上鎖。不過innodb儲存引擎會在資料庫內部其他多個地方使用鎖，從而允許對不同資源提供併發訪問。例如操作緩衝池中的LRU列表，刪除，新增，移動LRU列表中的元素，為了保證一致性，必須有鎖的介入。
6.2、innodb儲存引擎中的鎖
6.2.1、鎖的型別
S lock 共享鎖允許事務讀一行資料。X lock 排它鎖允許事務刪除或者更新一條資料。IS lock 意向共享鎖事務想要獲得一個表中某幾行的共享鎖。IX lock 意向拍他所事務想要獲得一個表中某幾行的排它鎖。因為InnoDB儲存引擎支援的是行級別的鎖，所以意向鎖其實不會阻塞除全表掃描以外的任何請求。
6.2.2、一致性的非鎖定讀操作
一致性非鎖定讀(consistent nonlocking read)是指innodb通過多版本控制(multi versioning)的方式來讀取當前執行時間資料庫中行的資料。非鎖定讀的機制大大提高了資料讀取的併發性，在InnoDB引擎中為預設的讀取方法，即讀取不會佔用和等代表上的鎖。多版本控制是通過快照實現的，快照資料其實就是當前資料之前的歷史版本，可能有多個版本。這種技術稱為行多版本技術，由此帶來的併發控制叫做多半本併發控制(multi version concurrency control,MVCC).在Read Committed和Repeatable Read(innodb預設的事務隔離級別)下，innodb儲存引擎使用非鎖定的一致性讀。但是對於快照資料的定義卻不同。在Read Commited級別，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照。在Repeatable級別下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。
6.2.3、SELECT…FOR UPDATE &SELECT…LOCK IN SHARE MODE
SELECT…FOR UPDATE 可以獲得一個X鎖。SELECT…LOCK IN SHARE MODE 可以獲得一個S鎖。注意上述操作時必須使用顯示提交方式，即加上begin,start transaction或者set autocommit = 0。
6.2.4、自增長和鎖
對於含有子增長計數器的表進行插入時，會執行”SELECT MAX(auto_inc_col) FROM t FOR UPDATE;”插入操作會更具這個自增長的計數器值加1賦予自增長列。這個實現方式叫做AUTO-INC Locking。這是一種特殊的鎖，為了提高併發，它不會在事務執行完才釋放，只是在語句執行後立即釋放。從mysql-5.1.22版本開始，innodb引擎提供了一種輕量級互斥量的自增長實現機制，這種機制大大提高了子增長值插入的效能。並且mysql-5.1.22開始，innodb引擎提供了一個引數innodb_autoinc_lock_mode，預設的值為1。在討論新的增長方式之前我們需要對自增長實現方式分類：1.INSERT-LIKE：指所有的插入語句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。2.Simple insert：指在插入前就能確定插入行數的語句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE這類語句。3.Bulk inserts：指在插入前不能確定得到插入行的語句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.4.Mixed-mode inserts:指其中一部分是子增長的，有一部分是確定的。現在有SIMPLE INSERT、BULK INSERTS、MIXED-MODE INSERTS三種型別的INSERT語句，有AUTO-inc locking(最早的)和輕量級互斥量的自增長兩種auto—increment鎖。1.innodb_autoinc_lock_mode=0 5.1.22之前的方式，也就是所有型別的insert都用AUTO-inc locking。2.innodb_autoinc_lock_mode=1 這個引數是5.1.22之後出現的也是之後的預設值，對於SIMPLE INSERT，使用輕量級互斥量的鎖，對於BULK INSERT，使用AUTO-inc locking。3.innodb_autoinc_lock_mode=2 指不管什麼情況都使用輕量級互斥的鎖，效率最高，但是複製只能使用row-basereplication，因為statement-base replication會出現問題。另外就是innodb和myisam的一個區別，innodb下，自增長必須是索引，而且必須是索引的第一個列，不然會報錯，myisam不會出現這個問題。
6.2.5、外來鍵和鎖
外來鍵主要用於引用完整性的約束檢查。innodb中，對於一個外來鍵列，如果沒有顯示的對這個列加索引，innodb就自動的對其加一個索引。
6.3、鎖的演算法
1.Record Lock，單行記錄上的鎖，鎖住索引記錄。2.GapLock，間隙鎖能鎖定一個範圍，但不包括記錄本身如 < 6 時，依然可以插入6。3.Next-KeyLock：Gap Lock + Record Lock，鎖定一個範圍並且鎖定記錄本身，如 < 6，插入6時會被阻塞。在REPEATABLE READ模式下 Next-KeyLock演算法是預設的行記錄鎖定演算法。
6.4、鎖問題
本來鎖問題會導致的是更新丟失、幻讀、髒讀、不可重複讀，但是innodb作者卻只寫出了三種問題,可能是幻讀通過innodb Next-key Lock解決了，作者就沒有提及。這幾個鎖問題對應事務隔離的4個安全級別:READ UNCOMMITTED(事務隔離最低的級別，有事務隔離就能解決更新丟失，但是存在髒讀的問題)。READ COMMITED(ORACLE和SQL SERVER預設的隔離級別，解決了髒讀，但是一個事務多次讀取的內容不同，出現了不可重複讀的問題)。READ REPEATABLE(可重複讀，innodb引擎的預設事務隔離級別，解決了不可重複讀的問題，但是產生了幻讀，innodb通過Next-key lock解決了幻讀)。SERIALIZABLE(可序列話，通過強制事務排序解決幻讀問題，會降低效能)總的看來innodb預設的 READ REPEATABLE是非常棒的。
6.5、阻塞
innodb中需要其他事務的鎖釋放它鎖佔用的資源，這個時候就會發生鎖等待，這就是阻塞。innodb引擎有兩個相關引數：innodb_lock_wait_timeout 用來設定等待的時間，預設是50秒，這是一個動態引數，可以隨時調整；innodb_rollback_on_timeout用來設定是否在等待超時時對進行中的事務進行回滾操作，預設是OFF，代表不回滾，這是一個靜態引數。
6.6、死鎖
死鎖會產生阻塞，所以可以通過6.5的引數，讓超時的阻塞回滾。還有就是開發的時候，每個事務對錶，欄位，行的操作，都是順序的，這樣可以很大程度上避免死鎖。