一文帶你複習好Mysql的核心知識點

SQL優化調優是展現程式員分析歸納能力的有效手段，雖然我們不是DBA，但是編碼開發時也會涉及許多對資料庫的CRUD需求。是以，通過了解Mysql資料庫的底層原理，對我們的筆試面試，還有提高業務編碼水準是有好處的。

Mysql 的底層結構

大體來說，MySQL 可以分為 Server 層 和 存儲引擎層兩部分：

• Server 層包括連接配接器、查詢緩存、分析器、優化器、執行器等。
• 存儲引擎層負責資料的存儲和提取。其架構模式是插件式的，支援 InnoDB、MyISAM、Memory 等多個存儲引擎。

現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設存儲引擎。

下面是Mysql的架構圖：

資料庫的Server 層包括：連接配接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的内置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實作，比如存儲過程、觸發器、視圖等。

1、連接配接器：連接配接器負責跟用戶端建立連接配接、擷取權限、維持和管理連接配接。

資料庫長連接配接：指連接配接成功後，如果用戶端持續有請求，則一直使用同一個連接配接。

資料庫短連接配接：則是指每次執行完很少的幾次查詢就斷開連接配接，下次查詢再重建立立一個。 

2、查詢緩存：MySQL 拿到一個查詢請求後，會先到查詢緩存。

但是大多數情況下我會建議你不要使用查詢緩存，為什麼呢？因為查詢緩存往往弊大于利（查詢緩存的失效非常頻繁，隻要有對一個表的更新，這個表上所有的查詢緩存都會被清空。是以很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了）。

3、分析器：MySQL 需要知道你要做什麼，是以需要對 SQL 語句做解析。

4、優化器：優化器是在表裡面有多個索引的時候，決定使用哪個索引。

5、執行器：通過分析器知道了你要做什麼，通過優化器知道了該怎麼做，于是就進入了執行器階段，開始執行語句。

資料庫的存儲引擎層：負責資料的存儲和提取，其架構模式是插件式的，支援 InnoDB、MyISAM、Memory 等多個存儲引擎。

Mysql 日志系統&兩階段送出

RedoLog 和 BinLog 差別

1、 redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實作的，所有引擎都可以使用。

2、 redo log 是實體日志，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。

3、redo log 是循環寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，并不會覆寫以前的日志。

mysql的兩階段送出原理

階段1：InnoDB redo log 寫盤（引擎層），InnoDB 事務進入 prepare 狀态；階段2：如果前面prepare成功，binlog 寫盤（Server層），那麼再繼續将事務日志持久化到binlog，如果持久化成功，那麼 InnoDB 事務 則進入 commit 狀态(實際是在redo log裡面寫上一個commit記錄)；好處：兩階段送出是跨系統維持資料邏輯一緻性時常用的一個方案，這個方案也同時解決磁盤IO的性能。

Mysql 的事務

資料庫的事務：就是要保證一組資料庫操作，要麼全部成功，要麼全部失敗。

資料庫執行引擎中的 InnoDB 支援事務，MySQL 原生的 MyISAM 引擎就不支援事務，這也是 MyISAM 被 InnoDB 取代的重要原因之一。

事務隔離特性ACID：表示原子性（atomicity）、一緻性（consistency）、隔離性（isolation）和持久性（durability）。

多事務同時執行引發的問題：髒讀（dirty read）、不可重複讀（non-repeatable read）、幻讀（phantom read）。

事務隔離級别：讀未送出（read uncommitted）、讀送出（read committed）、可重複讀（repeatable read）和串行化（serializable ）。

事務隔離真理：隔離級别越高，資料庫效率越低。很多時候要在二者之間尋找一個平衡（跟代碼加了重量級鎖同理：利用 synchronized 鎖住整個方法，效率比鎖住方法的某個代碼塊是要低很多的）。

對于事務最重要的便是隔離級别了，但每種隔離級别都不是十全十美的，相對應的會引發比對的問題，下面逐一解釋：

讀未送出

