基礎(chǔ)相關(guān)

1、關(guān)系型和非關(guān)系型數(shù)據(jù)庫(kù)的區(qū)別？

關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)點(diǎn)

• 容易理解，因?yàn)樗捎昧岁P(guān)系模型來(lái)組織數(shù)據(jù)。
• 可以保持?jǐn)?shù)據(jù)的一致性。
• 數(shù)據(jù)更新的開(kāi)銷(xiāo)比較小。
• 支持復(fù)雜查詢(xún)（帶 where 子句的查詢(xún)）

非關(guān)系型數(shù)據(jù)庫(kù)（NOSQL）的優(yōu)點(diǎn)

• 無(wú)需經(jīng)過(guò) SQL 層的解析，讀寫(xiě)效率高。
• 基于鍵值對(duì)，讀寫(xiě)性能很高，易于擴(kuò)展
• 可以支持多種類(lèi)型數(shù)據(jù)的存儲(chǔ)，如圖片，文檔等等。
• 擴(kuò)展（可分為內(nèi)存性數(shù)據(jù)庫(kù)以及文檔型數(shù)據(jù)庫(kù)，比如 Redis，MongoDB，HBase 等，適合場(chǎng)景：數(shù)據(jù)量大高可用的日志系統(tǒng)/地理位置存儲(chǔ)系統(tǒng)）。

2、詳細(xì)說(shuō)一下一條 MySQL 語(yǔ)句執(zhí)行的步驟

Server 層按順序執(zhí)行 SQL 的步驟為：

• 客戶(hù)端請(qǐng)求 -> 連接器（驗(yàn)證用戶(hù)身份，給予權(quán)限）
• 查詢(xún)緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作）
• 分析器（對(duì) SQL 進(jìn)行詞法分析和語(yǔ)法分析操作）
• 優(yōu)化器（主要對(duì)執(zhí)行的 SQL 優(yōu)化選擇最優(yōu)的執(zhí)行方案方法）
• 執(zhí)行器（執(zhí)行時(shí)會(huì)先看用戶(hù)是否有執(zhí)行權(quán)限，有才去使用這個(gè)引擎提供的接口）-> 去引擎層獲取數(shù)據(jù)返回（如果開(kāi)啟查詢(xún)緩存則會(huì)緩存查詢(xún)結(jié)果）

索引相關(guān)

3、MySQL 使用索引的原因？

根本原因

• 索引的出現(xiàn)，就是為了提高數(shù)據(jù)查詢(xún)的效率，就像書(shū)的目錄一樣。
• 對(duì)于數(shù)據(jù)庫(kù)的表而言，索引其實(shí)就是它的“目錄”。

擴(kuò)展

• 創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
• 幫助引擎層避免排序和臨時(shí)表
• 將隨機(jī) IO 變?yōu)轫樞?IO，加速表和表之間的連接。

4、索引的三種常見(jiàn)底層數(shù)據(jù)結(jié)構(gòu)以及優(yōu)缺點(diǎn)

三種常見(jiàn)的索引底層數(shù)據(jù)結(jié)構(gòu)：分別是哈希表、有序數(shù)組和搜索樹(shù)。

• 哈希表這種適用于等值查詢(xún)的場(chǎng)景，比如 memcached 以及其它一些 NoSQL 引擎，不適合范圍查詢(xún)。
• 有序數(shù)組索引只適用于靜態(tài)存儲(chǔ)引擎，等值和范圍查詢(xún)性能好，但更新數(shù)據(jù)成本高。
• N 叉樹(shù)由于讀寫(xiě)上的性能優(yōu)點(diǎn)以及適配磁盤(pán)訪(fǎng)問(wèn)模式以及廣泛應(yīng)用在數(shù)據(jù)庫(kù)引擎中。
• 擴(kuò)展（以 InnoDB 的一個(gè)整數(shù)字段索引為例，這個(gè) N 差不多是 1200?？脴?shù)高是 4 的時(shí)候，就可以存 1200 的 3 次方個(gè)值，這已經(jīng) 17 億了?？紤]到樹(shù)根的數(shù)據(jù)塊總是在內(nèi)存中的，一個(gè) 10 億行的表上一個(gè)整數(shù)字段的索引，查找一個(gè)值最多只需要訪(fǎng)問(wèn) 3 次磁盤(pán)。其實(shí)，樹(shù)的第二層也有很大概率在內(nèi)存中，那么訪(fǎng)問(wèn)磁盤(pán)的平均次數(shù)就更少了。）

5、索引的常見(jiàn)類(lèi)型以及它是如何發(fā)揮作用的？

根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類(lèi)型分為主鍵索引和非主鍵索引。

• 主鍵索引的葉子節(jié)點(diǎn)存的整行數(shù)據(jù)，在InnoDB里也被稱(chēng)為聚簇索引。
• 非主鍵索引葉子節(jié)點(diǎn)存的主鍵的值，在InnoDB里也被稱(chēng)為二級(jí)索引。

6、MyISAM 和 InnoDB 實(shí)現(xiàn) B 樹(shù)索引方式的區(qū)別是什么？

• InnoDB 存儲(chǔ)引擎：B+ 樹(shù)索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)本身，其數(shù)據(jù)文件本身就是索引文件。
• MyISAM 存儲(chǔ)引擎：B+ 樹(shù)索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)的物理地址，葉節(jié)點(diǎn)的 data 域存放的是數(shù)據(jù)記錄的地址，索引文件和數(shù)據(jù)文件是分離的。

7、InnoDB 為什么設(shè)計(jì) B+ 樹(shù)索引？

兩個(gè)考慮因素：

• InnoDB 需要執(zhí)行的場(chǎng)景和功能需要在特定查詢(xún)上擁有較強(qiáng)的性能。
• CPU 將磁盤(pán)上的數(shù)據(jù)加載到內(nèi)存中需要花費(fèi)大量時(shí)間。

為什么選擇 B+ 樹(shù)：

• 哈希索引雖然能提供O（1）復(fù)雜度查詢(xún)，但對(duì)范圍查詢(xún)和排序卻無(wú)法很好的支持，最終會(huì)導(dǎo)致全表掃描。

• B 樹(shù)能夠在非葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，但會(huì)導(dǎo)致在查詢(xún)連續(xù)數(shù)據(jù)可能帶來(lái)更多的隨機(jī) IO。

• 而 B+ 樹(shù)的所有葉節(jié)點(diǎn)可以通過(guò)指針來(lái)相互連接，減少順序遍歷帶來(lái)的隨機(jī) IO。

• 普通索引還是唯一索引？

  由于唯一索引用不上 change buffer 的優(yōu)化機(jī)制，因此如果業(yè)務(wù)可以接受，從性能角度出發(fā)建議你優(yōu)先考慮非唯一索引。

8、什么是覆蓋索引和索引下推？

覆蓋索引：

• 在某個(gè)查詢(xún)里面，索引 k 已經(jīng)“覆蓋了”我們的查詢(xún)需求，稱(chēng)為覆蓋索引。

• 覆蓋索引可以減少樹(shù)的搜索次數(shù)，顯著提升查詢(xún)性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

索引下推：

• MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿(mǎn)足條件的記錄，減少回表次數(shù)。

9、哪些操作會(huì)導(dǎo)致索引失效？

