頭圖 | CSDN 下載自視覺中國
作者 | sowhat1412 責編 | XXX
話不多說，直接上乾貨。
SQL 語句執行流程
MySQL 大體上可分為 Server 層和存儲引擎層兩部分。
Server 層：
連接器：TCP 握手後伺服器來驗證登陸用戶身份，A 用戶創建連接後，管理員對 A 用戶權限修改了也不會影響到已經創建的連結權限，必須重新登錄。查詢緩存：查詢後的結果存儲位置，MySQL8.0 版本以後已經取消，因為查詢緩存失效太頻繁，得不償失。分析器：根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。優化器：多種執行策略可實現目標，系統自動選擇最優進行執行。執行器：判斷是否有權限，將最終任務提交到存儲引擎。存儲引擎層
負責數據的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎(經常用的也是這個)。
SQL執行順序
BinLog、RedoLog、UndoLog
2.1 BinLog
BinLog 是記錄所有資料庫表結構變更（例如 create、alter table）以及表數據修改（insert、update、delete）的二進位日誌，主從資料庫同步用到的都是 BinLog 文件。BinLog 日誌文件有三種模式。
STATEMENT 模式
內容：binlog 只會記錄可能引起數據變更的 sql 語句
優勢：該模式下，因為沒有記錄實際的數據，所以日誌量和 IO 都消耗很低，性能是最優的
劣勢：但有些操作並不是確定的，比如 uuid() 函數會隨機產生唯一標識，當依賴 binlog 回放時，該操作生成的數據與原數據必然是不同的，此時可能造成無法預料的後果。
ROW 模式
內容：在該模式下，binlog 會記錄每次操作的源數據與修改後的目標數據，StreamSets 就要求該模式。
優勢：可以絕對精準的還原，從而保證了數據的安全與可靠，並且複製和數據恢復過程可以是並發進行的
劣勢：缺點在於 binlog 體積會非常大，同時，對於修改記錄多、欄位長度大的操作來說，記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數據。
MIXED 模式
內容：是對上述 STATEMENT 跟 ROW 兩種模式的混合使用。
細節：對於絕大部分操作，都使用 STATEMENT 來進行 binlog 的記錄，只有以下操作使用 ROW 來實現：表的存儲引擎為 NDB，使用了uuid() 等不確定函數，使用了 insert delay 語句，使用了臨時表。
主從同步流程：
主節點必須啟用二進位日誌，記錄任何修改了資料庫數據的事件。從節點開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進位日誌文件中的事件 。主節點啟動一個線程（dump Thread），檢查自己二進位日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具體哪一個二進位日誌文件內部的哪一個位置（主節點中的二進位文件會有多個）。從節點啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。mysql 默認的複製方式是異步的，並且複製的時候是有並行複製能力的。主庫把日誌發送給從庫後不管了，這樣會產生一個問題就是：假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。
由此產生兩個概念：全同步複製和半同步複製。
全同步複製
主庫寫入binlog後強制同步日誌到從庫，所有的從庫都執行完成後才返回給客戶端，但是很顯然這個方式的話性能會受到嚴重影響。
半同步複製
半同步複製的邏輯是這樣，從庫寫入日誌成功後返回ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成。
還可以延伸到由於主從配置不一樣、主庫大事務、從庫壓力過大、網絡震蕩等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備情況下如何避免主備循環複製？被刪庫跑路了如何正確恢復？(⊙o⊙)… 感覺越來越扯到 DBA 的活兒上去了。
2.2 RedoLog
可以先通過下面 demo 理解：
飯點記帳可以把帳單寫在帳本上也可以寫在粉板上。有人賒帳或者還帳的話，一般有兩種做法：
直接把帳本翻出來，把這次賒的帳加上去或者扣除掉。先在粉板上記下這次的帳，等打烊以後再把帳本翻出來核算。生意忙時選後者，因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒帳總額信息，找到之後再拿出算盤計算，最後再將結果寫回到帳本上。
同樣，在 MySQL 中如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程 IO 成本、查找成本都很高。而粉板和帳本配合的整個過程就是 MySQL 用到的是 Write-Ahead Logging 技術，它的關鍵點就是先寫日誌，再寫磁碟。此時帳本 = BinLog，粉板 = RedoLog。
1、 記錄更新時，InnoDB 引擎就會先把記錄寫到 RedoLog（粉板）裡面，並更新內存。同時，InnoDB 引擎會在空閒時將這個操作記錄更新到磁碟裡面。2、 如果更新太多 RedoLog 處理不了的時候，需先將 RedoLog 部分數據寫到磁碟，然後擦除 RedoLog 部分數據。RedoLog 類似轉盤。
RedoLog 有 write pos 跟 checkpoint
write pos ：是當前記錄的位置，一邊寫一邊後移，寫到第 3 號文件末尾後就回到 0 號文件開頭。
check point：是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。
write pos 和 check point 之間的是粉板上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示粉板滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。
有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe。
redolog 兩階段提交：為了讓 binlog 跟 redolog 兩份日誌之間的邏輯一致。提交流程大致如下：prepare 階段 -->寫 binlog -->commit
當在 2 之前崩潰時，重啟恢復後發現沒有 commit，回滾。備份恢復：沒有binlog 。一致當在 3 之前崩潰時，重啟恢復發現雖沒有 commit，但滿足 prepare 和binlog 完整，所以重啟後會自動 commit。備份：有 binlog。一致binlog 跟 redolog 區別：
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。redo log 是物理日誌，記錄的是在某個數據頁上做了什麼修改；binlog 是邏輯日誌，記錄的是這個語句的原始邏輯，比如給 ID=2 這一行的 c 欄位加1。redo log 是循環寫的，空間固定會用完；binlog 是可以追加寫入的。追加寫是指 binlog 文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。2.3 UndoLog
UndoLog 一般是邏輯日誌，主要分為兩種：
insert undo log：
代表事務在 insert 新記錄時產生的 undo log，只在事務回滾時需要，並且在事務提交後可以被立即丟棄
update undo log
事務在進行 update 或 delete 時產生的 undo log；不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該日誌時，對應的日誌才會被 purge 線程統一清除
MySQL 中的索引
索引的常見模型有哈希表、有序數組和搜索樹。
哈希表：一種以 KV 存儲數據的結構，只適合等值查詢，不適合範圍查詢。
有序數組：只適用於靜態存儲引擎，涉及到插入的時候比較麻煩。可以參考 Java 中的 ArrayList。
搜索樹：按照數據結構中的二叉樹來存儲數據，不過此時是 N 叉樹（B+樹）。廣泛應用在存儲引擎層中。
B+樹比 B 樹優勢在於：
B+ 樹非葉子節點存儲的只是索引，可以存儲的更多。B+樹比 B 樹更加矮胖，IO 次數更少。B+ 樹葉子節點前後管理，更加方便範圍查詢。同時結果都在葉子節點，查詢效率穩定。B+樹中更有利於對數據掃描，可以避免 B 樹的回溯掃描。索引的優點：
唯一索引可以保證每一行數據的唯一性 ；提高查詢速度 ；加速表與表的連接 ；顯著的減少查詢中分組和排序的時間；通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。索引的缺點：
創建跟維護都需要耗時 ；創建索引時，需要對表加鎖，在鎖表的同時，可能會影響到其他的數據操作 ；索引需要磁碟的空間進行存儲，磁碟佔用也很快；當對表中的數據進行 CRUD 的時，也會觸發索引的維護，而維護索引需要時間，可能會降低數據操作性能。索引設計的原則不應該：
索引不是越多越好。索引太多，維護索引需要時間跟空間；頻繁更新的數據，不宜建索引；數據量小的表沒必要建立索引。應該：
重複率小的列建議生成索引。因為重複數據少，索引樹查詢更有效率，等價基數越大越好；數據具有唯一性，建議生成唯一性索引。在資料庫的層面，保證數據正確性 ；頻繁 group by、order by 的列建議生成索引。可以大幅提高分組和排序效率 ；經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快。索引失效的場景：
模糊搜索：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。隱式類型轉換：比如 select * from t where name = xxx , name 是字符串類型，但是沒有加引號，所以是由 MySQL 隱式轉換的，所以會讓索引失效3、當語句中帶有 or的時候：比如 select * from t where name=『sw』 or age=14不符合聯合索引的最左前綴匹配：(A,B,C)的聯合索引，你只 where 了 C 或 B 或只有 B,C關於索引的知識點：
主鍵索引：主鍵索引的葉子節點存的是整行數據信息。在 InnoDB 裡，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵衝突、事務回滾等都可能導致不連續。
唯一索引：以唯一列生成的索引，該列不允許有重複值，但允許有空值(NULL)
普通索引跟唯一索引查詢性能：InnoDB 的數據是按數據頁為單位來讀寫的，默認每頁 16KB，因此這兩種索引查詢數據性能差別微乎其微。
change buffer：普通索引用在更新過程的加速，更新的欄位如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數據讀入內存來確保不違背唯一性，所以儘量用普通索引。
非主鍵索引：非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）
回表：先通過資料庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。
覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為覆蓋索引。
聯合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯合 16 個。
最左前綴原則：對多個欄位同時建立的組合索引(有順序，ABC，ACB 是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc 三個索引。另外組合索引實際還是一個索引，並非真的創建了多個索引，只是產生的效果等價於產生多個索引。
索引下推：MySQL 5.6 引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。
索引維護：B+樹為了維護索引有序性涉及到頁分裂跟頁合併。增刪數據時需考慮頁空間利用率。
自增主鍵：一般會建立與業務無關的自增主鍵，不會觸發葉子節點分裂。
延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵，再根據主鍵關聯原表獲得需要的數據。
InnoDB 存儲：* .frm 文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。*.ibd 文件則是該表的索引，數據存儲文件，既該表的所有索引樹，所有行記錄數據都存儲在該文件中。
MyISAM 存儲：* .frm 文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。* .MYD 文件是 MyISAM 存儲引擎表的所有行數據的文件。* .MYI 文件存放的是 MyISAM 存儲引擎表的索引相關數據的文件。MyISAM 引擎下，表數據和表索引數據是分開存儲的。
MyISAM 查詢：在 MyISAM 下，主鍵索引和輔助鍵索引都屬於非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結點得到的都是目的數據的地址，還需要通過該地址，才能在數據文件中找到目的數據。
PS：InnoDB 支持聚簇索引，MyISAM 不支持聚簇索引。
SQL 事務隔離級別
4.1 ACID 的四個特性
原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要麼都成功，要麼都不成功；一致性（Consistency）：理解成一串對數據進行操作的程序執行下來，不會對數據產生不好的影響，比如憑空產生，或消失；隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不幹擾，即使是並發事務的情況下，他們只是兩個並發執行沒有交集，互不影響的東西；當然實現中，也不一定需要這麼完整隔離性，即不一定需要這麼的互不幹擾，有時候還是允許有部分幹擾的。所以 MySQL 可以支持 4 種事務隔離性；持久性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中。PS：ACID 中 C 與 CAP 定理中 C 的區別
ACID 的 C 著重強調單資料庫事務操作時，要保證數據的完整和正確性，數據不會憑空消失跟增加。CAP理論中的 C 指的是對一個數據多個備份的讀寫一致性
4.2 事務操作可能會出現的數據問題
髒讀(dirty read)：B 事務更改數據還未提交，A 事務已經看到並且用了。B 事務如果回滾，則 A 事務做錯了；不可重複讀(non-repeatable read)：不可重複讀的重點是修改: 同樣的條件, 你讀取過的數據, 再次讀取出來發現值不一樣了，只需要鎖住滿足條件的記錄 ；幻讀(phantom read)：事務 A 先修改了某個表的所有紀錄的狀態欄位為已處理，未提交；事務 B 也在此時新增了一條未處理的記錄，並提交了；事務 A 隨後查詢記錄，卻發現有一條記錄是未處理的造成幻讀現象，幻讀僅專指新插入的行。幻讀會造成語義上的問題跟數據一致性問題；在可重複讀 RR 隔離級別下，普通查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在當前讀下才會出現。要用間隙鎖解決此問題。在說隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL 標準的事務隔離級別由低到高如下：
上圖從上到下的模式會導致系統的並行性能依次降低，安全性依次提高。
讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。
讀已提交(Oracle 默認)：別人改數據的事務已經提交，我在我的事務中才能讀到。
可重複讀(MySQL 默認)：別人改數據的事務已經提交，我在我的事務中也不去讀，以此保證重複讀一致性。
串行：我的事務尚未提交，別人就別想改數據。
標準跟實現：上面都是關於事務的標準，但是每一種資料庫都有不同的實現，比如 MySQL InnDB 默認為 RR 級別，但是不會出現幻讀。因為當事務 A 更新了所有記錄的某個欄位，此時事務 A 會獲得對這個表的表鎖，因為事務 A 還沒有提交，所以事務 A 獲得的鎖沒有釋放，此時事務 B 在該表插入新記錄，會因為無法獲得該表的鎖，則導致插入操作被阻塞。只有事務 A 提交了事務後，釋放了鎖，事務 B 才能進行接下去的操作。所以可以說，MySQL 的 RR 級別的隔離是已經實現解決了髒讀，不可重複讀和幻讀的。
MySQL 中的鎖
無論是 Java 的並發編程還是資料庫的並發操作都會涉及到鎖，研發人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設計思想。
悲觀鎖：
優點：適合在寫多讀少的並發環境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數據的安全性
缺點：加鎖會增加系統開銷，雖然能保證數據的安全，但數據處理吞吐量低，不適合在讀書寫少的場合下使用
樂觀鎖：
優點：在讀多寫少的並發場景下，可以避免資料庫加鎖的開銷，提高 DAO 層的響應性能，很多情況下 ORM 工具都有帶有樂觀鎖的實現，所以這些方法不一定需要我們人為的去實現。
缺點：在寫多讀少的並發場景下，即在寫操作競爭激烈的情況下，會導致 CAS 多次重試，衝突頻率過高，導致開銷比悲觀鎖更高。
實現：資料庫層面的樂觀鎖其實跟 CAS 思想類似， 通數據版本號或者時間戳也可以實現。
資料庫並發場景主要有三種：
讀-讀：不存在任何問題，也不需要並發控制；
讀-寫：有隔離性問題，可能遇到髒讀，幻讀，不可重複讀；
寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失。
兩類更新丟失問題：
第一類更新丟失：事務 A 的事務回滾覆蓋了事務 B 已提交的結果
第二類更新丟失：事務 A 的提交覆蓋了事務 B 已提交的結果
為了合理貫徹落實鎖的思想，MySQL 中引入了雜七雜八的各種鎖：
鎖分類
MySQL 支持三種層級的鎖定，分別為：
表級鎖定：MySQL 中鎖定粒度最大的一種鎖，最常使用的 MYISAM 與INNODB 都支持表級鎖定。
頁級鎖定：是 MySQL 中鎖定粒度介於行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
行級鎖定：Mysql 中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應的行對其上鎖，這樣的代價其實是比較高的，所以表鎖和行鎖各有所長。
MyISAM 中的鎖
雖然 MySQL 支持表、頁、行三級鎖定，但 MyISAM 存儲引擎只支持表鎖。所以 MyISAM 的加鎖相對比較開銷低，但數據操作的並發性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫並發從 MyISAM 所支持的鎖中也可以看出，MyISAM 是一個支持讀讀並發，但不支持通用讀寫並發，寫寫並發的資料庫引擎，所以它更適合用於讀多寫少的應用場合，一般工程中也用的較少。InnoDB 中的鎖
該模式下支持的鎖實在是太多了，具體如下：
共享鎖和排他鎖(Shared and Exclusive Locks)、意向鎖(Intention Locks)、記錄鎖(Record Locks)、間隙鎖(Gap Locks)、臨鍵鎖(Next-Key Locks)、插入意向鎖(Insert Intention Locks)、主鍵自增鎖(AUTO-INC Locks)、空間索引斷言鎖(Predicate Locks for Spatial Indexes)。
舉個慄子，比如行鎖裡的共享鎖跟排它鎖：lock in share modle 共享讀鎖：
為了確保自己查到的數據沒有被其他的事務正在修改，也就是說，確保查到的數據是最新的數據，並且不允許其他人來修改數據。但是自己不一定能夠修改數據，因為有可能其他的事務也對這些數據使用了 in share mode 的方式上了 S 鎖。如果不及時的 commit 或者 rollback 也可能會造成大量的事務等待。
for update 排它寫鎖:
為了讓自己查到的數據確保是最新數據，並且查到後的數據只允許自己來修改的時候，需要用到 for update。相當於一個 update 語句。在業務繁忙的情況下，如果事務沒有及時的 commit 或者 rollback 可能會造成其他事務長時間的等待，從而影響資料庫的並發使用效率。
Gap Lock 間隙鎖：
行鎖只能鎖住行，如果在記錄之間的間隙插入數據就無法解決了，因此MySQL 引入了間隙鎖(Gap Lock)。間隙鎖是左右開區間。間隙鎖之間不會衝突。間隙鎖和行鎖合稱 NextKeyLock，每個 NextKeyLock 是前開後閉區間。間隙鎖加鎖原則：
加鎖的基本單位是 NextKeyLock，是前開後閉區間。查找過程中訪問到的對象才會加鎖。索引上的等值查詢，給唯一索引加鎖的時候，NextKeyLock 退化為行鎖。索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，NextKeyLock 退化為間隙鎖。唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。
MVCC
MVCC：全稱 Multi-Version Concurrency Control，即多版本並發控制。MVCC 是一種並發控制的理念，維持一個數據的多個版本，使得讀寫操作沒有衝突。
MVCC 在 MySQL InnoDB 中實現目的主要是為了提高資料庫並發性能，用更好的方式去處理讀-寫衝突，做到即使有讀寫衝突時，也能做到不加鎖，非阻塞並發讀。
MySQL InnoDB 下的當前讀和快照讀
當前讀：像 select lock in share mode(共享鎖)、select for update 、update、insert、delete(排他鎖)這些操作都是一種當前讀，就是它讀取的是記錄的最新版本，讀取時還要保證其他並發事務不能修改當前記錄，會對讀取的記錄進行加鎖。當前讀可以認為是悲觀鎖的具體功能實現
快照讀：
不加鎖的 select 就是快照讀，即不加鎖的非阻塞讀；快照讀的前提是隔離級別不是串行級別，串行級別下的快照讀會退化成當前讀；之所以出現快照讀的情況，是基於提高並發性能的考慮，快照讀的實現是基於多版本並發控制，即 MVCC。可以認為 MVCC 是行鎖的一個變種，但它在很多情況下，避免了加鎖操作，降低了開銷；既然是基於多版本，即快照讀可能讀到的並不一定是數據的最新版本，而有可能是之前的歷史版本。快照讀就是 MVCC 思想在 MySQL 的具體非阻塞讀功能實現，MVCC 的目的就是為了實現讀-寫衝突不加鎖，提高並發讀寫性能，而這個讀指的就是快照讀。快照讀就是 MySQL 為我們實現 MVCC 理想模型的其中一個具體非阻塞讀功能。因為大佬不滿意只讓資料庫採用悲觀鎖這樣性能不佳的形式去解決讀-寫衝突問題，而提出了 MVCC，所以我們可以形成兩個組合：
MVCC + 悲觀鎖：MVCC 解決讀寫衝突，悲觀鎖解決寫寫衝突MVCC + 樂觀鎖：MVCC 解決讀寫衝突，樂觀鎖解決寫寫衝突
MVCC的實現原理
MVCC 實現原理主要是依賴記錄中的四個隱式欄位、undo 日誌、Consistent Read View 來實現的。
四個隱式欄位：
DB_TRX_ID：6byte，最近修改(修改/插入)事務ID：記錄創建這條記錄/最後一次修改該記錄的事務 ID；DB_ROLL_PTR：7byte，回滾指針，指向這條記錄的上一個版本（存儲於rollback segment 裡）；DB_ROW_ID：6byte，隱含的自增 ID（隱藏主鍵），如果數據表沒有主鍵，InnoDB 會自動以 DB_ROW_ID 產生一個聚簇索引；FLAG：一個刪除 flag 隱藏欄位, 既記錄被更新或刪除並不代表真的刪除，而是刪除 flag 變了。事務對一條記錄的修改，會導致該記錄的 undo log 成為一條記錄版本線性表(鍊表)，undo log 的鏈首就是最新的舊記錄，鏈尾就是最早的舊記錄。
undo 日誌：此知識點上文已經說過了，對 MVCC 有幫助的實質是 update undo log，undo log，實際上就是存在 rollback segment 中舊記錄鏈。
一致讀視圖 Consistent Read View：Read View 是事務進行快照讀操作的時候生產的讀視圖(Read View)，在該事務執行的快照讀的那一刻，會生成資料庫系統當前的一個快照，記錄並維護系統當前活躍事務的 ID(InnoDB) 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向InnoDB 的事務系統申請的，是按申請順序嚴格遞增的)。拿著這個 ID 跟記錄中 ID 對比進行選擇性展示，這裡說下大致的思維。
你可以簡單的理解為 MVCC 為每一行增加了兩個隱藏欄位，兩個欄位分別保存了這個行的當前事務 ID 跟行的刪除事務 ID。
insert 時：InnoDB 為新插入的每一行保存當前系統版本號作為版本號。select時：InnoDB 只會查找版本早於當前事務版本的數據行(也就是行的系統版本號<=事務的系統版本號)，這樣可以確保事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。行的刪除版本要麼未定義，要麼大於當前事務版本號，這可以確保事務讀取到的行在事務開始之前未被刪除。只有以上同時滿足的記錄，才能返回作為查詢結果。delete時：InnoDB 會為刪除的每一行保存當前系統的版本號(事務的ID )作為刪除標識.update時：InnoDB 執行 update，實際上是新插入了一行記錄，並保存其創建時間為當前事務的 ID，同時保存當前事務 ID 到要 update 的行的刪除時間。上面只是一個淺顯的講解 MVCC 選擇標準流程，源碼層面應該是根據低水位跟高水位來截取的。具體實現可自行百度。
重點：
事務中快照讀的結果是非常依賴該事務首次出現快照讀的地方，即某個事務中首次出現快照讀的地方非常關鍵，它有決定該事務後續快照讀結果的能力。在 RC 隔離級別下，是每個快照讀都會生成並獲取最新的 Read View；而在RR 隔離級別下，則是同一個事務中的第一個快照讀才會創建 Read View, 之後的快照讀獲取的都是同一個 Read View。
緩衝池（buffer pool）
應用系統分層架構，為了加速數據訪問，會把最常訪問的數據，放在緩存(cache)裡，避免每次都去訪問資料庫。作業系統，會有緩衝池(buffer pool)機制，避免每次訪問磁碟，以加速數據的訪問。MySQL 作為一個存儲系統，同樣具有緩衝池(buffer pool)機制，以避免每次查詢數據都進行磁碟 IO。
7.1 主要作用：
存在的意義是加速查詢 緩衝池(buffer pool) 是一種常見的降低磁碟訪問 的機制；緩衝池通常以頁(page 16K)為單位緩存數據；緩衝池的常見管理算法是 LRU，memcache，OS，InnoDB 都使用了這種算法；InnoDB 對普通 LRU 進行了優化：將緩衝池分為老生代和新生代，入緩衝池的頁，優先進入老生代，該頁被訪問，才進入新生代，以解決預讀失效的問題頁被訪問。且在老生代停留時間超過配置閾值的，才進入新生代，以解決批量數據訪問，大量熱數據淘汰的問題。7.2 預讀失效：
由於預讀(Read-Ahead)，提前把頁放入了緩衝池，但最終 MySQL 並沒有從頁中讀取數據，稱為預讀失效
7.3 緩衝池汙染：
當某一個 SQL 語句，要批量掃描大量數據時，可能導致把緩衝池的所有頁都替換出去，導致大量熱數據被換出，MySQL 性能急劇下降，這種情況叫緩衝池汙染。
解決辦法：加入老生代停留時間窗口策略後，短時間內被大量加載的頁，並不會立刻插入新生代頭部，而是優先淘汰那些，短期內僅僅訪問了一次的頁。
table 瘦身
空洞：MySQL 執行 delete 命令其實只是把記錄的位置，或者數據頁標記為了可復用，但磁碟文件的大小是不會變的。通過 delete 命令是不能回收表空間的。這些可以復用，而沒有被使用的空間，看起來就像是空洞。插入時候引發分裂同樣會產生空洞。
重建表思路：
新建一個跟 A 表結構相同的表 B；按照主鍵 ID 將 A 數據一行行讀取同步到表 B；用表 B 替換表 A 實現效果上的瘦身。重建表指令：
alter table A engine=InnoDB，慎重用，牛逼的 DBA 都用下面的開源工具。推薦 Github：gh-ost
SQL Joins、統計、 隨機查詢
7種 join 具體如下：
統計：
MyISAM 模式下把一個表的總行數存在了磁碟上，直接拿來用即可 InnoDB 引擎由於 MVCC 的原因，需要把數據讀出來然後累計求和 性能來說，由好到壞：count(欄位) < count(主鍵id) < count(1) ≈ count(*)，儘量用 count(*)。隨機查詢：
mysql> select word from words order byrand() limit 3;
直接使用 order by rand()，explain 這個語句發現需要 Using temporary 和 Using filesort，查詢的執行代價往往是比較大的。所以在設計的時要避開這種寫法。
mysql> selectcount(*) into @C from t;set @Y1 = floor(@C * rand());set @Y2 = floor(@C * rand());set @Y3 = floor(@C * rand());select * fromtlimit @Y1,1; select * fromtlimit @Y2,1;select * fromtlimit @Y3,1;
這樣可以避免臨時表跟排序的產生，最終查詢行數 = C + (Y1+1) + (Y2+1) + (Y3+1)
exist 和 in 對比：
in 查詢時首先查詢子查詢的表，然後將內表和外表做一個笛卡爾積，然後按照條件進行篩選。子查詢使用 exists，會先進行主查詢，將查詢到的每行數據循環帶入子查詢校驗是否存在，過濾出整體的返回數據。兩表大小相當，in 和 exists 差別不大。內表大，用 exists 效率較高；內表小，用 in 效率較高。查詢用 not in 那麼內外表都進行全表掃描，沒有用到索引；而 not extsts 的子查詢依然能用到表上的索引。not exists 都比 not in 要快。
MySQL 優化
SQL 優化主要分 4 個方向：SQL 語句跟索引、表結構、系統配置、硬體。
總優化思路就是最大化利用索引、儘可能避免全表掃描、減少無效數據的查詢：
減少數據訪問：設置合理的欄位類型，啟用壓縮，通過索引訪問等減少磁碟 IO。
返回更少的數據：只返回需要的欄位和數據分頁處理，減少磁碟 IO 及網絡 IO。
減少交互次數：批量 DML 操作，函數存儲等減少數據連接次數。
減少伺服器 CPU 開銷：儘量減少資料庫排序操作以及全表查詢，減少 CPU 內存佔用 。
分表分區：使用表分區，可以增加並行操作，更大限度利用 CPU 資源。
SQL 語句優化大致舉例：
1、合理建立覆蓋索引：可以有效減少回表。2、union，or，in都能命中索引，建議使用 in 3、負向條件(!=、<>、not in、not exists、not like 等) 索引不會使用索引，建議用in。4、在列上進行運算或使用函數會使索引失效，從而進行全表掃描 5、小心隱式類型轉換，原字符串用整型會觸發 CAST 函數導致索引失效。原 int 用字符串則會走索引。6、不建議使用%前綴模糊查詢。7、多表關聯查詢時，小表在前，大表在後。在 MySQL 中，執行 from 後的表關聯查詢是從左往右執行的(Oracle 相反)，第一張表會涉及到全表掃描。8、調整 Where 字句中的連接順序，MySQL 採用從左往右，自上而下的順序解析 where 子句。根據這個原理，應將過濾數據多的條件往前放，最快速度縮小結果集。
SQL調優大致思路：
先用慢查詢日誌定位具體需要優化的 sql；使用 explain 執行計劃查看索引使用情況 ；重點關注(一般情況下根據這 4 列就能找到索引問題)：key(查看有沒有使用索引)、key_len(查看索引使用是否充分)、type(查看索引類型)、Extra(查看附加信息：排序、臨時表、where 條件為 false 等)；根據上 1 步找出的索引問題優化 sql5、再回到第 2 步。
表結構優化：
儘量使用 TINYINT、SMALLINT、MEDIUM_INT 作為整數類型而非 INT，如果非負則加上 UNSIGNED 。VARCHAR 的長度只分配真正需要的空間 。儘量使用 TIMESTAMP 而非 DATETIME 。單表不要有太多欄位，建議在 20 以內。避免使用 NULL 欄位，很難查詢優化且佔用額外索引空間。字符串默認為''。讀寫分離：
只在主伺服器上寫，只在從伺服器上讀。對應到資料庫集群一般都是一主一從、一主多從。業務伺服器把需要寫的操作都寫到主資料庫中，讀的操作都去從庫查詢。主庫會同步數據到從庫保證數據的一致性。一般 讀寫分離 的實現方式有兩種：代碼封裝跟資料庫中間件。
分庫分表：分庫分表分為垂直和水平兩個方式，一般是先垂直後水平。
垂直分庫：將應用分為若干模塊，比如訂單模塊、用戶模塊、商品模塊、支付模塊等等。其實就是微服務的理念。垂直分表：一般將不常用欄位跟數據較大的欄位做拆分。水平分表：根據場景選擇什麼欄位作分表欄位，比如淘寶日訂單 1000 萬，用 userId 作分表欄位，數據查詢支持到最近 6 個月的訂單，超過 6 個月的做歸檔處理，那麼 6 個月的數據量就是 18 億，分 1024 張表，每個表存 200W 數據，hash(userId)%100 找到對應表格。ID生成器：分布式ID 需要跨庫全局唯一方便查詢存儲-檢索數據，確保唯一性跟數字遞增性。目前主要流行的分庫分表工具 就是 Mycat 和 sharding-sphere。
TiDB：開源分布式資料庫，結合了傳統的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持無限的水平擴展，具備強一致性和高可用性。TiDB 的目標是為 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 場景提供一站式的解決方案。TiDB 具備如下核心特點
支持 MySQL 協議（開發接入成本低）。100% 支持事務（數據一致性實現簡單、可靠）。無限水平拓展（不必考慮分庫分表），不停服務。TiDB 支持和 MySQL 的互備。遵循 jdbc 原則，學習成本低，強關係型，強一致性，不用擔心主從配置，不用考慮分庫分表，還可以無縫動態擴展。適合：
原業務的 MySQL 的業務遇到單機容量或者性能瓶頸時，可以考慮使用 TiDB 無縫替換 MySQL。大數據量下，MySQL 複雜查詢很慢。大數據量下，數據增長很快，接近單機處理的極限，不想分庫分表或者使用資料庫中間件等對業務侵入性較大、對業務有約束的 Sharding 方案。大數據量下，有高並發實時寫入、實時查詢、實時統計分析的需求。有分布式事務、多數據中心的數據 100% 強一致性、auto-failover 的高可用的需求。不適合：
單機 MySQL 能滿足的場景也用不到 TiDB。數據條數少於 5000w 的場景下通常用不到 TiDB，TiDB 是為大規模的數據場景設計的。如果你的應用數據量小（所有數據千萬級別行以下），且沒有高可用、強一致性或者多數據中心複製等要求，那麼就不適合使用 TiDB。