安基網 首頁 系統 數據庫 查看內容

大牛總結的MySQL鎖優化,寫得太好了

2019-10-20 10:17| 投稿: xiaotiger |來自: 互聯網


免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!

摘要: 隨著 IT 技術的飛速發展,各種技術層出不窮,讓人眼花繚亂。盡管技術在不斷更新換代,但是有些技術依舊被一代代 IT 人使用至今。圖片來自 PexelsMySQL 就是其中之一,它經歷了多個版本迭代。數據庫鎖是 MySQL 數據引 ...

【51CTO.com原創稿件】隨著 IT 技術的飛速發展,各種技術層出不窮,讓人眼花繚亂。盡管技術在不斷更新換代,但是有些技術依舊被一代代 IT 人使用至今。


MySQL 就是其中之一,它經歷了多個版本迭代。數據庫鎖是 MySQL 數據引擎的一部分,今天我們就一起來學習 MySQL 的數據庫鎖和它的優化。

MySQL 鎖分類

當多個事務或者進程訪問同一個資源的時候,為了保證數據的一致性,就需要用到鎖機制。

從鎖定資源的角度來看,MySQL 中的鎖分為:

  • 表級鎖
  • 行級鎖
  • 頁面鎖

表級鎖:對整張表加鎖。開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突的概率最高,并發度最低。

行級鎖:對某行記錄加鎖。開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖沖突的概率最低,并發度也最高。

頁面鎖:開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間,并發度一般。

在實際開發過程中,主要會使用到表級鎖和行級鎖兩種。既然鎖是針對資源的,那么這些資源就是數據,在 MySQL 提供插件式存儲引擎對數據進行存儲。

插件式存儲引擎的好處是,開發人員可以根據需要選擇適合的存儲引擎。

在眾多的存儲引擎中,有兩種引擎被比較多的使用,他們分別是:

  • MyISAM 存儲引擎,它不支持事務、表鎖設計,支持全文索引,主要面向一些在線分析處理(OLAP)數據庫應用。說白了主要就是查詢數據,對數據的插入,更新操作比較少。
  • InnoDB 存儲引擎,它支持事務,其設計目標主要面向在線事務處理(OLTP)的應用。

其特點是行鎖設計、支持外鍵,并支持類似于 Oracle 的非鎖定讀,即默認讀取操作不會產生鎖。

簡單來說,就是對數據的插入,更新操作比較多。從 MySQL 數據庫 5.5.8 版本開始,InnoDB 存儲引擎是默認的存儲引擎。

上面兩種存儲引擎在處理多進程數據操作的時候是如何表現的,就是我們接下來要討論的問題。

為了讓整個描述更加清晰,我們將表級鎖和行級鎖以及 MyISAM,InnoDB 存儲引擎,就形成了一個 2*2 的象限。



2*2 表行鎖,MyISAM,InnoDB 示意圖

由于 MyISAM 存儲引擎不支持行級鎖,實際上后面討論的問題會圍繞三個象限的討論展開。

從內容上來看,InnoDB 作為使用最多的存儲引擎遇到的問題和值得注意的地方較多,也是本文的重點。

MyISAM 存儲引擎和表級鎖

首先,來看第一象限的內容:



2*2 表行鎖,MyISAM,InnoDB 示意圖-第一象限

MyISAM 存儲引擎支持表級鎖,并且支持兩種鎖模式:

  • 對 MyISAM 表的讀操作(共享鎖),不會阻塞其他進程對同一表的讀請求,但會阻塞對其的寫請求。當讀鎖釋放后,才會執行其他進程的寫操作。
  • 對 MyISAM 表的寫操作(排他鎖),會阻塞其他進程對同一表的讀寫操作,當該鎖釋放后,才會執行其他進程的讀寫操作。

MyISAM 優化建議

在使用 MyISAM 存儲引擎時。執行 SQL 語句,會自動為 SELECT 語句加上共享鎖,為 UDI(更新,刪除,插入)操作加上排他鎖。

由于這個特性在多進程并發插入同一張表的時候,就會因為排他鎖而進行等待。

因此可以通過配置 concurrent_insert 系統變量,來控制其并發的插入行為。

①concurrent_insert=0 時,不允許并發插入。

