MySQL如何進行優(yōu)化

這篇文章主要講解了“MySQL如何進行優(yōu)化”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL如何進行優(yōu)化”吧！

•  案例背景

• 案例分析

• MySQL 主從結構

• 案例解答

• 總結

案例背景

假設你公司面臨雙 11 大促，投入了大量營銷費用用于平臺推廣，這帶來了巨大的流量，如果你是訂單系統(tǒng)的技術負責人，要怎么應對突如其來的讀寫流量呢?

這是一個很典型的應用場景，我想很多研發(fā)工程師會回答：通過 Redis 作為 MySQL 的緩存，然后當用戶查看“訂單中心”時，通過查詢訂單緩存，幫助  MySQL 抗住大部分的查詢請求。

應用緩存的原則之一是保證緩存命中率足夠高，不然很多請求會穿透緩存，最終打到數(shù)據(jù)庫上。然而在“訂單中心”這樣的場景中，每個用戶的訂單都不同，除非全量緩存數(shù)據(jù)庫訂單信息(又會帶來架構的復雜度)，不然緩存的命中率依舊很低。

所以在這種場景下，緩存只能作為數(shù)據(jù)庫的前置保護機制，但是還會有很多流量打到數(shù)據(jù)庫上，并且隨著用戶訂單不斷增多，請求到 MySQL  上的讀寫流量會越來越多，當單臺 MySQL 支撐不了大量的并發(fā)請求時，該怎么辦?

案例分析

互聯(lián)網大部分系統(tǒng)的訪問流量是讀多寫少，讀寫請求量的差距可能達到幾個數(shù)量級，就好比你在京東上的商品的瀏覽量肯定遠大于你的下單量。

所以你要考慮優(yōu)化數(shù)據(jù)庫來抗住高查詢請求，首先要做的就是區(qū)分讀寫流量區(qū)，這樣才方便針對讀流量做單獨擴展，這個過程就是流量的“讀寫分離”。

讀寫分離是提升 MySQL 并發(fā)的首選方案，因為當單臺 MySQL 無法滿足要求時，就只能用多個具有相同數(shù)據(jù)的 MySQL  實例組成的集群來承擔大量的讀寫請求。

MySQL 主從結構

MySQL 做讀寫分離的前提，是把 MySQL 集群拆分成“主 + 從”結構的數(shù)據(jù)集群，這樣才能實現(xiàn)程序上的讀寫分離，并且 MySQL  集群的主庫、從庫的數(shù)據(jù)是通過主從復制實現(xiàn)同步的。

那么面試官會問你“MySQL 集群如何實現(xiàn)主從復制?” 換一種問法就是“當你提交一個事務到 MySQL 集群后，MySQL  都執(zhí)行了哪些操作?”面試官往往會以該問題為切入點，挖掘你對 MySQL  集群主從復制原理的理解，然后再模擬一個業(yè)務場景，讓你給出解決主從復制問題的架構設計方案。

所以，針對面試官的套路，你要做好以下的準備：

• 掌握讀多寫少場景下的架構設計思路，知道緩存不能解決所有問題，“讀寫分離”是提升系統(tǒng)并發(fā)能力的重要手段。

• 深入了解數(shù)據(jù)庫的主從復制，掌握它的原理、問題，以及解決方案。

• 從實踐出發(fā)，做到技術的認知抽象，從方法論層面來看設計。

案例解答

MySQL 主從復制的原理無論是“MySQL 集群如何實現(xiàn)主從復制”還是“當你提交一個事務到 MySQL 集群后，MySQL  集群都執(zhí)行了哪些操作?”面試官主要是問你：MySQL 的主從復制的過程是怎樣的?

總的來講，MySQL 的主從復制依賴于 binlog ，也就是記錄 MySQL 上的所有變化并以二進制形式保存在磁盤上。復制的過程就是將 binlog  中的數(shù)據(jù)從主庫傳輸?shù)綇膸焐?。這個過程一般是異步的，也就是主庫上執(zhí)行事務操作的線程不會等待復制 binlog 的線程同步完成。

為了方便你記憶，我把 MySQL 集群的主從復制過程梳理成 3 個階段。

• 寫入 Binlog：主庫寫 binlog 日志，提交事務，并更新本地存儲數(shù)據(jù)。

