1MySQL 基礎(chǔ)

1.1 數(shù)據(jù)庫三大范式是什么

第一范式：每個列都不可以再拆分。

第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三范式：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。

在設(shè)計數(shù)據(jù)庫結(jié)構(gòu)的時候，要盡量遵守三范式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經(jīng)常會為了性能而妥協(xié)數(shù)據(jù)庫的設(shè)計。

1.2 MySQL存儲引擎MyISAM與InnoDB區(qū)別

存儲引擎Storage engine：MySQL中的數(shù)據(jù)、索引以及其他對象是如何存儲的，是一套文件系統(tǒng)的實現(xiàn)。

常用的存儲引擎有以下：

• Innodb引擎：Innodb引擎提供了對數(shù)據(jù)庫ACID事務(wù)的支持。并且還提供了行級鎖和外鍵的約束。它的設(shè)計的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫系統(tǒng)。
• MyIASM引擎(原本Mysql的默認(rèn)引擎)：不提供事務(wù)的支持，也不支持行級鎖和外鍵。
• MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高。

MyISAM與InnoDB區(qū)別

1.3 MyISAM索引與InnoDB索引的區(qū)別？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主鍵索引的葉子節(jié)點存儲著行數(shù)據(jù)，因此主鍵索引非常高效。
• MyISAM索引的葉子節(jié)點存儲的是行數(shù)據(jù)地址，需要再尋址一次才能得到數(shù)據(jù)。
• InnoDB非主鍵索引的葉子節(jié)點存儲的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢時做到覆蓋索引會非常高效。

2 索引

2.1 什么是索引？

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。

索引是一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B樹及其變種B+樹。

更通俗的說，索引就相當(dāng)于目錄。為了方便查找書中的內(nèi)容，通過對內(nèi)容建立索引形成目錄。索引是一個文件，它是要占據(jù)物理空間的。

2.2 索引有哪些優(yōu)缺點？

索引的優(yōu)點

• 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。

• 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

索引的缺點

• 時間方面：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間，具體地，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，會降低增/改/刪的執(zhí)行效率；
• 空間方面：索引需要占物理空間。

2.3 索引有哪些種類

2.4 索引使用場景（重點）

在使用索引之前，判斷是否適合使用索引。索引是有在多查詢、少增刪的場景下。因為增刪數(shù)據(jù)會讓MySQL調(diào)整B+樹，從而增加開銷。

判斷是否索引如下：

1. 先使用 show global status like 'Com_' 可以查看當(dāng)前數(shù)據(jù)庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次；
2. 再使用慢日志查詢，開啟慢日志 slow_query_log=1; 并設(shè)置多少秒之后算慢查詢 long_query_time=2; 查看慢日志文件
3. 然后 show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費(fèi)到哪里去了
4. 使用 explain 分析SQL語句

2.5 索引為什么不使用B樹和hash方式，而使用B+樹

（1）對于hash數(shù)據(jù)結(jié)構(gòu)的優(yōu)點是可以快速查詢到數(shù)據(jù)，但是缺點是不能進(jìn)行范圍查詢。

1. hash索引不支持模糊查詢以及多列索引的最左前綴匹配。原理也是因為hash函數(shù)的不可預(yù)測。AAAA和AAAAB的索引沒有相關(guān)性。
2. hash索引不支持使用索引進(jìn)行排序，原理同上
3. hash索引任何時候都避免不了回表查詢數(shù)據(jù)，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。
4. hash索引雖然在等值查詢上較快，但是不穩(wěn)定。性能不可預(yù)測，當(dāng)某個鍵值存在大量重復(fù)的時候，發(fā)生hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩(wěn)定，對于所有的查詢都是從根節(jié)點到葉子節(jié)點，且樹的高度較低。

（2）對于B樹，將數(shù)據(jù)是存放在每一個結(jié)點的，所以每個結(jié)點比較大。那么對于有固定大小的頁來說，一頁存不下多少數(shù)據(jù)，就要花資源去記錄下一頁數(shù)據(jù)，分頁查詢較多，查詢效率不高

