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

基礎(chǔ)

MySQ Logo

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

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

• 內(nèi)連接（inner join）：取得兩張表中滿(mǎn)足存在連接匹配關(guān)系的記錄。
• 外連接（outer join）：不只取得兩張表中滿(mǎn)足存在連接匹配關(guān)系的記錄，還包括某張表（或兩張表）中不滿(mǎn)足匹配關(guān)系的記錄。
• 交叉連接（cross join）：顯示兩張表所有記錄一一對(duì)應(yīng)，沒(méi)有匹配關(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-來(lái)源菜鳥(niǎo)教程

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

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

數(shù)據(jù)庫(kù)三范式

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

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

三大范式的作用是為了控制數(shù)據(jù)庫(kù)的冗余，是對(duì)空間的節(jié)省，實(shí)際上，一般互聯(lián)網(wǎng)公司的設(shè)計(jì)都是反范式的，通過(guò)冗余一些數(shù)據(jù)，避免跨表跨庫(kù)，利用空間換時(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來(lái)說(shuō)，最多能存放的字符個(gè)數(shù)為255，和編碼無(wú)關(guān)

varchar：

• varchar表示可變長(zhǎng)字符串，長(zhǎng)度是可變的；
• 插入的數(shù)據(jù)是多長(zhǎng)，就按照多長(zhǎng)來(lái)存儲(chǔ)；
• varchar在存取方面與char相反，它存取慢，因?yàn)殚L(zhǎng)度不固定，但正因如此，不占據(jù)多余的空間，是時(shí)間換空間的做法；
• 對(duì)于varchar來(lái)說(shuō)，最多能存放的字符個(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沒(méi)有字符集，text有一個(gè)字符集，并且根據(jù)字符集的校對(duì)規(guī)則對(duì)值進(jìn)行排序和比較

6.DATETIME和TIMESTAMP的異同？

相同點(diǎn)：

區(qū)別：

DATETIME和TIMESTAMP的區(qū)別

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

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

8.MySQL里記錄貨幣用什么字段類(lèi)型比較好？

貨幣在數(shù)據(jù)庫(kù)中MySQL常用Decimal和Numric類(lèi)型表示，這兩種類(lèi)型被MySQL實(shí)現(xiàn)為同樣的類(lèi)型。他們被用于保存與貨幣有關(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類(lèi)型屬于DML屬于DDL屬于DDL回滾可回滾不可回滾不可回滾刪除內(nèi)容表結(jié)構(gòu)還在，刪除表的全部或者一部分?jǐn)?shù)據(jù)行表結(jié)構(gòu)還在，刪除表中的所有數(shù)據(jù)從數(shù)據(jù)庫(kù)中刪除表，所有數(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è)列并且沒(méi)有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于 count（*）
• 如果有主鍵，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
• 如果表只有一個(gè)字段，則 select count（*）最優(yōu)。

13.一條SQL查詢(xún)語(yǔ)句的執(zhí)行順序？

查詢(xún)語(yǔ)句執(zhí)行順序

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

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

在這里插入圖片描述

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

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

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

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

存儲(chǔ)引擎

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

主要存儲(chǔ)引擎

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

功能MylSAMMEMORYInnoDB存儲(chǔ)限制256TBRAM64TB支持事務(wù)NoNoYes支持全文索引YesNoYes支持樹(shù)索引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開(kāi)始InnoDB支持全文索引。

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

大致上可以這么選擇：

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

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

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

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

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

1. 存儲(chǔ)結(jié)構(gòu)：每個(gè)MyISAM在磁盤(pán)上存儲(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)致其它查詢(xún)和更新都會(huì)被阻塞InnoDB支持行級(jí)鎖。

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

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

6. 表的具體行數(shù)：MyISAM保存了表的總行數(shù)，如果select count(*) from table;會(huì)直接取出出該值; InnoDB沒(méi)有保存表的總行數(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)閉過(guò)程進(jìn)行了記錄，能幫助定位MySQL問(wèn)題。
• 慢查詢(xún)?nèi)罩荆╯low query log）：慢查詢(xún)?nèi)罩臼怯脕?lái)記錄執(zhí)行時(shí)間超過(guò) long_query_time 這個(gè)變量定義的時(shí)長(zhǎng)的查詢(xún)語(yǔ)句。通過(guò)慢查詢(xún)?nèi)罩?，可以查找出哪些查?xún)語(yǔ)句的執(zhí)行效率很低，以便進(jìn)行優(yōu)化。
• 一般查詢(xún)?nèi)罩荆╣eneral log）：一般查詢(xún)?nèi)罩居涗浟怂袑?duì)MySQL數(shù)據(jù)庫(kù)請(qǐng)求的信息，無(wú)論請(qǐng)求是否正確執(zhí)行。
• 二進(jìn)制日志（bin log）：關(guān)于二進(jìn)制日志，它記錄了數(shù)據(jù)庫(kù)所有執(zhí)行的DDL和DML語(yǔ)句（除了數(shù)據(jù)查詢(xún)語(yǔ)句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ù)庫(kù)進(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ù)庫(kù)有關(guān)的日志記錄，包括InnoDB、MyISAM等存儲(chǔ)引擎的日志，而redo log只記InnoDB存儲(chǔ)引擎的日志。
• 記錄的內(nèi)容不同，bin log記錄的是關(guān)于一個(gè)事務(wù)的具體操作內(nèi)容，即該日志是邏輯日志。而redo log記錄的是關(guān)于每個(gè)頁(yè)（Page）的更改的物理情況。
• 寫(xiě)入的時(shí)間不同，bin log僅在事務(wù)提交前進(jìn)行提交，也就是只寫(xiě)磁盤(pán)一次。而在事務(wù)進(jìn)行的過(guò)程中，卻不斷有redo ertry被寫(xiě)入redo log中。
• 寫(xiě)入的方式也不相同，redo log是循環(huán)寫(xiě)入和擦除，bin log是追加寫(xiě)入，不會(huì)覆蓋已經(jīng)寫(xiě)的文件。

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

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

