MySQL基礎知識整理

MySQL基礎架構

MySQL基礎架構

簡單來說MySQL主要分為Server層和存儲引擎層。Server層主要包括連接器、查詢緩存、分析器、優化器和執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日誌模塊 binglog。

存儲引擎層主要負責數據的存儲和讀取，採用可以替換的插件式架構，支持InnoDB、MyISAM和Memory等多個存儲引擎，其中InnoDB引擎有自有的日誌模塊redolog。從MySQL5.5.5版本開始被當做默認的存儲引擎。

查詢語句的執行流程是：權限校驗->查詢緩存->分析器->優化器->權限校驗->執行器->存儲引擎

更新語句的執行流程是：查詢->分析器->權限校驗->執行器->存儲引擎->redolog(prepare狀態)->binlog->redo(commit狀態)

觸發器

一觸即發 當表上出現特定的事件時， 觸發該程序執行update/delete/insert。

觸發器對性能有損耗，應該謹慎使用對於事務表，

觸發器執行失敗則整個語句回滾

Row格式的主從複製，觸發器不會再從庫上執行

存儲過程

存儲在數據庫端的一組SQL語句集，用戶可以通過存儲過程名和傳參多次調用的程序模塊。

特點：

使用靈活，可以完成複雜的業務邏輯提高數據安全性，

屏蔽應用對錶的操作，易於審計

減少網絡傳輸

缺點：

提高了代碼維護的複雜度

索引

為什麼使用索引

• 索引可以加速查詢的效率。
• 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。
• 幫助服務器避免排序和臨時表。
• 將隨機IO變為順序IO
• 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

索引的代價

• 對錶中數據增刪改時，索引需要動態維護(需要維持平衡)，因此降低了數據的維護速度。
• 索引要佔用物理空間。
• 創建和維護索引需要耗費時間，隨著數據量增加而增加

索引的原理

MySQL有哈希索引和BTree索引兩種索引結構

哈希索引的底層數據結構是哈希表，在絕大數需求為單條記錄查詢時，可以選擇哈希索引。Hash索引不支持順序和範圍查詢是它最大的缺點。

BTree索引的底層數據結構是B+樹。

B+樹結構的優點

1. B+樹是平衡多路查找樹，檢索的時間複雜度是O(logn)。
2. 數據庫充分利用了磁盤塊的原理（磁盤數據存儲是採用塊的形式存儲的，每個塊的大小為4K，每次IO進行數據讀取時，同一個磁盤塊的數據可以一次性讀取出來）把節點大小限制和充分使用在磁盤塊大小範圍；把樹的節點關鍵字增多後樹的層級比原來的二叉樹少了，減少數據查找的次數和複雜度。
3. B+樹的非葉子節點不保存關鍵字記錄的指針，只保存數據索引，非葉子節點可以保存的關鍵字大大增加，樹的層級更少，所以查詢數據更快。
4. B+所有關鍵字數據地址都存在葉子節點上，所以每次查找的次數都相同所以查詢速度要比B樹更穩定;
5. B+樹的葉子節點的主鍵數據從小到大有序排列，除葉子節點外的所有節點的關鍵字，都在它的下一級子樹中同樣存在，最後所有數據都存儲在葉子節點中。左邊葉子節點結尾數據都會保存右邊葉子節點開始數據的指針，B+樹天然具備排序功能，B+樹所有的葉子節點數據構成了一個有序鏈表，在查詢大小區間的數據時候更方便，數據緊密性很高，緩存的命中率也會比B樹高。
6. B+樹全節點遍歷更快，B+樹遍歷整棵樹只需要遍歷所有的葉子節點即可，而不需要像B樹一樣需要對每一層進行遍歷，這有利於數據庫做全表掃描。

非聚簇索引是指MyISAM存儲引擎的BTree索引的B+樹的葉子節點的data域存放的是數據記錄的地址。

聚簇索引是指InnoDB存儲引擎的主鍵索引的B+樹的葉子節點的data域存放的是完整的數據記錄。

輔助索引是指InnDB存儲引擎的索引的B+樹的葉子節點的data域存放的是主鍵值。唯一索引，普通索引，前綴索引和全文索引等都屬於二級索引。

覆蓋索引是指一個索引包含所有需要查詢的字段的值。（不需要回表操作）