（3）對于B+樹來說，所有的數(shù)據(jù)都會出現(xiàn)在葉子節(jié)點。葉子節(jié)點形成一個單向鏈表。非葉子節(jié)點僅僅起到索引數(shù)據(jù)作用，具體的數(shù)據(jù)都是在葉子節(jié)點存放的。帶有順序指針的B+Tree，提高區(qū)間訪問的性能，利于排序。同時每個結(jié)點只是索引，一頁可以存放更多的索引，分頁查詢更少，效率更高。

假設(shè):

一行數(shù)據(jù)大小為1k，一頁中可以存儲16行這樣的數(shù)據(jù)。InnoDB的指針占用6個字節(jié)的空間，主鍵即使為bigint，占用字節(jié)數(shù)為8。

高度為2：

n * 8 + (n + 1) * 6 = 16*1024 , 算出n約為 1170

1171* 16 = 18736

也就是說，如果樹的高度為2，則可以存儲 18000 多條記錄。

高度為3：

1171 * 1171 * 16 = 21939856

也就是說，如果樹的高度為3，則可以存儲 2200w 左右的記錄。

2.6 索引設(shè)計的原則？

1. 針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
3. 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
4. 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
5. 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率。
6. 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率。
7. 如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢。改表內(nèi)容的時候，索引會進(jìn)行更新甚至重構(gòu)，索引列越多，這個時間就會越長。所以只保持需要的索引有利于查詢即可。

2.7 創(chuàng)建索引時需要注意什么？

1. 非空字段：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在mysql中，含有空值的列很難進(jìn)行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值；
2. 取值離散大的字段：（變量各個取值之間的差異程度）的列放到聯(lián)合索引的前面，可以通過count()函數(shù)查看字段的差異值，返回值越大說明字段的唯一值越多字段的離散程度高；
3. 索引字段越小越好：數(shù)據(jù)庫的數(shù)據(jù)存儲以頁為單位一頁存儲的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高（比如可以使用前置索引）。

2.8 使用索引查詢一定能提高查詢的性能嗎？為什么

通常，通過索引查詢數(shù)據(jù)比全表掃描要快。但是我們也必須注意到它的代價。

1. 索引需要空間來存儲，也需要定期維護(hù)， 每當(dāng)有記錄在表中增減或索引列被修改時，索引本身也會被修改。 這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O。 因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應(yīng)時間變慢。使用索引查詢不一定能提高查詢性能，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:
2. 基于一個范圍的檢索，一般查詢返回結(jié)果集小于表中記錄數(shù)的30%
3. 基于非唯一性索引的檢索

2.9 百萬級別或以上的數(shù)據(jù)如何加載和刪除

加載：

load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n'

刪除：在我們刪除數(shù)據(jù)庫百萬級別數(shù)據(jù)的時候，查詢MySQL官方手冊得知刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。

1. 所以我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引（此時大概耗時三分多鐘）
2. 然后刪除其中無用數(shù)據(jù)（此過程需要不到兩分鐘）
3. 刪除完成后重新創(chuàng)建索引(此時數(shù)據(jù)較少了)創(chuàng)建索引也非?？欤s十分鐘左右。
4. 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

2.10 什么是最左前綴原則？什么是最左匹配原則

在 user 表中，有一個聯(lián)合索引，這個聯(lián)合索引涉及到三個字段，順序分別為：profession，age，status。

對于最左前綴法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列后面的字段索引將失效。

最左前綴法則中指的最左邊的列，是指在查詢時，聯(lián)合索引的最左邊的字段(即是第一個字段)必須存在，與我們編寫SQL時，條件編寫的先后順序無關(guān)。

2.11 索引失效的情況

1. 不符合最左匹配原則，但是where語句中的順序可以和所以不一致
2. 使用范圍查找，比如>，<不可以使用索引；但是 >= 和 <= 可以使用索引
3. 索引列運(yùn)算
4. 字符串不加引號
5. 模糊查詢，但是這種情況是可以的 like '軟件%'
6. or連接條件
7. 數(shù)據(jù)分布影響

2.12 B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數(shù)據(jù)，

select * from user where name = 'Arm';  --回表查詢
select id,name from user where name = 'Arm'; --不需要回表查詢

