面試官：看你簡(jiǎn)歷上寫了 MySQL，我們來切磋下
大家好，我是二哥呀。就 Java 后端的面試來說，MySQL 是必然會(huì)考的內(nèi)容，尤其像索引、binlog，更是 MySQL 的靈魂。我在 G 哥的星球也看到不少打卡 MySQL 的，于是我就想，趁這個(gè)機(jī)會(huì)，給大家肝一波 MySQL 的高頻面試題。
不多 BB，寫了整整一個(gè)月的 MySQL 面試題，60 道，分為基礎(chǔ)篇、索引篇、日志篇、數(shù)據(jù)篇、主從備份篇、性能篇、鎖篇、其他篇等等 8 大部分，基本上能覆蓋掉 MySQL 的高頻題型。
先來一副思維導(dǎo)圖震撼下，搞起。
一、 基礎(chǔ)篇
1、關(guān)系型和非關(guān)系型數(shù)據(jù)庫的區(qū)別？
關(guān)系型數(shù)據(jù)庫的優(yōu)點(diǎn)
容易理解，因?yàn)樗捎昧岁P(guān)系模型來組織數(shù)據(jù)。 可以保持?jǐn)?shù)據(jù)的一致性。 數(shù)據(jù)更新的開銷比較小。 支持復(fù)雜查詢（帶 where 子句的查詢）
非關(guān)系型數(shù)據(jù)庫（NOSQL）的優(yōu)點(diǎn)
無需經(jīng)過 SQL 層的解析，讀寫效率高。 基于鍵值對(duì)，讀寫性能很高，易于擴(kuò)展 可以支持多種類型數(shù)據(jù)的存儲(chǔ)，如圖片，文檔等等。 擴(kuò)展（可分為內(nèi)存性數(shù)據(jù)庫以及文檔型數(shù)據(jù)庫，比如 Redis，MongoDB，HBase 等，適合場(chǎng)景：數(shù)據(jù)量大高可用的日志系統(tǒng)/地理位置存儲(chǔ)系統(tǒng)）。
2、詳細(xì)說一下一條 MySQL 語句執(zhí)行的步驟
Server 層按順序執(zhí)行 SQL 的步驟為：
客戶端請(qǐng)求 -> 連接器（驗(yàn)證用戶身份，給予權(quán)限） 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作） 分析器（對(duì) SQL 進(jìn)行詞法分析和語法分析操作） 優(yōu)化器（主要對(duì)執(zhí)行的 SQL 優(yōu)化選擇最優(yōu)的執(zhí)行方案方法） 執(zhí)行器（執(zhí)行時(shí)會(huì)先看用戶是否有執(zhí)行權(quán)限，有才去使用這個(gè)引擎提供的接口）-> 去引擎層獲取數(shù)據(jù)返回（如果開啟查詢緩存則會(huì)緩存查詢結(jié)果）
二、索引篇
3、MySQL 使用索引的原因？
根本原因
索引的出現(xiàn)，就是為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣。 對(duì)于數(shù)據(jù)庫的表而言，索引其實(shí)就是它的“目錄”。
擴(kuò)展
創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。 幫助引擎層避免排序和臨時(shí)表 將隨機(jī) IO 變?yōu)轫樞?IO，加速表和表之間的連接。
4、索引的三種常見底層數(shù)據(jù)結(jié)構(gòu)以及優(yōu)缺點(diǎn)
三種常見的索引底層數(shù)據(jù)結(jié)構(gòu)：分別是哈希表、有序數(shù)組和搜索樹。
哈希表這種適用于等值查詢的場(chǎng)景，比如 memcached 以及其它一些 NoSQL 引擎，不適合范圍查詢。 有序數(shù)組索引只適用于靜態(tài)存儲(chǔ)引擎，等值和范圍查詢性能好，但更新數(shù)據(jù)成本高。 N 叉樹由于讀寫上的性能優(yōu)點(diǎn)以及適配磁盤訪問模式以及廣泛應(yīng)用在數(shù)據(jù)庫引擎中。 擴(kuò)展（以 InnoDB 的一個(gè)整數(shù)字段索引為例，這個(gè) N 差不多是 1200?？脴涓呤?4 的時(shí)候，就可以存 1200 的 3 次方個(gè)值，這已經(jīng) 17 億了?？紤]到樹根的數(shù)據(jù)塊總是在內(nèi)存中的，一個(gè) 10 億行的表上一個(gè)整數(shù)字段的索引，查找一個(gè)值最多只需要訪問 3 次磁盤。其實(shí)，樹的第二層也有很大概率在內(nèi)存中，那么訪問磁盤的平均次數(shù)就更少了。）
5、索引的常見類型以及它是如何發(fā)揮作用的？
根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。
主鍵索引的葉子節(jié)點(diǎn)存的整行數(shù)據(jù)，在InnoDB里也被稱為聚簇索引。 非主鍵索引葉子節(jié)點(diǎn)存的主鍵的值，在InnoDB里也被稱為二級(jí)索引。
6、MyISAM 和 InnoDB 實(shí)現(xiàn) B 樹索引方式的區(qū)別是什么？
InnoDB 存儲(chǔ)引擎：B+ 樹索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)本身，其數(shù)據(jù)文件本身就是索引文件。 MyISAM 存儲(chǔ)引擎：B+ 樹索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)的物理地址，葉節(jié)點(diǎn)的 data 域存放的是數(shù)據(jù)記錄的地址，索引文件和數(shù)據(jù)文件是分離的。
7、InnoDB 為什么設(shè)計(jì) B+ 樹索引？
兩個(gè)考慮因素：
InnoDB 需要執(zhí)行的場(chǎng)景和功能需要在特定查詢上擁有較強(qiáng)的性能。 CPU 將磁盤上的數(shù)據(jù)加載到內(nèi)存中需要花費(fèi)大量時(shí)間。
為什么選擇 B+ 樹：
哈希索引雖然能提供O（1）復(fù)雜度查詢，但對(duì)范圍查詢和排序卻無法很好的支持，最終會(huì)導(dǎo)致全表掃描。
B 樹能夠在非葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，但會(huì)導(dǎo)致在查詢連續(xù)數(shù)據(jù)可能帶來更多的隨機(jī) IO。
而 B+ 樹的所有葉節(jié)點(diǎn)可以通過指針來相互連接，減少順序遍歷帶來的隨機(jī) IO。
普通索引還是唯一索引？
由于唯一索引用不上 change buffer 的優(yōu)化機(jī)制，因此如果業(yè)務(wù)可以接受，從性能角度出發(fā)建議你優(yōu)先考慮非唯一索引。
8、什么是覆蓋索引和索引下推？
覆蓋索引：
在某個(gè)查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，稱為覆蓋索引。
覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。
索引下推：
MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。
9、哪些操作會(huì)導(dǎo)致索引失效？
對(duì)索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 這兩種方式都會(huì)造成索引失效。原因在于查詢的結(jié)果可能是多個(gè)，不知道從哪個(gè)索引值開始比較，于是就只能通過全表掃描的方式來查詢。 對(duì)索引進(jìn)行函數(shù)/對(duì)索引進(jìn)行表達(dá)式計(jì)算，因?yàn)樗饕３值氖撬饕侄蔚脑贾?，而不是?jīng)過函數(shù)計(jì)算的值，自然就沒辦法走索引。 對(duì)索引進(jìn)行隱式轉(zhuǎn)換相當(dāng)于使用了新函數(shù)。 WHERE 子句中的 OR語句，只要有條件列不是索引列，就會(huì)進(jìn)行全表掃描。
10、如何給字符串加索引？
直接創(chuàng)建完整索引，這樣可能會(huì)比較占用空間。 創(chuàng)建前綴索引，節(jié)省空間，但會(huì)增加查詢掃描次數(shù)，并且不能使用覆蓋索引。 倒序存儲(chǔ)，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不夠的問題。 創(chuàng)建 hash 字段索引，查詢性能穩(wěn)定，有額外的存儲(chǔ)和計(jì)算消耗，跟第三種方式一樣，都不支持范圍掃描。
三、日志篇
11、MySQL 的 change buffer 是什么？
當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在內(nèi)存中就直接更新；而如果這個(gè)數(shù)據(jù)頁還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會(huì)將這些更新操作緩存在 change buffer 中。 這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)頁了，在下次查詢需要訪問這個(gè)數(shù)據(jù)頁的時(shí)候，將數(shù)據(jù)頁讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁有關(guān)的操作。通過這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性。 注意唯一索引的更新就不能使用 change buffer，實(shí)際上也只有普通索引可以使用。 適用場(chǎng)景： 對(duì)于寫多讀少的業(yè)務(wù)來說，頁面在寫完以后馬上被訪問到的概率比較小，此時(shí) change buffer 的使用效果最好。這種業(yè)務(wù)模型常見的就是賬單類、日志類的系統(tǒng)。 反過來，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫入之后馬上會(huì)做查詢，那么即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪問這個(gè)數(shù)據(jù)頁，會(huì)立即觸發(fā) merge 過程。這樣隨機(jī)訪問 IO 的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。
12、MySQL 是如何判斷一行掃描數(shù)的？
MySQL 在真正開始執(zhí)行語句之前，并不能精確地知道滿足這個(gè)條件的記錄有多少條。 而只能根據(jù)統(tǒng)計(jì)信息來估算記錄數(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)，所以采用了異步寫回磁盤的技術(shù)，寫入內(nèi)存后就返回。但是這樣，會(huì)存在 crash后 內(nèi)存數(shù)據(jù)丟失的隱患，而 redo log 具備 crash safe 的能力。
15、為什么 redo log 具有 crash-safe 的能力，是 binlog 無法替代的？
第一點(diǎn)：redo log 可確保 innoDB 判斷哪些數(shù)據(jù)已經(jīng)刷盤，哪些數(shù)據(jù)還沒有
redo log 和 binlog 有一個(gè)很大的區(qū)別就是，一個(gè)是循環(huán)寫，一個(gè)是追加寫。也就是說 redo log 只會(huì)記錄未刷盤的日志，已經(jīng)刷入磁盤的數(shù)據(jù)都會(huì)從 redo log 這個(gè)有限大小的日志文件里刪除。binlog 是追加日志，保存的是全量的日志。
當(dāng)數(shù)據(jù)庫 crash 后，想要恢復(fù)未刷盤但已經(jīng)寫入 redo log 和 binlog 的數(shù)據(jù)到內(nèi)存時(shí)，binlog 是無法恢復(fù)的。雖然 binlog 擁有全量的日志，但沒有一個(gè)標(biāo)志讓 innoDB 判斷哪些數(shù)據(jù)已經(jīng)刷盤，哪些數(shù)據(jù)還沒有。
但 redo log 不一樣，只要刷入磁盤的數(shù)據(jù)，都會(huì)從 redo log 中抹掉，因?yàn)槭茄h(huán)寫！數(shù)據(jù)庫重啟后，直接把 redo log 中的數(shù)據(jù)都恢復(fù)至內(nèi)存就可以了。
第二點(diǎn)：如果 redo log 寫入失敗，說明此次操作失敗，事務(wù)也不可能提交
redo log 每次更新操作完成后，就一定會(huì)寫入日志，如果寫入失敗，說明此次操作失敗，事務(wù)也不可能提交。 redo log 內(nèi)部結(jié)構(gòu)是基于頁的，記錄了這個(gè)頁的字段值變化，只要crash后讀取redo log進(jìn)行重放，就可以恢復(fù)數(shù)據(jù)。 這就是為什么 redo log 具有 crash-safe 的能力，而 binlog 不具備。
16、當(dāng)數(shù)據(jù)庫 crash 后，如何恢復(fù)未刷盤的數(shù)據(jù)到內(nèi)存中？
根據(jù) redo log 和 binlog 的兩階段提交，未持久化的數(shù)據(jù)分為幾種情況：
change buffer 寫入，redo log 雖然做了 fsync 但未 commit，binlog 未 fsync 到磁盤，這部分?jǐn)?shù)據(jù)丟失。
change buffer 寫入，redo log fsync 未 commit，binlog 已經(jīng) fsync 到磁盤，先從 binlog 恢復(fù) redo log，再從 redo log 恢復(fù) change buffer。
change buffer 寫入，redo log 和 binlog 都已經(jīng) fsync，直接從 redo log 里恢復(fù)。
17、redo log 寫入方式？
redo log包括兩部分內(nèi)容，分別是內(nèi)存中的日志緩沖(redo log buffer)和磁盤上的日志文件(redo log file)。
MySQL 每執(zhí)行一條 DML 語句，會(huì)先把記錄寫入 redo log buffer（用戶空間） ，再保存到內(nèi)核空間的緩沖區(qū) OS-buffer 中，后續(xù)某個(gè)時(shí)間點(diǎn)再一次性將多個(gè)操作記錄寫到 redo log file（刷盤） 。這種先寫日志，再寫磁盤的技術(shù)，就是WAL。
可以發(fā)現(xiàn)，redo log buffer寫入到redo log file，是經(jīng)過OS buffer中轉(zhuǎn)的。其實(shí)可以通過參數(shù)innodb_flush_log_at_trx_commit進(jìn)行配置，參數(shù)值含義如下：
0：稱為延遲寫，事務(wù)提交時(shí)不會(huì)將redo log buffer中日志寫入到OS buffer，而是每秒寫入OS buffer并調(diào)用寫入到redo log file中。 1：稱為實(shí)時(shí)寫，實(shí)時(shí)刷”，事務(wù)每次提交都會(huì)將redo log buffer中的日志寫入OS buffer并保存到redo log file中。 2：稱為實(shí)時(shí)寫，延遲刷。每次事務(wù)提交寫入到OS buffer，然后是每秒將日志寫入到redo log file。
18、redo log 的執(zhí)行流程?
我們來看下Redo log的執(zhí)行流程，假設(shè)執(zhí)行的 SQL 如下：
update?T?set?a?=1?where?id?=666
MySQL 客戶端將請(qǐng)求語句 update T set a =1 where id =666，發(fā)往 MySQL Server 層。 MySQL Server 層接收到 SQL 請(qǐng)求后，對(duì)其進(jìn)行分析、優(yōu)化、執(zhí)行等處理工作，將生成的 SQL 執(zhí)行計(jì)劃發(fā)到 InnoDB 存儲(chǔ)引擎層執(zhí)行。 InnoDB 存儲(chǔ)引擎層將a修改為1的這個(gè)操作記錄到內(nèi)存中。 記錄到內(nèi)存以后會(huì)修改 redo log 的記錄，會(huì)在添加一行記錄，其內(nèi)容是需要在哪個(gè)數(shù)據(jù)頁上做什么修改。 此后，將事務(wù)的狀態(tài)設(shè)置為 prepare ，說明已經(jīng)準(zhǔn)備好提交事務(wù)了。 等到 MySQL Server 層處理完事務(wù)以后，會(huì)將事務(wù)的狀態(tài)設(shè)置為 commit，也就是提交該事務(wù)。 在收到事務(wù)提交的請(qǐng)求以后，redo log 會(huì)把剛才寫入內(nèi)存中的操作記錄寫入到磁盤中，從而完成整個(gè)日志的記錄過程。
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 可能沒有寫入完全 MySQL 就掛了。所以需要配合 redo log 才可以進(jìn)行 crash safe。
20、什么是兩階段提交？
MySQL 將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，中間再穿插寫入binlog，這就是"兩階段提交"。
而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。redolog 用于恢復(fù)主機(jī)故障時(shí)的未更新的物理數(shù)據(jù)，binlog 用于備份操作。兩者本身就是兩個(gè)獨(dú)立的個(gè)體，要想保持一致，就必須使用分布式事務(wù)的解決方案來處理。
為什么需要兩階段提交呢?
如果不用兩階段提交的話，可能會(huì)出現(xiàn)這樣情況 先寫 redo log，crash 后 bin log 備份恢復(fù)時(shí)少了一次更新，與當(dāng)前數(shù)據(jù)不一致。 先寫 bin log，crash 后，由于 redo log 沒寫入，事務(wù)無效，所以后續(xù) bin log 備份恢復(fù)時(shí)，數(shù)據(jù)不一致。 兩階段提交就是為了保證 redo log 和 binlog 數(shù)據(jù)的安全一致性。只有在這兩個(gè)日志文件邏輯上高度一致了才能放心的使用。
在恢復(fù)數(shù)據(jù)時(shí)，redolog 狀態(tài)為 commit 則說明 binlog 也成功，直接恢復(fù)數(shù)據(jù)；如果 redolog 是 prepare，則需要查詢對(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，中文全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是日志先寫內(nèi)存，再寫磁盤。MySQL 執(zhí)行更新操作后，在真正把數(shù)據(jù)寫入到磁盤前，先記錄日志。
好處是不用每一次操作都實(shí)時(shí)把數(shù)據(jù)寫盤，就算 crash 后也可以通過redo log 恢復(fù)，所以能夠?qū)崿F(xiàn)快速響應(yīng) SQL 語句。
23、binlog 日志的三種格式
binlog 日志有三種格式
Statement：基于SQL語句的復(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ùn)行，還必須記錄每條語句在執(zhí)行的時(shí)候的一些相關(guān)信息，以保證所有語句能在備庫得到和在主庫端執(zhí)行時(shí)候相同的結(jié)果。
Row格式
不記錄 SQL 語句上下文相關(guān)信息，僅保存哪條記錄被修改。
優(yōu)點(diǎn)：binlog 中可以不記錄執(zhí)行的 SQL 語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程、或 function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。 缺點(diǎn):可能會(huì)產(chǎn)生大量的日志內(nèi)容。
Mixed格式
實(shí)際上就是 Statement 與 Row 的結(jié)合。一般的語句修改使用 statment 格式保存 binlog，如一些函數(shù)，statement 無法完成主從復(fù)制的操作，則采用 row 格式保存 binlog，MySQL 會(huì)根據(jù)執(zhí)行的每一條具體的 SQL 語句來區(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)前記錄的位置，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭。 checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。 write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。 如果 write pos 追上 checkpoint，表示“粉板”滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進(jìn)一下。 有了 redo log，當(dāng)數(shù)據(jù)庫發(fā)生宕機(jī)重啟后，可通過 redo log將未落盤的數(shù)據(jù)（check point之后的數(shù)據(jù)）恢復(fù)，保證已經(jīng)提交的事務(wù)記錄不會(huì)丟失，這種能力稱為crash-safe。
25、原本可以執(zhí)行得很快的 SQL 語句，執(zhí)行速度卻比預(yù)期的慢很多，原因是什么？如何解決？
原因：從大到小可分為四種情況
MySQL 數(shù)據(jù)庫本身被堵住了，比如：系統(tǒng)或網(wǎng)絡(luò)資源不夠。 SQL 語句被堵住了，比如：表鎖，行鎖等，導(dǎo)致存儲(chǔ)引擎不執(zhí)行對(duì)應(yīng)的 SQL 語句。 確實(shí)是索引使用不當(dāng)，沒有走索引。 表中數(shù)據(jù)的特點(diǎn)導(dǎo)致的，走了索引，但回表次數(shù)龐大。
解決：
考慮采用 force index 強(qiáng)行選擇一個(gè)索引 考慮修改語句，引導(dǎo) MySQL 使用我們期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，語義的邏輯是相同的。 第三種方法是，在有些場(chǎng)景下，可以新建一個(gè)更合適的索引，來提供給優(yōu)化器做選擇，或刪掉誤用的索引。 如果確定是索引根本沒必要，可以考慮刪除索引。
26、InnoDB 數(shù)據(jù)頁結(jié)構(gòu)
一個(gè)數(shù)據(jù)頁大致劃分七個(gè)部分
File Header：表示頁的一些通用信息，占固定的38字節(jié)。 page Header：表示數(shù)據(jù)頁專有信息，占固定的56字節(jié)。 inimum+Supermum：兩個(gè)虛擬的偽記錄，分別表示頁中的最小記錄和最大記錄，占固定的26字節(jié)。 User Records：真正存儲(chǔ)我們插入的數(shù)據(jù)，大小不固定。 Free Space：頁中尚未使用的部分，大小不固定。 Page Directory：頁中某些記錄的相對(duì)位置，也就是各個(gè)槽對(duì)應(yīng)的記錄在頁面中的地址偏移量。 File Trailer：用于檢驗(yàn)頁是否完整，占固定大小 8 字節(jié)。
四、數(shù)據(jù)篇
27、MySQL 是如何保證數(shù)據(jù)不丟失的？
只要redolog 和 binlog 保證持久化磁盤就能確保MySQL異常重啟后回復(fù)數(shù)據(jù) 在恢復(fù)數(shù)據(jù)時(shí)，redolog 狀態(tài)為 commit 則說明 binlog 也成功，直接恢復(fù)數(shù)據(jù)；如果 redolog 是 prepare，則需要查詢對(duì)應(yīng)的 binlog事務(wù)是否成功，決定是回滾還是執(zhí)行。
28、誤刪數(shù)據(jù)怎么辦？
DBA 的最核心的工作就是保證數(shù)據(jù)的完整性，先要做好預(yù)防，預(yù)防的話大概是通過這幾個(gè)點(diǎn)：
權(quán)限控制與分配(數(shù)據(jù)庫和服務(wù)器權(quán)限) 制作操作規(guī)范 定期給開發(fā)進(jìn)行培訓(xùn) 搭建延遲備庫 做好 SQL 審計(jì)，只要是對(duì)線上數(shù)據(jù)有更改操作的語句(DML和DDL)都需要進(jìn)行審核 做好備份。備份的話又分為兩個(gè)點(diǎn) (1)如果數(shù)據(jù)量比較大，用物理備份 xtrabackup。定期對(duì)數(shù)據(jù)庫進(jìn)行全量備份，也可以做增量備份。(2)如果數(shù)據(jù)量較少，用 mysqldump 或者 mysqldumper。再利用 binlog 來恢復(fù)或者搭建主從的方式來恢復(fù)數(shù)據(jù)。定期備份binlog 文件也是很有必要的 如果發(fā)生了數(shù)據(jù)刪除的操作，又可以從以下幾個(gè)點(diǎn)來恢復(fù): DML 誤操作語句造成數(shù)據(jù)不完整或者丟失?？梢酝ㄟ^ 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ù)回主庫上。 DDL語句誤操作(truncate和drop)，由于DDL語句不管 binlog_format 是 row 還是 statement ，在 binlog 里都只記錄語句，不記錄 image 所以恢復(fù)起來相對(duì)要麻煩得多。只能通過全量備份+應(yīng)用 binlog 的方式來恢復(fù)數(shù)據(jù)。一旦數(shù)據(jù)量比較大，那么恢復(fù)時(shí)間就特別長 rm 刪除：使用備份跨機(jī)房，或者最好是跨城市保存。
29、drop、truncate 和 delete 的區(qū)別
DELETE 語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。 TRUNCATE TABLE ?則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。 drop語句將表所占用的空間全釋放掉。 在速度上，一般來說，drop> truncate > delete。 如果想刪除部分?jǐn)?shù)據(jù)用 delete，注意帶上 where 子句，回滾段要足夠大； 如果想刪除表，當(dāng)然用 drop；如果想保留表而將所有數(shù)據(jù)刪除，如果和事務(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 + 線程 id，表示終止這個(gè)線程中正在執(zhí)行的語句 一個(gè)是 kill connection + 線程 id，這里 connection 可缺省，表示斷開這個(gè)線程的連接
kill 不掉的原因
kill命令被堵了，還沒到位 kill命令到位了，但是沒被立刻觸發(fā) kill命令被觸發(fā)了，但執(zhí)行完也需要時(shí)間
31、如何理解 MySQL 的邊讀邊發(fā)
如果客戶端接受慢，會(huì)導(dǎo)致 MySQL 服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間會(huì)很長。 服務(wù)端并不需要保存一個(gè)完整的結(jié)果集，取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程都是通過一個(gè) next_buffer 來操作的。 內(nèi)存的數(shù)據(jù)頁都是在 Buffer_Pool中操作的。 InnoDB 管理 Buffer_Pool 使用的是改進(jìn)的 LRU 算法，使用鏈表實(shí)現(xiàn)，實(shí)現(xiàn)上，按照 5:3 的比例把整個(gè) LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域。
32、MySQL 的大表查詢?yōu)槭裁床粫?huì)爆內(nèi)存？
由于 MySQL 是邊讀變發(fā)，因此對(duì)于數(shù)據(jù)量很大的查詢結(jié)果來說，不會(huì)再 server 端保存完整的結(jié)果集，所以，如果客戶端讀結(jié)果不及時(shí)，會(huì)堵住 MySQL 的查詢過程，但是不會(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可見。 可以與普通表重名。 增刪改查用的是臨時(shí)表。 show tables 不顯示普通表。 在實(shí)際應(yīng)用中，臨時(shí)表一般用于處理比較復(fù)雜的計(jì)算邏輯。 由于臨時(shí)表是每個(gè)線程自己可見的，所以不需要考慮多個(gè)線程執(zhí)行同一個(gè)處理時(shí)臨時(shí)表的重名問題，在線程退出的時(shí)候，臨時(shí)表會(huì)自動(dòng)刪除。
34、MySQL 存儲(chǔ)引擎介紹（InnoDB、MyISAM、MEMORY）
InnoDB 是事務(wù)型數(shù)據(jù)庫的首選引擎，支持事務(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 擁有較高的插入、查詢速度，但不支持事務(wù)。在 MySQL5.5.5 之前的版本中，MyISAM 是默認(rèn)存儲(chǔ)引擎 MEMORY 存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，為查詢和引用其他表數(shù)據(jù)提供快速訪問。
35、都說 InnoDB 好，那還要不要使用 MEMORY 引擎？
內(nèi)存表就是使用 memory 引擎創(chuàng)建的表 為什么我不建議你在生產(chǎn)環(huán)境上使用內(nèi)存表。這里的原因主要包括兩個(gè)方面：鎖粒度問題；數(shù)據(jù)持久化問題。 由于重啟會(huì)丟數(shù)據(jù)，如果一個(gè)備庫重啟，會(huì)導(dǎo)致主備同步線程停止；如果主庫跟這個(gè)備庫是雙 M 架構(gòu)，還可能導(dǎo)致主庫的內(nèi)存表數(shù)據(jù)被刪掉。
36、如果數(shù)據(jù)庫誤操作, 如何執(zhí)行數(shù)據(jù)恢復(fù)?
數(shù)據(jù)庫在某個(gè)時(shí)候誤操作，就可以找到距離誤操作最近的時(shí)間節(jié)點(diǎn)的bin log，重放到臨時(shí)數(shù)據(jù)庫里，然后選擇誤刪的數(shù)據(jù)節(jié)點(diǎn)，恢復(fù)到線上數(shù)據(jù)庫。
五、主從備份篇
37、MySQL 是如何保證主備同步？
主備關(guān)系的建立：
一開始創(chuàng)建主備關(guān)系的時(shí)候，是由備庫指定的，比如基于位點(diǎn)的主備關(guān)系，備庫說“我要從binlog文件A的位置P”開始同步，主庫就從這個(gè)指定的位置開始往后發(fā)。 而主備關(guān)系搭建之后，是主庫決定要發(fā)給數(shù)據(jù)給備庫的，所以主庫有新的日志也會(huì)發(fā)給備庫。
MySQL 主備切換流程：
客戶端讀寫都是直接訪問A，而節(jié)點(diǎn)B是備庫，只要將A的更新都同步過來，到本地執(zhí)行就可以保證數(shù)據(jù)是相同的。 當(dāng)需要切換的時(shí)候就把節(jié)點(diǎn)換一下，A的節(jié)點(diǎn)B的備庫
一個(gè)事務(wù)完整的同步過程：
備庫B和主庫A建立來了長鏈接，主庫A內(nèi)部專門線程用于維護(hù)了這個(gè)長鏈接。
在備庫B上通過changemaster命令設(shè)置主庫A的IP端口用戶名密碼以及從哪個(gè)位置開始請(qǐng)求binlog包括文件名和日志偏移量
在備庫B上執(zhí)行start-slave命令備庫會(huì)啟動(dòng)兩個(gè)線程：io_thread和sql_thread分別負(fù)責(zé)建立連接和讀取中轉(zhuǎn)日志進(jìn)行解析執(zhí)行
備庫讀取主庫傳過來的binlog文件備庫收到文件寫到本地成為中轉(zhuǎn)日志
后來由于多線程復(fù)制方案的引入，sql_thread演化成了多個(gè)線程。
38、什么是主備延遲
主庫和備庫在執(zhí)行同一個(gè)事務(wù)的時(shí)候出現(xiàn)時(shí)間差的問題，主要原因有：
有些部署條件下，備庫所在機(jī)器的性能要比主庫性能差。 備庫的壓力較大。 大事務(wù)，一個(gè)主庫上語句執(zhí)行10分鐘，那么這個(gè)事務(wù)可能會(huì)導(dǎo)致從庫延遲10分鐘。
39、為什么要有多線程復(fù)制策略？
因?yàn)閱尉€程復(fù)制的能力全面低于多線程復(fù)制，對(duì)于更新壓力較大的主庫，備庫可能是一直追不上主庫的，帶來的現(xiàn)象就是備庫上seconds_behind_master值越來越大。 在實(shí)際應(yīng)用中，建議使用可靠性優(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 中，就變成單線程復(fù)制了。 按行分發(fā)策略：如果兩個(gè)事務(wù)沒有更新相同的行，它們?cè)趥鋷焐峡梢圆⑿小Ｈ绻麅蓚€(gè)事務(wù)沒有更新相同的行，它們?cè)趥鋷焐峡梢圆⑿袌?zhí)行。顯然，這個(gè)模式要求 binlog 格式必須是 row。缺點(diǎn)：相比于按表并行分發(fā)策略，按行并行策略在決定線程分發(fā)的時(shí)候，需要消耗更多的計(jì)算資源。
41、MySQL的一主一備和一主多從有什么區(qū)別？
在一主一備的雙 M 架構(gòu)里，主備切換只需要把客戶端流量切到備庫；而在一主多從架構(gòu)里，主備切換除了要把客戶端流量切到備庫外，還需要把從庫接到新主庫上。
42、主庫出問題如何解決?
基于位點(diǎn)的主備切換：存在找同步位點(diǎn)這個(gè)問題 MySQL 5.6 版本引入了 GTID，徹底解決了這個(gè)困難。那么，GTID 到底是什么意思，又是如何解決找同步位點(diǎn)這個(gè)問題呢？ GTID：全局事務(wù) ID，是一個(gè)事務(wù)在提交的時(shí)候生成的，是這個(gè)事務(wù)的唯一標(biāo)識(shí)；它由兩部分組成，格式是：GTID=server_uuid:gno 每個(gè) MySQL 實(shí)例都維護(hù)了一個(gè) GTID 集合，用來對(duì)應(yīng)“這個(gè)實(shí)例執(zhí)行過的所有事務(wù)”。 在基于 GTID 的主備關(guān)系里，系統(tǒng)認(rèn)為只要建立主備關(guān)系，就必須保證主庫發(fā)給備庫的日志是完整的。因此，如果實(shí)例 B 需要的日志已經(jīng)不存在，A’就拒絕把日志發(fā)給 B。
43、MySQL 讀寫分離涉及到過期讀問題的幾種解決方案?
強(qiáng)制走主庫方案 sleep 方案 判斷主備無延遲方案 配合 semi-sync 方案 等主庫位點(diǎn)方案 GTID 方案。 實(shí)際生產(chǎn)中，先客戶端對(duì)請(qǐng)求做分類，區(qū)分哪些請(qǐng)求可以接受過期讀，而哪些請(qǐng)求完全不能接受過期讀；然后，對(duì)于不能接受過期讀的語句，再使用等 GTID 或等位點(diǎn)的方案。
44、MySQL的并發(fā)鏈接和并發(fā)查詢有什么區(qū)別？
在執(zhí)行show processlist的結(jié)果里，看到了幾千個(gè)連接，指的是并發(fā)連接。而"當(dāng)前正在執(zhí)行"的語句，才是并發(fā)查詢。 并發(fā)連接數(shù)多影響的是內(nèi)存，并發(fā)查詢太高對(duì)CPU不利。一個(gè)機(jī)器的CPU核數(shù)有限，線程全沖進(jìn)來，上下文切換的成本就會(huì)太高。 所以需要設(shè)置參數(shù)：innodb_thread_concurrency 用來限制線程數(shù)，當(dāng)線程數(shù)達(dá)到該參數(shù)，InnoDB就會(huì)認(rèn)為線程數(shù)用完了，會(huì)阻止其他語句進(jìn)入引擎執(zhí)行。
六、性能篇
45、短時(shí)間提高 MySQL 性能的方法
第一種方法：先處理掉那些占著連接但是不工作的線程?；蛘咴倏紤]斷開事務(wù)內(nèi)空閑太久的連接。kill connection + id 第二種方法：減少連接過程的消耗：慢查詢性能問題在 MySQL 中，會(huì)引發(fā)性能問題的慢查詢，大體有以下三種可能：索引沒有設(shè)計(jì)好；SQL 語句沒寫好；MySQL 選錯(cuò)了索引（force index）。
46、為什么 MySQL 自增主鍵 ID 不連續(xù)？
唯一鍵沖突
事務(wù)回滾
自增主鍵的批量申請(qǐng)
深層次原因是：MySQL 不判斷自增主鍵是否存在，從而減少加鎖的時(shí)間范圍和粒度，這樣能保持更高的性能，確保自增主鍵不能回退，所以才有自增主鍵不連續(xù)。
自增主鍵怎么做到唯一性？自增值加1來通過自增鎖控制并發(fā)
47、InnoDB 為什么要用自增 ID 作為主鍵？
自增主鍵的插入模式，符合遞增插入，每次都是追加操作，不涉及挪動(dòng)記錄，也不會(huì)觸發(fā)葉子節(jié)點(diǎn)的分裂。
每次插入新的記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁。
而有業(yè)務(wù)邏輯的字段做主鍵，不容易保證有序插入，由于每次插入主鍵的值近似于隨機(jī)
因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個(gè)位置， 頻繁的移動(dòng)、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，寫數(shù)據(jù)成本較高。
48、如何最快的復(fù)制一張表？
為了避免對(duì)源表加讀鎖，更穩(wěn)妥的方案是先將數(shù)據(jù)寫到外部文本文件，然后再寫回目標(biāo)表 一種方法是，使用 mysqldump 命令將數(shù)據(jù)導(dǎo)出成一組 INSERT 語句 另一種方法是直接將結(jié)果導(dǎo)出成.csv 文件。MySQL 提供語法，用來將查詢結(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) 的方法，可以通過導(dǎo)出 + 導(dǎo)入表空間的方式，實(shí)現(xiàn)物理拷貝表的功能。
49、grant 和 flush privileges語句
grant語句會(huì)同時(shí)修改數(shù)據(jù)表和內(nèi)存，判斷權(quán)限的時(shí)候使用的內(nèi)存數(shù)據(jù)，因此，規(guī)范使用是不需要加上 flush privileges 語句。 flush privileges 語句本身會(huì)用數(shù)據(jù)表的數(shù)據(jù)重建一份內(nèi)存權(quán)限數(shù)據(jù)，所以在權(quán)限數(shù)據(jù)可能存在不一致的情況下再使用。
50、要不要使用分區(qū)表？
分區(qū)并不是越細(xì)越好。實(shí)際上，單表或者單分區(qū)的數(shù)據(jù)一千萬行，只要沒有特別大的索引，對(duì)于現(xiàn)在的硬件能力來說都已經(jīng)是小表了。 分區(qū)也不要提前預(yù)留太多，在使用之前預(yù)先創(chuàng)建即可。比如，如果是按月分區(qū)，每年年底時(shí)再把下一年度的 12 個(gè)新分區(qū)創(chuàng)建上即可。對(duì)于沒有數(shù)據(jù)的歷史分區(qū)，要及時(shí)的 drop 掉。
51、join 用法
使用 left join 左邊的表不一定是驅(qū)動(dòng)表 如果需要 left join 的語義，就不能把被驅(qū)動(dòng)表的字段放在 where 條件里面做等值判斷或不等值判斷，必須都寫在 on 里面 標(biāo)準(zhǔn)的 group by 語句，是需要在 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 后寫的數(shù)據(jù)會(huì)覆蓋之前的數(shù)據(jù)。
Xid 只需要不在同一個(gè) binlog 文件出現(xiàn)重復(fù)值即可，理論上會(huì)出現(xiàn)重復(fù)值，但概率極小可忽略不計(jì)。
InnoDB 的 max_trx_id 遞增值每次 MySQL 重啟會(huì)保存起來。
Xid 是由 server 層維護(hù)的。InnoDB 內(nèi)部使用 Xid，就是為了能夠在 InnoDB 事務(wù)和 server 之間做關(guān)聯(lián)。但是，InnoDB 自己的 trx_id，是另外維護(hù)的。
thread_id 是我們使用中最常見的，而且也是處理得最好的一個(gè)自增 id 邏輯了。使用了insert_unique算法
53、Xid 在 MySQL 內(nèi)部是怎么生成的呢？
MySQL 內(nèi)部維護(hù)了一個(gè)全局變量 global_query_id，每次執(zhí)行語句（包括select語句）的時(shí)候?qū)⑺x值給 Query_id，然后給這個(gè)變量加 1。如果當(dāng)前語句是這個(gè)事務(wù)執(zhí)行的第一條語句，那么 MySQL 還會(huì)同時(shí)把 Query_id 賦值給這個(gè)事務(wù)的 Xid。
而 global_query_id 是一個(gè)純內(nèi)存變量，重啟之后就清零了。所以你就知道了，在同一個(gè)數(shù)據(jù)庫實(shí)例中，不同事務(wù)的 Xid 也是有可能相同的。但是 MySQL 重啟之后會(huì)重新生成新的 binlog 文件，這就保證了，同一個(gè) binlog 文件里，Xid 一定是惟一的。
七、鎖篇
54、說一下 MySQL 的鎖
MySQL 在 server 層 和 存儲(chǔ)引擎層 都運(yùn)用了大量的鎖 MySQL server 層需要講兩種鎖，第一種是MDL(metadata lock) 元數(shù)據(jù)鎖，第二種則 Table Lock 表鎖。 MDL 又名元數(shù)據(jù)鎖，那么什么是元數(shù)據(jù)呢，任何描述數(shù)據(jù)庫的內(nèi)容就是元數(shù)據(jù)，比如我們的表結(jié)構(gòu)、庫結(jié)構(gòu)等都是元數(shù)據(jù)。那為什么需要 MDL 呢？ 主要解決兩個(gè)問題：事務(wù)隔離問題；數(shù)據(jù)復(fù)制問題 InnoDB 有五種表級(jí)鎖：IS（意向讀鎖）；IX（意向?qū)戞i）；S（讀）；X（寫）；AUTO-INC 在對(duì)表進(jìn)行select/insert/delete/update語句時(shí)候不會(huì)加表級(jí)鎖 IS和IX的作用是為了判斷表中是否有已經(jīng)被加鎖的記錄 自增主鍵的保障就是有 AUTO-INC 鎖，是語句級(jí)別的：為表的某個(gè)列添加 AUTO_INCREMENT 屬性，之后在插?記錄時(shí)，可以不指定該列的值，系統(tǒng)會(huì)?動(dòng)為它賦上單調(diào)遞增的值。 InnoDB 4 種行級(jí)鎖 RecordLock：記錄鎖 GapLock：間隙鎖解決幻讀；前一次查詢不存在的東西在下一次查詢出現(xiàn)了，其實(shí)就是事務(wù)A中的兩次查詢之間事務(wù)B執(zhí)行插入操作被事務(wù)A感知了 Next-KeyLock：鎖住某條記錄又想阻止其它事務(wù)在改記錄前面的間隙插入新紀(jì)錄 InsertIntentionLock：插入意向鎖;如果插入到同一行間隙中的多個(gè)事務(wù)未插入到間隙內(nèi)的同一位置則無須等待 行鎖和表鎖的抉擇 全表掃描用行級(jí)鎖
55、什么是幻讀？
值在同一個(gè)事務(wù)中，存在前后兩次查詢同一個(gè)范圍的數(shù)據(jù)，第二次看到了第一次沒有查詢到的數(shù)據(jù)。
幻讀出現(xiàn)的場(chǎng)景：
事務(wù)的隔離級(jí)別是可重復(fù)讀，且是當(dāng)前讀。 幻讀指新插入的行。
幻讀帶來的問題：
對(duì)行鎖語義的破壞 破壞了數(shù)據(jù)一致性
解決：
加間隙鎖，鎖住行與行之間的間隙，阻塞新插入的操作。 帶來的問題：降低并發(fā)度，可能導(dǎo)致死鎖。
八、其它篇
56、為什么 MySQL 會(huì)抖一下？
臟頁會(huì)被后臺(tái)線程自動(dòng) flush，也會(huì)由于數(shù)據(jù)頁淘汰而觸發(fā) flush，而刷臟頁的過程由于會(huì)占用資源，可能會(huì)讓你的更新和查詢語句的響應(yīng)時(shí)間長一些。
57、為什么刪除了表，表文件的大小還是沒變？
數(shù)據(jù)項(xiàng)刪除之后 InnoDB 某個(gè)頁 page A 會(huì)被標(biāo)記為可復(fù)用。 delete 命令把整個(gè)表的數(shù)據(jù)刪除，結(jié)果就是，所有的數(shù)據(jù)頁都會(huì)被標(biāo)記為可復(fù)用。但是磁盤上，文件不會(huì)變小。 經(jīng)過大量增刪改的表，都是可能是存在空洞的。這些空洞也占空間所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。 重建表，就可以達(dá)到這樣的目的。可以使用 alter table A engine=InnoDB 命令來重建表。
58、count(*)實(shí)現(xiàn)方式以及各種 count 對(duì)比
對(duì)于 count(主鍵 id) 來說，InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。 對(duì)于 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，判斷是不可能為空的，按行累加。單看這兩個(gè)用法的差別的話，你能對(duì)比出來，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會(huì)涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。 對(duì)于 count(字段) 來說：如果這個(gè)“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個(gè)字段，判斷不能為 null，按行累加；如果這個(gè)“字段”定義允許為 null，那么執(zhí)行的時(shí)候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。也就是前面的第一條原則，server 層要什么字段，InnoDB 就返回什么字段。 但是 count *是例外，并不會(huì)把全部字段取出來，而是專門做了優(yōu)化，不取值。count(*)肯定不是 null，按行累加。所以結(jié)論是：按照效率排序的話， count(字段)count(*)。
59、orderby 排序的內(nèi)部原理
MySQL 會(huì)為每個(gè)線程分配一個(gè)內(nèi)存（sort-buffer）用于排序該內(nèi)存大小為 sort_buffer_size；
如果排序的數(shù)據(jù)量小于 sort_buffer_size，排序就會(huì)在內(nèi)存中完成；
內(nèi)部排序分為兩種
全字段排序：到索引樹上找到滿足條件的主鍵ID根據(jù)主鍵ID去取出數(shù)據(jù)放到sort_buffer然后進(jìn)行快速排序
rowid排序：通過控制排序的行數(shù)據(jù)的長度來讓sort_buffer中盡可能多的存放數(shù)據(jù)
如果數(shù)據(jù)量很大，內(nèi)存中無法存下這么多，就會(huì)使用磁盤臨時(shí)文件來輔助排序，稱為外部排序；
外部排序，MySQL會(huì)分為好幾份單獨(dú)的臨時(shí)文件來存放排序后的數(shù)據(jù)，一般是磁盤文件中進(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+3mysql> 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)
沒有什么使我停留——除了目的，縱然岸旁有玫瑰、有綠蔭、有寧靜的港灣，我是不系之舟。
推薦閱讀：