前言

朋友前一段時(shí)間去了美團(tuán)面試荡碾，回來(lái)就跟我吐槽，這哪是去面試java的局装，面試官就抓住MySQL一直問坛吁，頭皮都問的發(fā)麻了。想了想確實(shí)近幾年MySQL在面試中的占比是比較高铐尚，結(jié)合朋友的面試和自己的整理我歸納了一下面試中出現(xiàn)的比較頻繁的MySQL面試題拨脉，大家可以對(duì)照一下自己，查漏補(bǔ)缺宣增，看自己能答出來(lái)多少玫膀。

1、為什么使用索引爹脾？

• 通過(guò)創(chuàng)建唯一性索引帖旨，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
• 可以大大加快數(shù)據(jù)的檢索速度灵妨，這也是創(chuàng)建索引的最主要的原因碉就。
• 幫助服務(wù)器避免排序和臨時(shí)表
• 將隨機(jī)IO變?yōu)轫樞騃O。
• 可以加速表和表之間的連接闷串，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。

2筋量、Innodb為什么要用自增id作為主鍵烹吵？

如果表使用自增主鍵碉熄，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置肋拔，當(dāng)一頁(yè)寫滿锈津，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)。如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等）凉蜂，由于每次插入主鍵的值近似于隨機(jī)琼梆，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置， 頻繁的移動(dòng)窿吩、分頁(yè)操作造成了大量的碎片茎杂，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE（optimize table）來(lái)重建表并優(yōu)化填充頁(yè)面纫雁。

3煌往、MyISAM和InnoDB實(shí)現(xiàn)B樹索引方式的區(qū)別是什么？

• MyISAM轧邪，B+Tree葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址刽脖，在索引檢索的時(shí)候，首先按照B+Tree搜索算法搜索索引忌愚，如果指定的key存在曲管，則取出其data域的值，然后以data域的值為地址讀取相應(yīng)的數(shù)據(jù)記錄硕糊，這被稱為“非聚簇索引”
• InnoDB院水，其數(shù)據(jù)文件本身就是索引文件，相比MyISAM癌幕，索引文件和數(shù)據(jù)文件是分離的衙耕，其表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，樹的節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄勺远，這個(gè)索引的key是數(shù)據(jù)表的主鍵橙喘，因此InnoDB表數(shù)據(jù)文件本身就是主索引，這被稱為“聚簇索引”或者聚集索引胶逢，而其余的索引都作為輔助索引厅瞎，輔助索引的data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址，這也是和MyISAM不同的地方初坠。在根據(jù)主索引搜索時(shí)和簸，直接找到key所在的節(jié)點(diǎn)即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時(shí)碟刺，則需要先取出主鍵的值锁保，再走一遍主索引。因此，在設(shè)計(jì)表的時(shí)候爽柒，不建議使用過(guò)長(zhǎng)的字段為主鍵吴菠，也不建議使用非單調(diào)的字段作為主鍵，這樣會(huì)造成主索引頻繁分裂浩村。

4做葵、說(shuō)一下MySQL是如何執(zhí)行一條SQL的？具體步驟有哪些心墅？

SQL執(zhí)行的全部過(guò)程

Server層按順序執(zhí)行sql的步驟為：

1. 客戶端請(qǐng)求->
2. 連接器（驗(yàn)證用戶身份酿矢，給予權(quán)限） ->
3. 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作）->
4. 分析器（對(duì)SQL進(jìn)行詞法分析和語(yǔ)法分析操作） ->
5. 優(yōu)化器（主要對(duì)執(zhí)行的sql優(yōu)化選擇最優(yōu)的執(zhí)行方案方法） ->
6. 執(zhí)行器（執(zhí)行時(shí)會(huì)先看用戶是否有執(zhí)行權(quán)限怎燥，有才去使用這個(gè)引擎提供的接口）->
7. 去引擎層獲取數(shù)據(jù)返回（如果開啟查詢緩存則會(huì)緩存查詢結(jié)果）

簡(jiǎn)單概括：

• 連接器：管理連接瘫筐、權(quán)限驗(yàn)證；
• 查詢緩存：命中緩存則直接返回結(jié)果刺覆；
• 分析器：對(duì)SQL進(jìn)行詞法分析严肪、語(yǔ)法分析；（判斷查詢的SQL字段是否存在也是在這步）
• 優(yōu)化器：執(zhí)行計(jì)劃生成谦屑、選擇索引驳糯；
• 執(zhí)行器：操作引擎、返回結(jié)果氢橙；
• 存儲(chǔ)引擎：存儲(chǔ)數(shù)據(jù)酝枢、提供讀寫接口。

5悍手、你了解MySQL的內(nèi)部構(gòu)造嗎帘睦？一般可以分為哪兩個(gè)部分？

可以分為服務(wù)層和存儲(chǔ)引擎層兩部分坦康，其中：

