承接上文MySQL資料存儲原理一
葉子節點是有序的,在往裡面存儲資料的時候,假設這個資料是int類型,在業務允許的範圍之内且允許自增的情況下都要自增
為什麼需要自增
因為有序和分裂,
- 葉分裂的過程
假設這個葉子節點,隻能存4條記錄,存滿了之後,還想往裡面插入一條14的記錄,因為葉子節點一定要保證有序,14在13和15之間,這個節點已經滿了怎麼辦,把一個節點分裂成2個節點
然後把14放入進去,
分裂了之後,要保證上層節點有指針指向它,可能需要增加一個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;
這裡有一個事務子產品
- 開啟輸出鎖資訊
set global innodb_status_output_locks=1;
- 設定手動送出事務
- 開啟一個事務并做一個查詢操作
- 現在就一個事務,查詢下事務的狀态
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之間也表明這是一個間隙鎖,鎖定是某個範圍。
共有2個鎖結構,一個是表鎖,一個是行鎖以及三個行鎖的情況。
監控到這些資訊之後,就可以對目前的鎖做一個基本的判斷了。
關于這幾種類型的鎖,最關鍵的不是把這些概念搞明白,而是要了解每種鎖所對應的情況。
如果遇到死鎖怎麼去排查?
死鎖還是這麼排查,在這個事務子產品裡面會看到deadlock,這裡示範下死鎖的産生過程,
- 開啟事務1
- 再開啟一個視窗,開啟事務2
- 開啟了之後,檢視目前鎖的情況
此時還沒有鎖,
- 在事務1裡面加一個鎖
- 事務2裡面也加一個鎖
兩個事務分别都查詢出來了
- 在第一個事務裡面檢視狀态
- 可以看到兩個事務的情況,這兩個事務分别都加了行鎖,
事務1加了X行鎖,不是間隙鎖,
事務2也加了行鎖,
- 在第一個事務裡面查詢20,查不到
因為事務2中已經加了這個鎖了,是以事務1,隻能等待事務2釋放行鎖,
檢視鎖狀态,可以看到事務1裡面有一個等待狀态的行鎖
- 第二個事務查10
顯示死鎖狀态 (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的這條記錄查詢出來了
再出現事務記錄就可以看到檢測到死鎖的描述了
事務2為了避免或解除死鎖問題,rollback了
線上再出現鎖競争或死鎖排查問題就可以協查了。
show profiles
顯示目前這個sql語句在執行每個步驟的時候花了多久。開啟 、權限驗證、打開表、初始化、系統鎖,把每個操作的時間做一個顯示,目前sql語句執行的一個具體狀況,但這些東西在beta版本可能被淘汰掉。
https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
使用performance_schema替換show profiles
show databases;可以看到 performance_schema這個資料庫,
切換到這個資料庫,可以看到裡面有87張表,存儲的都是最基本的監控資訊。
select digest_text,avg_timer_wait from events_statements_summary_by_digest order by count_star;
這裡可以看到所有的sql的執行過程,performance_schema會進行實際資料的統計工作,幫你去做監控,但要開啟的話就需要占用資源,當你需要監控某些mysql性能參數的時候建議開啟。
sql調優的執行個體
這是一張城市資訊表,
裡面有19800條資料,按這個列完整的建索引的話,會占用很多空間,如何進行優化?
- 看下每個城市大概有多少個重複值
重複的很多,如果用全局索引建立的話,效率一定很低
取城市名稱的前7位字元建立索引,而不是用整串字元去建立索引,既可以提高檢索效率,又不會浪費太多空間
alter table citydemo add key(city(7));