聯合索引是指多個字段聯合形成的索引，使用時有最左前綴匹配的規則，並且聯合索引只能用於查找key是否存在（相等），遇到範圍查詢(>、<、between、like左匹配)等就不能進一步匹配了，後續退化為線性查找。MySQL會一直向右匹配直到遇到範圍查詢（>,<,BETWEEN,LIKE）就停止匹配。

InnoDB根據索引查詢的流程：在根據主鍵索引搜索時，直接找到key所在的節點即可取出數據；在根據輔助索引查找時，則需要先取出主鍵的值，在走一遍主鍵索引。

索引實踐

通過Explain命令可以查看索引是否生效，Explain命令顯示的字段的大概解釋如下：

type：表示MySQL在表中找到所需行的方式

最為常見的掃描方式有：system > const > eq_ref > ref > range > index > ALL

• system：系統表，少量數據，往往不需要進行磁盤IO；
• const：常量連接；命中主鍵(primary key)或者唯一(unique)索引，並且被連接的部分是一個常量(const)值。
• eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描；對於前表的每一行(row)，後表只有一行被掃描。
• ref：非主鍵非唯一索引等值掃描；對於前表的每一行(row)，後表可能有多於一行的數據被掃描。
• range：範圍掃描；它是索引上的範圍查詢，它會在索引上掃描特定範圍內的值。
• index：索引樹掃描；需要掃描索引上的全部數據。
• ALL：全表掃描(full table scan)；對於前表的每一行(row)，後表都要被全表掃描。

possible_keys: 此次查詢中可能選用的索引

key: 此次查詢中確切使用到的索引

key_len：表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度

ref：表示上述表的連接匹配條件，即哪些列或常量被用於查找索引列上的值

rows：表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數

extra: 該列包含MySQL解決查詢的詳細信息

查詢操作非常頻繁的字段，可以考慮建立索引。

對於頻繁被連接查詢的字段，可以考慮建立索引，提高多表連接查詢的效率。

被作為WHERE條件查詢的字段，應該被考慮建立索引。

被頻繁更新的字段應該慎重建立索引。

通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

使用select * 進行範圍查詢普通索引可能不會生效。

“列類型”與“where值類型”不符，不能命中索引，會導致全表掃描(full table scan)。

相join的兩個表的字符編碼不同，不能命中索引，會導致笛卡爾積的循環計算（nested loop）。

不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描。

mysql在使用不等於（！=或者<>）、is null和is not null的時候可能無法使用索引會導致全表掃描

使用覆蓋索引可以解決like以通配符開頭（’%abc…’）mysql索引失效會變成全表掃描的操作的問題。

儘量選擇區分度高的列作為索引，區分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重複的比率，比率越大我們掃描的記錄數就越少。

字符串索引，可以創建前綴索引，前綴索引僅限於字符串類型，較普通索引會佔用更小的空間，所以可以考慮使用前綴索引。

注意避免冗餘索引，儘可能的擴展已有的索引，不要新建立索引。比如表中已經有了a的索引，現在要加（a,b）的索引，那麼只需要修改原來的索引即可。

不建議使用過長的字段作為主鍵，也不建議使用非單調的字段作為主鍵，這樣會造成主索引頻繁分裂。順序主鍵也有缺點：對於高併發工作負載，在InnoDB中按主鍵順序插入可能會造成明顯的爭用。主鍵的上界會成為“熱點”。因為所有的插入都發生在這裡，所以併發插入可能導致間隙鎖競爭。另一個熱點可能是auto_increment鎖機制；如果遇到這個問題，則可能需要考慮重新設計表或者應用，比如應用層面生成單調遞增的主鍵ID，插表不使用auto_increment機制，或者更改innodb_autonc_lock_mode配置。

大多數情況下，索引查詢都是比全表掃描要快的。但是如果數據庫的數據量不大，那麼使用索引也不一定能夠帶來很大提升。

事務

關係數據庫中，事務（Transaction），指訪問並可能更新數據庫中各種數據項的一個程序執行單元(unit)。事務是恢復和併發控制的基本單位。一個事務可以是一條SQL語句，一組SQL語句或整個程序。事務是邏輯上的一組操作，將一組操作在邏輯上抽象成一個操作，要麼都執行，要麼都不執行。

ACID

原子性（Atomicity）：事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用；

一致性（Consistency）：執行事務前後，數據保持一致，多個事務對同一個數據讀取的結果是相同的；事務必須是使數據庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。

隔離性（Isolation）：併發訪問數據庫時，一個用戶的事務不被其他事務所幹擾，各併發事務之間數據庫是獨立的；即一個事務內部的操作及使用的數據對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。