2.13 什么是聚簇索引？何時使用聚簇索引與非聚簇索引

聚簇索引：將數(shù)據(jù)存儲與索引放到了一塊，找到索引也就找到了數(shù)據(jù)

非聚簇索引：將數(shù)據(jù)存儲于索引分開結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)點指向了數(shù)據(jù)的對應(yīng)行，myisam通過key_buffer把索引先緩存到內(nèi)存中，當(dāng)需要訪問數(shù)據(jù)時（通過索引訪問數(shù)據(jù)），在內(nèi)存中直接搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時，速度慢的原因

澄清一個概念：innodb中，在聚簇索引之上創(chuàng)建的索引稱之為輔助索引，輔助索引訪問數(shù)據(jù)總是需要二次查找，非聚簇索引都是輔助索引，像復(fù)合索引、前綴索引、唯一索引，輔助索引葉子節(jié)點存儲的不再是行的物理位置，而是主鍵值

何時使用聚簇索引與非聚簇索引

2.14 非聚簇索引一定會回表查詢嗎？

不一定，這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進(jìn)行回表查詢。

舉個簡單的例子，假設(shè)我們在員工表的年齡上建立了索引，那么當(dāng)進(jìn)行 select age from employee where age < 20的查詢時，在索引的葉子節(jié)點上，已經(jīng)包含了age信息，不會再次進(jìn)行回表查詢。

2.15 如何分析SQL語句（解釋使用explain）

explain 的屬性

Extra

3 事務(wù)

3.1 事物的四大特性(ACID)介紹一下?

關(guān)系性數(shù)據(jù)庫需要遵循ACID規(guī)則，具體內(nèi)容如下：

• 原子性： 事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動作要么全部完成，要么完全不起作用；
• 一致性： 執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，多個事務(wù)對同一個數(shù)據(jù)讀取的結(jié)果是相同的；
• 隔離性： 并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨(dú)立的；
• 持久性： 一個事務(wù)被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。

3.2 什么是臟讀？幻讀？不可重復(fù)讀？

• 臟讀(Drity Read)：事務(wù)A已更新一份數(shù)據(jù)，事務(wù)B在此時讀取了同一份數(shù)據(jù)，由于某些原因，事務(wù)A 進(jìn)行了RollBack操作，則事務(wù)B所讀取的數(shù)據(jù)就會是不正確的。（A一個晃身，騙過了B）
• 不可重復(fù)讀(Non-repeatable read)：事務(wù)A的兩次查詢之中數(shù)據(jù)不一致，這可能是兩次查詢過程中間插入了一個事務(wù)B更新的原有的數(shù)據(jù)。（轉(zhuǎn)個頭，數(shù)據(jù)就了）
• 幻讀(Phantom Read)：在一個事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一致，例如有事務(wù)A查詢了幾行 (Row)數(shù)據(jù)，而事務(wù)B卻在此時插入了新的幾行數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就會發(fā)現(xiàn)有幾行數(shù)據(jù)是它先前所沒有的。（原來沒有的，后來有了）

3.3 什么是事務(wù)的隔離級別？MySQL的默認(rèn)隔離級別是什么？

為了達(dá)到事務(wù)的四大特性，數(shù)據(jù)庫定義了4種不同的事務(wù)隔離級別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟讀、不可重復(fù)讀、幻讀這幾類問題。

SQL 標(biāo)準(zhǔn)定義了四個隔離級別（是個標(biāo)準(zhǔn)）：

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會導(dǎo)致臟讀、幻讀或不可重復(fù)讀。只能保證持久性
• READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。語句級別的。
• REPEATABLE-READ(可重復(fù)讀)： 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。事務(wù)級別。
• SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務(wù)依次逐個執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復(fù)讀以及幻讀。

這里需要注意的是：Mysql 默認(rèn)采用的 REPEATABLE_READ隔離級別

事務(wù)隔離機(jī)制的實現(xiàn)基于鎖機(jī)制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使用的是MVCC（多版本并發(fā)控制），通過保存修改的舊版本信息來支持并發(fā)一致性讀和回滾等特性。

