夯實(shí)MySQL基礎(chǔ)的問題歸納

本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識(shí)，其中主要總結(jié)了一些常見的問題，解決一下，包括了常規(guī)的，還有索引類、原理類和框架類的相關(guān)內(nèi)容，希望對(duì)大家有幫助。

推薦學(xué)習(xí)：mysql

常規(guī)篇

1、說一下數(shù)據(jù)庫(kù)的三大范式？?

第一范式：字段原子性，第二范式：行唯一，有主鍵列，第三范式：每列和主鍵列都相關(guān)。

實(shí)際應(yīng)用中會(huì)通過冗余少量字段來少關(guān)聯(lián)表，提升查詢效率。

2、只查詢一條數(shù)據(jù)，但是也執(zhí)行非常慢，原因一般有哪些？

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

3、count(*)、count(0)、count(id)實(shí)現(xiàn)方式的區(qū)別？

• 對(duì)于count(*)、count(常數(shù))、count(主鍵)形式的count函數(shù)來說，優(yōu)化器可以選擇掃描成本最小的索引執(zhí)行查詢，從而提升效率，它們的執(zhí)行過程是一樣的。
• 而對(duì)于count(非索引列)來說，優(yōu)化器選擇全表掃描，說明只能在聚集索引的葉子結(jié)點(diǎn)順序掃描。
• count(二級(jí)索引列)只能選擇包含我們指定的列的索引去執(zhí)行查詢，可能導(dǎo)致優(yōu)化器選擇的索引執(zhí)行的代價(jià)并不是最小。

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

1）如果數(shù)據(jù)量比較大,用物理備份xtrabackup。定期對(duì)數(shù)據(jù)庫(kù)進(jìn)行全量備份,也可以做增量備份。

2）如果數(shù)據(jù)量較少,用mysqldump或者mysqldumper，再利用binlog來恢復(fù)或者搭建主從的方式來恢復(fù)數(shù)據(jù)，可以從以下幾個(gè)點(diǎn)來恢復(fù)：

• DML誤操作語(yǔ)句：可以通過flashback，先解析binlog Event,然后在進(jìn)行反轉(zhuǎn)。
• DDL語(yǔ)句誤操作：只能通過全量備份+應(yīng)用binlog的方式來恢復(fù)數(shù)據(jù)。一旦數(shù)據(jù)量比較大,那么恢復(fù)時(shí)間就特別長(zhǎng)。
• rm 刪除：使用備份跨機(jī)房，或者最好是跨城市保存。

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

• DELETE語(yǔ)句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。
• TRUNCATE table 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器，執(zhí)行速度快。
• drop語(yǔ)句將表所占用的空間全釋放掉。

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

• MySQL 是“邊讀邊發(fā)的”，這就意味著，如果客戶端接收得慢，會(huì)導(dǎo)致 MySQL 服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間變長(zhǎng)。
• 服務(wù)端并不需要保存一個(gè)完整的結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程都是通過一個(gè)next_buffer來操作的。
• 內(nèi)存的數(shù)據(jù)頁(yè)是在 Buffer Pool (BP) 中管理的。
• InnoDB 管理 Buffer Pool 使用改進(jìn)的 LRU 算法，是用鏈表來實(shí)現(xiàn)的。在 InnoDB 實(shí)現(xiàn)上，按照 5:3 的比例把整個(gè) LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域，確保大批量加載冷數(shù)據(jù)時(shí)不會(huì)沖掉熱點(diǎn)數(shù)據(jù)。

7、深度分頁(yè)（超大分頁(yè)）怎么處理?

• 用id優(yōu)化：先找到上次分頁(yè)的最大ID,然后利用id上的索引來查詢,類似于select * from user where id>1000000 limit 100。
• 用覆蓋索引優(yōu)化：Mysql的查詢完全命中索引的時(shí)候,稱為覆蓋索引,是非常快的,因?yàn)椴樵冎恍枰谒饕线M(jìn)行查找,之后可以直接返回,而不用再回表拿數(shù)據(jù).因此我們可以先查出索引的ID,然后根據(jù)Id拿數(shù)據(jù)。
• 在業(yè)務(wù)允許的情況下限制頁(yè)數(shù)

8、日常開發(fā)中你是怎么優(yōu)化SQL的？

