MySQL的基礎(chǔ)問題有哪些

本篇內(nèi)容主要講解“MySQL的基礎(chǔ)問題有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQL的基礎(chǔ)問題有哪些”吧!

常規(guī)篇

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

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

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

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

• MySQL數(shù)據(jù)庫本身被堵住了，比如：系統(tǒng)或網(wǎng)絡(luò)資源不夠

• SQL語句被堵住了，比如：表鎖，行鎖等，導(dǎo)致存儲引擎不執(zhí)行對應(yīng)的SQL語句

• 確實是索引使用不當(dāng)，沒有走索引

• 表中數(shù)據(jù)的特點導(dǎo)致的，走了索引，但回表次數(shù)龐大

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

• 對于count(*)、count(常數(shù))、count(主鍵)形式的count函數(shù)來說，優(yōu)化器可以選擇掃描成本最小的索引執(zhí)行查詢，從而提升效率，它們的執(zhí)行過程是一樣的。

• 而對于count(非索引列)來說，優(yōu)化器選擇全表掃描，說明只能在聚集索引的葉子結(jié)點順序掃描。

• count(二級索引列)只能選擇包含我們指定的列的索引去執(zhí)行查詢，可能導(dǎo)致優(yōu)化器選擇的索引執(zhí)行的代價并不是最小。

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

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

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

• DML誤操作語句：可以通過flashback，先解析binlog event,然后在進(jìn)行反轉(zhuǎn)。

• DDL語句誤操作：只能通過全量備份+應(yīng)用binlog的方式來恢復(fù)數(shù)據(jù)。一旦數(shù)據(jù)量比較大,那么恢復(fù)時間就特別長。

• rm 刪除：使用備份跨機房，或者最好是跨城市保存。

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

• DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。

• TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會激活與表有關(guān)的刪除觸發(fā)器，執(zhí)行速度快。

• drop語句將表所占用的空間全釋放掉。

6、MySQL大表查詢?yōu)槭裁床粫瑑?nèi)存？

• MySQL 是“邊讀邊發(fā)的”，這就意味著，如果客戶端接收得慢，會導(dǎo)致 MySQL 服務(wù)端由于結(jié)果發(fā)不出去，這個事務(wù)的執(zhí)行時間變長。

• 服務(wù)端并不需要保存一個完整的結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程都是通過一個next_buffer來操作的。

• 內(nèi)存的數(shù)據(jù)頁是在 Buffer Pool (BP) 中管理的。

• InnoDB 管理 Buffer Pool 使用改進(jìn)的 LRU 算法，是用鏈表來實現(xiàn)的。在 InnoDB 實現(xiàn)上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域，確保大批量加載冷數(shù)據(jù)時不會沖掉熱點數(shù)據(jù)。

7、深度分頁（超大分頁）怎么處理?

• 用id優(yōu)化：先找到上次分頁的最大ID,然后利用id上的索引來查詢,類似于select * from user where id>1000000 limit 100。

• 用覆蓋索引優(yōu)化：Mysql的查詢完全命中索引的時候,稱為覆蓋索引,是非?？斓?因為查詢只需要在索引上進(jìn)行查找,之后可以直接返回,而不用再回表拿數(shù)據(jù).因此我們可以先查出索引的ID,然后根據(jù)Id拿數(shù)據(jù)。

• 在業(yè)務(wù)允許的情況下限制頁數(shù)

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

• 添加合適索引：對作為查詢條件和order by的字段建立索引，對于多個查詢字段的考慮建立組合索引，同時注意組合索引字段的順序,將最常用作限制條件的列放在最左邊，依次遞減，索引不宜太多，一般5個以內(nèi)。

• 優(yōu)化表結(jié)構(gòu)：數(shù)字型字段優(yōu)于字符串類型，數(shù)據(jù)類型更小通常更好，盡量使用 NOT NULL

• 優(yōu)化查詢語句：分析SQl執(zhí)行計劃，是否命中索引等，如果SQL很復(fù)雜，優(yōu)化SQL結(jié)構(gòu)，如果表數(shù)據(jù)量太大，考慮分表

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

• 在執(zhí)行show processlist的結(jié)果里，看到了幾千個連接，指的是并發(fā)連接。

• 而"當(dāng)前正在執(zhí)行"的語句，才是并發(fā)查詢。

• 并發(fā)連接數(shù)多影響的是內(nèi)存。

• 并發(fā)查詢太高對CPU不利。一個機器的CPU核數(shù)有限，線程全沖進(jìn)來，上下文切換的成本就會太高。