因為隔離級別越低，事務(wù)請求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫系統(tǒng)的隔離級別都是READ-COMMITTED(讀取提交內(nèi)容):，但是你要知道的是InnoDB 存儲引擎默認(rèn)使用 REPEATABLE-READ（可重讀） 并不會有任何性能損失。

InnoDB 存儲引擎在 分布式事務(wù) 的情況下一般會用到 SERIALIZABLE(可串行化) 隔離級別。

3.4 什么是MVCC機(jī)制？使用了什么思想？作用于那些地方？是如何實現(xiàn)？

MVCC的具體實現(xiàn)，還需要依賴于數(shù)據(jù)庫記錄中的三個隱式字段、undo log日志、readView

三個隱式字段

DB_TRX_ID：最近修改事務(wù)ID，記錄插入這條記錄或最后一次修改該記錄的事務(wù)ID。

DB_ROLL_PTR：回滾指針，指向這條記錄的上一個版本，用于配合undo log，指向上一個版本。

DB_ROW_ID：隱藏主鍵，如果表結(jié)構(gòu)沒有指定主鍵，將會生成該隱藏字段。

undo log日志

版本鏈，用于回滾

readView

ReadView（讀視圖）是 快照讀 SQL執(zhí)行時MVCC提取數(shù)據(jù)的依據(jù)，記錄并維護(hù)系統(tǒng)當(dāng)前活躍的事務(wù)（未提交的）id。

ReadView中包含了四個核心字段：

而在readview中就規(guī)定了版本鏈數(shù)據(jù)的訪問規(guī)則：trx_id 代表當(dāng)前undolog版本鏈對應(yīng)事務(wù)ID。

3.5 四種隔離級別的實現(xiàn)

3.5.1 讀未提交

使用當(dāng)前讀。在此隔離級別下，事務(wù)之間可以讀取彼此未提交的數(shù)據(jù)。但注意在所有寫操作執(zhí)行時都會加排它鎖，那還怎么讀未提交呢？

該級別主要的特點是釋放鎖的時機(jī)與眾不同：在執(zhí)行完寫操作后立即釋放，而不像其他隔離級別在事務(wù)提交以后釋放。因此極易出現(xiàn)臟讀(不可重復(fù)讀和幻讀就更不用說了)

但該級別的并發(fā)性能也正因為鎖釋放得很早而變得很高，就連寫寫操作都很難產(chǎn)生鎖競爭。

3.5.2 讀提交

使用快照讀。讀未提交有很大的數(shù)據(jù)可靠性問題，所以再往前一步，我們就得到了讀提交。

如何解決臟讀：實現(xiàn)了快照讀，每一次select都會產(chǎn)生一個新的數(shù)據(jù)快照，實現(xiàn)讀提交的第一個要點就是將鎖的釋放時機(jī)延遲到事務(wù)提交之后，從而可以實現(xiàn)讀提交，解決了臟讀。

對并發(fā)有較大的影響：但是，鎖的釋放時機(jī)延遲了，不僅寫與寫操作之間會產(chǎn)生鎖競爭，在鎖釋放之前，也無法執(zhí)行讀操作，這對并發(fā)性產(chǎn)生了很大的影響。為了提高并發(fā)性，MySQL采用了一種名為MVCC的解決方案，MVCC可以解決這樣的問題：既然不想阻塞等待最新的數(shù)據(jù)，那就無視當(dāng)前持有鎖的事務(wù)，讀取最新的歷史版本數(shù)據(jù)。

因此，在讀已提交的級別下，我們每次執(zhí)行select操作時都會通過MVCC獲取當(dāng)前數(shù)據(jù)的最新快照，不加任何鎖，也無視任何鎖(因為歷史數(shù)據(jù)是構(gòu)造出來的，身上不可能有鎖)，完美解決讀寫之間的并發(fā)問題，和讀未提交的并發(fā)性能只差在寫寫操作上。

而為了進(jìn)一步提升寫寫操作上的并發(fā)性能，該級別下不會使用間隙鎖，無論什么查詢都只會加行鎖，而且在執(zhí)行完WHERE條件篩選之后，會立即釋放掉不符合條件的行鎖。

