從 MySQL 5.7 開始，開發人員改變了 InnoDB 構建二級索引的方式，採用自下而上的方法，而不是早期版本中自上而下的方法了。在這篇文章中，我們將透過一個示例來說明如何構建 InnoDB 索引。最後，我將解釋如何透過為 innodb_fill_factor 設定更合適的值。

索引構建過程

在有資料的表上構建索引，InnoDB 中有以下幾個階段：1.讀取階段（從聚簇索引讀取並構建二級索引條目）2.合併排序階段3.插入階段（將排序記錄插入二級索引）在 5.6 版本之前，MySQL 透過一次插入一條記錄來構建二級索引。這是一種“自上而下”的方法。搜尋插入位置從樹的根部（頂部）開始並達到葉頁（底部）。該記錄插入游標指向的葉頁上。在查詢插入位置和進行業面拆分和合並方面開銷很大。從MySQL 5.7開始，新增索引期間的插入階段使用“排序索引構建”，也稱為“批次索引載入”。在這種方法中，索引是“自下而上”構建的。即葉頁（底部）首先構建，然後非葉級別直到根（頂部）。

示例

在這些情況下使用排序的索引構建：

ALTER TABLE t1 ADD INDEX（or CREATE INDEX）ALTER TABLE t1 ADD FULLTEXT INDEXALTER TABLE t1 ADD COLUMN, ALGORITHM = INPLACEOPIMIZE t1對於最後兩個用例，ALTER 會建立一箇中間表。中間表索引（主要和次要）使用“排序索引構建”構建。演算法在 0 級別建立頁，還要為此頁建立一個遊標使用 0 級別處的遊標插入頁面，直到填滿頁面填滿後，建立一個兄弟頁（不要插入到兄弟頁）為當前的整頁建立節點指標（子頁中的最小鍵，子頁碼），並將節點指標插入上一級（父頁）在較高級別，檢查遊標是否已定位。如果沒有，請為該級別建立父頁和遊標在父頁插入節點指標如果父頁已填滿，請重複步驟 3, 4, 5, 6現在插入兄弟頁並使遊標指向兄弟頁在所有插入的末尾，每個級別的遊標指向最右邊的頁。提交所有遊標（意味著提交修改頁面的迷你事務，釋放所有鎖存器）為簡單起見，上述演算法跳過了有關壓縮頁和 BLOB（外部儲存的 BLOB）處理的細節。透過自下而上的方式構建索引為簡單起見，假設子頁和非子頁中允許的 最大記錄數為 3CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c BLOB);INSERT INTO t1 VALUES (1, 11, "hello111");INSERT INTO t1 VALUES (2, 22, "hello222");INSERT INTO t1 VALUES (3, 33, "hello333");INSERT INTO t1 VALUES (4, 44, "hello444");INSERT INTO t1 VALUES (5, 55, "hello555");INSERT INTO t1 VALUES (6, 66, "hello666");INSERT INTO t1 VALUES (7, 77, "hello777");INSERT INTO t1 VALUES (8, 88, "hello888");INSERT INTO t1 VALUES (9, 99, "hello999");INSERT INTO t1 VALUES (10, 1010, "hello101010");ALTER TABLE t1 ADD INDEX k1(b);InnoDB 將主鍵欄位追加到二級索引。二級索引 k1 的記錄格式為(b, a)。在排序階段完成後，記錄為：(11,1), (22,2), (33,3), (44,4), (55,5), (66,6), (77,7), (88,8), (99,9), (1010, 10)初始插入階段讓我們從記錄 (11,1) 開始。在 0 級別（葉級別）建立頁建立一個到頁的遊標所有插入都將轉到此頁面，直到它填滿了箭頭顯示遊標當前指向的位置。它目前位於第 5 頁，下一個插入將轉到此頁面。還有兩個空閒插槽，因此插入記錄 (22,2) 和 (33,3) 非常簡單對於下一條記錄 (44,4)，頁碼 5 已滿（前面提到的假設最大記錄數為 3）。這就是步驟。頁填充時的索引構建建立一個兄弟頁，頁碼 6不要插入兄弟頁在遊標處提交頁面，即迷你事務提交，釋放鎖存器等作為提交的一部分，建立節點指標並將其插入到 【當前級別 + 1】 的父頁面中（即在 1 級別）節點指標的格式 (子頁面中的最小鍵,子頁碼) 。第 5 頁的最小鍵是 (11,1) 。在父級別插入記錄 ((11,1)，5)。1 級別的父頁尚不存在，MySQL 建立頁碼 7 和指向頁碼 7 的遊標。將 ((11,1)，5) 插入第 7 頁現在，返回到 0 級並建立從第 5 頁到第 6 頁的連結，反之亦然0 級別的遊標現在指向兄弟頁，頁碼為 6將 (44,4) 插入第 6 頁下一個插入 - (55,5) 和 (66,6) - 很簡單，它們轉到第 6 頁。插入記錄 (77,7) 類似於 (44,4)，除了父頁面 （頁面編號 7） 已經存在並且它有兩個以上記錄的空間。首先將節點指標 ((44,4),8) 插入第 7 頁，然後將 (77,7) 記錄到同級 8 頁中。插入記錄 (88,8) 和 (99,9) 很簡單，因為第 8 頁有兩個空閒插槽。下一個插入 (1010,10) 。將節點指標 ((77,7),8) 插入 1級別的父頁（頁碼 7）。MySQL 在 0 級建立同級頁碼 9。將記錄 (1010,10) 插入第 9 頁並將游標更改為此頁面。以此類推。在上面的示例中，資料庫在 0 級別提交到第 9 頁，在 1 級別提交到第 7 頁。我們現在有了一個完整的 B+-tree 索引，它是自下至上構建的！索引填充因子全域性變數 innodb_fill_factor 用於設定插入 B-tree 頁中的空間量。預設值為 100，表示使用整個業面（不包括頁首）。聚簇索引具有 innodb_fill_factor=100 的免除項。 在這種情況下，聚簇索引也空間的 1 /16 保持空閒。即 6.25% 的空間用於未來的 DML。值 80 意味著 MySQL 使用了 80% 的頁空間填充，預留 20% 於未來的更新。如果 innodb_fill_factor=100 則沒有剩餘空間供未來插入二級索引。如果在新增索引後，期望表上有更多的 DML，則可能導致業面拆分並再次合併。在這種情況下，建議使用 80-90 之間的值。此變數還會影響使用 OPTIMIZE TABLE 和 ALTER TABLE DROP COLUMN, ALGOITHM=INPLACE 重新建立的索引。也不應該設定太低的值，例如低於 50。因為索引會佔用浪費更多的磁碟空間，值較低時，索引中的頁數較多，索引統計資訊的取樣可能不是最佳的。最佳化器可以選擇具有次優統計資訊的錯誤查詢計劃。排序索引構建的優點沒有頁面拆分（不包括壓縮表）和合並沒有重複搜尋插入位置插入不會被重做記錄（頁分配除外），因此重做日誌子系統的壓力較小缺點ALTER 正在進行時，插入效能降低 Bug＃82940，但在後續版本中計劃修復。

