【揭秘】數(shù)據(jù)庫(kù)面試葵花寶典，讓你面試一次過(guò)

大家可以叫我老張，網(wǎng)名superZS！一直從事數(shù)據(jù)庫(kù)行業(yè)10余年，工作于某數(shù)據(jù)庫(kù)服務(wù)公司，兼數(shù)據(jù)庫(kù)資深講師，就面試中大家遇到的比較困惑的數(shù)據(jù)庫(kù)問(wèn)題，和剛進(jìn)入數(shù)據(jù)庫(kù)領(lǐng)域的同學(xué)們，我在這里給大家做一個(gè)詳細(xì)的總結(jié)，希望對(duì)大家在工作或者面試中有所幫助，老師會(huì)傾囊相授，道行尚淺，大家相互學(xué)習(xí)！讓我們努力學(xué)習(xí)技術(shù)，為了拿到高薪，追到心儀的姑娘，而奮斗吧！

葵花寶典

Question 1：

你目前接觸的mysql版本是什么？除了官方版本，還接觸過(guò)其他的mysql分支版本嘛？

產(chǎn)生分支的原因

許多開(kāi)發(fā)人員認(rèn)為有必要將其拆分成其他項(xiàng)目，并且每個(gè)分支項(xiàng)目都有自己的專(zhuān)長(zhǎng)。該需求以及Oracle對(duì)核心產(chǎn)品增長(zhǎng)緩慢的擔(dān)憂，導(dǎo)致出現(xiàn)了許多開(kāi)發(fā)人員感興趣的子項(xiàng)目和分支

三個(gè)流行MySQL分支：Drizzle、MariaDB和Percona Server（包括XtraDB引擎）

MariaDB不僅是mysql的替代品，主要還是創(chuàng)新和提高mysql自有技術(shù)。

   新功能介紹

1. multi-source replication 多源復(fù)制

2. 表的并行復(fù)制

3. galera cluster集群

4. spider水平分片

5. tokuDB存儲(chǔ)引擎

XtraDB是innodb存儲(chǔ)引擎的增強(qiáng)版，可用來(lái)更好地發(fā)揮最新的計(jì)算機(jī)硬件系統(tǒng)性能，還包含在高性能模式下的新特性。它可以向下兼容，因?yàn)樗窃趇nnodb基礎(chǔ)上構(gòu)建，所以他有更多的指標(biāo)和擴(kuò)展功能。而且它在cpu多核的條件下，可以更好地使用內(nèi)存，時(shí)數(shù)據(jù)庫(kù)性能提到更高！

Drizzle與mysql的差別就比較大了，并且不能兼容，如果想運(yùn)行此環(huán)境，就需要重寫(xiě)一些代碼了！

Question 2:

mysql主要的存儲(chǔ)引擎myisam和innodb的不同之處？

1. 事務(wù)的支持不同（innodb支持事務(wù)，myisam不支持事務(wù)）

2. 鎖粒度（innodb行鎖應(yīng)用，myisam表鎖）

3. 存儲(chǔ)空間（innodb既緩存索引文件又緩存數(shù)據(jù)文件，myisam只能緩存索引文件）

4. 存儲(chǔ)結(jié)構(gòu)

   （myisam：數(shù)據(jù)文件的擴(kuò)展名為.MYD myData ，索引文件的擴(kuò)展名是.MYI myIndex）

      （innodb：所有的表都保存在同一個(gè)數(shù)據(jù)文件里面 即為.Ibd）

   5. 統(tǒng)計(jì)記錄行數(shù)

       （myisam：保存有表的總行數(shù)，select count(*) from table;會(huì)直接取出出該值）

       （innodb：沒(méi)有保存表的總行數(shù)，select count(*) from table；就會(huì)遍歷整個(gè)表，消耗相當(dāng)大）

Question  3：

Innodb的體系結(jié)構(gòu)簡(jiǎn)單介紹一下？

談及到innodb的體系結(jié)構(gòu)，首先要考慮mysql的體系結(jié)構(gòu)，分為兩部分mysql的server層和存儲(chǔ)引擎層

先要跟面試官聊清楚mysql的整體方向，然后再去涉及innodb體系結(jié)構(gòu)

建議從三方面介紹innodb體系結(jié)構(gòu)：內(nèi)存----線程-----磁盤(pán)

內(nèi)存中包含insert_buffer,data_buffer,index_buffer,redo_log_buffer,double_write

內(nèi)存刷新到磁盤(pán)的機(jī)制，redo，臟頁(yè)，binlog的刷新條件

各種線程的作用，master_thread,purge_thread,redo log thread,read thread,write thread,page cleaner thread

磁盤(pán)中存放著數(shù)據(jù)文件，redo log，undo log，binlog

Question  4：

mysql有哪些索引類(lèi)型：

1. 數(shù)據(jù)結(jié)構(gòu)角度上可以分：B+tree索引，hash索引，fulltext索引（innodb，myisam都支持）

