導航：?

【黑馬Java筆記+踩坑匯總】JavaSE+JavaWeb+SSM+SpringBoot+瑞吉外賣+SpringCloud+黑馬旅游+谷粒商城+學成在線+MySQL高級篇+設計模式+?？兔嬖囶}

目錄

請你說說MySQL索引,以及它們的好處和壞處

請你說說MySQL的索引是什么結構,為什么不用哈希表

請你說說數據庫索引的底層數據結構

請你講講B樹和B+樹

數據庫為什么不用紅黑樹而用B+樹？

請你說說索引怎么實現的B+樹,為什么選這個數據結構？

請你說說聚簇索引和非聚簇索引

請你說說數據庫引擎有哪些,各自有什么區(qū)別

請你說說InnoDB的MVCC

請你說說樂觀鎖和悲觀鎖

請你介紹一下數據庫的ACID

請你說說MySQL的事務隔離級別

MySQL主從同步是如何實現的？

MySQL調優(yōu)

---

請你說說MySQL索引,以及它們的好處和壞處

得分點

檢索效率、磁盤資源、維護索引對性能的影響、最左前綴原則、三種索引介紹和區(qū)別

索引是一種用于快速查詢的排好序的數據結構，索引是存儲引擎實現的，不同索引的存儲引擎不一定相同。

優(yōu)點：

• 查詢效率高，磁盤I/O次數低；
• 每行數據索引唯一；
• 聯合、分組、排序查詢效率高；

缺點：

• 創(chuàng)建索引耗費時間；
• 維護索引耗費時間，每次增刪改時要維護索引；
• 索引占用磁盤空間，索引文件可能比數據文件更占空間。例如innoDB的1個聚簇索引和多個非聚簇索引加起來肯定比原數據文件占的內存多；
• 聯合索引查詢時，沒有遵循最左前綴原則將會導致索引不起作用，從而出現嚴重的性能問題。

innoDB索引按存儲方式可以分為：聚集索引、二級索引、聯合索引（嚴格說是二級索引）。

聚集索引：索引即數據（因為記錄就存在B+樹葉節(jié)點），數據即索引。?

• 數據訪問更快（因為索引和數據保存在同一個B+樹）；
• 排序查找和范圍查找很快（因為葉節(jié)點之間由雙向鏈表鏈接）；
• 分段加載，節(jié)省內存；
• 建議主鍵短、自增、不可更新，從而提高效率；

非聚簇索引（輔助、二級索引）：B+樹葉節(jié)點只存查詢字段的值和主鍵的值，通過主鍵值回表查聚集索引。

聯合索引：同時為多個列建立非聚簇索引。先按c2字段排序，c2字段相同時按c3字段排序。

聚簇索引和非聚簇索引區(qū)別

• 葉節(jié)點：聚簇索引葉子節(jié)點存記錄，非聚簇索引葉子節(jié)點存數據位置 。非聚簇索引不會影響數據表的物理存儲順序。
• 數量：一個表只能有一個聚簇索引，因為只能有一種排序存儲的方式，但可以有多個非聚簇索引，也就是多個索引目錄提供數據檢索。
• 效率：使用聚簇索引的時候，數據的查詢效率高 ，但如果對數據進行插入，刪除，更新等操作，效率會比非聚簇索引低。因為聚簇索引存的完整記錄，移動起來慢。

標準回答

在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

在表中建立索引，然后在索引中找到符合查詢條件的索引值，最后通過保存在索引中的ROWID（相當于頁碼）快速找到表中對應的記錄。

索引就像指向表行的指針，是一種允許查詢操作快速確定哪些行符合WHERE子句中的條件，并檢索到這些行的其他列值的數據結構；

索引主要有普通索引、唯一索引、主鍵索引、外鍵索引、全文索引、復合索引幾種；

在大數據量的查詢中，合理使用索引的優(yōu)點非常明顯，不僅能大幅提高匹配where條件的檢索效率，還能用于排序和分組操作的加速。

當時索引如果使用不當也有比較大的壞處：比如索引必定會增加存儲資源的消耗；同時也增大了插入、更新和刪除操作的維護成本，因為每個增刪改操作后相應列的索引都必須被更新。

加分回答-只要創(chuàng)建了索引，就一定會走索引嗎？

不一定。比如，在使用組合索引的時候，如果沒有遵從“最左前綴”的原則進行搜索，則索引是不起作用的。

最左前綴原則：指的是由多個列組成的聯合索引，在查詢時只會對最左邊使用的幾個列進行索引查詢。具體來說，如果索引鍵包含A、B、C三個列，而查詢語句僅使用了A和B列，則最左前綴的原則會指導MySQL只使用A、B兩列進行索引搜索，而不會使用C列。

當使用“最左前綴”原則時，指定的列的順序非常重要。如果指定的列順序不正確，就無法利用索引進行查詢，會產生全表掃描的情況，這會造成非常嚴重的性能問題。

舉例，假設在id、name、age字段上已經成功建立了一個名為MultiIdx的組合索引。索引行中按id、name、age的順序存放，索引可以搜索id、（id，name）、（id，name，age）字段組合。如果列不構成索引最左面的前綴，那么MySQL不能使用局部索引，如（age）或者（name，age）組合則不能使用該索引查詢。

請你說說MySQL的索引是什么結構,為什么不用哈希表

得分點

B+樹、自適應的哈希、內存耗費

MySQL中的索引是B+樹結構，可以分段加載節(jié)點數據，省內存，查詢效率也高。

自適應的哈希：InnoDB底層索引是B+樹，但他支持自適應的哈希索引。對于經常被訪問的數據，MySQL會把經常被訪問數據的數據頁地址存到哈希表里，這樣下次再查到這個熱點數據就會直接從哈希表找，提高查詢效率；

不用哈希表的原因：哈希表耗費內存，要把所有數據載入內存。

標準回答

MySQL中的索引B+樹實現的；

哈希表的查詢效率的確最高，時間復雜度O(1)，但是它要求將所有數據載入內存，而數據庫存儲的數據量級可能會非常大，全部載入內存基本上是不可能實現的；

B+樹可以分段加載需要的節(jié)點數據，可以在內存資源有限的前提下，極大提高查詢效率。

