面試必問之mysql基礎

mysql存儲引擎

如何選擇mysql存儲引擎

先得了解下各個存儲引擎差別

功能	MylSAM	MEMORY	InnoDB	Archive
功能	MylSAM	MEMORY	InnoDB	Archive
存儲限制	256TB	RAM	64TB	None
支援事務	No	No	Yes	No
支援全文索引	Yes	No	No	No
支援樹索引	Yes	Yes	Yes	No
支援哈希索引	No	Yes	No	No
支援資料緩存	No	N/A	Yes	No
支援外鍵	No	No	Yes	No

可以根據以下的原則來選擇 MySQL 存儲引擎：

• 如果要提供送出、復原和恢複的事務安全（ACID 相容）能力，并要求實作并發控制，InnoDB 是一個很好的選擇。
• 如果資料表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率。
• 如果隻是臨時存放資料，資料量不大，并且不需要較高的資料安全性，可以選擇将資料儲存在記憶體的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時表，存放查詢的中間結果。
• 如果隻有 INSERT 和 SELECT 操作，可以選擇Archive 引擎，Archive 存儲引擎支援高并發的插入操作，但是本身并不是事務安全的。Archive 存儲引擎非常适合存儲歸檔資料，如記錄日志資訊可以使用 Archive 引擎。

提示：使用哪一種引擎要根據需要靈活選擇，一個資料庫中多個表可以使用不同的引擎以滿足各種性能和實際需求。使用合适的存儲引擎将會提高整個資料庫的性能。

詳細說一下innodb與MyISAM的差別

MyISAM和InnoDB的差別：

1. InnoDB支援事務，MyISAM不支援。對于InnoDB每一條SQL語言都預設封裝成事務，自動送出，這樣會影響速度，是以最好把多條SQL語言放在begin和commit之間，組成一個事務；
2. InnoDB支援外鍵，而MyISAM不支援。
3. InnoDB是聚集索引，使用B+Tree作為索引結構，資料檔案是和（主鍵）索引綁在一起的（表資料檔案本身就是按B+Tree組織的一個索引結構），必須要有主鍵，通過主鍵索引效率很高。MyISAM是非聚集索引，也是使用B+Tree作為索引結構，索引和資料檔案是分離的，索引儲存的是資料檔案的指針。主鍵索引和輔助索引是獨立的。
4. InnoDB不儲存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變量儲存了整個表的行數，執行上述語句時隻需要讀出該變量即可，速度很快。
5. Innodb不支援全文索引，而MyISAM支援全文索引，查詢效率上MyISAM要高；5.7以後的InnoDB支援全文索引了。
6. InnoDB支援表、行級鎖(預設)，而MyISAM支援表級鎖。
7. InnoDB表必須有主鍵（使用者沒有指定的話會自己找或生産一個主鍵），而Myisam可以沒有。
8. Innodb存儲檔案有frm、ibd，而Myisam是frm、MYD、MYI。 Innodb：frm是表定義檔案，ibd是資料檔案。 Myisam：frm是表定義檔案，myd是資料檔案，myi是索引檔案。

索引

什麼是索引

索引其實是一種資料結構，能夠幫助我們快速的檢索資料庫中的資料。

有哪幾種索引

按照功能劃分

• 普通索引：最基本的索引，沒有任何限制。
• 唯一索引：與普通索引類似，但具有唯一性限制。
• 主鍵索引：特殊的唯一索引，不允許有空值。
• 複合索引：将多個列組合在一起建立索引，可以覆寫多個列。
• 外鍵索引：隻有InnoDB類型的表才可以使用外鍵索引，保證資料的一緻性、完整性和實作級聯操作。
• 全文索引：MySQL 自帶的全文索引隻能用于 InnoDB、MyISAM ，并且隻能對英文進行全文檢索，一般使用全文索引引擎（ES，Solr）。

按照結構劃分

• Hash索引
• B+ Tree索引

我們常用的innodb預設是B+索引