• 對(duì)索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 這兩種方式都會(huì)造成索引失效。原因在于查詢(xún)的結(jié)果可能是多個(gè)，不知道從哪個(gè)索引值開(kāi)始比較，于是就只能通過(guò)全表掃描的方式來(lái)查詢(xún)。
• 對(duì)索引進(jìn)行函數(shù)/對(duì)索引進(jìn)行表達(dá)式計(jì)算，因?yàn)樗饕３值氖撬饕侄蔚脑贾担皇墙?jīng)過(guò)函數(shù)計(jì)算的值，自然就沒(méi)辦法走索引。
• 對(duì)索引進(jìn)行隱式轉(zhuǎn)換相當(dāng)于使用了新函數(shù)。
• WHERE 子句中的 OR語(yǔ)句，只要有條件列不是索引列，就會(huì)進(jìn)行全表掃描。

10、字符串加索引

• 直接創(chuàng)建完整索引，這樣可能會(huì)比較占用空間。
• 創(chuàng)建前綴索引，節(jié)省空間，但會(huì)增加查詢(xún)掃描次數(shù)，并且不能使用覆蓋索引。
• 倒序存儲(chǔ)，再創(chuàng)建前綴索引，用于繞過(guò)字符串本身前綴的區(qū)分度不夠的問(wèn)題。
• 創(chuàng)建 hash 字段索引，查詢(xún)性能穩(wěn)定，有額外的存儲(chǔ)和計(jì)算消耗，跟第三種方式一樣，都不支持范圍掃描。

日志相關(guān)

11、MySQL 的 change buffer 是什么？

• 當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新；而如果這個(gè)數(shù)據(jù)頁(yè)還沒(méi)有在內(nèi)存中的話(huà)，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會(huì)將這些更新操作緩存在 change buffer 中。
• 這樣就不需要從磁盤(pán)中讀入這個(gè)數(shù)據(jù)頁(yè)了，在下次查詢(xún)需要訪(fǎng)問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁(yè)有關(guān)的操作。通過(guò)這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性。
• 注意唯一索引的更新就不能使用 change buffer，實(shí)際上也只有普通索引可以使用。
• 適用場(chǎng)景：
  - 對(duì)于寫(xiě)多讀少的業(yè)務(wù)來(lái)說(shuō)，頁(yè)面在寫(xiě)完以后馬上被訪(fǎng)問(wèn)到的概率比較小，此時(shí) change buffer 的使用效果最好。這種業(yè)務(wù)模型常見(jiàn)的就是賬單類(lèi)、日志類(lèi)的系統(tǒng)。
  
- 反過(guò)來(lái)，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫(xiě)入之后馬上會(huì)做查詢(xún)，那么即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪(fǎng)問(wèn)這個(gè)數(shù)據(jù)頁(yè)，會(huì)立即觸發(fā) merge 過(guò)程。這樣隨機(jī)訪(fǎng)問(wèn) IO 的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。

12、MySQL 是如何判斷一行掃描數(shù)的？

• MySQL 在真正開(kāi)始執(zhí)行語(yǔ)句之前，并不能精確地知道滿(mǎn)足這個(gè)條件的記錄有多少條。
• 而只能根據(jù)統(tǒng)計(jì)信息來(lái)估算記錄數(shù)。這個(gè)統(tǒng)計(jì)信息就是索引的“區(qū)分度。

13、MySQL 的 redo log 和 binlog 區(qū)別？

14、為什么需要 redo log？

• redo log 主要用于 MySQL 異常重啟后的一種數(shù)據(jù)恢復(fù)手段，確保了數(shù)據(jù)的一致性。
• 其實(shí)是為了配合 MySQL 的 WAL 機(jī)制。因?yàn)?MySQL 進(jìn)行更新操作，為了能夠快速響應(yīng)，所以采用了異步寫(xiě)回磁盤(pán)的技術(shù)，寫(xiě)入內(nèi)存后就返回。但是這樣，會(huì)存在 crash后 內(nèi)存數(shù)據(jù)丟失的隱患，而 redo log 具備 crash safe 的能力。

15、為什么 redo log 具有 crash-safe 的能力，是 binlog 無(wú)法替代的？

第一點(diǎn)：redo log 可確保 innoDB 判斷哪些數(shù)據(jù)已經(jīng)刷盤(pán)，哪些數(shù)據(jù)還沒(méi)有

• redo log 和 binlog 有一個(gè)很大的區(qū)別就是，一個(gè)是循環(huán)寫(xiě)，一個(gè)是追加寫(xiě)。也就是說(shuō) redo log 只會(huì)記錄未刷盤(pán)的日志，已經(jīng)刷入磁盤(pán)的數(shù)據(jù)都會(huì)從 redo log 這個(gè)有限大小的日志文件里刪除。binlog 是追加日志，保存的是全量的日志。

• 當(dāng)數(shù)據(jù)庫(kù) crash 后，想要恢復(fù)未刷盤(pán)但已經(jīng)寫(xiě)入 redo log 和 binlog 的數(shù)據(jù)到內(nèi)存時(shí)，binlog 是無(wú)法恢復(fù)的。雖然 binlog 擁有全量的日志，但沒(méi)有一個(gè)標(biāo)志讓 innoDB 判斷哪些數(shù)據(jù)已經(jīng)刷盤(pán)，哪些數(shù)據(jù)還沒(méi)有。

• 但 redo log 不一樣，只要刷入磁盤(pán)的數(shù)據(jù)，都會(huì)從 redo log 中抹掉，因?yàn)槭茄h(huán)寫(xiě)！數(shù)據(jù)庫(kù)重啟后，直接把 redo log 中的數(shù)據(jù)都恢復(fù)至內(nèi)存就可以了。

第二點(diǎn)：如果 redo log 寫(xiě)入失敗，說(shuō)明此次操作失敗，事務(wù)也不可能提交

• redo log 每次更新操作完成后，就一定會(huì)寫(xiě)入日志，如果寫(xiě)入失敗，說(shuō)明此次操作失敗，事務(wù)也不可能提交。
• redo log 內(nèi)部結(jié)構(gòu)是基于頁(yè)的，記錄了這個(gè)頁(yè)的字段值變化，只要crash后讀取redo log進(jìn)行重放，就可以恢復(fù)數(shù)據(jù)。
• 這就是為什么 redo log 具有 crash-safe 的能力，而 binlog 不具備。

16、當(dāng)數(shù)據(jù)庫(kù) crash 后，如何恢復(fù)未刷盤(pán)的數(shù)據(jù)到內(nèi)存中？

根據(jù) redo log 和 binlog 的兩階段提交，未持久化的數(shù)據(jù)分為幾種情況：

• change buffer 寫(xiě)入，redo log 雖然做了 fsync 但未 commit，binlog 未 fsync 到磁盤(pán)，這部分?jǐn)?shù)據(jù)丟失。

• change buffer 寫(xiě)入，redo log fsync 未 commit，binlog 已經(jīng) fsync 到磁盤(pán)，先從 binlog 恢復(fù) redo log，再?gòu)?redo log 恢復(fù) change buffer。

• change buffer 寫(xiě)入，redo log 和 binlog 都已經(jīng) fsync，直接從 redo log 里恢復(fù)。

17、redo log 寫(xiě)入方式？