持久性（Durability）： 一個事務被提交之後。它對數據庫中數據的改變是持久的，即使數據庫發生故障也不應該對其有任何影響。

事務狀態

每一個事務都對應著一個或多個數據庫操作，根據這些操作執行的不同階段，我們可以把事務劃分成幾個狀態：

活動的（active）：事務對應的數據庫操作正在執行過程中時，我們就說該事務處在活動的狀態。

部分提交的（partially committed）：當事務中的最後一個操作執行完成，但由於操作都在內存中執行，所造成的影響並沒有刷新到磁盤時，我們就說該事務處在部分提交的狀態。刷盤拓展：事務操作不會直接更改物理磁盤，而是先修改內存中的Buffer Pool中的數據，為什麼呢？每次刷慢，改動的數據頁不連續，隨機IO多。記錄在Redolog，（Undolog作用），事務提交時，Redolog刷到磁盤。

失敗的（failed）：當事務處在活動的或者部分提交的狀態時，可能遇到了某些錯誤（數據庫自身的錯誤、操作系統錯誤或者直接斷電等）而無法繼續執行，或者人為的停止當前事務的執行，我們就說該事務處在失敗的狀態。

中止的（aborted）：如果事務執行了半截而變為失敗的狀態，就要撤銷失敗事務對當前數據庫造成的影響。這個撤銷的過程叫做回滾。當回滾操作執行完畢時，也就是數據庫恢復到了執行事務之前的狀態，我們就說該事務處在了中止的狀態。

提交的（commited）：當一個處在部分提交的狀態的事務將修改過的數據都同步到磁盤上之後，該事務處在了提交的狀態。

事務狀態變化

併發事務帶來哪些問題?

不可重複讀和幻讀區別：

不可重複讀的重點是修改比如多次讀取一條記錄發現其中某些列的值被修改，幻讀的重點在於新增或者刪除比如多次讀取一條記錄發現記錄增多或減少了

捨棄一部分隔離性來換取一部分性能在這裡就體現在：設立一些隔離級別，隔離級別越低，越嚴重的問題就越可能發生。所以產生了一個SQL標準，在標準中設立了4個隔離級別：

• READ UNCOMMITTED：讀未提交（讀取記錄的最新版本）最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致髒讀、幻讀或不可重複讀。
• READ COMMITTED：讀已提交（
  每次讀取前生成一個ReadView），允許讀取併發事務已經提交的數據，可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生。
• REPEATABLE READ：可重複讀（第一次讀取前生成一個ReadView），對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止髒讀和不可重複讀（幻讀？表象上解決了幻讀，物理上沒有解決幻讀，需要forupdate加鎖來從根本上解決幻讀）。MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）。Next-Key Lock 鎖算法，因此可以避免幻讀的產生。
• SERIALIZABLE：可串行化。

MVCC原理

MVCC（Multi-Version Concurrency Control ，多版本併發控制）指的就是在使用READ COMMITTD、REPEATABLE READ這兩種隔離級別的事務在執行普通的SELECT操作時訪問記錄的版本鏈的過程，這樣子可以使不同事務的讀-寫、寫-讀操作併發執行，從而提升系統性能。

READ COMMITTD、REPEATABLE READ這兩個隔離級別的一個很大不同就是：生成ReadView的時機不同，READ COMMITTD在每一次進行普通SELECT操作前都會生成一個ReadView，而REPEATABLE READ只在第一次進行普通SELECT操作前生成一個ReadView，之後的查詢操作都重複使用這個ReadView。

對於使用InnoDB存儲引擎的表來說，它的主鍵索引記錄中都包含兩個必要的隱藏列：

• trx_id：每次一個事務對某條主鍵索引記錄進行改動時，都會把該事務的事務id賦值給trx_id隱藏列。
• roll_pointer：每次對某條主鍵索引記錄進行改動時，都會把舊的版本寫入到undo日誌中，然後這個隱藏列就相當於一個指針，可以通過它來找到該記錄修改前的信息。

​實際上insert undo只在事務回滾時起作用，當事務提交後，該類型的undo日誌就沒用了，它佔用的Undo Log Segment也會被系統回收（該undo日誌佔用的Undo頁面鏈表要麼被重用，要麼被釋放）。雖然真正的insert undo日誌佔用的存儲空間被釋放了，但是roll_pointer的值並不會被清除，roll_pointer屬性佔用7個字節，第一個比特位就標記著它指向的undo日誌的類型，如果該比特位的值為1時（undo日誌也分類型，這裡只提一下我們舉例子的undo日誌類型），就代表著它指向的undo日誌類型為insert undo。