• 需要注意的是，在線程進(jìn)入鎖等待以后，并發(fā)線程計數(shù)減一，所以等行鎖或者間隙鎖時的線程是不算在計數(shù)范圍內(nèi)的。也就是說進(jìn)入鎖等待的線程不吃CPU，從而避免整個系統(tǒng)鎖死。

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

• 相同的數(shù)據(jù)時，不會做update更新。

• 不過對不同的binlog格式，處理的日志方式有所不同：

• 1）基于row模式時，server層匹配到要更新的記錄，發(fā)現(xiàn)新值和舊值一致，不做更新，就直接返回，也不記錄binlog。

• 2）基于 statement 或者 mixed格式時，MySQL執(zhí)行 update 語句，并把更新語句記錄到binlog。

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

• datetime 的日期范圍是 1001——9999 年；timestamp 的時間范圍是 1970——2038 年

• datetime 存儲時間與時區(qū)無關(guān)；timestamp 存儲時間與時區(qū)有關(guān)，顯示的值也依賴于時區(qū)

• datetime 的存儲空間為 8 字節(jié)；timestamp 的存儲空間為 4 字節(jié)

• datetime 的默認(rèn)值為 null；timestamp 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時間(current_timestamp)

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

• 「讀未提交」（Read Uncommitted）最低級別，任何情況都無法保證

• 「讀已提交」（Read Committed）可避免臟讀的發(fā)生

• 「可重復(fù)讀」（Repeatable Read）可避免臟讀、不可重復(fù)讀的發(fā)生

• 「串行化」（Serializable）可避免臟讀、不可重復(fù)讀、幻讀的發(fā)生

• Mysql默認(rèn)的事務(wù)隔離級別是「可重復(fù)讀」(Repeatable Read)

13、在 MySQL 中有兩個 kill 命令

• kill query + 線程 id，表示終止這個線程中正在執(zhí)行的語句

• kill connection + 線程 id，這里 connection 可缺省，表示斷開這個線程的連接

索引篇

1、索引分類有哪些？

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

• 主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。

• 非主鍵索引的葉子節(jié)點內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index）。

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

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

   

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

  

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

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

• 哈希索引能夠以 O(1) 的速度處理單個數(shù)據(jù)行的增刪改查，但是面對范圍查詢或者排序時就會導(dǎo)致全表掃描的結(jié)果。

• B樹可以在非葉結(jié)點中存儲數(shù)據(jù)，由于所有的節(jié)點都可能包含目標(biāo)數(shù)據(jù)，我們總是要從根節(jié)點向下遍歷子樹查找滿足條件的數(shù)據(jù)行，這個特點帶來了大量的隨機 I/O，造成性能下降。

• B+樹所有的數(shù)據(jù)行都存儲在葉節(jié)點中，而這些葉節(jié)點可以通過『指針』依次按順序連接，當(dāng)我們在如下所示的 B+ 樹遍歷數(shù)據(jù)時可以直接在多個子節(jié)點之間進(jìn)行跳轉(zhuǎn)，這樣能夠節(jié)省大量的磁盤 I/O 時間。

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

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

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

• 在InnoDB里，索引B+ Tree的葉子節(jié)點存儲了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引，即將數(shù)據(jù)存儲與索引放到了一塊，找到索引也就找到了數(shù)據(jù)。

• 而索引B+Tree的葉子節(jié)點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引、二級索引。

• 第一次索引一般是順序IO，回表的操作屬于隨機IO。需要回表的次數(shù)越多，即隨機IO次數(shù)越多，我們就越傾向于使用全表掃描 。

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

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

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

• 最左前綴原則就是最左優(yōu)先，在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。

• MySQL會一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 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)化器會幫你優(yōu)化成索引可以識別的形式。

7、什么是索引下推？

• 滿足最左前綴原則的時候，最左前綴可以用于在索引中定位記錄。

• 在 MySQL 5.6 之前，只能從ID開始一個個回表。到主鍵索引上找出數(shù)據(jù)行，再對比字段值。

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

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

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

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

• 「原子性」：是使用 undo log 來實現(xiàn)的，如果事務(wù)執(zhí)行過程中出錯或者用戶執(zhí)行了rollback，系統(tǒng)通過undo log日志返回事務(wù)開始的狀態(tài)。

• 「持久性」：使用 redo log 來實現(xiàn)，只要redo log日志持久化了，當(dāng)系統(tǒng)崩潰，即可通過redo log把數(shù)據(jù)恢復(fù)。