讀未送出是指：一個事務還沒送出時，它做的變更就能被别的事務看到。(隔離性差，會出現髒讀情況）

問題：産生了“髒讀”資料，另一個未送出的事務，影響了資料居然也被讀取到了。解決辦法是設定隔離級别為“讀送出”。

讀送出

讀送出是指：一個别的事務送出之後，它做的變更才會被别的事務看到。（oracle預設，隔離性還行，會出現不可重複讀情況）

好處：解決了“髒讀”問題。

問題：産生了“不可重複讀”問題。

但正因為可以“及時的”讀取到别的事務送出結果，出現了一個事務範圍内同一個事務，但兩個相同的查詢卻傳回了不同資料，這就是不可重複讀。

解決辦法就是設定隔離級别為“可重複讀”。解決不可重複讀就需要鎖行了。

可重複讀

可重複讀是指，一個目前事務執行過程中看到的資料，總是跟這個目前事務在啟動時看到的資料是一緻的。當然在可重複讀隔離級别下，未送出變更對其他事務也是不可見的。（Mysql預設）

好處：解決了“不可重複讀”問題。

問題：産生了“幻讀”問題。

可重複讀，就是在開始讀取資料（事務開啟）時，update操作可以被隔離，但是insert/delete操作除外，這會出現一個新問題(幻讀)。解決幻讀就需要鎖表了。

如果使用鎖機制來實作這兩種隔離級别，在可重複讀中，該sql第一次讀取到資料後，就将這些資料加鎖，其它事務無法修改這些資料，就可以實作可重複讀了。

但這種方法卻無法鎖住insert的資料，是以當事務A先前讀取了資料，或者修改了全部資料，事務B還是可以insert資料送出，這時事務A就會 發現莫名其妙多了一條之前沒有的資料。

串行化

串行化：顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖沖突的時候，後通路的事務必須等前一個事務執行完成，才能繼續執行。

好處：解決了“幻讀”問題，因為可重複讀是行級鎖，而Serializable是表級鎖，把整張表鎖住了。

問題：性能大打折扣，不推薦。

總結：存在即合理，每種隔離級别都有自己的使用場景，你要根據自己的業務情況來定。

Mysql 的鎖

資料庫鎖出現的原因是為了處理并發問題，因為資料庫是一個多使用者共享的資源，當出現并發的時候，就會導緻出現各種各樣奇怪的問題，就像程式代碼一樣，出現多線程并發的時候，如果不做特殊控制的話，就會出現意外的事情，比如“髒“資料、修改丢失等問題。

從程式員的角度，鎖可以分為：“樂觀鎖” 和 “悲觀鎖”。

樂觀鎖：程式員設計表結構加字段&更新操作進行判斷是否字段數值一直。

1. 加上version的版本控制（後續進行更新操作，先比較version是否一緻，不一緻就需要重新取一遍資料）
2. 也可以使用timestamp的控制（此處的時間戳應該使用資料庫的而非業務系統的）。

悲觀鎖：（由系統提供的）表鎖、行鎖、間隙鎖等。

1. 表鎖(Table Lock)：對整個表進行加鎖。
2. 行鎖(Record Lock)：對索引記錄加鎖。
3. 間隙鎖(Gap Lock)：鎖住整個區間，包括：區間裡具體的索引記錄，不存在的空閑空間（可以是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引記錄之後的空間）。
4. next-key鎖：行鎖和間隙鎖組合起來。

針對悲觀鎖，其實日常的很多sql，我們已經不知不覺的使用到了：

執行個體1：行鎖

對主鍵或者唯一索引進行增删改或顯示的加鎖，InnoDB會加行鎖。

執行個體2：間隙鎖

間隙鎖的唯一目的就是阻止其他事務插入到間隙中。

一個表有id為1，2，3，5，6，9行資料，執行如下sql語句：

InnoDB不僅會鎖住id為5和6兩行的資料，也會鎖住id為4(雖然該行并不存在)的紀錄。

間隙鎖的目的是為了防止幻讀，其主要通過兩個方面實作這個目的： 

（1）防止間隙内有新資料被插入 

（2）防止已存在的資料，更新成間隙内的資料

執行個體3：next-key lock

next-key lock=行鎖+間隙鎖；如果一個事務在記錄R上的某個索引有共享/互斥鎖,也會對其前面一個範圍加鎖。

鎖定的區域

根據索引會形成一個個左開右閉的一個區間，根據查詢的條件其所在的區間，并且包括其後的區間。

參考文章：

https://blog.csdn.net/wanger61/article/details/107817158

Mysql 的索引模型與應用（InnoDB）

索引目的：

    為了提高資料查詢效率（減少磁盤查詢次數）。

索引模型：

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。

Mysql的InnoDB使用的是B+ 樹索引結構（當然也提供了Hash索引結構，但是使用的非常非常少）。

Hash索引 與 B+索引：

哈希索引的示意圖：

Hash索引的不足：

不支援聯合索引，不支援排序和分組，不支援範圍查詢，甚至大規模資料情況下，會帶來哈希沖突問題。

B+樹索引資料結構見下圖：

簡要概述：

• B+樹-采用平衡樹結構，每個葉子節點到根的路徑長度都相同
• B+樹-葉子節點按鍵值大小順序，放在同一層的葉子節點上

聚簇索引和非聚簇索引：

主鍵索引：主鍵索引樹，葉子結點包含整條資料内容。

非主鍵索引：非主鍵索引樹，葉子結點僅包含資料的ID内容，是以需要回表擷取更全面的資料（查詢多一次主鍵樹）。

下面總結一些索引使用的法則。不推薦使用索引的場景：

1. 表記錄太少；
2. 資料重複且分布平均的字段（隻有很少資料值字段或者枚舉字段）；
3. 經常插入、删除、修改的表要減少索引；
4. text，image等類型不應該建立索引，這些列的資料量大（假如text前10個字元唯一，也可以對text前10個字元建立索引）；
5. MySQL能估計出全表掃描比使用索引更快時，不使用索引；

聯合索引優于單索引的場景：

1. 由于MySQL每次查詢隻使用一個索引。與其說是“資料庫查詢隻能用到一個索引”，倒不如說，和全表掃描比起來，去分析兩個索引B+樹更加耗費時間。
2. 是以where A=a and B=b這種查詢使用（A，B）的組合索引最佳，B+樹根據（A，B）來排序。

推薦使用索引的場景：

1. 主鍵，unique字段；
2. 聯表的字段需要加索引；
3. 在where裡使用＞，≥，＝，＜，≤，is null和between等字段；
4. 使用不以通配符開始的like，where A like 'China%'；
5. 聚集函數MIN()，MAX()中的字段；
6. order by和group by字段；

索引失效場景：

1. 組合索引未使用最左字首，例如組合索引（A，B），where B=b不會使用索引；
2. like未使用最左字首，where A like '%China'；
3. 搜尋一個索引而在另一個索引上做order by，where A=a order by B，（一個失敗一個成功）隻使用A上的索引，因為查詢隻使用一個索引 ；
4. or 可能使第一個索引失效。如果查詢字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）。index merge 技術可以優化這個問題。
5. 如果列類型是字元串，要使用引号。例如where A='China'，否則索引失效（會進行類型轉換），見下圖：

   

   第1條sql 走索引，第2條sql則是全表掃描，非常慢。
6. 在索引列上進行計算、函數、手動或自動的類型轉換會導緻索引失效；見下圖：

索引的五個設計原則：

1. 第一個索引原則：盡量使用主鍵索引原則。（主鍵索引是聚簇索引，葉子結點保留完整資料；非聚簇索引需要回表，多查一次B+樹）
2. 第二個索引原則：控制查詢字段（ID替代通配符*），覆寫索引的手段，能夠大幅度提升性能。（隻查詢ID不用回表）
3. 第三個索引原則：最左字首原則。（索引複用，索引項是按照索引定義裡面出現的字段順序排序的）
4. 第四個索引原則：聯合索引，索引順序往往就是需要優先考慮采用的。
5. 第五個索引原則：索引下推原則。（在索引周遊過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數）

總結

我們總結一下，上文主要歸納總結了Mysql的主要核心概念：日志系統&隔離級别&索引模型&索引使用經驗等。

掃描二維碼

擷取技術幹貨

背景技術彙