mysql中如何把兩個查詢結果列數不同并成一張表

引言

本文整理了MySQL相關的知識，方便以后查閱。

基礎架構

下圖是 MySQL 的一個簡要架構圖，從下圖你可以很清晰的看到用戶的 SQL 語句在 MySQL 內部是如何執行的。先簡單介紹一下下圖涉及的一些組件的基本作用幫助大家理解這幅圖。 - 連接器：身份認證和權限相關(登錄 MySQL 的時候)。 - 查詢緩存: 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本后移除，因為這個功能不太實用）。 - 分析器: 沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。 - 優化器：按照 MySQL 認為最優的方案去執行。 - 執行器: 執行語句，然后從存儲引擎返回數據。

簡單來說 MySQL 主要分為 Server 層和存儲引擎層： - Server 層：主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志模塊 binlog 日志模塊。 - 存儲引擎：主要負責數據的存儲和讀取，采用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當做默認存儲引擎了。

基本組件

連接器

連接器主要和身份認證和權限相關的功能相關，就好比一個級別很高的門衛一樣。

主要負責用戶登錄數據庫，進行用戶的身份認證，包括校驗賬戶密碼，權限等操作，如果用戶賬戶密碼已通過，連接器會到權限表中查詢該用戶的所有權限，之后在這個連接里的權限邏輯判斷都是會依賴此時讀取到的權限數據，也就是說，后續只要這個連接不斷開，即時管理員修改了該用戶的權限，該用戶也是不受影響的。

查詢緩存

查詢緩存主要用來緩存我們所執行的 SELECT 語句以及該語句的結果集。

連接建立后，執行查詢語句的時候，會先查詢緩存，MySQL 會先校驗這個 sql 是否執行過，以 Key-Value 的形式緩存在內存中，Key 是查詢預計，Value 是結果集。如果緩存 key 被命中，就會直接返回給客戶端，如果沒有命中，就會執行后續的操作，完成后也會把結果緩存起來，方便下一次調用。當然在真正執行緩存查詢的時候還是會校驗用戶的權限，是否有該表的查詢條件。

MySQL 查詢不建議使用緩存，因為查詢緩存失效在實際業務場景中可能會非常頻繁，假如你對一個表更新的話，這個表上的所有的查詢緩存都會被清空。對于不經常更新的數據來說，使用緩存還是可以的。

所以，一般在大多數情況下我們都是不推薦去使用查詢緩存的。

MySQL 8.0 版本后刪除了緩存的功能，官方也是認為該功能在實際的應用場景比較少，所以干脆直接刪掉了。

分析器

MySQL 沒有命中緩存，那么就會進入分析器，分析器主要是用來分析 SQL 語句是來干嘛的，分析器也會分為幾步： 1. 第一步，詞法分析，一條 SQL 語句有多個字符串組成，首先要提取關鍵字，比如 select，提出查詢的表，提出字段名，提出查詢條件等等。做完這些操作后，就會進入第二步。 2. 第二步，語法分析，主要就是判斷你輸入的 sql 是否正確，是否符合 MySQL 的語法。

完成這 2 步之后，MySQL 就準備開始執行了，但是如何執行，怎么執行是最好的結果呢？這個時候就需要優化器上場了。

優化器

優化器的作用就是它認為的最優的執行方案去執行（有時候可能也不是最優），比如多個索引的時候該如何選擇索引，多表查詢的時候如何選擇關聯順序等。

可以說，經過了優化器之后可以說這個語句具體該如何執行就已經定下來。

執行器

當選擇了執行方案后，MySQL 就準備開始執行了，首先執行前會校驗該用戶有沒有權限，如果沒有權限，就會返回錯誤信息，如果有權限，就會去調用引擎的接口，返回接口執行的結果。

存儲引擎

存儲引擎就是真正保存數據，索引數據的容器。在MySQL中有兩個主流的存儲引擎，MyISAM和InnoDB。

區別

MyISAM是MySQL的默認數據庫引擎（5.5版之前）。雖然性能極佳，而且提供了大量的特性，包括全文索引、壓縮、空間函數等，但MyISAM不支持事務和行級鎖，而且最大的缺陷就是崩潰后無法安全恢復。不過，5.5版本之后，MySQL引入了InnoDB（事務性數據庫引擎），MySQL 5.5版本后默認的存儲引擎為InnoDB。

大多數時候我們使用的都是 InnoDB 存儲引擎，但是在某些情況下使用 MyISAM 也是合適的比如讀密集的情況下。（如果你不介意 MyISAM 崩潰回復問題的話）。

