MySQL面經

內容援引自JavaGuide、嗶哩嗶哩黑馬程式員資料庫從入門到精通，感謝各位大神原創分享

資料庫Mysql

常見的關係型資料庫包括mysql、SQL Server、Oracle、常見的非關係型資料庫Redis、MongDB等。

特點

Mysql開源免費，生態完善，支持事務、高可用（讀寫分離、分庫分表）。

基礎架構：

• 服務層：連接器、查詢緩存（移除）、分析器、優化器、執行器；通用日誌模塊binlog
• 存儲引擎層：插件式存儲引擎（為表設置存儲引擎），支持InnoDB、MyISAM等；InnoDB包括redolog和undolog日誌

存儲引擎

使用插件式存儲引擎，預設InnoDB支持事務、行鎖、外鍵，數據恢復（redolog），MyISAM不支持事務、採用表鎖、不支持外鍵，不支持數據恢復。此外InnoDB主鍵使用聚簇索引，葉子節點保存記錄，MyISAM使用非聚簇索引，葉子節點保存記錄的地址，兩者均為B+ Tree。

MySQL索引

用於快速查詢或快速定位的排序的數據結構，常見的索引結構包括Hash樹、B樹、B+樹、紅黑樹。InnoDB和MyISAM均使用B+樹作為索引結構。

索引優缺點

優點：加快檢索速度，創建唯一索引保證數據唯一性。缺點：創建、維護索引時間開銷，且索引占物理存儲空間。

索引結構

• 為什麼不使用hash？
  可能出現哈希碰撞（拉鏈式）、不支持順序查找和範圍查找。
• 為什麼不使用B樹？
  B樹節點存索引和數據，B+樹只有葉子節點存儲索引和數據且構成雙向鏈表，其它節點存儲索引，故相同數據量下B樹高度更高，查詢效率更低，且不支持範圍查找。
• 為什麼不使用紅黑樹？
  紅黑樹是自平衡二叉查找樹，樹過高造成大量的磁碟 IO。
• B+樹一般不超過3層，能存儲多少數據？
  最小存儲單元一頁16KB，葉子節點存索引和記錄，假設索引和一條記錄占1KB，則一頁可存16K/1K=16條記錄，非葉子節點存索引和指針，假設主鍵索引為bigint占8位元組，指針占6位元組，則一個節點可存16k/(8+4)=1170 個指針，兩層的B+樹可存1170*16條記錄，三層的B+樹可存1170*1170*16條記錄，約兩千萬數據量。

索引的類別

​ 索引相關的概念包括聚簇索引、非聚簇索引、主鍵索引、輔助索引、唯一索引、普通索引、聯合索引、覆蓋索引、首碼索引、全文索引。
​ 聚簇索引，葉子節點保存索引和記錄，非聚簇索引葉子節點保存索引和記錄相關值（記錄地址或主鍵），且InnoDB存儲引擎非聚簇索引不一定需要回表查詢（覆蓋索引）
​ 主鍵索引，非null，不可重覆，沒有顯示指定時檢查是否存在非null的唯一索引，存在則將該欄位作為主鍵索引否則預設創建6位元組的自增索引。設計表時不建議使用過長欄位作為主鍵，不建議使用非單調欄位作為主鍵（引發索引頻繁分裂，這解釋了為什麼不宜使用UUID作為主鍵）。
​ 聯合索引，多個欄位一起創建索引，索引使用要求滿足最左匹配原則，缺失停止匹配，範圍查詢右側欄位停止匹配

# 創建(a,b,c)聯合索引 等值查詢中a、ab、abc均可使用索引，b、bc、c不可使用索引，全部為等值查詢時欄位順序對是否使用索引不產生影響；
# 以下語句a,b走索引,c不走索引，建議將區分度高的欄位放最左側以過濾更多數據
select * from t where a=1 and b > 1 and c=1;  
# 如果是建立(a,c,b)聯合索引，則a,b,c都走索引

​ 索引下推：非聚簇索引遍歷過程中，根據索引中包含的欄位過濾不符合條件的記錄，減少回表次數。

正確使用索引

• 是否有必要創建索引，很少查詢的表沒必要創建索引，頻繁更新的欄位不適合創建索引；
• 為哪些欄位創建索引，為查詢欄位，排序欄位和分組欄位創建索引，優先創建聯合索引且區分度高的欄位放在左側（可能產生覆蓋索引效果，避免回表，且可以過濾較多記錄），字元串類型的欄位可優先考慮首碼索引；
• 避免索引失效，如隱式類型轉換、在欄位上進行函數操作、or邏輯中某條件欄位沒有索引則涉及的索引全部失效

索引優化

• SQL提示，在SQL語句中加入人為提示優化操作use index、ignore index、force index，註意use index僅是建議，不代表優化器會選擇的執行計劃；
• 插入數據，批量插入、手動提交事務、主鍵順序插入
• 主鍵 優化，減少主鍵長度、主鍵遞增、避免對主鍵進行修改
• update優化，InnoDB行鎖針對索引，有索引時鎖行，沒有索引鎖表

#id有主鍵索引，鎖行；
update student set no = '123' where id = 1; 
#name沒有索引，鎖表
update student set no = '123' where name = 'test'; 

• order by優化，多欄位排序且一個升序一個降序，要註意創建索引時索引的升序和降序
• limit優化，覆蓋索引、子查詢、聯表查詢

# 優化前
SELECT * FROM xxx limit 1000000,20
# 子查詢優化
SELECT * FROM xxx WHERE ID >=(select id from xxx limit 1000000, 1) limit 20;
# 聯表優化
SELECT * FROM xxx a JOIN (select id from xxx limit 1000000, 20) b ON a.ID = b.id;