但是，正因為對并發(fā)性能的極致追求或者說貪婪，該級別下還是遺留了不可重復(fù)讀和幻讀問題：

為什么不能解決不可重復(fù)讀：因為每次select時，這就意味著，如果我們在事務(wù)A中執(zhí)行多次的select，在每次select之間有其他事務(wù)更新了我們讀取的數(shù)據(jù)并提交了，那就 出現(xiàn)了不可重復(fù)讀

鎖的范圍: 因為沒有間隙鎖，這就意味著，如果我們在事務(wù)A中多次執(zhí)行 select * from user where age>18 and age<30 for update 時，其他事務(wù)是可以往 age為(18,30) 這個區(qū)間插入/刪除數(shù)據(jù)的，那就 出現(xiàn)了幻讀

3.5.3 可重復(fù)讀

使用快照讀**。且只有第一次select才形成快照讀**。既然讀提交依然有較大的數(shù)據(jù)可靠性能問題，那我們可以進(jìn)一步進(jìn)行限制，這樣就得到了可重復(fù)讀，該級別在讀提交的基礎(chǔ)上做了兩點修改，從而避免了不可重復(fù)讀和幻讀：

如何解決臟讀：一開始的修改其實是對快照的修改，即使sessionA回滾了，也只是快照回滾，跟數(shù)據(jù)庫的數(shù)據(jù)沒什么關(guān)系。所以，sessionB查詢數(shù)據(jù)庫中數(shù)據(jù)的時候不用擔(dān)心臟讀問題，解決了臟讀。

如何解決不可重復(fù)讀：一次事務(wù)中只在第一次select時生成版本，后續(xù)的查詢都是在這個版本上進(jìn)行，那么下次使用相同語句查詢時，訪問的快照仍然是第一次產(chǎn)生的數(shù)據(jù)快照。那么即使當(dāng)session1提交(commit)事務(wù)之后，session2訪問的仍然是事務(wù)提交之前的即數(shù)據(jù)處于prepare狀態(tài)的數(shù)據(jù)快照。這就解決了不可重復(fù)讀的問題。

為什么解決不了幻讀： 鎖的范圍: 在行鎖的基礎(chǔ)上，加上Gap Lock（間隙鎖），從而形成Next-Key Lock，在所有遍歷過的(不管是否匹配條件)索引行上以及之間的區(qū)域上，都加上鎖，阻塞其他事務(wù)在遍歷范圍內(nèi)進(jìn)行寫操作，從而避免了幻讀。盡管InnoDB在可重復(fù)讀級別下已經(jīng)將數(shù)據(jù)可靠性和并發(fā)性能兩方面做得盡善盡美了，但前提是用戶查詢時能夠主動使用Locking Reads，即select … lock in share mode（共享鎖）和select … for update（排它鎖）。如果只是使用普通的select，依然防不住幻讀。這是因為MVCC的 快照只對讀操作 有效，對寫操作無效。

舉例說明會更清晰一點： 事務(wù)A依次執(zhí)行如下3條sql，事務(wù)B在語句1和2之間，插入10條age=20的記錄，事務(wù)A就幻讀了：

select count(1) from user where age=20;
-- return 0: 當(dāng)前沒有age=20的
update user set name=test where age=20;
-- Affects 10 rows: 因為事務(wù)B剛寫入10條age=20的記錄，而寫操作是不受MVCC影響，能看到最新數(shù)據(jù)的，所以更新成功，而一旦操作成功，這些被操作的數(shù)據(jù)就會對當(dāng)前事務(wù)可見
select count(1) from user where age=20;
-- return 10: 出現(xiàn)幻讀  

這種場景，需要用戶主動使用Locking Read來防止其他事務(wù)在查詢范圍內(nèi)進(jìn)行寫操作，因此，為了防患于未然，隔離級別又往前邁了一步

在MySQL數(shù)據(jù)庫中默認(rèn)的隔離級別為Repeatable read (可重復(fù)讀)。

3.5.4 串行化

使用當(dāng)前讀。該級別下，會自動將所有普通select轉(zhuǎn)化為 select … lock in share mode 執(zhí)行，即針對同一數(shù)據(jù)的所有讀寫都變成互斥的了，可靠性大大提高，并發(fā)性大大降低.