是否支持行級鎖 : MyISAM 只有表級鎖(table-level locking)，而InnoDB 支持行級鎖(row-level locking)和表級鎖,默認為行級鎖。 是否支持事務和崩潰后的安全恢復： MyISAM 強調的是性能，每次查詢具有原子性,其執行速度比InnoDB類型更快，但是不提供事務支持。但是InnoDB 提供事務支持事務，外部鍵等高級數據庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。 是否支持外鍵： MyISAM不支持，而InnoDB支持。 是否支持MVCC ：僅 InnoDB 支持。應對高并發事務, MVCC比單純的加鎖更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作;MVCC可以使用樂觀(optimistic)鎖和悲觀(pessimistic)鎖來實現;各數據庫中MVCC實現并不統一。

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你并不在乎可擴展能力和并發能力，也不需要事務支持，也不在乎崩潰后的安全恢復問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。

索引

MySQL索引使用的數據結構主要有BTree索引和哈希索引。對于哈希索引來說，底層的數據結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引。

MySQL的BTree索引使用的是B樹中的B+Tree，但對于主要的兩種存儲引擎的實現方式是不同的： - MyISAM: B+Tree葉節點的data域存放的是數據記錄的地址。在索引檢索的時候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其 data 域的值，然后以 data 域的值為地址讀取相應的數據記錄。這被稱為“非聚簇索引”。 - InnoDB: 其數據文件本身就是索引文件。相比MyISAM，索引文件和數據文件是分離的，其表數據文件本身就是按B+Tree組織的一個索引結構，樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。這被稱為“聚簇索引（或聚集索引）”。而其余的索引都作為輔助索引，輔助索引的data域存儲相應記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。在根據主索引搜索時，直接找到key所在的節點即可取出數據；在根據輔助索引查找時，則需要先取出主鍵的值，再走一遍主索引。因此，在設計表的時候，不建議使用過長的字段作為主鍵，也不建議使用非單調的字段作為主鍵，這樣會造成主索引頻繁分裂。

InnoDB數據結構

各個數據頁可以組成一個雙向鏈表而每個數據頁中的記錄又可以組成一個單向鏈表 - 每個數據頁都會為存儲在它里邊兒的記錄生成一個頁目錄，在通過主鍵查找某條記錄的時候可以在頁目錄中使用二分法快速定位到對應的槽，然后再遍歷該槽對應分組中的記錄即可快速找到指定的記錄 - 以其他列(非主鍵)作為搜索條件：只能從最小記錄開始依次遍歷單鏈表中的每條記錄。

所以說，如果我們寫select * from user where id = '8'這樣沒有進行任何優化的sql語句，默認會這樣做： - 定位到記錄所在的頁，需要遍歷雙向鏈表，找到所在的頁 - 從所在的頁內中查找相應的記錄，由于不是根據主鍵查詢，只能遍歷所在頁的單鏈表了

哈希索引

除了B+樹之外，還有一種常見的是哈希索引。

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，只需一次哈希算法即可立刻定位到相應的位置，速度非常快。

本質上就是把鍵值換算成新的哈希值，根據這個哈希值來定位。

看起來哈希索引很強，能快速找到對應的項，但其實哈希索引有好幾個局限(根據他本質的原理可得)： - 哈希索引也沒辦法利用索引完成排序 - 不支持最左匹配原則 - 在有大量重復鍵值情況下，哈希索引的效率也是極低的---->哈希碰撞問題。 - 不支持范圍查詢

最左匹配原則

最左匹配原則： - 索引可以簡單如一個列(a)，也可以復雜如多個列(a, b, c, d)，即聯合索引。 - 如果是聯合索引，那么key也由多個列組成，同時，索引只能用于查找key是否存在（相等），遇到范圍查詢(>、<、between、like左匹配)等就不能進一步匹配了，后續退化為線性查找。 - 因此，列的排列順序決定了可命中索引的列數。

事務

事務最經典也經常被拿出來說例子就是轉賬了。假如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作就是：將小明的余額減少1000元，將小紅的余額增加1000元。萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰，導致小明余額減少而小紅的余額沒有增加，這樣就不對了。事務就是保證這兩個關鍵操作要么都成功，要么都要失敗。

ACID

原子性：事務是最小的執行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用；
一致性：執行事務前后，數據保持一致，多個事務對同一個數據讀取的結果是相同的；
隔離性：并發訪問數據庫時，一個用戶的事務不被其他事務所干擾，各并發事務之間數據庫是獨立的；
持久性：一個事務被提交之后。它對數據庫中數據的改變是持久的，即使數據庫發生故障也不應該對其有任何影響。

