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

標籤：其他

上一篇：第三部分：Spdlog 日志庫的實作原理

下一篇：第一部分：介紹 Spdlog 日志庫