深入淺出MySQL靈魂十連問，你真的有把握嗎？

一、SQL陳述句執行流程

MySQL大體上可分為Server層和存盤引擎層兩部分，

Server層：

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

存盤引擎層

負責資料的存盤和提取，其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存盤引擎，現在最常用的存盤引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認存盤引擎(經常用的也是這個)，

SQL執行順序

二、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 陳述句，使用了臨時表

主從同步流程：

1、主節點必須啟用二進制日志，記錄任何修改了資料庫資料的事件，

2、從節點開啟一個執行緒（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進制日志檔案中的事件 ，

3、主節點啟動一個執行緒（dump Thread），檢查自己二進制日志中的事件，跟對方請求的位置對比，如果不帶請求位置引數，則主節點就會從第一個日志檔案中的第一個事件一個一個發送給從節點，

4、從節點接收到主節點發送過來的資料把它放置到中繼日志（Relay log）檔案中，并記錄該次請求到主節點的具體哪一個二進制日志檔案內部的哪一個位置（主節點中的二進制檔案會有多個），

5、從節點啟動另外一個執行緒（sql Thread ），把 Relay log 中的事件讀取出來，并在本地再執行一次，

mysql默認的復制方式是異步的，并且復制的時候是有并行復制能力的，主庫把日志發送給從庫后不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫后，日志就丟失了，由此產生兩個概念，

1.全同步復制

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

2.半同步復制

半同步復制的邏輯是這樣，從庫寫入日志成功后回傳ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成，

還可以延伸到由于主從配置不一樣、主庫大事務、從庫壓力過大、網路震蕩等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備情況下如何避免主備回圈復制？被刪庫跑路了如何正確恢復？(⊙o⊙)… 感覺越來越扯到DBA的活兒上去了，

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，

redolog兩階段提交：為了讓binlog跟redolog兩份日志之間的邏輯一致，提交流程大致如下：

1 prepare階段 -->  2 寫binlog  --> 3 commit

1. 當在2之前崩潰時，重啟恢復后發現沒有commit，回滾，備份恢復：沒有binlog ，一致
2. 當在3之前崩潰時，重啟恢復發現雖沒有commit，但滿足prepare和binlog完整，所以重啟后會自動commit，備份：有binlog. 一致

binlog跟redolog區別：

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server層實作的，所有引擎都可以使用，
2. redo log是物理日志，記錄的是在某個資料頁上做了什么修改；binlog是邏輯日志，記錄的是這個陳述句的原始邏輯，比如給ID=2這一行的c欄位加1，
3. redo log是回圈寫的，空間固定會用完；binlog是可以追加寫入的，追加寫是指binlog檔案寫到一定大小后會切換到下一個，并不會覆寫以前的日志，

UndoLog

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

1. insert undo log

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

2. update undo log

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

三、MySQL中的索引

索引的常見模型有哈希表、有序陣列和搜索樹，

哈希表：一種以KV存盤資料的結構，只適合等值查詢，不適合范圍查詢，

有序陣列：只適用于靜態存盤引擎，涉及到插入的時候比較麻煩，可以參考Java中的ArrayList，

搜索樹：按照資料結構中的二叉樹來存盤資料，不過此時是N叉樹(B+樹)，廣泛應用在存盤引擎層中，

B+樹比B樹優勢在于：

1. B+ 樹非葉子節點存盤的只是索引，可以存盤的更多，B+樹比B樹更加矮胖，IO次數更少，
2. B+ 樹葉子節點前后管理，更加方便范圍查詢，同時結果都在葉子節點，查詢效率穩定，
3. 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不支持聚簇索引

四、SQL事務隔離級別

ACID的四個特性

1. 原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要么都成功，要么都不成功
2. 一致性（Consistency）：理解成一串對資料進行操作的程式執行下來，不會對資料產生不好的影響，比如憑空產生，或消失
3. 隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不干擾，即使是并發事務的情況下，他們只是兩個并發執行沒有交集，互不影響的東西；當然實作中，也不一定需要這么完整隔離性，即不一定需要這么的互不干擾，有時候還是允許有部分干擾的，所以MySQL可以支持4種事務隔離性
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標準的事務隔離級別由低到高如下：

上圖從上到下的模式會導致系統的并行性能依次降低，安全性依次提高，

讀未提交：別人改資料的事務尚未提交，我在我的事務中也能讀到，