事務日志

事務日志可以幫助提高事務的效率。使用事務日志，存儲引擎在修改表的數據時，只需要修改其內存拷貝，再把該修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據持久到磁盤。事務日志采用追加的方式，因此寫日志的操作是磁盤上一小塊區域內的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務日志的方式相對來說要快得多。事務日志持久化后，內存中被修改的數據可以在后臺慢慢地刷回磁盤。目前大多數存儲引擎都是這樣實現的，我們通常稱之為預寫式日志，修改數據要刷寫兩次磁盤。

如果數據的修改已經記錄到事務日志并持久化，但數據本身還沒有寫回磁盤，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這部分修改的數據。

并發問題

在典型的應用程序中，多個事務并發運行，經常會操作相同的數據來完成各自的任務（多個用戶對統一數據進行操作）。并發雖然是必須的，但可能會導致以下的問題。 - 臟讀（Dirty read）: 當一個事務正在訪問數據并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時另外一個事務也訪問了這個數據，然后使用了這個數據。因為這個數據是還沒有提交的數據，那么另外一個事務讀到的這個數據是“臟數據”，依據“臟數據”所做的操作可能是不正確的。 - 丟失修改（Lost to modify）: 指在一個事務讀取一個數據時，另外一個事務也訪問了該數據，那么在第一個事務中修改了這個數據后，第二個事務也修改了這個數據。這樣第一個事務內的修改結果就被丟失，因此稱為丟失修改。例如：事務1讀取某表中的數據A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果A=19，事務1的修改被丟失。 - 不可重復讀（Unrepeatableread）: 指在一個事務內多次讀同一數據。在這個事務還沒有結束時，另一個事務也訪問該數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改導致第一個事務兩次讀取的數據可能不太一樣。這就發生了在一個事務內兩次讀到的數據是不一樣的情況，因此稱為不可重復讀。 - 幻讀（Phantom read）: 幻讀與不可重復讀類似。它發生在一個事務（T1）讀取了幾行數據，接著另一個并發事務（T2）插入了一些數據時。在隨后的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

隔離級別

READ-UNCOMMITTED(讀取未提交)：最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重復讀。
READ-COMMITTED(讀取已提交)：允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生。
REPEATABLE-READ(可重復讀)：對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。
SERIALIZABLE(可串行化)：最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。

| 隔離級別 | 臟讀 | 不可重復讀 | 幻影讀 |

|-------------------------|-----|------------|--------|

| READ-UNCOMMITTED | √ | √ | √ |

| READ-COMMITTED | × | √ | √ |

| REPEATABLE-READ | × | × | √ |

| SERIALIZABLE | × | × | × |

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）。我們可以通過SELECT @@tx_isolation;命令來查看。

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

MVCC

很多應用的一個特點都是讀多寫少的場景，很多數據的讀取次數遠大于修改的次數，而讀取數據間互相排斥顯得不是很必要。所以就使用了一種讀寫鎖的方法，讀鎖和讀鎖之間不互斥，而寫鎖和寫鎖、讀鎖都互斥。這樣就很大提升了系統的并發能力。之后人們發現并發讀還是不夠，又提出了能不能讓讀寫之間也不沖突的方法，就是讀取數據時通過一種類似快照的方式將數據保存下來，這樣讀鎖就和寫鎖不沖突了，不同的事務session會看到自己特定版本的數據。當然快照是一種概念模型，不同的數據庫可能用不同的方式來實現這種功能。

InnoDB中通過UndoLog實現了數據的多版本，而并發控制通過鎖來實現。

Undo Log除了實現MVCC外，還用于事務的回滾。

區分各種log

MySQL Innodb中存在多種日志，除了錯誤日志、查詢日志外，還有很多和數據持久性、一致性有關的日志。

binlog，是mysql服務層產生的日志，常用來進行數據恢復、數據庫復制，常見的mysql主從架構，就是采用slave同步master的binlog實現的, 另外通過解析binlog能夠實現mysql到其他數據源（如ElasticSearch)的數據復制。

redo log記錄了數據操作在物理層面的修改，mysql中使用了大量緩存，緩存存在于內存中，修改操作時會直接修改內存，而不是立刻修改磁盤，當內存和磁盤的數據不一致時，稱內存中的數據為臟頁(dirty page)。為了保證數據的安全性，事務進行中時會不斷的產生redo log，在事務提交時進行一次flush操作，保存到磁盤中, redo log是按照順序寫入的，磁盤的順序讀寫的速度遠大于隨機讀寫。當數據庫或主機失效重啟時，會根據redo log進行數據的恢復，如果redo log中有事務提交，則進行事務提交修改數據。這樣實現了事務的原子性、一致性和持久性。