②concurrent_insert=1 時,如果 MyISAM 表中沒有空洞(即表中沒有被刪除的行),允許一個進程讀表時,另一個進程向表的尾部插入記錄(MySQL 默認設置)。

注:空洞是行記錄被刪除以后,只是被標記為“已刪除”其存儲空間沒有被回收,也就是說沒有被物理刪除。由另外一個進程,異步對這個數據進行刪除。

因為空間長度問題,刪除以后的物理空間不能被新的記錄所使用,從而形成了空洞。

③concurrent_insert=2 時,無論 MyISAM 表中有沒有空洞,都允許在表尾并發插入記錄。

如果在數據插入的時候,沒有并發刪除操作的話,可以嘗試把 concurrent_insert 設置為 1。

反之,在數據插入的時候有刪除操作且量較大時,也就是會產生“空洞”的時候,就需要把 concurrent_insert 設置為 2。

另外,當一個進程請求某個 MyISAM 表的讀鎖,另一個進程也請求同一表的寫鎖。

即使讀請求先到達,寫請求后到達,寫請求也會插到讀請求之前。因為 MySQL 的默認設置認為,寫請求比讀請求重要。

我們可以通過 low_priority_updates 來調節讀寫行為的優先級:

  • 數據庫以讀為主時,要優先保證查詢性能時,可通過 low_priority_updates=1 設置讀優先級高于寫優先級。
  • 數據庫以寫為主時,則不用設置 low_priority_updates 參數。

InnoDB 存儲引擎和表級鎖

再來看看第二象限的內容:



2*2 表行鎖,MyISAM,InnoDB 示意圖-第二象限

InnoDB 存儲引擎表鎖。當沒有對數據表中的索引數據進行查詢時,會執行表鎖操作。

上面是 InnoDB 實現行鎖,同時它也可以實現表鎖。其方式就是意向鎖(Intention Locks)。

這里介紹兩種意向鎖:

  • 意向共享鎖(IS):事務打算給數據行加行共享鎖,事務在給一個數據行加共享鎖前,必須先取得該表的 IS 鎖。
  • 意向排他鎖(IX):事務打算給數據行加行排他鎖,事務在給一個數據行加排他鎖前,必須先取得該表的 IX 鎖。

注:意向共享鎖和意向排他鎖是數據庫主動加的,不需要我們手動處理。對于 UPDATE、DELETE 和 INSERT 語句,InnoDB 會自動給數據集加排他鎖。

InnoDB表鎖的實現方式:假設有一個表 test2,有兩個字段分別是 id 和 name。

沒有設置主鍵同時也沒有設置任何索引(index)如下:



InnoDB 表鎖實現方式圖

InnoDB 存儲引擎和行級鎖

第四象限我們使用的比較多,討論的內容也相對多些:



2*2 表行鎖,MyISAM,InnoDB 示意圖-第四象限

InnoDB 存儲引擎行鎖,當數據查詢時針對索引數據進行時,會使用行級鎖。

共享鎖(S):當一個事務讀取一條記錄的時候,不會阻塞其他事務對同一記錄的讀請求,但會阻塞對其的寫請求。當讀鎖釋放后,才會執行其他事務的寫操作。

例如:select … lock in share mode

排他鎖(X):當一個事務對一條記錄進行寫操作時,會阻塞其他事務對同一表的讀寫操作,當該鎖釋放后,才會執行其他事務的讀寫操作。

例如:select … for update

行鎖的實現方式:假設有一個表 test1,有兩個字段分別是 id 和 name。

id 作為主鍵同時也是 table 的索引(index)如下:



InnoDB 行鎖實現方式圖

在高并發的情況下,多個事務同時請求更新數據,由于資源被占用等待事務增多。

如此,會造成性能問題,可以通過 innodb_lock_wait_timeout 來解決。innodb_lock_wait_timeout 是事務等待獲取資源的最長時間,單位為秒。如果超過時間還未分配到資源,則會返回應用失敗。

四種鎖的兼容情況:



共享鎖,排他鎖,意向共享鎖,意向排他鎖兼容圖例

如果一個事務請求的鎖模式與當前的鎖兼容, InnoDB 就將請求的鎖授予該事務;反之, 如果兩者不兼容,該事務就要等待鎖釋放。