• 添加合適索引：對(duì)作為查詢條件和order by的字段建立索引，對(duì)于多個(gè)查詢字段的考慮建立組合索引，同時(shí)注意組合索引字段的順序,將最常用作限制條件的列放在最左邊，依次遞減，索引不宜太多，一般5個(gè)以內(nèi)。
• 優(yōu)化表結(jié)構(gòu)：數(shù)字型字段優(yōu)于字符串類型，數(shù)據(jù)類型更小通常更好，盡量使用 NOT NULL
• 優(yōu)化查詢語(yǔ)句：分析SQl執(zhí)行計(jì)劃，是否命中索引等，如果SQL很復(fù)雜，優(yōu)化SQL結(jié)構(gòu)，如果表數(shù)據(jù)量太大，考慮分表

9、MySQL 的并發(fā)連接與并發(fā)查詢什么區(qū)別？

• 在執(zhí)行show processlist的結(jié)果里，看到了幾千個(gè)連接，指的是并發(fā)連接。
• 而”當(dāng)前正在執(zhí)行”的語(yǔ)句，才是并發(fā)查詢。
• 并發(fā)連接數(shù)多影響的是內(nèi)存。
• 并發(fā)查詢太高對(duì)CPU不利。一個(gè)機(jī)器的CPU核數(shù)有限，線程全沖進(jìn)來，上下文切換的成本就會(huì)太高。
• 需要注意的是，在線程進(jìn)入鎖等待以后，并發(fā)線程計(jì)數(shù)減一，所以等行鎖或者間隙鎖時(shí)的線程是不算在計(jì)數(shù)范圍內(nèi)的。也就是說進(jìn)入鎖等待的線程不吃CPU，從而避免整個(gè)系統(tǒng)鎖死。

10、MySQL更新字段值為原來的值內(nèi)部是怎么操作呢？

• 相同的數(shù)據(jù)時(shí)，不會(huì)做update更新。
• 不過對(duì)不同的binlog格式，處理的日志方式有所不同：
  • 1）基于row模式時(shí)，server層匹配到要更新的記錄，發(fā)現(xiàn)新值和舊值一致，不做更新，就直接返回，也不記錄binlog。
  • 2）基于 statement 或者 mixed格式時(shí)，MySQL執(zhí)行 update 語(yǔ)句，并把更新語(yǔ)句記錄到binlog。

11、datetime和timestamp有什么區(qū)別？

• datetime 的日期范圍是 1001——9999 年；timestamp 的時(shí)間范圍是 1970——2038 年
• datetime 存儲(chǔ)時(shí)間與時(shí)區(qū)無(wú)關(guān)；timestamp 存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴于時(shí)區(qū)
• datetime 的存儲(chǔ)空間為 8 字節(jié)；timestamp 的存儲(chǔ)空間為 4 字節(jié)
• datetime 的默認(rèn)值為 null；timestamp 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時(shí)間(current_timestamp)

12、事務(wù)的隔離級(jí)別有哪些？

• 「讀未提交」（Read Uncommitted）最低級(jí)別，任何情況都無(wú)法保證
• 「讀已提交」（Read Committed）可避免臟讀的發(fā)生
• 「可重復(fù)讀」（Repeatable Read）可避免臟讀、不可重復(fù)讀的發(fā)生
• 「串行化」（Serializable）可避免臟讀、不可重復(fù)讀、幻讀的發(fā)生
• Mysql默認(rèn)的事務(wù)隔離級(jí)別是「可重復(fù)讀」(Repeatable Read)

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

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

索引篇

1、索引分類有哪些？

• 根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。
• 主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。
• 非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引（secondary index）。

2、聚集索引和非聚集索引有什么區(qū)別？

• ?聚集索引：聚集索引就是以主鍵創(chuàng)建的索引，聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)。

  ??

• 非聚集索引：非主鍵創(chuàng)建的索引，在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列，使用非聚集索引查詢出數(shù)據(jù)時(shí)，拿到葉子上的主鍵再去查到想要查找的數(shù)據(jù)。(拿到主鍵再查找這個(gè)過程叫做回表)。

  

• 覆蓋索引：假設(shè)所查詢的列，剛好都是索引對(duì)應(yīng)的列，不用再回表查，那么這個(gè)索引列就叫覆蓋索引。??