redo log包括兩部分內(nèi)容，分別是內(nèi)存中的日志緩沖(redo log buffer)和磁盤(pán)上的日志文件(redo log file)。

MySQL 每執(zhí)行一條 DML 語(yǔ)句，會(huì)先把記錄寫(xiě)入 redo log buffer（用戶(hù)空間） ，再保存到內(nèi)核空間的緩沖區(qū) OS-buffer 中，后續(xù)某個(gè)時(shí)間點(diǎn)再一次性將多個(gè)操作記錄寫(xiě)到 redo log file（刷盤(pán)） 。這種先寫(xiě)日志，再寫(xiě)磁盤(pán)的技術(shù)，就是WAL。

可以發(fā)現(xiàn)，redo log buffer寫(xiě)入到redo log file，是經(jīng)過(guò)OS buffer中轉(zhuǎn)的。其實(shí)可以通過(guò)參數(shù)innodb_flush_log_at_trx_commit進(jìn)行配置，參數(shù)值含義如下：

• 0：稱(chēng)為延遲寫(xiě)，事務(wù)提交時(shí)不會(huì)將redo log buffer中日志寫(xiě)入到OS buffer，而是每秒寫(xiě)入OS buffer并調(diào)用寫(xiě)入到redo log file中。
• 1：稱(chēng)為實(shí)時(shí)寫(xiě)，實(shí)時(shí)刷”，事務(wù)每次提交都會(huì)將redo log buffer中的日志寫(xiě)入OS buffer并保存到redo log file中。
• 2：稱(chēng)為實(shí)時(shí)寫(xiě)，延遲刷。每次事務(wù)提交寫(xiě)入到OS buffer，然后是每秒將日志寫(xiě)入到redo log file。

18、redo log 的執(zhí)行流程?

我們來(lái)看下Redo log的執(zhí)行流程，假設(shè)執(zhí)行的 SQL 如下：

update?T?set?a?=1?where?id?=666

1. MySQL 客戶(hù)端將請(qǐng)求語(yǔ)句 update T set a =1 where id =666，發(fā)往 MySQL Server 層。
2. MySQL Server 層接收到 SQL 請(qǐng)求后，對(duì)其進(jìn)行分析、優(yōu)化、執(zhí)行等處理工作，將生成的 SQL 執(zhí)行計(jì)劃發(fā)到 InnoDB 存儲(chǔ)引擎層執(zhí)行。
3. InnoDB 存儲(chǔ)引擎層將a修改為1的這個(gè)操作記錄到內(nèi)存中。
4. 記錄到內(nèi)存以后會(huì)修改 redo log 的記錄，會(huì)在添加一行記錄，其內(nèi)容是需要在哪個(gè)數(shù)據(jù)頁(yè)上做什么修改。
5. 此后，將事務(wù)的狀態(tài)設(shè)置為 prepare ，說(shuō)明已經(jīng)準(zhǔn)備好提交事務(wù)了。
6. 等到 MySQL Server 層處理完事務(wù)以后，會(huì)將事務(wù)的狀態(tài)設(shè)置為 commit，也就是提交該事務(wù)。
7. 在收到事務(wù)提交的請(qǐng)求以后，redo log 會(huì)把剛才寫(xiě)入內(nèi)存中的操作記錄寫(xiě)入到磁盤(pán)中，從而完成整個(gè)日志的記錄過(guò)程。

19、binlog 的概念是什么，起到什么作用， 可以保證 crash-safe 嗎?

• binlog 是歸檔日志，屬于 MySQL Server 層的日志?？梢詫?shí)現(xiàn)主從復(fù)制和數(shù)據(jù)恢復(fù)兩個(gè)作用。
• 當(dāng)需要恢復(fù)數(shù)據(jù)時(shí)，可以取出某個(gè)時(shí)間范圍內(nèi)的 binlog 進(jìn)行重放恢復(fù)。
• 但是 binlog 不可以做 crash safe，因?yàn)?crash 之前，binlog 可能沒(méi)有寫(xiě)入完全 MySQL 就掛了。所以需要配合 redo log 才可以進(jìn)行 crash safe。

20、什么是兩階段提交？

MySQL 將 redo log 的寫(xiě)入拆成了兩個(gè)步驟：prepare 和 commit，中間再穿插寫(xiě)入binlog，這就是"兩階段提交"。

而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。redolog 用于恢復(fù)主機(jī)故障時(shí)的未更新的物理數(shù)據(jù)，binlog 用于備份操作。兩者本身就是兩個(gè)獨(dú)立的個(gè)體，要想保持一致，就必須使用分布式事務(wù)的解決方案來(lái)處理。

為什么需要兩階段提交呢?

• 如果不用兩階段提交的話(huà)，可能會(huì)出現(xiàn)這樣情況
• 先寫(xiě) redo log，crash 后 bin log 備份恢復(fù)時(shí)少了一次更新，與當(dāng)前數(shù)據(jù)不一致。
• 先寫(xiě) bin log，crash 后，由于 redo log 沒(méi)寫(xiě)入，事務(wù)無(wú)效，所以后續(xù) bin log 備份恢復(fù)時(shí)，數(shù)據(jù)不一致。
• 兩階段提交就是為了保證 redo log 和 binlog 數(shù)據(jù)的安全一致性。只有在這兩個(gè)日志文件邏輯上高度一致了才能放心的使用。

在恢復(fù)數(shù)據(jù)時(shí)，redolog 狀態(tài)為 commit 則說(shuō)明 binlog 也成功，直接恢復(fù)數(shù)據(jù)；如果 redolog 是 prepare，則需要查詢(xún)對(duì)應(yīng)的 binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。

21、MySQL 怎么知道 binlog 是完整的?

一個(gè)事務(wù)的 binlog 是有完整格式的：

• statement 格式的 binlog，最后會(huì)有 COMMIT；
• row 格式的 binlog，最后會(huì)有一個(gè) XID event。

22、什么是 WAL 技術(shù)，有什么優(yōu)點(diǎn)？

WAL，中文全稱(chēng)是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是日志先寫(xiě)內(nèi)存，再寫(xiě)磁盤(pán)。MySQL 執(zhí)行更新操作后，在真正把數(shù)據(jù)寫(xiě)入到磁盤(pán)前，先記錄日志。

好處是不用每一次操作都實(shí)時(shí)把數(shù)據(jù)寫(xiě)盤(pán)，就算 crash 后也可以通過(guò)redo log 恢復(fù)，所以能夠?qū)崿F(xiàn)快速響應(yīng) SQL 語(yǔ)句。

23、binlog 日志的三種格式

binlog 日志有三種格式

• Statement：基于SQL語(yǔ)句的復(fù)制((statement-based replication,SBR))
• Row：基于行的復(fù)制。(row-based replication,RBR)
• Mixed：混合模式復(fù)制。(mixed-based replication,MBR)

Statement格式

每一條會(huì)修改數(shù)據(jù)的 SQL 都會(huì)記錄在 binlog 中

• 優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。
• 缺點(diǎn)：由于記錄的只是執(zhí)行語(yǔ)句，為了這些語(yǔ)句能在備庫(kù)上正確運(yùn)行，還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，以保證所有語(yǔ)句能在備庫(kù)得到和在主庫(kù)端執(zhí)行時(shí)候相同的結(jié)果。

