MySQL DBA面試常見問題總結(jié)：索引，事務(wù)，存儲(chǔ)引擎，優(yōu)化等

作者：波波說運(yùn)維 2019-08-16 01:58:01

這篇文章主要是針對開發(fā)人員的,所以不涉及到MySQL的服務(wù)部署等操作,主要包括索引,事務(wù),存儲(chǔ)引擎，優(yōu)化等方面。

一、索引

關(guān)于MySQL的索引,之前實(shí)際上也介紹了很多次，這里主要介紹一些常用的面試題。

MySQL DBA面試常見問題總結(jié)--索引，事務(wù)，存儲(chǔ)引擎，優(yōu)化等

1. 什么是索引?索引是個(gè)什么樣的數(shù)據(jù)結(jié)構(gòu)呢?

索引是一種數(shù)據(jù)結(jié)構(gòu),可以幫助我們快速的進(jìn)行數(shù)據(jù)的查找。

索引的數(shù)據(jù)結(jié)構(gòu)和具體存儲(chǔ)引擎的實(shí)現(xiàn)有關(guān), 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經(jīng)常使用的InnoDB存儲(chǔ)引擎的默認(rèn)索引實(shí)現(xiàn)為:B+樹索引。

2. Hash索引和B+樹所有有什么區(qū)別或者說優(yōu)劣呢?

首先要知道Hash索引和B+樹索引的底層實(shí)現(xiàn)原理：

hash索引底層就是hash表,進(jìn)行查找時(shí),調(diào)用一次hash函數(shù)就可以獲取到相應(yīng)的鍵值,之后進(jìn)行回表查詢獲得實(shí)際數(shù)據(jù).B+樹底層實(shí)現(xiàn)是多路平衡查找樹.對于每一次的查詢都是從根節(jié)點(diǎn)出發(fā),查找到葉子節(jié)點(diǎn)方可以獲得所查鍵值,然后根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。

那么可以看出他們有以下的不同：

hash索引進(jìn)行等值查詢更快(一般情況下),但是卻無法進(jìn)行范圍查詢，因?yàn)樵趆ash索引中經(jīng)過hash函數(shù)建立索引之后,索引的順序與原順序無法保持一致,不能支持范圍查詢.而B+樹的的所有節(jié)點(diǎn)皆遵循(左節(jié)點(diǎn)小于父節(jié)點(diǎn),右節(jié)點(diǎn)大于父節(jié)點(diǎn),多叉樹也類似),天然支持范圍.
hash索引不支持使用索引進(jìn)行排序,原理同上.
hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因?yàn)閔ash函數(shù)的不可預(yù)測.AAAA和AAAAB的索引沒有相關(guān)性.
hash索引任何時(shí)候都避免不了回表查詢數(shù)據(jù),而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時(shí)候可以只通過索引完成查詢.
hash索引雖然在等值查詢上較快,但是不穩(wěn)定.性能不可預(yù)測,當(dāng)某個(gè)鍵值存在大量重復(fù)的時(shí)候,發(fā)生hash碰撞,此時(shí)效率可能極差.而B+樹的查詢效率比較穩(wěn)定,對于所有的查詢都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn),且樹的高度較低.

因此,在大多數(shù)情況下,直接選擇B+樹索引可以獲得穩(wěn)定且較好的查詢速度.而不需要使用hash索引.

3. B+樹在滿足聚簇索引和覆蓋索引的時(shí)候不需要回表查詢數(shù)據(jù),那么什么是聚簇索引?

在B+樹的索引中,葉子節(jié)點(diǎn)可能存儲(chǔ)了當(dāng)前的key值,也可能存儲(chǔ)了當(dāng)前的key值以及整行的數(shù)據(jù),這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個(gè)唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個(gè)鍵來建立聚簇索引.

當(dāng)查詢使用聚簇索引時(shí),在對應(yīng)的葉子節(jié)點(diǎn),可以獲取到整行數(shù)據(jù),因此不用再次進(jìn)行回表查詢.

4. 非聚簇索引一定會(huì)回表查詢嗎?

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

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

5. 在建立索引的時(shí)候,都有哪些需要考慮的因素呢?

