Mysql 連環(huán)20問(wèn)
大家好，我是蘇三
一、說(shuō)下 MySQL 的 redo log 和 binlog？
#每天一道面試題# 35
#悟空擰螺絲# 2021-08-16
（1）MySQL 分兩層：Server 層和引擎層。區(qū)別如下：
Server 層：主要做的是 MySQL 功能層面的事情。Server 層也有自己的日志，稱為 binlog（歸檔日志）
引擎層：負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。redo log 是 InnoDB 引擎特有的日志。
（2）redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；binlog 是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
（3）redo log 是循環(huán)寫(xiě)的，空間固定會(huì)用完；
（4）binlog 是可以追加寫(xiě)入的。“追加寫(xiě)”是指 binlog 文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。
二、說(shuō)說(shuō)建立索引的優(yōu)勢(shì)、負(fù)面影響和原則？
#每天一道面試題# 37
#悟空擰螺絲# 2021-08-18
（1）索引的優(yōu)勢(shì)？
檢索速度：快速訪問(wèn)數(shù)據(jù)表中的特定信息，提高檢索速度。
唯一性：創(chuàng)建唯一性索引，保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
加速連接：加速表和表之間的連接。
減少分組和排序的時(shí)間：使用分組和排序進(jìn)行數(shù)據(jù)檢索時(shí)，可以顯著減少查詢中分組和排序的時(shí)間。
（2）索引的負(fù)面影響？
耗時(shí)：創(chuàng)建索引和維護(hù)索引需要耗費(fèi)時(shí)間，這個(gè)時(shí)間隨著數(shù)據(jù)量的增加而增加。
占空間：索引需要占用物理空間，不光是表需要占用數(shù)據(jù)空間，每個(gè)索引也需要占用物理空間。
維護(hù)速度：當(dāng)對(duì)表進(jìn)行增、刪、改、的時(shí)候索引也要?jiǎng)討B(tài)維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
（3）為數(shù)據(jù)表建立索引的原則有哪些？
在最頻繁使用的、用以縮小查詢范圍的字段上建立索引。
在頻繁使用的、需要排序的字段上建立索引。
（4）什么情況下不適合建立索引？
對(duì)于查詢中很少涉及的列或者重復(fù)值比較多的列，不宜建立索引。
對(duì)于一些特殊的數(shù)據(jù)類(lèi)型，不宜建立索引，比如文本字段（text）。
三、說(shuō)下 MySQL中的索引有哪些分類(lèi)？
#每天一道面試題# 40
#悟空擰螺絲# 2021-08-21
MySQL的所有列類(lèi)型都可以被索引。
MyISASM和InnoDB類(lèi)型的表默認(rèn)創(chuàng)建的都是BTREE索引；
MySQL中的索引是在存儲(chǔ)引擎層中實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同，也不是所有的存儲(chǔ)引擎都支持所有的索引類(lèi)型。
MySQL目前提供了以下幾種索引。
1）BTREE索引：最常見(jiàn)的索引類(lèi)型，大部分引擎都支持BTREE索引，例如MyISASM、InnoDB、MEMORY等。
2）HASH索引：只有MEMORY和NDB引擎支持，適用于簡(jiǎn)單場(chǎng)景。
3）RTREE索引（空間索引）：空間索引是MylSAM的一個(gè)特殊索引類(lèi)型，主要用于地理空間數(shù)據(jù)類(lèi)型，通常使用較少。
4）FULLTEXT（全文索引）：全文索引也是MylSAM的一個(gè)特殊索引類(lèi)型，主要用于全文索引，InnoDB從MySQL 5.6版本開(kāi)始提供對(duì)全文索引的支持。
四、說(shuō)下使用索引的推薦原則有哪些？
#每天一道面試題# 41
#悟空擰螺絲# 2021-08-22
（1）最適合索引的列是出現(xiàn)在WHERE子句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT關(guān)鍵字后的選擇列表中的列。
（2）使用唯一索引。唯一性索引的值是唯一的，可以更快速的通過(guò)該索引來(lái)確定某條記錄。
（3）不要過(guò)度索引。因?yàn)槊總€(gè)索引都要占用額外的磁盤(pán)空間，并降低寫(xiě)操作的性能，增加維護(hù)成本。在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)也可能需要重構(gòu)，因此，索引越多，維護(hù)索引所花的時(shí)間也就越長(zhǎng)。
（4）為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引。
（5）刪除不再使用或者很少使用的索引。
（6）利用最左原則。mysql建立多列索引（聯(lián)合索引）有最左前綴的原則，即最左優(yōu)先，如：
如果有一個(gè)2列的索引(col1,col2),則已經(jīng)對(duì)(col1)、(col1,col2)上建立了索引；
如果有一個(gè)3列索引(col1,col2,col3)，則已經(jīng)對(duì)(col1)、(col1,col2)、(col1,col2,col3) 上建立了索引。
五、說(shuō)下 MySQL 覆蓋索引？
#每天一道面試題# 44
#悟空擰螺絲# 2021-08-26
概念：如果一個(gè)索引包含（或者說(shuō)覆蓋了）所有滿足查詢所需要的數(shù)據(jù)，那么就稱這類(lèi)索引為覆蓋索引（Covering Index）。在 MySQL 中，可以通過(guò)使用 explain 命令輸出的 Extra 列來(lái)判斷是否使用了索引覆蓋查詢。若使用了索引覆蓋查詢，則 Extra 列包含“Using index””字符串。
大白話解釋?zhuān)簊elect 的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取，換句話說(shuō)查詢列要被所使用的索引覆蓋。
優(yōu)點(diǎn)：
1）覆蓋索引能有效地提高查詢性能，因?yàn)楦采w索引只需要讀取索引而不需要再回表讀取數(shù)據(jù)。MySQL查詢優(yōu)化器在執(zhí)行查詢前會(huì)判斷是否有一個(gè)索引能執(zhí)行覆蓋查詢。
2）索引項(xiàng)通常比記錄要小，所以MySQL會(huì)訪問(wèn)更少的數(shù)據(jù)。
補(bǔ)充：
不是所有類(lèi)型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲(chǔ)索引的列，而哈希索引、空間索引和全文索引等都不存儲(chǔ)索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引
六、說(shuō)下聯(lián)合索引的各種匹配規(guī)則？
#每天一道面試題# 48
#悟空擰螺絲# 2021-09-01
（1）等值匹配?
（2）最左側(cè)列匹配?
（3）最左前綴匹配原則?
（4）范圍查找規(guī)則?
（5）等值匹配+范圍匹配的規(guī)則
比如建立了 (a、b、c) 聯(lián)合索引，那么有以下幾種情況可以用到索引：
（對(duì)勾?表示索引全命中）
七、說(shuō)下 MySQL 回表？
#每天一道面試題# 49
#悟空擰螺絲# 2021-09-02
回表查詢：先到普通索引上定位主鍵值，再到聚集索引上定位行記錄，它的性能較掃一遍索引樹(shù)低（一般情況下）。
詳細(xì)說(shuō)明：
一般我們自己建的索引不管是單列索引還是聯(lián)合索引，都稱為普通索引，相對(duì)應(yīng)的另外一種就是聚簇索引。每個(gè)普通索引就對(duì)應(yīng)著一顆獨(dú)立的索引B+樹(shù)，索引 B+ 樹(shù)的節(jié)點(diǎn)僅僅包含了索引里的幾個(gè)字段的值以及主鍵值。
根據(jù)索引樹(shù)按照條件找到了需要的數(shù)據(jù)，僅僅是索引里的幾個(gè)字段的值和主鍵值，如果用 select * 則還需要很多其他的字段，就得走一個(gè)回表操作，根據(jù)主鍵再到主鍵的聚簇索引里去找，聚簇索引的葉子節(jié)點(diǎn)是數(shù)據(jù)頁(yè)，找到數(shù)據(jù)頁(yè)里才能把一行數(shù)據(jù)的所有字段值提取出來(lái)。
假設(shè) select * from table order by a,b,c 的語(yǔ)句，（table 有 abcdef 6 個(gè)字段），首先得從聯(lián)合索引的索引樹(shù)里按照順序 a、b、c 取出來(lái)所有數(shù)據(jù)，接著對(duì)每一條數(shù)據(jù)都根據(jù)主鍵到聚簇索引的查找，其實(shí)性能不高。
有時(shí)候 MySQL 引擎會(huì)覺(jué)得用了既用了聯(lián)合索引和聚簇索引來(lái)查找指定的字段，太慢了，那不不如直接全表掃描得了，只用聚集索引就行。
聚簇（聚集）索引補(bǔ)充：
只有一個(gè)聚集索引，聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄。根據(jù)聚簇索引的 key 查找是非常快的。
（1）如果表定義了主鍵，則主鍵就是聚集索引；
（2）如果表沒(méi)有定義PK，則第一個(gè) not NULL unique 列是聚集索引；
（3）否則，InnoDB 會(huì)創(chuàng)建一個(gè)隱藏的 row-id 作為聚集索引。
八、說(shuō)下 MySQL 最左匹配原則知道嗎？
#每天一道面試題# 46
#悟空擰螺絲# 2021-08-28
最左優(yōu)先，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上。同時(shí)遇到范圍查詢(>、<、between、like)就會(huì)停止匹配。
比如有聯(lián)合索引 [a、b、c]，where 過(guò)濾條件中哪些排列組合可以用到索引？（比如這種：where a=xxx b=xxx and c=xxx）
以下排列組合都會(huì)走索引：a、ab、ac、ba、ca、abc、acb、bac、bca、cab、cba。必須有一個(gè) a，排列組合中的順序會(huì)被優(yōu)化器優(yōu)化，所以不用關(guān)心順序。
以下排列組合不會(huì)走索引：b、c、bc、cb。因?yàn)闆](méi)有 a。
關(guān)于范圍查詢：a=xxx and b<10 and b > 5 and c =xxx，c 字段用不到索引，因?yàn)?b 是一個(gè)范圍查詢，遇到范圍查詢就停止了。
最左匹配原則的原理：我們都知道索引的底層是一顆 B+ 樹(shù)，那么聯(lián)合索引當(dāng)然還是一顆 B+ 樹(shù)，只不過(guò)聯(lián)合索引的健值數(shù)量不是一個(gè)，而是多個(gè)。構(gòu)建一顆B+樹(shù)只能根據(jù)一個(gè)值來(lái)構(gòu)建，因此數(shù)據(jù)庫(kù)依據(jù)聯(lián)合索引最左的字段來(lái)構(gòu)建B+樹(shù)。例子：假如創(chuàng)建一個(gè)（a,b)的聯(lián)合索引，那么它的索引樹(shù)是這樣的，如下圖所示：
所以 b = 2 這種查詢條件沒(méi)有辦法利用索引，因?yàn)槁?lián)合索引首先是按a排序的，b是無(wú)序的。
同時(shí)我們還可以發(fā)現(xiàn)在a值相等的情況下，b值又是按順序排列的，但是這種順序是相對(duì)的。所以最左匹配原則遇上范圍查詢就會(huì)停止，剩下的字段都無(wú)法使用索引。例如 a=1 and b=2 a,b 字段都可以使用索引，因?yàn)樵?a 值確定的情況下 b 是相對(duì)有序的，而 a>1 and b=2，a 字段可以匹配上索引，但 b 值不可以，因?yàn)閍的值是一個(gè)范圍，在這個(gè)范圍中b是無(wú)序的。
九、說(shuō)下你在使用索引上遇到的一些問(wèn)題？
#365天學(xué)習(xí)打卡# 65
#陽(yáng)光下的喵# 2021-08-29
索引的出現(xiàn)是為了提高查詢效率，但是使用索引也存在一些常見(jiàn)的思維誤區(qū):
用索引 和 用索引快速查詢 ?是有區(qū)別的，查詢SQL經(jīng)常提到全表掃描效率低，這個(gè)全表掃描默認(rèn)指主鍵索引全表掃描，實(shí)際操作中非聚簇索引也存在全表掃描；
覆蓋索引簡(jiǎn)單點(diǎn)理解就是查詢的條件和要查詢的值都在同一顆索引樹(shù)上這種現(xiàn)象稱為覆蓋索引可以通過(guò)執(zhí)行計(jì)劃查看，和索引類(lèi)型是不是唯一索引，關(guān)聯(lián)索引沒(méi)有聯(lián)系；覆蓋索引是經(jīng)常用來(lái)做索引優(yōu)化的一種手段(覆蓋索引也存在慢操作）；
最左前綴 - 關(guān)聯(lián)索引時(shí)候使用，這個(gè)最左指的不是字段上的最左，可以是字符串級(jí)別的；比如 name，age 是關(guān)聯(lián)索引 name 用模糊查詢也是最左前綴；索引下推(Icp) - 5.6以后引入的機(jī)制，關(guān)聯(lián)索引使用，不過(guò) ICP 在explain執(zhí)行索引計(jì)劃時(shí)候 可能沒(méi)遇到ICP，也會(huì)顯示 using index condition；
回表 - 查完索引要去主鍵索引樹(shù)上查一下，多了一次磁盤(pán) io；
小問(wèn)題:
“
person 表中有 id、name、age、gender四個(gè)屬性id為主鍵索引，name，age組合為聯(lián)合索引；這條SQL屬于 回表、覆蓋索引、最左前綴、索引下推中的那些場(chǎng)景？
”
select?id，name，age?from?person?where?name?like?'趙%'；
參考答案：
① 沒(méi)有回表！因?yàn)殛P(guān)聯(lián)索引會(huì)存儲(chǔ)主鍵id。
② 查詢列的數(shù)據(jù)都能在關(guān)聯(lián)索引所在的索引樹(shù)上查到，所以滿足覆蓋索引的條件；
③ 字符串可以通過(guò)前n個(gè)字符檢索數(shù)據(jù)進(jìn)行索引前綴查詢，所以也滿足最左前綴；
④ 不滿足索引下推，索引下推是5.6引入的優(yōu)化機(jī)制，為了減少回表次數(shù)；沒(méi)有發(fā)生回表操作也就不存在索引下推；
十、說(shuō)下索引條件下推（ICP）？
#365天學(xué)習(xí)打卡# 66
#陽(yáng)光下的喵# 2021-08-30
首先聲明一點(diǎn)索引下推不是只存在關(guān)聯(lián)索引中，普通索引也可以執(zhí)行索引下推；一般提到索引下推條件反射的都會(huì)先想到關(guān)聯(lián)索引查詢；
Using index condition 可以理解為 ICP 的必要不充分條件；即執(zhí)行計(jì)劃Extra中出現(xiàn)Using index condition，但是SQL語(yǔ)句卻不一定發(fā)生索引下推，using index condition 我理解的是“可以下推”，也就是說(shuō)“在執(zhí)行邏輯上可以下推，但不一定非要執(zhí)行索引下推這個(gè)操作”；
表結(jié)構(gòu)：person 表中有四個(gè)字段，id，name，age，gender，其中 id為主鍵，name，age 關(guān)聯(lián)索引；
舉個(gè)索引下推的簡(jiǎn)單例子：
存在兩條記錄 它們name都是 ab，age 值分別是 2，15；
select * FROM person WHERE name like 'ab%' and age = 2;這條sql在回表時(shí)只查詢一條數(shù)據(jù)，查找到所有name是 ab 的記錄以后，又作了age=2的判斷才進(jìn)行回表查詢；5.6以前是查到所有name是 ab 的兩條記錄以后直接回表然后和age做對(duì)比。
十一、說(shuō)下 MyISAM 和 InnoDB 的區(qū)別？
#每天一道面試題# 38
#悟空擰螺絲# 2021-08-19
MySQL 支持多種存儲(chǔ)引擎， MyISAM 和 InnoDB 存儲(chǔ)引擎只是其中的兩種。
MyISAM 存儲(chǔ)引擎：
5.5.8 版本之前的默認(rèn)引擎，支持全文檢索、壓縮、空間函數(shù)等。不支持事務(wù)和行級(jí)鎖，所以一般用于有大量查詢少量插入的場(chǎng)景來(lái)使用，而且 MyISAM 不支持外鍵，并且索引和數(shù)據(jù)是分開(kāi)存儲(chǔ)的。
InnoDB 存儲(chǔ)引擎：
5.5.8 版本之后的默認(rèn)引擎，實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)的四種隔離級(jí)別，默認(rèn)為 REPEATABLE 級(jí)別?；诰鄞厮饕⒌?，和 MyISAM 相反它支持事務(wù)、外鍵，并且通過(guò) MVCC 來(lái)支持高并發(fā)，索引和數(shù)據(jù)存儲(chǔ)在一起。
十二、說(shuō)下 MySQL 的 Buffer Pool 的工作原理？
#每天一道面試題# 39
#悟空擰螺絲# 2021-08-20
MySQL 先把磁盤(pán)里面的數(shù)據(jù)加載到 Buffer Pool 中，增刪改都是基于 Buffer Pool 里面的內(nèi)存數(shù)據(jù)進(jìn)行操作的，內(nèi)存的效率比 IO 高很多倍。改了內(nèi)存數(shù)據(jù)后，再定期刷新到磁盤(pán)。
Buffer Pool 有三大雙端鏈表：free、flush、lru 鏈表。
free 主要指向空閑緩存頁(yè)。 flush 指向已修改的緩存頁(yè)。 lru 指向被修改的緩存頁(yè)，并根據(jù)最近最少使用的規(guī)則進(jìn)行排序。
流程：
一邊不停地加載數(shù)據(jù)到緩存頁(yè)里去，一邊不停地查詢和修改緩存數(shù)據(jù)，然后free鏈表中的緩存頁(yè)不停地減少↓，flush鏈表中的緩存頁(yè)不停地增加↑，lru鏈表中的緩存頁(yè)不停的在增加↑和移動(dòng)←→。另外一邊，后臺(tái)線程不停的把 lru 鏈表的冷數(shù)據(jù)區(qū)域的緩存頁(yè)以及 flush 鏈表的緩存頁(yè)，刷入磁盤(pán)中來(lái)清空緩存 頁(yè)，然后flush鏈表和lru鏈表中的緩存頁(yè)在減少↓，free鏈表中的緩存頁(yè)在增加↑。
十三、說(shuō)下 InnoDB 存儲(chǔ)引擎中的鎖？
#每天一道面試題# 47
#悟空擰螺絲# 2021-08-31
行級(jí)鎖：共享鎖（S Lock），允許事務(wù)讀一行數(shù)據(jù)。排他鎖（X Lock），允許事務(wù)刪除或更新一行數(shù)據(jù)。
表級(jí)鎖：意向共享鎖（IS Lock），事務(wù)想要獲得一張表中某幾行的共享鎖。意向排他鎖（IX Lock），事務(wù)想要獲得一張表中某幾行的排他鎖。
補(bǔ)充：
1、若將上鎖的對(duì)象看成一顆樹(shù)，那么對(duì)最下層的對(duì)象上鎖，就是對(duì)最細(xì)粒度的對(duì)象進(jìn)行上鎖，首先就需要對(duì)粗粒度的對(duì)象上鎖，
比如需要給某記錄上 X 鎖，那么就需要先對(duì)數(shù)據(jù)庫(kù) A、表、頁(yè) 上意向鎖 IX，最后對(duì)對(duì)記錄上 X 鎖。2、默認(rèn)讀操作不加鎖，走 MVCC 多版本控制機(jī)制。
3、為什么要有意向鎖？如果加了行鎖，肯定之前就會(huì)給加上意向鎖，有其他事務(wù)想要鎖住表，先看有沒(méi)有表級(jí)意向鎖，這樣就不用到記錄上看有沒(méi)有 S 或 X 鎖，優(yōu)點(diǎn)就是快，省了很多步驟。
舉個(gè)生活中的例子：
圖書(shū)館有很多層，每一層有很多房間，二樓的 201 房間正在裝修，不能進(jìn)入，相當(dāng)于給 201 房間加了排他鎖，然后在二樓門(mén)口立了一個(gè)警示牌：二樓有人正在裝修（相當(dāng)于加了一個(gè)意向排他鎖）。到圖書(shū)館關(guān)門(mén)的點(diǎn)了，管理員開(kāi)始檢查各樓棟是否還有人，發(fā)現(xiàn)二樓有個(gè)警示牌，呀，還有人在裝修啊，暫時(shí)就不能關(guān)門(mén)了。這里把 201 房間當(dāng)做某行記錄，二樓當(dāng)做表。裝修比作事務(wù) 1，管理員要關(guān)閉圖書(shū)館比作事務(wù) 2。
文縐縐解釋：
IS、IX鎖是表級(jí)鎖，它們的提出僅僅為了在之后加表級(jí)別的S鎖和X鎖時(shí)可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來(lái)查看表中有沒(méi)有上鎖的記錄。就是說(shuō)當(dāng)對(duì)一個(gè)行加鎖之后，如果有打算給行所在的表加一個(gè)表鎖，必須先看看該表的行有沒(méi)有被加鎖，否則就會(huì)出現(xiàn)沖突。IS鎖和IX鎖就避免了判斷表中行有沒(méi)有加鎖時(shí)對(duì)每一行的遍歷。直接查看表有沒(méi)有意向鎖就可以知道表中有沒(méi)有行鎖。收起
十四、說(shuō)下 MySQL 中的 MVCC 機(jī)制？
#每天一道面試題# 50
#悟空擰螺絲# 2021-09-03
MySQL 中有四種隔離級(jí)別，Read Repeatable （RR）級(jí)別可以防止臟讀、不可重復(fù)讀、幻讀問(wèn)題。Read Committed （RC）級(jí)別解決了臟讀問(wèn)題。
那它是怎么做到的呢？就是利用了 MVCC 多版本控制機(jī)制。而且可以實(shí)現(xiàn) 讀-寫(xiě)，寫(xiě)-讀不沖突。
本解答盡量通俗易懂：
多版本
就是有多個(gè)某行記錄更新后的版本，然后將這些版本從上到下串起來(lái)。有點(diǎn)像串糖葫蘆，這個(gè)就是版本鏈。
比如說(shuō)銀行轉(zhuǎn)賬記錄，將多次對(duì)賬戶的修改都串起來(lái)了。記錄里面有是哪個(gè)事務(wù)做的轉(zhuǎn)賬記錄，最后值等于多少。
（1）賬戶 A ?= 初始值 200元，事務(wù) id = 40 ->
（2）賬戶 A ?= 初始值 200元 + 100 元 = 300 元，事務(wù) id = 51 ->
（3）賬戶 A ?= 300 元 + 50 元 = 350 元，事務(wù) id = 59 ->
（4）賬戶 A = 350 元 - 30 元 = 320 元，事務(wù) id = 72
在 MySQL 就是利用 undo log 日志將這些串起來(lái)的。
如下圖所示，undolog 的版本串起來(lái)長(zhǎng)這樣：
控制
用自身的事務(wù) id 和其他地方存的事務(wù) id 進(jìn)行比較，看是否符合讀取版本鏈上的條件，如果符合，讀取后就返回了。怎么控制的呢？利用 ReadView。ReadView 其實(shí)也不難理解，就是對(duì)當(dāng)前活躍事務(wù)的一個(gè)統(tǒng)計(jì)。然后 MySQL 利用這個(gè)數(shù)據(jù)統(tǒng)計(jì) + 版本鏈上的事務(wù) id 來(lái)進(jìn)行比較，獲得某個(gè)可讀到的版本。
ReadView
保證你只能讀到事務(wù)開(kāi)啟之前，別的事務(wù)提交的值，或者自己提交的值。其他情況下無(wú)法讀取到其他事務(wù)提交的值，避免了臟讀。
ReadView 生成時(shí)機(jī)？
每個(gè)事務(wù)執(zhí)行查詢時(shí)都會(huì)生成自己事務(wù)的 ReadView。RC 級(jí)別是每次查詢都會(huì)重新生成一份，RR 級(jí)別是事務(wù)中的 ReadView 都不變。
ReadView 里面有四個(gè)重要的屬性：
m_ids 事務(wù)列表：有哪些事務(wù)在MySQL里執(zhí)行還沒(méi)提交的；min_trx_id 最小事務(wù) id：m_ids 列表中最小的值 ；max_trx_id 最大事務(wù) id：下一個(gè)要生成的事務(wù)id，就是最大事務(wù)id；creator_trx_id：當(dāng)前事務(wù)的 id。
這四個(gè)屬性怎么用的呢？
比如說(shuō)事務(wù) A 用來(lái)查詢，事務(wù) B 用來(lái)更新，它倆都開(kāi)啟了事務(wù)，也都還沒(méi)有提交，對(duì)應(yīng)的事務(wù) id 分別為 51 和 59，那么 ReadView 就長(zhǎng)這樣：
活躍事務(wù)列表就是 [51，59]。最小事務(wù) id = 51。最大事務(wù) id = 59+1 = 60。當(dāng)前事務(wù) id = 51。
事務(wù) A 首先拿著這幾個(gè)屬性值，到版本鏈上一個(gè)一個(gè)比較版本上的事務(wù) id，符合條件就返回。比較又分三種情況：
1、如果版本上的事務(wù) id < 最小值 51，說(shuō)明這個(gè)行記錄在這些活躍的事務(wù)創(chuàng)建前就已經(jīng)提交了，這個(gè)行記錄的版本對(duì)于當(dāng)前事務(wù) A 是可見(jiàn)的，就返回了。
2、如果版本上的事務(wù) id >= 最大值 60，則說(shuō)明提交的事務(wù)是 ReadView 生成之后創(chuàng)建的，這個(gè)版本也是不可讀的，就接著往下找。
3、如果版本上的事務(wù) id 在在最小和最大值之間，就進(jìn)行下一步判斷：
3.1、如果在這個(gè)列表 [51，59] 里面，說(shuō)明提交的事務(wù)是和 A 事務(wù)差不多時(shí)間開(kāi)啟的事務(wù)，被 ReadView 記錄在列表里面了。這種事務(wù)提交的版本也是不可讀的，就接著往下找。（避免了臟讀） 3.2、如果不在這個(gè)列表 [51，59] 里面，說(shuō)明事務(wù)已提交了，是可以讀取的，讀到了就返回。
RC 的讀已提交怎么做到的？
我們說(shuō) RC 隔離級(jí)別下，事務(wù) A 下次查詢時(shí)，就可以讀到其他事務(wù)提交的數(shù)據(jù)了（讀已提交），但是根據(jù)上面的3.1 的情況來(lái)看，事務(wù) A 是讀取不到事務(wù) B 提交的呀？
這就需要在 A 查詢時(shí)重新生成一個(gè) ReadView 了，來(lái)看下重新生成的長(zhǎng)啥樣：
活躍事務(wù)列表就是 [51]。
最小事務(wù) id = 51。
最大事務(wù) id = 60。注意：這是 MySQL 下一個(gè)要生成的事務(wù) id，不是指活躍事務(wù)中的最大事務(wù) id。
當(dāng)前事務(wù) id = 51。
看到了嗎？
事務(wù) B 的 事務(wù) id 59 不在活躍事務(wù)列表啦！但是又是小于最大事務(wù) id 60 的。這就符合 3.2 的情況啦，可以讀到這個(gè)版本了。
那下次 事務(wù) A 再次查詢時(shí)，又會(huì)生成一個(gè) ReadView，可以讀到其他事務(wù)提交的數(shù)據(jù)，這個(gè)數(shù)據(jù)和上次的數(shù)據(jù)很有可能不一樣，也就是說(shuō)不能保證每次讀到的數(shù)據(jù)一樣的，這就是不可重復(fù)讀。RR 的可重復(fù)讀怎么做到的？
它和 RC 不同的地方在于，事務(wù) A 查詢時(shí)，是不會(huì)重新生成 ReadView 的，也就是說(shuō) B 提交的事務(wù)讀取不到的，那就順著版本鏈繼續(xù)找唄。找著找著就只能讀事務(wù) A 自己提交的，或者事務(wù)開(kāi)啟之前，其他事務(wù)提交的，那么事務(wù) A 每次查詢都是讀到一樣的數(shù)據(jù)啦，但是讀取的都不是最新的數(shù)據(jù)，這就是可重復(fù)讀，避免了讀取數(shù)據(jù)不一致的情況。
注意：不管其他事務(wù)怎么修改數(shù)據(jù)，事務(wù) A 生成的 ReadView 是不會(huì)改變的，基于這個(gè) ReadView 看到的值都是一樣的！
RR 的幻讀是怎么避免的？
比如 A 執(zhí)行范圍查詢：select * from table where age > 10，查到了一條數(shù)據(jù) X。然后事務(wù) C 72 插入了一條數(shù)據(jù)，事務(wù) A 再次查詢時(shí)，可以查到兩條數(shù)據(jù) X 和 Y。但是 Y 的版本鏈上事務(wù) id 等于 72，大于最大事務(wù) id 60，說(shuō)明是事務(wù) A 發(fā)起查詢后，當(dāng)然是不可讀到的了，所以事務(wù) A 還是只能讀到數(shù)據(jù) X。
小結(jié)
通過(guò)版本鏈 + ReadView 做到了這些事情：避免了 RR 隔離級(jí)別下的臟讀、不可重復(fù)度、幻讀問(wèn)題。避免了 RC 隔離級(jí)別下的臟讀問(wèn)題，實(shí)現(xiàn)了讀取已提交數(shù)據(jù)的功能。
十五、說(shuō)下執(zhí)行計(jì)劃？
#每天一道面試題# 51
在生產(chǎn)過(guò)程中，經(jīng)常會(huì)遇到因?yàn)镾QL語(yǔ)句導(dǎo)致的性能瓶頸問(wèn)題，這時(shí)候就需要我們?nèi)?yōu)化SQL的執(zhí)行效率；EXPLAIN語(yǔ)句的各個(gè)輸出項(xiàng)指標(biāo)可以幫助我們有針對(duì)性的提升查詢語(yǔ)句的性能。
執(zhí)行計(jì)劃重要指標(biāo)解釋(id主鍵，name,age 聯(lián)合索引, a 普通索引，b普通列)：
type 字段 =>>
????ALL - 最牛的優(yōu)化結(jié)果這里就不展開(kāi)介紹了
??? range - 索引范圍查詢；
??? ref - 二級(jí)索引等值查詢；
??? const - 主鍵/唯一索引等值查詢；
??? index_merge - 多個(gè)索引查詢；
??? index - 覆蓋索引且需要掃描全部的索引；
??? eq_ref - 被連接表主鍵/唯一引等值查詢；
possible_keys 字段 =>> 可能用到的索引(備胎）；
key 字段 =>> 實(shí)際用到的索引(轉(zhuǎn)正)；
key_len 字段 =>> 實(shí)際使用索引的最大長(zhǎng)度
ref 字段 =>> 等值查詢有一個(gè)常數(shù)/列值
??? const - 索引等值查詢；
????[DB].[table].[column] - 被調(diào)用表索引列等值查詢；
Extra字段 =>>
??? Impossible WHERE - where false；
??? Using index - 覆蓋索引；
??? Using index condition - 索引下推；
??? Using where - 順序掃描，where條件查詢。
下面幾張圖說(shuō)明了具體出現(xiàn)的場(chǎng)景：
十六、說(shuō)下主從復(fù)制原理？
#每天一道面試題# 52
主從復(fù)制主要有以下流程：
master服務(wù)器將數(shù)據(jù)的改變記錄到binlog中；
slave服務(wù)器會(huì)在一定時(shí)間間隔內(nèi)對(duì)master 的binlog進(jìn)行檢查，如果發(fā)生改變，則開(kāi)始一個(gè)I/OThread請(qǐng)求讀取master中binlog；
同時(shí)主節(jié)點(diǎn)為每個(gè)I/O線程啟動(dòng)一個(gè)dump線程，用于向其發(fā)送二進(jìn)制事件，并保存至從節(jié)點(diǎn)本地的中繼日志中，從節(jié)點(diǎn)將啟動(dòng)SQL線程從中繼日志中讀取二進(jìn)制日志，在本地重放，使得其數(shù)據(jù)和主節(jié)點(diǎn)的保持一致，最后I/OThread和SQLThread將進(jìn)入睡眠狀態(tài)，等待下一次被喚醒；
大白話解釋：
從庫(kù)會(huì)生成兩個(gè)線程,一個(gè)I/O線程,一個(gè)SQL線程;
I/O線程會(huì)去請(qǐng)求主庫(kù)的binlog,并將得到的binlog寫(xiě)到本地的relay-log(中繼日志)文件中;
主庫(kù)會(huì)生成一個(gè)dump線程,用來(lái)給從庫(kù)I/O線程傳binlog;
SQL線程,會(huì)讀取relay log文件中的日志,并解析成sql語(yǔ)句逐一執(zhí)行;
主從復(fù)制存在數(shù)據(jù)丟失問(wèn)題的解決方案：在使用過(guò)程中需要開(kāi)啟半同步復(fù)制；
主從復(fù)制的使用場(chǎng)景主要有以下兩種：HA、讀寫(xiě)分離。
高可用(HA)架構(gòu)：
MySQL 的高可用由互為主從的MySQL構(gòu)成，平時(shí)只有主庫(kù)提供服務(wù)，備庫(kù)不提供服務(wù)。當(dāng)主庫(kù)停止服務(wù)時(shí)，服務(wù)自動(dòng)切換到備庫(kù)。
MHA管理工具 MHA存在Manager、Node兩種節(jié)點(diǎn)；Manager節(jié)點(diǎn)通過(guò)探測(cè)Node節(jié)點(diǎn)去判斷 MySQL運(yùn)行是否正常，如果發(fā)現(xiàn) Master故障。
就把他的一個(gè)Slave提升為Master，然后剩余Slave都掛到新的Master。
LVS+Keepalived
Keepalived可以進(jìn)行檢查心跳和動(dòng)態(tài)漂移；當(dāng)Master節(jié)點(diǎn)出現(xiàn)異常主服務(wù)所在keepalived會(huì)發(fā)出通知，然后slave節(jié)點(diǎn)的keepalived通知從節(jié)點(diǎn)切換為master。
讀寫(xiě)分離架構(gòu)：
高并發(fā)下讀寫(xiě)分離會(huì)出現(xiàn)數(shù)據(jù)延遲問(wèn)題。
解決方案如下：
分庫(kù)分表； 開(kāi)啟并行復(fù)制； 在業(yè)務(wù)邏輯上避免；
補(bǔ)充資料：
（以下MySQL 主從同步的原理的補(bǔ)充資料來(lái)自艾小仙）
master提交完事務(wù)后，寫(xiě)入binlog。 slave連接到master，獲取binlog。 master創(chuàng)建dump線程，推送binglog到slave。 slave啟動(dòng)一個(gè)IO線程讀取同步過(guò)來(lái)的master的binlog，記錄到relay log中繼日志中。 slave再開(kāi)啟一個(gè)sql線程讀取relay log事件并在slave執(zhí)行，完成同步。 slave記錄自己的binglog。
由于mysql默認(rèn)的復(fù)制方式是異步的，主庫(kù)把日志發(fā)送給從庫(kù)后不關(guān)心從庫(kù)是否已經(jīng)處理，這樣會(huì)產(chǎn)生一個(gè)問(wèn)題就是假設(shè)主庫(kù)掛了，從庫(kù)處理失敗了，這時(shí)候從庫(kù)升為主庫(kù)后，日志就丟失了。由此產(chǎn)生兩個(gè)概念。
全同步復(fù)制
主庫(kù)寫(xiě)入binlog后強(qiáng)制同步日志到從庫(kù)，所有的從庫(kù)都執(zhí)行完成后才返回給客戶端，但是很顯然這個(gè)方式的話性能會(huì)受到嚴(yán)重影響。
半同步復(fù)制
和全同步不同的是，半同步復(fù)制的邏輯是這樣，從庫(kù)寫(xiě)入日志成功后返回ACK確認(rèn)給主庫(kù)，主庫(kù)收到至少一個(gè)從庫(kù)的確認(rèn)就認(rèn)為寫(xiě)操作完成。
十七、說(shuō)下 SQL 標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別？
讀未提交（read uncommitted）是指，一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被別的事務(wù)看到。
讀提交（read committed）是指，一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到。
可重復(fù)讀（repeatable read）是指，一個(gè)事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級(jí)別下，未提交變更對(duì)其他事務(wù)也是不可見(jiàn)的。
串行化（serializable ），顧名思義是對(duì)于同一行記錄，“寫(xiě)”會(huì)加“寫(xiě)鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫(xiě)鎖沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。
十八、說(shuō)說(shuō)生成唯一 ID 的雪花算法是怎么樣的？
每天一道面試題 16 悟空擰螺絲 2021-07-28
snowflake（雪花算法）：Twitter 開(kāi)源的分布式 id 生成算法，64 位的 long 型的 id，分為 4 部分：
1 bit：不用，統(tǒng)一為 0 41 bits：毫秒時(shí)間戳，可以表示 69 年的時(shí)間。 10 bits：5 bits 代表機(jī)房 id，5 個(gè) bits 代表機(jī)器 id。最多代表 32 個(gè)機(jī)房，每個(gè)機(jī)房最多代表 32 臺(tái)機(jī)器。 12 bits：同一毫秒內(nèi)的 id，最多 4096 個(gè)不同 id，自增模式
優(yōu)點(diǎn)：
毫秒數(shù)在高位，自增序列在低位，整個(gè)ID都是趨勢(shì)遞增的。 不依賴數(shù)據(jù)庫(kù)等第三方系統(tǒng)，以服務(wù)的方式部署，穩(wěn)定性更高，生成ID的性能也是非常高的。 可以根據(jù)自身業(yè)務(wù)特性分配bit位，非常靈活。
缺點(diǎn)：
強(qiáng)依賴機(jī)器時(shí)鐘，如果機(jī)器上時(shí)鐘回?fù)埽梢运阉?2017 年閏秒 7:59:60），會(huì)導(dǎo)致發(fā)號(hào)重復(fù)或者服務(wù)會(huì)處于不可用狀態(tài)。
十九、說(shuō)下 MySQL 內(nèi)部的 XA 分布式事務(wù)？
#每天一道面試題# 42
#悟空擰螺絲# 2021-08-23
XA是X/Open DTP組織（X/Open DTP group）定義的兩階段提交協(xié)議。
MySQL本身的插件式架構(gòu)導(dǎo)致在其內(nèi)部需要使用XA事務(wù)，此時(shí)MySQL即是協(xié)調(diào)者，也是參與者。內(nèi)部XA事務(wù)發(fā)生在存儲(chǔ)引擎與插件之間或者存儲(chǔ)引擎與存儲(chǔ)引擎之間。例如，不同的存儲(chǔ)引擎之間是完全獨(dú)立的，因此當(dāng)一個(gè)事務(wù)涉及兩個(gè)不同的存儲(chǔ)引擎時(shí)，就必須使用內(nèi)部XA事務(wù)。由于只在單機(jī)上工作，所以被稱為內(nèi)部XA。
最為常見(jiàn)的內(nèi)部XA事務(wù)存在于二進(jìn)制日志（Binlog）和InnoDB存儲(chǔ)引擎之間。由于復(fù)制的需要，因此，目前絕大多數(shù)的數(shù)據(jù)庫(kù)都開(kāi)啟了Binlog功能。
在事務(wù)提交時(shí)，先寫(xiě)二進(jìn)制日志，再寫(xiě)InnoDB存儲(chǔ)引擎的重做日志。對(duì)上述兩個(gè)操作的要求也是原子的，即二進(jìn)制日志和重做日志必須同時(shí)寫(xiě)入。若二進(jìn)制日志先寫(xiě)了，而在寫(xiě)入InnoDB存儲(chǔ)引擎時(shí)發(fā)生了宕機(jī)，那么Slave可能會(huì)接收到Master傳過(guò)去的二進(jìn)制日志并執(zhí)行，最終導(dǎo)致了主從不一致的情況發(fā)生。
為了解決這個(gè)問(wèn)題，MySQL數(shù)據(jù)庫(kù)在Binlog與InnoDB存儲(chǔ)引擎之間采用XA事務(wù)。當(dāng)事務(wù)提交時(shí)，InnoDB存儲(chǔ)引擎會(huì)先做一個(gè)PREPARE操作，將事務(wù)的Xid寫(xiě)入，接著進(jìn)行Binlog的寫(xiě)入。如果在Binlog存儲(chǔ)引擎提交前，MYSQL數(shù)據(jù)庫(kù)宕機(jī)了，那么MySQL數(shù)據(jù)庫(kù)在重啟后會(huì)先檢查準(zhǔn)備的UXID事務(wù)是否已經(jīng)提交，若沒(méi)有，則在存儲(chǔ)引擎層再進(jìn)行一次提交操作
二十、說(shuō)下 MySQL 的外部 XA 事務(wù)？
#每天一道面試題# 43 #悟空擰螺絲# 2021-08-24
（1）XA 事務(wù)是什么
XA是X/Open DTP組織（X/Open DTP group）定義的兩階段提交協(xié)議。
分布式事務(wù)通常采用 2PC 協(xié)議，全稱 Two Phase Commitment Protocol（兩階段提交協(xié)議）。該協(xié)議主要為了解決在分布式數(shù)據(jù)庫(kù)場(chǎng)景下，所有節(jié)點(diǎn)間數(shù)據(jù)一致性的問(wèn)題。分布式事務(wù)通過(guò)2PC協(xié)議將提交分成兩個(gè)階段：
階段一為準(zhǔn)備（prepare）階段。即所有的參與者準(zhǔn)備執(zhí)行事務(wù)并鎖住需要的資源。參與者 ready 時(shí)，向transaction manager 報(bào)告已準(zhǔn)備就緒。
階段二為提交階段（commit）。當(dāng) transaction manager 確認(rèn)所有參與者都 ready 后，向所有參與者發(fā)送 commit命令。
缺點(diǎn)：第一個(gè)階段會(huì)鎖定資源，等待其他參與者 Ready，在高并發(fā)場(chǎng)景下，會(huì)嚴(yán)重影響系統(tǒng)的吞吐量。
（2）MySQL 的外部 XA
MySQL在執(zhí)行分布式事務(wù)（外部XA）的時(shí)候，MySQL服務(wù)器相當(dāng)于XA“事務(wù)資源管理器”，
與MySQL連接的客戶端相當(dāng)于“事務(wù)管理器”，比如 Java 應(yīng)用程序。
事務(wù)管理器負(fù)責(zé)協(xié)調(diào)多個(gè)數(shù)據(jù)庫(kù)的事務(wù)，先問(wèn)問(wèn)各個(gè)數(shù)據(jù)庫(kù)準(zhǔn)備好了嗎？如果準(zhǔn)備好了，則在數(shù)據(jù)庫(kù)執(zhí)行操作，如果任一數(shù)據(jù)庫(kù)沒(méi)有準(zhǔn)備，則回滾事務(wù)。
MySQL支持 XASTART/END/PREPARE/COMMIT 這些SQL語(yǔ)句，通過(guò)使用這些命令可以完成分布式事務(wù)的狀態(tài)轉(zhuǎn)移。
內(nèi)部XA事務(wù)用于同一實(shí)例下跨多引擎事務(wù)，而外部XA事務(wù)用于跨多 MySQL 實(shí)例的分布式事務(wù)，需要應(yīng)用層作為協(xié)調(diào)者。應(yīng)用層負(fù)責(zé)決定提交還是回滾。