可重復(fù)讀級別下使用Locking Read也可以變成讀寫互斥，那這兩個有什么區(qū)別呢？可重復(fù)讀我們可以自己選擇是否使用Locking Read，更自由，所以在可重復(fù)讀下我們可以選擇使用普通的select讀寫實現(xiàn)并發(fā)。

3.5.5 在這里對介紹的四種隔離級別的實現(xiàn)做一個小結(jié)

• “讀未提交”隔離級別下直接返回記錄上的最新值，沒有MVCC視圖概念；
• 在“讀提交”和“可重復(fù)度讀“隔離級別下，數(shù)據(jù)庫里面會創(chuàng)建一個MVCC視圖，訪問的時候以視圖的邏輯結(jié)果為準(zhǔn)
• 在”讀提交“級別下，這個MVCC視圖是在每個 SQL 語句開始執(zhí)行的時候創(chuàng)建的，即快照讀；
• 在可重復(fù)讀的隔離級別下，InnoDB每次進(jìn)行select查詢語句，只有第一次執(zhí)行select語句會產(chǎn)生數(shù)據(jù)快照(整張表的快照)，之后執(zhí)行相同的select語句，不再產(chǎn)生數(shù)據(jù)快照，那么下次使用相同語句查詢時，訪問的快照仍然是第一次產(chǎn)生的數(shù)據(jù)快照。那么即使當(dāng)session1提交(commit)事務(wù)之后，session2訪問的仍然是事務(wù)提交之前的即數(shù)據(jù)處于prepare狀態(tài)的數(shù)據(jù)快照。這就解決了不可重復(fù)讀的問題。
• 而“串行化”隔離級別下直接用加鎖的方式來避免并行訪問。

3.6 事務(wù)的七大傳播行為

propagation_required
支持當(dāng)前事務(wù)，如果當(dāng)前沒有事務(wù)，就新建一個事務(wù)。這是最常見的選擇。

propagation_supports
支持當(dāng)前事務(wù)，如果當(dāng)前沒有事務(wù)，就以非事務(wù)方式執(zhí)行。

propagation_mandatory
支持當(dāng)前事務(wù)，如果當(dāng)前沒有事務(wù)，就拋出異常。

propagation_requires_new
新建事務(wù)，如果當(dāng)前存在事務(wù)，把當(dāng)前事務(wù)掛起。

propagation_not_supported
以非事務(wù)方式執(zhí)行操作，如果當(dāng)前存在事務(wù)，就把當(dāng)前事務(wù)掛起。

propagation_never
以非事務(wù)方式執(zhí)行，如果當(dāng)前存在事務(wù)，則拋出異常。

propagation_nested
如果當(dāng)前存在事務(wù)，則在嵌套事務(wù)內(nèi)執(zhí)行。如果當(dāng)前沒有事務(wù)，則新建一個事務(wù)

4 鎖

4.1 MySQL中有哪些鎖

行鎖【共享鎖（S）、排他鎖（X）】、表鎖【表共享讀鎖（read lock）、表獨(dú)占寫鎖（write lock）】、意向鎖、元數(shù)據(jù)鎖、間隙鎖、臨鍵鎖

表鎖： 顧名思義是對數(shù)據(jù)庫中表上鎖，表鎖有兩種形式，表共享讀鎖（簡稱”讀鎖“），表獨(dú)占寫鎖（簡稱”寫鎖“）

表共享讀鎖： 指定表加了讀鎖，不會影響其他線程的讀，但是會阻塞其他線程的寫

表獨(dú)占寫鎖： 指定表加了寫鎖，會阻塞其他線程的讀和寫

頁級鎖： 頁級鎖是MyS QL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