服務(wù)層包括連接器竣付、查詢緩存、分析器滞欠、優(yōu)化器古胆、執(zhí)行器等，涵蓋MySQL的大多數(shù)核心服務(wù)功能筛璧，以及所有的內(nèi)置函數(shù)（如日期逸绎、時(shí)間、數(shù)學(xué)和加密函數(shù)等）夭谤，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)棺牧，比如存儲(chǔ)過(guò)程、觸發(fā)器朗儒、視圖等颊乘。

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取参淹。其架構(gòu)模式是插件式的，支持InnoDB乏悄、MyISAM承二、Memory等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是InnoDB纲爸，它從MySQL 5.5.5版本開始成為了默認(rèn)的存儲(chǔ)引擎。

6妆够、說(shuō)一說(shuō)Drop识啦、Delete與Truncate的共同點(diǎn)和區(qū)別

第一種回答

Drop、Delete神妹、Truncate都表示刪除颓哮，但是三者有一些差別：

Delete用來(lái)刪除表的全部或者一部分?jǐn)?shù)據(jù)行，執(zhí)行Delete之后鸵荠，用戶需要提交(commmit)或者回滾(rollback)來(lái)執(zhí)行刪除或者撤銷刪除冕茅，會(huì)觸發(fā)這個(gè)表上所有的delete觸發(fā)器。

Truncate刪除表中的所有數(shù)據(jù)蛹找，這個(gè)操作不能回滾姨伤，也不會(huì)觸發(fā)這個(gè)表上的觸發(fā)器，TRUNCATE比Delete更快庸疾，占用的空間更小乍楚。

Drop命令從數(shù)據(jù)庫(kù)中刪除表，所有的數(shù)據(jù)行届慈，索引和權(quán)限也會(huì)被刪除徒溪，所有的DML觸發(fā)器也不會(huì)被觸發(fā)，這個(gè)命令也不能回滾金顿。

因此臊泌，在不再需要一張表的時(shí)候，用Drop揍拆；在想刪除部分?jǐn)?shù)據(jù)行時(shí)候渠概，用Delete；在保留表而刪除所有數(shù)據(jù)的時(shí)候用Truncate礁凡。

第二種回答

• Drop直接刪掉表;
• Truncate刪除表中數(shù)據(jù)高氮，再插入時(shí)自增長(zhǎng)id又從1開始 ;
• Delete刪除表中數(shù)據(jù)，可以加where字句顷牌。

具體解析

1. DELETE語(yǔ)句執(zhí)行刪除的過(guò)程是每次從表中刪除一行剪芍，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存窟蓝，刪除行是不能恢復(fù)的罪裹。并且在刪除的過(guò)程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器饱普。執(zhí)行速度快。
2. 表和索引所占空間状共。當(dāng)表被TRUNCATE 后套耕，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小，而DELETE操作不會(huì)減少表或索引所占用的空間峡继。drop語(yǔ)句將表所占用的空間全釋放掉冯袍。
3. 一般而言，drop > truncate > delete
4. 應(yīng)用范圍碾牌。TRUNCATE 只能對(duì)TABLE康愤；DELETE可以是table和view
5. TRUNCATE 和DELETE只刪除數(shù)據(jù)，而DROP則刪除整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)）舶吗。
6. truncate與不帶where的delete ：只刪除數(shù)據(jù)征冷，而不刪除表的結(jié)構(gòu)（定義）drop語(yǔ)句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲(chǔ)過(guò)程/函數(shù)將被保留，但其狀態(tài)會(huì)變?yōu)椋篿nvalid誓琼。
7. delete語(yǔ)句為DML（Data Manipulation Language),這個(gè)操作會(huì)被放到 rollback segment中,事務(wù)提交后才生效检激。如果有相應(yīng)的 tigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā)。
8. truncate腹侣、drop是DDL（Data Define Language),操作立即生效叔收，原數(shù)據(jù)不放到 rollback segment中，不能回滾
9. 在沒有備份情況下筐带，謹(jǐn)慎使用 drop 與 truncate今穿。要?jiǎng)h除部分?jǐn)?shù)據(jù)行采用delete且注意結(jié)合where來(lái)約束影響范圍÷准回滾段要足夠大蓝晒。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除，如果與事務(wù)無(wú)關(guān)帖鸦，用truncate即可實(shí)現(xiàn)芝薇。如果和事務(wù)有關(guān)，或老是想觸發(fā)trigger,還是用delete作儿。
10. Truncate table 表名 速度快,而且效率高,因?yàn)? truncate table 在功能上與不帶 WHERE 子句的 DELETE 語(yǔ)句相同：二者均刪除表中的全部行洛二。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少攻锰。DELETE 語(yǔ)句每次刪除一行晾嘶，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過(guò)釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。
11. TRUNCATE TABLE 刪除表中的所有行堂污，但表結(jié)構(gòu)及其列、約束机断、索引等保持不變楷拳。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值吏奸，請(qǐng)改用 DELETE欢揖。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 DROP TABLE 語(yǔ)句奋蔚。
12. 對(duì)于由 FOREIGN KEY 約束引用的表她混，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語(yǔ)句泊碑。由于 TRUNCATE TABLE 不記錄在日志中产上，所以它不能激活觸發(fā)器。

