招銀面試官，聽說你精通 MySQL，我們來大戰(zhàn) 66 回合

二哥編程知識(shí)星球 （戳鏈接加入）正式上線了，來和 280 多名 小伙伴一起打怪升級(jí)吧！這是一個(gè) Java 學(xué)習(xí)指南 + 編程實(shí)戰(zhàn)的私密圈子，你可以向二哥提問、幫你制定學(xué)習(xí)計(jì)劃、跟著二哥一起做實(shí)戰(zhàn)項(xiàng)目，沖沖沖。

Java程序員進(jìn)階之路網(wǎng)址：https://tobebetterjavaer.com

總之，MySQL 必須得會(huì)，還得“精通”，今天給大家分享一波 MySQL 連環(huán) 66 問，2 萬字+50 張手繪圖，可以啃起來了。

日常開發(fā)，不會(huì) MySQL，領(lǐng)導(dǎo)都看你不順眼；當(dāng)然了，如果你發(fā)現(xiàn)領(lǐng)導(dǎo)不會(huì) MySQL，除了可以惡狠狠地鄙視他之外，一定得反思，這家伙不會(huì)是老板的親戚吧？

你去面試，不會(huì) MySQL，出門左拐右拐都行，反正就是回去等消息。

大家好，我是二哥呀。有一說一，MySQL 已經(jīng)成為 Java 后端程序員必須要掌握的技能之一了。

（玩了個(gè)倒序，相信大家雪亮的眼睛發(fā)現(xiàn)哪里不對(duì)勁了?。?/p>

基礎(chǔ)

作為 SQL Boy，基礎(chǔ)部分不會(huì)有人不會(huì)吧？面試也不怎么問，基礎(chǔ)掌握不錯(cuò)的小伙伴可以跳過這一部分。當(dāng)然，可能會(huì)現(xiàn)場(chǎng)寫一些 SQL 語句，SQ 語句可以通過牛客、LeetCode、LintCode 之類的網(wǎng)站來練習(xí)。

1. 什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？

• 內(nèi)連接（inner join）：取得兩張表中滿足存在連接匹配關(guān)系的記錄。
• 外連接（outer join）：不只取得兩張表中滿足存在連接匹配關(guān)系的記錄，還包括某張表（或兩張表）中不滿足匹配關(guān)系的記錄。
• 交叉連接（cross join）：顯示兩張表所有記錄一一對(duì)應(yīng)，沒有匹配關(guān)系進(jìn)行篩選，它是笛卡爾積在 SQL 中的實(shí)現(xiàn)，如果 A 表有 m 行，B 表有 n 行，那么 A 和 B 交叉連接的結(jié)果就有 m*n 行。
• 笛卡爾積：是數(shù)學(xué)中的一個(gè)概念，例如集合 A={a,b}，集合 B={1,2,3}，那么 A??B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

2. 那 MySQL 的內(nèi)連接、左連接、右連接有有什么區(qū)別？

MySQL 的連接主要分為內(nèi)連接和外連接，外連接常用的有左連接、右連接。

MySQL-joins-來源菜鳥教程

• inner join 內(nèi)連接，在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集
• left join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回左表所有的行，即使在右表中沒有匹配的記錄。
• right join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒有匹配的記錄。

3.說一下數(shù)據(jù)庫的三大范式？

• 第一范式：數(shù)據(jù)表中的每一列（每個(gè)字段）都不可以再拆分。例如用戶表，用戶地址還可以拆分成國(guó)家、省份、市，這樣才是符合第一范式的。
• 第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。例如訂單表里，存儲(chǔ)了商品信息（商品價(jià)格、商品類型），那就需要把商品 ID 和訂單 ID 作為聯(lián)合主鍵，才滿足第二范式。
• 第三范式：在滿足第二范式的基礎(chǔ)上，表中的非主鍵只依賴于主鍵，而不依賴于其他非主鍵。例如訂單表，就不能存儲(chǔ)用戶信息（姓名、地址）。

三大范式的作用是為了控制數(shù)據(jù)庫的冗余，是對(duì)空間的節(jié)省，實(shí)際上，一般互聯(lián)網(wǎng)公司的設(shè)計(jì)都是反范式的，通過冗余一些數(shù)據(jù)，避免跨表跨庫，利用空間換時(shí)間，提高性能。

4.varchar 與 char 的區(qū)別？

char：

• char 表示定長(zhǎng)字符串，長(zhǎng)度是固定的；
• 如果插入數(shù)據(jù)的長(zhǎng)度小于 char 的固定長(zhǎng)度時(shí)，則用空格填充；
• 因?yàn)殚L(zhǎng)度固定，所以存取速度要比 varchar 快很多，甚至能快 50%，但正因?yàn)槠溟L(zhǎng)度固定，所以會(huì)占據(jù)多余的空間，是空間換時(shí)間的做法；
• 對(duì)于 char 來說，最多能存放的字符個(gè)數(shù)為 255，和編碼無關(guān)

varchar：

• varchar 表示可變長(zhǎng)字符串，長(zhǎng)度是可變的；
• 插入的數(shù)據(jù)是多長(zhǎng)，就按照多長(zhǎng)來存儲(chǔ)；
• varchar 在存取方面與 char 相反，它存取慢，因?yàn)殚L(zhǎng)度不固定，但正因如此，不占據(jù)多余的空間，是時(shí)間換空間的做法；
• 對(duì)于 varchar 來說，最多能存放的字符個(gè)數(shù)為 65532

日常的設(shè)計(jì)，對(duì)于長(zhǎng)度相對(duì)固定的字符串，可以使用 char，對(duì)于長(zhǎng)度不確定的，使用 varchar 更合適一些。

5.blob 和 text 有什么區(qū)別？

• blob 用于存儲(chǔ)二進(jìn)制數(shù)據(jù)，而 text 用于存儲(chǔ)大字符串。
• blob 沒有字符集，text 有一個(gè)字符集，并且根據(jù)字符集的校對(duì)規(guī)則對(duì)值進(jìn)行排序和比較

6.DATETIME 和 TIMESTAMP 的異同？

相同點(diǎn)：

1. 兩個(gè)數(shù)據(jù)類型存儲(chǔ)時(shí)間的表現(xiàn)格式一致。均為 YYYY-MM-DD HH:MM:SS
2. 兩個(gè)數(shù)據(jù)類型都包含「日期」和「時(shí)間」部分。
3. 兩個(gè)數(shù)據(jù)類型都可以存儲(chǔ)微秒的小數(shù)秒（秒后 6 位小數(shù)秒）

區(qū)別：

DATETIME 和 TIMESTAMP 的區(qū)別

1. 日期范圍：DATETIME 的日期范圍是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的時(shí)間范圍是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
2. 存儲(chǔ)空間：DATETIME 的存儲(chǔ)空間為 8 字節(jié)；TIMESTAMP 的存儲(chǔ)空間為 4 字節(jié)
3. 時(shí)區(qū)相關(guān)：DATETIME 存儲(chǔ)時(shí)間與時(shí)區(qū)無關(guān)；TIMESTAMP 存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴于時(shí)區(qū)
4. 默認(rèn)值：DATETIME 的默認(rèn)值為 null；TIMESTAMP 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時(shí)間(CURRENT_TIMESTAMP)

7.MySQL 中 in 和 exists 的區(qū)別？

MySQL 中的 in 語句是把外表和內(nèi)表作 hash 連接，而 exists 語句是對(duì)外表作 loop 循環(huán)，每次 loop 循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。我們可能認(rèn)為 exists 比 in 語句的效率要高，這種說法其實(shí)是不準(zhǔn)確的，要區(qū)分情景：

1. 如果查詢的兩個(gè)表大小相當(dāng)，那么用 in 和 exists 差別不大。
2. 如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用 exists，子查詢表小的用 in。
3. not in 和 not exists：如果查詢語句使用了 not in，那么內(nèi)外表都進(jìn)行全表掃描，沒有用到索引；而 not extsts 的子查詢依然能用到表上的索引。所以無論那個(gè)表大，用 not exists 都比 not in 要快。

8.MySQL 里記錄貨幣用什么字段類型比較好？

貨幣在數(shù)據(jù)庫中 MySQL 常用 Decimal 和 Numric 類型表示，這兩種類型被 MySQL 實(shí)現(xiàn)為同樣的類型。他們被用于保存與貨幣有關(guān)的數(shù)據(jù)。

例如 salary DECIMAL(9,2)，9(precision)代表將被用于存儲(chǔ)值的總的小數(shù)位數(shù)，而 2(scale)代表將被用于存儲(chǔ)小數(shù)點(diǎn)后的位數(shù)。存儲(chǔ)在 salary 列中的值的范圍是從-9999999.99 到 9999999.99。

DECIMAL 和 NUMERIC 值作為字符串存儲(chǔ)，而不是作為二進(jìn)制浮點(diǎn)數(shù)，以便保存那些值的小數(shù)精度。

之所以不使用 float 或者 double 的原因：因?yàn)?float 和 double 是以二進(jìn)制存儲(chǔ)的，所以有一定的誤差。

9.MySQL 怎么存儲(chǔ) emoji???

MySQL 可以直接使用字符串存儲(chǔ) emoji。

但是需要注意的，utf8 編碼是不行的，MySQL 中的 utf8 是閹割版的 utf8，它最多只用 3 個(gè)字節(jié)存儲(chǔ)字符，所以存儲(chǔ)不了表情。那該怎么辦？

需要使用 utf8mb4 編碼。

alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;

10.drop、delete 與 truncate 的區(qū)別？

三者都表示刪除，但是三者有一些差別：

因此，在不再需要一張表的時(shí)候，用 drop；在想刪除部分?jǐn)?shù)據(jù)行時(shí)候，用 delete；在保留表而刪除所有數(shù)據(jù)的時(shí)候用 truncate。

11.UNION 與 UNION ALL 的區(qū)別？

• 如果使用 UNION ALL，不會(huì)合并重復(fù)的記錄行
• 效率 UNION 高于 UNION ALL

12.count(1)、count(*) 與 count(列名) 的區(qū)別？

執(zhí)行效果：

• count(*)包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為 NULL
• count(1)包括了忽略所有列，用 1 代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為 NULL
• count(列名)只包括列名那一列，在統(tǒng)計(jì)結(jié)果的時(shí)候，會(huì)忽略列值為空（這里的空不是只空字符串或者 0，而是表示 null）的計(jì)數(shù)，即某個(gè)字段值為 NULL 時(shí)，不統(tǒng)計(jì)。

執(zhí)行速度：

• 列名為主鍵，count(列名)會(huì)比 count(1)快
• 列名不為主鍵，count(1)會(huì)比 count(列名)快
• 如果表多個(gè)列并且沒有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于 count（*）
• 如果有主鍵，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
• 如果表只有一個(gè)字段，則 select count（*）最優(yōu)。

13.一條 SQL 查詢語句的執(zhí)行順序？