為什麼采用B+ 樹嗎？

B+樹是為磁盤及其他存儲輔助裝置而設計一種平衡查找樹（不是二叉樹）。B+樹中，所有記錄的節點按大小順序存放在同一層的葉節點中，各葉節點用指針進行連接配接。

資料庫中B+樹索引分為聚集索引（clustered index）和非聚集索引（secondary index）.這兩種索引的共同點是内部都是B+樹，高度都是平衡的，葉節點存放着所有資料。不同點是葉節點是否存放着一整行資料。

B+樹有如下特點:

• B+樹每個節點可以包含更多的節點，這樣做有兩個原因,一個是降低樹的高度。另外一個是将資料範圍變為多個區間，區間越多，資料檢索越快。
• 每個節點不再隻是存儲一個key了，可以存儲多個key。
• 非葉子節點存儲key，葉子節點存儲key和資料。
• 葉子節點兩兩指針互相連結，順序查詢性能更高。

通俗的講

• B+樹的非葉子節點隻是存儲key，占用空間非常小，是以每一層的節點能索引到的資料範圍更加的廣。換句話說，每次IO操作可以搜尋更多的資料。
• 葉子節點兩兩相連，符合磁盤的預讀特性。比如葉子節點存儲50和55，它有個指針指向了60和62這個葉子節點，那麼當我們從磁盤讀取50和55對應的資料的時候，由于磁盤的預讀特性，會順便把60和62對應的資料讀取出來。這個時候屬于順序讀取，而不是磁盤尋道了，加快了速度。
• 支援範圍查詢，而且部分範圍查詢非常高效，每個節點能索引的範圍更大更精确,也意味着 B+樹單次磁盤IO的資訊量大于B-樹,I/O效率更高。

B+樹和Hash索引比較起來有什麼優缺點嗎？

• Hash索引僅僅能滿足"=","IN"和""查詢，不能使用範圍查詢,因為經過相應的Hash算法處理之後的Hash值的大小關系，并不能保證和Hash運算前完全一樣；
• Hash索引無法被用來避免資料的排序操作，因為Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣；
• Hash索引不能利用部分索引鍵查詢，對于組合索引，Hash索引在計算Hash值的時候是組合索引鍵合并後再一起計算Hash值，而不是單獨計算Hash值，是以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash索引也無法被利用；
• Hash索引在任何時候都不能避免表掃描，由于不同索引鍵存在相同Hash值，是以即使取滿足某個Hash鍵值的資料的記錄條數，也無法從Hash索引中直接完成查詢，還是要回表查詢資料；
• Hash索引遇到大量Hash值相等的情況後性能并不一定就會比B+樹索引高。 如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為隻需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據連結清單往後掃描，直到找到相應的資料；
• 如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；
• 同理，哈希索引沒辦法利用索引完成排序，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；
• 哈希索引也不支援多列聯合索引的最左比對規則；
• B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。
• 在大多數場景下，都會有範圍查詢、排序、分組等查詢特征，用B+樹索引就可以了。

為什麼主鍵推薦自增長

因為使用自增 id 可以避免頁分裂

什麼是頁分裂

mysql （注意本文講的 mysql 預設為InnoDB 引擎）底層資料結構是 B+ 樹，所謂的索引其實就是一顆 B+ 樹，一個表有多少個索引就會有多少顆 B+ 樹，mysql 中的資料都是按順序儲存在 B+ 樹上的（是以說索引本身是有序的）。 然後 mysql 在底層又是以資料頁為機關來存儲資料的，一個資料頁大小預設為 16k，當然你也可以自定義大小，也就是說如果一個資料頁存滿了，mysql 就會去申請一個新的資料頁來存儲資料。 如果主鍵為自增 id 的話，mysql 在寫滿一個資料頁的時候，直接申請另一個新資料頁接着寫就可以了。

如果主鍵是非自增 id，為了確定索引有序，mysql 就需要将每次插入的資料都放到合适的位置上。

