2021-Java后端工程師面試指南-(MySQL）
前言
文本已收錄至我的GitHub倉庫，歡迎Star：https://github.com/bin392328206/six-finger
種一棵樹最好的時間是十年前，其次是現(xiàn)在
Tips
面試指南系列，很多情況下不會去深挖細節(jié)，是小六六以被面試者的角色去回顧知識的一種方式，所以我默認大部分的東西，作為面試官的你，肯定是懂的。
https://www.processon.com/view/link/600ed9e9637689349038b0e4
上面的是腦圖地址
叨絮
可能大家覺得有點老生常談了，確實也是。面試題，面試寶典，隨便一搜，根本看不完，也看不過來，那我寫這個的意義又何在呢？其實嘛我寫這個的有以下的目的
第一就是通過一個體系的復習，讓自己前面的寫的文章再重新的過一遍，總結(jié)升華嘛 第二就是通過寫文章幫助大家建立一個復習體系，我會將大部分會問的的知識點以點帶面的形式給大家做一個導論
然后下面是前面的文章匯總
2021-Java后端工程師面試指南-(引言) 2021-Java后端工程師面試指南-(Java基礎篇） 2021-Java后端工程師面試指南-(并發(fā)-多線程) 2021-Java后端工程師面試指南-(JVM）
今天大家一起來復習復習MySQL吧
聊聊MySql的結(jié)構(gòu)吧
大體來說，MySQL 可以分為 Server 層和存儲引擎層兩部分。
Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服 務功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學和加密函數(shù)等），所有跨存儲引擎的功能都 在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。
而存儲引擎層負責數(shù)據(jù)的存儲和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、 Memory 等多個存儲引擎。現(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成 為了默認存儲引擎。
聊聊InnoDB和MyISAM的區(qū)別吧
第一個也是最重要的一個 InnoDB支持事務，MyISAM不支持 在MySQL中，表級鎖有兩種模式：表共享讀鎖，表獨占寫鎖。也就是說對于MyISAM引擎的表，多個用戶可以對同一個表發(fā)起讀的請求，但是如果一個用戶對表進行寫操作，那么則會阻塞其他用戶對這個表的讀和寫。InnoDB引擎的表是通過索引項來加鎖實現(xiàn)的，即只有通過索引條件檢索數(shù)據(jù)的時候，InnoDB才會使用行級鎖，否則也會使用表級鎖。 InnoDB聚集索引，MyISAM 非聚集索引 企業(yè)級生成環(huán)境強制用InnoDB，所以下面的面試題都是基于InnoDB。
說說一個查詢SQL的執(zhí)行過程
連接器：首先肯定和mysql建立連接的過程 查詢緩存:在8以前，mysql會把相同的sql，緩存起來，但是因為發(fā)現(xiàn)效率不是那么好，8之后刪除了 分析器: 如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。首先，MySQL 需要知道你要做什么，因此 需要對 SQL 語句做解析 優(yōu)化器：優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引 執(zhí)行器：MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進入了執(zhí)行器階 段，開始執(zhí)行語句 返回數(shù)據(jù)給到客戶端
說說一條SQL的插入流程
update T set c=c+1 where ID=2;
執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi) 存，然后再返回。 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的 一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務 執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤。 執(zhí)行器調(diào)用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀 態(tài)，更新完成。
說說Buffer Pool吧
它是mysql 一個非常重要的內(nèi)存組件，因為是在內(nèi)存中操作的，所以速度比較快 建議設置合理的buffer pool的大小，如果大小在內(nèi)存的百分60合適 要明確的是pool的結(jié)構(gòu)是一頁一頁的 如果內(nèi)存夠大，可以多設計幾個pool
Buffer Pool臟數(shù)據(jù)頁到底為什么會臟
是因為我們新增 更新 刪除操作的時候只是對內(nèi)存進行操作，和對我們redo log日志進行操作，所以呢就會有臟數(shù)據(jù) 在buffer pool里面 有一個維護臟數(shù)據(jù)頁的雙向鏈表，用來明確哪個數(shù)據(jù)頁需要刷 然后還有就是lru鏈表，就是假設我們的pool滿了，那么我們肯定要把一些數(shù)據(jù)刪除，就是lru算法了（基于冷熱數(shù)據(jù)分離的思想的lru）
說說InnoDB頁
InnoDB是一個將表中的數(shù)據(jù)存儲到磁盤上的存儲引擎，所以即使關(guān)機后重啟我們的數(shù)據(jù)還是存在的。而真正處理數(shù)據(jù)的過程是發(fā)生在內(nèi)存中的，所以需要把磁盤中的數(shù)據(jù)加載到內(nèi)存中，如果是處理寫入或修改請求的話，還需要把內(nèi)存中的內(nèi)容刷新到磁盤上。而我們知道讀寫磁盤的速度非常慢，和內(nèi)存讀寫差了幾個數(shù)量級，所以當我們想從表中獲取某些記錄時，InnoDB存儲引擎需要一條一條的把記錄從磁盤上讀出來么？不，那樣會慢死，InnoDB采取的方式是：將數(shù)據(jù)劃分為若干個頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小一般為 16 KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。
說說InnoDB行格式是怎么樣的
就是我們mysql里面一行的數(shù)據(jù)，再innodb里面分為了2個部分
一個是我們原始的數(shù)據(jù)，真實的數(shù)據(jù)，也就是列的值 還有一個額外的數(shù)據(jù) 一個是變長字段的列表，一個是NUll值，還有一個是記錄頭信息
聊聊整個磁盤的存儲的結(jié)構(gòu)
首先是InnoDB的頁存儲結(jié)構(gòu)，我們知道最大的結(jié)構(gòu)是表，表里面可以分為很多個區(qū)，每個區(qū)里面又有很多的頁 多個不同的頁組成的是一個雙向鏈表，而每個頁里面的數(shù)據(jù)行會按主鍵的大小組成一個單向鏈表，并且每4到8個數(shù)據(jù)組成一個槽，每個槽存儲在pageDirectoy里面 ，當我們要查詢頁的行數(shù)據(jù)的時候，可以先定位到頁，然后用2分法定位到槽，然后遍歷槽，來定位到當前行的數(shù)據(jù)。
聊聊索引吧
首先哈 索引的本質(zhì)是什么呢？其實索引就是一直加快磁盤查詢速度的一些數(shù)據(jù)結(jié)構(gòu)，因為我們磁盤i/o的性能比較慢，索引可以加快我們的查詢速度。
聊聊有哪些數(shù)據(jù)結(jié)構(gòu)適合做索引結(jié)構(gòu)的，優(yōu)缺點是什么
Hash索引：hash表，我相信大家都很熟悉了，他的優(yōu)點查詢速度快，但是他不支持范圍查詢，哈希表這種結(jié)構(gòu)適用于只有等值查詢的場景 二叉樹：如果數(shù)據(jù)多了，樹高會很高，查詢的成本就會隨著樹高的增加而增加。 B樹：B樹已經(jīng)是不錯的一個索引結(jié)構(gòu)了，但是他的子節(jié)點也存儲數(shù)據(jù)，所以還是不能控制數(shù)高，因為樹的高度，其實就是代表我們的io B+樹：其實很簡單，我們看一下上面的數(shù)據(jù)結(jié)構(gòu)，最開始的Hash不支持范圍查詢，二叉樹樹高很高，只有B樹跟B+有的一比。B樹一個節(jié)點可以存儲多個元素，相對于完全平衡二叉樹整體的樹高降低了，磁盤IO效率提高了。而B+樹是B樹的升級版，只是把非葉子節(jié)點冗余一下，這么做的好處是為了提高范圍查找的效率。
你可以說說InnoDB 的索引模型嗎？
主鍵索引，在 InnoDB 里，主鍵索引也被稱為聚簇索引 普通索引，就是我們一般的索引 唯一索引，具體排他性的索引 組合索，可以多個列的索引
說說怎么從磁盤上加載數(shù)據(jù)，也就是查詢的執(zhí)行方式
MySQL的查詢的執(zhí)行方式大致分為下邊兩種：
使用全表掃描進行查詢 使用索引進行查詢 針對主鍵或唯一二級索引的等值查詢 針對普通二級索引的等值查詢 針對索引列的范圍查詢 直接掃描整個索引
磁盤訪問方式的分類
const：通過主鍵或者唯一二級索引列與常數(shù)的等值比較來定位一條記錄 ref：對于某個包含多個索引列的二級索引來說，只要是最左邊的連續(xù)索引列是與常數(shù)的等值比較就可能采用ref的訪問方法 range：類似于范圍查詢的方式 index：這個是什么意思呢？就是比如我們的where條件不符合查詢的索引，但是查詢的條件在一個組合索引中，那我們遍歷索引數(shù)，比遍歷數(shù)據(jù)數(shù)要快。 all:最直接的查詢執(zhí)行方式就是全表掃描，對于InnoDB表來說也就是直接掃描聚簇索引.
說說常見的sql需要注意到的點，也就是sql優(yōu)化
對查詢進行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。 應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描 應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。 盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如： 應盡量避免在where子句中對字段進行函數(shù)操作，這將導致引擎放棄使用索引而進行全表掃描 不要在 where 子句中的“=”左邊進行函數(shù)、算術(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引 并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當索引列有大量數(shù)據(jù)重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。 索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數(shù)最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要 任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段 盡量避免大事務操作，提高系統(tǒng)并發(fā)能力 盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應該考慮相應需求是否合理。 最左原則，是設計組合索引的原則。 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
說說EXPLAIN關(guān)鍵字吧
小六六挑選幾個有參考價值的列來說說。
id 每個單查詢都有，id越大越先執(zhí)行,id相同表示加載表的順序是從上到下。 type ：這個字段就是我們前面說的查詢的分類了 重點關(guān)注 possible_keys 可能的索引 key 實際用到的索引 重點關(guān)注 key_len 實際使用的索引長度 rows 預估要讀取的行數(shù) 重點關(guān)注 Extra 額外的信息 比如看是否用到回表 Using index,或者是否用到了臨時表之類的
說說count(字段) count(主鍵 id) count(1) count(*)
count(主鍵 id) ，InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加 count(1) ，InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數(shù)字“1”進去，判斷是不可能為空的，按行累加。 count(字段)，如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加； count() ，并不會把全部字段取出來，而是專門做了優(yōu)化，不取值。count() 肯定不是 null，按行累加 按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count()，所以我建議你，盡量使用 count()。
事務
說說mysql的事務吧
ACID 這個肯定得背的
原子性(A)：事務是最小單位，不可再分 一致性?：事務要求所有的DML語句操作的時候，必須保證同時成功或者同時失敗 隔離性(I)：事務A和事務B之間具有隔離性 持久性(D)：是事務的保證，事務終結(jié)的標志(內(nèi)存的數(shù)據(jù)持久到硬盤文件中)
聊聊它的隔離級別吧
讀未提交 會發(fā)生臟讀 讀已提交 會發(fā)生 不可重復讀 可重復讀 會發(fā)生 幻讀 串行化，沒有問題
說說sping默認的事務傳播級別
Spring中事務的默認實現(xiàn)使用的是AOP，也就是代理的方式，如果大家在使用代碼測試時，同一個Service類中的方法相互調(diào)用需要使用注入的對象來調(diào)用，不要直接使用this.方法名來調(diào)用，this.方法名調(diào)用是對象內(nèi)部方法調(diào)用，不會通過Spring代理，也就是事務不會起作用 REQUIRED(Spring默認的事務傳播類型)，如果當前沒有事務，則自己新建一個事務，如果當前存在事務，則加入這個事務，這個我們一般用的最多 SUPPORTS 當前存在事務，則加入當前事務，如果當前沒有事務，就以非事務方法執(zhí)行 MANDATORY 當前存在事務，則加入當前事務，如果當前事務不存在，則拋出異常。 REQUIRES_NEW 創(chuàng)建一個新事務，如果存在當前事務，則掛起該事務。 NOT_SUPPORTED 始終以非事務方式執(zhí)行,如果當前存在事務，則掛起當前事務
說說MVCC唄，談談你自己的看法
在Mysql的InnoDB引擎中就是指在已提交讀(READ COMMITTD)和可重復讀(REPEATABLE READ)這兩種隔離級別下的事務對于SELECT操作會訪問版本鏈中的記錄的過程。 在InnoDB引擎表中，它的聚簇索引記錄中有兩個必要的隱藏列：trx_id和roll_pointer mvcc通過排它鎖的形式來修改數(shù)據(jù) 修改之前會把數(shù)據(jù)放到undolog日志，如果事務提交，那就條件到數(shù)據(jù)里面，如果事務回滾，則放棄這個事務鏈 讀已提交和可重復讀的MVcc的區(qū)別就是 再這個事務級別下，一個事務操作里面每次查詢都會生成一個新的視圖，更新自己最小事務id和最大事務id,然后可重復讀不會，它只會在事務開始的時候生成一個一致性視圖。
Mysql的主從架構(gòu)聊聊
說說什么是mysql主從復制？
主從復制是指將主數(shù)據(jù)庫的DDL和DML操作通過二進制日志傳到從數(shù)據(jù)庫上，然后在從數(shù)據(jù)庫上對這些日志進行重新執(zhí)行，從而使從數(shù)據(jù)庫和主數(shù)據(jù)庫的數(shù)據(jù)保持一致。
那你聊聊主從復制的原理
MySql主庫在事務提交時會把數(shù)據(jù)變更作為事件記錄在二進制日志Binlog中； 主庫推送二進制日志文件Binlog中的事件到從庫的中繼日志Relay Log中，之后從庫根據(jù)中繼日志重做數(shù)據(jù)變更操作，通過邏輯復制來達到主庫和從庫的數(shù)據(jù)一致性； MySql通過三個線程來完成主從庫間的數(shù)據(jù)復制，其中Binlog Dump線程跑在主庫上，I/O線程和SQL線程跑著從庫上； 當在從庫上啟動復制時，首先創(chuàng)建I/O線程連接主庫，主庫隨后創(chuàng)建Binlog Dump線程讀取數(shù)據(jù)庫事件并發(fā)送給I/O線程，I/O線程獲取到事件數(shù)據(jù)后更新到從庫的中繼日志Relay Log中去，之后從庫上的SQL線程讀取中繼日志Relay Log中更新的數(shù)據(jù)庫事件并應用，如下圖所示。
聊聊Mysql的分庫分表吧
首先來說說分庫分表的各種類型吧
垂直分表：這個就是我們說的把大表變成小表，也就是分字段 水平分表，就是說我們把數(shù)據(jù)分到多個表里面 按月分表，也就是這些數(shù)據(jù)不會變了，然后按時間分。查詢的時候不能跨月查詢 分庫的話，一般現(xiàn)在一個庫就是一個服務（按業(yè)務分庫），這樣分，或者是多個庫一個服務（按表分庫）
說說常用的分庫分表中間件
mycat：阿里開源的，但是目前生態(tài)不那么好了， Sharding Sphere 這個很好，融合了Sharding-JDBC、Sharding-Proxy、Sharding-Sidecar 文檔齊全 其實分庫分表你不用中間件自己也能做，就是他們也是代理的模式幫你去聚合查詢，如果你有5個庫，那你要查排序，是不是每個庫都要查出來，最后總的合起來排序這樣。分頁這些都是，實現(xiàn)起來還是很麻煩 ShardingSphere-JDBC 在 Java 的 JDBC 層提供的額外服務。它使用客戶端直連數(shù)據(jù)庫，以 jar 包形式提供服務，無需額外部署和依賴，可理解為增強版的 JDBC 驅(qū)動，完全兼容 JDBC 和各種 ORM 框架。 ShardingSphere-Proxy 是 Apache ShardingSphere 的第二個產(chǎn)品。它定位為透明化的數(shù)據(jù)庫代理端，提供封裝了數(shù)據(jù)庫二進制協(xié)議的服務端版本，用于完成對異構(gòu)語言的支持。
說說如何滿足“跨越多個水平切分數(shù)據(jù)庫，且分庫依據(jù)與排序依據(jù)為不同屬性，并需要進行分頁”的查詢需求
服務層通過uid取模將數(shù)據(jù)分布到兩個庫上去之后，每個數(shù)據(jù)庫都失去了全局視野，數(shù)據(jù)按照time局部排序之后由于不清楚到底是哪種情況，所以必須每個庫都返回3頁數(shù)據(jù) 業(yè)務折衷法-禁止跳頁查詢 用正常的方法取得第一頁數(shù)據(jù)，并得到第一頁記錄的time_max
結(jié)束
Mysql就這些吧，也不是很全，分庫分表有很多實戰(zhàn)，但是我們在公司用的hbase,所以對于這塊涉及沒有那么多，接下來Redis吧
日常求贊
好了各位，以上就是這篇文章的全部內(nèi)容了，能看到這里的人呀，都是真粉。
創(chuàng)作不易，各位的支持和認可，就是我創(chuàng)作的最大動力，我們下篇文章見
微信 搜 "六脈神劍的程序人生" 回復888 有我找的許多的資料送給大家