建立索引的時(shí)候一般要考慮到字段的使用頻率,經(jīng)常作為條件進(jìn)行查詢的字段比較適合.如果需要建立聯(lián)合索引的話,還需要考慮聯(lián)合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對表造成太大的壓力.這些都和實(shí)際的表結(jié)構(gòu)以及查詢方式有關(guān).

6. 聯(lián)合索引是什么?為什么需要注意聯(lián)合索引中的順序?

MySQL可以使用多個(gè)字段同時(shí)建立一個(gè)索引,叫做聯(lián)合索引.在聯(lián)合索引中,如果想要命中索引,需要按照建立索引時(shí)的字段順序挨個(gè)使用,否則無法命中索引.

具體原因?yàn)椋?/p>

MySQL使用索引時(shí)需要索引有序,假設(shè)現(xiàn)在建立了"name,age,school"的聯(lián)合索引,那么索引的排序?yàn)? 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進(jìn)行排序.

當(dāng)進(jìn)行查詢時(shí),此時(shí)索引僅僅按照name嚴(yán)格有序,因此必須首先使用name字段進(jìn)行等值查詢,之后對于匹配到的列而言,其按照age字段嚴(yán)格有序,此時(shí)可以使用age字段用做索引查找,,,以此類推.因此在建立聯(lián)合索引的時(shí)候應(yīng)該注意索引列的順序,一般情況下,將查詢需求頻繁或者字段選擇性高的列放在前面.此外可以根據(jù)特例的查詢或者表結(jié)構(gòu)進(jìn)行單獨(dú)的調(diào)整.

7. 簡單描述mysql中，索引，主鍵，唯一索引，聯(lián)合索引的區(qū)別，對數(shù)據(jù)庫的性能有什么影響(從讀寫兩方面)

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

普通索引(由關(guān)鍵字KEY或INDEX定義的索引)的唯一任務(wù)是加快對數(shù)據(jù)的訪問速度。

普通索引允許被索引的數(shù)據(jù)列包含重復(fù)的值。如果能確定某個(gè)數(shù)據(jù)列將只包含彼此各不相同的值，在為這個(gè)數(shù)據(jù)列創(chuàng)建索引的時(shí)候就應(yīng)該用關(guān)鍵字UNIQUE把它定義為一個(gè)唯一索引。也就是說，唯一索引可以保證數(shù)據(jù)記錄的唯一性。

主鍵，是一種特殊的唯一索引，在一張表中只能定義一個(gè)主鍵索引，主鍵用于唯一標(biāo)識(shí)一條記錄，使用關(guān)鍵字 PRIMARY KEY 來創(chuàng)建。

索引可以覆蓋多個(gè)數(shù)據(jù)列，如像INDEX(columnA, columnB)索引，這就是聯(lián)合索引。

索引可以極大的提高數(shù)據(jù)的查詢速度，但是會(huì)降低插入、刪除、更新表的速度，因?yàn)樵趫?zhí)行這些寫操作時(shí)，還要操作索引文件。

二、事務(wù)

MySQL DBA面試常見問題總結(jié)--索引，事務(wù)，存儲(chǔ)引擎，優(yōu)化等

1. ACID是什么?

A=Atomicity：原子性,就是上面說的,要么全部成功,要么全部失敗.不可能只執(zhí)行一部分操作.
C=Consistency：系統(tǒng)(數(shù)據(jù)庫)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)移到另一個(gè)一致性的狀態(tài),不會(huì)存在中間狀態(tài).
I=Isolation：隔離性: 通常來說:一個(gè)事務(wù)在完全提交之前,對其他事務(wù)是不可見的.注意前面的通常來說加了紅色,意味著有例外情況.
D=Durability：持久性,一旦事務(wù)提交,那么就永遠(yuǎn)是這樣子了,哪怕系統(tǒng)崩潰也不會(huì)影響到這個(gè)事務(wù)的結(jié)果.

2. 同時(shí)有多個(gè)事務(wù)在進(jìn)行會(huì)怎么樣呢?

多事務(wù)的并發(fā)進(jìn)行一般會(huì)造成以下幾個(gè)問題：