當往一個快滿或已滿的資料頁中插入資料時，新插入的資料會将資料頁寫滿，mysql 就需要申請新的資料頁，并且把上個資料頁中的部分資料挪到新的資料頁上。

這就造成了頁分裂，這個大量移動資料的過程是會嚴重影響插入效率的。

其實對主鍵 id 還有一個小小的要求，在滿足業務需求的情況下，盡量使用占空間更小的主鍵 id，因為普通索引的葉子節點上儲存的是主鍵 id 的值，如果主鍵 id 占空間較大的話，那将會成倍增加 mysql 空間占用大小。

mysql鎖了解嗎？

MySQL有哪幾種鎖，能說下嗎？

1. 類型次元

• 共享鎖（讀鎖 / S 鎖）
• 排它鎖（寫鎖 / X 鎖） 類型細分： 意向共享鎖 意向排他（互斥）鎖
• 悲觀鎖（使用鎖，即 for update）
• 樂觀鎖（使用版本号字段，類似 CAS 機制，即使用者自己控制。缺點：并發很高的時候，多了很多無用的重試）

2. 鎖的粒度（粒度次元）

• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。
• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

3. 鎖的算法（算法次元）

• Record Lock（單行記錄）
• Gap Lock（間隙鎖，鎖定一個範圍，但不包含鎖定記錄）
• Next-Key Lock（Record Lock + Gap Lock，鎖定一個範圍，并且鎖定記錄本身， MySql 防止幻讀，就是使用此鎖實作）

預設讀會上鎖嗎？

預設是 MVCC 機制（“一緻性非鎖定讀”）保證 RR 級别的隔離正确性，是不上鎖的。 可以選擇手動上鎖：select xxxx for update (排他鎖); select xxxx lock in share mode(共享鎖)，稱之為“一緻性鎖定讀”。

使用鎖之後，就能在 RR 級别下，避免幻讀。當然，預設的 MVCC 讀，也能避免幻讀。

高并發情況下鎖會帶來有哪些問題？

在高并發的情況下事務的并發處理會帶來幾個問題

• 髒讀：指在事務 A 處理過程裡讀取到了事務 B 未送出的事務中的資料。比如在轉賬的例子中：小 A 開了一個事務給小 B 轉了1000 塊，還沒送出事務的時候就跟小 B 說，錢已經到賬了。這個時候小 B 去看了一下餘額，發現果真到賬了（然後就開開心心刷抖音去了），這個時候小 A 復原了事務，把 1000 塊又搞回去了。小 B 刷完抖音再去看下餘額，發現錢又不見了。
• 不可重複讀：指在一個事務執行的過程中多次查詢某一資料的時候結果不一緻的現象，由于在執行的過程中被另一個事務修改了這個資料并送出了事務。比如：事務 A 第一次讀小明的年齡是 18 歲，此時事務 B 将小明的年齡改成了 20 并送出了，這個時候事務 A 再次讀取小明的年齡發現是 20，這就是同一條資料不可重複讀。
• 幻讀：幻讀通常指的是對一批資料的操作完成後，有其他事務又插入了滿足條件的資料導緻的現象。比如：事務 A 将資料庫性别為男的狀态都改成1 表示有錢人，這個時候事務 B 又插入了一條狀态為 0 沒錢人的記錄，這個時候，使用者再檢視剛剛修改的資料時就會發現還有一行沒有修改，這就出現了幻讀。幻讀往往針對 insert 操作，髒讀和不可重複讀針對 select 操作。

mysql是怎麼處理的

mysql針對上述問題增加了事務的隔離級别

• Read uncommitted (讀未送出)：最低級别，任何情況都無法保證。
• Read committed (讀已送出)：可避免髒讀的發生。
• Repeatable read (可重複讀)：可避免髒讀、不可重複讀的發生。
• Serializable (串行化)：可避免髒讀、不可重複讀、幻讀的發生。