Undo Log: 除了記錄redo log外，當進行數據修改時還會記錄undo log，undo log用于數據的撤回操作，它記錄了修改的反向操作，比如，插入對應刪除，修改對應修改為原來的數據，通過undo log可以實現事務回滾，并且可以根據undo log回溯到某個特定的版本的數據，實現MVCC。

redo log 和binlog的一致性，為了防止寫完binlog但是redo log的事務還沒提交導致的不一致，innodb 使用了兩階段提交

InnoDB prepare （持有prepare_commit_mutex，寫redo log 和 undo log）
write/sync Binlog （可以同步，也可以異步，只要redo log寫入之后就可以放心提交了，因為即便丟失了bin log也可以通過redo log恢復數據）
InnoDB commit (寫入COMMIT標記后釋放prepare_commit_mutex)

實現

InnoDB行記錄中除了剛才提到的rowid外，還有trx_id和db_roll_ptr, trx_id表示最近修改的事務的id,db_roll_ptr指向undo segment中的undo log。

新增一個事務時事務id會增加，所以trx_id能夠表示事務開始的先后順序。

Undo log分為Insert和Update兩種，delete可以看做是一種特殊的update，即在記錄上修改刪除標記。update undo log記錄了數據之前的數據信息，通過這些信息可以還原到之前版本的狀態。當進行插入操作時，生成的Insert undo log在事務提交后即可刪除，因為其他事務不需要這個undo log。進行刪除修改操作時，會生成對應的undo log，并將當前數據記錄中的db_roll_ptr指向新的undo log

可見性判斷

CREATE TABLE `testunique` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`uid` int(11) DEFAULT NULL,`ukey` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `id_uid` (`uid`),KEY `index_key` (`ukey`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;

隔離級別REPEATABLE READ

只有當session2 commit之后的查詢才能查到session1插入的數據

事務可見性的處理過程:

RR級別下一個事務開始后第一個snapshot read的時候，會將當期活動的事務id記錄下來，記錄到read view中。RC級別則是每次snapshot read都會創建一個新的read view。

假設當前,read view中最大的事務id為tmax, 最小為tmin。則判斷一個數據是否可見以及對應的版本的方法為： 1. 如果該行中的trx_id, 賦值給tid, 如果tid和當前事務id相等或小于tmin，說明是事務內發生的或開啟前的修改，則直接返回該版本數據; 2. 如果trx_id大于tmax, 則查看該版本的db_roll_ptr中的trx_id，賦值給tid并從頭開始判斷。 3. 如果tid小于tmax并且不在read view中，則返回，否則從回滾段中找出undo log的trx_id，賦值給tid從頭判斷。

所以可見性是，只有當第一次讀之前提交的修改和自己的修改可見，其他的均不可見。

Undo log刪除

undo log在沒有活動事務依賴（用于consistent read或回滾)便可以清除，innodb 中存在后臺purge 線程進行后臺輪詢刪除undo log。

Current Read & Snapshot Read

REPEATABLE READ隔離級別下普通的讀操作即select都不加鎖，使用MVCC進行一致性讀取，這種讀取又叫做snapshot read。

在innoDB中，因為MVCC的實現，如果只進行普通讀select * from t where a=1;那么是可以保證不可重復讀和消除幻讀，因為普通讀操作會走snapshot read，innoDB會根據tid過濾掉事務開啟前未提交的事務。

而update, insert, delete, select … for update, select … lock in share mode都會進行加鎖，并且讀取的是當前版本，也就是READ COMMITTED讀的效果。

InnoDB中加鎖的方法是鎖住對應的索引，一個操作進行前會選擇一個索引進行掃描，掃描到一行后加上對應的鎖然后返回給上層然后繼續掃描。InnoDB支持行級鎖(record lock)，上述需要加鎖的操作中，除了select … lock in share mode 是加shared lock（共享鎖或讀鎖）外其他操作都加的是exclusive lock(即排他鎖或寫鎖)。在加行級鎖前，會對表加一個intention lock，即意向鎖，意向鎖是表級鎖，不會和行級鎖沖突，主要用途是表明一個要加行級鎖或正在加鎖的操作。

