掌握MYSQL進階

免費學習推薦：mysql影片教學

文章目錄

• 1 前言
• • 1.1 資料庫架構
  • 1.2 監控資訊
• 2 影響資料庫的因素
• • 2.1 超高的QPS和TPS
  • 2.2 大量的並發和超高的CPU使用率
  • 2.3 磁碟IO
  • 2.4 網路卡流量
  • 2.5 大表
  • • 2.5.1 大表對查詢的影響
    • 2.5.2 大表對DDL操作的影響
    • 2.5.3 如何處理資料庫中的大表
  • #2.6 大事務
  • • 2.6.1 什麼是交易
    • 2.6.2 交易的原子性（ATOMICITY）
    • 2.6.3 交易的一致性（CONSISTENCY）
    • 2.6.4 交易的隔離性（ISOLATION）
    • 2.6.5 交易的持久性（DURABILITY）
    • 2.6.7 什麼是大交易

1 前言

伺服器的壓力來很大一部分壓力來自於資料庫的效能，如果沒有穩定的資料庫及伺服器環境，那麼伺服器很容易出現一些故障甚至是宕機，造成的後果也是不可估量的， 因此資料庫的效能最佳化不可或缺。

1.1 資料庫架構

一般的資料庫架構都是一台主伺服器，下面搭載著幾個或十幾個從伺服器進行主從同步，當主伺服器宕機之後，需要程式設計師手動選出一台資料最新的從伺服器接替主伺服器，然後對新的主伺服器進行同步。然而有時因為從伺服器較多，導致這個過程是相當耗時的，並且在這個過程也是對網卡的容量的一個挑戰。

1.2 監控訊息

QPS & TPS：數值越高越好。
並發量：同一時間處理的請求的數量。
CPU使用率：越低越好。
磁碟IO：讀寫效能越高越好。
注意：一般公司在大促活動前後，最好不要在主庫上進行資料庫備份，或在大型活動前取消這類計劃，因為這會嚴重損耗伺服器的效能。

2 影響資料庫的因素

影響資料庫的因素有很多，例如有：sql查詢速度，伺服器硬件，網卡流量，磁碟IO等等，後面我們會細說，以下介紹一下一些監控訊息中回饋給我們的訊息，以及我們應該如何優化它。

2.1 超高的QPS和TPS

由於效率低的SQL，往往會造成超高的QPS和TPS的風險。在一般的大促期間，網站的訪問量會大大的提高，也會提高資料庫的QPS和TPS。
什麼是QPS：每秒鐘處理的查詢量。例如我們有一個cpu的情況下，10ms可以處理一個sql，那麼1s就可以處理100個sql，QPS

2.2 大量的並發和超高的CPU使用率

那在這種情況下會導致什麼風險呢？
在大量的並發下，可能會導致資料庫連線數被佔滿，這種情況下，盡量將資料庫參數max_connections設定的大一點（預設值為100），如果超過了這個值的時候會出現報500錯誤的情況。
在超高的CPU使用率下，會因CPU資源耗盡而出現宕機。

2.3 磁碟IO

資料庫的瓶頸之一就是磁碟IO，那麼它會帶來幾點風險：

1. 磁碟IO效能突然下降
   這往往會發生在熱資料大於伺服器可用記憶體的情況下。通常這種情況我們只能使用更快的磁碟設備來解決。
2. 其他大量消耗磁碟效能的排程任務
   這一點我們上面也提到了，最好避免在大促之前在主資料庫上進行備份，或在從伺服器上進行，調整排程任務，做好磁碟維護。

2.4 網路卡流量

顯而易見，網路卡流量過大造成網路卡IO被佔滿的風險。
一般的網卡是千兆網卡（1000Mb/8 ≈ 100MB）
如果連接數超過了網卡最大容量的時候，就會出現的服務無法連接的情況，那麼我們應該如何避免無法連接資料庫的情況：

1. 減少從伺服器的數量
   因為每個從伺服器都要從主伺服器上面複製日誌，因此從伺服器越多，網路卡流量就越大。
2. 進行分級快取
   一定要避免前端快取突然失效而導致的後端存取量突然變大的情況。
3. 避免使用 select *進行查詢
   這是一種最基本的資料庫最佳化的方法，查詢出沒有必要的欄位也會消耗大量的網路流量。