多個網路使用者同時讀取同一資料庫表，並不會產生衝突，只有一部分讀另一部分寫或者大家都要寫資料庫時才會產生衝突，資料庫執行的是併發操作，也就是說微觀上是序列操作，宏觀上是並行操作。mysql是網路資料庫，支援多事務處理，為了保證資料庫的一致性，就要在訪問資料庫時合理運用互斥機制。

瞭解這個機制就好辦了，常用的鎖有共享鎖，也就是讀鎖，排斥鎖也就是寫鎖，還有更新鎖，也就是更新操作時加的鎖，也可以歸類於寫鎖。如果已經加了讀鎖了，就不要再加寫鎖了，防止資料不一致。如果有寫鎖了就不要再加寫鎖了，防止資料庫死鎖。

InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。下面我們先介紹一點背景知識，然後詳細討論InnoDB的鎖問題。

背景知識

事務（Transaction）及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。

原子性（Atomicity）：所謂原子性就是將一組操作作為一個操作單元，是原子操作，即要麼全部執行，要麼全部不執行。一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；事務結束時，所有的內部資料結構（如B樹索引或雙向連結串列）也都必須是正確的。(拿銀行轉賬來說，一致性要求事務的執行不應改變A、B 兩個賬戶的金額總和。如果沒有這種一致性要求，轉賬過程中就會發生錢無中生有，或者不翼而飛的現象。事務應該把資料庫從一個一致性狀態轉換到另外一個一致性狀態。)隔離性（Isolation）：隔離性指併發的事務是相互隔離的。即一個事務內部的操作及正在操作的資料必須封鎖起來，不被其它企圖進行修改的事務看到。 永續性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