分段加載：將整個B+樹劃分成若干個段，每個段包含多個數據頁。在查詢過程中，只需要將當前需要查詢的節(jié)點所在的段加載到內存中，而不必將整棵樹都加載到內存中，從而減少內存的占用和I/O操作次數。

請你說說數據庫索引的底層數據結構

得分點

介紹并比較優(yōu)缺點、二叉樹、紅黑樹、哈希表、B樹、B+樹

數據庫（不單指MySQL）索引可選的底層數據機構包括：

- 二叉樹：某些場景下會退化成鏈表，查詢慢；

- 紅黑樹：紅黑樹是二叉樹，索引樹層數會很高，查詢次數和IO不如B+樹；

- Hash表：增刪改查時間復雜度O（1），但①太占空間，②難以排序、范圍查詢，③無法對單獨一個字段索引，④重復值多時處理沖突耗費性能和IO；

- B-Tree：多路平衡查找樹，非葉節(jié)點也存數據，左小右大，一般層數比B+樹深，查詢速度和IO次數也就不如B+樹；可以分段加載節(jié)點數據。

- B+Tree：多路平衡查找樹，非葉節(jié)點存目錄，葉節(jié)點存記錄。查詢效率更高（比B樹矮胖，層數很難超過4層），IO次數也少（很難超過3次），更穩(wěn)定，范圍查詢效率高（因為葉節(jié)點之間由雙向鏈表鏈接）。可以分段加載節(jié)點數據。

標準答案

數據庫（不單指MySQL）索引可選的底層數據機構包括：

- 二叉樹

- 紅黑樹

- Hash表

- B-Tree

但mysql索引的底層用的并不是二叉樹和紅黑樹。因為二叉樹和紅黑樹在某些場景下都會暴露出一些缺陷。

首先，二叉樹在某些場景下會退化成鏈表，而鏈表的查找需要從頭部開始遍歷，而這就失去了加索引的意義。

不使用紅黑樹的原因是：紅黑樹作為底層數據結構在面對在些表數據動輒數百萬數千萬的場景時，會導致索引樹的層數很高。索引從根節(jié)點開始查找，而如果我們需要查找的數據在底層的葉子節(jié)點上，那么樹的高度是多少，就要進行多少次查找，數據存在磁盤上，訪問需要進行磁盤IO，這會導致效率過低；

而B+樹由B樹和索引順序訪問方法演化而來，它是為磁盤或其他直接存取輔助設備設計的一種平衡查找樹，在B+樹中，所有記錄節(jié)點都是按鍵值的大小順序存放在同一層的葉子節(jié)點，各葉子節(jié)點通過指針進行鏈接。

B+樹索引在數據庫中的一個特點就是高扇出性，例如在InnoDB存儲引擎中，每個頁的大小為16KB。在數據庫中，B+樹的高度一般都在2～4層，這意味著查找某一鍵值最多只需要2到4次IO操作，這還不錯。因為現在一般的磁盤每秒至少可以做100次IO操作，2～4次的IO操作意味著查詢時間只需0.02～0.04秒。

為什么B+樹的高度一般不會超過4層：

真實一個數據頁可以存100條記錄，?一個目錄頁能存1000條數據；4層B+樹能存一千萬條數據，我們用到的B+樹都不會超過4層，每個頁內部可以用二分查找更快查找。

InnoDB非聚簇索引情況：