7蛾狗、MySQL優(yōu)化了解嗎？說(shuō)一下從哪些方面可以做到性能優(yōu)化仪媒？

• 為搜索字段創(chuàng)建索引
• 避免使用 Select *沉桌，列出需要查詢的字段
• 垂直分割分表
• 選擇正確的存儲(chǔ)引擎

8、數(shù)據(jù)庫(kù)隔離級(jí)別

• 未提交讀算吩，事務(wù)中發(fā)生了修改留凭，即使沒有提交，其他事務(wù)也是可見的偎巢，比如對(duì)于一個(gè)數(shù)A原來(lái)50修改為100蔼夜，但是我還沒有提交修改，另一個(gè)事務(wù)看到這個(gè)修改压昼，而這個(gè)時(shí)候原事務(wù)發(fā)生了回滾求冷，這時(shí)候A還是50，但是另一個(gè)事務(wù)看到的A是100.可能會(huì)導(dǎo)致臟讀窍霞、幻讀或不可重復(fù)讀
• 提交讀匠题，對(duì)于一個(gè)事務(wù)從開始直到提交之前，所做的任何修改是其他事務(wù)不可見的但金，舉例就是對(duì)于一個(gè)數(shù)A原來(lái)是50韭山，然后提交修改成100，這個(gè)時(shí)候另一個(gè)事務(wù)在A提交修改之前冷溃，讀取的A是50钱磅，剛讀取完，A就被修改成100似枕，這個(gè)時(shí)候另一個(gè)事務(wù)再進(jìn)行讀取發(fā)現(xiàn)A就突然變成100了盖淡；可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生
• 重復(fù)讀菠净，就是對(duì)一個(gè)記錄讀取多次的記錄是相同的禁舷，比如對(duì)于一個(gè)數(shù)A讀取的話一直是A彪杉，前后兩次讀取的A是一致的；可以阻止臟讀和不可重復(fù)讀牵咙，但幻讀仍有可能發(fā)生
• 可串行化讀派近，在并發(fā)情況下，和串行化的讀取的結(jié)果是一致的洁桌，沒有什么不同渴丸，比如不會(huì)發(fā)生臟讀和幻讀；該級(jí)別可以防止臟讀另凌、不可重復(fù)讀以及幻讀

隔離級(jí)別臟讀不可重復(fù)讀幻影讀READ-UNCOMMITTED 未提交讀√√√READ-COMMITTED 提交讀×√√REPEATABLE-READ 重復(fù)讀××√SERIALIZABLE 可串行化讀×××

MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀）

這里需要注意的是：與 SQL 標(biāo)準(zhǔn)不同的地方在于InnoDB 存儲(chǔ)引擎在 REPEATABLE-READ（可重讀）事務(wù)隔離級(jí)別 下使用的是Next-Key Lock 鎖算法谱轨，因此可以避免幻讀的產(chǎn)生，這與其他數(shù)據(jù)庫(kù)系統(tǒng)(如 SQL Server)是不同的吠谢。所以 說(shuō)InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀） 已經(jīng)可以完全保證事務(wù)的隔離性要 求土童，即達(dá)到了 SQL標(biāo)準(zhǔn)的SERIALIZABLE(可串行化)隔離級(jí)別。

因?yàn)楦綦x級(jí)別越低工坊，事務(wù)請(qǐng)求的鎖越少献汗，所以大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別都是READ-COMMITTED(讀取提交內(nèi) 容):，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用 REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失王污。

InnoDB 存儲(chǔ)引擎在分布式事務(wù) 的情況下一般會(huì)用到SERIALIZABLE(可串行化)隔離級(jí)別罢吃。

9、都知道數(shù)據(jù)庫(kù)索引采用B+樹而不是B樹昭齐，原因也有很多尿招，主要原因是什么？

主要原因：B+樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷阱驾，而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的就谜，而B樹只能中序遍歷所有節(jié)點(diǎn)，效率太低里覆。

10吁伺、文件索引和數(shù)據(jù)庫(kù)索引為什么使用B+樹?（第9個(gè)問題的詳細(xì)回答）

文件與數(shù)據(jù)庫(kù)都是需要較大的存儲(chǔ)，也就是說(shuō)租谈，它們都不可能全部存儲(chǔ)在內(nèi)存中篮奄，故需要存儲(chǔ)到磁盤上。而所謂索引割去，則為了數(shù)據(jù)的快速定位與查找窟却，那么索引的結(jié)構(gòu)組織要盡量減少查找過(guò)程中磁盤I/O的存取次數(shù)，因此B+樹相比B樹更為合適呻逆。數(shù)據(jù)庫(kù)系統(tǒng)巧妙利用了局部性原理與磁盤預(yù)讀原理夸赫，將一個(gè)節(jié)點(diǎn)的大小設(shè)為等于一個(gè)頁(yè)，這樣每個(gè)節(jié)點(diǎn)只需要一次I/O就可以完全載入咖城，而紅黑樹這種結(jié)構(gòu)茬腿，高度明顯要深的多呼奢，并且由于邏輯上很近的節(jié)點(diǎn)(父子)物理上可能很遠(yuǎn)，無(wú)法利用局部性切平。

