1、SQL語句執行流程

MySQL大體上可分為Server層和儲存引擎層兩部分。

Server層：

聯結器：TCP握手後伺服器來驗證登陸使用者身份，A使用者建立連線後，管理員對A使用者許可權修改了也不會影響到已經建立的連結許可權，必須重新登陸。
查詢快取：查詢後的結果儲存位置，MySQL8.0版本以後已經取消，因為查詢快取失效太頻繁，得不償失。
分析器：根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。
最佳化器：多種執行策略可實現目標，系統自動選擇最優進行執行。
執行器：判斷是否有許可權，將最終任務提交到儲存引擎。

儲存引擎層

負責資料的儲存和提取。其架構模式是外掛式的，支援InnoDB、MyISAM、Memory等多個儲存引擎。現在最常用的儲存引擎是InnoDB，它從MySQL 5.5.5版本開始成為了預設儲存引擎(經常用的也是這個)。

SQL執行順序

2、BinLog、RedoLog、UndoLog

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 語句，使用了臨時表

順豐快遞：請簽收MySQL靈魂十連 主從同步流程：

1、主節點必須啟用二進位制日誌，記錄任何修改了資料庫資料的事件。
2、從節點開啟一個執行緒（I/O Thread)把自己扮演成 mysql 的客戶端，透過 mysql 協議，請求主節點的二進位制日誌檔案中的事件。
3、主節點啟動一個執行緒（dump Thread），檢查自己二進位制日誌中的事件，跟對方請求的位置對比，如果不帶請求位置引數，則主節點就會從第一個日誌檔案中的第一個事件一個一個傳送給從節點。
4、從節點接收到主節點傳送過來的資料把它放置到中繼日誌（Relay log）檔案中。並記錄該次請求到主節點的具體哪一個二進位制日誌檔案內部的哪一個位置（主節點中的二進位制檔案會有多個）。
5、從節點啟動另外一個執行緒（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。

mysql預設的複製方式是非同步的，並且複製的時候是有並行複製能力的。主庫把日誌傳送給從庫後不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。由此產生兩個概念。

全同步複製

主庫寫入binlog後強制同步日誌到從庫，所有的從庫都執行完成後才返回給客戶端，但是很顯然這個方式的話效能會受到嚴重影響。

半同步複製

半同步複製的邏輯是這樣，從庫寫入日誌成功後返回ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成。

還可以延伸到由於主從配置不一樣、主庫大事務、從庫壓力過大、網路震盪等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備情況下如何避免主備迴圈複製？被刪庫跑路瞭如何正確恢復？(⊙o⊙)… 感覺越來越扯到DBA的活兒上去了。順豐快遞：請簽收MySQL靈魂十連

RedoLog

可以先透過下面demo理解：

飯點記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：

1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。
2、先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。

生意忙時選後者，因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒賬總額資訊，找到之後再拿出算盤計算，最後再將結果寫回到賬本上。

同樣在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。順豐快遞：請簽收MySQL靈魂十連 redolog兩階段提交：為了讓binlog跟redolog兩份日誌之間的邏輯一致。提交流程大致如下：

1 prepare階段 --> 2 寫binlog --> 3 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檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

UndoLog

UndoLog 一般是邏輯日誌，主要分為兩種：

insert undo log

代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄

update undo log

事務在進行update或delete時產生的undo log; 不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該日誌時，對應的日誌才會被purge執行緒統一清除

3、MySQL中的索引

索引的常見模型有雜湊表、有序陣列和搜尋樹。

雜湊表：一種以KV儲存資料的結構，只適合等值查詢，不適合範圍查詢。
有序陣列：只適用於靜態儲存引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。
搜尋樹：按照資料結構中的二叉樹來儲存資料，不過此時是N叉樹(B+樹)。廣泛應用在儲存引擎層中。

順豐快遞：請簽收MySQL靈魂十連 B+樹比B樹優勢在於：

B+ 樹非葉子節點儲存的只是索引，可以儲存的更多。B+樹比B樹更加矮胖，IO次數更少。
B+ 樹葉子節點前後管理，更加方便範圍查詢。同時結果都在葉子節點，查詢效率穩定。
B+樹中更有利於對資料掃描，可以避免B樹的回溯掃描。