• 「隔離性」：通過鎖以及 MVCC,使事務(wù)相互隔離開。

• 「一致性」：通過回滾、恢復(fù)，以及并發(fā)情況下的隔離性，從而實現(xiàn)一致性。

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

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

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

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

11、索引有哪些分類？

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

• 主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index）。

• 非主鍵索引的葉子節(jié)點內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index）。

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

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

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

• 對索引使用函數(shù)/對索引進(jìn)行表達(dá)式計算：因為索引保存的是索引字段的原始值，而不是經(jīng)過函數(shù)計算后的值，自然就沒辦法走索引了。

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

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

方案篇

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

• 停機擴容（不推薦）

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

2、如何設(shè)計可以動態(tài)擴容縮容的分庫分表方案？

原理篇

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

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

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

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

• MySQL會為每個線程分配一個內(nèi)存（sort_buffer）用于排序該內(nèi)存大小為sort_buffer_size。

• 如果排序的數(shù)據(jù)量小于sort_buffer_size，排序?qū)趦?nèi)存中完成。

• 如果排序數(shù)據(jù)量很大，內(nèi)存中無法存下這么多數(shù)據(jù)，則會使用磁盤臨時文件來輔助排序，也稱外部排序。

• 在使用外部排序時，MySQL會分成好幾份單獨的臨時文件用來存放排序后的數(shù)據(jù)，然后在將這些文件合并成一個大文件。

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

• MVCC(Multiversion concurrency control) 就是同一份數(shù)據(jù)保留多版本的一種方式，進(jìn)而實現(xiàn)并發(fā)控制。在查詢的時候，通過read view和版本鏈找到對應(yīng)版本的數(shù)據(jù)。

• 作用：提升并發(fā)性能。對于高并發(fā)場景，MVCC 比行級鎖開銷更小。

• MVCC 的實現(xiàn)依賴于版本鏈，版本鏈?zhǔn)峭ㄟ^表的三個隱藏字段實現(xiàn)。

• 1）DB_TRX_ID：當(dāng)前事務(wù) id，通過事務(wù) id 的大小判斷事務(wù)的時間順序。

• 2）DB_ROLL_PRT：回滾指針，指向當(dāng)前行記錄的上一個版本，通過這個指針將數(shù)據(jù)的多個版本連接在一起構(gòu)成undo log版本鏈。

• 3）DB_ROLL_ID：主鍵，如果數(shù)據(jù)表沒有主鍵，InnoDB 會自動生成主鍵。

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

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

• 只要 redolog 和 binlog 保證持久化磁盤就能確保 MySQL 異常重啟后數(shù)據(jù)恢復(fù) binlog 寫入機制。

• redolog確保系統(tǒng)異常后，丟失的數(shù)據(jù)可以重做，binlog將數(shù)據(jù)進(jìn)行歸檔，確保丟失的數(shù)據(jù)可以恢復(fù)。

• 事務(wù)執(zhí)行前先寫redolog，事務(wù)執(zhí)行過程中，先把日志寫到 binlog cache 里，事務(wù)提交的時候，再把 binlog cache 寫到 binlog 文件中。

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

• 數(shù)據(jù)項刪除之后InnoDB標(biāo)記 page A 會被標(biāo)記為可復(fù)用

• delete 命令把整個表的數(shù)據(jù)刪除呢？結(jié)果就是，所有的數(shù)據(jù)頁都會被標(biāo)記為可復(fù)用。但是磁盤上，文件不會變小。

• 經(jīng)過大量增刪改的表，都是可能是存在空洞的。這些空洞也占空間所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。

• 重建表，就可以達(dá)到這樣的目的。可以使用 alter table A engine=InnoDB 命令來重建表。

7、binlog三種格式對比

• row格式的binlog記錄的操作行的主鍵id以及每個字段的真實值，所以不會出現(xiàn)主備操作數(shù)據(jù)不一致的情況。

• statement：記錄的源SQL語句

• mixed：前兩種混合，為什么還需要有mixed格式的文件，因為有些 statement 格式的 binlog 可能會導(dǎo)致主備不一致，所以要使用 row 格式。但 row 格式的缺點是，很占空間。MySQL 就取了個折中方案，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

8、MySQL加鎖規(guī)則

• 原則 1：加鎖的基本單位是 next-key lock，next-key lock 是前開后閉區(qū)間。

• 原則 2：查找過程中訪問到的對象才會加鎖

• 優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。

• 優(yōu)化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖

• 一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

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