併發事務處理帶來的問題

　　相對於序列處理來說，併發事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支援更多的使用者。但併發事務處理也會帶來一些問題，主要包括以下幾種情況。

　　更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題－－最後的更 新覆蓋了由其他事務所做的更新。例如，兩個編輯人員製作了同一文件的電子副本。每個編輯人員獨立地更改其副本，然後儲存更改後的副本，這樣就覆蓋了原始文 檔。最後儲存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一檔案，則可避免此問 題。　　髒讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加 控制，第二個事務讀取了這些“髒”資料，並據此做進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象地叫做"髒讀"。　　不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些資料後的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重複讀”。　　幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”。

2、事務併發會產生問題例子:

1）第一類丟失更新：在沒有事務隔離的情況下，兩個事務都同時更新一行資料，但是第二個事務卻中途失敗退出， 導致對資料的兩個修改都失效了。

例如：

張三的工資為5000，事務A中獲取工資為5000，事務B獲取工資為5000，匯入100，並提交資料庫，工資變為5100，

隨後

事務A發生異常，回滾了，恢復張三的工資為5000，這樣就導致事務B的更新丟失了。

2）髒讀：髒讀就是指當一個事務正在訪問資料，並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務也訪問這個資料，然後使用了這個資料。例如：　　張三的工資為5000,事務A中把他的工資改為8000,但事務A尚未提交。　　與此同時，　　事務B正在讀取張三的工資，讀取到張三的工資為8000。　　隨後，　　事務A發生異常，而回滾了事務。張三的工資又回滾為5000。　　最後，　　事務B讀取到的張三工資為8000的資料即為髒資料，事務B做了一次髒讀。3）不可重複讀：是指在一個事務內，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也訪問該同一資料。那麼，在第一個事務中的兩次讀資料之間，由於第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的。這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為是不可重複讀。例如：　　在事務A中，讀取到張三的工資為5000，操作沒有完成，事務還沒提交。　　與此同時，　　事務B把張三的工資改為8000，並提交了事務。　　隨後，　　在事務A中，再次讀取張三的工資，此時工資變為8000。在一個事務中前後兩次讀取的結果並不致，導致了不可重複讀。4）第二類丟失更新：不可重複讀的特例。有兩個併發事務同時讀取同一行資料，然後其中一個對它進行修改提交，而另一個也進行了修改提交。這就會造成第一次寫操作失效。

例如：

在事務A中，讀取到張三的存款為5000，操作沒有完成，事務還沒提交。　　與此同時，　　事務B，儲存1000，把張三的存款改為6000，並提交了事務。　　隨後，　　在事務A中，儲存500，把張三的存款改為5500，並提交了事務，這樣事務A的更新覆蓋了事務B的更新。