1. FROM：對(duì) FROM 子句中的左表<left_table>和右表<right_table>執(zhí)行笛卡兒積（Cartesianproduct），產(chǎn)生虛擬表 VT1
2. ON：對(duì)虛擬表 VT1 應(yīng)用 ON 篩選，只有那些符合<join_condition>的行才被插入虛擬表 VT2 中
3. JOIN：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作為外部行添加到虛擬表 VT2 中，產(chǎn)生虛擬表 VT3。如果 FROM 子句包含兩個(gè)以上表，則對(duì)上一個(gè)連接生成的結(jié)果表 VT3 和下一個(gè)表重復(fù)執(zhí)行步驟 1）～步驟 3），直到處理完所有的表為止
4. WHERE：對(duì)虛擬表 VT3 應(yīng)用 WHERE 過濾條件，只有符合<where_condition>的記錄才被插入虛擬表 VT4 中
5. GROUP BY：根據(jù) GROUP BY 子句中的列，對(duì) VT4 中的記錄進(jìn)行分組操作，產(chǎn)生 VT5
6. CUBE|ROLLUP：對(duì)表 VT5 進(jìn)行 CUBE 或 ROLLUP 操作，產(chǎn)生表 VT6
7. HAVING：對(duì)虛擬表 VT6 應(yīng)用 HAVING 過濾器，只有符合<having_condition>的記錄才被插入虛擬表 VT7 中。
8. SELECT：第二次執(zhí)行 SELECT 操作，選擇指定的列，插入到虛擬表 VT8 中
9. DISTINCT：去除重復(fù)數(shù)據(jù)，產(chǎn)生虛擬表 VT9
10. ORDER BY：將虛擬表 VT9 中的記錄按照<order_by_list>進(jìn)行排序操作，產(chǎn)生虛擬表 VT10。11）
11. LIMIT：取出指定行的記錄，產(chǎn)生虛擬表 VT11，并返回給查詢用戶

數(shù)據(jù)庫架構(gòu)

14.說說 MySQL 的基礎(chǔ)架構(gòu)?

MySQL 邏輯架構(gòu)圖主要分三層：

• 客戶端：最上層的服務(wù)并不是 MySQL 所獨(dú)有的，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)。比如連接處理、授權(quán)認(rèn)證、安全等等。
• Server 層：大多數(shù) MySQL 的核心服務(wù)功能都在這一層，包括查詢解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時(shí)間、數(shù)學(xué)和加密函數(shù)），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過程、觸發(fā)器、視圖等。
• 存儲(chǔ)引擎層：第三層包含了存儲(chǔ)引擎。存儲(chǔ)引擎負(fù)責(zé) MySQL 中數(shù)據(jù)的存儲(chǔ)和提取。Server 層通過 API 與存儲(chǔ)引擎進(jìn)行通信。這些接口屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層的查詢過程透明。

15.一條 SQL 查詢語句在 MySQL 中如何執(zhí)行的？

• 先檢查該語句是否有權(quán)限，如果沒有權(quán)限，直接返回錯(cuò)誤信息，如果有權(quán)限會(huì)先查詢緩存 (MySQL8.0 版本以前)。
• 如果沒有緩存，分析器進(jìn)行語法分析，提取 sql 語句中 select 等關(guān)鍵元素，然后判斷 sql 語句是否有語法錯(cuò)誤，比如關(guān)鍵詞是否正確等等。
• 語法解析之后，MySQL 的服務(wù)器會(huì)對(duì)查詢的語句進(jìn)行優(yōu)化，確定執(zhí)行的方案。
• 完成查詢優(yōu)化后，按照生成的執(zhí)行計(jì)劃調(diào)用數(shù)據(jù)庫引擎接口，返回執(zhí)行結(jié)果。

存儲(chǔ)引擎

16.MySQL 有哪些常見存儲(chǔ)引擎？

主要存儲(chǔ)引擎以及功能如下：

MySQL5.5 之前，默認(rèn)存儲(chǔ)引擎是 MylSAM，5.5 之后變成了 InnoDB。

InnoDB 支持的哈希索引是自適應(yīng)的，InnoDB 會(huì)根據(jù)表的使用情況自動(dòng)為表生成哈希索引，不能人為干預(yù)是否在一張表中生成哈希索引。

MySQL 5.6 開始 InnoDB 支持全文索引。

17.那存儲(chǔ)引擎應(yīng)該怎么選擇？

大致上可以這么選擇：

• 大多數(shù)情況下，使用默認(rèn)的 InnoDB 就夠了。如果要提供提交、回滾和恢復(fù)的事務(wù)安全（ACID 兼容）能力，并要求實(shí)現(xiàn)并發(fā)控制，InnoDB 就是比較靠前的選擇了。
• 如果數(shù)據(jù)表主要用來插入和查詢記錄，則 MyISAM 引擎提供較高的處理效率。
• 如果只是臨時(shí)存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時(shí)表，存放查詢的中間結(jié)果。

使用哪一種引擎可以根據(jù)需要靈活選擇，因?yàn)榇鎯?chǔ)引擎是基于表的，所以一個(gè)數(shù)據(jù)庫中多個(gè)表可以使用不同的引擎以滿足各種性能和實(shí)際需求。使用合適的存儲(chǔ)引擎將會(huì)提高整個(gè)數(shù)據(jù)庫的性能。

18.InnoDB 和 MylSAM 主要有什么區(qū)別？

PS:MySQL8.0 都開始慢慢流行了，如果不是面試，MylSAM 其實(shí)可以不用怎么了解。

1.   存儲(chǔ)結(jié)構(gòu)：每個(gè) MyISAM 在磁盤上存儲(chǔ)成三個(gè)文件；InnoDB 所有的表都保存在同一個(gè)數(shù)據(jù)文件中（也可能是多個(gè)文件，或者是獨(dú)立的表空間文件），InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB。

2. 事務(wù)支持：MyISAM 不提供事務(wù)支持；InnoDB 提供事務(wù)支持事務(wù)，具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全特性。

3   最小鎖粒度：MyISAM 只支持表級(jí)鎖，更新時(shí)會(huì)鎖住整張表，導(dǎo)致其它查詢和更新都會(huì)被阻塞 InnoDB 支持行級(jí)鎖。

4. 索引類型：MyISAM 的索引為聚簇索引，數(shù)據(jù)結(jié)構(gòu)是 B 樹；InnoDB 的索引是非聚簇索引，數(shù)據(jù)結(jié)構(gòu)是 B+樹。

5.   主鍵必需：MyISAM 允許沒有任何索引和主鍵的表存在；InnoDB 如果沒有設(shè)定主鍵或者非空唯一索引，**就會(huì)自動(dòng)生成一個(gè) 6 字節(jié)的主鍵(用戶不可見)**，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

6. 表的具體行數(shù)：MyISAM 保存了表的總行數(shù)，如果 select count(*) from table;會(huì)直接取出出該值; InnoDB 沒有保存表的總行數(shù)，如果使用 select count(*) from table；就會(huì)遍歷整個(gè)表;但是在加了 wehre 條件后，MyISAM 和 InnoDB 處理的方式都一樣。

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

日志

19.MySQL 日志文件有哪些？分別介紹下作用？

MySQL 日志文件有很多，包括 ：

• 錯(cuò)誤日志（error log）：錯(cuò)誤日志文件對(duì) MySQL 的啟動(dòng)、運(yùn)行、關(guān)閉過程進(jìn)行了記錄，能幫助定位 MySQL 問題。
• 慢查詢?nèi)罩?/strong>（slow query log）：慢查詢?nèi)罩臼怯脕碛涗泩?zhí)行時(shí)間超過 long_query_time 這個(gè)變量定義的時(shí)長(zhǎng)的查詢語句。通過慢查詢?nèi)罩?，可以查找出哪些查詢語句的執(zhí)行效率很低，以便進(jìn)行優(yōu)化。
• 一般查詢?nèi)罩?/strong>（general log）：一般查詢?nèi)罩居涗浟怂袑?duì) MySQL 數(shù)據(jù)庫請(qǐng)求的信息，無論請(qǐng)求是否正確執(zhí)行。
• 二進(jìn)制日志（bin log）：關(guān)于二進(jìn)制日志，它記錄了數(shù)據(jù)庫所有執(zhí)行的 DDL 和 DML 語句（除了數(shù)據(jù)查詢語句 select、show 等），以事件形式記錄并保存在二進(jìn)制文件中。

還有兩個(gè) InnoDB 存儲(chǔ)引擎特有的日志文件：

• 重做日志（redo log）：重做日志至關(guān)重要，因?yàn)樗鼈冇涗浟藢?duì)于 InnoDB 存儲(chǔ)引擎的事務(wù)日志。
• 回滾日志（undo log）：回滾日志同樣也是 InnoDB 引擎提供的日志，顧名思義，回滾日志的作用就是對(duì)數(shù)據(jù)進(jìn)行回滾。當(dāng)事務(wù)對(duì)數(shù)據(jù)庫進(jìn)行修改，InnoDB 引擎不僅會(huì)記錄 redo log，還會(huì)生成對(duì)應(yīng)的 undo log 日志；如果事務(wù)執(zhí)行失敗或調(diào)用了 rollback，導(dǎo)致事務(wù)需要回滾，就可以利用 undo log 中的信息將數(shù)據(jù)回滾到修改之前的樣子。

20.binlog 和 redo log 有什么區(qū)別？

• bin log 會(huì)記錄所有與數(shù)據(jù)庫有關(guān)的日志記錄，包括 InnoDB、MyISAM 等存儲(chǔ)引擎的日志，而 redo log 只記 InnoDB 存儲(chǔ)引擎的日志。
• 記錄的內(nèi)容不同，bin log 記錄的是關(guān)于一個(gè)事務(wù)的具體操作內(nèi)容，即該日志是邏輯日志。而 redo log 記錄的是關(guān)于每個(gè)頁（Page）的更改的物理情況。
• 寫入的時(shí)間不同，bin log 僅在事務(wù)提交前進(jìn)行提交，也就是只寫磁盤一次。而在事務(wù)進(jìn)行的過程中，卻不斷有 redo ertry 被寫入 redo log 中。
• 寫入的方式也不相同，redo log 是循環(huán)寫入和擦除，bin log 是追加寫入，不會(huì)覆蓋已經(jīng)寫的文件。

21.一條更新語句怎么執(zhí)行的了解嗎？

更新語句的執(zhí)行是 Server 層和引擎層配合完成，數(shù)據(jù)除了要寫入表中，還要記錄相應(yīng)的日志。

1. 執(zhí)行器先找引擎獲取 ID=2 這一行。ID 是主鍵，存儲(chǔ)引擎檢索數(shù)據(jù)，找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。
2. 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
3. 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。
4. 執(zhí)行器生成這個(gè)操作的 binlog，并把 binlog 寫入磁盤。
5. 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

從上圖可以看出，MySQL 在執(zhí)行更新語句的時(shí)候，在服務(wù)層進(jìn)行語句的解析和執(zhí)行，在引擎層進(jìn)行數(shù)據(jù)的提取和存儲(chǔ)；同時(shí)在服務(wù)層對(duì) binlog 進(jìn)行寫入，在 InnoDB 內(nèi)進(jìn)行 redo log 的寫入。