InnoDB 頁的大小為 16KB，一般表的主鍵類型為 INT(占用4個字節(jié))或 BIGINT (占用8個字節(jié))，指針類型也一般為4或8個字節(jié)，也就是說一個頁 (B+Tree 中的一個節(jié)點)中大概存儲16KB/(8B+8B)=1K個鍵值 (因為是估值，為方便計算，這里的K取值為 10^3。也就是說一個深度為3的B+Tree 索引可以維護 10^3*10^3*10^3= 1 億條記錄(這里假定一個數據頁也存儲10A3條行記錄數據了)

實際情況中每個節(jié)點可能不能填充滿，因此在數據庫中，B+Tree 的高度一般都在 2~4 層。MySQL的lnnoDB 存儲引擎在設計時是將根節(jié)點常駐內存的，也就是說查找某一鍵值的行記錄時最多只需要 2~4 ?次磁盤IO操作。

數據頁大小：

MyISAM 的數據頁大小是固定的，是 1KB，也就是說，MyISAM 存儲引擎的數據都是以 1KB 的塊進行管理的。

InnoDB 存儲引擎的數據頁大小是可調的，默認是 16KB。在 MySQL 5.7 版本之前，InnoDB 的數據頁大小默認是 8KB。可以通過參數?innodb_page_size?來設置 InnoDB 的數據頁大小，取值范圍是 4KB、8KB、16KB 和 32KB。

請你講講B樹和B+樹

得分點

平衡多路查找樹、記錄存儲位置、非葉節(jié)點存儲內容、葉節(jié)點關系、查詢性能和IO次數

1. 非葉節(jié)點的值數量和孩子數量：k階樹，B+樹非葉節(jié)點有k個值、k個孩子，B樹非葉節(jié)點有k-1個值、k個孩子；

2. 記錄存儲位置：B+樹記錄全存在葉節(jié)點，B樹記錄存在所有節(jié)點；

3. 非葉節(jié)點存儲內容：B+樹非葉節(jié)點存索引，B樹非葉節(jié)點存記錄；

4. 葉子結點之間關系：B+樹所有葉子節(jié)點構成一個有序雙向鏈表；B樹葉節(jié)點之間沒指針，只是有序；

查詢性能：B+樹查詢效率更高（比B樹矮胖，層數很難超過4層），IO次數也少（很難超過3次），更穩(wěn)定，范圍查詢效率高（因為葉節(jié)點之間由雙向鏈表鏈接）。

B樹和B+樹都可以分段加載節(jié)點數據。

標準回答

它們都是平衡多路查找樹，是在二叉查找樹基礎上的改進數據結構。在二叉查找樹上查找一個數據時，最壞情況的查找次數為樹的深度，當數據量很大時，查詢次數可能還是很大，造成大量的磁盤IO，從而影響查詢效率；

為了減少磁盤IO的次數，必須降低樹的深度，因此在二叉查找樹基礎上將樹改成了多叉加上一些限制條件，就形成了B樹；

B+樹是B樹的變種，區(qū)別主要是：對于k階的B樹，每個中間節(jié)點只存k-1個值、k個指針（是那k-1個值的范圍），而B+樹存k個值和k個指針；B樹中所有節(jié)點中值的總集是全部關鍵字集合，而B+樹中所有葉子節(jié)點值的總集就是全部關鍵字集合；B+樹為所有葉子節(jié)點增加了鏈接，從而實現了快速的范圍查找；

加分回答-B+樹優(yōu)點

B+樹由B樹和索引順序訪問方法演化而來，它是為磁盤或其他直接存取輔助設備設計的一種平衡查找樹，在B+樹中，所有記錄節(jié)點都是按鍵值的大小順序存放在同一層的葉子節(jié)點，各葉子節(jié)點通過指針進行鏈接。

B+樹索引在數據庫中的一個特點就是高扇出性，例如在InnoDB存儲引擎中，每個頁的大小為16KB。在數據庫中，B+樹的高度一般都在2～4層，這意味著查找某一鍵值最多只需要1到3次IO操作（（根節(jié)點被緩存，不算IO次數））。因為現在一般的磁盤每秒至少可以做100次IO操作，2～4次的IO操作意味著查詢時間只需0.02～0.04秒。

數據庫為什么不用紅黑樹而用B+樹？

得分點

介紹紅黑樹和B+樹、叉數、高度、查詢性能、磁盤IO次數、范圍查詢、分段加載數據

因為B+樹是多叉，紅黑樹是二叉，B+樹更矮胖，查詢性能更高，IO次數更少。

B+樹是多路平衡查找樹，非葉節(jié)點存目錄，葉節(jié)點存記錄。查詢效率更高（比B樹矮胖，層數很難超過4層），IO次數也少（很難超過3次），更穩(wěn)定，范圍查詢效率高（因為葉節(jié)點之間由雙向鏈表鏈接）。?可以分段加載節(jié)點數據。??

m階B+樹：

• 每個非葉節(jié)點關鍵字個數和孩子個數相等；
• 根結點關鍵字個數2到m，非跟結點關鍵字個數?m/2?到m；
• 所有葉子在同一層；

紅黑樹： 近似平衡二叉樹，左右子樹高差有可能大于 1，查找效率略低于平衡二叉樹，但增刪效率高于平衡二叉樹，適合頻繁插入刪除。

• 結點非黑即紅；
• 根結點是黑色，葉節(jié)點是黑色空節(jié)點（常省略）；
• 任何相鄰節(jié)點不能同時為紅色；
• 從任一結點到其每個葉子的所有路徑都包含相同數目的黑色結點；
• 查詢性能穩(wěn)定O(logN)，高度最高2log(n+1)；

標準回答

首先，紅黑樹是一種近似平衡二叉樹（不完全平衡），結點非黑即紅的樹，它的樹高最高不會超過 2*log(n)，因此查找的時間復雜度為 O(log(n))，無論是增刪改查，它的性能都十分穩(wěn)定；

但是，紅黑樹本質還是二叉樹，在數據量非常大時，需要訪問+判斷的節(jié)點數還是會比較多，同時數據是存在磁盤上的，訪問需要進行磁盤IO，導致效率較低；

而B+樹是多叉的，可以有效減少磁盤IO次數；同時B+樹增加了葉子結點間的連接，能保證范圍查詢時找到起點和終點后快速取出需要的數據。

加分回答-紅黑樹做索引底層數據結構的缺陷

試想一下，以紅黑樹作為底層數據結構在面對在些表數據動輒數百萬數千萬的場景時，創(chuàng)建的索引它的樹高得有多高？

索引從根節(jié)點開始查找，而如果我們需要查找的數據在底層的葉子節(jié)點上，那么樹的高度是多少，就要進行多少次查找，數據存在磁盤上，訪問需要進行磁盤IO，這會導致效率過低；

那么紅黑樹作為索引數據結構的弊端即是：樹的高度過高導致查詢效率變慢。

請你說說索引怎么實現的B+樹,為什么選這個數據結構？

得分點

B+樹、查詢效率、IO次數、范圍排序查詢、分段加載數據頁

B+樹：

B+樹是多路平衡查找樹，非葉節(jié)點存目錄，葉節(jié)點存記錄。

為什么選B+樹？

查詢效率更高（層數很難超過4層），IO次數也少（很難超過3次），更穩(wěn)定，范圍、排序查詢效率高（因為葉節(jié)點之間由雙向鏈表鏈接）?？梢苑侄渭虞d節(jié)點數據。?

m階B+樹：

• 每個非葉節(jié)點關鍵字個數和孩子個數相等；
• 根結點關鍵字個數2到m，非跟結點關鍵字個數?m/2?到m；
• 所有葉子在同一層；

?

標準回答

索引本質上就是通過預排序+樹型結構來加快檢索的效率，而MySQL中使用InnoDB和MyISAM引擎時都使用了B+樹實現索引。

它是一棵平衡多路查找樹，是在二叉查找樹基礎上的改進數據結構。在二叉查找樹上查找一個數據時，最壞情況的查找次數為樹的深度，當數據量很大時，查詢次數可能還是很大，造成大量的磁盤IO，從而影響查詢效率；

為了減少磁盤IO的次數，必須降低樹的深度，因此在二叉查找樹基礎上將樹改成了多叉加上一些限制條件，就形成了B樹；

B+樹中所有葉子節(jié)點值的總集就是全部關鍵字集合；B+樹為所有葉子節(jié)點增加了鏈接，從而實現了快速的范圍查找；

在B+樹中，所有記錄節(jié)點都是按鍵值的大小順序存放在同一層的葉子節(jié)點上，由各葉子節(jié)點指針進行連接。在數據庫中，B+樹的高度一般都在2～4層，這也就是說查找某一鍵值的行記錄時最多只需要2到4次IO。這很不錯，因為當前一般的機械磁盤每秒至少可以做100次IO，2～4次的IO意味著查詢時間只需0.02～0.04秒。

在數據庫中，B+樹索引還可以分為聚集索引和輔助索引，但不管是聚集索引還是輔助索引，其內部都是B+樹的，即高度平衡的，葉子節(jié)點存放著所有的數據。聚集索引與輔助索引不同的是，葉子節(jié)點存放的是否是一整行的信息。

請你說說聚簇索引和非聚簇索引

得分點

介紹、區(qū)別、葉節(jié)點、數量、效率、回表

innoDB索引按存儲方式可以分為：聚集索引、二級索引、聯合索引（嚴格說是二級索引）。

聚集索引：索引即數據（因為記錄就存在B+樹葉節(jié)點），數據即索引。?

• 數據訪問更快（因為索引和數據保存在同一個B+樹）；
• 排序查找和范圍查找很快（因為葉節(jié)點之間由雙向鏈表鏈接）；
• 分段加載，節(jié)省內存；
• 建議主鍵短、自增、不可更新，從而提高效率；

非聚簇索引（輔助、二級索引）：B+樹葉節(jié)點只存查詢字段的值和主鍵的值，通過主鍵值回表查聚集索引。

聯合索引：同時為多個列建立非聚簇索引。先按c2字段排序，c2字段相同時按c3字段排序。

聚簇索引和非聚簇索引區(qū)別

• 葉節(jié)點：聚簇索引葉子節(jié)點存記錄，非聚簇索引葉子節(jié)點存數據位置 。非聚簇索引不會影響數據表的物理存儲順序。
• 數量：一個表只能有一個聚簇索引，因為只能有一種排序存儲的方式，但可以有多個非聚簇索引，也就是多個索引目錄提供數據檢索。
• 效率：使用聚簇索引的時候，數據的查詢效率高 ，但如果對數據進行插入，刪除，更新等操作，效率會比非聚簇索引低。因為聚簇索引存的完整記錄，移動起來慢。

標準回答

兩者主要區(qū)別是數據和索引是否分離。聚簇索引是將數據與索引存儲到一起，找到索引也就找到了數據；而非聚簇索引是將數據和索引存儲分離開，索引樹的葉子節(jié)點存儲了數據行的地址。

在InnoDB中，一個表有且僅有一個聚簇索引（因為原始數據只留一份，而數據和聚簇索引在一起），并且該索引是建立在主鍵上的，即使沒有指定主鍵，也會特殊處理生成一個聚簇索引；其他索引都是輔助索引，使用輔助索引訪問索引外的其他字段時都需要進行二次查找。

而在MyISAM中，所有索引都是非聚簇索引，葉子節(jié)點存儲著數據的地址，對于主鍵索引和普通索引在存儲上沒有區(qū)別。

加分回答

在InnoDB存儲引擎中，可以將B+樹索引分為聚簇索引和輔助索引（非聚簇索引）。無論是何種索引，每個頁的大小都為16KB，且不能更改。

聚簇索引是根據主鍵創(chuàng)建的一棵B+樹，聚簇索引的葉子節(jié)點存放了表中的所有記錄。輔助索引是根據索引鍵創(chuàng)建的一棵B+樹，與聚簇索引不同的是，其葉子節(jié)點僅存放索引鍵值，以及該索引鍵值指向的主鍵。也就是說，如果通過輔助索引來查找數據，那么當找到輔助索引的葉子節(jié)點后，很有可能還需要根據主鍵值查找聚簇索引來得到數據，這種查找方式又被稱為書簽查找。因為輔助索引不包含行記錄的所有數據，這就意味著每頁可以存放更多的鍵值，因此其高度一般都要小于聚簇索引。

請你說說數據庫引擎有哪些,各自有什么區(qū)別

得分點

InnoDB、MyISAM，外鍵、事務、高并發(fā)、鎖級別、內存要求、磁盤耗費、查詢性能、增刪改性能

InnoDB：支持外鍵和事務，行鎖適合高并發(fā)，緩存索引和數據，內存要求高（因為要緩存索引和記錄），適合存大數據量，增刪改性能更優(yōu)（行級鎖高并發(fā)），耗費磁盤（因為有多個非聚簇索引，索引可能比記錄空間還大）。

MyISAM：不支持外鍵和事務，表鎖不適合高并發(fā)，緩存索引和數據地址，內存要求低（因為不用緩存記錄），查詢性能更優(yōu)（因為查詢時InnoDB要維護MVCC一致，而且多緩存了記錄），節(jié)省磁盤（因為磁盤不存完整記錄）。

標準回答

InnoDB 引擎是 MySQL 的事務安全（ACID 兼容）存儲引擎，具有提交、回滾和崩潰恢復功能來保護用戶數據；行級鎖定讀取增加了多用戶并發(fā)性和性能；將用戶數據存儲在聚集索引中，以減少基于主鍵的常見查詢的 I/O；還支持 FOREIGN KEY 維護數據完整性。

MyISAM引擎的表占用空間較小，表級鎖定限制了讀/寫工作負載的性能，因此它通常用于只讀或以讀取為主的場景。

Memory引擎是將所有數據存儲在 RAM 中，以便在需要快速查找非關鍵數據的環(huán)境中進行快速訪問，以前被稱為 HEAP 引擎。

Archive引擎非常適合存儲大量的獨立的，作為歷史記錄的數據，因為它們不經常被讀取。它 擁有高效的插入速度，但其對查詢的支持相對較差。

Cluster/NDB是高冗余的存儲引擎，用多臺數據機器聯合提供服務以提高整體性能和安全性。適合數據量大，安全和性能要求高的應用。

Federated引擎提供連接單獨的 MySQL 服務器，從多個物理服務器創(chuàng)建一個邏輯數據庫的能力，非常適合分布式或數據集市環(huán)境。

標準回答2

InnoDB是具有事務、回滾和崩潰修復能力的事務安全型引擎，它可以實現行級鎖來保證高性能的大量數據中的并發(fā)操作；MyISAM是具有默認支持全文索引、壓縮功能及較高查詢性能的非事務性引擎。具體來說，可以在以下角度上形成對比：

事務：InnoDB支持事務；MyISAM不支持。

數據鎖：InnoDB支持行級鎖；MyISAM只支持表級鎖。

讀寫性能：InnoDB增刪改性能更優(yōu)（行級鎖高并發(fā)）；MyISAM查詢性能更優(yōu)。

全文索引：InnoDB不支持（但可通過插件等方式支持）；MyISAM默認支持。

外鍵：InnoDB支持外鍵；MyISAM不支持。

存儲結構：InnoDB在磁盤存儲為一個文件；MyISAM在磁盤上存儲成三個文件（表定義、數據、索引）。

存儲空間：InnoDB需要更多的內存和存儲；MyISAM支持支持三種不同的存儲格式：靜態(tài)表(默認)、動態(tài)表、壓縮表。

移植：InnoDB在數據量小時可通過拷貝數據文件、備份binlog、mysqldump工具移植，數據量大時比較麻煩；可單獨對某個表通過拷貝表文件移植。

崩潰恢復：InnoDB有崩潰恢復機制；MyISAM沒有。

默認推薦：InnoDB是MySQL5.5之后的默認引擎。

加分回答-InnoDB中行級鎖是怎么實現的？

InnoDB行級鎖是通過給索引上的索引項加鎖來實現的。只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。

當表中鎖定其中的某幾行時，不同的事務可以使用不同的索引鎖定不同的行。另外，不論使用主鍵索引、唯一索引還是普通索引，InnoDB都會使用行鎖來對數據加鎖。

請你說說InnoDB的MVCC

得分點

概念、三劍客、流程、讀提交和可重復讀MVCC原理

MVCC：多版本并發(fā)控制，通過管理記錄的多個版本，實現了數據庫事務并發(fā)時的一致性讀，即當前事務讀取正在被其他事務更新的行時，能讀到該記錄被更新之前的版本。解決了讀寫沖突。

MVCC三劍客：隱藏字段、Undo Log、Read View。

隱藏字段：innoDB聚簇索引葉節(jié)點記錄有兩個隱藏字段，trx_id和roll_pointer。

• trx_id：事務id。事務修改記錄時會將事務id寫在trx_id列。
• roll_pointer：回滾指針。指向回滾日志里該記錄版本鏈的最近結點。用于找到回滾日志里該記錄被事務修改前的版本。

Undo Log回滾日志：存儲各記錄的版本鏈，用于回滾和存檔。記錄的一個版本就是版本鏈的一個結點，每個結點的數據域是整個記錄，指針域指向上個版本的結點。首結點是最近記錄。

Read View：事務快照讀時生成的數據系統(tǒng)讀視圖，記錄并維護當前活躍事務的id列表。事務id是按創(chuàng)建時間遞增的。

• 快照讀：不加鎖的查詢語句，可能讀到舊版本數據。
• 當前讀：加鎖的查詢語句，讀到的一定是最新版本數據

?Read View的規(guī)則，即可見性算法：

通過讀視圖，可以判斷當前查詢中，記錄的某個版本是否可見。

判斷方法是比較各版本的trx_id和讀視圖里的活躍事務id，如果某版本trx_id小于讀視圖的最小事務id，則代表那個版本是生成讀視圖之前的已提交版本，當前查詢就可以訪問那個版本。

MVCC流程：

查詢，生成讀視圖，用讀視圖的活躍事務id依次對比各版本的事務id，找到符合規(guī)則的數據。

應用： 事務隔離級別里的讀提交和可重復讀底層是由MVCC實現的。并且MySQL InnoDB 引擎的可重復讀解決了幻讀問題，快照讀由MVCC解決，當前讀通過 next-key lock解決。

讀提交的MVCC原理：事務每次讀到的都是最新已提交的數據。每次讀取數據前都生成一個ReadView。快照讀生成Read View，不斷對比版本鏈各版本的trx_id，直到發(fā)現某版本trx_id比Read View的活躍事務列表里最小trx_id還小，該版本則是快照讀前最新已提交的數據。

可重復讀的MVCC原理：只在第一次查詢時生成ReadView，之后查詢用第一次快照讀時生成的ReadView。

標準回答

全稱 Multi-Version Concurrency Control ，即多版本并發(fā)控制，邏輯是維持一個數據的多個版本，使得讀寫操作沒有沖突。MVCC主要是為了提高數據庫并發(fā)性能，用更好的方式去處理讀-寫沖突，做到即使有讀寫沖突時，也能做到不加鎖，非阻塞并發(fā)讀。

它是一種用來解決讀-寫沖突的無鎖并發(fā)控制機制。在并發(fā)讀寫數據庫時，可以做到在讀操作時不用阻塞寫操作，寫操作也不用阻塞讀操作，提高了數據庫并發(fā)讀寫的性能，還可以解決臟讀、幻讀、不可重復讀等事務隔離問題，但不能解決更新丟失問題。

加分回答-三劍客

InnoDB默認的隔離級別是RR（REPEATABLE READ），RR解決臟讀、不可重復讀、幻讀等問題，使用的是MVCC。MVCC全稱Multi-Version Concurrency Control，即多版本的并發(fā)控制協議。它最大的優(yōu)點是讀不加鎖，因此讀寫不沖突，并發(fā)性能好。InnoDB實現MVCC，多個版本的數據可以共存，主要基于以下技術及數據結構：

1. 隱藏列：InnoDB中每行數據都有隱藏列，隱藏列中包含了本行數據的事務id、指向undo log的指針等。

2. 基于undo log的版本鏈：每行數據的隱藏列中包含了指向undo log的指針，而每條undo log也會指向更早版本的undo log，從而形成一條版本鏈。

3. ReadView：通過隱藏列和版本鏈，MySQL可以將數據恢復到指定版本。但是具體要恢復到哪個版本，則需要根據ReadView來確定。所謂ReadView，是指事務（記做事務A）在某一時刻給整個事務系統(tǒng)（trx_sys）打快照，之后再進行讀操作時，會將讀取到的數據中的事務id與trx_sys快照比較，從而判斷數據對該ReadView是否可見，即對事務A是否可見。

?

請你說說樂觀鎖和悲觀鎖

得分點

定義、樂觀鎖三種實現方式、使用場景

MySQL數據庫的鎖機制主要有兩種：樂觀鎖和悲觀鎖。?

樂觀鎖：每次拿數據時候樂觀地認為別人不會改這個數據，更新時再判斷鎖。因為讀的時候不判斷鎖，所以讀效率高；因為更新時才判斷鎖所以也不會死鎖。

實現方式：版本號機制、時間戳機制、CAS機制。

版本號機制：表中有一個version字段， 讀時取version字段，更新時如果發(fā)現版本號沒變就給版本號加一并更新，如果發(fā)現版本號變了就拒絕更新。

時間戳機制：表中有一個時間戳字段，更新時對比時間戳得知是否被更改。

CAS機制：使用像compare and swap這樣的原子操作來不斷嘗試修改記錄，如果數據沒有被其他線程修改則會更新成功，否則需重試。

應用場景：?

git等版本控制工具是悲觀鎖，在pull到push期間如果別人已經push過，那么我會push失敗。

庫存多、高并發(fā)的秒殺場景適合用樂觀鎖，為防止主從同步出錯要強制從主機查數據。在提交訂單業(yè)務的最后一步查庫存余量。

悲觀鎖：每次拿數據時候悲觀地認為別人正在改這個數據，拿數據時候就判斷鎖，拿到鎖后阻塞其他線程。因為阻塞所以并發(fā)差（特別是長事務）、可能死鎖。因為阻塞所以有排他性，精度高。

庫存少、低并發(fā)、高價格、高精度的秒殺場景適合用悲觀鎖。訂單業(yè)務剛開始查庫存的時候就加鎖，然后生成訂單，減庫存，釋放鎖。

標準回答

樂觀鎖：樂觀鎖總是假設最好的情況，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據，可以使用版本號機制和CAS算法實現。樂觀鎖適用于多讀的應用類型，這樣可以提高吞吐量（單位時間內完成的任務、處理的數據量、傳輸的信息量等），像數據庫提供的類似于write_condition機制，其實都是提供的樂觀鎖。

UPDATE ... SET version=version+1 WHERE version=${version};

悲觀鎖：悲觀鎖總是假設最壞的情況，每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會阻塞直到它拿到鎖（共享資源每次只給一個線程使用，其它線程阻塞，用完后再把資源轉讓給其它線程）。傳統(tǒng)的關系型數據庫里邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

加分回答-兩種鎖的使用場景

樂觀鎖：

GIT，SVN，CVS等代碼版本控制管理器，就是一個樂觀鎖使用很好的場景，例如：A、B程序員，同時從SVN服務器上下載了code.html文件，當A完成提交后，此時B再提交，那么會報版本沖突，此時需要B進行版本處理合并后，再提交到服務器。這其實就是樂觀鎖的實現全過程。如果此時使用的是悲觀鎖，那么意味者所有程序員都必須一個一個等待操作提交完，才能訪問文件，這是難以接受的。

悲觀鎖：

悲觀鎖的好處在于可以減少并發(fā)，但是當并發(fā)量非常大的時候，由于鎖消耗資源、鎖定時間過長等原因，很容易導致系統(tǒng)性能下降，資源消耗嚴重。因此一般我們可以在并發(fā)量不是很大，并且出現并發(fā)情況導致的異常用戶和系統(tǒng)都很難以接受的情況下，會選擇悲觀鎖進行。

請你介紹一下數據庫的ACID

得分點

原子性、一致性、隔離性、持久性

數據庫的ACID是指事務四個特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）。

原子性：事務的所有操作，要么全部成功，要么全部失敗。?

一致性：事務前后，數據庫的約束沒有被破壞，保持前后一致。

隔離性：操作同一資源的并發(fā)事務之間相互隔離，不會互相干擾。

持久性：事務的結果最終一定會持久化到數據庫，宕機等故障也無法影響。

標準回答

事務可由一條非常簡單的SQL語句組成，也可以由一組復雜的SQL語句組成。在事務中的操作，要么都執(zhí)行修改，要么都不執(zhí)行，這就是事務的目的，也是事務模型區(qū)別于文件系統(tǒng)的重要特征之一。

事務需遵循ACID四個特性：

- A（atomicity），原子性。原子性指整個數據庫事務是不可分割的工作單位。只有使事務中所有的數據庫操作都執(zhí)行成功，整個事務的執(zhí)行才算成功。事務中任何一個SQL語句執(zhí)行失敗，那么已經執(zhí)行成功的SQL語句也必須撤銷，數據庫狀態(tài)應該退回到執(zhí)行事務前的狀態(tài)。

- C（consistency），一致性。一致性指事務將數據庫從一種狀態(tài)轉變?yōu)榱硪环N一致的狀態(tài)。在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞。