3、InnoDB 為什么設(shè)計(jì)B+樹，而不是B-Tree，Hash，二叉樹，紅黑樹？

• 哈希索引能夠以 O(1) 的速度處理單個(gè)數(shù)據(jù)行的增刪改查，但是面對(duì)范圍查詢或者排序時(shí)就會(huì)導(dǎo)致全表掃描的結(jié)果。
• B樹可以在非葉結(jié)點(diǎn)中存儲(chǔ)數(shù)據(jù)，由于所有的節(jié)點(diǎn)都可能包含目標(biāo)數(shù)據(jù)，我們總是要從根節(jié)點(diǎn)向下遍歷子樹查找滿足條件的數(shù)據(jù)行，這個(gè)特點(diǎn)帶來了大量的隨機(jī) I/O，造成性能下降。
• B+樹所有的數(shù)據(jù)行都存儲(chǔ)在葉節(jié)點(diǎn)中，而這些葉節(jié)點(diǎn)可以通過『指針』依次按順序連接，當(dāng)我們?cè)谌缦滤镜?B+ 樹遍歷數(shù)據(jù)時(shí)可以直接在多個(gè)子節(jié)點(diǎn)之間進(jìn)行跳轉(zhuǎn)，這樣能夠節(jié)省大量的磁盤 I/O 時(shí)間。

• 二叉樹：樹的高度不均勻，不能自平衡，查找效率跟數(shù)據(jù)有關(guān)（樹的高度），并且IO代價(jià)高。

• 紅黑樹：樹的高度隨著數(shù)據(jù)量增加而增加，IO代價(jià)高。

4、講一講聚簇索引與非聚簇索引？

• 在InnoDB里，索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引，即將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到了數(shù)據(jù)。
• 而索引B+Tree的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引、二級(jí)索引。
• 第一次索引一般是順序IO，回表的操作屬于隨機(jī)IO。需要回表的次數(shù)越多，即隨機(jī)IO次數(shù)越多，我們就越傾向于使用全表掃描 。

5、非聚簇索引一定會(huì)回表查詢嗎？

• 不一定，這涉及到查詢語(yǔ)句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進(jìn)行回表查詢。一個(gè)索引包含（覆蓋）所有需要查詢字段的值，被稱之為“覆蓋索引”。

6、講一講MySQL的最左前綴原則？

• 最左前綴原則就是最左優(yōu)先，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。
• MySQL會(huì)一直向右匹配直到遇到范圍查詢（>、 3 and d = 4 如果建立（a，b，c，d）順序的索引，d是用不到索引的，如果建立（a，b，d，c）的索引則都可以用到，a，b，d的順序可以任意調(diào)整。
• =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立（a，b，c）索引可以任意順序，MySQL的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

7、什么是索引下推？

• 滿足最左前綴原則的時(shí)候，最左前綴可以用于在索引中定位記錄。
• 在 MySQL 5.6 之前，只能從ID開始一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值。
• 而 MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

8、Innodb為什么要用自增id作為主鍵？

• 如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)。如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置， 頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE（optimize table）來重建表并優(yōu)化填充頁(yè)面。

9、事務(wù)ACID特性的實(shí)現(xiàn)原理？

• 「原子性」：是使用 undo log 來實(shí)現(xiàn)的，如果事務(wù)執(zhí)行過程中出錯(cuò)或者用戶執(zhí)行了rollback，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)。
• 「持久性」：使用 redo log 來實(shí)現(xiàn)，只要redo log日志持久化了，當(dāng)系統(tǒng)崩潰，即可通過redo log把數(shù)據(jù)恢復(fù)。
• 「隔離性」：通過鎖以及 MVCC,使事務(wù)相互隔離開。
• 「一致性」：通過回滾、恢復(fù)，以及并發(fā)情況下的隔離性，從而實(shí)現(xiàn)一致性。

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

• InnoDB 存儲(chǔ)引擎：B+ 樹索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)本身；

• MyISAM 存儲(chǔ)引擎：B+ 樹索引的葉子節(jié)點(diǎn)保存數(shù)據(jù)的物理地址；