5）幻讀：是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。例如：　　目前工資為5000的員工有10人，事務A讀取所有工資為5000的人數為10人。　　此時，　　事務B插入一條工資也為5000的記錄。　　這是，事務A再次讀取工資為5000的員工，記錄為11人。此時產生了幻讀。提醒：不可重複讀的重點是修改，同樣的條件，你讀取過的資料，再次讀取出來發現值不一樣了幻讀的重點在於新增或者刪除，同樣的條件，第 1 次和第 2 次讀出來的記錄數不一樣

事務隔離級別

　　在上面講到的併發事務處理帶來的問題中，“更新丟失”通常是應該完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。

“髒讀”、“不可重複讀”和“幻讀”，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上可分為以下兩種。

　　一種是在讀取資料前，對其加鎖，阻止其他事務對資料進行修改。

　 　另一種是不用加任何鎖，透過一定機制生成一個數據請求時間點的一致性資料快照（Snapshot)，並用這個快照來提供一定級別（語句級或事務級）的一 致 性讀取。從使用者的角度來看，好像是資料庫可以提供同一資料的多個版本，因此，這種技術叫做資料多版本併發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。

　　資料庫的事務隔離越嚴格，併發副作用越 小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上 “序列化”進行，這顯然與“併發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重複讀”和“幻讀”並不敏 感，可能更關心資料併發訪問的能力。

為了解決“隔離”與“併發”的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己的業務邏輯要求，透過選擇不同的隔離級別來平衡 “隔離”與“併發”的矛盾。表20-5很好地概括了這4個隔離級別的特性。

表20-5 4種隔離級別比較

讀資料一致性及允許的併發副作用

隔離級別

讀資料一致性

髒讀

不可重複讀

幻讀

未提交讀（Read uncommitted）

最低級別，只能保證不讀取物理上損壞的資料

是

是

是

已提交度（Read committed）

語句級

否

是

是

可重複讀（Repeatable read）

事務級

否

否

是

可序列化（Serializable）

最高級別，事務級

否

否

否

　　最後要說明的是：各具體資料庫並不一定完全實現了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標準隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支援上述ISO/ANSI SQL92定義的4個隔離級別外，還支援一個叫做“快照”的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。MySQL 支援全部4個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級別下是採用MVCC一致性讀，但某些情況下又不是，這些內容在後面的章節中將會做進 一步介紹。

mysql預設的事務處理級別是"REPEATABLE-READ",也就是可重複讀

如果發現鎖爭用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以透過設定InnoDB Monitors來進一步觀察發生鎖衝突的表、資料行等，並分析鎖爭用的原因。

可以用下面的語句來進行檢視：

　　在SHOW INNODB STATUS的顯示內容中，會有詳細的當前鎖等待的資訊，包括表名、鎖型別、鎖定記錄的情況等，便於進行進一步的分析和問題的確定。開啟監視器以後，預設 情況下每15秒會向日志中記錄監控的內容，如果長時間開啟會導致.err檔案變得非常的巨大，所以使用者在確認問題原因之後，要記得刪除監控表以關閉監視 器，或者透過使用"--console"選項來啟動伺服器以關閉寫日誌檔案。

1.行鎖和表鎖

在mysql 的 InnoDB引擎支援行鎖，與Oracle不同，mysql的行鎖是透過索引載入的，即是行鎖是加在索引響應的行上的，要是對應的SQL語句沒有走索引，則會全表掃描，

行鎖則無法實現，取而代之的是表鎖。

表鎖：不會出現死鎖，發生鎖衝突機率高，併發低。

行鎖：會出現死鎖，發生鎖衝突機率低，併發高。

鎖衝突：例如說事務A將某幾行上鎖後，事務B又對其上鎖，鎖不能共存否則會出現鎖衝突。（但是共享鎖可以共存，共享鎖和排它鎖不能共存，排它鎖和排他鎖也不可以）

