天天看點

MySQL底層原理二之鎖機制

作者:平凡人筆記

承接上文MySQL資料存儲原理一

葉子節點是有序的,在往裡面存儲資料的時候,假設這個資料是int類型,在業務允許的範圍之内且允許自增的情況下都要自增

為什麼需要自增

因為有序和分裂,

  • 葉分裂的過程
MySQL底層原理二之鎖機制

假設這個葉子節點,隻能存4條記錄,存滿了之後,還想往裡面插入一條14的記錄,因為葉子節點一定要保證有序,14在13和15之間,這個節點已經滿了怎麼辦,把一個節點分裂成2個節點

MySQL底層原理二之鎖機制

然後把14放入進去,

MySQL底層原理二之鎖機制

分裂了之後,要保證上層節點有指針指向它,可能需要增加一個key值,上層節點若也滿了,它也需要進行分裂。

往葉子節點裡面插入一條資料,它會影響上面不同層數節點的變化,這就是頁分裂的過程。

除了分裂,還有一個葉合并,比如1個節點1KB,另一個節點3KB,2個節點需要合并成一個節點4KB。

葉分裂、葉合并管理都是比較麻煩的。

如果是自增的話,直接後面追加就可以了,前面的不用管,就沒有葉分裂、葉合并問題了。

但是會涉及到另外一個問題:索引的維護。

插入大量資料的時候,會導緻性能變低,很多時間都浪費在索引的維護上面了。

在實際删除一條記錄的時候,不是把磁盤裡的資料真的删除了,而是給一個删除的标記,當有新資料來了之後可以往裡面補全的。

mysql鎖

  • 樂觀鎖/悲觀鎖
  • cas就是樂觀鎖,加一個version字段,通過版本辨別;悲觀鎖是mysql自帶的排他鎖
  • 按照鎖粒度劃分

按照鎖定粒度分為表鎖和行鎖,意向鎖和自增鎖都是表鎖,間隙鎖、臨鍵鎖、記錄鎖全部都屬于行鎖。一般來說,聊鎖的時候,都是聊鎖的粒度問題。

  • 按照鎖的方式劃分
  • 隻有共享鎖之間是相容的,其他都不相容。

資料庫裡面經常會發生鎖的問題,怎麼去排查鎖?

鎖一般情況下是不需要人為加的,人為加的話,常用的基本隻有兩種:

  • select for update
  • select lock in share mode

鎖的作用

為了滿足事務隔離性,解決對應的一個并發問題,最重要保證資料的一緻性,是以聊鎖的時候,必不可少的要牽涉到事務,innodb存儲引擎支援事務,myisam不支援事務。

檢視目前innodb存儲引擎的狀态

show engine innodb status \G;
           
MySQL底層原理二之鎖機制

這裡有一個事務子產品

  • 開啟輸出鎖資訊
set global innodb_status_output_locks=1;
           
  • 設定手動送出事務
MySQL底層原理二之鎖機制
  • 開啟一個事務并做一個查詢操作
  • 現在就一個事務,查詢下事務的狀态
MySQL底層原理二之鎖機制

TRANSACTION 143222, ACTIVE 125 sec,這個事務活躍了125秒。

2 lock struct(s), heap size 1136, 3 row lock(s) 兩個鎖結構,3個行鎖。

TABLE LOCK table test .rental trx id 143222 lock mode IX 這是一個表鎖,rental這個表被這個事務 143222鎖住了, IX表示意向鎖。

RECORD LOCKS space id 7753 page no 3 n bits 72 index PRIMARY of table test .rental trx id 143222 lock_mode X 這是一個行鎖。如果這個表沒有主鍵,這裡就會顯示GEN_CLUST_INDEX,這是一個聚簇索引,

聚簇索引clustered index的建立

  • 如果定義了 primary key,MySQL則會使用該primary key作為clustered index;
  • 如果沒有定義 primary key,MySQL會使用第一個字段不為空并且是 unique index作為clustered index;
  • 如果db table既沒有primary key,又沒有滿足條件的unique key,MySQL則會建立一個名為 GEN_CLUST_INDEX的clustered index。

表示目前這個事務在這個表的聚簇索引上加了一個X鎖,3-72之間也表明這是一個間隙鎖,鎖定是某個範圍。