• InnoDB，其數(shù)據(jù)文件本身就是索引文件，相比MyISAM，索引文件和數(shù)據(jù)文件是分離的，其表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，樹的節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄，這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引，這被稱為“聚簇索引”或者聚集索引，而其余的索引都作為輔助索引，輔助索引的data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。

11、索引有哪些分類？

• 根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。
• 主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。
• 非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引（secondary index）。?

12、有哪些場(chǎng)景會(huì)導(dǎo)致索引失效？

背景：B+ 樹提供的這個(gè)快速定位能力，來源于同一層兄弟節(jié)點(diǎn)的有序性，所以說破壞了這個(gè)有序性，大概率就失效了，具體有如下幾種情況：

• 對(duì)索引使用左或者左右模糊匹配：也就是 like %xx 或者 like %xx% 這兩種方式都會(huì)造成索引失效。原因在于查詢的結(jié)果可能是「陳林、張林、周林」等之類的，所以不知道從哪個(gè)索引值開始比較，于是就只能通過全表掃描的方式來查詢。

• 對(duì)索引使用函數(shù)/對(duì)索引進(jìn)行表達(dá)式計(jì)算：因?yàn)樗饕４娴氖撬饕侄蔚脑贾担皇墙?jīng)過函數(shù)計(jì)算后的值，自然就沒辦法走索引了。

• 對(duì)索引隱式類型轉(zhuǎn)換：相當(dāng)于用了新函數(shù)

• WHERE 子句中的 OR：的含義就是兩個(gè)只要滿足一個(gè)即可，因此只有一個(gè)條件列是索引列是沒有意義的，只要有條件列不是索引列，就會(huì)進(jìn)行全表掃描。

方案篇

1、有一個(gè)未分庫(kù)分表的系統(tǒng)，如何設(shè)計(jì)才可以讓系統(tǒng)動(dòng)態(tài)切換到分庫(kù)分表上？

• 停機(jī)擴(kuò)容（不推薦）
• 雙寫遷移方案：設(shè)計(jì)好擴(kuò)容后的表結(jié)構(gòu)方案，然后對(duì)單庫(kù)和分庫(kù)實(shí)現(xiàn)雙寫，觀察一周沒問題后，關(guān)閉單庫(kù)的讀流量，再觀察一段時(shí)間，持續(xù)穩(wěn)定后，關(guān)閉單庫(kù)的寫流量，平滑切換到分庫(kù)分表中。

2、如何設(shè)計(jì)可以動(dòng)態(tài)擴(kuò)容縮容的分庫(kù)分表方案？?

原理篇

1、一條 MySQL 語(yǔ)句執(zhí)行步驟是什么樣的？

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

2、order by 排序內(nèi)部原理是什么樣的？

• MySQL會(huì)為每個(gè)線程分配一個(gè)內(nèi)存（sort_buffer）用于排序該內(nèi)存大小為sort_buffer_size。
• 如果排序的數(shù)據(jù)量小于sort_buffer_size，排序?qū)?huì)在內(nèi)存中完成。
• 如果排序數(shù)據(jù)量很大，內(nèi)存中無(wú)法存下這么多數(shù)據(jù)，則會(huì)使用磁盤臨時(shí)文件來輔助排序，也稱外部排序。
• 在使用外部排序時(shí)，MySQL會(huì)分成好幾份單獨(dú)的臨時(shí)文件用來存放排序后的數(shù)據(jù)，然后在將這些文件合并成一個(gè)大文件。

3、MVCC 實(shí)現(xiàn)原理？

• MVCC(Multiversion concurrency control) 就是同一份數(shù)據(jù)保留多版本的一種方式，進(jìn)而實(shí)現(xiàn)并發(fā)控制。在查詢的時(shí)候，通過read view和版本鏈找到對(duì)應(yīng)版本的數(shù)據(jù)。
• 作用：提升并發(fā)性能。對(duì)于高并發(fā)場(chǎng)景，MVCC 比行級(jí)鎖開銷更小。
• MVCC 的實(shí)現(xiàn)依賴于版本鏈，版本鏈?zhǔn)峭ㄟ^表的三個(gè)隱藏字段實(shí)現(xiàn)。
  • 1）DB_TRX_ID：當(dāng)前事務(wù) id，通過事務(wù) id 的大小判斷事務(wù)的時(shí)間順序。
  • 2）DB_ROLL_PRT：回滾指針，指向當(dāng)前行記錄的上一個(gè)版本，通過這個(gè)指針將數(shù)據(jù)的多個(gè)版本連接在一起構(gòu)成undo log版本鏈。
  • 3）DB_ROLL_ID：主鍵，如果數(shù)據(jù)表沒有主鍵，InnoDB 會(huì)自動(dòng)生成主鍵。