- I（isolation），隔離性。事務的隔離性要求每個讀寫事務的對象與其他事務的操作對象能相互分離，即該事務提交前對其他事務都不可見，這通常使用鎖來實現。

- D（durability） ，持久性。事務一旦提交，其結果就是永久性的，即使發(fā)生宕機等故障，數據庫也能將數據恢復。持久性保證的是事務系統(tǒng)的高可靠性，而不是高可用性。

加分回答-事務分類

事務可以分為以下幾種類型：

- 扁平事務：是事務類型中最簡單的一種，而在實際生產環(huán)境中，這可能是使用最為頻繁的事務。在扁平事務中，所有操作都處于同一層次，其由BEGIN WORK開始，由COMMIT WORK或ROLLBACK WORK結束。處于之間的操作是原子的，要么都執(zhí)行，要么都回滾。

- 帶有保存點的扁平事務：除了支持扁平事務支持的操作外，允許在事務執(zhí)行過程中回滾到同一事務中較早的一個狀態(tài)，這是因為可能某些事務在執(zhí)行過程中出現的錯誤并不會對所有的操作都無效，放棄整個事務不合乎要求，開銷也太大。保存點（savepoint）用來通知系統(tǒng)應該記住事務當前的狀態(tài)，以便以后發(fā)生錯誤時，事務能回到該狀態(tài)。