4. 分離業務網路和伺服器網路
   這樣可以避免主從同步或網路備份影響網路的效能。

2.5 大表

什麼樣的表格可以稱為大表？其實都是相對而言，對於不同儲存引擎會有不同的限制。像nosql的資料存儲，並沒有限製表的行數，理論上只要磁碟的容量允許，都可以進行存儲。但當一張表的行數超過千萬行的時候，就會對資料庫的效能產生很大的影響。那我們可以總結大表的特點：

• 記錄行數龐大，單表超過千萬行
• 表格資料檔龐大，表格資料檔超過10G

但就算符合了以上的特點，它也可能對我們資料庫效能不會產生很大的影響，因此這個說法是相對的，例如像一般資料庫的日誌表，即使記錄行數很大，文件大小很大，但我們一般只對它進行增加和查詢，不涉及大量的i修改和刪除操作，因此不會對資料庫效能產生很大的影響。
但當有一天因為業務發生變更，需要對這張10個G的日誌表進行列增加的時候，那麼這個工程量無疑是巨大的。

2.5.1 大表對查詢的影響

大表往往代表慢查詢的產生，慢查詢即是指很難在一定的時間內過濾出所需要的數據。
例如在一個上千萬條資料的日誌表上，有一個叫做訂單來源的字段，它記錄訂單是在哪一個平台上進行生成的。在一開始業務不需要的情況下，是不會對資料庫效能造成影響的，但是後面由於業務需求，需要查看這上千萬條資料的具體來自於哪一個平台的訂單量，這一下就產生了很大的問題。
因為由於這些訂單的來源管道只有幾個，區分度很低，所以在上千萬的資料中查詢某一些資料的話，這會消耗大量的磁碟IO，嚴重降低了磁碟的效率。在使用者每一次查看訂單的時候，都會從資料庫查詢一次訂單的來源，這樣會產生大量的慢查詢。

2.5.2 大表對DDL操作的影響

大表對DDL操作的影響，這會為我們帶來什麼風險？

1. 在建立索引上需要很長的時間
   在MySQL的5.5版本之前，資料庫在建立索引的時候會進行鎖定表，而在5.5版本之後，雖然不會鎖定表，但由於MySQL的複製機制是在新的主機上執行，然後才能透過日誌方式傳送給從機，這樣會引起長時間的主從延遲，影響正常的業務。
2. 修改表結構需要長時間鎖定表
   在修改表的結構過程中進行鎖定表，會給我們造成長時間主從延遲的風險。由於我們MySQL的主從複製機制，往往是所有的表結構操作是在主機上先執行完成再透過日誌方式傳給從機進行相同的操作，然後才完成表結構的主從複製。
   假設我們修改一個表格的結構，在主伺服器上修改的時間為480s，那麼我們在從資料庫上的修改時間也為480s。由於現在MySQL的主從複製都是使用單線程，所以一旦有大表修改，在從伺服器上沒有完成相關操作之前，其他的資料修改操作都無法執行，因此這會造成至少480s以上的主從延遲。
   同時會影響資料的正常操作，這會造成所有的插入操作被阻塞，連線數會大額提高並佔滿伺服器，這時就會導致伺服器出現500的連線錯誤。

2.5.3 如何處理資料庫中的大表

1. 分庫分錶，把一張大表分成多個小表
   困難：
   1. 分錶主鍵的選擇
   2. 分錶後跨分區資料的查詢和統計
2. 大表的歷史資料歸檔
   作用：減少對前後端業務的影響
   困難：

##2.6 大事務

1. 2.6.1 什麼是交易
2. 
   
##交易是資料庫系統區別於其它一切檔案系統的重要特性之一。

交易是一組具有原子性的SQL語句，或是一個獨立的工作單元。 因此交易需要符合以下4個特性：原子性，一致性，隔離性，持久性。

#########2.6.2 事務的原子性（ATOMICITY）######

定義：一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要么全部提交成功，要么全部失敗，對於一個事務來說，不可能只執行其中的一部分操作。
範例：
A要轉給B1000元，在A帳戶中取出1000元時，資料庫上A的餘額減去1000，但是在加到B餘額上的時候，伺服器出現了故障，那A的1000元需要回退到A的帳戶中，保持事務原子性，要么一起成功，要么一起失敗。