2. 存儲(chǔ)角度上可以分：聚集索引，非聚集索引

3. 邏輯角度上可以分：primary key，normal key，單列，復(fù)合，覆蓋索引

Question  5：

mysql binlog有幾種格式：

1. statement

    優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能

    缺點(diǎn)：當(dāng)使用一些特殊函數(shù)的時(shí)候，或者跨庫(kù)操作的時(shí)候容易丟失數(shù)據(jù)

注：在生產(chǎn)中不建議使用

2. row

    優(yōu)點(diǎn)：清晰記錄每行的數(shù)據(jù)信息，不會(huì)出現(xiàn)跨庫(kù)丟數(shù)據(jù)的情況

    缺點(diǎn)：內(nèi)容當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，但就會(huì)產(chǎn)生大量的binlog，對(duì)于網(wǎng)絡(luò)開(kāi)銷(xiāo)也比較大

注：生產(chǎn)中推薦使用

3. mixed

    是mysql5.1的時(shí)候，一個(gè)過(guò)渡版本，DDL語(yǔ)句會(huì)記錄成statement，DML會(huì)記錄row。

注：生產(chǎn)中不建議使用

Qusetion 6：

mysql主從復(fù)制的具體原理是什么？

主服務(wù)器把數(shù)據(jù)更新記錄到二進(jìn)制日志中，從服務(wù)器通過(guò)io thread向主庫(kù)發(fā)起binlog請(qǐng)求，主服務(wù)器通過(guò)IO dump thread把二進(jìn)制日志傳遞給從庫(kù)，從庫(kù)通過(guò)io thread記錄到自己的中繼日志中。然后再通過(guò)sql thread應(yīng)用中繼日志中sql的內(nèi)容。

Qusetion 7：

數(shù)據(jù)庫(kù)中雙一是什么？

sync_binlog=1

innodb_flush_log_at_trx_commit=1

innodb_flush_log_at_trx_commit和sync_binlog 兩個(gè)參數(shù)是控制MySQL 磁盤(pán)寫(xiě)入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)

innodb_flush_log_at_trx_commit設(shè)置為1，每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫(xiě)入log file，并且刷到磁盤(pán)中去。

sync_binlog =N (N>0) ，MySQL 在每寫(xiě) N次 二進(jìn)制日志binary log時(shí)，會(huì)使用fdatasync()函數(shù)將它的寫(xiě)二進(jìn)制日志binary log同步到磁盤(pán)中去

Qusetion 8：

如何監(jiān)控mysql  replication復(fù)制延遲？

1. 可以通過(guò)第三方工具 業(yè)界中的瑞士×××percona-toolkit中的命令，pt-heartbeat進(jìn)行主從延遲監(jiān)控。

2. 傳統(tǒng)方法，通過(guò)比較主從服務(wù)器之間的position號(hào)的差異值。

3. 還可以通過(guò)查看seconds_behind_master估算一下主從延遲時(shí)間

Qusetion  9：

大表DDL語(yǔ)句，如何實(shí)施，才能把性能影響降到最低？

1. 可以通過(guò)傳統(tǒng)方法導(dǎo)入導(dǎo)出數(shù)據(jù)，新建一張與原表一樣的表結(jié)構(gòu)，把需要執(zhí)行的ddl語(yǔ)句在無(wú)數(shù)據(jù)的新表執(zhí)行，然后把老表中的數(shù)據(jù)導(dǎo)入到新表中，把新表改成老表的名字

2. 通過(guò)第三方工具 業(yè)界中的瑞士×××percona-toolkit中的命令，pt-online-schema-change進(jìn)行在線操作

3. 對(duì)于新版本的mysql（5.7）可以直接在線online ddl

Qusetion  10：

為什么要為innodb表設(shè)置自增列做主鍵？

1.使用自增列做主鍵，寫(xiě)入順序是自增的，和B+數(shù)葉子節(jié)點(diǎn)分裂順序一致

2.表不指定自增列做主鍵，同時(shí)也沒(méi)有可以被選為主鍵的唯一索引，InnoDB就會(huì)選擇內(nèi)置的rowid作為主鍵，寫(xiě)入順序和rowid增長(zhǎng)順序一致

所以InnoDB表的數(shù)據(jù)寫(xiě)入順序能和B+樹(shù)索引的葉子節(jié)點(diǎn)順序一致的話，這時(shí)候存取效率是最高

Qusetion  11：

如何優(yōu)化一條有問(wèn)題的sql語(yǔ)句？

針對(duì)sql語(yǔ)句的優(yōu)化，我們不要上來(lái)就回答添加索引，這樣顯得太不專(zhuān)業(yè)。我們可以從如下幾個(gè)角度去分析

1. 回歸到表的設(shè)計(jì)層面，數(shù)據(jù)類(lèi)型選擇是否合理

2. 大表碎片的整理是否完善