最重要的是握础，B+樹還有一個(gè)最大的好處：方便掃庫(kù)。

B樹必須用中序遍歷的方法按序掃庫(kù)悴品，而B+樹直接從葉子結(jié)點(diǎn)挨個(gè)掃一遍就完了禀综，B+樹支持range-query非常方便，而B樹不支持苔严，這是數(shù)據(jù)庫(kù)選用B+樹的最主要原因定枷。

B+樹查找效率更加穩(wěn)定，B樹有可能在中間節(jié)點(diǎn)找到數(shù)據(jù)届氢，穩(wěn)定性不夠欠窒。

B+tree的磁盤讀寫代價(jià)更低：B+tree的內(nèi)部結(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針(紅色部分)，因此其內(nèi)部結(jié)點(diǎn)相對(duì)B 樹更小退子。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一塊盤中贱迟，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多絮供，相對(duì)來(lái)說(shuō)IO讀寫次數(shù)也就降低了；

B+tree的查詢效率更加穩(wěn)定：由于內(nèi)部結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)茶敏，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引壤靶，所以，任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路惊搏。所有關(guān)鍵字查詢的路徑長(zhǎng)度相同贮乳，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)；

11恬惯、聽說(shuō)過(guò)視圖嗎向拆？那游標(biāo)呢？

視圖是一種虛擬的表酪耳，通常是有一個(gè)表或者多個(gè)表的行或列的子集浓恳，具有和物理表相同的功能 游標(biāo)是對(duì)查詢出來(lái)的結(jié)果集作為一個(gè)單元來(lái)有效的處理。一般不使用游標(biāo)碗暗，但是需要逐條處理數(shù)據(jù)的時(shí)候颈将，游標(biāo)顯得十分重要。

12言疗、MySQL中為什么要有事務(wù)回滾機(jī)制晴圾？

而在 MySQL 中，恢復(fù)機(jī)制是通過(guò)回滾日志（undo log）實(shí)現(xiàn)的噪奄，所有事務(wù)進(jìn)行的修改都會(huì)先記錄到這個(gè)回滾日志中死姚，然后在對(duì)數(shù)據(jù)庫(kù)中的對(duì)應(yīng)行進(jìn)行寫入人乓。當(dāng)事務(wù)已經(jīng)被提交之后，就無(wú)法再次回滾了都毒。

回滾日志作用：1)能夠在發(fā)生錯(cuò)誤或者用戶執(zhí)行 ROLLBACK 時(shí)提供回滾相關(guān)的信息 2) 在整個(gè)系統(tǒng)發(fā)生崩潰色罚、數(shù)據(jù)庫(kù)進(jìn)程直接被殺死后，當(dāng)用戶再次啟動(dòng)數(shù)據(jù)庫(kù)進(jìn)程時(shí)温鸽，還能夠立刻通過(guò)查詢回滾日志將之前未完成的事務(wù)進(jìn)行回滾保屯，這也就需要回滾日志必須先于數(shù)據(jù)持久化到磁盤上，是我們需要先寫日志后寫數(shù)據(jù)庫(kù)的主要原因涤垫。

13姑尺、數(shù)據(jù)庫(kù)引擎InnoDB與MyISAM的區(qū)別

InnoDB

• 是 MySQL 默認(rèn)的事務(wù)型存儲(chǔ)引擎，只有在需要它不支持的特性時(shí)蝠猬，才考慮使用其它存儲(chǔ)引擎切蟋。
• 實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別，默認(rèn)級(jí)別是可重復(fù)讀(REPEATABLE READ)榆芦。在可重復(fù)讀隔離級(jí)別下柄粹，通過(guò)多版本并發(fā)控制(MVCC)+ 間隙鎖(Next-Key Locking)防止幻影讀。
• 主索引是聚簇索引匆绣，在索引中保存了數(shù)據(jù)驻右，從而避免直接讀取磁盤，因此對(duì)查詢性能有很大的提升崎淳。
• 內(nèi)部做了很多優(yōu)化堪夭，包括從磁盤讀取數(shù)據(jù)時(shí)采用的可預(yù)測(cè)性讀、能夠加快讀操作并且自動(dòng)創(chuàng)建的自適應(yīng)哈希索引拣凹、能夠加速插入操作的插入緩沖區(qū)等森爽。
• 支持真正的在線熱備份。其它存儲(chǔ)引擎不支持在線熱備份嚣镜，要獲取一致性視圖需要停止對(duì)所有表的寫入爬迟，而在讀寫混合場(chǎng)景中，停止寫入可能也意味著停止讀取菊匿。