• 同步 Binlog：把 binlog 復制到所有從庫上，每個從庫把 binlog 寫到暫存日志中。

• 回放 Binlog：回放 binlog，并更新存儲數(shù)據(jù)。

但在面試中你不能簡單地只講這幾個階段，要盡可能詳細地說明主庫和從庫的數(shù)據(jù)同步過程，為的是讓面試官感受到你技術的扎實程度(詳細過程如下)。

MySQL 主庫在收到客戶端提交事務的請求之后，會先寫入  binlog，再提交事務，更新存儲引擎中的數(shù)據(jù)，事務提交完成后，返回給客戶端“操作成功”的響應。

從庫會創(chuàng)建一個專門的 I/O 線程，連接主庫的 log dump 線程，來接收主庫的 binlog 日志，再把 binlog 信息寫入 relay log  的中繼日志里，再返回給主庫“復制成功”的響應。

從庫會創(chuàng)建一個用于回放 binlog 的線程，去讀 relay log 中繼日志，然后回放 binlog  更新存儲引擎中的數(shù)據(jù)，最終實現(xiàn)主從的數(shù)據(jù)一致性。

在完成主從復制之后，你就可以在寫數(shù)據(jù)時只寫主庫，在讀數(shù)據(jù)時只讀從庫，這樣即使寫請求會鎖表或者鎖記錄，也不會影響讀請求的執(zhí)行。

同時，在讀流量比較大時，你可以部署多個從庫共同承擔讀流量，這就是“一主多從”的部署方式，你在垂直電商項目中可以用該方式抵御較高的并發(fā)讀流量。另外，從庫也可以作為一個備庫，以避免主庫故障導致的數(shù)據(jù)丟失。

MySQL 一主多從

當然，一旦你提及“一主多從”，面試官很容易設陷阱問你：那大促流量大時，是不是只要多增加幾臺從庫，就可以抗住大促的并發(fā)讀請求了?

當然不是。

因為從庫數(shù)量增加，從庫連接上來的 I/O 線程也比較多，主庫也要創(chuàng)建同樣多的 log dump  線程來處理復制的請求，對主庫資源消耗比較高，同時還受限于主庫的網絡帶寬。所以在實際使用中，一個主庫一般跟 2～3 個從庫(1 套數(shù)據(jù)庫，1 主 2 從 1  備主)，這就是一主多從的 MySQL 集群結構。

其實，你從 MySQL 主從復制過程也能發(fā)現(xiàn)，MySQL 默認是異步模式：MySQL 主庫提交事務的線程并不會等待 binlog  同步到各從庫，就返回客戶端結果。這種模式一旦主庫宕機，數(shù)據(jù)就會發(fā)生丟失。

而這時，面試官一般會追問你“MySQL 主從復制還有哪些模型?”主要有三種。

• 同步復制：事務線程要等待所有從庫的復制成功響應。

• 異步復制：事務線程完全不等待從庫的復制成功響應。

• 半同步復制：MySQL 5.7  版本之后增加的一種復制方式，介于兩者之間，事務線程不用等待所有的從庫復制成功響應，只要一部分復制成功響應回來就行，比如一主二從的集群，只要數(shù)據(jù)成功復制到任意一個從庫上，主庫的事務線程就可以返回給客戶端。

這種半同步復制的方式，兼顧了異步復制和同步復制的優(yōu)點，即使出現(xiàn)主庫宕機，至少還有一個從庫有最新的數(shù)據(jù)，不存在數(shù)據(jù)丟失的風險。

講到這兒，你基本掌握了 MySQL 主從復制的原理，但如果面試官想挖掘你的架構設計能力，還會從架構設計上考察你怎么解決 MySQL  主從復制延遲的問題，比如問你“在系統(tǒng)設計上有哪些方案可以解決主從復制的延遲問題?”

從架構上解決主從復制延遲

我們來結合實際案例設計一個主從復制延遲的解決方案。

在電商平臺，每次用戶發(fā)布商品評論時，都會先調用評論審核，目的是對用戶發(fā)布的商品評論進行如言論監(jiān)控、圖片鑒黃等操作。

