Mysql

關(guān)于mysql的面試題都在這里了

面試官：什么是聚集索引？什么是非聚集索引？

聚集索引就是索引和數(shù)據(jù)都在同一個(gè)文件里，如InnoDB的xxx.idb文件，企業(yè)開(kāi)發(fā)里，我似乎就沒(méi)有用過(guò)非innodb的引擎，所以，我們?nèi)粘ｉ_(kāi)發(fā)中使用的基本都是聚集索引。也就是B+tree樹(shù)。（這樣是不是就很容易記住了）

那什么是非聚集索引？那當(dāng)然是數(shù)據(jù)和索引不在同一個(gè)文件里了，類似MyISAM。

面試官：那B-tree 和 B+tree有什么區(qū)別？

（TMD，每次問(wèn)這個(gè)有意思嗎？）

B+tree葉子節(jié)點(diǎn)只存儲(chǔ)索引，不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在最下一層的節(jié)點(diǎn)，并且是一個(gè)雙向鏈表。

B-tree葉子節(jié)點(diǎn)即存儲(chǔ)索索引又存儲(chǔ)數(shù)據(jù)。

最底層是雙向鏈表：每個(gè)節(jié)點(diǎn)都會(huì)記住頭尾的指針，方便修改，新增，刪除，支持范圍查找。

面試官：那B+tree這樣設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)有什么好處？

B+tree只有在葉子結(jié)點(diǎn)上有數(shù)據(jù)，為了讓IO每次讀取的Page（16k）頁(yè)更加多。

cpu 一次io讀取16k，也就是一個(gè)非葉子結(jié)點(diǎn)，讀取B+tree一次可以讀取1170個(gè)非葉子結(jié)點(diǎn)的數(shù)據(jù)。

面試官：Innodb為什么必須有主鍵，而且用自增主鍵？而不是用UUID？

因?yàn)椋?/p>

1：整型節(jié)省空間，一個(gè)節(jié)點(diǎn)存儲(chǔ)更多的索引個(gè)數(shù)。

2.一個(gè)節(jié)點(diǎn)橫向掃描比較大小很快。

3.why用自增，insert的時(shí)候直接加到最后，快，也不會(huì)再葉子結(jié)點(diǎn)出現(xiàn)分支。

面試官：InnoDB 事務(wù)工作原理

InnoDB 是一個(gè)事務(wù)性的存儲(chǔ)引擎，而 InnoDB 的事務(wù)實(shí)現(xiàn)是基于事務(wù)日志 redo log 和 undo log 實(shí)現(xiàn)的

持久性：redo log 是重做日志，提供再寫入操作，實(shí)現(xiàn)事務(wù)的持久性；
一致性：undo log 是回滾日志，提供回滾操作，保證事務(wù)的一致性。

面試官：那么Innodb支持哪些隔離級(jí)別？

READ UNCOMMITTED (可以讀未提交的): 查詢可以讀取到其他事務(wù)正在修改的數(shù)據(jù),即使其他事務(wù)的修改還沒(méi)有提交.這種隔離等級(jí)無(wú)法避免臟讀.
READ COMMITTED(只可以讀已經(jīng)提交的):其他事務(wù)對(duì)數(shù)據(jù)庫(kù)的修改,只要已經(jīng)提交,其修改的結(jié)果就是可見(jiàn)的,與這兩個(gè)事務(wù)開(kāi)始的先后順序無(wú)關(guān).這種隔離等級(jí)避免了臟讀,但是無(wú)法實(shí)現(xiàn)可重復(fù)讀,甚至有可能產(chǎn)生幻讀.
REPEATABLE READ(可重復(fù)讀):比read committed更進(jìn)了一步,它只能讀取在它開(kāi)始之前已經(jīng)提交的事務(wù)對(duì)數(shù)據(jù)庫(kù)的修改,在它開(kāi)始以后,所有其他事務(wù)對(duì)數(shù)據(jù)庫(kù)的修改對(duì)它來(lái)說(shuō)均不可見(jiàn).從而實(shí)現(xiàn)了可重復(fù)讀,但是仍有可能幻
SERIALIZABLE(可串行化):這是事務(wù)隔離等級(jí)的最高級(jí)別.其實(shí)現(xiàn)原理就是對(duì)于所有的query,即使是查詢,也會(huì)加上讀鎖,避免其他事務(wù)對(duì)數(shù)據(jù)的修改.所以它成功的避免了幻讀.但是代價(jià)是,數(shù)據(jù)庫(kù)系統(tǒng)的并發(fā)處理能力大大降低,所以它不會(huì)被用到生產(chǎn)系統(tǒng)中.