MyISAM

• 設(shè)計(jì)簡(jiǎn)單付呕，數(shù)據(jù)以緊密格式存儲(chǔ)。對(duì)于只讀數(shù)據(jù)跌捆，或者表比較小凡涩、可以容忍修復(fù)操作，則依然可以使用它疹蛉。
• 提供了大量的特性活箕，包括壓縮表、空間數(shù)據(jù)索引等。
• 不支持事務(wù)育韩。
• 不支持行級(jí)鎖克蚂，只能對(duì)整張表加鎖，讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖筋讨，寫入時(shí)則對(duì)表加排它鎖埃叭。但在表有讀取操作的同時(shí)，也可以往表中插入新的記錄悉罕，這被稱為并發(fā)插入(CONCURRENT INSERT)赤屋。

總結(jié)

• 事務(wù): InnoDB 是事務(wù)型的，可以使用 Commit 和 Rollback 語(yǔ)句壁袄。
• 并發(fā): MyISAM 只支持表級(jí)鎖类早，而 InnoDB 還支持行級(jí)鎖。
• 外鍵: InnoDB 支持外鍵嗜逻。
• 備份: InnoDB 支持在線熱備份涩僻。
• 崩潰恢復(fù): MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高很多，而且恢復(fù)的速度也更慢栈顷。
• 其它特性: MyISAM 支持壓縮表和空間數(shù)據(jù)索引逆日。

14、數(shù)據(jù)庫(kù)并發(fā)事務(wù)會(huì)帶來(lái)哪些問題萄凤？

數(shù)據(jù)庫(kù)并發(fā)會(huì)帶來(lái)臟讀室抽、幻讀、丟棄更改靡努、不可重復(fù)讀這四個(gè)常見問題坪圾，其中：

臟讀：在第一個(gè)修改事務(wù)和讀取事務(wù)進(jìn)行的時(shí)候，讀取事務(wù)讀到的數(shù)據(jù)為100颤难，這是修改之后的數(shù)據(jù)，但是之后該事務(wù)滿足一致性等特性而做了回滾操作已维，那么讀取事務(wù)得到的結(jié)果就是臟數(shù)據(jù)了行嗤。

幻讀：一般是T1在某個(gè)范圍內(nèi)進(jìn)行修改操作（增加或者刪除），而T2讀取該范圍導(dǎo)致讀到的數(shù)據(jù)是修改之間的了垛耳，強(qiáng)調(diào)范圍栅屏。

丟棄修改：兩個(gè)寫事務(wù)T1 T2同時(shí)對(duì)A=0進(jìn)行遞增操作，結(jié)果T2覆蓋T1堂鲜，導(dǎo)致最終結(jié)果是1 而不是2栈雳，事務(wù)被覆蓋

不可重復(fù)讀：T2 讀取一個(gè)數(shù)據(jù)，然后T1 對(duì)該數(shù)據(jù)做了修改缔莲。如果 T2 再次讀取這個(gè)數(shù)據(jù)哥纫，此時(shí)讀取的結(jié)果和第一次讀取的結(jié)果不同。

臟讀

臟讀

第一個(gè)事務(wù)首先讀取var變量為50痴奏，接著準(zhǔn)備更新為100的時(shí)蛀骇，并未提交厌秒，第二個(gè)事務(wù)已經(jīng)讀取var為100，此時(shí)第一個(gè)事務(wù)做了回滾擅憔。最終第二個(gè)事務(wù)讀取的var和數(shù)據(jù)庫(kù)的var不一樣鸵闪。

幻讀（幻影讀）

幻讀

T1 讀取某個(gè)范圍的數(shù)據(jù)，T2 在這個(gè)范圍內(nèi)插入新的數(shù)據(jù)暑诸，T1 再次讀取這個(gè)范圍的數(shù)據(jù)蚌讼，此時(shí)讀取的結(jié)果和和第一次讀取的結(jié)果不同。

丟棄修改

丟棄修改

T1 和 T2 兩個(gè)事務(wù)都對(duì)一個(gè)數(shù)據(jù)進(jìn)行修改个榕，T1 先修改篡石，T2 隨后修改，T2 的修改覆蓋了 T1 的修改笛洛。例如：事務(wù)1讀取某表中的數(shù)據(jù)A=50夏志，事務(wù)2也讀取A=50，事務(wù)1修改A=A+50苛让，事務(wù)2也修改A=A+50沟蔑，最終結(jié)果A=100，事務(wù)1的修改被丟失狱杰。

不可重復(fù)讀

不可重復(fù)讀

T2 讀取一個(gè)數(shù)據(jù)瘦材，T1 對(duì)該數(shù)據(jù)做了修改。如果 T2 再次讀取這個(gè)數(shù)據(jù)仿畸，此時(shí)讀取的結(jié)果和第一次讀取的結(jié)果不同食棕。