間隙鎖

前面談到行鎖是針對一條記錄進行加鎖。當對一個范圍內的記錄加鎖的時候,我們稱之為間隙鎖。

當使用范圍條件索引數據時,InnoDB 會對符合條件的數據索引項加鎖。對于鍵值在條件范圍內但并不存在的記錄,叫做“間隙(GAP)”,InnoDB 也會對這個“間隙”加鎖,這就是間隙鎖。間隙鎖和行鎖合稱(Next-Key鎖)。

如果表中只有 11 條記錄,其 id 的值分別是 1,2,...,10,11 下面的 SQL:

Select * from table_gapwhere id > 10 for update;

這是一個范圍條件的檢索,InnoDB 不僅會對符合條件的 id 值為 10 的記錄加鎖,會對 id 大于 10 的“間隙”加鎖,即使大于 10 的記錄不存在,例如 12,13。

InnoDB 使用間隙鎖的目的:

  • 一方面是為了防止幻讀。對于上例,如果不使用間隙鎖,其他事務插入了 id 大于 10 的任何記錄,本事務再次執行 select 語句,就會發生幻讀。
  • 另一方面,也是為了滿足恢復和復制的需要。



間隙鎖圖

死鎖

兩個事務都需要獲得對方持有的排他鎖才能繼續完成任務,這種互相等待對方釋放資源的情況就是死鎖。



死鎖圖

檢測死鎖:InnoDB 存儲引擎能檢測到死鎖的循環依賴并立即返回一個錯誤。

死鎖恢復:死鎖發生以后,只有部分或完全回滾其中一個事務,才能打破死鎖。

InnoDB 方法是,將持有最少行級排他鎖的事務回滾。在應用程序設計時必須考慮處理死鎖,多數情況下重新執行因死鎖回滾的事務即可。

避免死鎖:

  • 在事務開始時,如果有記錄要修改,先使用 SELECT... FOR UPDATE 語句獲取鎖,即使這些修改語句是在后面執行。
  • 在事務中,如果要更新記錄,直接申請排他鎖。而不是查詢時申請共享鎖、更新時再申請排他鎖。

這樣做會導致,當申請排他鎖時,其他事務可能已經獲得了相同記錄的共享鎖,從而造成鎖沖突,甚至死鎖。

簡單來說,如果你要更新記錄要做兩步操作,第一步查詢,第二步更新。就不要第一步上共享鎖,第二部上排他鎖了,直接在第一步就上排他鎖,搶占先機。

  • 如果事務需要鎖定多個表,那么盡量按照相同的順序使用加鎖語句,可以降低產生死鎖的機會。
  • 通過 SELECT ... LOCK INSHARE MODE(共享鎖)獲取行的讀鎖后,如果當前事務再需要對該記錄進行更新操作,則很有可能造成死鎖。所以,如果要對行記錄進行修改,直接上排他鎖。
  • 改變事務隔離級別(事務隔離級別在后面詳細說明)。

MySQL 鎖定情況的查詢

在實際開發中無法避免數據被鎖的問題,那么我們可以通過哪些手段來查詢鎖呢?

表級鎖可以通過兩個變量的查詢:

  • Table_locks_immediate,產生表級鎖的次數。
  • Table_locks_waited,數顯表級鎖而等待的次數。

行級鎖可以通過下面幾個變量查詢:

  • Innodb_row_lock_current_waits,當前正在等待鎖定的數量。
  • Innodb_row_lock_time(重要),從系統啟動到現在鎖定總時長。
  • Innodb_row_lock_time_avg(重要),每次等待所花平均時間。
  • Innodb_row_lock_time_max,從系統啟動到現在等待最長的一次花費時間。
  • Innodb_row_lock_waits(重要),從系統啟動到現在總共等待的次數。

MySQL 事務隔離級別

前面講的死鎖是因為并發訪問數據庫造成。當多個事務同時訪問數據庫,做并發操作的時候會發生以下問題。

臟讀(dirty read),一個事務在處理過程中,讀取了另外一個事務未提交的數據。未提交的數據稱之為臟數據。



臟讀例子

不可重復讀(non-repeatable read),在事務范圍內,多次查詢某條記錄,每次得到不同的結果。