4、change buffer是什么，有何作用？

5、MySQL是如何保證數(shù)據(jù)不丟失?

• 只要 redolog 和 binlog 保證持久化磁盤就能確保 MySQL 異常重啟后數(shù)據(jù)恢復(fù) binlog 寫入機(jī)制。
• redolog確保系統(tǒng)異常后，丟失的數(shù)據(jù)可以重做，binlog將數(shù)據(jù)進(jìn)行歸檔，確保丟失的數(shù)據(jù)可以恢復(fù)。
• 事務(wù)執(zhí)行前先寫redolog，事務(wù)執(zhí)行過程中，先把日志寫到 binlog cache 里，事務(wù)提交的時(shí)候，再把 binlog cache 寫到 binlog 文件中。

6、為什么刪除了表，表文件的大小還是沒變？

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

7、binlog三種格式對(duì)比

• row格式的binlog記錄的操作行的主鍵id以及每個(gè)字段的真實(shí)值，所以不會(huì)出現(xiàn)主備操作數(shù)據(jù)不一致的情況。
• statement：記錄的源SQL語(yǔ)句
• mixed：前兩種混合，為什么還需要有mixed格式的文件，因?yàn)橛行?statement 格式的 binlog 可能會(huì)導(dǎo)致主備不一致，所以要使用 row 格式。但 row 格式的缺點(diǎn)是，很占空間。MySQL 就取了個(gè)折中方案，MySQL 自己會(huì)判斷這條 SQL 語(yǔ)句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

8、MySQL加鎖規(guī)則

• 原則 1：加鎖的基本單位是 next-key lock，next-key lock 是前開后閉區(qū)間。
• 原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖
• 優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。
• 優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖
• 一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止。

9、什么是臟讀、不可重復(fù)讀、幻讀呢？

• 「臟讀」: 臟讀指的是讀到了其他事務(wù)未提交的數(shù)據(jù)，未提交意味著這些數(shù)據(jù)可能會(huì)回滾，也就是可能最終不會(huì)存到數(shù)據(jù)庫(kù)中，也就是不存在的數(shù)據(jù)。讀到了并不一定最終存在的數(shù)據(jù)，這就是臟讀。
• 「不可重復(fù)讀」: 不可重復(fù)讀指的是在一個(gè)事務(wù)內(nèi)，最開始讀到的數(shù)據(jù)和事務(wù)結(jié)束前的任意時(shí)刻讀到的同一批數(shù)據(jù)出現(xiàn)不一致的情況。
• 「幻讀」: 幻讀，并不是說兩次讀取獲取的結(jié)果集不同，幻讀側(cè)重的方面是某一次的 select 操作得到的結(jié)果的數(shù)據(jù)狀態(tài)無(wú)法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些：select 某記錄是否存在，不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在，無(wú)法插入，此時(shí)就發(fā)生了幻讀。

10、MySQL都有哪些鎖呢?像上面那樣子進(jìn)行鎖定豈不是有點(diǎn)阻礙并發(fā)效率了?

• 從鎖的類別上來講,有共享鎖和排他鎖。
  • 1）共享鎖: 又叫做讀鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí),對(duì)數(shù)據(jù)加上共享鎖.共享鎖可以同時(shí)加上多個(gè)。
  • 2）排他鎖: 又叫做寫鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時(shí),對(duì)數(shù)據(jù)加上排他鎖.排他鎖只可以加一個(gè),他和其他的排他鎖,共享鎖都相斥。
• 鎖的粒度取決于具體的存儲(chǔ)引擎,InnoDB實(shí)現(xiàn)了行級(jí)鎖,頁(yè)級(jí)鎖,表級(jí)鎖。

• 他們的加鎖開銷從大大小,并發(fā)能力也是從大到小。

框架篇

1、Mysql 主從復(fù)制原理的是啥？