不僅如此，在對(duì) redo log 寫入時(shí)有兩個(gè)階段的提交，一是 binlog 寫入之前prepare狀態(tài)的寫入，二是 binlog 寫入之后commit狀態(tài)的寫入。

22.那為什么要兩階段提交呢？

為什么要兩階段提交呢？直接提交不行嗎？

我們可以假設(shè)不采用兩階段提交的方式，而是采用“單階段”進(jìn)行提交，即要么先寫入 redo log，后寫入 binlog；要么先寫入 binlog，后寫入 redo log。這兩種方式的提交都會(huì)導(dǎo)致原先數(shù)據(jù)庫的狀態(tài)和被恢復(fù)后的數(shù)據(jù)庫的狀態(tài)不一致。

先寫入 redo log，后寫入 binlog：

在寫完 redo log 之后，數(shù)據(jù)此時(shí)具有crash-safe能力，因此系統(tǒng)崩潰，數(shù)據(jù)會(huì)恢復(fù)成事務(wù)開始之前的狀態(tài)。但是，若在 redo log 寫完時(shí)候，binlog 寫入之前，系統(tǒng)發(fā)生了宕機(jī)。此時(shí) binlog 沒有對(duì)上面的更新語句進(jìn)行保存，導(dǎo)致當(dāng)使用 binlog 進(jìn)行數(shù)據(jù)庫的備份或者恢復(fù)時(shí)，就少了上述的更新語句。從而使得id=2這一行的數(shù)據(jù)沒有被更新。

先寫入 binlog，后寫入 redo log：

寫完 binlog 之后，所有的語句都被保存，所以通過 binlog 復(fù)制或恢復(fù)出來的數(shù)據(jù)庫中 id=2 這一行的數(shù)據(jù)會(huì)被更新為 a=1。但是如果在 redo log 寫入之前，系統(tǒng)崩潰，那么 redo log 中記錄的這個(gè)事務(wù)會(huì)無效，導(dǎo)致實(shí)際數(shù)據(jù)庫中id=2這一行的數(shù)據(jù)并沒有更新。

簡(jiǎn)單說，redo log 和 binlog 都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。

23.redo log 怎么刷入磁盤的知道嗎？

redo log 的寫入不是直接落到磁盤，而是在內(nèi)存中設(shè)置了一片稱之為redo log buffer的連續(xù)內(nèi)存空間，也就是redo 日志緩沖區(qū)。

什么時(shí)候會(huì)刷入磁盤？

在如下的一些情況中，log buffer 的數(shù)據(jù)會(huì)刷入磁盤：

• log buffer 空間不足時(shí)

log buffer 的大小是有限的，如果不停的往這個(gè)有限大小的 log buffer 里塞入日志，很快它就會(huì)被填滿。如果當(dāng)前寫入 log buffer 的 redo 日志量已經(jīng)占滿了 log buffer 總?cè)萘康拇蠹s一半左右，就需要把這些日志刷新到磁盤上。

• 事務(wù)提交時(shí)

在事務(wù)提交時(shí)，為了保證持久性，會(huì)把 log buffer 中的日志全部刷到磁盤。注意，這時(shí)候，除了本事務(wù)的，可能還會(huì)刷入其它事務(wù)的日志。

• 后臺(tái)線程輸入

有一個(gè)后臺(tái)線程，大約每秒都會(huì)刷新一次log buffer中的redo log到磁盤。

• 正常關(guān)閉服務(wù)器時(shí)
• 觸發(fā) checkpoint 規(guī)則

重做日志緩存、重做日志文件都是以塊（block）的方式進(jìn)行保存的，稱之為重做日志塊（redo log block）,塊的大小是固定的 512 字節(jié)。我們的 redo log 它是固定大小的，可以看作是一個(gè)邏輯上的 log group，由一定數(shù)量的log block 組成。

它的寫入方式是從頭到尾開始寫，寫到末尾又回到開頭循環(huán)寫。

其中有兩個(gè)標(biāo)記位置：

write pos是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭。checkpoint是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到磁盤。

當(dāng)write_pos追上checkpoint時(shí)，表示 redo log 日志已經(jīng)寫滿。這時(shí)候就不能接著往里寫數(shù)據(jù)了，需要執(zhí)行checkpoint規(guī)則騰出可寫空間。

所謂的checkpoint 規(guī)則，就是 checkpoint 觸發(fā)后，將 buffer 中日志頁都刷到磁盤。

SQL 優(yōu)化

24.慢 SQL 如何定位呢？

慢 SQL 的監(jiān)控主要通過兩個(gè)途徑：

• 慢查詢?nèi)罩?/strong>：開啟 MySQL 的慢查詢?nèi)罩荆偻ㄟ^一些工具比如 mysqldumpslow 去分析對(duì)應(yīng)的慢查詢?nèi)罩?，?dāng)然現(xiàn)在一般的云廠商都提供了可視化的平臺(tái)。
• 服務(wù)監(jiān)控：可以在業(yè)務(wù)的基建中加入對(duì)慢 SQL 的監(jiān)控，常見的方案有字節(jié)碼插樁、連接池?cái)U(kuò)展、ORM 框架過程，對(duì)服務(wù)運(yùn)行中的慢 SQL 進(jìn)行監(jiān)控和告警。

25.有哪些方式優(yōu)化慢 SQL？

慢 SQL 的優(yōu)化，主要從兩個(gè)方面考慮，SQL 語句本身的優(yōu)化，以及數(shù)據(jù)庫設(shè)計(jì)的優(yōu)化。

避免不必要的列

這個(gè)是老生常談，但還是經(jīng)常會(huì)出的情況，SQL 查詢的時(shí)候，應(yīng)該只查詢需要的列，而不要包含額外的列，像slect * 這種寫法應(yīng)該盡量避免。

分頁優(yōu)化

在數(shù)據(jù)量比較大，分頁比較深的情況下，需要考慮分頁的優(yōu)化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

優(yōu)化方案：

• 延遲關(guān)聯(lián)

先通過 where 條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過主鍵 id 提取數(shù)據(jù)行，而不是通過原來的二級(jí)索引提取數(shù)據(jù)行

例如：

select a.* from table a, 
 (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
 where a.id = b.id

• 書簽方式

書簽方式就是找到 limit 第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值，根據(jù)這個(gè)主鍵值再去過濾并 limit

例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引優(yōu)化

合理地設(shè)計(jì)和使用索引，是優(yōu)化慢 SQL 的利器。

利用覆蓋索引

InnoDB 使用非主鍵索引查詢數(shù)據(jù)時(shí)會(huì)回表，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引

例如對(duì)于如下查詢：

select name from test where city='上海'

我們將被查詢的字段建立到聯(lián)合索引中，這樣查詢結(jié)果就可以直接從索引中獲取

alter table test add index idx_city_name (city, name);

低版本避免使用 or 查詢

在 MySQL 5.0 之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來替代，因?yàn)樵缙诘?MySQL 版本使用 or 查詢可能會(huì)導(dǎo)致索引失效，高版本引入了索引合并，解決了這個(gè)問題。

避免使用 != 或者 <> 操作符

SQL 中，不等于操作符會(huì)導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描，即使比較的字段上有索引

解決方法：通過把不等于操作符改成 or，可以使用索引，避免全表掃描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

適當(dāng)使用前綴索引

適當(dāng)?shù)厥褂们熬Y所云，可以降低索引的空間占用，提高索引的查詢效率。

比如，郵箱的后綴都是固定的“@xxx.com”，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引

alter table test add index index2(email(6));

PS:需要注意的是，前綴索引也存在缺點(diǎn)，MySQL 無法利用前綴索引做 order by 和 group by 操作，也無法作為覆蓋索引

避免列上函數(shù)運(yùn)算

要避免在列字段上進(jìn)行算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則可能會(huì)導(dǎo)致存儲(chǔ)引擎無法正確使用索引，從而影響了查詢的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正確使用聯(lián)合索引

使用聯(lián)合索引的時(shí)候，注意最左匹配原則。

JOIN 優(yōu)化

優(yōu)化子查詢

盡量使用 Join 語句來替代子查詢，因?yàn)樽硬樵兪乔短撞樵?，而嵌套查詢?huì)新創(chuàng)建一張臨時(shí)表，而臨時(shí)表的創(chuàng)建與銷毀會(huì)占用一定的系統(tǒng)資源以及花費(fèi)一定的時(shí)間，同時(shí)對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響更大

小表驅(qū)動(dòng)大表

關(guān)聯(lián)查詢的時(shí)候要拿小表去驅(qū)動(dòng)大表，因?yàn)殛P(guān)聯(lián)的時(shí)候，MySQL 內(nèi)部會(huì)遍歷驅(qū)動(dòng)表，再去連接被驅(qū)動(dòng)表。

比如 left join，左表就是驅(qū)動(dòng)表，A 表小于 B 表，建立連接的次數(shù)就少，查詢速度就被加快了。

 select name from A left join B ;

適當(dāng)增加冗余字段

增加冗余字段可以減少大量的連表查詢，因?yàn)槎鄰埍淼倪B表查詢性能很低，所有可以適當(dāng)?shù)脑黾尤哂嘧侄?，以減少多張表的關(guān)聯(lián)查詢，這是以空間換時(shí)間的優(yōu)化策略

避免使用 JOIN 關(guān)聯(lián)太多的表

《阿里巴巴 Java 開發(fā)手冊(cè)》規(guī)定不要 join 超過三張表，第一 join 太多降低查詢的速度，第二 join 的 buffer 會(huì)占用更多的內(nèi)存。

如果不可避免要 join 多張表，可以考慮使用數(shù)據(jù)異構(gòu)的方式異構(gòu)到 ES 中查詢。

排序優(yōu)化

利用索引掃描做排序

MySQL 有兩種方式生成有序結(jié)果：其一是對(duì)結(jié)果集進(jìn)行排序的操作，其二是按照索引順序掃描得出的結(jié)果自然是有序的

但是如果索引不能覆蓋查詢所需列，就不得不每掃描一條記錄回表查詢一次，這個(gè)讀操作是隨機(jī) IO，通常會(huì)比順序全表掃描還慢

因此，在設(shè)計(jì)索引時(shí)，盡可能使用同一個(gè)索引既滿足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有當(dāng)索引的列順序和 ORDER BY 子句的順序完全一致，并且所有列的排序方向都一樣時(shí)，才能夠使用索引來對(duì)結(jié)果做排序

UNION 優(yōu)化

條件下推

MySQL 處理 union 的策略是先創(chuàng)建臨時(shí)表，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來做查詢，很多優(yōu)化策略在 union 查詢中都會(huì)失效，因?yàn)樗鼰o法利用索引

最好手工將 where、limit 等子句下推到 union 的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化

此外，除非確實(shí)需要服務(wù)器去重，一定要使用 union all，如果不加 all 關(guān)鍵字，MySQL 會(huì)給臨時(shí)表加上 distinct 選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表做唯一性檢查，代價(jià)很高。