15、數(shù)據(jù)庫(kù)悲觀鎖和樂觀鎖的原理和應(yīng)用場(chǎng)景分別有什么错沽？

悲觀鎖簿晓，先獲取鎖，再進(jìn)行業(yè)務(wù)操作千埃，一般就是利用類似 SELECT … FOR UPDATE 這樣的語(yǔ)句憔儿，對(duì)數(shù)據(jù)加鎖，避免其他事務(wù)意外修改數(shù)據(jù)放可。當(dāng)數(shù)據(jù)庫(kù)執(zhí)行SELECT … FOR UPDATE時(shí)會(huì)獲取被select中的數(shù)據(jù)行的行鎖谒臼，select for update獲取的行鎖會(huì)在當(dāng)前事務(wù)結(jié)束時(shí)自動(dòng)釋放，因此必須在事務(wù)中使用耀里。

樂觀鎖蜈缤，先進(jìn)行業(yè)務(wù)操作，只在最后實(shí)際更新數(shù)據(jù)時(shí)進(jìn)行檢查數(shù)據(jù)是否被更新過(guò)冯挎。Java 并發(fā)包中的 AtomicFieldUpdater 類似底哥，也是利用 CAS 機(jī)制，并不會(huì)對(duì)數(shù)據(jù)加鎖，而是通過(guò)對(duì)比數(shù)據(jù)的時(shí)間戳或者版本號(hào)叠艳，來(lái)實(shí)現(xiàn)樂觀鎖需要的版本判斷奶陈。

16、MySQL索引主要使用的兩種數(shù)據(jù)結(jié)構(gòu)是什么附较？

• 哈希索引吃粒，對(duì)于哈希索引來(lái)說(shuō)，底層的數(shù)據(jù)結(jié)構(gòu)肯定是哈希表拒课，因此在絕大多數(shù)需求為單條記錄查詢的時(shí)候徐勃，可以選擇哈希索引，查詢性能最快早像；其余大部分場(chǎng)景僻肖，建議選擇BTree索引
• BTree索引，Mysql的BTree索引使用的是B樹中的B+Tree卢鹦，BTREE索引就是一種將索引值按一定的算法臀脏，存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹），每次查詢都是從樹的入口root開始冀自，依次遍歷node揉稚，獲取leaf。但對(duì)于主要的兩種存儲(chǔ)引擎（MyISAM和InnoDB）的實(shí)現(xiàn)方式是不同的熬粗。

17搀玖、數(shù)據(jù)庫(kù)為什么要進(jìn)行分庫(kù)和分表呢？都放在一個(gè)庫(kù)或者一張表中不可以嗎驻呐？

分庫(kù)與分表的目的在于灌诅，減小數(shù)據(jù)庫(kù)的單庫(kù)單表負(fù)擔(dān)，提高查詢性能含末，縮短查詢時(shí)間猜拾。

通過(guò)分表，可以減少數(shù)據(jù)庫(kù)的單表負(fù)擔(dān)佣盒，將壓力分散到不同的表上挎袜，同時(shí)因?yàn)椴煌谋砩系臄?shù)據(jù)量少了，起到提高查詢性能沼撕，縮短查詢時(shí)間的作用宋雏，此外芜飘，可以很大的緩解表鎖的問題务豺。分表策略可以歸納為垂直拆分和水平拆分:

水平分表：取模分表就屬于隨機(jī)分表，而時(shí)間維度分表則屬于連續(xù)分表嗦明。如何設(shè)計(jì)好垂直拆分笼沥，我的建議：將不常用的字段單獨(dú)拆分到另外一張擴(kuò)展表. 將大文本的字段單獨(dú)拆分到另外一張擴(kuò)展表, 將不經(jīng)常修改的字段放在同一張表中，將經(jīng)常改變的字段放在另一張表中。對(duì)于海量用戶場(chǎng)景奔浅，可以考慮取模分表馆纳，數(shù)據(jù)相對(duì)比較均勻，不容易出現(xiàn)熱點(diǎn)和并發(fā)訪問的瓶頸汹桦。

庫(kù)內(nèi)分表鲁驶，僅僅是解決了單表數(shù)據(jù)過(guò)大的問題，但并沒有把單表的數(shù)據(jù)分散到不同的物理機(jī)上舞骆，因此并不能減輕 MySQL 服務(wù)器的壓力钥弯，仍然存在同一個(gè)物理機(jī)上的資源競(jìng)爭(zhēng)和瓶頸，包括 CPU督禽、內(nèi)存脆霎、磁盤 IO、網(wǎng)絡(luò)帶寬等狈惫。

分庫(kù)與分表帶來(lái)的分布式困境與應(yīng)對(duì)之策數(shù)據(jù)遷移與擴(kuò)容問題----一般做法是通過(guò)程序先讀出數(shù)據(jù)睛蛛，然后按照指定的分表策略再將數(shù)據(jù)寫入到各個(gè)分表中。分頁(yè)與排序問題----需要在不同的分表中將數(shù)據(jù)進(jìn)行排序并返回胧谈，并將不同分表返回的結(jié)果集進(jìn)行匯總和再次排序忆肾，最后再返回給用戶。

