mysql相關(guān)面試題有哪些

本篇內(nèi)容主要講解“mysql相關(guān)面試題有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“mysql相關(guān)面試題有哪些”吧!

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

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

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

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

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

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

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

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

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

以上面的user表來舉例，我們?cè)僭黾右粋€(gè)name字段，然后做一些查詢?cè)囋嚒?/p>

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實(shí)現(xiàn)，這時(shí)候只能讀不能寫。

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

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

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

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

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

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

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

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

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

而隔離性有4個(gè)隔離級(jí)別，分別是：

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

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

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

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

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

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

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

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

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

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

I隔離性由MVCC來保證

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

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

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

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

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

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

select * from user where id<=3;

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

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

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

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

由于MVCC的原理是查找創(chuàng)建版本小于或等于當(dāng)前事務(wù)版本，刪除版本為空或者大于當(dāng)前事務(wù)版本，小明的真實(shí)的查詢應(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原理，我們來說什么是幻讀就簡(jiǎn)單多了。舉一個(gè)常見的場(chǎng)景，用戶注冊(cè)時(shí)，我們先查詢用戶名是否存在，不存在就插入，假定用戶名是唯一索引。

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

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

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

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

間隙鎖是可重復(fù)讀級(jí)別下才會(huì)有的鎖，結(jié)合MVCC和間隙鎖可以解決幻讀的問題。我們還是以u(píng)ser舉例，假設(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可以插入成功，那么因?yàn)楸淼拈g隙mysql自動(dòng)幫我們生成了區(qū)間(左開右閉)

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

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

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

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

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

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

垂直分庫

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

垂直分表

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

水平分表

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

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

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

因?yàn)槲覀冎麈I默認(rèn)都是自增的，那么分表之后的主鍵在不同表就肯定會(huì)有沖突了。有幾個(gè)辦法考慮：

1. 設(shè)定步長(zhǎng)，比如1-1024張表我們分別設(shè)定1-1024的基礎(chǔ)步長(zhǎng)，這樣主鍵落到不同的表就不會(huì)沖突了。

2. 分布式ID，自己實(shí)現(xiàn)一套分布式ID生成算法或者使用開源的比如雪花算法這種

3. 分表后不使用主鍵作為查詢依據(jù)，而是每張表單獨(dú)新增一個(gè)字段作為唯一主鍵使用，比如訂單表訂單號(hào)是唯一的，不管最終落在哪張表都基于訂單號(hào)作為查詢依據(jù)，更新也一樣。

11. 分表后非sharding_key的查詢?cè)趺刺幚砟兀?/h4>

1. 可以做一個(gè)mapping表，比如這時(shí)候商家要查詢訂單列表怎么辦呢？不帶user_id查詢的話你總不能掃全表吧？所以我們可以做一個(gè)映射關(guān)系表，保存商家和用戶的關(guān)系，查詢的時(shí)候先通過商家查詢到用戶列表，再通過user_id去查詢。

2. 打?qū)挶恚话愣?，商戶端?duì)數(shù)據(jù)實(shí)時(shí)性要求并不是很高，比如查詢訂單列表，可以把訂單表同步到離線（實(shí)時(shí)）數(shù)倉，再基于數(shù)倉去做成一張寬表，再基于其他如es提供查詢服務(wù)。

3. 數(shù)據(jù)量不是很大的話，比如后臺(tái)的一些查詢之類的，也可以通過多線程掃表，然后再聚合結(jié)果的方式來做。或者異步的形式也是可以的。

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

public class TaskExecutor {
    public <T> List<T> executeTask(Collection<? extends Callable<T>> tasks) throws Exception {
        List<T> result = Lists.newArrayList();
        List<Future<T>> futures = ExecutorUtil.invokeAll(tasks);
        for (Future<T> 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啟動(dòng)一個(gè)IO線程讀取同步過來的master的binlog，記錄到relay log中繼日志中

5. slave再開啟一個(gè)sql線程讀取relay log事件并在slave執(zhí)行，完成同步

6. slave記錄自己的binglog

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

全同步復(fù)制

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

半同步復(fù)制

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

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

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

到此，相信大家對(duì)“mysql相關(guān)面試題有哪些”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！