不知不覺，面渣逆襲系列已經(jīng)肝了差不多十篇，每一篇都是上萬字，幾十圖，基本上涵蓋了面試的主要知識(shí)點(diǎn)，這期MySQL結(jié)束之后，這個(gè)系列可能會(huì)暫時(shí)告一段落，作為面渣逆襲系列第一階段的收官之作，大家多多點(diǎn)贊、收藏哦！

基礎(chǔ)

MySQ Logo

作為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={,,,,,,}。

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ù)庫三范式

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

你設(shè)計(jì)遵守范式嗎？

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

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

varchar

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)：

區(qū)別：

DATETIME和TIMESTAMP的區(qū)別

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ū)分情景：

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)閒loat和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ū)別？

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

deletetruncatedrop類型屬于DML屬于DDL屬于DDL回滾可回滾不可回滾不可回滾刪除內(nèi)容表結(jié)構(gòu)還在，刪除表的全部或者一部分?jǐn)?shù)據(jù)行表結(jié)構(gòu)還在，刪除表中的所有數(shù)據(jù)從數(shù)據(jù)庫中刪除表，所有數(shù)據(jù)行，索引和權(quán)限也會(huì)被刪除刪除速度刪除速度慢，需要逐行刪除刪除速度快刪除速度最快

因此，在不再需要一張表的時(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ū)別？

三種計(jì)數(shù)方式

執(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í)行順序？

查詢語句執(zhí)行順序

數(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ǔ)引擎

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

功能MylSAMMEMORYInnoDB存儲(chǔ)限制256TBRAM64TB支持事務(wù)NoNoYes支持全文索引YesNoYes支持樹索引YesYesYes支持哈希索引NoYesYes支持?jǐn)?shù)據(jù)緩存NoN/AYes支持外鍵NoNoYes

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í)可以不用怎么了解。

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

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主要日志

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

• 錯(cuò)誤日志（error log）：錯(cuò)誤日志文件對(duì)MySQL的啟動(dòng)、運(yùn)行、關(guān)閉過程進(jìn)行了記錄，能幫助定位MySQL問題。
• 慢查詢?nèi)罩荆╯low query log）：慢查詢?nèi)罩臼怯脕碛涗泩?zhí)行時(shí)間超過 long_query_time 這個(gè)變量定義的時(shí)長(zhǎng)的查詢語句。通過慢查詢?nèi)罩?，可以查找出哪些查詢語句的執(zhí)行效率很低，以便進(jìn)行優(yōu)化。
• 一般查詢?nèi)罩荆╣eneral 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)的日志。

update執(zhí)行

從上圖可以看出，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ù)沒有被更新。

先寫redo log，后寫bin log的問題

先寫入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ù)并沒有更新。

先寫bin log，后寫redo log的問題

簡(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ū)。

redo log緩沖

什么時(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 組成。

redo log分塊和寫入

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

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

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

write pos和checkpoint

當(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è)途徑：

發(fā)現(xiàn)慢SQL

• 慢查詢?nèi)罩荆洪_啟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)化。

SQL優(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 ) bwhere 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ì)劃的信息。

explain

explain

• 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)容，一樣的道理。

索引加快查詢遠(yuǎn)離

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

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

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è)雙向有序鏈表。

B+樹索引

在這張圖里，有兩個(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ù)呢？

B+樹存儲(chǔ)數(shù)據(jù)條數(shù)

假設(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 = ‘張三’;

InnoDB回表

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。

沒有使用ICP

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

使用ICP

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

鎖

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

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ù)記錄。

簡(jiǎn)化的主鍵索引

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ù)四大特性

• 原子性：事務(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ù)的提交操作才算完成。

ACID靠什么保證

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ā)生的問題：

隔離級(jí)別臟讀不可重復(fù)讀幻讀Read Uncommited 讀取未提交是是是Read Commited 讀取已提交否是否Repeatable Read 可重復(fù)讀否否是Serialzable 可串行化否否否

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è)過程如下：

update操作

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

MVCC

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è)快照，我們可以獲?。?/p>

事務(wù)和ReadView

• 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ù)器，一般有兩種方式：程序代碼封裝和中間件封裝。

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

業(yè)務(wù)代碼封裝

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

中間件封裝指的是獨(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)是：

數(shù)據(jù)庫中間件

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

主從復(fù)制

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ù)制延遲有幾種常見的方法:

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

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

例如，對(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路由

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)單很多。

• 第一階段：在線雙寫，查詢走老庫
• 建立好新的庫表結(jié)構(gòu)，數(shù)據(jù)寫入久庫的同時(shí)，也寫入拆分的新庫
• 數(shù)據(jù)遷移，使用數(shù)據(jù)遷移程序，將舊庫中的歷史數(shù)據(jù)遷移到新庫
• 使用定時(shí)任務(wù)，新舊庫的數(shù)據(jù)對(duì)比，把差異補(bǔ)齊第一階段
• 第二階段：在線雙寫，查詢走新庫
• 完成了歷史數(shù)據(jù)的同步和校驗(yàn)
• 把對(duì)數(shù)據(jù)的讀切換到新庫第二階段
• 第三階段：舊庫下線
• 舊庫不再寫入新的數(shù)據(jù)
• 經(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ù)娜哂嘁恍┳侄巍１热缫郧暗谋砭痛鎯?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ī)制，所以需要一些手段來保證全局主鍵唯一。

運(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ù)量是成正比的。