另外InnoDB中除了record lock外還有一種gap lock，即鎖住兩個記錄間的間隙，防止其他事務插入數據，用于防止幻讀。當要鎖的索引是主鍵索引或唯一索引并且是單數據加鎖時，不需要加gap lock，只通過行鎖即可。當要鎖索引不是唯一索引或者是范圍加鎖時，需要對索引數據和索引前的gap加鎖，這種方式叫做next-key locking。

另外在插入數據時，還需要提前在插入行的前面部分加上insert intention lock, 即插入意向鎖，插入意向鎖之間不會沖突，會和gap鎖沖突導致等待。當插入時遇到duplicated key錯誤時，會在要插入的行上加上share lock。

因為InnoDB的MVCC機制：自己修改過的數據在快照中可見&update操作是基于當前版本而不是快照版本，所以InnoDB的RR隔離級別存更新操作自動降級到了RC級別，即更新是在最新版本的數據上進行，故RR隔離級別下的非READ-ONLY事務，可能存在不可重復讀和幻象的情況。

a事務先select，b事務update或insert確實會加一個gap鎖，但是如果b事務commit，這個gap鎖就會釋放（釋放后a事務可以隨意dml操作）
a事務再select出來的結果在MVCC下還和第一次select一樣，
接著a事務不加條件地update，這個update會作用在所有行上（包括b事務新加的）
a事務再次select就會出現b事務中的修改或者新添加的行，并且這一行數據已經被update修改了

從上面的例子中，我們看出innoDB的非READ-ONLY RR事務，退化成了RC級別。

為了保證InnoDB的RR隔離級別達到ANSI-RR級別，對于單純的SELECT語句，需要顯示for update進行gap鎖，更新語句自動會上gap鎖，防止其它事務的干擾，這樣就能達到可重復讀和消除幻讀的目的。

鎖

MyISAM和InnoDB存儲引擎使用的鎖： - MyISAM采用表級鎖(table-level locking)。 - InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖

表級鎖和行級鎖對比： - 表級鎖： MySQL中鎖定粒度最大的一種鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。其鎖定粒度最大，觸發鎖沖突的概率最高，并發度最低，MyISAM和 InnoDB引擎都支持表級鎖。 - 行級鎖： MySQL中鎖定粒度最小的一種鎖，只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，并發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。

InnoDB存儲引擎的鎖的算法有三種： - Record lock：單個行記錄上的鎖 - Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身 - Next-key lock：record+gap 鎖定一個范圍，包含記錄本身

InnoDB存儲引擎總結

從MySQL5.6開始，InnoDB是MySQL數據庫的默認存儲引擎。它支持事務，支持行鎖和外鍵，通過MVCC來獲得高并發。

MySQL5.6的InnoDB存儲引擎體系結構圖如下:

InnoDB緩沖池

InnoDB緩沖池是內存中用來緩存表數據和索引的一片區域。

當緩沖池大小達到GB級別時，通過設置多個緩沖池實例，可以提高并發處理能力，減少數據庫內部資源競爭。對于存儲到或從緩沖池中讀取的每個頁，都使用哈希函數隨機分配到不同的緩沖池實例中。 - innodb_buffer_pool_size: 設置緩沖池的大小(單位: Byte)，重啟生效。 - innodb_buffer_pool_instances: 設置緩沖池實例的個數，每個緩沖池管理自己的free lists，flush lists,LRU list和其他數據結構，并受到自己的互斥鎖保護。此值默認為1，最大為64。只有當 innodb_buffer_pool_size 超過1G時，此參數才會起作用。重啟生效。

緩沖池的LRU算法

InnoDB以列表的方式管理緩沖池，使用優化后的LRU算法。此算法可以最大限度地減少進入緩沖池并從未被再次訪問的頁的數量，這樣可以確保熱點頁保持在緩沖池中。

當緩沖池的free list沒有可用的空閑頁時，InnoDB會回收LRU列表中最近最少使用的頁，并將新讀取到的頁添加到LRU列表的midpoint位置，稱之為“midpoint insertion strategy”。它將LRU列表視為兩個子列表，midpoint之前的列表稱為new子列表，包含最近經常訪問的頁；midpoint之后的列表稱為old子列表，包含最近不常訪問的頁。

最初，新添加到緩沖池的頁位于old子列表的頭部。當在緩沖池中第一次訪問這些頁時，會將它們移到new子列表的頭部，此時發生的操作稱為page made young。隨著數據庫的運行，緩沖池中沒有被訪問到的頁由于移到LRU列表的尾部而變老，最終會回收LRU列表尾部長時間未被訪問的頁。

可以通過 innodb_old_blocks_pct 參數設置old子列表在LRU列表中所占的比例。默認值為37，對應3/8的位置。取值范圍從5到95。