每次對記錄進行改動，都會記錄一條undo日誌，每條undo日誌也都有一個roll_pointer屬性（INSERT操作對應的undo日誌沒有該屬性，因為該記錄並沒有更早的版本），可以將這些undo日誌都連起來，串成一個鏈表。

對該記錄每次更新後，都會將舊值放到一條undo日誌中，就算是該記錄的一箇舊版本，隨著更新次數的增多，所有的版本都會被roll_pointer屬性連接成一個鏈表，我們把這個鏈表稱之為版本鏈，版本鏈的頭節點就是當前記錄最新的值。另外，每個版本中還包含生成該版本時對應的事務id。

ReadView

只讀事務的事務id默認為0；insert, delete, update才會為事務分配事務id。

ReadView中主要包含4個比較重要的內容：

• m_ids：表示在生成ReadView時當前系統中活躍的讀寫事務的事務id列表。
• min_trx_id：表示在生成ReadView時當前系統中活躍的讀寫事務中最小的事務id，也就是m_ids中的最小值。
• max_trx_id：表示生成ReadView時系統中應該分配給下一個事務的id值。 這裡說一下max_trx_id並不是m_ids中的最大值，事務id是遞增分配的。比方說現在有id為1，2，3這三個事務，之後id為3的事務提交了。那麼一個新的讀事務在生成ReadView時，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
• creator_trx_id：表示生成該ReadView的事務的事務id。

有了這個ReadView，這樣在訪問某條記錄時，只需要按照下邊的步驟判斷記錄的某個版本是否可見：

• 如果被訪問版本的trx_id屬性值與ReadView中的creator_trx_id值相同，意味著當前事務在訪問它自己修改過的記錄，所以該版本可以被當前事務訪問。
• 如果被訪問版本的trx_id屬性值小於ReadView中的min_trx_id值，表明生成該版本的事務在當前事務生成ReadView前已經提交，所以該版本可以被當前事務訪問。
• 如果被訪問版本的trx_id屬性值大於或等於ReadView中的max_trx_id值，表明生成該版本的事務在當前事務生成ReadView後才開啟，所以該版本不可以被當前事務訪問。
• 如果被訪問版本的trx_id屬性值在ReadView的min_trx_id和max_trx_id之間，那就需要判斷一下trx_id屬性值是不是在m_ids列表中，如果在，說明創建ReadView時生成該版本的事務還是活躍的，該版本不可以被訪問；如果不在，說明創建ReadView時生成該版本的事務已經被提交，該版本可以被訪問。

如果某個版本的數據對當前事務不可見的話，那就順著版本鏈找到下一個版本的數據，繼續按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最後一個版本。如果最後一個版本也不可見的話，那麼就意味著該條記錄對該事務完全不可見，查詢結果就不包含該記錄。

Spring事務相關

@Transactional事務不要濫用。事務會影響數據庫的QPS，另外使用事務的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統計修正等。

事務傳播屬性（Propagation）

REQUIRED：（默認屬性）如果存在一個事務，則支持當前事務。如果沒有事務則開啟一個新的事務。 被設置成這個級別時，會為每一個被調用的方法創建一個邏輯事務域。如果前面的方法已經創建了事務，那麼後面的方法支持當前的事務，如果當前沒有事務會重新建立事務。

MANDATORY：支持當前事務，如果當前沒有事務，就拋出異常。

NEVER：以非事務方式執行，如果當前存在事務，則拋出異常。

NOT_SUPPORTED：以非事務方式執行操作，如果當前存在事務，就把當前事務掛起。

REQUIRES_NEW：新建事務，如果當前存在事務，把當前事務掛起。

SUPPORTS：支持當前事務，如果當前沒有事務，就以非事務方式執行。

NESTED：支持當前事務，新增Savepoint點，與當前事務同步提交或回滾。 嵌套事務一個非常重要的概念就是內層事務依賴於外層事務。外層事務失敗時，會回滾內層事務所做的動作。而內層事務操作失敗並不會引起外層事務的回滾。

連接池

為什麼需要連接池？

當併發量很低的時候，連接可以臨時建立，但當服務吞吐量達到幾百、幾千的時候，建立連接connect和銷燬連接close就會成為瓶頸，此時該如何優化呢？