那什么是幻想讀？ ??

“幻讀”又叫"幻象讀",是''不可重復(fù)讀''的一種特殊場(chǎng)景：當(dāng)事務(wù)1兩次執(zhí)行''SELECT ... WHERE''檢索一定范圍內(nèi)數(shù)據(jù)的操作中間，事務(wù)2在這個(gè)表中創(chuàng)建了(如[[INSERT]])了一行新數(shù)據(jù)，這條新數(shù)據(jù)正好滿足事務(wù)1的“WHERE”子句。如圖事務(wù)1執(zhí)行了兩遍同樣的查詢語(yǔ)句,第二遍比第一遍多出了一條數(shù)據(jù),這就是幻讀。

有點(diǎn)燒腦....

面試官：如何解決幻讀？

使用mvcc啊！！！

在快照讀讀情況下，mysql通過(guò)mvcc來(lái)避免幻讀。

在當(dāng)前讀讀情況下，mysql通過(guò)next-key或者gap 鎖來(lái)避免幻讀。

MVCC就是，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)用的技術(shù)。它使得大部分支持行鎖的事務(wù)引擎,不再單純的使用行鎖來(lái)進(jìn)行數(shù)據(jù)庫(kù)的并發(fā)控制,取而代之的是,把數(shù)據(jù)庫(kù)的行鎖與行的多個(gè)版本結(jié)合起來(lái),只需要很小的開(kāi)銷,就可以實(shí)現(xiàn)非鎖定讀,從而大大提高數(shù)據(jù)庫(kù)系統(tǒng)的并發(fā)性能。

實(shí)現(xiàn)原理：

innodb對(duì)每一行都加上了兩個(gè)隱含的列,其中一列存儲(chǔ)行被更新的”時(shí)間”,另外一列存儲(chǔ)行被刪除的”時(shí)間”. 但是innodb存儲(chǔ)的并不是絕對(duì)的時(shí)間,而是與時(shí)間對(duì)應(yīng)的數(shù)據(jù)庫(kù)系統(tǒng)的版本號(hào),每當(dāng)一個(gè)事務(wù)開(kāi)始的時(shí)候,innodb都會(huì)給這個(gè)事務(wù)分配一個(gè)遞增的版本號(hào),所以版本號(hào)也可以被認(rèn)為是事務(wù)號(hào).對(duì)于每一個(gè)”查詢”語(yǔ)句,innodb都會(huì)把這個(gè)查詢語(yǔ)句的版本號(hào)同這個(gè)查詢語(yǔ)句遇到的行的版本號(hào)進(jìn)行對(duì)比,然后結(jié)合不同的事務(wù)隔離等級(jí),來(lái)決定是否返回該行.

在讀取數(shù)據(jù)的時(shí)候,innodb幾乎不用獲得任何鎖, 每個(gè)查詢都通過(guò)版本檢查,只獲得自己需要的數(shù)據(jù)版本,從而大大提高了系統(tǒng)的并發(fā)度.

SELECT時(shí)，讀取創(chuàng)建版本號(hào)<=當(dāng)前事務(wù)版本號(hào)，刪除版本號(hào)為空或>當(dāng)前事務(wù)版本號(hào)。
INSERT時(shí)，保存當(dāng)前事務(wù)版本號(hào)為行的創(chuàng)建版本號(hào)
DELETE時(shí)，保存當(dāng)前事務(wù)版本號(hào)為行的刪除版本號(hào)
UPDATE時(shí)，插入一條新紀(jì)錄，保存當(dāng)前事務(wù)版本號(hào)為行創(chuàng)建版本號(hào)，同時(shí)保存當(dāng)前事務(wù)版本號(hào)到原來(lái)刪除的行

