(轉)Mysql技術內幕InnoDB儲存引擎－表&索引演算法和鎖

標籤：運算式   使用者自訂函數   rect   5.5   面向   排它   tab   檔案格式   mode   

表

原文：http://yingminxing.com/mysql%E6%8A%80%E6%9C%AF%E5%86%85%E5%B9%95innodb%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%EF%BC%8D%E8%A1%A8%E7%B4%A2%E5%BC%95%E7%AE%97%E6%B3%95%E5%92%8C%E9%94%81/

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 若插入非法值將插入Null 字元串作為特殊錯誤值。

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、平衡二叉樹

平衡二叉樹(左節點索引值<根節點索引值 <右節點索引值)首先的符合二叉樹定義，其次必須滿足任何節點的左右兩個子樹高度最大差1.平衡二叉樹的效率較高，但是維護平衡二次樹需要消耗比較多的資源。多用於記憶體結構對象中，維護開銷相對比較小。

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的參數，讓逾時的阻塞復原。還有就是開發的時候，每個事務對錶，欄位，行的操作，都是順序的，這樣可以很大程度上避免死結。

(轉)Mysql技術內幕InnoDB儲存引擎－表&索引演算法和鎖