正文

mysql快照讀（mysql快照讀原理）

發(fā)布時間：2023-04-08 06:12:51 稿源：創(chuàng)意嶺閱讀： 73

大家好！今天讓創(chuàng)意嶺的小編來大家介紹下關(guān)于mysql快照讀的問題，以下是小編對此問題的歸納整理，讓我們一起來看看吧。

開始之前先推薦一個非常厲害的Ai人工智能工具，一鍵生成原創(chuàng)文章、方案、文案、工作計劃、工作報告、論文、代碼、作文、做題和對話答疑等等

只需要輸入關(guān)鍵詞，就能返回你想要的內(nèi)容，越精準，寫出的就越詳細，有微信小程序端、在線網(wǎng)頁版、PC客戶端

官網(wǎng)：https://ai.de1919.com。

創(chuàng)意嶺作為行業(yè)內(nèi)優(yōu)秀的企業(yè)，服務(wù)客戶遍布全球各地，如需了解SEO相關(guān)業(yè)務(wù)請撥打電話175-8598-2043，或添加微信：1454722008

本文目錄:

1、mysql是如何實現(xiàn)可重復讀的？
2、「MySQL」這個詞怎么讀？
3、如何優(yōu)化因 MYSQL 讀寫頻繁，負載過高導致的CPU高占用率
4、mysql在RR隔離級別下，某些特定場景下出現(xiàn)幻讀

mysql快照讀（mysql快照讀原理）

一、mysql是如何實現(xiàn)可重復讀的？

一個事務(wù)要更新一行，如果剛好有另外一個事務(wù)擁有這一行的行鎖，會被鎖住，進入等待狀態(tài)。既然進入了等待狀態(tài)，那么等到這個事務(wù)自己獲取到行鎖要更新數(shù)據(jù)的時候，它讀到的值又是什么呢?

可重復讀隔離級別下，事務(wù)在啟動的時候就“拍了個整個庫的快照”。如果一個庫有100G，那么我啟動一個事務(wù)，MySQL就要拷⻉100G的數(shù)據(jù)出來，這個過程得多慢啊。但是平時事務(wù)執(zhí)行起來卻是非?？斓摹２皇侨靠截惓鰜砟鞘窃趺磳崿F(xiàn)的呢?

InnoDB里面每個事務(wù)有一個唯一的事務(wù)ID，叫作transaction id。它是在事務(wù)開始的時候向InnoDB的事務(wù)系統(tǒng)申請的，是按申請順序嚴格遞增的。

而每行數(shù)據(jù)也都是有多個版本的。每次事務(wù)更新數(shù)據(jù)的時候，都會生成一個新的數(shù)據(jù)版本，并且把transaction id賦值給這個數(shù) 據(jù)版本的事務(wù)ID，記為row trx_id。同時，舊的數(shù)據(jù)版本要保留，并且在新的數(shù)據(jù)版本中，能夠有信息可以直接拿到它。

數(shù)據(jù)表中的一行記錄，其實可能有多個版本(row)，每個版本有自己的row trx_id。

圖中虛線框里是同一行數(shù)據(jù)的4個版本，當前最新版本是V4，k的值是22，它是被transaction id 為25的事務(wù)更新的，因此它的row trx_id也是25。語句更新會生成undo log(回滾日志)，圖中的三個虛線箭頭，就是undo log。

按照可重復讀的定義，一個事務(wù)啟動的時候，能夠看到所有已經(jīng)提交的事務(wù)結(jié)果。但是之后，這個事務(wù)執(zhí)行期間，其他事務(wù)的更新對它不可⻅。

一個事務(wù)只需要在啟動的時候聲明說，“以我啟動的時刻為準，如果一個數(shù)據(jù)版本是在我啟動之前生成的，就認;如果是我啟動以后才生成的，我就不認，我必須要找到它的上一個版本”。

如果“上一個版本”也不可⻅，那就得繼續(xù)往前找。如果是這個事務(wù)自己更新的數(shù)據(jù)，它自己還是要認的。

在實現(xiàn)上， InnoDB為每個事務(wù)構(gòu)造了一個數(shù)組，用來保存這個事務(wù)啟動瞬間，當前正在“活躍”的所有事務(wù)ID?！盎钴S”指的就是，啟動了但還沒提交。數(shù)組里面事務(wù)ID的最小值記為低水位，當前系統(tǒng)里面已經(jīng)創(chuàng)建過的事務(wù)ID的最大值加1記為高水位。這個視圖數(shù)組和高水位，就組成了當前事務(wù)的一致性視圖(read-view)。而數(shù)據(jù)版本的可⻅性規(guī)則，就是基于數(shù)據(jù)的row trx_id和這個一致性視圖的對比結(jié)果得到的。