- 鏈事務：可視為保存點模式的一個變種。鏈事務的思想是：在提交一個事務時，釋放不需要的數據對象，將必要的處理上下文隱式地傳給下一個要開始的事務。注意，提交事務操作和開始下一個事務操作將合并為一個原子操作。這意味著下一個事務將看到上一個事務的結果，就好像在一個事務中進行的。

- 嵌套事務：是一個層次結構框架。有一個頂層事務（top-level transaction）控制著各個層次的事務。頂層事務之下嵌套的事務被稱為子事務（subtransaction），其控制每一個局部的變換。

- 分布式事務：通常是一個在分布式環(huán)境下運行的扁平事務，因此需要根據數據所在位置訪問網絡中的不同節(jié)點。對于分布式事務，同樣需要滿足ACID特性，要么都發(fā)生，要么都失效。

對于MySQL的InnoDB存儲引擎來說，它支持扁平事務、帶有保存點的扁平事務、鏈事務、分布式事務。對于嵌套事務，MySQL數據庫并不是原生的，因此對于有并行事務需求的用戶來說MySQL就無能為力了，但是用戶可以通過帶有保存點的事務來模擬串行的嵌套事務。

請你說說MySQL的事務隔離級別

得分點

隔離級別概念、三種讀問題、四種隔離級別的特點、底層、解決讀問題