• Master的更新事件(update、insert、delete)會(huì)按照順序?qū)懭隻in-log中。當(dāng)Slave連接到Master的后,Master機(jī)器會(huì)為Slave開啟binlog dump線程,該線程會(huì)去讀取bin-log日志。
• Slave連接到Master后,Slave庫(kù)有一個(gè)I/O線程?通過請(qǐng)求binlog dump Thread讀取bin-log日志,然后寫入從庫(kù)的relay log日志中。
• Slave還有一個(gè)?SQL線程,實(shí)時(shí)監(jiān)控 relay-log日志內(nèi)容是否有更新,解析文件中的SQL語(yǔ)句,在Slave數(shù)據(jù)庫(kù)中去執(zhí)行。?

2、Mysql主從復(fù)制同步方式有哪些？

• 異步復(fù)制：Mysql主從同步 默認(rèn)是異步復(fù)制的。就是上面三步中,只有第一步是同步的(也就是Mater寫入bin log日志),就是主庫(kù)寫入binlog日志后即可成功返回客戶端，無(wú)須等待binlog日志傳遞給從庫(kù)的過程。
• 同步復(fù)制：對(duì)于同步復(fù)制而言，Master主機(jī)將事件發(fā)送給Slave主機(jī)后會(huì)觸發(fā)一個(gè)等待，直到所有Slave節(jié)點(diǎn)（如果有多個(gè)Slave）返回?cái)?shù)據(jù)復(fù)制成功的信息給Master。
• 半同步復(fù)制：對(duì)于半同步復(fù)制而言，Master主機(jī)將事件發(fā)送給Slave主機(jī)后會(huì)觸發(fā)一個(gè)等待，直到其中一個(gè)Slave節(jié)點(diǎn)（如果有多個(gè)Slave）返回?cái)?shù)據(jù)復(fù)制成功的信息給Master。

3、Mysql主從同步延時(shí)產(chǎn)生原因?怎么優(yōu)化？

• 主節(jié)點(diǎn)如果執(zhí)行一個(gè)很大的事務(wù)，那么就會(huì)對(duì)主從延遲產(chǎn)生較大的影響

• 網(wǎng)絡(luò)延遲，日志較大，slave數(shù)量過多

• 主上多線程寫入，從節(jié)點(diǎn)只有單線程同步

• 機(jī)器性能問題，從節(jié)點(diǎn)是否使用了“爛機(jī)器”

• 鎖沖突問題也可能導(dǎo)致從機(jī)的SQL線程執(zhí)行慢

4、Mysql主從同步延時(shí)產(chǎn)生原因?怎么優(yōu)化？

• 大事務(wù)：將大事務(wù)分為小事務(wù)，分批更新數(shù)據(jù)
• 減少Slave的數(shù)量，不要超過5個(gè)，減少單次事務(wù)的大小
• Mysql 5.7之后，可以使用多線程復(fù)制，使用MGR復(fù)制架構(gòu)
• 在磁盤、raid卡、調(diào)度策略有問題的情況下可能會(huì)出現(xiàn)單個(gè)IO延遲很高的情況，可用iostat命令查看DB數(shù)據(jù)盤的IO情況，再進(jìn)一步判斷
• 針對(duì)鎖問題可以通過抓去processlist以及查看information_schema下面和鎖以及事務(wù)相關(guān)的表來查看。

6、bin log/redo log/undo log是什么？

• bin log是Mysql數(shù)據(jù)庫(kù)級(jí)別的文件，記錄對(duì)Mysql數(shù)據(jù)庫(kù)執(zhí)行修改的所有操作，不會(huì)記錄select和show語(yǔ)句。
• redo log中記錄的是要更新的數(shù)據(jù)，比如一條數(shù)據(jù)已提交成功，并不會(huì)立即同步到磁盤，而是先記錄到redo log中，等待合適的時(shí)機(jī)再刷盤，為了實(shí)現(xiàn)事務(wù)的持久性。
• undo log用于數(shù)據(jù)的撤回操作，它保留了記錄修改前的內(nèi)容。通過undo log可以實(shí)現(xiàn)事務(wù)回滾，并且可以根據(jù)undo log回溯到某個(gè)特定的版本的數(shù)據(jù)，實(shí)現(xiàn)MVCC。

推薦學(xué)習(xí)：mysql