Row格式

不記錄 SQL 語(yǔ)句上下文相關(guān)信息，僅保存哪條記錄被修改。

• 優(yōu)點(diǎn)：binlog 中可以不記錄執(zhí)行的 SQL 語(yǔ)句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程、或 function、或trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。
• 缺點(diǎn):可能會(huì)產(chǎn)生大量的日志內(nèi)容。

Mixed格式

實(shí)際上就是 Statement 與 Row 的結(jié)合。一般的語(yǔ)句修改使用 statment 格式保存 binlog，如一些函數(shù)，statement 無(wú)法完成主從復(fù)制的操作，則采用 row 格式保存 binlog，MySQL 會(huì)根據(jù)執(zhí)行的每一條具體的 SQL 語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式。

24、redo log日志格式

redo log buffer (內(nèi)存中)是由首尾相連的四個(gè)文件組成的，它們分別是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

• write pos 是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移，寫(xiě)到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開(kāi)頭。
• checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。
• write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來(lái)記錄新的操作。
• 如果 write pos 追上 checkpoint，表示“粉板”滿(mǎn)了，這時(shí)候不能再執(zhí)行新的更新，得停下來(lái)先擦掉一些記錄，把 checkpoint 推進(jìn)一下。
• 有了 redo log，當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)重啟后，可通過(guò) redo log將未落盤(pán)的數(shù)據(jù)（check point之后的數(shù)據(jù)）恢復(fù)，保證已經(jīng)提交的事務(wù)記錄不會(huì)丟失，這種能力稱(chēng)為crash-safe。

25、原本可以執(zhí)行得很快的 SQL 語(yǔ)句，執(zhí)行速度卻比預(yù)期的慢很多，原因是什么？如何解決？

原因：從大到小可分為四種情況

• MySQL 數(shù)據(jù)庫(kù)本身被堵住了，比如：系統(tǒng)或網(wǎng)絡(luò)資源不夠。
• SQL 語(yǔ)句被堵住了，比如：表鎖，行鎖等，導(dǎo)致存儲(chǔ)引擎不執(zhí)行對(duì)應(yīng)的 SQL 語(yǔ)句。
• 確實(shí)是索引使用不當(dāng)，沒(méi)有走索引。
• 表中數(shù)據(jù)的特點(diǎn)導(dǎo)致的，走了索引，但回表次數(shù)龐大。

解決：

• 考慮采用 force index 強(qiáng)行選擇一個(gè)索引
• 考慮修改語(yǔ)句，引導(dǎo) MySQL 使用我們期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，語(yǔ)義的邏輯是相同的。
• 第三種方法是，在有些場(chǎng)景下，可以新建一個(gè)更合適的索引，來(lái)提供給優(yōu)化器做選擇，或刪掉誤用的索引。
• 如果確定是索引根本沒(méi)必要，可以考慮刪除索引。

26、InnoDB 數(shù)據(jù)頁(yè)結(jié)構(gòu)

一個(gè)數(shù)據(jù)頁(yè)大致劃分七個(gè)部分

• File Header：表示頁(yè)的一些通用信息，占固定的38字節(jié)。
• page Header：表示數(shù)據(jù)頁(yè)專(zhuān)有信息，占固定的56字節(jié)。
• inimum+Supermum：兩個(gè)虛擬的偽記錄，分別表示頁(yè)中的最小記錄和最大記錄，占固定的26字節(jié)。
• User Records：真正存儲(chǔ)我們插入的數(shù)據(jù)，大小不固定。
• Free Space：頁(yè)中尚未使用的部分，大小不固定。
• Page Directory：頁(yè)中某些記錄的相對(duì)位置，也就是各個(gè)槽對(duì)應(yīng)的記錄在頁(yè)面中的地址偏移量。
• File Trailer：用于檢驗(yàn)頁(yè)是否完整，占固定大小 8 字節(jié)。

數(shù)據(jù)相關(guān)

27、MySQL 是如何保證數(shù)據(jù)不丟失的？

• 只要redolog 和 binlog 保證持久化磁盤(pán)就能確保MySQL異常重啟后回復(fù)數(shù)據(jù)
• 在恢復(fù)數(shù)據(jù)時(shí)，redolog 狀態(tài)為 commit 則說(shuō)明 binlog 也成功，直接恢復(fù)數(shù)據(jù)；如果 redolog 是 prepare，則需要查詢(xún)對(duì)應(yīng)的 binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。

28、誤刪數(shù)據(jù)怎么辦？

DBA 的最核心的工作就是保證數(shù)據(jù)的完整性，先要做好預(yù)防，預(yù)防的話(huà)大概是通過(guò)這幾個(gè)點(diǎn)：

• 權(quán)限控制與分配(數(shù)據(jù)庫(kù)和服務(wù)器權(quán)限)
• 制作操作規(guī)范
• 定期給開(kāi)發(fā)進(jìn)行培訓(xùn)
• 搭建延遲備庫(kù)
• 做好 SQL 審計(jì)，只要是對(duì)線(xiàn)上數(shù)據(jù)有更改操作的語(yǔ)句(DML和DDL)都需要進(jìn)行審核
• 做好備份。備份的話(huà)又分為兩個(gè)點(diǎn) (1)如果數(shù)據(jù)量比較大，用物理備份 xtrabackup。定期對(duì)數(shù)據(jù)庫(kù)進(jìn)行全量備份，也可以做增量備份。(2)如果數(shù)據(jù)量較少，用 mysqldump 或者 mysqldumper。再利用 binlog 來(lái)恢復(fù)或者搭建主從的方式來(lái)恢復(fù)數(shù)據(jù)。定期備份binlog 文件也是很有必要的
• 如果發(fā)生了數(shù)據(jù)刪除的操作，又可以從以下幾個(gè)點(diǎn)來(lái)恢復(fù):
• DML 誤操作語(yǔ)句造成數(shù)據(jù)不完整或者丟失。可以通過(guò) flashback，美團(tuán)的 myflash，也是一個(gè)不錯(cuò)的工具，本質(zhì)都差不多
• 都是先解析 binlog event，然后在進(jìn)行反轉(zhuǎn)。把 delete 反轉(zhuǎn)為insert，insert 反轉(zhuǎn)為 delete，update前后 image 對(duì)調(diào)。
• 所以必須設(shè)置binlog_format=row 和 binlog_row_image=full，切記恢復(fù)數(shù)據(jù)的時(shí)候，應(yīng)該先恢復(fù)到臨時(shí)的實(shí)例，然后在恢復(fù)回主庫(kù)上。
• DDL語(yǔ)句誤操作(truncate和drop)，由于DDL語(yǔ)句不管 binlog_format 是 row 還是 statement ，在 binlog 里都只記錄語(yǔ)句，不記錄 image 所以恢復(fù)起來(lái)相對(duì)要麻煩得多。
• 只能通過(guò)全量備份+應(yīng)用 binlog 的方式來(lái)恢復(fù)數(shù)據(jù)。一旦數(shù)據(jù)量比較大，那么恢復(fù)時(shí)間就特別長(zhǎng)
• rm 刪除：使用備份跨機(jī)房，或者最好是跨城市保存。

29、drop、truncate 和 delete 的區(qū)別