為什么要將新讀取到的頁放在midpoint位置而不是LRU列表的頭部？若直接將讀取到的頁插入到LRU列表的頭部，當出現全表掃描或索引掃描的時候，需要將大量的新頁讀入到緩沖池中，導致熱點頁從緩沖池刷出，而這些新頁可能僅在這次查詢中用到，并不是熱點數據，這樣就會額外產生大量的磁盤I/O操作，影響效率。為了避免此問題，InnoDB引擎引入了參數：innodb_old_blocks_time，此參數表示第一次讀取old子列表中的頁后，需要等待多少毫秒才會將此頁移到new子列表。默認值為1000。增加此值可以讓更多的頁更快的老化。

Change Buffer

change buffer用來緩存不在緩沖池中的輔助索引頁(非唯一索引)的變更。這些緩存的的變更，可能由INSERT、UPDATE或DELETE操作產生，當讀操作將這些變更的頁從磁盤載入緩沖池時，InnoDB引擎會將change buffer中緩存的變更跟載入的輔助索引頁合并。

不像聚簇索引，輔助索引通常不是唯一的，并且輔助索引的插入順序是相對隨機的。若不用change buffer，那么每有一個頁產生變更，都要進行I/O操作來合并變更。使用change buffer可以先將輔助索引頁的變更緩存起來，當這些變更的頁被其他操作載入緩沖池時再執行merge操作，這樣可以減少大量的隨機I/O。change buffer可能緩存了一個頁內的多條記錄的變更，這樣可以將多次I/O操作減少至一次。

在內存中，change buffer占據緩沖池的一部分。在磁盤上，change buffer是系統表空間的一部分，以便數據庫重啟后緩存的索引變更可以繼續被緩存。

innodb_change_buffering 參數可以配置將哪些操作緩存在change buffer中。可以通過此參數開啟或禁用insert操作，delete操作(當索引記錄初始標記為刪除時)和purge操作(當索引記錄被物理刪除時)。update操作是inset和delete操作的組合。該參數的取值如下： - all: 默認值，包含inserts、deletes和purges - none: 不緩存任何操作 - inserts: 緩存insert操作 - deletes: 緩存標記刪除(delete-marking)操作 - changes: 緩存inserts和deletes - purges: 緩存后臺進程發生的物理刪除操作

自適應哈希索引

自適應哈希索引是InnoDB表通過在內存中構造一個哈希索引來加速查詢的優化技術，此優化只針對使用 '=' 和 'IN' 運算符的查詢。MySQL會監視InnoDB表的索引查找，若能通過構造哈希索引來提高效率，那么InnoDB會自動為經常訪問的輔助索引頁建立哈希索引。

這個哈希索引總是基于輔助索引(B+樹結構)來構造。MySQL通過索引鍵的任意長度的前綴和索引的訪問模式來構造哈希索引。InnoDB只為某些熱點頁構建哈希索引。

可通過 innodb_adaptive_hash_index 參數開啟或禁用此功能，默認是開啟狀態。開啟此功能后， InnoDB會根據需要自動創建這個哈希索引，而不用人為干預創建，這就是叫自適應的原因。此功能并不是在所有情況下都適用，且AHI需要的內存都是從緩沖池申請的，所以此功能的開啟或關閉需要通過測試來具體確定。可以通過 SHOW ENGINE INNODB STATUS 命令查看AHI的使用狀況。

Redo Log

重做日志用來實現事務的持久性。其由兩部分組成：一是內存中的重做日志緩沖(redo log buffer)，其是易失的；二是磁盤上的重做日志文件(redo log file)，其是持久的。

在MySQL數據庫宕機恢復期間會用到重做日志文件，用于更正不完整事務寫入的數據。

InnoDB通過Force Log at Commit機制實現事務的持久性，即在事務提交前，先將事務的重做日志刷新到到重做日志文件。

重做日志在磁盤上由一組文件組成，通常命名為 ib_logfile0 和 ib_logfile1。

MySQL以循環方式將日志寫入重做日志文件。假設現在有兩個重做日志文件：ib_logfile0和ib_logfile1。重做日志先寫入到ib_logfile0，當ib_logfile0寫滿后再寫入ib_logfile1。當ib_logfile1也寫滿后，再往ib_logfile0中寫，而之前的內容會被覆蓋。

innodb_log_file_size 參數用來設置每個重做日志文件的大小(單位：Byte)。innodb_log_files_in_group 參數用來設置重做日志文件組中日志文件的個數。 innodb_log_group_home_dir 參數設置重做日志文件所在的路徑。從MySQL5.6.3開始，重做日志文件總大小的最大值從之前的4GB提升到了512GB。