26.怎么看執(zhí)行計(jì)劃（explain），如何理解其中各個(gè)字段的含義？

explain 是 sql 優(yōu)化的利器，除了優(yōu)化慢 sql，平時(shí)的 sql 編寫，也應(yīng)該先 explain，查看一下執(zhí)行計(jì)劃，看看是否還有優(yōu)化的空間。

直接在 select 語句之前增加explain 關(guān)鍵字，就會(huì)返回執(zhí)行計(jì)劃的信息。

1. id 列：MySQL 會(huì)為每個(gè) select 語句分配一個(gè)唯一的 id 值
2. select_type 列，查詢的類型，根據(jù)關(guān)聯(lián)、union、子查詢等等分類，常見的查詢類型有 SIMPLE、PRIMARY。
3. table 列：表示 explain 的一行正在訪問哪個(gè)表。
4. type 列：最重要的列之一。表示關(guān)聯(lián)類型或訪問類型，即 MySQL 決定如何查找表中的行。

性能從最優(yōu)到最差分別為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system

system：當(dāng)表僅有一行記錄時(shí)(系統(tǒng)表)，數(shù)據(jù)量很少，往往不需要進(jìn)行磁盤 IO，速度非?？?/p>

• const

const：表示查詢時(shí)命中 primary key 主鍵或者 unique 唯一索引，或者被連接的部分是一個(gè)常量(const)值。這類掃描效率極高，返回?cái)?shù)據(jù)量少，速度非常快。

• eq_ref

eq_ref：查詢時(shí)命中主鍵primary key 或者 unique key索引， type 就是 eq_ref。

• ref_or_null

ref_or_null：這種連接類型類似于 ref，區(qū)別在于 MySQL會(huì)額外搜索包含NULL值的行。

• index_merge

index_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個(gè)以上的索引。

• unique_subquery

unique_subquery：替換下面的 IN子查詢，子查詢返回不重復(fù)的集合。

• index_subquery

index_subquery：區(qū)別于unique_subquery，用于非唯一索引，可以返回重復(fù)值。

• range

range：使用索引選擇行，僅檢索給定范圍內(nèi)的行。簡(jiǎn)單點(diǎn)說就是針對(duì)一個(gè)有索引的字段，給定范圍檢索數(shù)據(jù)。在where語句中使用 bettween...and、<、>、<=、in 等條件查詢 type 都是 range。

• index

index：Index 與ALL 其實(shí)都是讀全表，區(qū)別在于index是遍歷索引樹讀取，而ALL是從硬盤中讀取。

• ALL

就不用多說了，全表掃描。

6. possible_keys 列：顯示查詢可能使用哪些索引來查找，使用索引優(yōu)化 sql 的時(shí)候比較重要。
7. key 列：這一列顯示 mysql 實(shí)際采用哪個(gè)索引來優(yōu)化對(duì)該表的訪問，判斷索引是否失效的時(shí)候常用。
8. key_len 列：顯示了 MySQL 使用
9. ref 列：ref 列展示的就是與索引列作等值匹配的值，常見的有：const（常量），func，NULL，字段名。
10. rows 列：這也是一個(gè)重要的字段，MySQL 查詢優(yōu)化器根據(jù)統(tǒng)計(jì)信息，估算 SQL 要查到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù)，這個(gè)值非常直觀顯示 SQL 的效率好壞，原則上 rows 越少越好。
11. Extra 列：顯示不適合在其它列的額外信息，雖然叫額外，但是也有一些重要的信息：

• Using index：表示 MySQL 將使用覆蓋索引，以避免回表
• Using where：表示會(huì)在存儲(chǔ)引擎檢索之后再進(jìn)行過濾
• Using temporary ：表示對(duì)查詢結(jié)果排序時(shí)會(huì)使用一個(gè)臨時(shí)表。

索引

索引可以說是 MySQL 面試中的重中之重，一定要徹底拿下。

27.能簡(jiǎn)單說一下索引的分類嗎？

從三個(gè)不同維度對(duì)索引分類：

例如從基本使用使用的角度來講：

• 主鍵索引: InnoDB 主鍵是默認(rèn)的索引，數(shù)據(jù)列不允許重復(fù)，不允許為 NULL，一個(gè)表只能有一個(gè)主鍵。
• 唯一索引: 數(shù)據(jù)列不允許重復(fù)，允許為 NULL 值，一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。
• 普通索引: 基本的索引類型，沒有唯一性的限制，允許為 NULL 值。
• 組合索引：多列值組成一個(gè)索引，用于組合搜索，效率大于索引合并

28.為什么使用索引會(huì)加快查詢？

傳統(tǒng)的查詢方法，是按照表的順序遍歷的，不論查詢幾條數(shù)據(jù)，MySQL 需要將表的數(shù)據(jù)從頭到尾遍歷一遍。

在我們添加完索引之后，MySQL 一般通過 BTREE 算法生成一個(gè)索引文件，在查詢數(shù)據(jù)庫時(shí)，找到索引文件進(jìn)行遍歷，在比較小的索引數(shù)據(jù)里查找，然后映射到對(duì)應(yīng)的數(shù)據(jù)，能大幅提升查找的效率。

和我們通過書的目錄，去查找對(duì)應(yīng)的內(nèi)容，一樣的道理。

29.創(chuàng)建索引有哪些注意點(diǎn)？

索引雖然是 sql 性能優(yōu)化的利器，但是索引的維護(hù)也是需要成本的，所以創(chuàng)建索引，也要注意：

1. 索引應(yīng)該建在查詢應(yīng)用頻繁的字段

在用于 where 判斷、 order 排序和 join 的(on)字段上創(chuàng)建索引。

2. 索引的個(gè)數(shù)應(yīng)該適量

索引需要占用空間；更新時(shí)候也需要維護(hù)。

3. 區(qū)分度低的字段，例如性別，不要建索引。

離散度太低的字段，掃描的行數(shù)降低的有限。

4. 頻繁更新的值，不要作為主鍵或者索引

維護(hù)索引文件需要成本；還會(huì)導(dǎo)致頁分裂，IO 次數(shù)增多。

5. 組合索引把散列性高(區(qū)分度高)的值放在前面

為了滿足最左前綴匹配原則

6. 創(chuàng)建組合索引，而不是修改單列索引。

組合索引代替多個(gè)單列索引（對(duì)于單列索引，MySQL 基本只能使用一個(gè)索引，所以經(jīng)常使用多個(gè)條件查詢時(shí)更適合使用組合索引）

7. 過長(zhǎng)的字段，使用前綴索引。當(dāng)字段值比較長(zhǎng)的時(shí)候，建立索引會(huì)消耗很多的空間，搜索起來也會(huì)很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個(gè)就叫前綴索引。
8. 不建議用無序的值(例如身份證、UUID )作為索引

當(dāng)主鍵具有不確定性，會(huì)造成葉子節(jié)點(diǎn)頻繁分裂，出現(xiàn)磁盤存儲(chǔ)的碎片化

30.索引哪些情況下會(huì)失效呢？

• 查詢條件包含 or，可能導(dǎo)致索引失效
• 如果字段類型是字符串，where 時(shí)一定用引號(hào)括起來，否則會(huì)因?yàn)殡[式類型轉(zhuǎn)換，索引失效
• like 通配符可能導(dǎo)致索引失效。
• 聯(lián)合索引，查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列，索引失效。
• 在索引列上使用 mysql 的內(nèi)置函數(shù)，索引失效。
• 對(duì)索引列運(yùn)算（如，+、-、*、/），索引失效。
• 索引字段上使用（！= 或者 < >，not in）時(shí)，可能會(huì)導(dǎo)致索引失效。
• 索引字段上使用 is null， is not null，可能導(dǎo)致索引失效。
• 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣，可能導(dǎo)致索引失效。
• MySQL 優(yōu)化器估計(jì)使用全表掃描要比使用索引快,則不使用索引。

31.索引不適合哪些場(chǎng)景呢？

• 數(shù)據(jù)量比較少的表不適合加索引
• 更新比較頻繁的字段也不適合加索引
• 離散低的字段不適合加索引（如性別）

32.索引是不是建的越多越好呢？

當(dāng)然不是。

• 索引會(huì)占據(jù)磁盤空間
• 索引雖然會(huì)提高查詢效率，但是會(huì)降低更新表的效率。比如每次對(duì)表進(jìn)行增刪改操作，MySQL 不僅要保存數(shù)據(jù)，還有保存或者更新對(duì)應(yīng)的索引文件。

33.MySQL 索引用的什么數(shù)據(jù)結(jié)構(gòu)了解嗎？

MySQL 的默認(rèn)存儲(chǔ)引擎是 InnoDB，它采用的是 B+樹結(jié)構(gòu)的索引。

• B+樹：只有葉子節(jié)點(diǎn)才會(huì)存儲(chǔ)數(shù)據(jù)，非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值。葉子節(jié)點(diǎn)之間使用雙向指針連接，最底層的葉子節(jié)點(diǎn)形成了一個(gè)雙向有序鏈表。

在這張圖里，有兩個(gè)重點(diǎn)：

• 最外面的方塊，的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（粉色所示）和指針（黃色/灰色所示），如根節(jié)點(diǎn)磁盤包含數(shù)據(jù)項(xiàng) 17 和 35，包含指針 P1、P2、P3，P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即 3、4、5……、65。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如 17、35 并不真實(shí)存在于數(shù)據(jù)表中。
• 葉子節(jié)點(diǎn)之間使用雙向指針連接，最底層的葉子節(jié)點(diǎn)形成了一個(gè)雙向有序鏈表，可以進(jìn)行范圍查詢。

34.那一棵 B+樹能存儲(chǔ)多少條數(shù)據(jù)呢？

假設(shè)索引字段是 bigint 類型，長(zhǎng)度為 8 字節(jié)。指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)，這樣一共 14 字節(jié)。非葉子節(jié)點(diǎn)(一頁)可以存儲(chǔ) 16384/14=1170 個(gè)這樣的 單元(鍵值+指針)，代表有 1170 個(gè)指針。

樹深度為 2 的時(shí)候，有 1170^2 個(gè)葉子節(jié)點(diǎn)，可以存儲(chǔ)的數(shù)據(jù)為 1170*1170*16=21902400。

在查找數(shù)據(jù)時(shí)一次頁的查找代表一次 IO，也就是說，一張 2000 萬左右的表，查詢數(shù)據(jù)最多需要訪問 3 次磁盤。

所以在 InnoDB 中 B+ 樹深度一般為 1-3 層，它就能滿足千萬級(jí)的數(shù)據(jù)存儲(chǔ)。

35.為什么要用 B+ 樹，而不用普通二叉樹？

可以從幾個(gè)維度去看這個(gè)問題，查詢是否夠快，效率是否穩(wěn)定，存儲(chǔ)數(shù)據(jù)多少，以及查找磁盤次數(shù)。

為什么不用普通二叉樹？