（1）當服務啟動的時候，先建立好若干連接Array[DBClientConnection]；

（2）當請求到達的時候，再從Array中取出一個，執行下游操作，執行完放回；

從而避免反覆的建立和銷燬連接，抵消每次獲取資源的消耗，以提升性能。

除了初始化資源，池化設計還包括如下這些特徵：池子的初始值、池子的活躍值、池子的最大值等，這些特徵可以直接映射到java線程池和數據庫連接池的成員屬性中。

數據庫連接本質就是一個 socket 的連接。數據庫服務端還要維護一些緩存和用戶權限信息之類的 所以佔用了一些內存。我們可以把數據庫連接池是看做是維護的數據庫連接的緩存，以便將來需要對數據庫的請求時可以重用這些連接。為每個用戶打開和維護數據庫連接，尤其是對動態數據庫驅動的網站應用程序的請求，既昂貴又浪費資源。在連接池中，創建連接後，將其放置在池中，並再次使用它，因此不必建立新的連接。如果使用了所有連接，則會建立一個新連接並將其添加到池中。連接池還減少了用戶必須等待建立與數據庫的連接的時間。

存儲引擎

整體架構

Page是整個InnoDB存儲的最基本構件，也是InnoDB磁盤管理的最小單位，與數據庫相關的所有內容都存儲在這種Page結構裡。Page分為幾種類型，常見的頁類型有數據頁（B-tree Node），Undo頁（Undo Log Page），系統頁（System Page）和事務數據頁（Transaction System Page）等。單個Page的大小是16K，每個Page使用一個32位的int值來唯一標識，這也正好對應InnoDB最大64TB的存儲容量（16Kib * 2^32 = 64Tib）。磁盤數據存儲是採用塊的形式存儲的，每個塊的大小為4K，每次IO進行數據讀取時，同一個磁盤塊的數據可以一次性讀取出來。

以下內容整理來自架構師之路公眾號

InnoDB整體架構分為三層：

內存結構(In-Memory Structure)，這一層在MySQL服務進程內；

OS Cache，這一層屬於內核態內存；

磁盤結構(On-Disk Structure)，這一層在文件系統上；

InnoDB整體架構

InnoDB內存結構包含四大核心組件，分別是：

緩衝池(Buffer Pool)

MySQL數據存儲包含內存與磁盤兩個部分；內存緩衝池(buffer pool)以頁為單位，緩存最熱的數據頁(data page)與索引頁(index page)；InnoDB以變種LRU算法管理緩衝池，並能夠解決“預讀失效”與“緩衝池汙染”的問題；

緩衝池緩存表數據與索引數據，把磁盤上的數據加載到緩衝池，避免每次訪問都進行磁盤IO，起到加速訪問的作用。

寫緩衝(Change Buffer)

目的是提升InnoDB性能，加速寫請求，避免每次寫入都進行磁盤IO。

自適應哈希索引(Adaptive Hash Index)

目的是提升InnoDB性能，加速讀請求，減少索引查詢的尋路路徑。

日誌緩衝(Log Buffer)

目的是提升InnoDB性能，極大優化redo日誌性能，並提供了高併發與強一致性的折衷方案。

事務提交後，必須將事務對數據頁的修改刷(fsync)到磁盤上，才能保證事務的ACID特性。刷盤是一個隨機寫操作，隨機寫性能較低，如果每次事務提交都刷盤，會極大影響數據庫的性能。

優化的方法是先寫redo log(write log first)，將隨機寫優化為順序寫；將每次寫優化為批量寫。

redo log是為了保證已提交事務的ACID特性，同時能夠提高數據庫性能的技術。

redo log是一種順序寫，它有三層架構：MySQL應用層：Log Buffer，OS內核層：OS cache和OS文件：log file。

事務提交時，將redo log寫入Log Buffer，就會認為事務提交成功；如果寫入Log Buffer的數據，write入OS cache之前，數據庫崩潰，就會出現數據丟失；如果寫入OS cache的數據，fsync入磁盤之前，操作系統奔潰，也可能出現數據丟失；

策略一：最佳性能(innodb_flush_log_at_trx_commit=0)

每隔一秒，才將Log Buffer中的數據批量write入OS cache，同時MySQL主動fsync。

這種策略，如果數據庫奔潰，有一秒的數據丟失。

策略二：強一致(innodb_flush_log_at_trx_commit=1)