MySQL底層原理二之鎖機制

共有2個鎖結構,一個是表鎖,一個是行鎖以及三個行鎖的情況。

監控到這些資訊之後,就可以對目前的鎖做一個基本的判斷了。

關于這幾種類型的鎖,最關鍵的不是把這些概念搞明白,而是要了解每種鎖所對應的情況。

如果遇到死鎖怎麼去排查?

死鎖還是這麼排查,在這個事務子產品裡面會看到deadlock,這裡示範下死鎖的産生過程,

  • 開啟事務1
  • 再開啟一個視窗,開啟事務2
MySQL底層原理二之鎖機制
  • 開啟了之後,檢視目前鎖的情況

此時還沒有鎖,

  • 在事務1裡面加一個鎖
  • 事務2裡面也加一個鎖

兩個事務分别都查詢出來了

  • 在第一個事務裡面檢視狀态
  • 可以看到兩個事務的情況,這兩個事務分别都加了行鎖,
MySQL底層原理二之鎖機制

事務1加了X行鎖,不是間隙鎖,

MySQL底層原理二之鎖機制

事務2也加了行鎖,

  • 在第一個事務裡面查詢20,查不到
MySQL底層原理二之鎖機制

因為事務2中已經加了這個鎖了,是以事務1,隻能等待事務2釋放行鎖,

MySQL底層原理二之鎖機制

檢視鎖狀态,可以看到事務1裡面有一個等待狀态的行鎖

  • 第二個事務查10
MySQL底層原理二之鎖機制

顯示死鎖狀态 (Deadlock)了。

  • 總結上述過程
  • 1、事務1對10的這條記錄加鎖
  • 2、事務2對20的這條記錄加鎖
  • 3、事務1對20的這條記錄加鎖,它能夠加上鎖的前提,是需要事務2釋放了20這條記錄的行鎖,事務1才能加上鎖,是以事務1需要等待事務2釋放了20這條記錄的行鎖
  • 4、事務2對10的這條記錄加鎖,同理,事務2也需要等待事務1釋放了10這條記錄上的行鎖,但此時事務2檢測到死鎖狀态,事務2抛出異常,事務2被復原,事務2就會釋放對記錄20上的鎖,事務1得以對記錄20加鎖,
  • 5、是以事務1查詢20的這條記錄查詢出來了
MySQL底層原理二之鎖機制

再出現事務記錄就可以看到檢測到死鎖的描述了

MySQL底層原理二之鎖機制

事務2為了避免或解除死鎖問題,rollback了

MySQL底層原理二之鎖機制

線上再出現鎖競争或死鎖排查問題就可以協查了。

show profiles

MySQL底層原理二之鎖機制

顯示目前這個sql語句在執行每個步驟的時候花了多久。開啟 、權限驗證、打開表、初始化、系統鎖,把每個操作的時間做一個顯示,目前sql語句執行的一個具體狀況,但這些東西在beta版本可能被淘汰掉。

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
           
MySQL底層原理二之鎖機制

使用performance_schema替換show profiles

show databases;可以看到 performance_schema這個資料庫,

MySQL底層原理二之鎖機制

切換到這個資料庫,可以看到裡面有87張表,存儲的都是最基本的監控資訊。

select digest_text,avg_timer_wait from events_statements_summary_by_digest order by count_star;
           
MySQL底層原理二之鎖機制

這裡可以看到所有的sql的執行過程,performance_schema會進行實際資料的統計工作,幫你去做監控,但要開啟的話就需要占用資源,當你需要監控某些mysql性能參數的時候建議開啟。

sql調優的執行個體

MySQL底層原理二之鎖機制

這是一張城市資訊表,

MySQL底層原理二之鎖機制

裡面有19800條資料,按這個列完整的建索引的話,會占用很多空間,如何進行優化?

  • 看下每個城市大概有多少個重複值

重複的很多,如果用全局索引建立的話,效率一定很低

MySQL底層原理二之鎖機制

取城市名稱的前7位字元建立索引,而不是用整串字元去建立索引,既可以提高檢索效率,又不會浪費太多空間

alter table citydemo add key(city(7));
           
MySQL底層原理二之鎖機制

繼續閱讀