普通二叉樹存在退化的情況，如果它退化成鏈表，相當(dāng)于全表掃描。平衡二叉樹相比于二叉查找樹來說，查找效率更穩(wěn)定，總體的查找速度也更快。

為什么不用平衡二叉樹呢？

讀取數(shù)據(jù)的時(shí)候，是從磁盤讀到內(nèi)存。如果樹這種數(shù)據(jù)結(jié)構(gòu)作為索引，那每查找一次數(shù)據(jù)就需要從磁盤中讀取一個(gè)節(jié)點(diǎn)，也就是一個(gè)磁盤塊，但是平衡二叉樹可是每個(gè)節(jié)點(diǎn)只存儲(chǔ)一個(gè)鍵值和數(shù)據(jù)的，如果是 B+ 樹，可以存儲(chǔ)更多的節(jié)點(diǎn)數(shù)據(jù)，樹的高度也會(huì)降低，因此讀取磁盤的次數(shù)就降下來啦，查詢效率就快。

36.為什么用 B+ 樹而不用 B 樹呢？

B+相比較 B 樹，有這些優(yōu)勢(shì)：

• 它是 B Tree 的變種，B Tree 能解決的問題，它都能解決。

B Tree 解決的兩大問題：每個(gè)節(jié)點(diǎn)存儲(chǔ)更多關(guān)鍵字；路數(shù)更多

• 掃庫、掃表能力更強(qiáng)

如果我們要對(duì)表進(jìn)行全表掃描，只需要遍歷葉子節(jié)點(diǎn)就可以 了，不需要遍歷整棵 B+Tree 拿到所有的數(shù)據(jù)。

• B+Tree 的磁盤讀寫能力相對(duì)于 B Tree 來說更強(qiáng)，IO 次數(shù)更少

根節(jié)點(diǎn)和枝節(jié)點(diǎn)不保存數(shù)據(jù)區(qū)， 所以一個(gè)節(jié)點(diǎn)可以保存更多的關(guān)鍵字，一次磁盤加載的關(guān)鍵字更多，IO 次數(shù)更少。

• 排序能力更強(qiáng)

因?yàn)槿~子節(jié)點(diǎn)上有下一個(gè)數(shù)據(jù)區(qū)的指針，數(shù)據(jù)形成了鏈表。

• 效率更加穩(wěn)定

B+Tree 永遠(yuǎn)是在葉子節(jié)點(diǎn)拿到數(shù)據(jù)，所以 IO 次數(shù)是穩(wěn)定的。

37.Hash 索引和 B+ 樹索引區(qū)別是什么？

• B+ 樹可以進(jìn)行范圍查詢，Hash 索引不能。
• B+ 樹支持聯(lián)合索引的最左側(cè)原則，Hash 索引不支持。
• B+ 樹支持 order by 排序，Hash 索引不支持。
• Hash 索引在等值查詢上比 B+ 樹效率更高。
• B+ 樹使用 like 進(jìn)行模糊查詢的時(shí)候，like 后面（比如 % 開頭）的話可以起到優(yōu)化的作用，Hash 索引根本無法進(jìn)行模糊查詢。

38.聚簇索引與非聚簇索引的區(qū)別？

首先理解聚簇索引不是一種新的索引，而是而是一種數(shù)據(jù)存儲(chǔ)方式。聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。我們熟悉的兩種存儲(chǔ)引擎——MyISAM 采用的是非聚簇索引，InnoDB 采用的是聚簇索引。

可以這么說：

• 索引的數(shù)據(jù)結(jié)構(gòu)是樹，聚簇索引的索引和數(shù)據(jù)存儲(chǔ)在一棵樹上，樹的葉子節(jié)點(diǎn)就是數(shù)據(jù)，非聚簇索引索引和數(shù)據(jù)不在一棵樹上。

• 一個(gè)表中只能擁有一個(gè)聚簇索引，而非聚簇索引一個(gè)表可以存在多個(gè)。
• 聚簇索引，索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序；索引，索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同。
• 聚簇索引：物理存儲(chǔ)按照索引排序；非聚集索引：物理存儲(chǔ)不按照索引排序；

39.回表了解嗎？

在 InnoDB 存儲(chǔ)引擎里，利用輔助索引查詢，先通過輔助索引找到主鍵索引的鍵值，再通過主鍵值查出主鍵索引里面沒有符合要求的數(shù)據(jù)，它比基于主鍵索引的查詢多掃描了一棵索引樹，這個(gè)過程就叫回表。

例如:select \* from user where name = ‘張三’;

40.覆蓋索引了解嗎？

在輔助索引里面，不管是單列索引還是聯(lián)合索引，如果 select 的數(shù)據(jù)列只用輔助索引中就能夠取得，不用去查主鍵索引，這時(shí)候使用的索引就叫做覆蓋索引，避免了回表。

比如，select name from user where name = ‘張三’;

41.什么是最左前綴原則/最左匹配原則？

注意：最左前綴原則、最左匹配原則、最左前綴匹配原則這三個(gè)都是一個(gè)概念。

最左匹配原則：在 InnoDB 的聯(lián)合索引中，查詢的時(shí)候只有匹配了前一個(gè)/左邊的值之后，才能匹配下一個(gè)。

根據(jù)最左匹配原則，我們創(chuàng)建了一個(gè)組合索引，如 (a1,a2,a3)，相當(dāng)于創(chuàng)建了（a1）、(a1,a2)和 (a1,a2,a3) 三個(gè)索引。

為什么不從最左開始查，就無法匹配呢？

比如有一個(gè) user 表，我們給 name 和 age 建立了一個(gè)組合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

組合索引在 B+Tree 中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，它是按照從左到右的順序來建立搜索樹的 (name 在左邊，age 在右邊)。

從這張圖可以看出來，name 是有序的，age 是無序的。當(dāng) name 相等的時(shí)候， age 才是有序的。

這個(gè)時(shí)候我們使用 where name= ‘張三‘ and age = ‘20 ‘去查詢數(shù)據(jù)的時(shí)候， B+Tree 會(huì)優(yōu)先比較 name 來確定下一步應(yīng)該搜索的方向，往左還是往右。如果 name 相同的時(shí)候再比較 age。但是如果查詢條件沒有 name，就不知道下一步應(yīng)該查哪個(gè) 節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候 name 是第一個(gè)比較因子，所以就沒用上索引。

42.什么是索引下推優(yōu)化？

索引條件下推優(yōu)化（Index Condition Pushdown (ICP) ）是 MySQL5.6 添加的，用于優(yōu)化數(shù)據(jù)查詢。

• 不使用索引條件下推優(yōu)化時(shí)存儲(chǔ)引擎通過索引檢索到數(shù)據(jù)，然后返回給 MySQL Server，MySQL Server 進(jìn)行過濾條件的判斷。
• 當(dāng)使用索引條件下推優(yōu)化時(shí)，如果存在某些被索引的列的判斷條件時(shí)，MySQL Server 將這一部分判斷條件下推給存儲(chǔ)引擎，然后由存儲(chǔ)引擎通過判斷索引是否符合 MySQL Server 傳遞的條件，只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來返回給 MySQL 服務(wù)器。

例如一張表，建了一個(gè)聯(lián)合索引（name, age），查詢語句：select * from t_user where name like '張%' and age=10;，由于name使用了范圍查詢，根據(jù)最左匹配原則：

不使用 ICP，引擎層查找到name like '張%'的數(shù)據(jù)，再由 Server 層去過濾age=10這個(gè)條件，這樣一來，就回表了兩次，浪費(fèi)了聯(lián)合索引的另外一個(gè)字段age。

但是，使用了索引下推優(yōu)化，把 where 的條件放到了引擎層執(zhí)行，直接根據(jù)name like '張%' and age=10的條件進(jìn)行過濾，減少了回表的次數(shù)。

索引條件下推優(yōu)化可以減少存儲(chǔ)引擎查詢基礎(chǔ)表的次數(shù)，也可以減少 MySQL 服務(wù)器從存儲(chǔ)引擎接收數(shù)據(jù)的次數(shù)。

鎖

43.MySQL 中有哪幾種鎖，列舉一下？

如果按鎖粒度劃分，有以下 3 種：

• 表鎖：開銷小，加鎖快；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低;不會(huì)出現(xiàn)死鎖。
• 行鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高。
• 頁鎖：開銷和加鎖速度介于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

如果按照兼容性，有兩種，

• 共享鎖（S Lock）,也叫讀鎖（read lock），相互不阻塞。
• 排他鎖（X Lock），也叫寫鎖（write lock），排它鎖是阻塞的，在一定時(shí)間內(nèi)，只有一個(gè)請(qǐng)求能執(zhí)行寫入，并阻止其它鎖讀取正在寫入的數(shù)據(jù)。

44.說說 InnoDB 里的行鎖實(shí)現(xiàn)?

我們拿這么一個(gè)用戶表來表示行級(jí)鎖，其中插入了 4 行數(shù)據(jù)，主鍵值分別是 1,6,8,12，現(xiàn)在簡(jiǎn)化它的聚簇索引結(jié)構(gòu)，只保留數(shù)據(jù)記錄。

InnoDB 的行鎖的主要實(shí)現(xiàn)如下：

• Record Lock 記錄鎖

記錄鎖就是直接鎖定某行記錄。當(dāng)我們使用唯一性的索引(包括唯一索引和聚簇索引)進(jìn)行等值查詢且精準(zhǔn)匹配到一條記錄時(shí)，此時(shí)就會(huì)直接將這條記錄鎖定。例如select * from t where id =6 for update;就會(huì)將id=6的記錄鎖定。

• Gap Lock 間隙鎖

間隙鎖(Gap Locks) 的間隙指的是兩個(gè)記錄之間邏輯上尚未填入數(shù)據(jù)的部分,是一個(gè)左開右開空間。

間隙鎖就是鎖定某些間隙區(qū)間的。當(dāng)我們使用用等值查詢或者范圍查詢，并且沒有命中任何一個(gè)record，此時(shí)就會(huì)將對(duì)應(yīng)的間隙區(qū)間鎖定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就會(huì)將(1,6)區(qū)間鎖定。

• Next-key Lock 臨鍵鎖

臨鍵指的是間隙加上它右邊的記錄組成的左開右閉區(qū)間。比如上述的(1,6]、(6,8]等。

臨鍵鎖就是記錄鎖(Record Locks)和間隙鎖(Gap Locks)的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。當(dāng)我們使用范圍查詢，并且命中了部分record記錄，此時(shí)鎖住的就是臨鍵區(qū)間。注意，臨鍵鎖鎖住的區(qū)間會(huì)包含最后一個(gè) record 的右邊的臨鍵區(qū)間。例如select * from t where id > 5 and id <= 7 for update;會(huì)鎖住(4,7]、(7,+∞)。mysql 默認(rèn)行鎖類型就是臨鍵鎖(Next-Key Locks)。當(dāng)使用唯一性索引，等值查詢匹配到一條記錄的時(shí)候，臨鍵鎖(Next-Key Locks)會(huì)退化成記錄鎖；沒有匹配到任何記錄的時(shí)候，退化成間隙鎖。