每次事務提交，都將Log Buffer中的數據write入OS cache，同時MySQL主動fsync。

這種策略，是InnoDB的默認配置，為的是保證事務ACID特性。

策略三：折衷(innodb_flush_log_at_trx_commit=2)

每次事務提交，都將Log Buffer中的數據write入OS cache；

每隔一秒，MySQL主動將OS cache中的數據批量fsync。 這是高併發業務，行業內的最佳實踐。

鎖相關

InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖。

表級鎖： MySQL中鎖定粒度最大的一種鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。其鎖定粒度最大，觸發鎖衝突的概率最高，併發度最低，MyISAM和 InnoDB引擎都支持表級鎖。寫時，要加寫鎖：如果表沒有鎖，對錶加寫鎖；否則，入寫鎖隊列；讀時，要加讀鎖：如果表沒有寫鎖，對錶加讀鎖；否則，入讀鎖隊列；表鎖釋放時：如果寫鎖隊列和讀鎖隊列裡都有鎖，寫有更高的優先級，即寫鎖隊列先出列。這麼做的原因是，如果有“大查詢”，可能會導致寫鎖被批量“餓死”，而寫鎖往往釋放很快。

自增鎖是一種特殊的

行級鎖： MySQL中鎖定粒度最小的一種鎖，只針對當前操作的行進行加鎖。 行級鎖能大大減少數據庫操作的衝突。其加鎖粒度最小，併發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。

InnoDB支持的行級鎖，包括如下幾種。

• Record lock（記錄鎖）：對索引項加鎖，鎖定符合條件的行。其他事務不能修改和刪除加鎖項。
• Gap lock（間隙鎖）：對索引項之間的“間隙”加鎖，鎖定記錄的範圍（對第一條記錄前的間隙或最後一條將記錄後的間隙加鎖），不包含索引項本身。其他事務不能在鎖範圍內插入數據，這樣就防止了別的事務新增幻影行。
• Next-key lock（臨鍵鎖）：鎖定索引項本身和索引範圍。即Record Lock和Gap Lock的結合。可解決幻讀問題。InnoDB對於行的查詢使用Next-key lock。當查詢的索引含有唯一屬性時，將next-key lock降級為record key。

意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖佔用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那麼就在表上面添加一個意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。意向共享鎖可以同時並存多個，但是意向排他鎖同時只能有一個存在。

意向鎖是表級鎖，表示的是一種意向，僅僅表示事務正在讀或寫某一行記錄，在真正加行鎖時才會判斷是否衝突。意向鎖是InnoDB自動加的，不需要用戶干預。

意向共享鎖（IS）： 表示事務準備給數據行記入共享鎖，事務在一個數據行加共享鎖前必須先取得該表的IS鎖。

意向排他鎖（IX）： 表示事務準備給數據行加入排他鎖，事務在一個數據行加排他鎖前必須先取得該表的IX鎖。

InnoDB的行級鎖是基於索引實現的，如果查詢語句為命中任何索引，那麼InnoDB會使用表級鎖. 此外，InnoDB的行級鎖是針對索引加的鎖，不針對數據記錄，因此即使訪問不同行的記錄，如果使用了相同的索引鍵仍然會出現鎖衝突，還需要注意的是，在通過

SELECT ...LOCK IN SHARE MODE; 或 SELECT ...FOR UPDATE;

使用鎖的時候，如果表沒有定義任何索引，那麼InnoDB會創建一個隱藏的聚簇索引並使用這個索引來加記錄鎖。

導致雙方都在等待，這就產生了死鎖。

發生死鎖後，InnoDB一般都可以檢測到，並使一個事務釋放鎖回退，另一個則可以獲取鎖完成事務，我們可以採取以上方式避免死鎖：

通過表級鎖來減少死鎖產生的概率；

多個程序儘量約定以相同的順序訪問表（這也是解決併發理論中哲學家就餐問題的一種思路）；

同一個事務儘可能做到一次鎖定所需要的所有資源。

通過show engine innodb status; 能夠看到很多事務與鎖之間的信息，對分析問題十分有幫助。

普通select在讀未提交(Read Uncommitted)，讀提交(Read Committed, RC)，可重複讀(Repeated Read, RR)這三種事務隔離級別下，普通select使用快照讀(snpashot read)，不加鎖，併發非常高；在串行化(Serializable)這種事務的隔離級別下，普通select會升級為select ... in share mode;