update執(zhí)行

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

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

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

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

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

先寫(xiě)入redo log，后寫(xiě)入binlog：

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

先寫(xiě)redo log，后寫(xiě)bin log的問(wèn)題

先寫(xiě)入binlog，后寫(xiě)入redo log：

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

先寫(xiě)bin log，后寫(xiě)redo log的問(wèn)題

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

23.redo log怎么刷入磁盤(pán)的知道嗎？

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

redo log緩沖

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

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

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

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

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

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

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

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

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

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

redo log分塊和寫(xiě)入

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

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

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

write pos和checkpoint

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

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

SQL 優(yōu)化

24.慢SQL如何定位呢？

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

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

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

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

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

SQL優(yōu)化

避免不必要的列

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

分頁(yè)優(yōu)化

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

例如：

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

優(yōu)化方案：

• 延遲關(guān)聯(lián)先通過(guò)where條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過(guò)主鍵id提取數(shù)據(jù)行，而不是通過(guò)原來(lái)的二級(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
• 書(shū)簽方式書(shū)簽方式就是找到limit第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值，根據(jù)這個(gè)主鍵值再去過(guò)濾并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使用非主鍵索引查詢(xún)數(shù)據(jù)時(shí)會(huì)回表，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢(xún)的字段，那它沒(méi)有必要再回表查詢(xún)了，這就叫覆蓋索引

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

select name from test where city=’上海’

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

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

低版本避免使用or查詢(xún)

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

避免使用 != 或者 操作符

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

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

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

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

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

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

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

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

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

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

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

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

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

JOIN優(yōu)化

優(yōu)化子查詢(xún)

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

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

關(guān)聯(lián)查詢(xú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ù)就少，查詢(xún)速度就被加快了。

select name from A left join B ;

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

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

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

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

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

排序優(yōu)化

利用索引掃描做排序

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

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

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

例如：

–建立索引（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í)，才能夠使用索引來(lái)對(duì)結(jié)果做排序

UNION優(yōu)化

條件下推

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