讀已提交(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支持三種層級的鎖定，分別為

1.表級鎖定

MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定，

2.頁級鎖定

是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢，所以取了折衷的頁級，一次鎖定相鄰的一組記錄，

3.行級鎖定

Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖，行級鎖能大大減少資料庫操作的沖突，其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應的行對其上鎖，這樣的代價其實是比較高的，所以表鎖和行鎖各有所長，

MyISAM中的鎖

1. 雖然MySQL支持表，頁，行三級鎖定，但MyISAM存盤引擎只支持表鎖，所以MyISAM的加鎖相對比較開銷低，但資料操作的并發性能相對就不高，但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫并發

2. 從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、唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止，

六、MVCC

MVCC：

1、全稱Multi-Version Concurrency Control，即多版本并發控制，MVCC是一種并發控制的理念，維持一個資料的多個版本，使得讀寫操作沒有沖突，

2、MVCC在MySQL InnoDB中實作目的主要是為了提高資料庫并發性能，用更好的方式去處理讀-寫沖突，做到即使有讀寫沖突時，也能做到不加鎖，非阻塞并發讀，

MySQL InnoDB下的當前讀和快照讀

1.當前讀

1、像select lock in share mode(共享鎖)、select for update 、update、insert、delete(排他鎖)這些操作都是一種當前讀，就是它讀取的是記錄的最新版本，讀取時還要保證其他并發事務不能修改當前記錄，會對讀取的記錄進行加鎖，

2、當前讀可以認為是悲觀鎖的具體功能實作

2.快照讀

1、不加鎖的select就是快照讀，即不加鎖的非阻塞讀；快照讀的前提是隔離級別不是串行級別，串行級別下的快照讀會退化成當前讀；之所以出現快照讀的情況，是基于提高并發性能的考慮，快照讀的實作是基于多版本并發控制，即MVCC，可以認為MVCC是行鎖的一個變種，但它在很多情況下，避免了加鎖操作，降低了開銷；既然是基于多版本，即快照讀可能讀到的并不一定是資料的最新版本，而有可能是之前的歷史版本，

2、快照讀就是MVCC思想在MySQL的具體非阻塞讀功能實作，MVCC的目的就是為了實作讀-寫沖突不加鎖，提高并發讀寫性能，而這個讀指的就是快照讀，

3、快照讀就是MySQL為我們實作MVCC理想模型的其中一個具體非阻塞讀功能，

因為大佬不滿意只讓資料庫采用悲觀鎖這樣性能不佳的形式去解決讀-寫沖突問題，而提出了MVCC，所以我們可以形成兩個組合：

MVCC + 悲觀鎖：MVCC解決讀寫沖突，悲觀鎖解決寫寫沖突

MVCC + 樂觀鎖：MVCC解決讀寫沖突，樂觀鎖解決寫寫沖突

MVCC的實作原理

MVCC實作原理主要是依賴記錄中的 四個隱式欄位、undo日志 、Consistent Read View來實作的，

四個隱式欄位：

1.DB_TRX_ID：

6byte，最近修改(修改/插入)事務ID：記錄創建這條記錄/最后一次修改該記錄的事務ID

2.DB_ROLL_PTR

7byte，回滾指標，指向這條記錄的上一個版本（存盤于rollback segment里）

3.DB_ROW_ID

6byte，隱含的自增ID（隱藏主鍵），如果資料表沒有主鍵，InnoDB會自動以DB_ROW_ID產生一個聚簇索引

4.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，

1.insert時：

InnoDB為新插入的每一行保存當前系統版本號作為版本號，

2.select時：

1、 InnoDB只會查找版本早于當前事務版本的資料行(也就是行的系統版本號<=事務的系統版本號)，這樣可以確保事務讀取的行，要么是在事務開始前已經存在的，要么是事務自身插入或者修改過的，

2、行的洗掉版本要么未定義，要么大于當前事務版本號，這可以確保事務讀取到的行在事務開始之前未被洗掉，

3、只有1，2 同時滿足的記錄，才能回傳作為查詢結果，

3.delete時：

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

4.update時：

InnoDB執行update，實際上是新插入了一行記錄，并保存其創建時間為當前事務的ID，同時保存當前事務ID到要update的行的洗掉時間，

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

重點：

1、事務中快照讀的結果是非常依賴該事務首次出現快照讀的地方，即某個事務中首次出現快照讀的地方非常關鍵，它有決定該事務后續快照讀結果的能力，

2、在RC隔離級別下，是每個快照讀都會生成并獲取最新的Read View；而在RR隔離級別下，則是同一個事務中的第一個快照讀才會創建Read View, 之后的快照讀獲取的都是同一個Read View，

七、緩沖池（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并沒有從頁中讀取資料，稱為預讀失效

緩沖池污染：

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

八、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

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

7種join具體如下：

統計：

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要快，

十、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步

表結構優化：

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，

寫在最后

歡迎大家關注我的公眾號【風平浪靜如碼】，海量Java相關文章，學習資料都會在里面更新，整理的資料也會放在里面，

覺得寫的還不錯的就點個贊，加個關注唄！點關注，不迷路，持續更新！！！

標籤：Java

上一篇：JAVA中常見的阻塞佇列詳解

下一篇：高手都這么給 Spring MVC 做單元測驗！