評論在更新完主庫后，商品發(fā)布模塊會異步調用審核模塊，并把評論 ID 傳遞給審核模塊，然后再由評論審核模塊用評論 ID  查詢從庫中獲取到完整的評論信息。此時如果主從數(shù)據(jù)庫存在延遲，在從庫中就會獲取不到評論信息，整個流程就會出現(xiàn)異常。

主從延遲影響評論讀取的實時性

這是主從復制延遲導致的查詢異常，解決思路有很多，我提供給你幾個方案。

使用數(shù)據(jù)冗余

可以在異步調用審核模塊時，不僅僅發(fā)送商品  ID，而是發(fā)送審核模塊需要的所有評論信息，借此避免在從庫中重新查詢數(shù)據(jù)(這個方案簡單易實現(xiàn)，推薦你選擇)。但你要注意每次調用的參數(shù)大小，過大的消息會占用網絡帶寬和通信時間。

使用緩存解決

可以在寫入數(shù)據(jù)主庫的同時，把評論數(shù)據(jù)寫到 Redis 緩存里，這樣其他線程再獲取評論信息時會優(yōu)先查詢緩存，也可以保證數(shù)據(jù)的一致性。

不過這種方式會帶來緩存和數(shù)據(jù)庫的一致性問題，比如兩個線程同時更新數(shù)據(jù)，操作步驟如下：

線程 A 先更新數(shù)據(jù)庫為 100，此時線程 B 把數(shù)據(jù)庫和緩存中的數(shù)據(jù)都更新成了 200，然后線程 A 又把緩存更新為 100，這樣數(shù)據(jù)庫中的值 200  和緩存中的值 100 就不一致了。

總的來說，通過緩存解決 MySQL 主從復制延遲時，會出現(xiàn)數(shù)據(jù)庫與緩存數(shù)據(jù)不一致的情況。

直接查詢主庫

該方案在使用時一定要謹慎，你要提前明確查詢的數(shù)據(jù)量不大，不然會出現(xiàn)主庫寫請求鎖行，影響讀請求的執(zhí)行，最終對主庫造成比較大的壓力。

當然了，面試官除了從架構上考察你對 MySQL主從復制延遲的理解，還會問你一些擴展問題，比如：當 MySQL  做了主從分離后，對于數(shù)據(jù)庫的使用方式就發(fā)生了變化，以前只需要使用一個數(shù)據(jù)庫地址操作數(shù)據(jù)庫，現(xiàn)在卻要使用一個主庫地址和多個從庫地址，并且還要區(qū)分寫入操作和查詢操作，那從工程代碼上設計，怎么實現(xiàn)主庫和從庫的數(shù)據(jù)訪問呢?

實現(xiàn)主庫和從庫的數(shù)據(jù)庫訪問

一種簡單的做法是：提前把所有數(shù)據(jù)源配置在工程中，每個數(shù)據(jù)源對應一個主庫或者從庫，然后改造代碼，在代碼邏輯中進行判斷，將 SQL  語句發(fā)送給某一個指定的數(shù)據(jù)源來處理。

這個方案簡單易實現(xiàn)，但 SQL 路由規(guī)則侵入代碼邏輯，在復雜的工程中不利于代碼的維護。

另一個做法是：獨立部署的代理中間件，如 MyCat，這一類中間件部署在獨立的服務器上，一般使用標準的 MySQL 通信協(xié)議，可以代理多個數(shù)據(jù)庫。

該方案的優(yōu)點是隔離底層數(shù)據(jù)庫與上層應用的訪問復雜度，比較適合有獨立運維團隊的公司選型;缺陷是所有的 SQL  語句都要跨兩次網絡傳輸，有一定的性能損耗，再就是運維中間件是一個專業(yè)且復雜的工作，需要一定的技術沉淀。

總結

我們先從一個案例出發(fā)，了解了在互聯(lián)網流量讀多寫少的情況下，需要通過“讀寫分離”提升系統(tǒng)的并發(fā)能力，又因為“讀寫分離”的前提是做  “主+從”的數(shù)據(jù)集群架構，所以我們又講了主從復制的原理，以及怎么解決主從復制帶來的延遲。

感謝各位的閱讀，以上就是“MySQL如何進行優(yōu)化”的內容了，經過本文的學習后，相信大家對MySQL如何進行優(yōu)化這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！