InnoDB利用了“所有數(shù)據(jù)都有多個版本”的這個特性，實現(xiàn)了“秒級創(chuàng)建快照”的能力。

回到我們最開始的表格，看看最后執(zhí)行的結(jié)果是多少。做如下假設(shè):

事務(wù)A的視圖數(shù)組就是[99,100], 事務(wù)B的視圖數(shù)組是[99,100,101], 事務(wù)C的視圖數(shù)組是[99,100,101,102]。為了簡化分析，我先把其他干擾語句去掉，只畫出跟事務(wù)A查詢邏輯有關(guān)的操作:

第一個有效更新是事務(wù)C，把數(shù)據(jù)從(1,1)改成了(1,2)。這時候，這個數(shù)據(jù)的最新版本的row trx_id是102，而90這個版本已經(jīng)成為了歷史版本。第二個有效更新是事務(wù)B，把數(shù)據(jù)從(1,2)改成了(1,3)。這時候，這個數(shù)據(jù)的最新版本(即row trx_id)是101，而102又成為了歷史版本。

事務(wù)B的update語句，如果按照一致性讀，好像結(jié)果不對哦?

事務(wù)B的視圖數(shù)組是先生成的，之后事務(wù)C才提交，不是應(yīng)該看不⻅(1,2)嗎，怎么能算出(1,3)來?

事務(wù)B在更新之前查詢一次數(shù)據(jù)，這個查詢返回的k的值確實是1。但是，當它要去更新數(shù)據(jù)的時候，就不能再在歷史版本上更新了，否則事務(wù)C的更新就丟失了。因此，事務(wù)B此時的set k=k+1是在(1,2)的基礎(chǔ)上進行的操作。所以，這里就用到了這樣一條規(guī)則:更新數(shù)據(jù)都是先讀后寫的，而這個讀，只能讀當前的值，稱為 “當前讀” ( current read )。

在更新的時候，當前讀拿到的數(shù)據(jù)是(1,2)，更新后生成了新版本的數(shù)據(jù)(1,3)，這個新版本的row trx_id是101。

所以，在執(zhí)行事務(wù)B查詢語句的時候，一看自己的版本號是101，最新數(shù)據(jù)的版本號也是101，是自己的更新，可以直接使用，所以查詢得到的k的值是3。

select語句如果加鎖，也是當前讀。

如果把事務(wù)A的查詢語句select * from t where id=1修改一下，加上lock in share mode 或 for update，也都可以讀到版本號是101的數(shù)據(jù)，返回的k的值是3。下面這兩個select語句，就是分別加了讀鎖(S鎖，共享鎖)和寫鎖(X鎖，排他鎖)。

事務(wù)C’的不同是，更新后并沒有⻢上提交，在它提交前，事務(wù)B的更新語句先發(fā)起了。前面說過了，雖然事務(wù)C’還沒提交，但是(1,2)這個版本也已經(jīng)生成了，并且是當前的最新版本。那么，事務(wù)B的更新語句會怎么處理呢?

兩階段鎖協(xié)議，事務(wù)C’沒提交，也就是說(1,2)這個版本上的寫鎖還沒釋放。而事務(wù)B是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務(wù)C’釋放這個鎖，才能繼續(xù)它的當前讀。

回到最初的問題，事務(wù)的可重復讀的能力是怎么實現(xiàn)的?

二、「MySQL」這個詞怎么讀？

My S-Q-L，不專業(yè)的可以讀作 my sequel(?？?，但絕非是賣塞科兒

mysql快照讀（mysql快照讀原理）

其實很多老師并不知道他的讀法，當時聯(lián)系的數(shù)據(jù)庫是dbase, foxbase, foxpro, access, MS SQL server (oracle)， 15年前，mysql類似于幼兒園的游戲。此外，SQL server還被用于遵從SQL 92.99標準，在這方面mysql不太正式，因此您可以看到事務(wù)隔離級別。這樣也很不錯，mysql有很多非正式的，但更為方便的語法。

具體可以看他的專業(yè)書。

mysql快照讀（mysql快照讀原理）

MySQL包含許多特性，特別是在MySQL 5.0和5.1中，添加了一些主要特性和特性。MySQL的特定功能或語句非常豐富。這就是為什么MySQL核心技術(shù)手冊(版本2)是有價值的?！癕ySQL核心技術(shù)手冊(第二版)”“需要讓讀者快速找到具體的細節(jié)，無論是SQL關(guān)鍵字還是MySQL命令行選項，特定的API信息或?qū)嵱贸绦蚬芾怼?/p>