元數(shù)據(jù)鎖MDL：MDL加鎖過程是系統(tǒng)自動控制，無需顯式使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性（也就是表結(jié)構(gòu)在此時不能發(fā)生改變），在表上有活動事務(wù)的時候，不可以對元數(shù)據(jù)進(jìn)行寫入操作。為了避免DML與DDL沖突，保證讀寫的正確性。增刪改查，加MDL讀鎖(共享)；當(dāng)對表結(jié)構(gòu)進(jìn)行變更操作的時候，加MDL寫鎖(排他）

意向鎖：為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。在執(zhí)行DML操作時，會對涉及的行加行鎖，同時也會對該表加上意向鎖。而其他客戶端，在對這張表加表鎖的時候，會根據(jù)該表上所加的意向鎖來判定是否可以成功加表鎖，而不用逐行判斷行鎖情況了。

行鎖： 對數(shù)據(jù)庫表中的某一行加鎖。分為共享鎖和排他鎖。增刪改都是排他鎖，查是共享鎖

間隙鎖和臨鍵鎖：

A.索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優(yōu)化為間隙鎖 。（比如表里存在2,5兩條數(shù)據(jù)，查3這條數(shù)據(jù)，2和5之間就會被鎖，同時無法插入4）

B.索引上的等值查詢(非唯一普通索引)，向右遍歷時最后一個值不滿足查詢需求時，next-keylock 退化為間隙鎖。 假如，我們要根據(jù)這個二級索引查詢值為18的數(shù)據(jù)，并加上共享鎖，我們是只鎖定18這一行就可以了嗎？ 并不是，因為是非唯一索引，這個結(jié)構(gòu)中可能有多個18的存在，所以，在加鎖時會繼續(xù)往后找，找到一個不滿足條件的值（當(dāng)前案例中也就是29）。此時會對18加臨鍵鎖，并對29之前的間隙加鎖

C. 索引上的范圍查詢(唯一索引)–會訪問到不滿足條件的第一個值為止。查詢的條件為id>=19，并添加共享鎖。 此時我們可以根據(jù)數(shù)據(jù)庫表中現(xiàn)有的數(shù)據(jù)，將數(shù)據(jù)分為三個部分：[19]、(19,25]、(25,+∞]所以數(shù)據(jù)庫數(shù)據(jù)在加鎖是，就是將19加了行鎖，25的臨鍵鎖（包含25及25之前的間隙），正無窮的臨鍵鎖(正無窮及之前的間隙)

4.2 隔離級別與鎖的關(guān)系

在讀未提交（Read Uncommitted）級別下，讀取數(shù)據(jù)不需要加共享鎖，這樣就不會跟被修改的數(shù)據(jù)上的排他鎖沖突。

在Read Committed級別下，讀操作需要加共享鎖，但是在語句執(zhí)行完以后釋放共享鎖；

在Repeatable Read級別下，讀操作需要加行級共享鎖，但是在事務(wù)提交之前并不釋放共享鎖，也就是必須等待事務(wù)執(zhí)行完畢以后才釋放共享鎖。

SERIALIZABLE 是限制性最強(qiáng)的隔離級別，讀操作需要加表級共享鎖，因為該級別鎖定整個范圍的鍵，并一直持有鎖，直到事務(wù)完成。

5 集群模式

5.1 為什么要使用relay log，slave不可以直接讀取binlog嘛

中繼日志（relay log）只在主從服務(wù)器架構(gòu)的從服務(wù)器上存在。從服務(wù)器（slave）為了與主服務(wù)器(Master)保持一致，要從主服務(wù)器讀取二進(jìn)制日志的內(nèi)容，并且把讀取到的信息寫入本地的日志文件中，這個從服務(wù)器本地的日志文件就叫中繼日志。然后，從服務(wù)器讀取中繼日志，并根據(jù)中繼日志的內(nèi)容對從服務(wù)器的數(shù)據(jù)進(jìn)行更新，完成主從服務(wù)器的數(shù)據(jù)同步。

中繼日志是連接mastert(主服務(wù)器)和slave(從服務(wù)器)的信息，它是復(fù)制的核心，I/O線程將來自master的binlog存儲到中繼日志中，中繼日志充當(dāng)緩沖，這樣master不必等待slave執(zhí)行完成就可以發(fā)送下一個binlog。這樣master不必等待slave執(zhí)行完成就可以發(fā)送下一個binlog文章來源地址http://www.zghlxwxcb.cn/news/detail-472925.html

到了這里，關(guān)于Java 面試 | MySQL（2023版）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！