索引的優點：

1、唯一索引可以保證每一行資料的唯一性
2、提高查詢速度
3、加速表與表的連線
4、顯著的減少查詢中分組和排序的時間
5、透過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。

索引的缺點：

1、建立跟維護都需要耗時
2、建立索引時，需要對錶加鎖，在鎖表的同時，可能會影響到其他的資料操作
3、索引需要磁碟的空間進行儲存，磁碟佔用也很快。
4、當對錶中的資料進行CRUD的時，也會觸發索引的維護，而維護索引需要時間，可能會降低資料操作效能

索引設計的原則不應該：

1、索引不是越多越好。索引太多，維護索引需要時間跟空間。
2、頻繁更新的資料，不宜建索引。
3、資料量小的表沒必要建立索引。

應該：

1、重複率小的列建議生成索引。因為重複資料少，索引樹查詢更有效率，等價基數越大越好。
2、資料具有唯一性，建議生成唯一性索引。在資料庫的層面，保證資料正確性
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率
4、經常用於查詢條件的欄位建議生成索引。透過索引查詢，速度更快

索引失效的場景

1、模糊搜尋：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。
2、隱式型別轉換：比如select * from t where name = xxx , name是字串型別，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效 3、當語句中帶有or的時候：比如select * from t where name=‘sw’ or age=14
4、不符合聯合索引的最左字首匹配：(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不支援聚簇索引

4、SQL事務隔離級別

ACID的四個特性

原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要麼都成功，要麼都不成功
一致性（Consistency）：理解成一串對資料進行操作的程式執行下來，不會對資料產生不好的影響，比如憑空產生，或消失
隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不干擾，即使是併發事務的情況下，他們只是兩個併發執行沒有交集，互不影響的東西；當然實現中，也不一定需要這麼完整隔離性，即不一定需要這麼的互不干擾，有時候還是允許有部分干擾的。所以MySQL可以支援4種事務隔離性
永續性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中

PS：ACID中C與CAP定理中C的區別

ACID的C著重強調單資料庫事務操作時，要保證資料的完整和正確性，資料不會憑空消失跟增加。CAP 理論中的C指的是對一個資料多個備份的讀寫一致性

事務操作可能會出現的資料問題

1、髒讀(dirty read)：B事務更改資料還未提交，A事務已經看到並且用了。B事務如果回滾，則A事務做錯了
2、 不可重複讀(non-repeatable read)：不可重複讀的重點是修改: 同樣的條件, 你讀取過的資料, 再次讀取出來發現值不一樣了，只需要鎖住滿足條件的記錄
3、 幻讀(phantom read)：事務A先修改了某個表的所有紀錄的狀態欄位為已處理，未提交；事務B也在此時新增了一條未處理的記錄，並提交了；事務A隨後查詢記錄，卻發現有一條記錄是未處理的造成幻讀現象，幻讀僅專指新插入的行。幻讀會造成語義上的問題跟資料一致性問題。
4、在可重複讀RR隔離級別下，普通查詢是快照讀，是不會看到別的事務插入的資料的。因此，幻讀在當前讀下才會出現。要用間隙鎖解決此問題。

在說隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別由低到高如下：順豐快遞：請簽收MySQL靈魂十連上圖從上到下的模式會導致系統的並行效能依次降低，安全性依次提高。

讀未提交：別人改資料的事務尚未提交，我在我的事務中也能讀到。
讀已提交(Oracle預設)：別人改資料的事務已經提交，我在我的事務中才能讀到。
可重複讀(MySQL預設)：別人改資料的事務已經提交，我在我的事務中也不去讀，以此保證重複讀一致性。
序列：我的事務尚未提交，別人就別想改資料。

標準跟實現：上面都是關於事務的標準，但是每一種資料庫都有不同的實現，比如MySQL InnDB 預設為RR級別，但是不會出現幻讀。因為當事務A更新了所有記錄的某個欄位，此時事務A會獲得對這個表的表鎖，因為事務A還沒有提交，所以事務A獲得的鎖沒有釋放，此時事務B在該表插入新記錄，會因為無法獲得該表的鎖，則導致插入操作被阻塞。只有事務A提交了事務後，釋放了鎖，事務B才能進行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經實現解決了髒讀，不可重複讀和幻讀的。

5、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間隙鎖：

1、行鎖只能鎖住行，如果在記錄之間的間隙插入資料就無法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區間。間隙鎖之間不會衝突。
2、間隙鎖和行鎖合稱NextKeyLock，每個NextKeyLock是前開後閉區間。

間隙鎖加鎖原則(學完忘那種)：

1、加鎖的基本單位是 NextKeyLock，是前開後閉區間。
2、查詢過程中訪問到的物件才會加鎖。
3、索引上的等值查詢，給唯一索引加鎖的時候，NextKeyLock退化為行鎖。
4、索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，NextKeyLock退化為間隙鎖。
5、唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

6、MVCC

MVCC：

1、全稱Multi-Version Concurrency Control，即多版本併發控制。MVCC是一種併發控制的理念，維持一個資料的多個版本，使得讀寫操作沒有衝突。
2、MVCC在MySQL InnoDB中實現目的主要是為了提高資料庫併發效能，用更好的方式去處理讀-寫衝突，做到即使有讀寫衝突時，也能做到不加鎖，非阻塞併發讀。

MySQL InnoDB下的當前讀和快照讀

當前讀

1、像select lock in share mode(共享鎖)、select for update 、update、insert、delete(排他鎖)這些操作都是一種當前讀，就是它讀取的是記錄的最新版本，讀取時還要保證其他併發事務不能修改當前記錄，會對讀取的記錄進行加鎖。
2、當前讀可以認為是悲觀鎖的具體功能實現

快照讀

1、不加鎖的select就是快照讀，即不加鎖的非阻塞讀；快照讀的前提是隔離級別不是序列級別，序列級別下的快照讀會退化成當前讀；之所以出現快照讀的情況，是基於提高併發效能的考慮，快照讀的實現是基於多版本併發控制，即MVCC，可以認為MVCC是行鎖的一個變種，但它在很多情況下，避免了加鎖操作，降低了開銷；既然是基於多版本，即快照讀可能讀到的並不一定是資料的最新版本，而有可能是之前的歷史版本。
2、快照讀就是MVCC思想在MySQL的具體非阻塞讀功能實現，MVCC的目的就是為了實現讀-寫衝突不加鎖，提高併發讀寫效能，而這個讀指的就是快照讀。
3、快照讀就是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時：

1、 InnoDB只會查詢版本早於當前事務版本的資料行(也就是行的系統版本號<=事務的系統版本號)，這樣可以確保事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。
2、行的刪除版本要麼未定義，要麼大於當前事務版本號，這可以確保事務讀取到的行在事務開始之前未被刪除。
3、只有1，2 同時滿足的記錄，才能返回作為查詢結果。

delete時：

InnoDB會為刪除的每一行儲存當前系統的版本號(事務的ID)作為刪除標識.

update時：

InnoDB執行update，實際上是新插入了一行記錄，並儲存其建立時間為當前事務的ID，同時儲存當前事務ID到要update的行的刪除時間。

上面只是一個淺顯的講解MVCC選擇標準流程，原始碼層面應該是根據低水位跟高水位來擷取的。具體實現可自行百度。

重點：

1、事務中快照讀的結果是非常依賴該事務首次出現快照讀的地方，即某個事務中首次出現快照讀的地方非常關鍵，它有決定該事務後續快照讀結果的能力。
2、在RC隔離級別下，是每個快照讀都會生成並獲取最新的Read View；而在RR隔離級別下，則是同一個事務中的第一個快照讀才會建立Read View, 之後的快照讀獲取的都是同一個Read View。

7、緩衝池（buffer pool）

應用系統分層架構，為了加速資料訪問，會把最常訪問的資料，放在快取(cache)裡，避免每次都去訪問資料庫。作業系統，會有緩衝池(buffer pool)機制，避免每次訪問磁碟，以加速資料的訪問。MySQL作為一個儲存系統，同樣具有緩衝池(buffer pool)機制，以避免每次查詢資料都進行磁碟IO，主要作用：

1、存在的意義是加速查詢
2、緩衝池(buffer pool) 是一種常見的降低磁碟訪問 的機制；
3、緩衝池通常以頁(page 16K)為單位快取資料；
4、緩衝池的常見管理演算法是LRU，memcache，OS，InnoDB都使用了這種演算法；
5、InnoDB對普通LRU進行了最佳化：將緩衝池分為老生代和新生代，入緩衝池的頁，優先進入老生代，該頁被訪問，才進入新生代，以解決預讀失效的問題頁被訪問。且在老生代停留時間超過配置閾值的，才進入新生代，以解決批次資料訪問，大量熱資料淘汰的問題

預讀失效：

由於預讀(Read-Ahead)，提前把頁放入了緩衝池，但最終MySQL並沒有從頁中讀取資料，稱為預讀失效

順豐快遞：請簽收MySQL靈魂十連 緩衝池汙染：

當某一個SQL語句，要批次掃描大量資料時，可能導致把緩衝池的所有頁都替換出去，導致大量熱資料被換出，MySQL效能急劇下降，這種情況叫緩衝池汙染。解決辦法：加入老生代停留時間視窗策略後，短時間內被大量載入的頁，並不會立刻插入新生代頭部，而是優先淘汰那些，短期內僅僅訪問了一次的頁。

8、table瘦身

空洞：

MySQL執行delete命令其實只是把記錄的位置，或者資料頁標記為了可複用，但磁碟檔案的大小是不會變的。透過delete命令是不能回收表空間的。這些可以複用，而沒有被使用的空間，看起來就像是空洞。插入時候引發分裂同樣會產生空洞。

重建表思路：

1、新建一個跟A表結構相同的表B
2、按照主鍵ID將A資料一行行讀取同步到表B
3、用表B替換表A實現效果上的瘦身。

重建表指令：

1、alter table A engine=InnoDB，慎重用，牛逼的DBA都用下面的開源工具。
2、推薦Github：gh-ost

9、SQL Joins、統計、隨機查詢

7種join具體如下：順豐快遞：請簽收MySQL靈魂十連統計：

1、MyISAM模式下把一個表的總行數存在了磁碟上，直接拿來用即可
2、InnoDB引擎由於 MVCC的原因，需要把資料讀出來然後累計求和
3、效能來說由壞到好：count(欄位) < count(主鍵id) < count(1) ≈ count(*)，儘量用count(*)即可。

隨機查詢：

mysql> select word from words order by rand() limit 3;

直接使用order by rand()，explain 這個語句發現需要 Using temporary和 Using filesort，查詢的執行代價往往是比較大的。所以在設計的時要避開這種寫法。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1; 
select * from t limit @Y2,1;
select * from t limit @Y3,1;

這樣可以避免臨時表跟排序的產生，最終查詢行數 = C + (Y1+1) + (Y2+1) + (Y3+1)

exist 和 in 對比：

1、in查詢時首先查詢子查詢的表，然後將內表和外表做一個笛卡爾積，然後按照條件進行篩選。
2、子查詢使用 exists，會先進行主查詢，將查詢到的每行資料迴圈帶入子查詢校驗是否存在，過濾出整體的返回資料。
3、兩表大小相當，in 和 exists 差別不大。內表大，用 exists 效率較高；內表小，用 in 效率較高。
4、查詢用not in 那麼內外表都進行全表掃描，沒有用到索引；而not exists 的子查詢依然能用到表上的索引。not exists比not in要快。

10、MySQL最佳化

SQL最佳化主要分4個方向：SQL語句跟索引、表結構、系統配置、硬體。

總最佳化思路就是最大化利用索引、儘可能避免全表掃描、減少無效資料的查詢：

1、減少資料訪問：設定合理的欄位型別，啟用壓縮，透過索引訪問等減少磁碟 IO。
2、返回更少的資料：只返回需要的欄位和資料分頁處理，減少磁碟 IO 及網路 IO。
3、減少互動次數：批次 DML 操作，函式儲存等減少資料連線次數。
4、減少伺服器 CPU 開銷：儘量減少資料庫排序操作以及全表查詢，減少 CPU 記憶體佔用。
5、分表分割槽：使用表分割槽，可以增加並行操作，更大限度利用 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調優大致思路：

1、先用慢查詢日誌定位具體需要最佳化的sql

2、使用 explain 執行計劃檢視索引使用情況

3、重點關注(一般情況下根據這4列就能找到索引問題)：

1、key（檢視有沒有使用索引）
2、key_len（檢視索引使用是否充分）
3、type（檢視索引型別）
4、Extra（檢視附加資訊：排序、臨時表、where條件為false等）

4、根據上1步找出的索引問題最佳化sql 5、再回到第2步

順豐快遞：請簽收MySQL靈魂十連

表結構最佳化：

1、儘量使用TINYINT、SMALLINT、MEDIUM_INT作為整數型別而非INT，如果非負則加上UNSIGNED 。
2、VARCHAR的長度只分配真正需要的空間。
3、儘量使用TIMESTAMP而非DATETIME 。
4、單表不要有太多欄位，建議在20以內。
5、避免使用NULL欄位，很難查詢最佳化且佔用額外索引空間。字串預設為''。

讀寫分離：

只在主伺服器上寫，只在從伺服器上讀。對應到資料庫叢集一般都是一主一從、一主多從。業務伺服器把需要寫的操作都寫到主資料庫中，讀的操作都去從庫查詢。主庫會同步資料到從庫保證資料的一致性。一般讀寫分離的實現方式有兩種：程式碼封裝跟資料庫中介軟體。

分庫分表：分庫分表分為垂直和水平兩個方式，一般是先垂直後水平。

1、垂直分庫：將應用分為若干模組，比如訂單模組、使用者模組、商品模組、支付模組等等。其實就是微服務的理念。
2、垂直分表：一般將不常用欄位跟資料較大的欄位做拆分。
3、水平分表：根據場景選擇什麼欄位作分表欄位，比如淘寶日訂單1000萬，用userId作分表欄位，資料查詢支援到最近6個月的訂單，超過6個月的做歸檔處理，那麼6個月的資料量就是18億，分1024張表，每個表存200W資料，hash(userId)%100找到對應表格。
4、ID生成器：分散式ID 需要跨庫全域性唯一方便查詢儲存-檢索資料，確保唯一性跟數字遞增性。

目前主要流行的分庫分表工具就是Mycat和sharding-sphere。

TiDB：開源分散式資料庫，結合了傳統的 RDBMS 和NoSQL 的最佳特性。TiDB 相容 MySQL，支援無限的水平擴充套件，具備強一致性和高可用性。TiDB 的目標是為 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 場景提供一站式的解決方案。TiDB 具備如下核心特點

1、支援 MySQL 協議（開發接入成本低）。
2、100% 支援事務（資料一致性實現簡單、可靠）。
3、無限水平擴充（不必考慮分庫分表），不停服務。
4、TiDB 支援和 MySQL 的互備。
5、遵循jdbc原則，學習成本低，強關係型，強一致性，不用擔心主從配置，不用考慮分庫分表，還可以無縫動態擴充套件。

適合：

1、原業務的 MySQL 的業務遇到單機容量或者效能瓶頸時，可以考慮使用 TiDB 無縫替換 MySQL。
2、大資料量下，MySQL 複雜查詢很慢。
3、大資料量下，資料增長很快，接近單機處理的極限，不想分庫分表或者使用資料庫中介軟體等對業務侵入性較大、對業務有約束的 Sharding 方案。
4、大資料量下，有高併發實時寫入、實時查詢、實時統計分析的需求。5、有分散式事務、多資料中心的資料 100% 強一致性、auto-failover 的高可用的需求。

不適合：

1、單機 MySQL 能滿足的場景也用不到 TiDB。
2、資料條數少於 5000w 的場景下通常用不到 TiDB，TiDB 是為大規模的資料場景設計的。
3、如果你的應用資料量小（所有資料千萬級別行以下），且沒有高可用、強一致性或者多資料中心複製等要求，那麼就不適合使用 TiDB。