• DELETE 語(yǔ)句執(zhí)行刪除的過(guò)程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。
• TRUNCATE TABLE ?則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過(guò)程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。
• drop語(yǔ)句將表所占用的空間全釋放掉。
• 在速度上，一般來(lái)說(shuō)，drop> truncate > delete。
• 如果想刪除部分?jǐn)?shù)據(jù)用 delete，注意帶上 where 子句，回滾段要足夠大；
• 如果想刪除表，當(dāng)然用 drop；如果想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無(wú)關(guān)，用 truncate 即可；
• 如果和事務(wù)有關(guān)，或者想觸發(fā) trigger，還是用 delete；如果是整理表內(nèi)部的碎片，可以用 truncate 跟上 reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。

30、在 MySQL 中有兩個(gè) kill 命令

• 一個(gè)是 kill query + 線(xiàn)程 id，表示終止這個(gè)線(xiàn)程中正在執(zhí)行的語(yǔ)句
• 一個(gè)是 kill connection + 線(xiàn)程 id，這里 connection 可缺省，表示斷開(kāi)這個(gè)線(xiàn)程的連接

kill 不掉的原因

• kill命令被堵了，還沒(méi)到位
• kill命令到位了，但是沒(méi)被立刻觸發(fā)
• kill命令被觸發(fā)了，但執(zhí)行完也需要時(shí)間

31、如何理解 MySQL 的邊讀邊發(fā)

• 如果客戶(hù)端接受慢，會(huì)導(dǎo)致 MySQL 服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間會(huì)很長(zhǎng)。
• 服務(wù)端并不需要保存一個(gè)完整的結(jié)果集，取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程都是通過(guò)一個(gè) next_buffer 來(lái)操作的。
• 內(nèi)存的數(shù)據(jù)頁(yè)都是在 Buffer_Pool中操作的。
• InnoDB 管理 Buffer_Pool 使用的是改進(jìn)的 LRU 算法，使用鏈表實(shí)現(xiàn)，實(shí)現(xiàn)上，按照 5:3 的比例把整個(gè) LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域。

32、MySQL 的大表查詢(xún)?yōu)槭裁床粫?huì)爆內(nèi)存？

• 由于 MySQL 是邊讀變發(fā)，因此對(duì)于數(shù)據(jù)量很大的查詢(xún)結(jié)果來(lái)說(shuō)，不會(huì)再 server 端保存完整的結(jié)果集，所以，如果客戶(hù)端讀結(jié)果不及時(shí)，會(huì)堵住 MySQL 的查詢(xún)過(guò)程，但是不會(huì)把內(nèi)存打爆。
• InnoDB 引擎內(nèi)部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改進(jìn)的 LRU 算法，使用鏈表實(shí)現(xiàn)，實(shí)現(xiàn)上，按照 5:3 的比例把整個(gè) LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域。對(duì)冷數(shù)據(jù)的全掃描，影響也能做到可控制。

33、MySQL 臨時(shí)表的用法和特性

• 只對(duì)當(dāng)前session可見(jiàn)。
• 可以與普通表重名。
• 增刪改查用的是臨時(shí)表。
• show tables 不顯示普通表。
• 在實(shí)際應(yīng)用中，臨時(shí)表一般用于處理比較復(fù)雜的計(jì)算邏輯。
• 由于臨時(shí)表是每個(gè)線(xiàn)程自己可見(jiàn)的，所以不需要考慮多個(gè)線(xiàn)程執(zhí)行同一個(gè)處理時(shí)臨時(shí)表的重名問(wèn)題，在線(xiàn)程退出的時(shí)候，臨時(shí)表會(huì)自動(dòng)刪除。

34、MySQL 存儲(chǔ)引擎介紹（InnoDB、MyISAM、MEMORY）

• InnoDB 是事務(wù)型數(shù)據(jù)庫(kù)的首選引擎，支持事務(wù)安全表 (ACID)，支持行鎖定和外鍵。MySQL5.5.5 之后，InnoDB 作為默認(rèn)存儲(chǔ)引擎
• MyISAM 基于 ISAM 的存儲(chǔ)引擎，并對(duì)其進(jìn)行擴(kuò)展。它是在 Web、數(shù)據(jù)存儲(chǔ)和其他應(yīng)用環(huán)境下最常用的存儲(chǔ)引擎之一。MyISAM 擁有較高的插入、查詢(xún)速度，但不支持事務(wù)。在 MySQL5.5.5 之前的版本中，MyISAM 是默認(rèn)存儲(chǔ)引擎
• MEMORY 存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，為查詢(xún)和引用其他表數(shù)據(jù)提供快速訪(fǎng)問(wèn)。

35、都說(shuō) InnoDB 好，那還要不要使用 MEMORY 引擎？

• 內(nèi)存表就是使用 memory 引擎創(chuàng)建的表
• 為什么我不建議你在生產(chǎn)環(huán)境上使用內(nèi)存表。這里的原因主要包括兩個(gè)方面：鎖粒度問(wèn)題；數(shù)據(jù)持久化問(wèn)題。
• 由于重啟會(huì)丟數(shù)據(jù)，如果一個(gè)備庫(kù)重啟，會(huì)導(dǎo)致主備同步線(xiàn)程停止；如果主庫(kù)跟這個(gè)備庫(kù)是雙 M 架構(gòu)，還可能導(dǎo)致主庫(kù)的內(nèi)存表數(shù)據(jù)被刪掉。

36、如果數(shù)據(jù)庫(kù)誤操作, 如何執(zhí)行數(shù)據(jù)恢復(fù)?

數(shù)據(jù)庫(kù)在某個(gè)時(shí)候誤操作，就可以找到距離誤操作最近的時(shí)間節(jié)點(diǎn)的bin log，重放到臨時(shí)數(shù)據(jù)庫(kù)里，然后選擇誤刪的數(shù)據(jù)節(jié)點(diǎn)，恢復(fù)到線(xiàn)上數(shù)據(jù)庫(kù)。

主從備份相關(guān)

37、MySQL 是如何保證主備同步？

主備關(guān)系的建立：

• 一開(kāi)始創(chuàng)建主備關(guān)系的時(shí)候，是由備庫(kù)指定的，比如基于位點(diǎn)的主備關(guān)系，備庫(kù)說(shuō)“我要從binlog文件A的位置P”開(kāi)始同步，主庫(kù)就從這個(gè)指定的位置開(kāi)始往后發(fā)。
• 而主備關(guān)系搭建之后，是主庫(kù)決定要發(fā)給數(shù)據(jù)給備庫(kù)的，所以主庫(kù)有新的日志也會(huì)發(fā)給備庫(kù)。

MySQL 主備切換流程：

• 客戶(hù)端讀寫(xiě)都是直接訪(fǎng)問(wèn)A，而節(jié)點(diǎn)B是備庫(kù)，只要將A的更新都同步過(guò)來(lái)，到本地執(zhí)行就可以保證數(shù)據(jù)是相同的。
• 當(dāng)需要切換的時(shí)候就把節(jié)點(diǎn)換一下，A的節(jié)點(diǎn)B的備庫(kù)

一個(gè)事務(wù)完整的同步過(guò)程：