加鎖select主要是指：select ... for update，select ... in share mode。如果，在唯一索引(unique index)上使用唯一的查詢條件(unique search condition)，會使用記錄鎖(record lock)，而不會封鎖記錄之間的間隔，即不會使用間隙鎖(gap lock)與臨鍵鎖(next-key lock)；其他的查詢條件和索引條件，InnoDB會封鎖被掃描的索引範圍，並使用間隙鎖與臨鍵鎖，避免索引範圍區間插入記錄；

update與delete和加鎖select類似，如果在唯一索引上使用唯一的查詢條件來update/delete，例如：update t set name=xxx where id=1;也只加記錄鎖；否則，符合查詢條件的索引記錄之前，都會加排他臨鍵鎖(exclusive next-key lock)，來封鎖索引記錄與之前的區間；尤其需要特殊說明的是，如果update的是聚集索引(clustered index)記錄，則對應的普通索引(secondary index)記錄也會被隱式加鎖，這是由InnoDB索引的實現機制決定的：普通索引存儲PK的值，檢索普通索引本質上要二次掃描聚集索引。

insert和update與delete不同，它會用排它鎖封鎖被插入的索引記錄，而不會封鎖記錄之前的範圍。同時，會在插入區間加插入意向鎖(insert intention lock)，但這個並不會真正封鎖區間，也不會阻止相同區間的不同KEY插入。

主從複製

實現讀寫分離

MySQL主備複製原理

• MySQL master 將數據變更寫入二進制日誌( binary log, 其中記錄叫做二進制日誌事件binary log events，可以通過 show binlog events 進行查看)
• MySQL slave 將 master 的 binary log events 拷貝到它的中繼日誌(relay log)
• MySQL slave 重放 relay log 中事件，將數據變更反映它自己的數據。

mysql5.6：按照庫並行複製，建議使用“多庫”架構；

mysql5.7：按照GTID並行複製；

MySQL並行複製，縮短主從同步時延的方法，體現著這樣的一些架構思想：

• 多線程是一種常見的縮短執行時間的方法；例如，很多crontab可以用多線程，切分數據，並行執行。

• 多線程併發分派任務時，必須保證冪等性：MySQL提供了“按照庫冪等”，“按照commit_id冪等”兩種方式，很值得借鑑；例如，群消息，可以按照group_id冪等；用戶消息，可以按照user_id冪等。

canal 工作原理

• canal 模擬 MySQL slave 的交互協議，偽裝自己為 MySQL slave ，向 MySQL master 發送dump 協議
• MySQL master 收到 dump 請求，開始推送 binary log 給 slave (即 canal )
• canal 解析 binary log 對象(原始為 byte 流)

show binary logs;

show binlog events in 'binlog.000039';

mysqlbinlog --start-position=234 --stop-position=507 --base64-output="decode-rows" -v /var/lib/mysql/binlog.000039

在binlog落盤之後，MySQL就會認為事務的持久化已經完成(在這個時刻之後，就算數據庫發生了崩潰都可以在重啟後正確的恢復該事務)。但是該事務產生的數據變更被別的客戶端查詢出來還需要在commit全部完成之後。MySQL會在binlog落盤之後會立即將新增的binlog發送給訂閱者以儘可能的降低主從延遲。但由於多線程時序等原因，當訂閱者在收到該binlog之後立即發起一個查詢操作，可能不會查詢到任何該事務產生的數據變更(因為此時該事務所處線程可能尚未完成最後的commit步驟)。如果應用需要根據binlog作為一些業務邏輯的觸發點，還是需要考慮引入一些延時重試機制或者重新考慮合適的實現架構。

分庫分表相關

垂直拆分

根據數據庫裡面數據表的相關性進行拆分。 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。

簡單來說垂直拆分是指數據表列的拆分，把一張列比較多的表拆分為多張表。

水平拆分

水平拆分是指數據錶行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存放。

1. 確定一個路由算法，例如hash取模；或者根據時間範圍
2. 將單庫中的數據，通過這個路由算法遷移到多庫中去，以實現單庫數據量的減少；
3. 通過這個路由算法尋找數據（讀）；
4. 通過這個路由算法插入數據（寫）；

分庫後將數據分佈到不同的數據庫實例（甚至物理機器）上，以達到降低數據量，增強性能的擴容目的。可以使用數據冗餘這種反範式設計來滿足分庫後不同維度的查詢需求，為了屏蔽“冗餘數據”對服務帶來的複雜性，可以優化為線下異步雙寫法（使用canel）。