事務隔離級別是指操作同一資源的并發(fā)事務之間的隔離度。隔離級別越高，事務之間的相互干擾就越小，安全性就越高。

讀問題：

• 臟讀：讀到了臟數據。當前事務讀到另一個未提交事務剛改的數據。只有讀未提交會臟讀。
• 不可重復讀：前后重復讀的數據不一樣。前后兩次讀同數據，這期間數據被其他事務改了，導致前后讀取的數據不同。
• 幻讀：前后讀的數據是一樣，但多了幾行或少了幾行，像幻覺一樣。事務前后讀的數據集合不同，導致出現“幻像”行。僅串行化能解決幻讀問題。

事務隔離級別

• 讀未提交：事務能讀到所有未提交事務的數據。壓根不加鎖、沒隔離，性能最高。
• 讀提交：事務能讀到已提交事務的數據。底層由MVVC實現，每次快照讀都生成讀視圖?。解決臟讀問題。
• 可重復讀（默認）：前后讀的數據相同。底層由MVVC實現，僅第一次快照讀生成讀視圖。解決臟讀、不可重復讀問題。MySQL InnoDB 引擎的可重復讀解決了幻讀問題?？煺兆x由MVCC解決，當前讀通過 next-key lock解決。
• 串行化：事務一拿到鎖阻塞其他事務，直到釋放鎖。讀時共享鎖，寫時排它鎖。阻塞導致性能最差。解決臟讀、不可重復讀、幻讀問題。?