• 備庫(kù)B和主庫(kù)A建立來(lái)了長(zhǎng)鏈接，主庫(kù)A內(nèi)部專(zhuān)門(mén)線(xiàn)程用于維護(hù)了這個(gè)長(zhǎng)鏈接。

• 在備庫(kù)B上通過(guò)changemaster命令設(shè)置主庫(kù)A的IP端口用戶(hù)名密碼以及從哪個(gè)位置開(kāi)始請(qǐng)求binlog包括文件名和日志偏移量

• 在備庫(kù)B上執(zhí)行start-slave命令備庫(kù)會(huì)啟動(dòng)兩個(gè)線(xiàn)程：io_thread和sql_thread分別負(fù)責(zé)建立連接和讀取中轉(zhuǎn)日志進(jìn)行解析執(zhí)行

• 備庫(kù)讀取主庫(kù)傳過(guò)來(lái)的binlog文件備庫(kù)收到文件寫(xiě)到本地成為中轉(zhuǎn)日志

• 后來(lái)由于多線(xiàn)程復(fù)制方案的引入，sql_thread演化成了多個(gè)線(xiàn)程。

38、什么是主備延遲

主庫(kù)和備庫(kù)在執(zhí)行同一個(gè)事務(wù)的時(shí)候出現(xiàn)時(shí)間差的問(wèn)題，主要原因有：

• 有些部署條件下，備庫(kù)所在機(jī)器的性能要比主庫(kù)性能差。
• 備庫(kù)的壓力較大。
• 大事務(wù)，一個(gè)主庫(kù)上語(yǔ)句執(zhí)行10分鐘，那么這個(gè)事務(wù)可能會(huì)導(dǎo)致從庫(kù)延遲10分鐘。

39、為什么要有多線(xiàn)程復(fù)制策略？

• 因?yàn)閱尉€(xiàn)程復(fù)制的能力全面低于多線(xiàn)程復(fù)制，對(duì)于更新壓力較大的主庫(kù)，備庫(kù)可能是一直追不上主庫(kù)的，帶來(lái)的現(xiàn)象就是備庫(kù)上seconds_behind_master值越來(lái)越大。
• 在實(shí)際應(yīng)用中，建議使用可靠性?xún)?yōu)先策略，減少主備延遲，提升系統(tǒng)可用性，盡量減少大事務(wù)操作，把大事務(wù)拆分小事務(wù)。

40、MySQL 的并行策略有哪些？

• 按表分發(fā)策略：如果兩個(gè)事務(wù)更新不同的表，它們就可以并行。因?yàn)閿?shù)據(jù)是存儲(chǔ)在表里的，所以按表分發(fā)，可以保證兩個(gè) worker 不會(huì)更新同一行。缺點(diǎn)：如果碰到熱點(diǎn)表，比如所有的更新事務(wù)都會(huì)涉及到某一個(gè)表的時(shí)候，所有事務(wù)都會(huì)被分配到同一個(gè) worker 中，就變成單線(xiàn)程復(fù)制了。
• 按行分發(fā)策略：如果兩個(gè)事務(wù)沒(méi)有更新相同的行，它們?cè)趥鋷?kù)上可以并行。如果兩個(gè)事務(wù)沒(méi)有更新相同的行，它們?cè)趥鋷?kù)上可以并行執(zhí)行。顯然，這個(gè)模式要求 binlog 格式必須是 row。缺點(diǎn)：相比于按表并行分發(fā)策略，按行并行策略在決定線(xiàn)程分發(fā)的時(shí)候，需要消耗更多的計(jì)算資源。

41、MySQL的一主一備和一主多從有什么區(qū)別？

在一主一備的雙 M 架構(gòu)里，主備切換只需要把客戶(hù)端流量切到備庫(kù)；而在一主多從架構(gòu)里，主備切換除了要把客戶(hù)端流量切到備庫(kù)外，還需要把從庫(kù)接到新主庫(kù)上。

42、主庫(kù)出問(wèn)題如何解決?

• 基于位點(diǎn)的主備切換：存在找同步位點(diǎn)這個(gè)問(wèn)題
• MySQL 5.6 版本引入了 GTID，徹底解決了這個(gè)困難。那么，GTID 到底是什么意思，又是如何解決找同步位點(diǎn)這個(gè)問(wèn)題呢？
• GTID：全局事務(wù) ID，是一個(gè)事務(wù)在提交的時(shí)候生成的，是這個(gè)事務(wù)的唯一標(biāo)識(shí)；它由兩部分組成，格式是：GTID=server_uuid:gno
• 每個(gè) MySQL 實(shí)例都維護(hù)了一個(gè) GTID 集合，用來(lái)對(duì)應(yīng)“這個(gè)實(shí)例執(zhí)行過(guò)的所有事務(wù)”。
• 在基于 GTID 的主備關(guān)系里，系統(tǒng)認(rèn)為只要建立主備關(guān)系，就必須保證主庫(kù)發(fā)給備庫(kù)的日志是完整的。因此，如果實(shí)例 B 需要的日志已經(jīng)不存在，A’就拒絕把日志發(fā)給 B。

43、MySQL 讀寫(xiě)分離涉及到過(guò)期讀問(wèn)題的幾種解決方案?

• 強(qiáng)制走主庫(kù)方案
• sleep 方案
• 判斷主備無(wú)延遲方案
• 配合 semi-sync 方案
• 等主庫(kù)位點(diǎn)方案
• GTID 方案。
• 實(shí)際生產(chǎn)中，先客戶(hù)端對(duì)請(qǐng)求做分類(lèi)，區(qū)分哪些請(qǐng)求可以接受過(guò)期讀，而哪些請(qǐng)求完全不能接受過(guò)期讀；然后，對(duì)于不能接受過(guò)期讀的語(yǔ)句，再使用等 GTID 或等位點(diǎn)的方案。

44、MySQL的并發(fā)鏈接和并發(fā)查詢(xún)有什么區(qū)別？

• 在執(zhí)行show processlist的結(jié)果里，看到了幾千個(gè)連接，指的是并發(fā)連接。而"當(dāng)前正在執(zhí)行"的語(yǔ)句，才是并發(fā)查詢(xún)。
• 并發(fā)連接數(shù)多影響的是內(nèi)存，并發(fā)查詢(xún)太高對(duì)CPU不利。一個(gè)機(jī)器的CPU核數(shù)有限，線(xiàn)程全沖進(jìn)來(lái)，上下文切換的成本就會(huì)太高。
• 所以需要設(shè)置參數(shù)：innodb_thread_concurrency 用來(lái)限制線(xiàn)程數(shù)，當(dāng)線(xiàn)程數(shù)達(dá)到該參數(shù)，InnoDB就會(huì)認(rèn)為線(xiàn)程數(shù)用完了，會(huì)阻止其他語(yǔ)句進(jìn)入引擎執(zhí)行。

性能相關(guān)

45、短時(shí)間提高 MySQL 性能的方法

• 第一種方法：先處理掉那些占著連接但是不工作的線(xiàn)程?；蛘咴倏紤]斷開(kāi)事務(wù)內(nèi)空閑太久的連接。kill connection + id
• 第二種方法：減少連接過(guò)程的消耗：慢查詢(xún)性能問(wèn)題在 MySQL 中，會(huì)引發(fā)性能問(wèn)題的慢查詢(xún)，大體有以下三種可能：索引沒(méi)有設(shè)計(jì)好；SQL 語(yǔ)句沒(méi)寫(xiě)好；MySQL 選錯(cuò)了索引（force index）。