間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都是用來解決幻讀問題的，在已提交讀（READ COMMITTED）隔離級(jí)別下，間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都會(huì)失效！

上面是行鎖的三種實(shí)現(xiàn)算法，除此之外，在行上還存在插入意向鎖。

• Insert Intention Lock 插入意向鎖

一個(gè)事務(wù)在插入一條記錄時(shí)需要判斷一下插入位置是不是被別的事務(wù)加了意向鎖 ，如果有的話，插入操作需要等待，直到擁有 gap 鎖 的那個(gè)事務(wù)提交。但是事務(wù)在等待的時(shí)候也需要在內(nèi)存中生成一個(gè) 鎖結(jié)構(gòu) ，表明有事務(wù)想在某個(gè) 間隙 中插入新記錄，但是現(xiàn)在在等待。這種類型的鎖命名為 Insert Intention Locks ，也就是插入意向鎖 。

假如我們有個(gè) T1 事務(wù)，給(1,6)區(qū)間加上了意向鎖，現(xiàn)在有個(gè) T2 事務(wù)，要插入一個(gè)數(shù)據(jù)，id 為 4，它會(huì)獲取一個(gè)（1,6）區(qū)間的插入意向鎖，又有有個(gè) T3 事務(wù)，想要插入一個(gè)數(shù)據(jù)，id 為 3，它也會(huì)獲取一個(gè)（1,6）區(qū)間的插入意向鎖，但是，這兩個(gè)插入意向鎖鎖不會(huì)互斥。

45.意向鎖是什么知道嗎？

意向鎖是一個(gè)表級(jí)鎖，不要和插入意向鎖搞混。

意向鎖的出現(xiàn)是為了支持 InnoDB 的多粒度鎖，它解決的是表鎖和行鎖共存的問題。

當(dāng)我們需要給一個(gè)表加表鎖的時(shí)候，我們需要根據(jù)去判斷表中有沒有數(shù)據(jù)行被鎖定，以確定是否能加成功。

假如沒有意向鎖，那么我們就得遍歷表中所有數(shù)據(jù)行來判斷有沒有行鎖；

有了意向鎖這個(gè)表級(jí)鎖之后，則我們直接判斷一次就知道表中是否有數(shù)據(jù)行被鎖定了。

有了意向鎖之后，要執(zhí)行的事務(wù) A 在申請(qǐng)行鎖（寫鎖）之前，數(shù)據(jù)庫會(huì)自動(dòng)先給事務(wù) A 申請(qǐng)表的意向排他鎖。當(dāng)事務(wù) B 去申請(qǐng)表的互斥鎖時(shí)就會(huì)失敗，因?yàn)楸砩嫌幸庀蚺潘i之后事務(wù) B 申請(qǐng)表的互斥鎖時(shí)會(huì)被阻塞。

46.MySQL 的樂觀鎖和悲觀鎖了解嗎？

• 悲觀鎖（Pessimistic Concurrency Control）：

悲觀鎖認(rèn)為被它保護(hù)的數(shù)據(jù)是極其不安全的，每時(shí)每刻都有可能被改動(dòng)，一個(gè)事務(wù)拿到悲觀鎖后，其他任何事務(wù)都不能對(duì)該數(shù)據(jù)進(jìn)行修改，只能等待鎖被釋放才可以執(zhí)行。

數(shù)據(jù)庫中的行鎖，表鎖，讀鎖，寫鎖均為悲觀鎖。

• 樂觀鎖（Optimistic Concurrency Control）

樂觀鎖認(rèn)為數(shù)據(jù)的變動(dòng)不會(huì)太頻繁。

樂觀鎖通常是通過在表中增加一個(gè)版本(version)或時(shí)間戳(timestamp)來實(shí)現(xiàn)，其中，版本最為常用。

事務(wù)在從數(shù)據(jù)庫中取數(shù)據(jù)時(shí)，會(huì)將該數(shù)據(jù)的版本也取出來(v1)，當(dāng)事務(wù)對(duì)數(shù)據(jù)變動(dòng)完畢想要將其更新到表中時(shí)，會(huì)將之前取出的版本 v1 與數(shù)據(jù)中最新的版本 v2 相對(duì)比，如果 v1=v2，那么說明在數(shù)據(jù)變動(dòng)期間，沒有其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改，此時(shí)，就允許事務(wù)對(duì)表中的數(shù)據(jù)進(jìn)行修改，并且修改時(shí) version 會(huì)加 1，以此來表明數(shù)據(jù)已被變動(dòng)。

如果，v1 不等于 v2，那么說明數(shù)據(jù)變動(dòng)期間，數(shù)據(jù)被其他事務(wù)改動(dòng)了，此時(shí)不允許數(shù)據(jù)更新到表中，一般的處理辦法是通知用戶讓其重新操作。不同于悲觀鎖，樂觀鎖通常是由開發(fā)者實(shí)現(xiàn)的。

47.MySQL 遇到過死鎖問題嗎，你是如何解決的？

排查死鎖的一般步驟是這樣的：

（1）查看死鎖日志 show engine innodb status;

（2）找出死鎖 sql

（3）分析 sql 加鎖情況

（4）模擬死鎖案發(fā)

（5）分析死鎖日志

（6）分析死鎖結(jié)果

當(dāng)然，這只是一個(gè)簡(jiǎn)單的流程說明，實(shí)際上生產(chǎn)中的死鎖千奇百怪，排查和解決起來沒那么簡(jiǎn)單。

事務(wù)

48.MySQL 事務(wù)的四大特性說一下？

• 原子性：事務(wù)作為一個(gè)整體被執(zhí)行，包含在其中的對(duì)數(shù)據(jù)庫的操作要么全部被執(zhí)行，要么都不執(zhí)行。
• 一致性：指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)不會(huì)被破壞，假如 A 賬戶給 B 賬戶轉(zhuǎn) 10 塊錢，不管成功與否，A 和 B 的總金額是不變的。
• 隔離性：多個(gè)事務(wù)并發(fā)訪問時(shí)，事務(wù)之間是相互隔離的，即一個(gè)事務(wù)不影響其它事務(wù)運(yùn)行效果。簡(jiǎn)言之，就是事務(wù)之間是進(jìn)水不犯河水的。
• 持久性：表示事務(wù)完成以后，該事務(wù)對(duì)數(shù)據(jù)庫所作的操作更改，將持久地保存在數(shù)據(jù)庫之中。

49.那 ACID 靠什么保證的呢？

• 事務(wù)的隔離性是通過數(shù)據(jù)庫鎖的機(jī)制實(shí)現(xiàn)的。
• 事務(wù)的一致性由 undo log 來保證：undo log 是邏輯日志，記錄了事務(wù)的 insert、update、deltete 操作，回滾的時(shí)候做相反的 delete、update、insert 操作來恢復(fù)數(shù)據(jù)。
• 事務(wù)的原子性和持久性由 redo log 來保證：redolog 被稱作重做日志，是物理日志，事務(wù)提交的時(shí)候，必須先將事務(wù)的所有日志寫入 redo log 持久化，到事務(wù)的提交操作才算完成。

50.事務(wù)的隔離級(jí)別有哪些？MySQL 的默認(rèn)隔離級(jí)別是什么？

事務(wù)的四個(gè)隔離級(jí)別

• 讀未提交（Read Uncommitted）
• 讀已提交（Read Committed）
• 可重復(fù)讀（Repeatable Read）
• 串行化（Serializable）

MySQL 默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀 (Repeatable Read)。

51.什么是幻讀，臟讀，不可重復(fù)讀呢？

• 事務(wù) A、B 交替執(zhí)行，事務(wù) A 讀取到事務(wù) B 未提交的數(shù)據(jù)，這就是臟讀。
• 在一個(gè)事務(wù)范圍內(nèi)，兩個(gè)相同的查詢，讀取同一條記錄，卻返回了不同的數(shù)據(jù)，這就是不可重復(fù)讀。
• 事務(wù) A 查詢一個(gè)范圍的結(jié)果集，另一個(gè)并發(fā)事務(wù) B 往這個(gè)范圍中插入 / 刪除了數(shù)據(jù)，并靜悄悄地提交，然后事務(wù) A 再次查詢相同的范圍，兩次讀取得到的結(jié)果集不一樣了，這就是幻讀。

不同的隔離級(jí)別，在并發(fā)事務(wù)下可能會(huì)發(fā)生的問題：

52.事務(wù)的各個(gè)隔離級(jí)別都是如何實(shí)現(xiàn)的？

讀未提交

讀未提交，就不用多說了，采取的是讀不加鎖原理。

• 事務(wù)讀不加鎖，不阻塞其他事務(wù)的讀和寫
• 事務(wù)寫阻塞其他事務(wù)寫，但不阻塞其他事務(wù)讀；

讀取已提交&可重復(fù)讀

讀取已提交和可重復(fù)讀級(jí)別利用了ReadView和MVCC，也就是每個(gè)事務(wù)只能讀取它能看到的版本（ReadView）。

• READ COMMITTED：每次讀取數(shù)據(jù)前都生成一個(gè) ReadView
• REPEATABLE READ ：在第一次讀取數(shù)據(jù)時(shí)生成一個(gè) ReadView

串行化

串行化的實(shí)現(xiàn)采用的是讀寫都加鎖的原理。

串行化的情況下，對(duì)于同一行事務(wù)，寫會(huì)加寫鎖，讀會(huì)加讀鎖。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行。

53.MVCC 了解嗎？怎么實(shí)現(xiàn)的？

MVCC(Multi Version Concurrency Control)，中文名是多版本并發(fā)控制，簡(jiǎn)單來說就是通過維護(hù)數(shù)據(jù)歷史版本，從而解決并發(fā)訪問情況下的讀一致性問題。關(guān)于它的實(shí)現(xiàn)，要抓住幾個(gè)關(guān)鍵點(diǎn)，隱式字段、undo 日志、版本鏈、快照讀&當(dāng)前讀、Read View。

版本鏈

對(duì)于 InnoDB 存儲(chǔ)引擎，每一行記錄都有兩個(gè)隱藏列DB_TRX_ID、DB_ROLL_PTR

• DB_TRX_ID，事務(wù) ID，每次修改時(shí)，都會(huì)把該事務(wù) ID 復(fù)制給DB_TRX_ID；
• DB_ROLL_PTR，回滾指針，指向回滾段的 undo 日志。

假如有一張user表，表中只有一行記錄，當(dāng)時(shí)插入的事務(wù) id 為 80。此時(shí)，該條記錄的示例圖如下：

接下來有兩個(gè)DB_TRX_ID分別為100、200的事務(wù)對(duì)這條記錄進(jìn)行update操作，整個(gè)過程如下：

由于每次變動(dòng)都會(huì)先把undo日志記錄下來，并用DB_ROLL_PTR指向undo日志地址。因此可以認(rèn)為，對(duì)該條記錄的修改日志串聯(lián)起來就形成了一個(gè)版本鏈，版本鏈的頭節(jié)點(diǎn)就是當(dāng)前記錄最新的值。如下：