2.6.3 交易的一致性（CONSISTENCY）

#定義：一致性是指交易將資料庫從一個一致性狀態轉換到另一個一致性狀態，在事務開始之前和事務結束後資料庫中資料的完整性沒有被破壞。
例：
銀行中A的1000塊轉給了B，A的餘額為0，B的帳戶餘額從0變為1000，但是從頭到尾，A B = 1000（A的餘額） 0（ B的餘額） = 0（A的餘額） 1000（B的餘額），也就是說，A和B的總餘額數是不變的，從頭到尾還是1000元。

2.6.4 交易的隔離性（ISOLATION）

#定義：隔離性要求一個交易對資料庫中資料的修改，在未提交完成其他交易時不可見的。
範例：
銀行中A從餘額1000元中取款500元，在取款事務還沒提交前，執行了一個查詢A帳戶餘額的事務，那查詢出來的結果還是餘額1000元，因為在在取款事務還沒提交之前，其他業務對它的事務過程是不可見的。

• SQL標準中定義的四個隔離等級

  • # 未提交讀取（READ UNCOMMITED）

    • 未提交的交易對外可見，就是我們常說的髒讀，查詢到的資料稱之為髒資料。

  • 已提交讀取（READ COMMITED）

    • 很多的數據中預設的隔離級別，在交易提交之後才能讀出數據，也就是事務對外不可見。

  • 可重複讀取（REPEATABLE READ）

    • 比已提交讀取更高一層的級別，在可重複讀取的隔離級別事務中，一個未提交的事務中查詢表中的數據，在另外一個事務中向這張表插入一條數據並提交，但當回到剛剛未提交的事務中再查詢一次表的數據和上一次查詢到的結果是相同的，並沒有查詢到剛剛插入的那一條資料。
    • 但在已提交讀取的隔離等級中是可以查到剛剛的那條資料的
    • 查看目前資料庫的隔離等級語句：
      show variables like '% iso%'
    • 修改目前資料庫隔離等級語句：
      set session tx_isolation='read-committed'

  • 可串行化（SERIALIZABLE）

    • 最高的隔離等級。簡單來說就是會在讀取的每一條資料上都加鎖，所以可能會導致大量的鎖逾時和鎖佔用的問題，因此在實際業務中我們很少使用這個隔離等級。除非是嚴格要求資料一致性，並且可以接受在沒有並發的情況下，我們才會考慮使用這個隔離等級。

  • 隔離性：

    • 未提交讀取

  • 並發性：

    • 未提交讀取> 已提交讀取>可重複讀取> 可串行化

2.6.5 交易的持久性（DURABILITY）

定義：一旦交易提交，則其所做的修改就會永久儲存到資料庫中。此時即使系統崩潰，已經提交的修改資料也不會遺失（不包括磁碟損壞等物理因素）。
範例：
銀行中A用戶存入帳戶1000元，事務提交後，即使銀行系統崩潰，但在恢復回來後，除非A對餘額進行了操作，否則A帳戶中的1000元是不會變的，這就是事務的持久性。

2.6.7 什麼是大事務

講了這麼多，那什麼是大事務？
大事務就是指運行時間比較長，操作的資料比較多的事務。舉例來說，有一個理財產品每天都會統計每個用戶前一天的收入所得，那如果需要統計所有用戶的收入所得並更新到用戶餘額中，這時數以億計的更新就需要數小時，如果中途出現的故障進行的回滾，事務需要進行的時間就更加不可估量，還不包括在更新過程中，會對用戶的餘額進行加鎖，造成所有用戶都無法使用餘額這樣的問題。

• 大交易會造成哪些風險：

1. #鎖定太多的數據，造成大量的阻塞和鎖定逾時
2. 回滾時所需的時間比較長
3. 執行時間長，容易造成主從延遲

• #如何避免大交易？

1. 避免一次處理太多的資料。
2. 移出不必要的交易中的SELECT操作。

能做到以上的兩點基本上可以避免大事務的產生。

更多相關免費學習推薦：mysql教學#(影片)

#

以上是掌握MYSQL進階的詳細內容。更多資訊請關注PHP中文網其他相關文章！