臟讀：A事務(wù)讀取到了B事務(wù)未提交的內(nèi)容,而B事務(wù)后面進(jìn)行了回滾.
不可重復(fù)讀:：當(dāng)設(shè)置A事務(wù)只能讀取B事務(wù)已經(jīng)提交的部分,會(huì)造成在A事務(wù)內(nèi)的兩次查詢,結(jié)果竟然不一樣,因?yàn)樵诖似陂gB事務(wù)進(jìn)行了提交操作.
幻讀:：A事務(wù)讀取了一個(gè)范圍的內(nèi)容,而同時(shí)B事務(wù)在此期間插入了一條數(shù)據(jù).造成"幻覺".

3. 怎么解決這些問題呢?MySQL的事務(wù)隔離級(jí)別了解嗎?

MySQL的四種隔離級(jí)別如下：

(1) 未提交讀(READ UNCOMMITTED)

這就是上面所說的例外情況了,這個(gè)隔離級(jí)別下,其他事務(wù)可以看到本事務(wù)沒有提交的部分修改.因此會(huì)造成臟讀的問題(讀取到了其他事務(wù)未提交的部分,而之后該事務(wù)進(jìn)行了回滾).

這個(gè)級(jí)別的性能沒有足夠大的優(yōu)勢,但是又有很多的問題,因此很少使用.

(2) 已提交讀(READ COMMITTED)

其他事務(wù)只能讀取到本事務(wù)已經(jīng)提交的部分.這個(gè)隔離級(jí)別有不可重復(fù)讀的問題,在同一個(gè)事務(wù)內(nèi)的兩次讀取,拿到的結(jié)果竟然不一樣,因?yàn)榱硗庖粋€(gè)事務(wù)對數(shù)據(jù)進(jìn)行了修改.

(3) REPEATABLE READ(可重復(fù)讀)

可重復(fù)讀隔離級(jí)別解決了上面不可重復(fù)讀的問題(看名字也知道),但是仍然有一個(gè)新問題,就是幻讀,當(dāng)你讀取id> 10 的數(shù)據(jù)行時(shí),對涉及到的所有行加上了讀鎖,此時(shí)例外一個(gè)事務(wù)新插入了一條id=11的數(shù)據(jù),因?yàn)槭切虏迦氲?所以不會(huì)觸發(fā)上面的鎖的排斥,那么進(jìn)行本事務(wù)進(jìn)行下一次的查詢時(shí)會(huì)發(fā)現(xiàn)有一條id=11的數(shù)據(jù),而上次的查詢操作并沒有獲取到,再進(jìn)行插入就會(huì)有主鍵沖突的問題.

(4) SERIALIZABLE(可串行化)

這是最高的隔離級(jí)別,可以解決上面提到的所有問題,因?yàn)樗麖?qiáng)制將所以的操作串行執(zhí)行,這會(huì)導(dǎo)致并發(fā)性能極速下降,因此也不是很常用.

4. Innodb默認(rèn)使用的是哪種隔離級(jí)別呢?

InnoDB默認(rèn)使用的是可重復(fù)讀隔離級(jí)別.

5. MySQL都有哪些鎖呢?像上面那樣子進(jìn)行鎖定豈不是有點(diǎn)阻礙并發(fā)效率了?

從鎖的類別上來講,有共享鎖和排他鎖。

共享鎖：又叫做讀鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí),對數(shù)據(jù)加上共享鎖.共享鎖可以同時(shí)加上多個(gè).
排他鎖：又叫做寫鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時(shí),對數(shù)據(jù)加上排他鎖.排他鎖只可以加一個(gè),他和其他的排他鎖,共享鎖都相斥.

MyISAM支持表鎖，InnoDB支持表鎖和行鎖，默認(rèn)為行鎖

表級(jí)鎖：開銷小，加鎖快，不會(huì)出現(xiàn)死鎖。鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)量最低
行級(jí)鎖：開銷大，加鎖慢，會(huì)出現(xiàn)死鎖。鎖力度小，發(fā)生鎖沖突的概率小，并發(fā)度最高

6. 鎖的優(yōu)化策略

讀寫分離
分段加鎖
減少鎖持有的時(shí)間
多個(gè)線程盡量以相同的順序去獲取資源