最好手工將where、limit等子句下推到union的各個(gè)子查詢(xún)中，以便優(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編寫(xiě)，也應(yīng)該先explain，查看一下執(zhí)行計(jì)劃，看看是否還有優(yōu)化的空間。

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

explain

explain

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

索引

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

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

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

索引分類(lèi)

例如從基本使用使用的角度來(lái)講：

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

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

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

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

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

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

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

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

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

• 查詢(xún)條件包含or，可能導(dǎo)致索引失效
• 如果字段類(lèi)型是字符串，where時(shí)一定用引號(hào)括起來(lái)，否則會(huì)因?yàn)殡[式類(lèi)型轉(zhuǎn)換，索引失效
• like通配符可能導(dǎo)致索引失效。
• 聯(lián)合索引，查詢(xú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)致索引失效。
• 左連接查詢(xún)或者右連接查詢(xún)查詢(xún)關(guān)聯(lián)的字段編碼格式不一樣，可能導(dǎo)致索引失效。
• MySQL優(yōu)化器估計(jì)使用全表掃描要比使用索引快,則不使用索引。

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

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

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

當(dāng)然不是。

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

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

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

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

B+樹(shù)索引

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

• 最外面的方塊，的塊我們稱(chēng)之為一個(gè)磁盤(pán)塊，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（粉色所示）和指針（黃色/灰色所示），如根節(jié)點(diǎn)磁盤(pán)包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤(pán)塊，P2表示在17和35之間的磁盤(pán)塊，P3表示大于35的磁盤(pán)塊。真實(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)行范圍查詢(xún)。

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

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

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

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

在查找數(shù)據(jù)時(shí)一次頁(yè)的查找代表一次 IO，也就是說(shuō)，一張 2000 萬(wàn)左右的表，查詢(xún)數(shù)據(jù)最多需要訪問(wèn) 3 次磁盤(pán)。

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

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

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

為什么不用普通二叉樹(shù)？

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

為什么不用平衡二叉樹(shù)呢？

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

36.為什么用 B+ 樹(shù)而不用 B 樹(shù)呢？

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

• 它是 B Tree 的變種，B Tree 能解決的問(wèn)題，它都能解決。B Tree 解決的兩大問(wèn)題：每個(gè)節(jié)點(diǎn)存儲(chǔ)更多關(guān)鍵字；路數(shù)更多
• 掃庫(kù)、掃表能力更強(qiáng)如果我們要對(duì)表進(jìn)行全表掃描，只需要遍歷葉子節(jié)點(diǎn)就可以 了，不需要遍歷整棵 B+Tree 拿到所有的數(shù)據(jù)。
• B+Tree 的磁盤(pán)讀寫(xiě)能力相對(duì)于 B Tree 來(lái)說(shuō)更強(qiáng)，IO次數(shù)更少根節(jié)點(diǎn)和枝節(jié)點(diǎn)不保存數(shù)據(jù)區(qū)， 所以一個(gè)節(jié)點(diǎn)可以保存更多的關(guān)鍵字，一次磁盤(pá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+ 樹(shù)索引區(qū)別是什么？

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

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

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

可以這么說(shuō)：

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

聚簇索引和非聚簇索引

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

39.回表了解嗎？

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

例如: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)合索引中，查詢(xún)的時(shí)候只有匹配了前一個(gè)/左邊的值之后，才能匹配下一個(gè)。

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

為什么不從最左開(kāi)始查，就無(wú)法匹配呢？

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

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

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

組合索引

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

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

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

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

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

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

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

沒(méi)有使用ICP

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

使用ICP

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

鎖

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

MySQL中的鎖

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

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

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

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

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

我們拿這么一個(gè)用戶(hù)表來(lái)表示行級(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)行等值查詢(xú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è)左開(kāi)右開(kāi)空間。

間隙鎖

間隙鎖就是鎖定某些間隙區(qū)間的。當(dāng)我們使用用等值查詢(xún)或者范圍查詢(xún)，并且沒(méi)有命中任何一個(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 臨鍵鎖

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

臨鍵鎖

臨鍵鎖就是記錄鎖(Record Locks)和間隙鎖(Gap Locks)的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。當(dāng)我們使用范圍查詢(xún)，并且命中了部分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)行鎖類(lèi)型就是臨鍵鎖(Next-Key Locks)。當(dāng)使用唯一性索引，等值查詢(xún)匹配到一條記錄的時(shí)候，臨鍵鎖(Next-Key Locks)會(huì)退化成記錄鎖；沒(méi)有匹配到任何記錄的時(shí)候，退化成間隙鎖。

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

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

• Insert Intention Lock 插入意向鎖

一個(gè)事務(wù)在插入一條記錄時(shí)需要判斷一下插入位置是不是被別的事務(wù)加了意向鎖 ，如果有的話(huà)，插入操作需要等待，直到擁有 gap鎖 的那個(gè)事務(wù)提交。但是事務(wù)在等待的時(shí)候也需要在內(nèi)存中生成一個(gè) 鎖結(jié)構(gòu) ，表明有事務(wù)想在某個(gè) 間隙 中插入新記錄，但是現(xiàn)在在等待。這種類(lèi)型的鎖命名為 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的多粒度鎖，它解決的是表鎖和行鎖共存的問(wèn)題。

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

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

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

有了意向鎖之后，要執(zhí)行的事務(wù)A在申請(qǐng)行鎖（寫(xiě)鎖）之前，數(shù)據(jù)庫(kù)會(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的樂(lè)觀鎖和悲觀鎖了解嗎？

• 悲觀鎖（Pessimistic Concurrency Control）：

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

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

• 樂(lè)觀鎖（Optimistic Concurrency Control）

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

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

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

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

47.MySQL 遇到過(guò)死鎖問(wèn)題嗎，你是如何解決的？

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

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

（2）找出死鎖 sql

（3）分析 sql 加鎖情況

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

（5）分析死鎖日志

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

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

事務(wù)

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

事務(wù)四大特性

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

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

• 事務(wù)的隔離性是通過(guò)數(shù)據(jù)庫(kù)鎖的機(jī)制實(shí)現(xiàn)的。
• 事務(wù)的一致性由undo log來(lái)保證：undo log是邏輯日志，記錄了事務(wù)的insert、update、deltete操作，回滾的時(shí)候做相反的delete、update、insert操作來(lái)恢復(fù)數(shù)據(jù)。
• 事務(wù)的原子性和持久性由redo log來(lái)保證：redolog被稱(chēng)作重做日志，是物理日志，事務(wù)提交的時(shí)候，必須先將事務(wù)的所有日志寫(xiě)入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è)相同的查詢(xún)，讀取同一條記錄，卻返回了不同的數(shù)據(jù)，這就是不可重復(fù)讀。
• 事務(wù) A 查詢(xún)一個(gè)范圍的結(jié)果集，另一個(gè)并發(fā)事務(wù) B 往這個(gè)范圍中插入 / 刪除了數(shù)據(jù)，并靜悄悄地提交，然后事務(wù) A 再次查詢(xún)相同的范圍，兩次讀取得到的結(jié)果集不一樣了，這就是幻讀。

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

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

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

讀未提交

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

• 事務(wù)讀不加鎖，不阻塞其他事務(wù)的讀和寫(xiě)
• 事務(wù)寫(xiě)阻塞其他事務(wù)寫(xiě)，但不阻塞其他事務(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)采用的是讀寫(xiě)都加鎖的原理。

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

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

MVCC(Multi Version Concurrency Control)，中文名是多版本并發(fā)控制，簡(jiǎn)單來(lái)說(shuō)就是通過(guò)維護(hù)數(shù)據(jù)歷史版本，從而解決并發(fā)訪問(wèn)情況下的讀一致性問(wèn)題。關(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í)，該條記錄的示例圖如下：

在這里插入圖片描述

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

update操作

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

MVCC

ReadView

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

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

事務(wù)和ReadView

• m_ids ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(wù)的事務(wù)id 列表。
• min_trx_id ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(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 ，這樣在訪問(wèn)某條記錄時(shí)，只需要按照下邊的步驟判斷記錄的某個(gè)版本是否可見(jiàn)：

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

如果某個(gè)版本的數(shù)據(jù)對(duì)當(dāng)前事務(wù)不可見(jiàn)的話(huà)，那就順著版本鏈找到下一個(gè)版本的數(shù)據(jù)，繼續(xù)按照上邊的步驟判斷可見(jiàn)性，依此類(lèi)推，直到版本鏈中的最后一個(gè)版本。如果最后一個(gè)版本也不可見(jiàn)的話(huà)，那么就意味著該條記錄對(duì)該事務(wù)完全不可見(jiàn)，查詢(xún)結(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ù)庫(kù)讀寫(xiě)分離了解嗎？

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

讀寫(xiě)分離

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

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

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

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

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

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

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

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

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

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

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

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

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

主從復(fù)制

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

主從同步延遲的原因

一個(gè)服務(wù)器開(kāi)放Ｎ個(gè)鏈接給客戶(hù)端來(lái)連接的，這樣有會(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ù)制延遲有幾種常見(jiàn)的方法:

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

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

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

58.你們一般是怎么分庫(kù)的呢？

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

垂直分庫(kù)

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

水平分庫(kù)

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

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

表拆分

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

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

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

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

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

范圍路由

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

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

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

同樣以訂單 id 為例，假如我們一開(kāi)始就規(guī)劃了 4個(gè)數(shù)據(jù)庫(kù)表，路由算法可以簡(jiǎn)單地用 id % 4 的值來(lái)表示數(shù)據(jù)所屬的數(shù)據(jù)庫(kù)表編號(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)致單表性能存在問(wèn)題。而用了 Hash 路由后，增加子表數(shù)量是非常麻煩的，所有數(shù)據(jù)都要重分布。Hash 路由的優(yōu)缺點(diǎn)和范圍路由基本相反，Hash 路由的優(yōu)點(diǎn)是表分布比較均勻，缺點(diǎn)是擴(kuò)充新的表很麻煩，所有數(shù)據(jù)都要重分布。

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

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

配置路由

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

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

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

• 第一階段：在線雙寫(xiě)，查詢(xún)走老庫(kù)
• 建立好新的庫(kù)表結(jié)構(gòu)，數(shù)據(jù)寫(xiě)入久庫(kù)的同時(shí)，也寫(xiě)入拆分的新庫(kù)
• 數(shù)據(jù)遷移，使用數(shù)據(jù)遷移程序，將舊庫(kù)中的歷史數(shù)據(jù)遷移到新庫(kù)
• 使用定時(shí)任務(wù)，新舊庫(kù)的數(shù)據(jù)對(duì)比，把差異補(bǔ)齊第一階段
• 第二階段：在線雙寫(xiě)，查詢(xún)走新庫(kù)
• 完成了歷史數(shù)據(jù)的同步和校驗(yàn)
• 把對(duì)數(shù)據(jù)的讀切換到新庫(kù)第二階段
• 第三階段：舊庫(kù)下線
• 舊庫(kù)不再寫(xiě)入新的數(shù)據(jù)
• 經(jīng)過(guò)一段時(shí)間，確定舊庫(kù)沒(méi)有請(qǐng)求之后，就可以下線老庫(kù)

第三階段

62.常用的分庫(kù)分表中間件有哪些？

• sharding-jdbc
• Mycat

63.那你覺(jué)得分庫(kù)分表會(huì)帶來(lái)什么問(wèn)題呢？

從分庫(kù)的角度來(lái)講：

• 事務(wù)的問(wèn)題

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

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

• 跨庫(kù) JOIN 問(wèn)題

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

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

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

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

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

從分表的角度來(lái)看：

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

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

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

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

• ID 問(wèn)題

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

運(yùn)維

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

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

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

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

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

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

• 通過(guò)中間表轉(zhuǎn)換過(guò)去創(chuàng)建一個(gè)臨時(shí)的新表，把舊表的結(jié)構(gòu)完全復(fù)制過(guò)去，添加字段，再把舊表數(shù)據(jù)復(fù)制過(guò)去，刪除舊表，新表命名為舊表的名稱(chēng)，這種方式可能回丟掉一些數(shù)據(jù)。
• 用pt-online-schema-changept-online-schema-change是percona公司開(kāi)發(fā)的一個(gè)工具，它可以在線修改表結(jié)構(gòu)，它的原理也是通過(guò)中間表。
• 先在從庫(kù)添加 再進(jìn)行主從切換如果一張表數(shù)據(jù)量大且是熱表（讀寫(xiě)特別頻繁），則可以考慮先在從庫(kù)添加，再進(jìn)行主從切換，切換后再將其他幾個(gè)節(jié)點(diǎn)上添加字段。

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

排查過(guò)程：

（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)整 (比如說(shuō)加索引、改 sql、改內(nèi)存參數(shù))

（3）重新跑這些 SQL。

其他情況：

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

原文鏈接：https://mp.weixin.qq.com/s/zSTyZ-8CFalwAYSB0PN6wA