死鎖：例如說兩個事務，事務A鎖住了1~5行，同時事務B鎖住了6~10行，此時事務A請求鎖住6~10行，就會阻塞直到事務B施放6~10行的鎖，而隨後事務B又請求鎖住1~5行，事務B也阻塞直到事務A釋放1~5行的鎖。死鎖發生時，會產生Deadlock錯誤。

鎖是對錶操作的，所以自然鎖住全表的表鎖就不會出現死鎖。

2.行鎖的型別

行鎖分 共享鎖 和 排它鎖。

共享鎖又稱：讀鎖。當一個事務對某幾行上讀鎖時，允許其他事務對這幾行進行讀操作，但不允許其進行寫操作，也不允許其他事務給這幾行上排它鎖，但允許上讀鎖。

排它鎖又稱：寫鎖。當一個事務對某幾個上寫鎖時，不允許其他事務寫，但允許讀。更不允許其他事務給這幾行上任何鎖。包括寫鎖。

上共享鎖的寫法：lock in share mode

例如： select math from zje where math>60 lock in share mode；

上排它鎖的寫法：for update

例如：select math from zje where math >60 for update；

3.行鎖的實現

注意幾點：

1.行鎖必須有索引才能實現，否則會自動鎖全表，那麼就不是行鎖了。

2.兩個事務不能鎖同一個索引，例如：

3.insert ，delete ， update在事務中都會自動預設加上排它鎖。

實現：

會話1：begin；select math from zje where math>60 for update；會話2：begin；update zje set math=99 where math=68；阻塞...........

會話相當與使用者

如上，會話1先把zje表中math>60的行上排它鎖。然後會話2試圖把math=68的行進行修改，math=68處於math>60中，所以是已經被鎖的，會話2進行操作時，

就會阻塞，等待會話1把鎖釋放。當commit時或者程式結束時，會釋放鎖。

行級鎖定不是MySQL自己實現的鎖定方式，而是由其他儲存引擎自己所實現的，如廣為大家所知的InnoDB儲存引擎，以及MySQL的分散式儲存引擎NDBCluster等都是實現了行級鎖定。考慮到行級鎖定君由各個儲存引擎自行實現，而且具體實現也各有差別，而InnoDB是目前事務型儲存引擎中使用最為廣泛的儲存引擎，所以這裡我們就主要分析一下InnoDB的鎖定特性。1.InnoDB鎖定模式及實現機制考慮到行級鎖定君由各個儲存引擎自行實現，而且具體實現也各有差別，而InnoDB是目前事務型儲存引擎中使用最為廣泛的儲存引擎，所以這裡我們就主要分析一下InnoDB的鎖定特性。總的來說，InnoDB的鎖定機制和Oracle資料庫有不少相似之處。InnoDB的行級鎖定同樣分為兩種型別，共享鎖和排他鎖，而在鎖定機制的實現過程中為了讓行級鎖定和表級鎖定共存，InnoDB也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。當一個事務需要給自己需要的某個資源加鎖的時候，如果遇到一個共享鎖正鎖定著自己需要的資源的時候，自己可以再加一個共享鎖，不過不能加排他鎖。但是，如果遇到自己需要鎖定的資源已經被一個排他鎖佔有之後，則只能等待該鎖定釋放資源之後自己才能獲取鎖定資源並新增自己的鎖定。而意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖佔用的時候，該事務可以需要鎖定行的表上面新增一個合適的意向鎖。如果自己需要一個共享鎖，那麼就在表上面新增一個意向共享鎖。而如果自己需要的是某行（或者某些行）上面新增一個排他鎖的話，則先在表上面新增一個意向排他鎖。意向共享鎖可以同時並存多個，但是意向排他鎖同時只能有一個存在。所以，可以說InnoDB的鎖定模式實際上可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX），我們可以透過以下表格來總結上面這四種所的共存邏輯關係：

如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。意向鎖是InnoDB自動加的，不需使用者干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖（X)；對於普通SELECT語句，InnoDB不會加任何鎖；