前言

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

1、為什么使用索引？

• 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(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)一頁寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁。如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個(gè)位置， 頻繁的移動(dòng)、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE（optimize table）來重建表并優(yōu)化填充頁面。

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í)候，不建議使用過長(zhǎng)的字段為主鍵，也不建議使用非單調(diào)的字段作為主鍵，這樣會(huì)造成主索引頻繁分裂。

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

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

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

1. 客戶端請(qǐng)求->
2. 連接器（驗(yàn)證用戶身份，給予權(quán)限） ->
3. 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作）->
4. 分析器（對(duì)SQL進(jìn)行詞法分析和語法分析操作） ->
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)行詞法分析、語法分析；（判斷查詢的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ǔ)過程、觸發(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、說一說Drop、Delete與Truncate的共同點(diǎn)和區(qū)別

第一種回答

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

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

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

Drop命令從數(shù)據(jù)庫中刪除表，所有的數(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語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。
2. 表和索引所占空間。當(dāng)表被TRUNCATE 后，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小，而DELETE操作不會(huì)減少表或索引所占用的空間。drop語句將表所占用的空間全釋放掉。
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語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲(chǔ)過程/函數(shù)將被保留，但其狀態(tài)會(huì)變?yōu)椋篿nvalid。
7. delete語句為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段要足夠大。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除，如果與事務(wù)無關(guān)，用truncate即可實(shí)現(xiàn)。如果和事務(wù)有關(guān)，或老是想觸發(fā)trigger,還是用delete。
10. Truncate table 表名 速度快,而且效率高,因?yàn)? truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
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 語句。
12. 對(duì)于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。

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

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

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

• 未提交讀，事務(wù)中發(fā)生了修改，即使沒有提交，其他事務(wù)也是可見的，比如對(duì)于一個(gè)數(shù)A原來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原來是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ù)庫系統(tǒng)(如 SQL Server)是不同的。所以 說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ù)庫系統(tǒng)的隔離級(jí)別都是READ-COMMITTED(讀取提交內(nèi) 容):，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用 REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失。

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

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

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

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

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

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

B樹必須用中序遍歷的方法按序掃庫，而B+樹直接從葉子結(jié)點(diǎn)挨個(gè)掃一遍就完了，B+樹支持range-query非常方便，而B樹不支持，這是數(shù)據(jù)庫選用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ì)來說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、聽說過視圖嗎？那游標(biāo)呢？

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

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

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

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

13、數(shù)據(jù)庫引擎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í)別下，通過多版本并發(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 語句。
• 并發(fā): MyISAM 只支持表級(jí)鎖，而 InnoDB 還支持行級(jí)鎖。
• 外鍵: InnoDB 支持外鍵。
• 備份: InnoDB 支持在線熱備份。
• 崩潰恢復(fù): MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高很多，而且恢復(fù)的速度也更慢。
• 其它特性: MyISAM 支持壓縮表和空間數(shù)據(jù)索引。

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

數(shù)據(jù)庫并發(fā)會(huì)帶來臟讀、幻讀、丟棄更改、不可重復(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ù)庫的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ù)庫悲觀鎖和樂觀鎖的原理和應(yīng)用場(chǎng)景分別有什么？

悲觀鎖，先獲取鎖，再進(jìn)行業(yè)務(wù)操作，一般就是利用類似 SELECT … FOR UPDATE 這樣的語句，對(duì)數(shù)據(jù)加鎖，避免其他事務(wù)意外修改數(shù)據(jù)。當(dāng)數(shù)據(jù)庫執(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ù)是否被更新過。Java 并發(fā)包中的 AtomicFieldUpdater 類似，也是利用 CAS 機(jī)制，并不會(huì)對(duì)數(shù)據(jù)加鎖，而是通過對(duì)比數(shù)據(jù)的時(shí)間戳或者版本號(hào)，來實(shí)現(xiàn)樂觀鎖需要的版本判斷。

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

• 哈希索引，對(duì)于哈希索引來說，底層的數(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ù)庫為什么要進(jìn)行分庫和分表呢？都放在一個(gè)庫或者一張表中不可以嗎？

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

通過分表，可以減少數(shù)據(jù)庫的單表負(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ā)訪問的瓶頸。

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

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

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

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

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

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

• FULLTEXT ：即為全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 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、為什么說B+tree比B 樹更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫索引？

B+tree的磁盤讀寫代價(jià)更低，B+tree的查詢效率更加穩(wěn)定 數(shù)據(jù)庫索引采用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷，而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，而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ù)庫作數(shù)據(jù)存儲(chǔ)，一天五萬條以上的增量，預(yù)計(jì)運(yùn)維三年，你有哪些優(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。

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

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

24、覆蓋索引是什么？

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

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

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

• 超鍵：在關(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)。除此之外我們還可以把它跟其他屬性組合起來，比如：(學(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)單的說，例子中的元組的候選鍵為學(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)取全部