ReadView

對(duì)于Read Committed和Repeatable Read隔離級(jí)別來說，都需要讀取已經(jīng)提交的事務(wù)所修改的記錄，也就是說如果版本鏈中某個(gè)版本的修改沒有提交，那么該版本的記錄時(shí)不能被讀取的。所以需要確定在Read Committed和Repeatable Read隔離級(jí)別下，版本鏈中哪個(gè)版本是能被當(dāng)前事務(wù)讀取的。于是就引入了ReadView這個(gè)概念來解決這個(gè)問題。

Read View 就是事務(wù)執(zhí)行快照讀時(shí)，產(chǎn)生的讀視圖，相當(dāng)于某時(shí)刻表記錄的一個(gè)快照，通過這個(gè)快照，我們可以獲取：

• m_ids ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫事務(wù)的事務(wù) id 列表。
• min_trx_id ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫事務(wù)中最小的 事務(wù) id ，也就是 m_ids 中的最小值。
• max_trx_id ：表示生成 ReadView 時(shí)系統(tǒng)中應(yīng)該分配給下一個(gè)事務(wù)的 id 值。
• creator_trx_id ：表示生成該 ReadView 的事務(wù)的 事務(wù) id

有了這個(gè) ReadView ，這樣在訪問某條記錄時(shí)，只需要按照下邊的步驟判斷記錄的某個(gè)版本是否可見：

• 如果被訪問版本的 DB_TRX_ID 屬性值與 ReadView 中的 creator_trx_id 值相同，意味著當(dāng)前事務(wù)在訪問它自己修改過的記錄，所以該版本可以被當(dāng)前事務(wù)訪問。
• 如果被訪問版本的 DB_TRX_ID 屬性值小于 ReadView 中的 min_trx_id 值，表明生成該版本的事務(wù)在當(dāng)前事務(wù)生成 ReadView 前已經(jīng)提交，所以該版本可以被當(dāng)前事務(wù)訪問。
• 如果被訪問版本的 DB_TRX_ID 屬性值大于 ReadView 中的 max_trx_id 值，表明生成該版本的事務(wù)在當(dāng)前事務(wù)生成 ReadView 后才開啟，所以該版本不可以被當(dāng)前事務(wù)訪問。
• 如果被訪問版本的 DB_TRX_ID 屬性值在 ReadView 的 min_trx_id 和 max_trx_id 之間，那就需要判斷一下 trx_id 屬性值是不是在 m_ids 列表中，如果在，說明創(chuàng)建 ReadView 時(shí)生成該版本的事務(wù)還是活躍的，該版本不可以被訪問；如果不在，說明創(chuàng)建 ReadView 時(shí)生成該版本的事務(wù)已經(jīng)被提交，該版本可以被訪問。

如果某個(gè)版本的數(shù)據(jù)對(duì)當(dāng)前事務(wù)不可見的話，那就順著版本鏈找到下一個(gè)版本的數(shù)據(jù)，繼續(xù)按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最后一個(gè)版本。如果最后一個(gè)版本也不可見的話，那么就意味著該條記錄對(duì)該事務(wù)完全不可見，查詢結(jié)果就不包含該記錄。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔離級(jí)別的的一個(gè)非常大的區(qū)別就是它們生成 ReadView 的時(shí)機(jī)不同。

READ COMMITTED 是每次讀取數(shù)據(jù)前都生成一個(gè) ReadView，這樣就能保證自己每次都能讀到其它事務(wù)提交的數(shù)據(jù)；REPEATABLE READ 是在第一次讀取數(shù)據(jù)時(shí)生成一個(gè) ReadView，這樣就能保證后續(xù)讀取的結(jié)果完全一致。

高可用/性能

54.數(shù)據(jù)庫讀寫分離了解嗎？

讀寫分離的基本原理是將數(shù)據(jù)庫讀寫操作分散到不同的節(jié)點(diǎn)上，下面是基本架構(gòu)圖：

讀寫分離的基本實(shí)現(xiàn)是:

• 數(shù)據(jù)庫服務(wù)器搭建主從集群，一主一從、一主多從都可以。
• 數(shù)據(jù)庫主機(jī)負(fù)責(zé)讀寫操作，從機(jī)只負(fù)責(zé)讀操作。
• 數(shù)據(jù)庫主機(jī)通過復(fù)制將數(shù)據(jù)同步到從機(jī)，每臺(tái)數(shù)據(jù)庫服務(wù)器都存儲(chǔ)了所有的業(yè)務(wù)數(shù)據(jù)。
• 業(yè)務(wù)服務(wù)器將寫操作發(fā)給數(shù)據(jù)庫主機(jī)，將讀操作發(fā)給數(shù)據(jù)庫從機(jī)。

55.那讀寫分離的分配怎么實(shí)現(xiàn)呢？

將讀寫操作區(qū)分開來，然后訪問不同的數(shù)據(jù)庫服務(wù)器，一般有兩種方式：程序代碼封裝和中間件封裝。

1. 程序代碼封裝

程序代碼封裝指在代碼中抽象一個(gè)數(shù)據(jù)訪問層（所以有的文章也稱這種方式為 "中間層封裝" ） ，實(shí)現(xiàn)讀寫操作分離和數(shù)據(jù)庫服務(wù)器連接的管理。例如，基于 Hibernate 進(jìn)行簡(jiǎn)單封裝，就可以實(shí)現(xiàn)讀寫分離：

目前開源的實(shí)現(xiàn)方案中，淘寶的 TDDL (Taobao Distributed Data Layer, 外號(hào)：頭都大了）是比較有名的。

2. 中間件封裝

中間件封裝指的是獨(dú)立一套系統(tǒng)出來，實(shí)現(xiàn)讀寫操作分離和數(shù)據(jù)庫服務(wù)器連接的管理。中間件對(duì)業(yè)務(wù)服務(wù)器提供 SQL 兼容的協(xié)議，業(yè)務(wù)服務(wù)器無須自己進(jìn)行讀寫分離。

對(duì)于業(yè)務(wù)服務(wù)器來說，訪問中間件和訪問數(shù)據(jù)庫沒有區(qū)別，事實(shí)上在業(yè)務(wù)服務(wù)器看來，中間件就是一個(gè)數(shù)據(jù)庫服務(wù)器。

其基本架構(gòu)是：

56.主從復(fù)制原理了解嗎？

• master 數(shù)據(jù)寫入，更新 binlog
• master 創(chuàng)建一個(gè) dump 線程向 slave 推送 binlog
• slave 連接到 master 的時(shí)候，會(huì)創(chuàng)建一個(gè) IO 線程接收 binlog，并記錄到 relay log 中繼日志中
• slave 再開啟一個(gè) sql 線程讀取 relay log 事件并在 slave 執(zhí)行，完成同步
• slave 記錄自己的 binglog

57.主從同步延遲怎么處理？

主從同步延遲的原因

一個(gè)服務(wù)器開放Ｎ個(gè)鏈接給客戶端來連接的，這樣有會(huì)有大并發(fā)的更新操作, 但是從服務(wù)器的里面讀取 binlog 的線程僅有一個(gè)，當(dāng)某個(gè) SQL 在從服務(wù)器上執(zhí)行的時(shí)間稍長(zhǎng) 或者由于某個(gè) SQL 要進(jìn)行鎖表就會(huì)導(dǎo)致，主服務(wù)器的 SQL 大量積壓，未被同步到從服務(wù)器里。這就導(dǎo)致了主從不一致， 也就是主從延遲。

主從同步延遲的解決辦法

解決主從復(fù)制延遲有幾種常見的方法:

1. 寫操作后的讀操作指定發(fā)給數(shù)據(jù)庫主服務(wù)器

例如，注冊(cè)賬號(hào)完成后，登錄時(shí)讀取賬號(hào)的讀操作也發(fā)給數(shù)據(jù)庫主服務(wù)器。這種方式和業(yè)務(wù)強(qiáng)綁定，對(duì)業(yè)務(wù)的侵入和影響較大，如果哪個(gè)新來的程序員不知道這樣寫代碼，就會(huì)導(dǎo)致一個(gè) bug。

2. 讀從機(jī)失敗后再讀一次主機(jī)

這就是通常所說的 "二次讀取" ，二次讀取和業(yè)務(wù)無綁定，只需要對(duì)底層數(shù)據(jù)庫訪問的 API 進(jìn)行封裝即可，實(shí)現(xiàn)代價(jià)較小，不足之處在于如果有很多二次讀取，將大大增加主機(jī)的讀操作壓力。例如，黑客暴力破解賬號(hào)，會(huì)導(dǎo)致大量的二次讀取操作，主機(jī)可能頂不住讀操作的壓力從而崩潰。

3. 關(guān)鍵業(yè)務(wù)讀寫操作全部指向主機(jī)，非關(guān)鍵業(yè)務(wù)采用讀寫分離

例如，對(duì)于一個(gè)用戶管理系統(tǒng)來說，注冊(cè) + 登錄的業(yè)務(wù)讀寫操作全部訪問主機(jī)，用戶的介紹、爰好、等級(jí)等業(yè)務(wù)，可以采用讀寫分離，因?yàn)榧词褂脩舾牧俗约旱淖晕医榻B，在查詢時(shí)卻看到了自我介紹還是舊的，業(yè)務(wù)影響與不能登錄相比就小很多，還可以忍受。

58.你們一般是怎么分庫的呢？

• 垂直分庫：以表為依據(jù)，按照業(yè)務(wù)歸屬不同，將不同的表拆分到不同的庫中。

• 水平分庫：以字段為依據(jù)，按照一定策略（hash、range 等），將一個(gè)庫中的數(shù)據(jù)拆分到多個(gè)庫中。

59.那你們是怎么分表的？

• 水平分表：以字段為依據(jù)，按照一定策略（hash、range 等），將一個(gè)表中的數(shù)據(jù)拆分到多個(gè)表中。
• 垂直分表：以字段為依據(jù)，按照字段的活躍性，將表中字段拆到不同的表（主表和擴(kuò)展表）中。

60.水平分表有哪幾種路由方式？

什么是路由呢？就是數(shù)據(jù)應(yīng)該分到哪一張表。

水平分表主要有三種路由方式：

• 范圍路由：選取有序的數(shù)據(jù)列 （例如，整形、時(shí)間戳等） 作為路由的條件，不同分段分散到不同的數(shù)據(jù)庫表中。

我們可以觀察一些支付系統(tǒng)，發(fā)現(xiàn)只能查一年范圍內(nèi)的支付記錄，這個(gè)可能就是支付公司按照時(shí)間進(jìn)行了分表。

范圍路由設(shè)計(jì)的復(fù)雜點(diǎn)主要體現(xiàn)在分段大小的選取上，分段太小會(huì)導(dǎo)致切分后子表數(shù)量過多，增加維護(hù)復(fù)雜度；分段太大可能會(huì)導(dǎo)致單表依然存在性能問題，一般建議分段大小在 100 萬至 2000 萬之間，具體需要根據(jù)業(yè)務(wù)選取合適的分段大小。