MVVC：多版本并發(fā)控制。MVCC三劍客：隱藏字段、Undo Log、Read View。

共享鎖（讀鎖）：在共享鎖下，多個線程可以同時讀取數據，但只有一個線程能夠修改數據。當一個線程在修改數據時，必須獲得獨占鎖，以便其他線程不能訪問數據。

排它鎖（寫鎖）：在排它鎖下，只有一個線程可以修改數據，其他線程不允許訪問數據。

標準回答?

SQL 標準定義了四種隔離級別，這四種隔離級別分別是：

- 讀未提交（READ UNCOMMITTED）

- 讀提交 （READ COMMITTED）；

- 可重復讀 （REPEATABLE READ）；

- 串行化 （SERIALIZABLE）。

下面是四種隔離級別在解決臟讀、不可重復讀、幻讀問題方面的情況：

隔離級別	臟讀	不可重復讀	幻讀
讀未提交	存在	存在	存在
讀已提交	不存在	存在	存在
可重復讀	不存在	不存在	存在
串行化	不存在	不存在	不存在

臟讀（Dirty Read）：指一個事務讀取了另一個未提交的事務所寫入的數據，如果隔離級別越高，則越不容易出現臟讀問題。

不可重復讀（Non-Repeatable Read）：指一個事務在讀取同一數據時，由于另外一個事務的修改或刪除，導致兩次讀取的數據不同。如果隔離級別越高，則越不容易出現不可重復讀問題。

幻讀（Phantom Read）：指一個事務多次執(zhí)行同一個查詢，但每次返回的數據集合都不同，導致出現“幻像”行。如果隔離級別越高，則越不容易出現幻讀問題。

加分回答-鎖

READ UNCOMMITTED讀未提交：

它是性能最好、也最野蠻的方式，因為它壓根兒就不加鎖，所以根本談不上什么隔離效果，可以理解為沒有隔離。

REPEATABLE READ & READ COMMITTED：

為了解決不可重復讀，MySQL 采用了 MVVC (多版本并發(fā)控制) 的方式。

我們在數據庫表中看到的一行記錄可能實際上有多個版本，每個版本的記錄除了有數據本身外，還要有一個表示版本的字段，記為 row trx_id，而這個字段就是使其產生的事務的 id，事務 ID 記為 transaction id，它在事務開始的時候向事務系統(tǒng)申請，按時間先后順序遞增。

SERIALIZABLE：

讀的時候加共享鎖，其他事務可以并發(fā)讀，但是不能寫。寫的時候加排它鎖，其他事務不能并發(fā)寫也不能并發(fā)讀。

MySQL主從同步是如何實現的？

步驟、延時問題、原理（三個線程）、主從庫數量

主從同步實現步驟：?

1. 主服務器把數據更改記錄到二進制日志（binlog，記錄改不記錄讀，用于數據復制和數據恢復）中；
2. 從服務器異步近似實時地把主服務器的二進制日志復制到自己的中繼日志（relay log）中；
3. 從服務器重做中繼日志中的操作，把更改應用到自己的數據庫上，以達到數據的最終一致性。

復制最大的問題是主服務器壓力大導致的復制延時問題。

復制原理：?

• 主庫二進制日志轉儲線程：負責將二進制日志發(fā)給從庫。強制從主庫讀取數據的時，會給二進制日志加鎖?，讀完解鎖。
• 從庫I/O 線程：負責連接主庫，并向主庫發(fā)送請求和復制二進制日志到中繼日志。
• 從庫SQL 線程：負責讀取并執(zhí)行中繼日志中的更新語句，實現主從同步。?

主從庫數量：?

• 每個 Master 可以有多個 Slave
• 每個 Slave 只能有一個唯一的服務器ID，只有一個 Master。

?

標準答案

復制（replication）是MySQL數據庫提供的一種高可用高性能的解決方案，一般用來建立大型的應用?？傮w來說，replication的工作原理分為以下3個步驟：

1.主服務器（master）把數據更改記錄到二進制日志（binlog）中。

