《我想進(jìn)大廠》之mysql奪命連環(huán)13問

想進(jìn)大廠，mysql不會那可不行，來接受mysql面試挑戰(zhàn)吧，看看你能堅持到哪里？

1. 能說下myisam 和 innodb的區(qū)別嗎？

myisam引擎是5.1版本之前的默認(rèn)引擎，支持全文檢索、壓縮、空間函數(shù)等，但是不支持事務(wù)和行級鎖，所以一般用于有大量查詢少量插入的場景來使用，而且myisam不支持外鍵，并且索引和數(shù)據(jù)是分開存儲的。

innodb是基于聚簇索引建立的，和myisam相反它支持事務(wù)、外鍵，并且通過MVCC來支持高并發(fā)，索引和數(shù)據(jù)存儲在一起。

2. 說下mysql的索引有哪些吧，聚簇和非聚簇索引又是什么？

索引按照數(shù)據(jù)結(jié)構(gòu)來說主要包含B+樹和Hash索引。

假設(shè)我們有張表，結(jié)構(gòu)如下：

create?table?user(
?id?int(11)?not?null,
??age?int(11)?not?null,
??primary?key(id),
??key(age)
);

B+樹是左小右大的順序存儲結(jié)構(gòu)，節(jié)點只包含id索引列，而葉子節(jié)點包含索引列和數(shù)據(jù)，這種數(shù)據(jù)和索引在一起存儲的索引方式叫做聚簇索引，一張表只能有一個聚簇索引。假設(shè)沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替，如果沒有的話則會隱式定義一個主鍵作為聚簇索引。

這是主鍵聚簇索引存儲的結(jié)構(gòu)，那么非聚簇索引的結(jié)構(gòu)是什么樣子呢？非聚簇索引(二級索引)保存的是主鍵id值，這一點和myisam保存的是數(shù)據(jù)地址是不同的。

最終，我們一張圖看看InnoDB和Myisam聚簇和非聚簇索引的區(qū)別

3. 那你知道什么是覆蓋索引和回表嗎？

覆蓋索引指的是在一次查詢中，如果一個索引包含或者說覆蓋所有需要查詢的字段的值，我們就稱之為覆蓋索引，而不再需要回表查詢。

而要確定一個查詢是否是覆蓋索引，我們只需要explain sql語句看Extra的結(jié)果是否是“Using index”即可。

以上面的user表來舉例，我們再增加一個name字段，然后做一些查詢試試。

explain?select?*?from?user?where?age=1;?//查詢的name無法從索引數(shù)據(jù)獲取
explain?select?id,age?from?user?where?age=1;?//可以直接從索引獲取

4. 鎖的類型有哪些呢

mysql鎖分為共享鎖和排他鎖，也叫做讀鎖和寫鎖。

讀鎖是共享的，可以通過lock in share mode實現(xiàn)，這時候只能讀不能寫。

寫鎖是排他的，它會阻塞其他的寫鎖和讀鎖。從顆粒度來區(qū)分，可以分為表鎖和行鎖兩種。

表鎖會鎖定整張表并且阻塞其他用戶對該表的所有讀寫操作，比如alter修改表結(jié)構(gòu)的時候會鎖表。

行鎖又可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現(xiàn)，樂觀鎖則通過版本號實現(xiàn)。

5. 你能說下事務(wù)的基本特性和隔離級別嗎？

事務(wù)基本特性ACID分別是：

原子性指的是一個事務(wù)中的操作要么全部成功，要么全部失敗。

一致性指的是數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另外一個一致性的狀態(tài)。比如A轉(zhuǎn)賬給B100塊錢，假設(shè)中間sql執(zhí)行過程中系統(tǒng)崩潰A也不會損失100塊，因為事務(wù)沒有提交，修改也就不會保存到數(shù)據(jù)庫。

隔離性指的是一個事務(wù)的修改在最終提交前，對其他事務(wù)是不可見的。

持久性指的是一旦事務(wù)提交，所做的修改就會永久保存到數(shù)據(jù)庫中。

而隔離性有4個隔離級別，分別是：

read uncommit?讀未提交，可能會讀到其他事務(wù)未提交的數(shù)據(jù)，也叫做臟讀。

用戶本來應(yīng)該讀取到id=1的用戶age應(yīng)該是10，結(jié)果讀取到了其他事務(wù)還沒有提交的事務(wù)，結(jié)果讀取結(jié)果age=20，這就是臟讀。

read commit?讀已提交，兩次讀取結(jié)果不一致，叫做不可重復(fù)讀。

不可重復(fù)讀解決了臟讀的問題，他只會讀取已經(jīng)提交的事務(wù)。