3. 表的統(tǒng)計(jì)信息，是不是準(zhǔn)確的

4. 審查表的執(zhí)行計(jì)劃，判斷字段上面有沒(méi)有合適的索引

5. 針對(duì)索引的選擇性，建立合適的索引（就又涉及到大表DDL的操作問(wèn)題）

Qusetion  12：

服務(wù)器負(fù)載過(guò)高或者網(wǎng)頁(yè)打開(kāi)緩慢，簡(jiǎn)單說(shuō)說(shuō)你的優(yōu)化思路 ？

1. 首先我們要發(fā)現(xiàn)問(wèn)題的過(guò)程，通過(guò)操作系統(tǒng)，數(shù)據(jù)庫(kù)，程序設(shè)計(jì)，硬件角度四個(gè)維度找到問(wèn)題所在

2. 找到瓶頸點(diǎn)的位置

3. 制定好優(yōu)化方案，形成處理問(wèn)題的體系

4. 體系制定好之后，在測(cè)試環(huán)境進(jìn)行優(yōu)化方案的測(cè)試

5. 測(cè)試環(huán)境如果優(yōu)化效果很好，再實(shí)施到生產(chǎn)環(huán)境

6. 做好處理問(wèn)題的記錄

Qusetion  13：

接觸過(guò)哪些mysql的主流架構(gòu)？架構(gòu)應(yīng)用中有哪些問(wèn)題需要考慮？

1. M-S
   

2. MHA

3. MM keepalived

4. PXC

共同存在的問(wèn)題：主從延遲問(wèn)題的存在，在主庫(kù)宕機(jī)，切換過(guò)程中要考慮數(shù)據(jù)一致性的問(wèn)題，避免出現(xiàn)主從復(fù)制不一致

Qusetion14：

什么是死鎖？鎖等待？如何優(yōu)化這類(lèi)問(wèn)題？通過(guò)數(shù)據(jù)庫(kù)哪些表可以監(jiān)控？

死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上互相占用，并請(qǐng)求加鎖時(shí)，而導(dǎo)致的惡性循環(huán)現(xiàn)象。當(dāng)多個(gè)事務(wù)以不同順序試圖加鎖同一資源時(shí)，就會(huì)產(chǎn)生死鎖。

鎖等待：mysql數(shù)據(jù)庫(kù)中，不同session在更新同行數(shù)據(jù)中，會(huì)出現(xiàn)鎖等待

重要的三張鎖的監(jiān)控表innodb_trx，innodb_locks，innodb_lock_waits

Qusetion  15：

處理過(guò)mysql哪些案例

我們可以簡(jiǎn)單從mysql四個(gè)知識(shí)模塊跟他聊聊mysql體系結(jié)構(gòu)，數(shù)據(jù)備份恢復(fù)，優(yōu)化，高可用集群架構(gòu)

1. mysql版本的升級(jí)

2. 處理mysql集群的各種坑和問(wèn)題

3. 根據(jù)公司業(yè)務(wù)類(lèi)型，設(shè)計(jì)合理mysql庫(kù)，表，架構(gòu)。

4. 定期進(jìn)行災(zāi)備恢復(fù)演練

5. 誤刪除數(shù)據(jù)之后，恢復(fù)數(shù)據(jù)
   

簡(jiǎn)單先從這幾個(gè)方向說(shuō)說(shuō)，每個(gè)問(wèn)題再展開(kāi)分析。

當(dāng)然還會(huì)有一些人事上面的問(wèn)題，例如為啥選我們的公司，你覺(jué)得你自己的優(yōu)勢(shì)是什么？你期望的薪資大概是多少？這些問(wèn)題，就很簡(jiǎn)單了。我們只要過(guò)了技術(shù)面試，這些都不是啥問(wèn)題了！

今后還會(huì)逐漸展開(kāi)某個(gè)問(wèn)題的具體剖析，和詳細(xì)步驟處理方法！望大家繼續(xù)關(guān)注后期創(chuàng)作

_____________________________________________________________

打個(gè)小廣告，最近老張我開(kāi)了視頻課程，希望大家多多支持，和我的文章一樣，定會(huì)讓你有不小的收獲！

MySQL體系結(jié)構(gòu)深入剖析及實(shí)戰(zhàn)DBA視頻課程
課程介紹：

通過(guò)對(duì) MySQL 體系結(jié)構(gòu)深入剖析講解，配合生產(chǎn)環(huán)境備份恢復(fù)，主從復(fù)制，高可用集群架構(gòu)和優(yōu)化等實(shí)戰(zhàn)演練，讓同學(xué)們可以對(duì) MySQL 數(shù)據(jù)庫(kù)有個(gè)由淺到深的認(rèn)識(shí)。最后的課程部分還會(huì)對(duì)面試題總結(jié)進(jìn)行講解，有利于同學(xué)們可以找到理想的 MySQL DBA 的工作。