2.從服務器（slave）把主服務器的二進制日志復制到自己的中繼日志（relay log）中。

3.從服務器重做中繼日志中的事件，把更改應用到自己的數據庫上，以達到數據的最終一致性。

復制的工作原理并不復雜，其實就是一個完全備份加上二進制日志備份的還原。不同的是這個二進制日志的還原操作基本上實時在進行中。這里特別需要注意的是，復制不是完全實時地進行同步，而是異步實時。這中間存在主從服務器之間的執(zhí)行延時，如果主服務器的壓力很大，則可能導致主從服務器延時較大。

MySQL調優(yōu)

得分點：觀察、調優(yōu)

關鍵字：觀察、 查看狀態(tài)、慢查詢日志、定位慢sql、執(zhí)行成本、執(zhí)行計劃。調優(yōu)、緩存、mysql參數、定時清理數據、存儲引擎、索引、表結構、分表

觀察：查看狀態(tài)、開啟并分析慢查詢日志、定位慢sql語句，查詢sql執(zhí)行成本、分析sql執(zhí)行計劃

1. 查看狀態(tài)，如sql查詢成本、連接次數、慢查詢次數等性能參數；

   show status like '參數';

2. 開啟慢查詢日志，修改慢查詢閾值，mysqldumpslow分析慢查詢日志里查詢慢的語句；

   set slow_query_log='ON';    #開啟慢查詢日志
   set long_query_time = 1;     #設置慢查詢閾值

   #命令行，按照查詢時間排序，查看前五條 慢查詢SQL 語句
   mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log    

3. 定位慢sql語句，查詢sql執(zhí)行成本。開啟并使用show profiles和show profile查詢sql語句列表和指定sql語句執(zhí)行成本（持續(xù)時間、cpu、io情況）；
4. explan分析sql執(zhí)行計劃（訪問類型、記錄條數、索引長度等）；

調優(yōu)：緩存、mysql參數、定時清理數據、存儲引擎、索引、表結構、分表

1. 緩存優(yōu)化。mysql調整緩沖池大小等參數；引入redis。InnoDB使用緩沖池緩存記錄和索引
2. 配置MySQL參數。關閉不必要的服務和日志（調優(yōu)結束關閉慢查詢日志），調整最大連接數。
3. 定時清理數據。對于不再使用的表、數據、日志、緩存等，應該及時清理，避免占用過多的MySQL資源，從而提高MySQL的性能。
4. 使用合適的存儲引擎。MyISAM比較適合讀取頻繁，寫入較少的場景（因為表級鎖、B+樹葉存地址），而InnoDB比較適合并發(fā)寫入的場景（因為行級鎖、B+樹葉存記錄）。
5. 索引優(yōu)化。在頻繁查詢（特別是分組、范圍查詢）的列、唯一特性的列上建立索引，避免創(chuàng)建過多索引、聯合索引把頻繁查詢的列放左邊。
6. 表結構優(yōu)化。遵循第一范式（每個屬性不可再分），第二范式（表必須有一個主鍵），第三范式（非主鍵列必須直接依賴于主鍵），避免無限制的增加列，盡量使用枚舉類型。
7. 分表。將大表分成較小的表，局限范圍，在大量查詢時優(yōu)化性能。

MySQL的緩沖池被分為多個不同的緩存池，其中包括：

• 查詢緩存：用來緩存查詢結果。
• InnoDB緩存池：用來緩存熱點表和索引數據頁。
• MyISAM緩存池：用來緩存表數據塊。

MySQL的緩沖池使用的是LRU（最近最少使用）淘汰策略，它會優(yōu)先緩存最近使用的數據。當緩沖池的空間不足時，MySQL會將最不常用的數據從緩沖池中替換出去，以騰出空間緩存新的數據。

MySQL的緩存設置包括多個參數，其中比較常見的緩存參數包括以下幾個：

1. key_buffer_size：該參數用來設置MyISAM索引的緩存大小。如果應用程序中涉及到大量的索引查詢，可以適當提高該值。一般來說，key_buffer_size占用總內存的1/4到1/3比較合適。

2. innodb_buffer_pool_size：該參數用來設置InnoDB緩沖池的大小。InnoDB存儲引擎使用緩沖池來緩存數據和索引文件。如果InnoDB表的讀寫頻次較高，建議將該值設置為物理內存的70%到80%。

3. sort_buffer_size：該參數用來設置排序緩沖區(qū)大小。如果查詢中涉及到ORDER BY或GROUP BY操作，可以適當提高該值。一般來說，sort_buffer_size占用總內存的1/4到1/3比較合適。

4. read_buffer_size和read_rnd_buffer_size：這兩個參數是用來設置讀取緩沖區(qū)大小的，默認值為128 KB。如果應用程序中經常進行大文件的讀取操作，可以適當提高這兩個參數。

5. binlog_cache_size：該參數是用來設置二進制日志的緩存大小。如果應用程序中需要持久化一些數據，可以開啟二進制日志，并適當調整該參數。

總的來說，MySQL的緩存設置需要根據應用程序的實際情況進行調整，例如根據讀寫頻次調整InnoDB緩沖池大小，根據查詢特點調整sort_buffer_size大小等。合理的緩存設置可以大大提高MySQL的性能和穩(wěn)定性。

配置MySQL參數

• max_connections是MySQL的一個系統(tǒng)參數，它用來限制同一時刻可以與MySQL服務器建立的最大連接數。默認情況下，max_connections的值是100，可以通過設置my.cnf文件或動態(tài)修改來調整該值。如果應用程序需要處理大量的請求，可以適當提高max_connections的值。但是，需要注意的是，隨著max_connections的增加，MySQL服務器需要消耗更多的內存、CPU資源以及線程資源。如果過多的連接數，不僅會降低系統(tǒng)的響應速度，還可能導致系統(tǒng)崩潰或者出現僵尸進程。
• innodb_flush_log_at_trx_commit是MySQL InnoDB存儲引擎的一個重要參數，用來控制事務提交時的日志寫入行為。該參數的默認值為1，表示每次提交事務都會立即將事務日志寫入磁盤。在這種模式下，可以保證事務的持久化和一致性，但同時會降低系統(tǒng)性能，特別是在高并發(fā)寫入的場景下。