用戶開啟事務(wù)讀取id=1用戶，查詢到age=10，再次讀取發(fā)現(xiàn)結(jié)果=20，在同一個事務(wù)里同一個查詢讀取到不同的結(jié)果叫做不可重復(fù)讀。

repeatable read?可重復(fù)復(fù)讀，這是mysql的默認(rèn)級別，就是每次讀取結(jié)果都一樣，但是有可能產(chǎn)生幻讀。

serializable?串行，一般是不會使用的，他會給每一行讀取的數(shù)據(jù)加鎖，會導(dǎo)致大量超時和鎖競爭的問題。

6. 那ACID靠什么保證的呢？

A原子性由undo log日志保證，它記錄了需要回滾的日志信息，事務(wù)回滾時撤銷已經(jīng)執(zhí)行成功的sql

C一致性一般由代碼層面來保證

I隔離性由MVCC來保證

D持久性由內(nèi)存+redo log來保證，mysql修改數(shù)據(jù)同時在內(nèi)存和redo log記錄這次操作，事務(wù)提交的時候通過redo log刷盤，宕機(jī)的時候可以從redo log恢復(fù)

7. 那你說說什么是幻讀，什么是MVCC？

要說幻讀，首先要了解MVCC，MVCC叫做多版本并發(fā)控制，實際上就是保存了數(shù)據(jù)在某個時間節(jié)點的快照。

我們每行數(shù)實際上隱藏了兩列，創(chuàng)建時間版本號，過期(刪除)時間版本號，每開始一個新的事務(wù)，版本號都會自動遞增。

還是拿上面的user表舉例子，假設(shè)我們插入兩條數(shù)據(jù)，他們實際上應(yīng)該長這樣。

這時候假設(shè)小明去執(zhí)行查詢，此時current_version=3

select?*?from?user?where?id<=3;

同時，小紅在這時候開啟事務(wù)去修改id=1的記錄，current_version=4

update?user?set?name='張三三'?where?id=1;

執(zhí)行成功后的結(jié)果是這樣的

如果這時候還有小黑在刪除id=2的數(shù)據(jù)，current_version=5，執(zhí)行后結(jié)果是這樣的。

由于MVCC的原理是查找創(chuàng)建版本小于或等于當(dāng)前事務(wù)版本，刪除版本為空或者大于當(dāng)前事務(wù)版本，小明的真實的查詢應(yīng)該是這樣

select?*?from?user?where?id<=3?and?create_version<=3?and?(delete_version>3?or?delete_version?is?null);

所以小明最后查詢到的id=1的名字還是'張三'，并且id=2的記錄也能查詢到。這樣做是為了保證事務(wù)讀取的數(shù)據(jù)是在事務(wù)開始前就已經(jīng)存在的，要么是事務(wù)自己插入或者修改的。

明白MVCC原理，我們來說什么是幻讀就簡單多了。舉一個常見的場景，用戶注冊時，我們先查詢用戶名是否存在，不存在就插入，假定用戶名是唯一索引。

1. 小明開啟事務(wù)current_version=6查詢名字為'王五'的記錄，發(fā)現(xiàn)不存在。

2. 小紅開啟事務(wù)current_version=7插入一條數(shù)據(jù)，結(jié)果是這樣：

3. 小明執(zhí)行插入名字'王五'的記錄，發(fā)現(xiàn)唯一索引沖突，無法插入，這就是幻讀。

8. 那你知道什么是間隙鎖嗎？

間隙鎖是可重復(fù)讀級別下才會有的鎖，結(jié)合MVCC和間隙鎖可以解決幻讀的問題。我們還是以user舉例，假設(shè)現(xiàn)在user表有幾條記錄

當(dāng)我們執(zhí)行：

begin;
select?*?from?user?where?age=20?for?update;

begin;
insert?into?user(age)?values(10);?#成功
insert?into?user(age)?values(11);?#失敗
insert?into?user(age)?values(20);?#失敗
insert?into?user(age)?values(21);?#失敗
insert?into?user(age)?values(30);?#失敗

只有10可以插入成功，那么因為表的間隙mysql自動幫我們生成了區(qū)間(左開右閉)

(negative?infinity，10],(10,20],(20,30],(30,positive?infinity)

由于20存在記錄，所以(10,20]，(20,30]區(qū)間都被鎖定了無法插入、刪除。

如果查詢21呢？就會根據(jù)21定位到(20,30)的區(qū)間(都是開區(qū)間)。

需要注意的是唯一索引是不會有間隙索引的。

9. 你們數(shù)據(jù)量級多大？分庫分表怎么做的？

首先分庫分表分為垂直和水平兩個方式，一般來說我們拆分的順序是先垂直后水平。

垂直分庫