第一個事務中的兩次讀取數據之間,由于第二個事務的修改,第一個事務兩次讀到的數據可能不一樣。



不可重復讀例子

幻讀(phantom read),是事務非獨立執行時發生的一種現象。




幻讀的例子

在同一時間點,數據庫允許多個并發事務,同時對數據進行讀寫操作,會造成數據不一致性。



四種隔離級別,解決事務并發問題對照圖

隔離性就是用來防止這種數據不一致的。事務隔離根據級別不同,從低到高包括:

  • 讀未提交(read uncommitted):它是最低的事務隔離級別,一個事務還沒提交時,它做的變更就能被別的事務看到。有臟讀的可能性。
  • 讀提交(read committed):保證一個事物提交后才能被另外一個事務讀取。另外一個事務不能讀取該事物未提交的數據。可避免臟讀的發生,但是可能會造成不可重復讀。
  • 可重復讀(repeatable read MySQL 默認方式):多次讀取同一范圍的數據會返回第一次查詢的快照,即使其他事務對該數據做了更新修改。事務在執行期間看到的數據前后必須是一致的。
  • 串行化(serializable):是最可靠的事務隔離級別。“寫”會加“排他鎖”,“讀”會加“共享鎖”。

當出現讀寫鎖沖突的時候,后訪問的事務必須等前一個事務執行完成,所以事務執行是串行的。可避免臟讀、不可重復讀、幻讀。

InnoDB 優化建議

從鎖機制的實現方面來說,InnoDB 的行級鎖帶來的性能損耗可能比表級鎖要高一點,但在并發方面的處理能力遠遠優于 MyISAM 的表級鎖。這也是大多數公司的 MySQL 都是使用 InnoDB 模式的原因。

但是,InnoDB 也有脆弱的一面,下面提出幾個優化建議供大家參考:

  • 盡可能讓數據檢索通過索引完成,避免 InnoDB 因為無法通過索引加行鎖,而導致升級為表鎖的情況。換句話說就是,多用行鎖,少用表鎖。
  • 加索引的時候盡量準確,避免造成不必要的鎖定影響其他查詢。
  • 盡量減少給予范圍的數據檢索(間隙鎖),避免因為間隙鎖帶來的影響,鎖定了不該鎖定的記錄。
  • 盡量控制事務的大小,減少鎖定的資源量和鎖定時間。
  • 盡量使用較低級別的事務隔離,減少 MySQL 因為事務隔離帶來的成本。

總結



MySQL 數據庫鎖的思維導圖

MySQL 的鎖主要分為表級鎖和行級鎖。MyISAM 引擎使用的是表級鎖,針對表級的共享鎖和排他鎖,可以通過 concurrent_insert 和 low_priority_updates 參數來優化。

InnoDB 支持表鎖和行鎖,根據索引來判斷如何選擇。行鎖有,行共享鎖和行排他鎖;表鎖有,意向共享鎖,意向排他鎖,表鎖是系統自己加上的;鎖范圍的是間隙鎖。遇到死鎖,我們如何檢測,恢復以及如何避免。

MySQL 有四個事務級別分別是,讀未提交,讀提交,可重復讀,串行化。他們的隔離級別依次升高。

通過隔離級別的設置,可以避免,臟讀,不可重復讀和幻讀的情況。最后,對于使用比較多的 InnoDB 引擎,提出了一些優化建議。

作者:崔皓

簡介:十六年開發和架構經驗,曾擔任過惠普武漢交付中心技術專家,需求分析師,項目經理,后在創業公司擔任技術/產品經理。善于學習,樂于分享。目前專注于技術架構與研發管理。

【51CTO原創稿件,合作站點轉載請注明原文作者和出處為51CTO.com】



小編推薦:欲學習電腦技術、系統維護、網絡管理、編程開發和安全攻防等高端IT技術,請 點擊這里 注冊賬號,公開課頻道價值萬元IT培訓教程免費學,讓您少走彎路、事半功倍,好工作升職加薪!

本文出自:https://www.toutiao.com/a6748659273795895819/

免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!


鮮花

握手

雷人

路過

雞蛋

相關閱讀

最新評論

 最新
返回頂部
湖北快3 购买技巧