不能將鎖的粒度過于細(xì)化，不然可能會(huì)出現(xiàn)線程的加鎖和釋放次數(shù)過多，反而效率不如一次加一把大鎖。

三、存儲(chǔ)引擎

MySQL DBA面試常見問題總結(jié)--索引，事務(wù)，存儲(chǔ)引擎，優(yōu)化等

1. MySQL支持哪些存儲(chǔ)引擎?

MySQL支持多種存儲(chǔ)引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多數(shù)的情況下,直接選擇使用InnoDB引擎都是最合適的,InnoDB也是MySQL的默認(rèn)存儲(chǔ)引擎.

2. InnoDB和MyISAM有什么區(qū)別?

InnoDB支持事務(wù)，而MyISAM不支持事務(wù)
InnoDB支持行級(jí)鎖，而MyISAM支持表級(jí)鎖
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外鍵，而MyISAM不支持
InnoDB不支持全文索引，而MyISAM支持。

四、優(yōu)化

MySQL DBA面試常見問題總結(jié)--索引，事務(wù)，存儲(chǔ)引擎，優(yōu)化等

1. 超大分頁怎么處理?

超大的分頁一般從兩個(gè)方向上來解決.

數(shù)據(jù)庫層面,這也是我們主要集中關(guān)注的(雖然收效沒那么大),類似于select * from table where age > 20 limit 1000000,10這種查詢其實(shí)也是有可以優(yōu)化的余地的. 這條語句需要load1000000數(shù)據(jù)然后基本上全部丟棄,只取10條當(dāng)然比較慢. 當(dāng)時(shí)我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也load了一百萬的數(shù)據(jù),但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會(huì)很快. 同時(shí)如果ID連續(xù)的好,我們還可以select * from table where id > 1000000 limit 10,效率也是不錯(cuò)的,優(yōu)化的可能性有許多種,但是核心思想都一樣,就是減少load的數(shù)據(jù).
從需求的角度減少這種請求….主要是不做類似的需求(直接跳轉(zhuǎn)到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預(yù)測,可緩存)以及防止ID泄漏且連續(xù)被人惡意攻擊.

解決超大分頁,其實(shí)主要是靠緩存,可預(yù)測性的提前查到內(nèi)容,緩存至redis等k-V數(shù)據(jù)庫中,直接返回即可.

2. 有關(guān)注生產(chǎn)環(huán)境sql耗時(shí)嗎?統(tǒng)計(jì)過慢查詢嗎?對慢查詢都怎么優(yōu)化過?

在業(yè)務(wù)系統(tǒng)中,除了使用主鍵進(jìn)行的查詢,其他的都會(huì)在測試庫上測試其耗時(shí),慢查詢的統(tǒng)計(jì)主要由運(yùn)維在做,會(huì)定期將業(yè)務(wù)中的慢查詢反饋給我們.慢查詢的優(yōu)化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數(shù)據(jù)列?還是數(shù)據(jù)量太大?

所以優(yōu)化也是針對這三個(gè)方向來的,

首先分析語句,看看是否load了額外的數(shù)據(jù),可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結(jié)果中并不需要的列,對語句進(jìn)行分析以及重寫.
分析語句的執(zhí)行計(jì)劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引.
如果對語句的優(yōu)化已經(jīng)無法進(jìn)行,可以考慮表中的數(shù)據(jù)量是否太大,如果是的話可以進(jìn)行橫向或者縱向的分表.

3. MySQL數(shù)據(jù)庫作發(fā)布系統(tǒng)的存儲(chǔ)，數(shù)據(jù)量增大的情況，怎么優(yōu)化?

設(shè)計(jì)良好的數(shù)據(jù)庫結(jié)構(gòu)，允許部分?jǐn)?shù)據(jù)冗余，盡量避免join查詢，提高效率。
選擇合適的表字段數(shù)據(jù)類型和存儲(chǔ)引擎，適當(dāng)?shù)奶砑铀饕?/li>
mysql庫主從讀寫分離。
找規(guī)律分表，減少單表中的數(shù)據(jù)量提高查詢速度。
添加緩存機(jī)制，比如memcached，apc等。
不經(jīng)常改動(dòng)的頁面，生成靜態(tài)頁面。
書寫高效率的SQL。比如 SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE.