水平拆分的實現方案

客戶端代理： 分庫邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實現。

中間件代理： 在應用和數據中間加了一個代理層。分庫邏輯統一維護在中間件服務中。

水平拆分的主鍵ID

性能優化的建議

超 100 萬行的批量寫 (UPDATE,DELETE,INSERT) 操作,要分批多次進行操作

大批量操作可能會造成嚴重的主從延遲，主從環境中,大批量操作可能會造成嚴重的主從延遲，大批量的寫操作一般都需要執行一定長的時間， 而只有當主庫上執行完成後，才會在其他從庫上執行，所以會造成主庫與從庫長時間的延遲情況

binlog 日誌為 row 格式時會產生大量的日誌，大批量寫操作會產生大量日誌，特別是對於 row 格式二進制數據而言，由於在 row 格式中會記錄每一行數據的修改，我們一次修改的數據越多，產生的日誌量也就會越多，日誌的傳輸和恢復所需要的時間也就越長，這也是造成主從延遲的一個原因

避免產生大事務操作，大批量修改數據，一定是在一個事務中進行的，這就會造成表中大批量數據進行鎖定，從而導致大量的阻塞，阻塞會對 MySQL 的性能產生非常大的影響。

特別是長時間的阻塞會佔滿所有數據庫的可用連接，這會使生產環境中的其他應用無法連接到數據庫，因此一定要注意大批量寫操作要進行分批。

拆分複雜的大 SQL 為多個小 SQL

大 SQL 邏輯上比較複雜，需要佔用大量 CPU 進行計算的 SQL，MySQL 中，一個 SQL 只能使用一個 CPU 進行計算，SQL 拆分後可以通過並行執行來提高處理效率。

避免使用子查詢，可以把子查詢優化為 join 操作

通常子查詢在 in 子句中，且子查詢中為簡單 SQL(不包含 union、group by、order by、limit 從句) 時,才可以把子查詢轉化為關聯查詢進行優化。子查詢性能差的原因，子查詢的結果集無法使用索引，通常子查詢的結果集會被存儲到臨時表中，不論是內存臨時表還是磁盤臨時表都不會存在索引，所以查詢性能會受到一定的影響。特別是對於返回結果集比較大的子查詢，其對查詢性能的影響也就越大。由於子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的 CPU 和 IO 資源，產生大量的慢查詢。

參考鏈接

1. http://www.jiangxinlingdu.com/mysql/2019/06/07/binlog.html
2. https://www.jiqizhixin.com/articles/2018-12-05-14
3. https://blog.csdn.net/hao_yunfeng/article/details/82392261
4. https://www.jianshu.com/p/5dd5993f981b
5. https://www.bilibili.com/video/av59851676?p=2
6. https://blog.csdn.net/csdnlijingran/article/details/102309593
7. https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/%E4%B8%80%E6%9D%A1sql%E8%AF%AD%E5%8F%A5%E5%9C%A8mysql%E4%B8%AD%E5%A6%82%E4%BD%95%E6%89%A7%E8%A1%8C%E7%9A%84.md
8. https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95.md
9. 《MySQL 實戰45講》
10. https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/MySQL%20Index.md
11. https://www.jianshu.com/p/54c6d5db4fe6
12. https://zhuanlan.zhihu.com/p/27700617
13. https://blog.csdn.net/u011240877/article/details/80490663
14. https://blog.csdn.net/lisuyibmd/article/details/53004848
15. https://juejin.im/post/5b55b842f265da0f9e589e79
16. https://blog.csdn.net/qq_25188255/article/details/81316498
17. MySql事務雜談
18. https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/MySQL.md
19. https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485185&idx=1&sn=66ef08b4ab6af5757792223a83fc0d45&chksm=cea248caf9d5c1dc72ec8a281ec16aa3ec3e8066dbb252e27362438a26c33fbe842b0e0adf47&token=79317275&lang=zh_CN#rd
20. https://blog.csdn.net/qq_34337272/article/details/80611486
21. 架構師之路公眾號
22. https://segmentfault.com/a/1190000006158186
23. https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485117&idx=1&sn=92361755b7c3de488b415ec4c5f46d73&chksm=cea24976f9d5c060babe50c3747616cce63df5d50947903a262704988143c2eeb4069ae45420&token=79317275&lang=zh_CN#rd

閱讀更多 突破程序員 的文章

關鍵字: 拆分 SQL 數據結構