重做日志(redo log)跟二進制日志(binlog)的區別： - 重做日志在InnoDB存儲引擎層產生；而二進制日志在MySQL數據庫上層產生，不僅僅針對InnoDB引擎，MySQL中的任何存儲引擎對于數據庫的變更都會產生二進制日志。 - 兩者記錄的內容形式不同，二進制日志是一種邏輯日志，其記錄的是對應的SQL語句；重做日志是物理格式日志，其記錄的是每個頁的變更。 - 兩種日志記錄寫入磁盤的時間點不同，二進制日志只在事務提交完成后進行寫入；而重做日志在事務進行中被不斷的寫入，也就是日志并不是隨事務提交的順序寫入的。

重做日志緩沖是一塊內存區域，用來緩存即將被寫入到重做日志文件的數據。InnoDB引擎首先將重做日志信息緩存到重做日志緩沖，然后定期將其刷新到磁盤上的重做日志文件。

如下三種情況會將重做日志緩沖中的數據刷新到磁盤的重做日志文件中：

Master Thread會定期將重做日志緩沖刷新到重做日志文件，即使這個事務還沒有提交。 - 事務提交時 - 當重做日志緩沖沒有足夠的空間時 - InnoDB通過Force Log at Commit機制實現事務的持久性，即在事務提交前，先將事務的重做日志刷新到到重做日志文件。

innodb_flush_method 定義用于將數據刷新到InnoDB數據文件和日志文件的方法，會影響I/O吞吐量。默認值為NULL，可選項包含：fsync、O_DIRECT和其他。若在Unix-like系統上此參數設置為NULL，那么默認使用fsync。

fsync: InnoDB調用系統的fsync()刷新數據文件和日志文件。

O_DIRECT: InnoDB使用O_DIRECT方式打開數據文件，然后使用fsync()刷新數據文件和日志文件。啟用后將繞過操作系統緩存，直接寫文件。

為確保每次重做日志緩沖都能寫入到磁盤的重做日志文件，在每次將重做日志緩沖寫入重做日志文件后，InnoDB引擎都需要調用一次fsync操作。由于默認情況下 innodb_flush_method 參數未設置為O_DIRECT，因此重做日志緩沖先寫入文件系統緩存。

innodb_log_buffer_size 參數可以設置重做日志緩沖的大小。

innodb_flush_log_at_trx_commit 參數用來控制重做日志緩沖刷新到磁盤的策略。取值范圍如下： - 1: 默認值。表示事務提交時必須調用一次fsync操作。 - 0: 表示事務提交時不進行刷新日志到磁盤。這個操作在master thread中完成，在master thread中每1秒會進行一次刷新日志到磁盤操作。 - 2: 表示事務提交時將重做日志緩沖寫入重做日志文件，但僅寫入文件系統的緩存中，不進行fsync操作。此情況下，若操作系統發生宕機，會丟失未從文件系統緩沖刷新到重做日志文件的那部分日志。

Undo Log

undo log(也稱為rollback segment)用來存儲被事務修改的記錄的副本。

undo日志有兩個作用：一個是實現事務的原子性，即當事務由于意外情況未能成功運行時，可以使事務回滾，從而讓數據恢復到事務開始時的狀態；另一個作用是實現MVCC機制，當用戶讀取一行記錄時，若該記錄已經被其他事務占有，當前事務可以通過undo日志讀取該記錄之前的版本信息，以此實現一致性非鎖定讀。

每個回滾段(rollback segment)記錄了1024個undo段(undo segment)，InnoDB引擎在每個undo段中進行undo頁的申請。

undo log分為insert undo log和update undo log。 - insert undo log指事務在INSERT操作中產生的undo日志。因為INSERT操作的記錄僅對當前事務可見，所以該undo日志在事務提交后可以直接刪除。 - update undo log通常保存的是對DELETE和UPDATE操作產生的undo日志。該undo日志可能需要提供MVCC機制，因此其不能在事務提交后立即刪除。當事務提交后，它會放入回滾段的history鏈表的頭部，等待purge線程進行最后的刪除。

默認情況下，undo日志位于系統表空間(system tablespace)中。從MySQL5.6起，可以通過 innodb_undo_tablespaces 和 innodb_undo_directory 參數將undo日志存放在獨立表空間中。

系統表空間

InnoDB系統表空間包含InnoDB數據字典(InnoDB相關對象的元數據)、雙寫緩沖(doublewrite buffer)、change buffer和undo logs。此外，還包含用戶在系統表空間中創建的表數據和索引數據。由于多個表的數據可以在共同存放在系統表空間中，以此其也稱為共享表空間。