4. 實(shí)踐中如何優(yōu)化MySQL

最好是按照以下順序優(yōu)化：

SQL語句及索引的優(yōu)化
數(shù)據(jù)庫表結(jié)構(gòu)的優(yōu)化
系統(tǒng)配置的優(yōu)化
硬件的優(yōu)化

五、其他

1. MySQL中的varchar和char有什么區(qū)別.

char是一個(gè)定長字段,假如申請了char(10)的空間,那么無論實(shí)際存儲(chǔ)多少內(nèi)容.該字段都占用10個(gè)字符,而varchar是變長的,也就是說申請的只是最大長度,占用的空間為實(shí)際字符長度+1,最后一個(gè)字符存儲(chǔ)使用了多長的空間.

在檢索效率上來講,char > varchar,因此在使用中,如果確定某個(gè)字段的值的長度,可以使用char,否則應(yīng)該盡量使用varchar.例如存儲(chǔ)用戶MD5加密后的密碼,則應(yīng)該使用char.

2. varchar(10)和int(10)代表什么含義?

varchar的10代表了申請的空間長度,也是可以存儲(chǔ)的數(shù)據(jù)的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能存儲(chǔ)的數(shù)字大小以及占用的空間都是相同的,只是在展示時(shí)按照長度展示.

3. MySQL的binlog有有幾種錄入格式?分別有什么區(qū)別?

有三種格式,statement,row和mixed.

statement模式下,記錄單元為語句.即每一個(gè)sql造成的影響會(huì)記錄.由于sql的執(zhí)行是有上下文的,因此在保存的時(shí)候需要保存相關(guān)的信息,同時(shí)還有一些使用了函數(shù)之類的語句無法被記錄復(fù)制.
row級(jí)別下,記錄單元為每一行的改動(dòng),基本是可以全部記下來但是由于很多操作,會(huì)導(dǎo)致大量行的改動(dòng)(比如alter table),因此這種模式的文件保存的信息太多,日志量太大.
mixed. 一種折中的方案,普通操作使用statement記錄,當(dāng)無法使用statement的時(shí)候使用row.

此外,新版的MySQL中對row級(jí)別也做了一些優(yōu)化,當(dāng)表結(jié)構(gòu)發(fā)生變化的時(shí)候,會(huì)記錄語句而不是逐行記錄.

4. 有了解橫向分表和縱向分表嗎?

橫向分表是按行分表.假設(shè)我們有一張用戶表,主鍵是自增ID且同時(shí)是用戶的ID.數(shù)據(jù)量較大,有1億多條,那么此時(shí)放在一張表里的查詢效果就不太理想.我們可以根據(jù)主鍵ID進(jìn)行分表,無論是按尾號(hào)分,或者按ID的區(qū)間分都是可以的. 假設(shè)按照尾號(hào)0-99分為100個(gè)表,那么每張表中的數(shù)據(jù)就僅有100w.這時(shí)的查詢效率無疑是可以滿足要求的.

縱向分表是按列分表.假設(shè)我們現(xiàn)在有一張文章表.包含字段id-摘要-內(nèi)容.而系統(tǒng)中的展示形式是刷新出一個(gè)列表,列表中僅包含標(biāo)題和摘要,當(dāng)用戶點(diǎn)擊某篇文章進(jìn)入詳情時(shí)才需要正文內(nèi)容.此時(shí),如果數(shù)據(jù)量大,將內(nèi)容這個(gè)很大且不經(jīng)常使用的列放在一起會(huì)拖慢原表的查詢速度.我們可以將上面的表分為兩張.id-摘要,id-內(nèi)容.當(dāng)用戶點(diǎn)擊詳情,那主鍵再來取一次內(nèi)容即可.而增加的存儲(chǔ)量只是很小的主鍵字段.代價(jià)很小.

當(dāng)然,分表其實(shí)和業(yè)務(wù)的關(guān)聯(lián)度很高,在分表之前一定要做好調(diào)研以及benchmark.不要按照自己的猜想盲目操作.

5. 三個(gè)范式