1 MYSQL 事務(wù)隔離級(jí)別 四種隔離級(jí)別： 臟讀，可重復(fù)讀，幻讀總結(jié)
2适肠，詳細(xì)畫圖分析 MVCC 多版本并發(fā)控制與UndoLog 底層原理霍衫；
3， 超詳細(xì)總結(jié)行鎖 for update ,間隙鎖侯养，悲觀鎖與樂(lè)觀鎖機(jī)制原理 總結(jié)
4敦跌， MySQL 性能調(diào)優(yōu)與監(jiān)控工具面試總結(jié)
MYSql 默認(rèn)事務(wù)隔離級(jí)別； 可重復(fù)讀 會(huì)發(fā)生幻讀
事務(wù)隔離級(jí)別： 多個(gè)不同的session 直接事務(wù)如何管理：
讀已提交：
讀未提交： 臟讀
可重復(fù)讀： 避免臟讀
串行化：
事務(wù)：
行鎖： commit 或者rollback 釋放
可重復(fù)讀： 在同一個(gè)事務(wù)中逛揩，最開(kāi)始與結(jié)束前查詢到的數(shù)據(jù)都是一致的柠傍。
原理采用mvcc 多版本控制原理： 無(wú)鎖方式+快照版本 Read view undolog 日志；
此時(shí)允許幻讀辩稽，單不允許重復(fù)讀和臟讀携兵；
mysql三種日志：
Binlog mysql 自帶二進(jìn)制文件 主從復(fù)制集群
readlog innodb 緩存頁(yè)的日志
undolog 事務(wù)日志 底層 記憶集鏈
mvcc 多版本控制原理：
一張表中： 三個(gè)隱藏列：
Rowid： 隱含是自增ID(隱藏主鍵)，如果數(shù)據(jù)表沒(méi)有主鍵搂誉，InnoDb 會(huì)自動(dòng)以DB_ROW_ID 產(chǎn)生一個(gè)聚簇索引 6個(gè)字節(jié)
DB_TRX_ID： 當(dāng)前全局事務(wù)id
DB_Roll_PTR: 回滾 id 修改/delete
MVCC多版本控制原理
簡(jiǎn)單回顧：MySQL InnoDB 引擎默認(rèn)為REPEATABLE READ
同一個(gè)事務(wù)中徐紧，多次查詢的結(jié)果還是原來(lái)數(shù)據(jù) 底層采用MVCC多版本控制機(jī)制實(shí)現(xiàn)，讀取原來(lái)快照數(shù)據(jù)炭懊。
此時(shí)允許幻讀并级，但不允許重復(fù)讀與臟讀。
MVCC侮腹，Multi-Version Concurrency Control嘲碧，多版本并發(fā)控制。MVCC 是一種并發(fā)控制的方法父阻，一般在數(shù)據(jù)庫(kù)管理系統(tǒng)中愈涩，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)；在編程語(yǔ)言中實(shí)現(xiàn)事務(wù)內(nèi)存加矛，就是同一份數(shù)據(jù)臨時(shí)保留多版本的一種方式履婉，進(jìn)而實(shí)現(xiàn)并發(fā)控制
如果是傳統(tǒng)鎖的方式保證實(shí)現(xiàn)多版本控制，需要通過(guò)鎖的方式實(shí)現(xiàn)斟览，效率非常低毁腿，MVCC 使用了一種不同的手段， 無(wú)鎖并發(fā)控制每個(gè)連接到數(shù)據(jù)庫(kù)的讀者苛茂，在某個(gè)瞬間看到的是數(shù)據(jù)庫(kù)的一個(gè)快照已烤，寫操作造成的變化在寫操作完成之前（或者數(shù)據(jù)庫(kù)事務(wù)提交之前）對(duì)于其他的讀者來(lái)說(shuō)是不可見(jiàn)的。
MVCC插入流程(insert)：
在MySQL中建表時(shí)妓羊，每個(gè)表都會(huì)有三列隱藏記錄胯究，其中和MVCC有關(guān)系的有兩列
1.DB_TRX_ID
6byte，最近修改(修改/插入)事務(wù)ID：記錄創(chuàng)建這條記錄/最后一次修改該記錄的事務(wù)ID
2.DB_ROLL_PTR
7byte躁绸，回滾指針裕循，指向這條記錄的上一個(gè)版本（存儲(chǔ)于rollback segment里）
3.DB_ROW_ID
6byte臣嚣，隱含的自增ID（隱藏主鍵），如果數(shù)據(jù)表沒(méi)有主鍵费韭，InnoDB會(huì)自動(dòng)以DB_ROW_ID產(chǎn)生一個(gè)聚簇索引
MVCC新增
在插入數(shù)據(jù)的時(shí)候茧球，假設(shè)系統(tǒng)的全局事務(wù)ID從1開(kāi)始，
|
set tx_isolation='repeatable-read';
begin;-- 獲取全局事務(wù)id
INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');
INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');
commit;
|
底層表結(jié)構(gòu)
[圖片上傳失敗...(image-64a967-1604282132901)]
插入的過(guò)程中會(huì)把全局事務(wù)ID記錄到列 DB_TRX_ID 中去
MVCC刪除
表格做刪除邏輯星持，執(zhí)行以下SQL語(yǔ)句（假設(shè)獲取到的事務(wù)邏輯ID為 3）
|
begin; -- 獲得全局事務(wù)ID = 3
delete mayikt_account where id= 2;
commit;
|
MVCC修改
修改邏輯和刪除邏輯有點(diǎn)相似抢埋，修改數(shù)據(jù)的時(shí)候會(huì)先復(fù)制一條當(dāng)前記錄行數(shù)據(jù)，同事標(biāo)記這條數(shù)據(jù)的數(shù)據(jù)行版本號(hào)為當(dāng)前是事務(wù)版本號(hào)督暂，最后把原來(lái)的數(shù)據(jù)行的刪除版本號(hào)標(biāo)記為當(dāng)前是事務(wù)揪垄。
|
begin;-- 獲取全局系統(tǒng)事務(wù)ID 假設(shè)為 7
update mayikt_account set balance=700 where id=1;
commit;
|
MVCC查詢
查詢規(guī)則：
- 查找數(shù)據(jù)行版本號(hào)早于當(dāng)前事務(wù)版本號(hào)的數(shù)據(jù)行記錄
數(shù)據(jù)行的版本號(hào)要小于或等于當(dāng)前是事務(wù)的系統(tǒng)版本號(hào)，這樣也就確保了讀取到的數(shù)據(jù)是當(dāng)前事務(wù)開(kāi)始前已經(jīng)存在的數(shù)據(jù)逻翁，或者是自身事務(wù)改變過(guò)的數(shù)據(jù)
- 查找刪除版本號(hào)要么為NULL饥努，要么大于當(dāng)前事務(wù)版本號(hào)的記錄
這樣確保查詢出來(lái)的數(shù)據(jù)行記錄在事務(wù)開(kāi)啟之前沒(méi)有被刪除
查詢與新增原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底層查詢： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2；
|
begin; -- 獲取全局事務(wù)id 2
INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');
commit;
|
查詢與修改原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底層查詢： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2八回；
|
begin; -- 獲取全局事務(wù)id 2
update mayikt_account set balance=balance-50 where id = 1;
commit;
|
查詢與delete原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account
select * from mayikt_account
commit;
|
sessionB
|
begin; -- 獲取全局事務(wù)id 2
delete from mayikt_account where id=1;
commit;
|
底層實(shí)現(xiàn)原理
Redolog日志 innodb引擎數(shù)據(jù)頁(yè)緩存日志
Undolog日志 事務(wù)回滾的日志 MVCC結(jié)合Undolog實(shí)現(xiàn)
Binlog日志 mysql 二進(jìn)制文件
隱式字段酷愧、undo日志、Read View來(lái)實(shí)現(xiàn)缠诅；
DB_TRX_ID
6byte溶浴，最近修改(修改/插入)事務(wù)ID：記錄創(chuàng)建這條記錄/最后一次修改該記錄的事務(wù)ID
DB_ROLL_PTR
7byte，回滾指針管引，指向這條記錄的上一個(gè)版本（存儲(chǔ)于rollback segment里）
DB_ROW_ID
6byte士败，隱含的自增ID（隱藏主鍵），如果數(shù)據(jù)表沒(méi)有主鍵褥伴，InnoDB會(huì)自動(dòng)以DB_ROW_ID產(chǎn)生一個(gè)聚簇索引
[圖片上傳失敗...(image-19b8ad-1604282132900)]##### MVCC多版本控制原理
簡(jiǎn)單回顧：MySQL InnoDB 引擎默認(rèn)為REPEATABLE READ
同一個(gè)事務(wù)中谅将，多次查詢的結(jié)果還是原來(lái)數(shù)據(jù) 底層采用MVCC多版本控制機(jī)制實(shí)現(xiàn)，讀取原來(lái)快照數(shù)據(jù)重慢。
此時(shí)允許幻讀饥臂，但不允許重復(fù)讀與臟讀。
MVCC伤锚，Multi-Version Concurrency Control擅笔，多版本并發(fā)控制。MVCC 是一種并發(fā)控制的方法屯援，一般在數(shù)據(jù)庫(kù)管理系統(tǒng)中，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)念脯；在編程語(yǔ)言中實(shí)現(xiàn)事務(wù)內(nèi)存狞洋，就是同一份數(shù)據(jù)臨時(shí)保留多版本的一種方式，進(jìn)而實(shí)現(xiàn)并發(fā)控制
如果是傳統(tǒng)鎖的方式保證實(shí)現(xiàn)多版本控制绿店，需要通過(guò)鎖的方式實(shí)現(xiàn)吉懊，效率非常低庐橙，MVCC 使用了一種不同的手段， 無(wú)鎖并發(fā)控制每個(gè)連接到數(shù)據(jù)庫(kù)的讀者借嗽，在某個(gè)瞬間看到的是數(shù)據(jù)庫(kù)的一個(gè)快照态鳖，寫操作造成的變化在寫操作完成之前（或者數(shù)據(jù)庫(kù)事務(wù)提交之前）對(duì)于其他的讀者來(lái)說(shuō)是不可見(jiàn)的。
MVCC插入流程(insert)：
在MySQL中建表時(shí)恶导，每個(gè)表都會(huì)有三列隱藏記錄浆竭，其中和MVCC有關(guān)系的有兩列
1.DB_TRX_ID
6byte，最近修改(修改/插入)事務(wù)ID：記錄創(chuàng)建這條記錄/最后一次修改該記錄的事務(wù)ID
2.DB_ROLL_PTR
7byte惨寿，回滾指針邦泄，指向這條記錄的上一個(gè)版本（存儲(chǔ)于rollback segment里）
3.DB_ROW_ID
6byte，隱含的自增ID（隱藏主鍵）裂垦，如果數(shù)據(jù)表沒(méi)有主鍵顺囊，InnoDB會(huì)自動(dòng)以DB_ROW_ID產(chǎn)生一個(gè)聚簇索引
MVCC新增
在插入數(shù)據(jù)的時(shí)候，假設(shè)系統(tǒng)的全局事務(wù)ID從1開(kāi)始蕉拢，
|
set tx_isolation='repeatable-read';
begin;-- 獲取全局事務(wù)id
INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');
INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');
commit;
|
底層表結(jié)構(gòu)
[圖片上傳失敗...(image-762dda-1604282136437)]
插入的過(guò)程中會(huì)把全局事務(wù)ID記錄到列 DB_TRX_ID 中去
MVCC刪除
表格做刪除邏輯特碳，執(zhí)行以下SQL語(yǔ)句（假設(shè)獲取到的事務(wù)邏輯ID為 3）
|
begin; -- 獲得全局事務(wù)ID = 3
delete mayikt_account where id= 2;
commit;
|
MVCC修改
修改邏輯和刪除邏輯有點(diǎn)相似，修改數(shù)據(jù)的時(shí)候會(huì)先復(fù)制一條當(dāng)前記錄行數(shù)據(jù)晕换，同事標(biāo)記這條數(shù)據(jù)的數(shù)據(jù)行版本號(hào)為當(dāng)前是事務(wù)版本號(hào)午乓，最后把原來(lái)的數(shù)據(jù)行的刪除版本號(hào)標(biāo)記為當(dāng)前是事務(wù)。
|
begin;-- 獲取全局系統(tǒng)事務(wù)ID 假設(shè)為 7
update mayikt_account set balance=700 where id=1;
commit;
|
MVCC查詢
查詢規(guī)則：
- 查找數(shù)據(jù)行版本號(hào)早于當(dāng)前事務(wù)版本號(hào)的數(shù)據(jù)行記錄
數(shù)據(jù)行的版本號(hào)要小于或等于當(dāng)前是事務(wù)的系統(tǒng)版本號(hào)届巩，這樣也就確保了讀取到的數(shù)據(jù)是當(dāng)前事務(wù)開(kāi)始前已經(jīng)存在的數(shù)據(jù)硅瞧，或者是自身事務(wù)改變過(guò)的數(shù)據(jù)
- 查找刪除版本號(hào)要么為NULL，要么大于當(dāng)前事務(wù)版本號(hào)的記錄
這樣確保查詢出來(lái)的數(shù)據(jù)行記錄在事務(wù)開(kāi)啟之前沒(méi)有被刪除
查詢與新增原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底層查詢： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2恕汇；
|
begin; -- 獲取全局事務(wù)id 2
INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');
commit;
|
查詢與修改原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底層查詢： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2腕唧；
|
begin; -- 獲取全局事務(wù)id 2
update mayikt_account set balance=balance-50 where id = 1;
commit;
|
查詢與delete原理
sessionA
|
begin;-- 獲取全局事務(wù)id 1
-- select * from mayikt_account
select * from mayikt_account
commit;
|
sessionB
|
begin; -- 獲取全局事務(wù)id 2
delete from mayikt_account where id=1;
commit;
|
底層實(shí)現(xiàn)原理
Redolog日志 innodb引擎數(shù)據(jù)頁(yè)緩存日志
Undolog日志 事務(wù)回滾的日志 MVCC結(jié)合Undolog實(shí)現(xiàn)
Binlog日志 mysql 二進(jìn)制文件
隱式字段、undo日志瘾英、Read View來(lái)實(shí)現(xiàn)枣接；
DB_TRX_ID
6byte，最近修改(修改/插入)事務(wù)ID：記錄創(chuàng)建這條記錄/最后一次修改該記錄的事務(wù)ID
DB_ROLL_PTR
7byte缺谴，回滾指針但惶，指向這條記錄的上一個(gè)版本（存儲(chǔ)于rollback segment里）
DB_ROW_ID
6byte，隱含的自增ID（隱藏主鍵）湿蛔，如果數(shù)據(jù)表沒(méi)有主鍵膀曾，InnoDB會(huì)自動(dòng)以DB_ROW_ID產(chǎn)生一個(gè)聚簇索引
[圖片上傳失敗...(image-7cd910-1604282136435)]
undo日志
undo log主要分為兩種：
1.insert undo log 代表事務(wù)在insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要，并且在事務(wù)提交后可以被立即丟棄
2.update undo log
事務(wù)在進(jìn)行update或delete時(shí)產(chǎn)生的undo log; 不僅在事務(wù)回滾時(shí)需要阳啥，在快照讀時(shí)也需要添谊；所以不能隨便刪除，只有在快速讀或事務(wù)回滾不涉及該日志時(shí)察迟，對(duì)應(yīng)的日志才會(huì)被purge線程統(tǒng)一清除
清除(purge)：
從前面的分析可以看出斩狱，為了實(shí)現(xiàn)InnoDB的MVCC機(jī)制耳高，更新或者刪除操作都只是設(shè)置一下老記錄的deleted_bit，并不真正將過(guò)時(shí)的記錄刪除所踊。
為了節(jié)省磁盤空間泌枪，InnoDB有專門的purge線程來(lái)清理deleted_bit為true的記錄。為了不影響MVCC的正常工作秕岛，purge線程自己也維護(hù)了一個(gè)read view（這個(gè)read view相當(dāng)于系統(tǒng)中最老活躍事務(wù)的read view）;如果某個(gè)記錄的deleted_bit為true碌燕，并且DB_TRX_ID相對(duì)于purge線程的read view可見(jiàn)，那么這條記錄一定是可以被安全清除的瓣蛀。
undo日志
undo log主要分為兩種：
1.insert undo log 代表事務(wù)在insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要陆蟆，并且在事務(wù)提交后可以被立即丟棄
2.update undo log
事務(wù)在進(jìn)行update或delete時(shí)產(chǎn)生的undo log; 不僅在事務(wù)回滾時(shí)需要，在快照讀時(shí)也需要惋增；所以不能隨便刪除叠殷，只有在快速讀或事務(wù)回滾不涉及該日志時(shí)，對(duì)應(yīng)的日志才會(huì)被purge線程統(tǒng)一清除
清除(purge)：
從前面的分析可以看出诈皿，為了實(shí)現(xiàn)InnoDB的MVCC機(jī)制林束，更新或者刪除操作都只是設(shè)置一下老記錄的deleted_bit，并不真正將過(guò)時(shí)的記錄刪除稽亏。
為了節(jié)省磁盤空間壶冒，InnoDB有專門的purge線程來(lái)清理deleted_bit為true的記錄。為了不影響MVCC的正常工作截歉，purge線程自己也維護(hù)了一個(gè)read view（這個(gè)read view相當(dāng)于系統(tǒng)中最老活躍事務(wù)的read view）;如果某個(gè)記錄的deleted_bit為true胖腾，并且DB_TRX_ID相對(duì)于purge線程的read view可見(jiàn)，那么這條記錄一定是可以被安全清除的瘪松。
悲觀鎖與樂(lè)觀鎖之間區(qū)別
for update定義
for update是一種行級(jí)鎖咸作，又叫排它鎖，一旦用戶對(duì)某個(gè)行施加了行級(jí)加鎖宵睦，則該用戶可以查詢也可以更新被加鎖的數(shù)據(jù)行记罚，其它用戶只能查詢但不能更新被加鎖的數(shù)據(jù)行．如果其它用戶想更新該表中的數(shù)據(jù)行，則也必須對(duì)該表施加行級(jí)鎖．即使多個(gè)用戶對(duì)一個(gè)表均使用了共享更新壳嚎，但也不允許兩個(gè)事務(wù)同時(shí)對(duì)一個(gè)表進(jìn)行更新桐智，真正對(duì)表進(jìn)行更新時(shí)，是以獨(dú)占方式鎖表烟馅，一直到提交或復(fù)原該事務(wù)為止说庭。行鎖永遠(yuǎn)是獨(dú)占方式鎖。
而select … for update 語(yǔ)句是我們經(jīng)常使用手工加鎖語(yǔ)句郑趁。在數(shù)據(jù)庫(kù)中執(zhí)行select … for update ,大家會(huì)發(fā)現(xiàn)會(huì)對(duì)數(shù)據(jù)庫(kù)中的表或某些行數(shù)據(jù)進(jìn)行鎖表口渔，在mysql中，如果查詢條件帶有主鍵穿撮，會(huì)鎖行數(shù)據(jù)缺脉，如果沒(méi)有，會(huì)鎖表悦穿。
sessionA
begin;
select * from mayikt_account where id=2 for update;
commit;
sessionB
修改id=2 直接卡出
悲觀鎖與樂(lè)觀鎖實(shí)現(xiàn)
悲觀鎖比較悲觀攻礼，多線程同時(shí)對(duì)同一行數(shù)據(jù)修改的時(shí)候，最終只有一個(gè)線程修改成功栗柒。
Innodb引擎自帶行鎖機(jī)制礁扮。
樂(lè)觀鎖：比較樂(lè)觀，多線程同時(shí)對(duì)同一行數(shù)據(jù)修改的時(shí)候瞬沦，如果沒(méi)有獲取到行鎖的線程太伊，
會(huì)采用自旋的方式不斷重試，一般采用cas實(shí)現(xiàn)逛钻。
mysql 間隙鎖：
間隙鎖基本概念：
間隙鎖是一個(gè)在索引記錄之間的間隙上的鎖僚焦；
間隙鎖的作用：
在mysql 的innodb 引擎中，如果操作的是一個(gè)區(qū)別的數(shù)據(jù)曙痘，會(huì)鎖住這個(gè)區(qū)間所有的記錄芳悲，即使這個(gè)記錄不存在，這時(shí)候另一個(gè)會(huì)話去插入這個(gè)區(qū)間的數(shù)據(jù)边坤，就必須等待上一個(gè)結(jié)束名扛；
INSERT INTO `mayikt_account` VALUES (18, 'xiaoxiao18', '600');
INSERT INTO `mayikt_account` VALUES (22, 'xiaoxiao20', '600');
sessionA
begin;
update mayikt_account set name='mayikt6' where id>18 and id<22;
commit;
從id>18 and id <22 上了間隙鎖，在沒(méi)有釋放鎖的時(shí)候 其他的session無(wú)法對(duì)該段位做操作茧痒。
sessionB
INSERT INTO mayikt_account VALUES (19, 'mayikt19', '500');
我們也可以使用間隙鎖解決幻讀問(wèn)題
SessionA 先執(zhí)行：
update mayikt_account set name='mayikt6' where id>18 and id<22;
另外SessionB 無(wú)法insert id在18-22 段位肮韧。
行鎖：提交或者回滾
主鍵索引或者唯一索引會(huì)使用間隙鎖嗎
1.如果where條件都命中的情況下，則不會(huì)發(fā)生間隙鎖旺订，只會(huì)增加記錄鎖
2.如果where條件部分命中或者全部沒(méi)有命中的情況下弄企，則使用間隙鎖
如何避免行鎖升級(jí)表鎖
InnoDB的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖耸峭，并且該索引不能失效桩蓉，否則都會(huì)從行鎖升級(jí)為表鎖挽霉。
sessionA
begin;
update mayikt_account set name='mayikt6' where balance='300';
commit;
修改的時(shí)候查詢的條件不是索引字段篷扩，會(huì)走全表掃描 全表掃描的時(shí)候?qū)γ啃袛?shù)據(jù)都加上行鎖
，最終形成表鎖嗡髓。
sessionB
無(wú)法修改該任意一條數(shù)據(jù)本涕，直接發(fā)生表鎖业汰。
刪除表鎖
unlock tables;
優(yōu)化注意事項(xiàng)
1.盡可能讓所有數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，避免無(wú)索引行鎖升級(jí)為表鎖
2.盡可能減少檢索條件范圍菩颖，避免間隙鎖
3.盡量控制事務(wù)大小样漆，減少鎖定資源量和時(shí)間長(zhǎng)度，涉及事務(wù)加鎖的sql盡量放在事務(wù)最后執(zhí)行