系統表空間可由一個或多個文件組成。默認情況下，在MySQL數據目錄中有一個命名為 ibdata1 的系統表空間文件。innodb_data_file_path 參數可以設置系統表空間文件的大小和數量。

可以通過 innodb_file_per_table 參數啟用獨立表空間。即每創建一個表就會產生一個單獨的 .ibd 文件存放此表的記錄和索引。若未啟用此參數，那么InnoDB引擎創建的表就會存在于系統表空間中。

雙寫緩沖

雙寫緩沖技術是為了解決partial page write問題而開發的。doublewrite buffer是系統表空間上的連續的128個頁(兩個區)，大小為2M。

當發生數據庫宕機時，可能InnoDB存儲引擎正在寫入某個頁到表中，而這個頁只寫了一部分，比如16KB的頁（默認頁大小），只寫了前4KB（因為大部分磁盤頁大小是4KB，它只能保證4KB的原子寫入），之后就發生了宕機，這種情況被稱為部分寫失效（partial page write）。

doublewrite的工作原理是：在將緩沖池中的頁寫入磁盤上對應位置之前，先將緩沖池中的頁copy到內存中的doublewrite buffer，之后順序地將內存中doublewrite buffer中的頁寫入系統表空間中的doublewrite區域，然后立即調用系統fsync函數，同步數據到磁盤文件中，避免緩沖寫帶來的問題。在完成doublewrite頁的寫入之后，再將內存上doublewrite buffer中的頁寫入到自己的表空間文件。

通過雙寫緩沖，數據文件和雙寫緩沖文件中，至少有一份數據是正確無誤的，如果寫磁盤過程發生了崩潰，那么MySQL重啟時可以通過校驗和來確認是否有錯誤數據，如果雙寫緩沖文件錯誤了，就從數據文件中拉取原始數據根據redo log得出正確的目標數據，而如果數據文件錯誤了，則將雙寫緩沖中的數據重新寫入數據文件。

大表優化

當MySQL單表記錄數過大時，數據庫的CRUD性能會明顯下降，一些常見的優化措施如下： 1. 限定數據的范圍 - 務必禁止不帶任何限制數據范圍條件的查詢語句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內 2. 讀/寫分離 - 經典的數據庫拆分方案，主庫負責寫，從庫負責讀 3. 垂直分區 - 根據數據庫里面數據表的相關性進行拆分。例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。 - 簡單來說垂直拆分是指數據表列的拆分，把一張列比較多的表拆分為多張表。 - 垂直拆分的優點：可以使得列數據變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分區可以簡化表的結構，易于維護。 - 垂直拆分的缺點：主鍵會出現冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區會讓事務變得更加復雜。 4. 水平分區 - 保持數據表結構不變，通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中，達到了分布式的目的。水平拆分可以支撐非常大的數據量。 - 水平拆分是指數據表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存放。舉個例子：我們可以將用戶信息表拆分成多個用戶信息表，這樣就可以避免單一表數據量過大對性能造成影響。 - 水平拆分可以支持非常大的數據量。需要注意的一點是：分表僅僅是解決了單一表數據過大的問題，但由于表的數據還是在同一臺機器上，其實對于提升MySQL并發能力沒有什么意義，所以水平拆分最好分庫。 - 水平拆分能夠支持非常大的數據量存儲，應用端改造也少，但分片事務難以解決，跨節點Join性能較差，邏輯復雜。盡量不要對數據進行分片，因為拆分會帶來邏輯、部署、運維的各種復雜度，一般的數據表在優化得當的情況下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片，盡量選擇客戶端分片架構，這樣可以減少一次和中間件的網絡I/O。

參考內容

[1]https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/一條sql語句在mysql中如何執行的.md

[2]https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL.md

[3]https://juejin.im/post/5b55b842f265da0f9e589e79

[4]https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/

[5]https://my.oschina.net/u/553773/blog/792144

本文作者： 貝克街的流浪貓
本文鏈接：https://www.beikejiedeliulangmao.top/JNI-%E8%B0%83%E8%AF%95%E6%8A%80%E6%9C%AF/
版權聲明： 本博客所有文章除特別聲明外，均采用 BY-NC-SA 許可協議。轉載請注明出處！
創作聲明： 本文基于上述所有參考內容進行創作，其中可能涉及復制、修改或者轉換，圖片均來自網絡，如有侵權請聯系我，我會第一時間進行刪除。