基于現(xiàn)在微服務(wù)拆分來說，都是已經(jīng)做到了垂直分庫了

垂直分表

如果表字段比較多，將不常用的、數(shù)據(jù)較大的等等做拆分

水平分表

首先根據(jù)業(yè)務(wù)場景來決定使用什么字段作為分表字段(sharding_key)，比如我們現(xiàn)在日訂單1000萬，我們大部分的場景來源于C端，我們可以用user_id作為sharding_key，數(shù)據(jù)查詢支持到最近3個月的訂單，超過3個月的做歸檔處理，那么3個月的數(shù)據(jù)量就是9億，可以分1024張表，那么每張表的數(shù)據(jù)大概就在100萬左右。

比如用戶id為100，那我們都經(jīng)過hash(100)，然后對1024取模，就可以落到對應(yīng)的表上了。

10. 那分表后的ID怎么保證唯一性的呢？

因為我們主鍵默認(rèn)都是自增的，那么分表之后的主鍵在不同表就肯定會有沖突了。有幾個辦法考慮：

1. 設(shè)定步長，比如1-1024張表我們分別設(shè)定1-1024的基礎(chǔ)步長，這樣主鍵落到不同的表就不會沖突了。
2. 分布式ID，自己實現(xiàn)一套分布式ID生成算法或者使用開源的比如雪花算法這種
3. 分表后不使用主鍵作為查詢依據(jù)，而是每張表單獨新增一個字段作為唯一主鍵使用，比如訂單表訂單號是唯一的，不管最終落在哪張表都基于訂單號作為查詢依據(jù)，更新也一樣。

11. 分表后非sharding_key的查詢怎么處理呢？

1. 可以做一個mapping表，比如這時候商家要查詢訂單列表怎么辦呢？不帶user_id查詢的話你總不能掃全表吧？所以我們可以做一個映射關(guān)系表，保存商家和用戶的關(guān)系，查詢的時候先通過商家查詢到用戶列表，再通過user_id去查詢。
2. 打?qū)挶恚话愣?，商戶端對?shù)據(jù)實時性要求并不是很高，比如查詢訂單列表，可以把訂單表同步到離線（實時）數(shù)倉，再基于數(shù)倉去做成一張寬表，再基于其他如es提供查詢服務(wù)。
3. 數(shù)據(jù)量不是很大的話，比如后臺的一些查詢之類的，也可以通過多線程掃表，然后再聚合結(jié)果的方式來做?；蛘弋惒降男问揭彩强梢缘?。

List>>?taskList?=?Lists.newArrayList();
for?(int?shardingIndex?=?0;?shardingIndex?1024;?shardingIndex++)?{
????taskList.add(()?->?(userMapper.getProcessingAccountList(shardingIndex)));
}
List?list?=?null;
try?{
????list?=?taskExecutor.executeTask(taskList);
}?catch?(Exception?e)?{
????//do?something
}

public?class?TaskExecutor?{
????public??List?executeTask(Collection>?tasks)?throws?Exception?{
????????List?result?=?Lists.newArrayList();
????????List>?futures?=?ExecutorUtil.invokeAll(tasks);
????????for?(Future?future?:?futures)?{
????????????result.add(future.get());
????????}
????????return?result;
????}
}

12. 說說mysql主從同步怎么做的吧？

首先先了解mysql主從同步的原理

1. master提交完事務(wù)后，寫入binlog
2. slave連接到master，獲取binlog
3. master創(chuàng)建dump線程，推送binglog到slave
4. slave啟動一個IO線程讀取同步過來的master的binlog，記錄到relay log中繼日志中
5. slave再開啟一個sql線程讀取relay log事件并在slave執(zhí)行，完成同步
6. slave記錄自己的binglog

由于mysql默認(rèn)的復(fù)制方式是異步的，主庫把日志發(fā)送給從庫后不關(guān)心從庫是否已經(jīng)處理，這樣會產(chǎn)生一個問題就是假設(shè)主庫掛了，從庫處理失敗了，這時候從庫升為主庫后，日志就丟失了。由此產(chǎn)生兩個概念。

全同步復(fù)制

主庫寫入binlog后強(qiáng)制同步日志到從庫，所有的從庫都執(zhí)行完成后才返回給客戶端，但是很顯然這個方式的話性能會受到嚴(yán)重影響。

半同步復(fù)制

和全同步不同的是，半同步復(fù)制的邏輯是這樣，從庫寫入日志成功后返回ACK確認(rèn)給主庫，主庫收到至少一個從庫的確認(rèn)就認(rèn)為寫操作完成。

13. 那主從的延遲怎么解決呢？

這個問題貌似真的是個無解的問題，只能是說自己來判斷了，需要走主庫的強(qiáng)制走主庫查詢。