• 「臟讀」: 臟讀指的是讀到了其他事務(wù)未提交的數(shù)據(jù)，未提交意味著這些數(shù)據(jù)可能會回滾，也就是可能最終不會存到數(shù)據(jù)庫中，也就是不存在的數(shù)據(jù)。讀到了并不一定最終存在的數(shù)據(jù)，這就是臟讀。

• 「不可重復(fù)讀」: 不可重復(fù)讀指的是在一個事務(wù)內(nèi)，最開始讀到的數(shù)據(jù)和事務(wù)結(jié)束前的任意時刻讀到的同一批數(shù)據(jù)出現(xiàn)不一致的情況。

• 「幻讀」: 幻讀，并不是說兩次讀取獲取的結(jié)果集不同，幻讀側(cè)重的方面是某一次的 select 操作得到的結(jié)果的數(shù)據(jù)狀態(tài)無法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些：select 某記錄是否存在，不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時發(fā)現(xiàn)此記錄已存在，無法插入，此時就發(fā)生了幻讀。

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

• 從鎖的類別上來講,有共享鎖和排他鎖。

• 1）共享鎖: 又叫做讀鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時,對數(shù)據(jù)加上共享鎖.共享鎖可以同時加上多個。

• 2）排他鎖: 又叫做寫鎖. 當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時,對數(shù)據(jù)加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥。

• 鎖的粒度取決于具體的存儲引擎,InnoDB實現(xiàn)了行級鎖,頁級鎖,表級鎖。

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

框架篇

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

• Master的更新事件(update、insert、delete)會按照順序?qū)懭?code>bin-log中。當(dāng)Slave連接到Master的后,Master機器會為Slave開啟binlog dump線程,該線程會去讀取bin-log日志。

• Slave連接到Master后,Slave庫有一個I/O線程 通過請求binlog dump thread讀取bin-log日志,然后寫入從庫的relay log日志中。

• Slave還有一個 SQL線程,實時監(jiān)控 relay-log日志內(nèi)容是否有更新,解析文件中的SQL語句,在Slave數(shù)據(jù)庫中去執(zhí)行。

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

• 異步復(fù)制：Mysql主從同步 默認(rèn)是異步復(fù)制的。就是上面三步中,只有第一步是同步的(也就是Mater寫入bin log日志),就是主庫寫入binlog日志后即可成功返回客戶端，無須等待binlog日志傳遞給從庫的過程。

• 同步復(fù)制：對于同步復(fù)制而言，Master主機將事件發(fā)送給Slave主機后會觸發(fā)一個等待，直到所有Slave節(jié)點（如果有多個Slave）返回數(shù)據(jù)復(fù)制成功的信息給Master。

• 半同步復(fù)制：對于半同步復(fù)制而言，Master主機將事件發(fā)送給Slave主機后會觸發(fā)一個等待，直到其中一個Slave節(jié)點（如果有多個Slave）返回數(shù)據(jù)復(fù)制成功的信息給Master。

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

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

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

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

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

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

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

• 大事務(wù)：將大事務(wù)分為小事務(wù)，分批更新數(shù)據(jù)

• 減少Slave的數(shù)量，不要超過5個，減少單次事務(wù)的大小

• Mysql 5.7之后，可以使用多線程復(fù)制，使用MGR復(fù)制架構(gòu)

• 在磁盤、raid卡、調(diào)度策略有問題的情況下可能會出現(xiàn)單個IO延遲很高的情況，可用iostat命令查看DB數(shù)據(jù)盤的IO情況，再進(jìn)一步判斷

• 針對鎖問題可以通過抓去processlist以及查看information_schema下面和鎖以及事務(wù)相關(guān)的表來查看。

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

• bin log是Mysql數(shù)據(jù)庫級別的文件，記錄對Mysql數(shù)據(jù)庫執(zhí)行修改的所有操作，不會記錄select和show語句。

• redo log中記錄的是要更新的數(shù)據(jù)，比如一條數(shù)據(jù)已提交成功，并不會立即同步到磁盤，而是先記錄到redo log中，等待合適的時機再刷盤，為了實現(xiàn)事務(wù)的持久性。

• undo log用于數(shù)據(jù)的撤回操作，它保留了記錄修改前的內(nèi)容。通過undo log可以實現(xiàn)事務(wù)回滾，并且可以根據(jù)undo log回溯到某個特定的版本的數(shù)據(jù)，實現(xiàn)MVCC。

到此，相信大家對“MySQL的基礎(chǔ)問題有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！