范圍路由的優(yōu)點(diǎn)是可以隨著數(shù)據(jù)的增加平滑地?cái)U(kuò)充新的表。例如，現(xiàn)在的用戶是 100 萬，如果增加到 1000 萬，只需要增加新的表就可以了，原有的數(shù)據(jù)不需要?jiǎng)?。范圍路由的一個(gè)比較隱含的缺點(diǎn)是分布不均勻，假如按照 1000 萬來進(jìn)行分表，有可能某個(gè)分段實(shí)際存儲(chǔ)的數(shù)據(jù)量只有 1000 條，而另外一個(gè)分段實(shí)際存儲(chǔ)的數(shù)據(jù)量有 900 萬條。

• Hash 路由：選取某個(gè)列 （或者某幾個(gè)列組合也可以） 的值進(jìn)行 Hash 運(yùn)算，然后根據(jù) Hash 結(jié)果分散到不同的數(shù)據(jù)庫表中。

同樣以訂單 id 為例，假如我們一開始就規(guī)劃了 4 個(gè)數(shù)據(jù)庫表，路由算法可以簡(jiǎn)單地用 id % 4 的值來表示數(shù)據(jù)所屬的數(shù)據(jù)庫表編號(hào)，id 為 12 的訂單放到編號(hào)為 50 的子表中，id 為 13 的訂單放到編號(hào)為 61 的字表中。

Hash 路由設(shè)計(jì)的復(fù)雜點(diǎn)主要體現(xiàn)在初始表數(shù)量的選取上，表數(shù)量太多維護(hù)比較麻煩，表數(shù)量太少又可能導(dǎo)致單表性能存在問題。而用了 Hash 路由后，增加子表數(shù)量是非常麻煩的，所有數(shù)據(jù)都要重分布。Hash 路由的優(yōu)缺點(diǎn)和范圍路由基本相反，Hash 路由的優(yōu)點(diǎn)是表分布比較均勻，缺點(diǎn)是擴(kuò)充新的表很麻煩，所有數(shù)據(jù)都要重分布。

• 配置路由：配置路由就是路由表，用一張獨(dú)立的表來記錄路由信息。同樣以訂單 id 為例，我們新增一張 order_router 表，這個(gè)表包含 orderjd 和 tablejd 兩列 , 根據(jù) orderjd 就可以查詢對(duì)應(yīng)的 table_id。

配置路由設(shè)計(jì)簡(jiǎn)單，使用起來非常靈活，尤其是在擴(kuò)充表的時(shí)候，只需要遷移指定的數(shù)據(jù)，然后修改路由表就可以了。

配置路由的缺點(diǎn)就是必須多查詢一次，會(huì)影響整體性能；而且路由表本身如果太大（例如，幾億條數(shù)據(jù)） ，性能同樣可能成為瓶頸，如果我們?cè)俅螌⒙酚杀矸謳旆直?，則又面臨一個(gè)死循環(huán)式的路由算法選擇問題。

61.不停機(jī)擴(kuò)容怎么實(shí)現(xiàn)？

實(shí)際上，不停機(jī)擴(kuò)容，實(shí)操起來是個(gè)非常麻煩而且很有風(fēng)險(xiǎn)的操作，當(dāng)然，面試回答起來就簡(jiǎn)單很多。

• 第一階段：在線雙寫，查詢走老庫

1. 建立好新的庫表結(jié)構(gòu)，數(shù)據(jù)寫入久庫的同時(shí)，也寫入拆分的新庫
2. 數(shù)據(jù)遷移，使用數(shù)據(jù)遷移程序，將舊庫中的歷史數(shù)據(jù)遷移到新庫
3. 使用定時(shí)任務(wù)，新舊庫的數(shù)據(jù)對(duì)比，把差異補(bǔ)齊

• 第二階段：在線雙寫，查詢走新庫

1. 完成了歷史數(shù)據(jù)的同步和校驗(yàn)
2. 把對(duì)數(shù)據(jù)的讀切換到新庫

• 第三階段：舊庫下線

1. 舊庫不再寫入新的數(shù)據(jù)
2. 經(jīng)過一段時(shí)間，確定舊庫沒有請(qǐng)求之后，就可以下線老庫

62.常用的分庫分表中間件有哪些？

• sharding-jdbc
• Mycat

63.那你覺得分庫分表會(huì)帶來什么問題呢？

從分庫的角度來講：

• 事務(wù)的問題

使用關(guān)系型數(shù)據(jù)庫，有很大一點(diǎn)在于它保證事務(wù)完整性。

而分庫之后單機(jī)事務(wù)就用不上了，必須使用分布式事務(wù)來解決。

• 跨庫 JOIN 問題

在一個(gè)庫中的時(shí)候我們還可以利用 JOIN 來連表查詢，而跨庫了之后就無法使用 JOIN 了。

此時(shí)的解決方案就是在業(yè)務(wù)代碼中進(jìn)行關(guān)聯(lián)，也就是先把一個(gè)表的數(shù)據(jù)查出來，然后通過得到的結(jié)果再去查另一張表，然后利用代碼來關(guān)聯(lián)得到最終的結(jié)果。

這種方式實(shí)現(xiàn)起來稍微比較復(fù)雜，不過也是可以接受的。

還有可以適當(dāng)?shù)娜哂嘁恍┳侄?/strong>。比如以前的表就存儲(chǔ)一個(gè)關(guān)聯(lián) ID，但是業(yè)務(wù)時(shí)常要求返回對(duì)應(yīng)的 Name 或者其他字段。這時(shí)候就可以把這些字段冗余到當(dāng)前表中，來去除需要關(guān)聯(lián)的操作。

還有一種方式就是數(shù)據(jù)異構(gòu)，通過 binlog 同步等方式，把需要跨庫 join 的數(shù)據(jù)異構(gòu)到 ES 等存儲(chǔ)結(jié)構(gòu)中，通過 ES 進(jìn)行查詢。

從分表的角度來看：

• 跨節(jié)點(diǎn)的 count,order by,group by 以及聚合函數(shù)問題

只能由業(yè)務(wù)代碼來實(shí)現(xiàn)或者用中間件將各表中的數(shù)據(jù)匯總、排序、分頁然后返回。

• 數(shù)據(jù)遷移，容量規(guī)劃，擴(kuò)容等問題

數(shù)據(jù)的遷移，容量如何規(guī)劃，未來是否可能再次需要擴(kuò)容，等等，都是需要考慮的問題。

• ID 問題

數(shù)據(jù)庫表被切分后，不能再依賴數(shù)據(jù)庫自身的主鍵生成機(jī)制，所以需要一些手段來保證全局主鍵唯一。

1. 還是自增，只不過自增步長(zhǎng)設(shè)置一下。比如現(xiàn)在有三張表，步長(zhǎng)設(shè)置為 3，三張表 ID 初始值分別是 1、2、3。這樣第一張表的 ID 增長(zhǎng)是 1、4、7。第二張表是 2、5、8。第三張表是 3、6、9，這樣就不會(huì)重復(fù)了。
2. UUID，這種最簡(jiǎn)單，但是不連續(xù)的主鍵插入會(huì)導(dǎo)致嚴(yán)重的頁分裂，性能比較差。
3. 分布式 ID，比較出名的就是 Twitter 開源的 sonwflake 雪花算法

運(yùn)維

64.百萬級(jí)別以上的數(shù)據(jù)如何刪除？

關(guān)于索引：由于索引需要額外的維護(hù)成本，因?yàn)樗饕募菃为?dú)存在的文件,所以當(dāng)我們對(duì)數(shù)據(jù)的增加,修改,刪除,都會(huì)產(chǎn)生額外的對(duì)索引文件的操作,這些操作需要消耗額外的 IO,會(huì)降低增/改/刪的執(zhí)行效率。

所以，在我們刪除數(shù)據(jù)庫百萬級(jí)別數(shù)據(jù)的時(shí)候，查詢 MySQL 官方手冊(cè)得知?jiǎng)h除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。

1. 所以我們想要?jiǎng)h除百萬數(shù)據(jù)的時(shí)候可以先刪除索引
2. 然后刪除其中無用數(shù)據(jù)
3. 刪除完成后重新創(chuàng)建索引創(chuàng)建索引也非?？?/section>

65.百萬千萬級(jí)大表如何添加字段？

當(dāng)線上的數(shù)據(jù)庫數(shù)據(jù)量到達(dá)幾百萬、上千萬的時(shí)候，加一個(gè)字段就沒那么簡(jiǎn)單，因?yàn)榭赡軙?huì)長(zhǎng)時(shí)間鎖表。

大表添加字段，通常有這些做法：

• 通過中間表轉(zhuǎn)換過去

創(chuàng)建一個(gè)臨時(shí)的新表，把舊表的結(jié)構(gòu)完全復(fù)制過去，添加字段，再把舊表數(shù)據(jù)復(fù)制過去，刪除舊表，新表命名為舊表的名稱，這種方式可能回丟掉一些數(shù)據(jù)。

• 用 pt-online-schema-change

pt-online-schema-change是 percona 公司開發(fā)的一個(gè)工具，它可以在線修改表結(jié)構(gòu)，它的原理也是通過中間表。

• 先在從庫添加 再進(jìn)行主從切換

如果一張表數(shù)據(jù)量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進(jìn)行主從切換，切換后再將其他幾個(gè)節(jié)點(diǎn)上添加字段。

66.MySQL 數(shù)據(jù)庫 cpu 飆升的話，要怎么處理呢？

排查過程：

（1）使用 top 命令觀察，確定是 mysqld 導(dǎo)致還是其他原因。

（2）如果是 mysqld 導(dǎo)致的，show processlist，查看 session 情況，確定是不是有消耗資源的 sql 在運(yùn)行。

（3）找出消耗高的 sql，看看執(zhí)行計(jì)劃是否準(zhǔn)確， 索引是否缺失，數(shù)據(jù)量是否太大。

處理：

（1）kill 掉這些線程 (同時(shí)觀察 cpu 使用率是否下降)，

（2）進(jìn)行相應(yīng)的調(diào)整 (比如說加索引、改 sql、改內(nèi)存參數(shù))

（3）重新跑這些 SQL。

其他情況：

也有可能是每個(gè) sql 消耗資源并不多，但是突然之間，有大量的 session 連進(jìn)來導(dǎo)致 cpu 飆升，這種情況就需要跟應(yīng)用一起來分析為何連接數(shù)會(huì)激增，再做出相應(yīng)的調(diào)整，比如說限制連接數(shù)等

沒有什么使我停留——除了目的，縱然岸旁有玫瑰、有綠蔭、有寧靜的港灣，我是不系之舟。

推薦閱讀：

• 計(jì)算機(jī)專業(yè)如何細(xì)分？
• 推薦一款macOS 軟件包管理神器
• ?？粕?，去培訓(xùn)還是直接找工作？
• 我扔進(jìn)垃圾桶的第一本 Java 書...