mysql 給幾個主鍵值 批量校驗是否存在_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是否存在(相等)，遇到範圍查詢(>、,

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)：當一個處在部分送出的狀态的事務将修改過的資料都同步到磁盤上之後，該事務處在了送出的狀态。

事務狀态變化

并發事務帶來哪些問題?

髒讀(Dirty read): 當一個事務正在通路資料并且對資料進行了修改，而這種修改還沒有送出到資料庫中，這時另外一個事務也通路了這個資料，然後使用了這個資料。因為這個資料是還沒有送出的資料，那麼另外一個事務讀到的這個資料是“髒資料”，依據“髒資料”所做的操作可能是不正确的。 丢失修改(Lost to modify): 指在一個事務讀取一個資料時，另外一個事務也通路了該資料，那麼在第一個事務中修改了這個資料後，第二個事務也修改了這個資料。這樣第一個事務内的修改結果就被丢失，是以稱為丢失修改。 例如：事務1讀取某表中的資料A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果A=19，事務1的修改被丢失。 不可重複讀(Unrepeatableread): 指在一個事務内多次讀同一資料。在這個事務還沒有結束時，另一個事務也通路該資料。那麼，在第一個事務中的兩次讀資料之間，由于第二個事務的修改導緻第一個事務兩次讀取的資料可能不太一樣。這就發生了在一個事務内兩次讀到的資料是不一樣的情況，是以稱為不可重複讀。 幻讀(Phantom read): 幻讀與不可重複讀類似。它發生在一個事務(T1)讀取了幾行資料，接着另一個并發事務(T2)插入了一些資料時。在随後的查詢中，第一個事務(T1)就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，是以稱為幻讀。

不可重複讀和幻讀差別：

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

舍棄一部分隔離性來換取一部分性能在這裡就展現在：設立一些隔離級别，隔離級别越低，越嚴重的問題就越可能發生。是以産生了一個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引擎都支援表級鎖。寫時，要加寫鎖：如果表沒有鎖，對表加寫鎖；否則，入寫鎖隊列；讀時，要加讀鎖：如果表沒有寫鎖，對表加讀鎖；否則，入讀鎖隊列；表鎖釋放時：如果寫鎖隊列和讀鎖隊列裡都有鎖，寫有更高的優先級，即寫鎖隊列先出列。這麼做的原因是，如果有“大查詢”，可能會導緻寫鎖被批量“餓死”，而寫鎖往往釋放很快。

自增鎖是一種特殊的表級别鎖(table-level lock)，專門針對事務插入AUTO_INCREMENT類型的列。如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

行級鎖： 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 流)

binlog檢視

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

UUID：不适合作為主鍵，因為太長了，并且無序不可讀，查詢效率低。比較适合用于生成唯一的名字的标示比如檔案的名字。 資料庫自增 id : 兩台資料庫分别設定不同步長，生成不重複ID的政策來實作高可用。這種方式生成的 id 有序，但是需要獨立部署資料庫執行個體，成本高，還會有性能瓶頸。 利用 redis 生成 id : 性能比較好，靈活友善，不依賴于資料庫。但是，引入了新的元件造成系統更加複雜，可用性降低，編碼更加複雜，增加了系統成本。 Twitter的snowflake算法 ：第一位為未使用，接下來的41位為毫秒級時間(41位的長度可以使用69年)，然後是5位data center Id和5位worker Id(10位的長度最多支援部署1024個節點) ，最後12位是毫秒内的計數(12位的計數順序号支援每個節點每毫秒産生4096個ID序号)。一共加起來剛好64位，為一個Long型(轉換成字元串後長度最多19)。snowflake生成的ID整體上按照時間自增排序，并且整個分布式系統内不會産生ID碰撞(由datacenter和workerId作區分)，并且效率較高。經測試snowflake每秒能夠産生26萬個ID。 美團的Leaf分布式ID生成系統 ：Leaf 是美團開源的分布式ID生成器，能保證全局唯一性、趨勢遞增、單調遞增、資訊安全，裡面也提到了幾種分布式方案的對比，但也需要依賴關系資料庫、Zookeeper等中間件。感覺還不錯。美團技術團隊的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

性能優化的建議

超 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