18第岖、不可重復(fù)讀和幻讀區(qū)別是什么难菌？可以舉個(gè)例子嗎？

不可重復(fù)讀的重點(diǎn)是修改蔑滓，幻讀的重點(diǎn)在于新增或者刪除郊酒。

• 例1（同樣的條件, 你讀取過(guò)的數(shù)據(jù), 再次讀取出來(lái)發(fā)現(xiàn)值不一樣了 ）：事務(wù)1中的A先生讀取自己的工資為 1000的操作還沒完成，事務(wù)2中的B先生就修改了A的工資為2000键袱，導(dǎo)致A再讀自己的工資時(shí)工資變?yōu)?2000燎窘；這就是不可重復(fù)讀。
• 例2（同樣的條件, 第1次和第2次讀出來(lái)的記錄數(shù)不一樣 ）：假某工資單表中工資大于3000的有4人蹄咖，事務(wù)1讀取了所有工資大于3000的人褐健，共查到4條記錄，這時(shí)事務(wù)2 又插入了一條工資大于3000的記錄澜汤，事務(wù)1再次讀取時(shí)查到的記 錄就變?yōu)榱?條蚜迅，這樣就導(dǎo)致了幻讀。

19俊抵、MySQL中有四種索引類型谁不，可以簡(jiǎn)單說(shuō)說(shuō)嗎？

• FULLTEXT ：即為全文索引徽诲，目前只有MyISAM引擎支持刹帕。其可以在CREATE TABLE 吵血，ALTER TABLE ，CREATE INDEX 使用偷溺，不過(guò)目前只有 CHAR蹋辅、VARCHAR ，TEXT 列上可以創(chuàng)建全文索引挫掏。
• HASH ：由于HASH的唯一（幾乎100%的唯一）及類似鍵值對(duì)的形式侦另，很適合作為索引。HASH索引可以一次定位尉共，不需要像樹形索引那樣逐層查找,因此具有極高的效率淋肾。但是，這種高效是有條件的爸邢，即只在“=”和“in”條件下高效樊卓，對(duì)于范圍查詢、排序及組合索引仍然效率不高杠河。
• BTREE ：BTREE索引就是一種將索引值按一定的算法茧彤，存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹）电爹，每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf闸英。這是MySQL里默認(rèn)和最常用的索引類型走敌。
• RTREE ：RTREE在MySQL很少使用统求，僅支持geometry數(shù)據(jù)類型害淤，支持該類型的存儲(chǔ)引擎只有MyISAM、BDb卑雁、InnoDb募书、NDb、Archive幾種测蹲。相對(duì)于BTREE莹捡，RTREE的優(yōu)勢(shì)在于范圍查找。

20扣甲、視圖的作用是什么篮赢？可以更改嗎？

視圖是虛擬的表琉挖，與包含數(shù)據(jù)的表不一樣启泣，視圖只包含使用時(shí)動(dòng)態(tài)檢索數(shù)據(jù)的查詢；不包含任何列或數(shù)據(jù)示辈。使用視圖可以簡(jiǎn)化復(fù)雜的 sql 操作寥茫，隱藏具體的細(xì)節(jié)，保護(hù)數(shù)據(jù)顽耳；視圖創(chuàng)建后坠敷，可以使用與表相同的方式利用它們。

視圖不能被索引射富，也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值膝迎，如果視圖本身內(nèi)有order by 則對(duì)視圖再次order by將被覆蓋。

創(chuàng)建視圖：create view xxx as xxxx

對(duì)于某些視圖比如未使用聯(lián)結(jié)子查詢分組聚集函數(shù)Distinct Union等胰耗，是可以對(duì)其更新的限次，對(duì)視圖的更新將對(duì)基表進(jìn)行更新；但是視圖主要用于簡(jiǎn)化檢索柴灯，保護(hù)數(shù)據(jù)卖漫，并不用于更新，而且大部分視圖都不可以更新赠群。

21羊始、為什么說(shuō)B+tree比B 樹更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引？

B+tree的磁盤讀寫代價(jià)更低查描，B+tree的查詢效率更加穩(wěn)定 數(shù)據(jù)庫(kù)索引采用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷突委，而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節(jié)點(diǎn)冬三，效率太低匀油。

B+樹的特點(diǎn)

• 所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中(稠密索引)，且鏈表中的關(guān)鍵字恰好是有序的;
• 不可能在非葉子結(jié)點(diǎn)命中;
• 非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引(稀疏索引)勾笆，葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)(關(guān)鍵字)數(shù)據(jù)的數(shù)據(jù)層;

22敌蚜、一道場(chǎng)景題：假如你所在的公司選擇MySQL數(shù)據(jù)庫(kù)作數(shù)據(jù)存儲(chǔ)，一天五萬(wàn)條以上的增量窝爪，預(yù)計(jì)運(yùn)維三年弛车，你有哪些優(yōu)化手段？