46、為什么 MySQL 自增主鍵 ID 不連續(xù)？

• 唯一鍵沖突

• 事務(wù)回滾

• 自增主鍵的批量申請(qǐng)

• 深層次原因是：MySQL 不判斷自增主鍵是否存在，從而減少加鎖的時(shí)間范圍和粒度，這樣能保持更高的性能，確保自增主鍵不能回退，所以才有自增主鍵不連續(xù)。

• 自增主鍵怎么做到唯一性？自增值加1來(lái)通過(guò)自增鎖控制并發(fā)。

47、InnoDB 為什么要用自增 ID 作為主鍵？

• 自增主鍵的插入模式，符合遞增插入，每次都是追加操作，不涉及挪動(dòng)記錄，也不會(huì)觸發(fā)葉子節(jié)點(diǎn)的分裂。

• 每次插入新的記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫(xiě)滿(mǎn)，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)。

• 而有業(yè)務(wù)邏輯的字段做主鍵，不容易保證有序插入，由于每次插入主鍵的值近似于隨機(jī)

• 因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置， 頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，寫(xiě)數(shù)據(jù)成本較高。

48、如何最快的復(fù)制一張表？

• 為了避免對(duì)源表加讀鎖，更穩(wěn)妥的方案是先將數(shù)據(jù)寫(xiě)到外部文本文件，然后再寫(xiě)回目標(biāo)表
• 一種方法是，使用 mysqldump 命令將數(shù)據(jù)導(dǎo)出成一組 INSERT 語(yǔ)句
• 另一種方法是直接將結(jié)果導(dǎo)出成.csv 文件。MySQL 提供語(yǔ)法，用來(lái)將查詢(xún)結(jié)果導(dǎo)出到服務(wù)端本地目錄：select * from db1.t where a>900 into outfile '/server_tmp/t.csv';得到.csv 導(dǎo)出文件后，你就可以用下面的 load data 命令將數(shù)據(jù)導(dǎo)入到目標(biāo)表 db2.t 中：load data infile '/server_tmp/t.csv' into table db2.t;
• 物理拷貝：在 MySQL 5.6 版本引入了可傳輸表空間(transportable tablespace) 的方法，可以通過(guò)導(dǎo)出 + 導(dǎo)入表空間的方式，實(shí)現(xiàn)物理拷貝表的功能。

49、grant 和 flush privileges語(yǔ)句

• grant語(yǔ)句會(huì)同時(shí)修改數(shù)據(jù)表和內(nèi)存，判斷權(quán)限的時(shí)候使用的內(nèi)存數(shù)據(jù)，因此，規(guī)范使用是不需要加上 flush privileges 語(yǔ)句。
• flush privileges 語(yǔ)句本身會(huì)用數(shù)據(jù)表的數(shù)據(jù)重建一份內(nèi)存權(quán)限數(shù)據(jù)，所以在權(quán)限數(shù)據(jù)可能存在不一致的情況下再使用。

50、要不要使用分區(qū)表？

• 分區(qū)并不是越細(xì)越好。實(shí)際上，單表或者單分區(qū)的數(shù)據(jù)一千萬(wàn)行，只要沒(méi)有特別大的索引，對(duì)于現(xiàn)在的硬件能力來(lái)說(shuō)都已經(jīng)是小表了。
• 分區(qū)也不要提前預(yù)留太多，在使用之前預(yù)先創(chuàng)建即可。比如，如果是按月分區(qū)，每年年底時(shí)再把下一年度的 12 個(gè)新分區(qū)創(chuàng)建上即可。對(duì)于沒(méi)有數(shù)據(jù)的歷史分區(qū)，要及時(shí)的 drop 掉。

51、join 用法

• 使用 left join 左邊的表不一定是驅(qū)動(dòng)表
• 如果需要 left join 的語(yǔ)義，就不能把被驅(qū)動(dòng)表的字段放在 where 條件里面做等值判斷或不等值判斷，必須都寫(xiě)在 on 里面
• 標(biāo)準(zhǔn)的 group by 語(yǔ)句，是需要在 select 部分加一個(gè)聚合函數(shù)，比如select a,count(*) from t group by a order by null;

52、MySQL 有哪些自增ID？各自場(chǎng)景是什么？

• 表的自增 ID 達(dá)到上限之后，在申請(qǐng)值不會(huì)變化，進(jìn)而導(dǎo)致聯(lián)系插入數(shù)據(jù)的時(shí)候報(bào)主鍵沖突錯(cuò)誤。

• row_id 達(dá)到上限之后，歸 0 在重新遞增，如果出現(xiàn)相同的 row_id 后寫(xiě)的數(shù)據(jù)會(huì)覆蓋之前的數(shù)據(jù)。

• Xid 只需要不在同一個(gè) binlog 文件出現(xiàn)重復(fù)值即可，理論上會(huì)出現(xiàn)重復(fù)值，但概率極小可忽略不計(jì)。

• InnoDB 的 max_trx_id 遞增值每次 MySQL 重啟會(huì)保存起來(lái)。

• Xid 是由 server 層維護(hù)的。InnoDB 內(nèi)部使用 Xid，就是為了能夠在 InnoDB 事務(wù)和 server 之間做關(guān)聯(lián)。但是，InnoDB 自己的 trx_id，是另外維護(hù)的。

• thread_id 是我們使用中最常見(jiàn)的，而且也是處理得最好的一個(gè)自增 id 邏輯了。使用了insert_unique算法

53、Xid 在 MySQL 內(nèi)部是怎么生成的呢？

MySQL 內(nèi)部維護(hù)了一個(gè)全局變量 global_query_id，每次執(zhí)行語(yǔ)句（包括select語(yǔ)句）的時(shí)候?qū)⑺x值給 Query_id，然后給這個(gè)變量加 1。如果當(dāng)前語(yǔ)句是這個(gè)事務(wù)執(zhí)行的第一條語(yǔ)句，那么 MySQL 還會(huì)同時(shí)把 Query_id 賦值給這個(gè)事務(wù)的 Xid。

而 global_query_id 是一個(gè)純內(nèi)存變量，重啟之后就清零了。所以你就知道了，在同一個(gè)數(shù)據(jù)庫(kù)實(shí)例中，不同事務(wù)的 Xid 也是有可能相同的。但是 MySQL 重啟之后會(huì)重新生成新的 binlog 文件，這就保證了，同一個(gè) binlog 文件里，Xid 一定是惟一的。

鎖相關(guān)

54、說(shuō)一下 MySQL 的鎖