面試官：那你都知道m(xù)ysql里的哪些鎖，到底怎么用這些鎖？

（說(shuō)真的，開(kāi)發(fā)中真的太少見(jiàn)用這些鎖了，要不是為了面試，誰(shuí)會(huì)復(fù)習(xí)這破玩意兒）

行鎖：是加在索引行(對(duì)！是索引行！不是數(shù)據(jù)行！)上的鎖。比如select * from user where id=1 and id=10 for update，就會(huì)在id=1和id=10的索引行上加Record Lock。
Gap Locks 間隙鎖，它會(huì)鎖住兩個(gè)索引之間的區(qū)域。比如select * from user where id>1 and id<10 for update，就會(huì)在id為(1,10)的索引區(qū)間上加Gap Lock。
Next-Key Locks(間隙鎖)：也叫間隙鎖，它是Record Lock + Gap Lock形成的一個(gè)閉區(qū)間鎖。比如select * from user where id>=1 and id<=10 for update，就會(huì)在id為[1,10]的索引閉區(qū)間上加Next-Key Lock。這樣組合起來(lái)就有，行級(jí)共享鎖，表級(jí)共享鎖，行級(jí)排它鎖，表級(jí)排它鎖。
讀鎖：保證數(shù)據(jù)只能讀取，不能被修改。如果事務(wù)A對(duì)數(shù)據(jù)M加上S鎖，則事務(wù)A可以讀記錄M但不能修改記錄M，其他事務(wù)(這里用事務(wù)B)只能對(duì)記錄M再加上S鎖，不能加X(jué)鎖，直到事務(wù)A釋放了記錄M上的S鎖，保證了其他事務(wù)(事務(wù)B)可以讀記錄M，但在事務(wù)A釋放M上的S鎖之前不能對(duì)記錄M進(jìn)行任何修改。
寫鎖：若事務(wù)A對(duì)數(shù)據(jù)對(duì)象M加上X鎖，事務(wù)A可以讀記錄M也可以修改記錄M，其他事務(wù)(事務(wù)B)不能再對(duì)記錄M加任何鎖，直到事務(wù)A釋放記錄M上的鎖，保證了其他事務(wù)(事務(wù)B)在事務(wù)A釋放記錄M上的鎖之前不能再讀取和修改記錄M。

面試官：binlog用過(guò)嗎？

binlog是Mysql sever層維護(hù)的一種二進(jìn)制日志，與innodb引擎中的redo/undo log是完全不同的日志；其主要是用來(lái)記錄對(duì)mysql數(shù)據(jù)更新或潛在發(fā)生更新的SQL語(yǔ)句，記錄了所有的DDL和DML(除了數(shù)據(jù)查詢語(yǔ)句)語(yǔ)句，并以事務(wù)的形式保存在磁盤中，還包含語(yǔ)句所執(zhí)行的消耗的時(shí)間，MySQL的二進(jìn)制日志是事務(wù)安全型的。

一般來(lái)說(shuō)開(kāi)啟二進(jìn)制日志大概會(huì)有1%的性能損耗(參見(jiàn)MySQL官方中文手冊(cè) 5.1.24版)。

作用主要有：

復(fù)制：MySQL Replication在Master端開(kāi)啟binlog，Master把它的二進(jìn)制日志傳遞給slaves并回放來(lái)達(dá)到master-slave數(shù)據(jù)一致的目的
數(shù)據(jù)恢復(fù)：通過(guò)mysqlbinlog工具恢復(fù)數(shù)據(jù)
增量備份

寫再多可能就記不住了，抓住重點(diǎn)，這些足夠應(yīng)付大廠大頭兵崗位了。如果你覺(jué)得有用給我點(diǎn)個(gè)贊吧。我是王炸，下期見(jiàn)！！