• 設(shè)計(jì)良好的數(shù)據(jù)庫(kù)結(jié)構(gòu)蒲每，允許部分?jǐn)?shù)據(jù)冗余帅韧，盡量避免join查詢，提高效率啃勉。
• 選擇合適的表字段數(shù)據(jù)類型和存儲(chǔ)引擎忽舟，適當(dāng)?shù)奶砑铀饕?/li>
• MySQL庫(kù)主從讀寫分離。
• 找規(guī)律分表淮阐，減少單表中的數(shù)據(jù)量提高查詢速度叮阅。
• 添加緩存機(jī)制，比如Memcached泣特，Apc等浩姥。
• 不經(jīng)常改動(dòng)的頁(yè)面，生成靜態(tài)頁(yè)面状您。
• 書寫高效率的SQL勒叠。比如 SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE兜挨。

23、什么時(shí)候需要建立數(shù)據(jù)庫(kù)索引呢眯分？

在最頻繁使用的拌汇、用以縮小查詢范圍的字段,需要排序的字段上建立索引。不宜：1）對(duì)于查詢中很少涉及的列或者重復(fù)值比較多的列 2）對(duì)于一些特殊的數(shù)據(jù)類型弊决，不宜建立索引噪舀，比如文本字段（text）等。

24飘诗、覆蓋索引是什么与倡？

如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要查詢的字段的值，我們就稱 之為“覆蓋索引”昆稿。

我們知道在InnoDB存儲(chǔ)引 擎中纺座，如果不是主鍵索引，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵+列值溉潭。最終還是要“回表”比驻，也就是要通過(guò)主鍵再查找一次,這樣就 會(huì)比較慢。覆蓋索引就是把要查詢出的列和索引是對(duì)應(yīng)的岛抄，不做回表操作别惦！

25、數(shù)據(jù)庫(kù)中的主鍵夫椭、超鍵掸掸、候選鍵、外鍵是什么蹭秋？（很棒）

• 超鍵：在關(guān)系中能唯一標(biāo)識(shí)元組的屬性集稱為關(guān)系模式的超鍵
• 候選鍵：不含有多余屬性的超鍵稱為候選鍵扰付。也就是在候選鍵中，若再刪除屬性仁讨，就不是鍵了羽莺！
• 主鍵：用戶選作元組標(biāo)識(shí)的一個(gè)候選鍵程序主鍵
• 外鍵：如果關(guān)系模式R中屬性K是其它模式的主鍵，那么k在模式R中稱為外鍵洞豁。

舉例：

學(xué)號(hào)姓名性別年齡系別專業(yè)20020612李輝男20計(jì)算機(jī)軟件開發(fā)20060613張明男18計(jì)算機(jī)軟件開發(fā)20060614王小玉女19物理力學(xué)20060615李淑華女17生物動(dòng)物學(xué)20060616趙靜男21化學(xué)食品化學(xué)20060617趙靜女20生物植物學(xué)

1. 超鍵：于是我們從例子中可以發(fā)現(xiàn) 學(xué)號(hào)是標(biāo)識(shí)學(xué)生實(shí)體的唯一標(biāo)識(shí)盐固。那么該元組的超鍵就為學(xué)號(hào)。除此之外我們還可以把它跟其他屬性組合起來(lái)丈挟，比如：(學(xué)號(hào)刁卜，性別)，(學(xué)號(hào)曙咽，年齡)
2. 候選鍵：根據(jù)例子可知蛔趴，學(xué)號(hào)是一個(gè)可以唯一標(biāo)識(shí)元組的唯一標(biāo)識(shí)，因此學(xué)號(hào)是一個(gè)候選鍵例朱，實(shí)際上孝情，候選鍵是超鍵的子集鱼蝉，比如 （學(xué)號(hào)，年齡）是超鍵箫荡，但是它不是候選鍵魁亦。因?yàn)樗€有了額外的屬性。
3. 主鍵：簡(jiǎn)單的說(shuō)菲茬，例子中的元組的候選鍵為學(xué)號(hào)，但是我們選定他作為該元組的唯一標(biāo)識(shí)派撕，那么學(xué)號(hào)就為主鍵婉弹。
4. 外鍵是相對(duì)于主鍵的，比如在學(xué)生記錄里终吼，主鍵為學(xué)號(hào)镀赌，在成績(jī)單表中也有學(xué)號(hào)字段，因此學(xué)號(hào)為成績(jī)單表的外鍵际跪，為學(xué)生表的主鍵商佛。

主鍵為候選鍵的子集，候選鍵為超鍵的子集姆打，而外鍵的確定是相對(duì)于主鍵的良姆。

最后

本文就先寫到這里，面試中常問的一些題目我都有整理的幔戏，后面會(huì)持續(xù)更新玛追，有需要完整PDF文檔的可以移步公眾號(hào)：前程有光，免費(fèi)領(lǐng)取全部