《“MySQL核心技術(shù)手冊(第二版)”“覆蓋適用于所有命令和MySQL 5.1版本的編程信息，包括一些新特性和語言界面，對于大多數(shù)語句和函數(shù)，使用示例給出。

mysql快照讀（mysql快照讀原理）

MySQL核心技術(shù)手冊(版本2)為MySQL語句、函數(shù)、配置選項和實用程序提供了完整的手冊。為了幫助初學者入門，我們提供了一些教程。為PHP、Perl和C語言提供了應(yīng)用程序編程接口(api)。在每個章節(jié)的API開頭提供了一個簡要的指南。新副本、觸發(fā)器和存儲過程部分。提供了一些MySQL的實際示例。一些有用的技巧可以幫助讀者克服困難。

三、如何優(yōu)化因 MYSQL 讀寫頻繁，負載過高導致的CPU高占用率

診斷思路

mpstat -P ALL 1，查看cpu使用情況，主要消耗在sys即os系統(tǒng)調(diào)用上

mysql快照讀（mysql快照讀原理）

perf top，cpu主要消耗在_spin_lock

mysql快照讀（mysql快照讀原理）

生成perf report查看詳細情況

mysql快照讀（mysql快照讀原理）

CPU主要消耗在mutex爭用上，說明有鎖熱點。

采用pt-pmp跟蹤mysqld執(zhí)行情況，熱點主要集中在mem_heap_alloc和mem_heap_free上。

mysql快照讀（mysql快照讀原理）

Pstack提供更詳細的API調(diào)用棧

Innodb在讀取數(shù)據(jù)記錄時的API路徑為

row_search_for_mysql --》row_vers_build_for_consistent_read --》mem_heap_create_block_func --》mem_area_alloc --》malloc --》 _L_unlock_10151 --》__lll_unlock_wait_private

row_vers_build_for_consistent_read會陷入一個死循環(huán)，跳出條件是該條記錄不需要快照讀或者已經(jīng)從undo中找出對應(yīng)的快照版本，每次循環(huán)都會調(diào)用mem_heap_alloc/free。

而該表的記錄更改很頻繁，導致其undo history list比較長，搜索快照版本的代價更大，就會頻繁的申請和釋放堆內(nèi)存。

Linux原生的內(nèi)存庫函數(shù)為ptmalloc，malloc/free調(diào)用過多時很容易產(chǎn)生鎖熱點。

當多條 SQL 并發(fā)執(zhí)行時，會最終觸發(fā)os層面的spinlock，導致上述情形。

解決方案

將mysqld的內(nèi)存庫函數(shù)替換成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并發(fā)調(diào)用。

修改my.cnf，添加如下參數(shù)并重啟

[mysqld_safe]malloc-lib=tcmalloc

上周五早上7點執(zhí)行的操作，到現(xiàn)在超過72小時，期間該實例沒有再出現(xiàn)cpu長期飆高的情形。

以下是修改前后cpu使用率對比

mysql快照讀（mysql快照讀原理）

四、mysql在RR隔離級別下，某些特定場景下出現(xiàn)幻讀

見圖，主要是select xx for update，又或者是update語句更新了，使用了當前讀。所以后面再次select（13行）出現(xiàn)幻讀，如果只是select的話（10行不是update，是個select），是不會出現(xiàn)幻讀的情況，因為符合mvcc規(guī)則，用的還是一開始的快照。

todo：看下10行是update的情況下的內(nèi)容：SELECT * FROM information_schema . INNODB_TRX

如果10行，update的id為1，則不會出現(xiàn)幻讀的情況，這里因為update的時候把session2里的更新到了

以上就是關(guān)于mysql快照讀相關(guān)問題的回答。希望能幫到你，如有更多相關(guān)問題，您也可以聯(lián)系我們的客服進行咨詢，客服也會為您講解更多精彩的知識和內(nèi)容。