• MySQL 在 server 層 和 存儲(chǔ)引擎層 都運(yùn)用了大量的鎖
• MySQL server 層需要講兩種鎖，第一種是MDL(metadata lock) 元數(shù)據(jù)鎖，第二種則 Table Lock 表鎖。
• MDL 又名元數(shù)據(jù)鎖，那么什么是元數(shù)據(jù)呢，任何描述數(shù)據(jù)庫(kù)的內(nèi)容就是元數(shù)據(jù)，比如我們的表結(jié)構(gòu)、庫(kù)結(jié)構(gòu)等都是元數(shù)據(jù)。那為什么需要 MDL 呢？
• 主要解決兩個(gè)問(wèn)題：事務(wù)隔離問(wèn)題；數(shù)據(jù)復(fù)制問(wèn)題
• InnoDB 有五種表級(jí)鎖：IS（意向讀鎖）；IX（意向?qū)戞i）；S（讀）；X（寫(xiě)）；AUTO-INC
• 在對(duì)表進(jìn)行select/insert/delete/update語(yǔ)句時(shí)候不會(huì)加表級(jí)鎖
• IS和IX的作用是為了判斷表中是否有已經(jīng)被加鎖的記錄
• 自增主鍵的保障就是有 AUTO-INC 鎖，是語(yǔ)句級(jí)別的：為表的某個(gè)列添加 AUTO_INCREMENT 屬性，之后在插?記錄時(shí)，可以不指定該列的值，系統(tǒng)會(huì)?動(dòng)為它賦上單調(diào)遞增的值。
• InnoDB 4 種行級(jí)鎖
• RecordLock：記錄鎖
• GapLock：間隙鎖解決幻讀；前一次查詢(xún)不存在的東西在下一次查詢(xún)出現(xiàn)了，其實(shí)就是事務(wù)A中的兩次查詢(xún)之間事務(wù)B執(zhí)行插入操作被事務(wù)A感知了
• Next-KeyLock：鎖住某條記錄又想阻止其它事務(wù)在改記錄前面的間隙插入新紀(jì)錄
• InsertIntentionLock：插入意向鎖;如果插入到同一行間隙中的多個(gè)事務(wù)未插入到間隙內(nèi)的同一位置則無(wú)須等待
• 行鎖和表鎖的抉擇
• 全表掃描用行級(jí)鎖

55、什么是幻讀？

值在同一個(gè)事務(wù)中，存在前后兩次查詢(xún)同一個(gè)范圍的數(shù)據(jù)，第二次看到了第一次沒(méi)有查詢(xún)到的數(shù)據(jù)。

幻讀出現(xiàn)的場(chǎng)景：

• 事務(wù)的隔離級(jí)別是可重復(fù)讀，且是當(dāng)前讀。
• 幻讀指新插入的行。

幻讀帶來(lái)的問(wèn)題：

• 對(duì)行鎖語(yǔ)義的破壞
• 破壞了數(shù)據(jù)一致性

解決：

• 加間隙鎖，鎖住行與行之間的間隙，阻塞新插入的操作。
• 帶來(lái)的問(wèn)題：降低并發(fā)度，可能導(dǎo)致死鎖。

其它為什么系列

56、為什么 MySQL 會(huì)抖一下？

• 臟頁(yè)會(huì)被后臺(tái)線(xiàn)程自動(dòng) flush，也會(huì)由于數(shù)據(jù)頁(yè)淘汰而觸發(fā) flush，而刷臟頁(yè)的過(guò)程由于會(huì)占用資源，可能會(huì)讓你的更新和查詢(xún)語(yǔ)句的響應(yīng)時(shí)間長(zhǎng)一些。

57、為什么刪除了表，表文件的大小還是沒(méi)變？

• 數(shù)據(jù)項(xiàng)刪除之后 InnoDB 某個(gè)頁(yè) page A 會(huì)被標(biāo)記為可復(fù)用。
• delete 命令把整個(gè)表的數(shù)據(jù)刪除，結(jié)果就是，所有的數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用。但是磁盤(pán)上，文件不會(huì)變小。
• 經(jīng)過(guò)大量增刪改的表，都是可能是存在空洞的。這些空洞也占空間所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。
• 重建表，就可以達(dá)到這樣的目的?？梢允褂?alter table A engine=InnoDB 命令來(lái)重建表。

58、count(*)實(shí)現(xiàn)方式以及各種 count 對(duì)比

• 對(duì)于 count(主鍵 id) 來(lái)說(shuō)，InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來(lái)，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。
• 對(duì)于 count(1) 來(lái)說(shuō)，InnoDB 引擎遍歷整張表，但不取值。server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，判斷是不可能為空的，按行累加。單看這兩個(gè)用法的差別的話(huà)，你能對(duì)比出來(lái)，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會(huì)涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。
• 對(duì)于 count(字段) 來(lái)說(shuō)：如果這個(gè)“字段”是定義為 not null 的話(huà)，一行行地從記錄里面讀出這個(gè)字段，判斷不能為 null，按行累加；如果這個(gè)“字段”定義允許為 null，那么執(zhí)行的時(shí)候，判斷到有可能是 null，還要把值取出來(lái)再判斷一下，不是 null 才累加。也就是前面的第一條原則，server 層要什么字段，InnoDB 就返回什么字段。
• 但是 count * 是例外，并不會(huì)把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化，不取值。count(*) 肯定不是 null，按行累加。
• 所以結(jié)論是：按照效率排序的話(huà)，count(字段)

59、orderby 排序內(nèi)部原理

• MySQL 會(huì)為每個(gè)線(xiàn)程分配一個(gè)內(nèi)存（sort-buffer）用于排序該內(nèi)存大小為 sort_buffer_size；

• 如果排序的數(shù)據(jù)量小于 sort_buffer_size，排序就會(huì)在內(nèi)存中完成；

  內(nèi)部排序分為兩種

• 全字段排序：到索引樹(shù)上找到滿(mǎn)足條件的主鍵ID根據(jù)主鍵ID去取出數(shù)據(jù)放到sort_buffer然后進(jìn)行快速排序

• rowid排序：通過(guò)控制排序的行數(shù)據(jù)的長(zhǎng)度來(lái)讓sort_buffer中盡可能多的存放數(shù)據(jù)

• 如果數(shù)據(jù)量很大，內(nèi)存中無(wú)法存下這么多，就會(huì)使用磁盤(pán)臨時(shí)文件來(lái)輔助排序，稱(chēng)為外部排序；

• 外部排序，MySQL會(huì)分為好幾份單獨(dú)的臨時(shí)文件來(lái)存放排序后的數(shù)據(jù)，一般是磁盤(pán)文件中進(jìn)行歸并，然后將這些文件合并成一個(gè)大文件；

60、如何高效的使用 MySQL 顯式隨機(jī)消息

• 隨機(jī)取出 Y1,Y2,Y3之后，算出Ymax,Ymin

• 得到id集后算出Y1、Y2、Y3對(duì)應(yīng)的三個(gè)id 最后 select * from t where id in (id1, id2, id3) 這樣掃描的行數(shù)應(yīng)該是C+Ymax+3

  mysql> select count(*) into @C from t;
  set @Y1 = floor(@C * rand());
  set @Y2 = floor(@C * rand());
  set @Y3 = floor(@C * rand());
  Ymax = max(Y1,Y2,Y3)
  Ymin = min(Y1,Y2,Y3)
  select id from t limit Ymin，(Ymax - Ymin)
  

持續(xù)更新中。

參考：

• 極客時(shí)間《MySQL實(shí)戰(zhàn) 45 講》

• https://www.nowcoder.com/discuss/744934?type=1&order=0&pos=25&page=1&ncTraceId=&channel=-1&source_id=discuss_tag_nctrack

今天的嘮嗑就到這里了。

我是敖丙，我們下期見(jiàn)！