MySQL事務

ACID原則，原子性、一致性、隔離性、持久性

​ 其中一致性是目的，原子性是指要麼都執行，要麼都不執行，隔離性是指併發事務的獨立性，持久性是指事務被提交後可持久化。

併發事務的問題，臟讀、不可重覆讀、幻讀

​ 臟讀是指事務A讀取事務B未提交的數據，不可重覆讀是指事務A多次讀某條記錄的讀取結果不同，幻讀是指幻讀指事務A讀取某一範圍的數據行，事務B在該範圍內插入了新行，事務A再讀取該範圍的數據行時，出現幻影行。

併發事務控制，鎖+MVCC

​ MySQL中通過讀寫鎖實現併發控制，讀鎖為共用鎖，寫鎖為排它鎖，讀讀相容，讀寫或寫寫互斥。按粒度MySQL鎖又可劃分為表鎖和行鎖，其中表鎖不會出現死鎖，鎖衝突概率高，併發性能低；行鎖針對索引欄位加鎖，會出現死鎖，併發度高，行鎖 又包括記錄鎖、間隙鎖、臨鍵鎖。

• 行鎖發生死鎖的場景描述

• MVCC 多版本併發控制

​ MySQL的隔離級別包括讀未提交（臟讀、不可重覆讀、幻讀風險），讀已提交（不可重覆讀，幻讀風險），可重覆讀（預設隔離級別，幻讀風險）和可串列化。特殊的，InnoDB實現的可重覆讀隔離級別可解決幻讀風險，快照讀由MVCC機制保證，當前讀使用臨鍵鎖保證。
​ 在讀已提交和可重覆讀隔離級別下，執行普通select會使用一致性非鎖定讀MVCC，讀記錄的快照數據；執行insert、delete、update、select...lock in share mode、select...for update會使用鎖定讀，讀取記錄的最新數據，並對讀取到的記錄加鎖，即當前讀。
​ MVCC機制的實現依賴隱藏欄位、Read View和undo log，InnoDB存儲引擎為記錄添加預設主鍵（主鍵不存在且不存在非空的唯一索引時預設添加）、事務id，回滾指針3個隱藏欄位；讀已提交隔離級別下每次select查詢前創建Read View，可重覆讀隔離級別下事務開始第一次select前創建Read View，Read View用於可見性判斷，主要包括m_low_limit_id、m_up_limit_id、m_ids、m_creator_trx_id欄位，根據數據可見性演算法（比較記錄的事務id和Read View中欄位）若當前記錄對該事務不可見則使用回滾指針進行數據回滾。

三大日誌

​ Mysql日誌包括查詢日誌、慢查詢日誌、錯誤日誌和binlog日誌、redolog日誌、undolog日誌，其中binlog支持數據備份和主從同步，rodolog支持數據 恢復以保證持久性，undolog支持事務回滾以保證原子性和支持MVCC多版本併發控制。

binlog - MySQL

​ binlog日誌支持數據備份和主從同步，包括三種記錄格式statement、row和mixed，其中statement記錄SQL語句（獲得時間戳等SQL語句容易導致數據備份不一致或主從數據不一致），row記錄SQL語句和操作數以規避以上問題，但占用記憶體，折中方案mixed由MySQL判斷是否會引起數據不一致，選擇statement或row。
​ binlog的刷盤策略：1）事務提交將binlog cache寫入到page cache，系統自行決定刷盤；2）事務提交進行刷盤；3）折中方案，提交事務binlog cache寫入到page cache，提交N個事務進行刷盤；

redolog - InnoDB

​ redolog日誌支持數據恢復，保證事務的持久性。Mysql數據以頁16KB為單位（頁、段、區、表），查詢記錄時從磁碟載入數據頁放入緩衝池Buffer pool中，後續查詢優先在緩衝池中查找，未命中再從磁碟載入，減少IO開銷。更新記錄時，更新緩存數據，將數據頁上的更新記錄到redolog buffer中，根據一定的刷盤策略進行持久化。
​ rodolog刷盤策略：1）事務提交不進行刷盤（Mysql實例掛或宕機可能會有一秒數據的丟失）；2）事務提交將redolog buffer寫入page cache中（Mysql實例掛沒有數據丟失，宕機可能會有一秒的數據丟失）；3）事務提交刷盤（Mysql實例掛或宕機不會有數據丟失）。兜底措施後臺線程每隔1s將redolog buffer寫入到page cache，然後進行刷盤；redolog buffer占用記憶體到一定閾值後臺線程主動刷盤。
​ 為什麼要使用redolog，而不是直接將修改的數據頁刷盤？通常數據更新隻影響數據頁中的少量記錄，且數據頁刷盤是隨機寫，刷盤成本高。採用redolog記錄更新屬於順序寫，刷盤成本低，有利於提高資料庫的併發能力。
​ 兩階段提交：redolog prepare - binlog - redolog commit。

• redolog-宕機-binlog，主從結構中，主使用redolog數據恢復，從使用binlog數據恢復，主從數據不一致。
• binlog-宕機-redolog，主從結構中主使用redolog，從使用binlog，主從數據不一致。
• 兩階段提交，redolog prepare - 宕機 - binlog - redolog commit，redolog有事務記錄，binlog沒有事務記錄，事務回滾；redolog prepare - binlog - 宕機 - redolog commit，redolog有事務記錄，binlog也有對應的事務記錄，提交事務恢復 數據。

undolog

​ undolog日誌支持事務回滾和MVCC，保證事務的原子性和隔離性。

MySQL執行計劃

explain sql