MYSQL進階知識點有哪些

這篇文章主要介紹MYSQL進階知識點有哪些，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

文章目錄

• 1 前言

• 
  

• 1.1 數據庫架構

• 1.2 監控信息

• 2 影響數據庫的因素

• 
  

• 2.6.1 什么是事務

• 2.6.2 事務的原子性（ATOMICITY）

• 2.6.3 事務的一致性（CONSISTENCY）

• 2.6.4 事務的隔離性（ISOLATION）

• 2.6.5 事務的持久性（DURABILITY）

• 2.6.7 什么是大事務

• 2.5.1 大表對查詢的影響

• 2.5.2 大表對DDL操作的影響

• 2.5.3 如何處理數據庫中的大表

• 2.1 超高的QPS和TPS

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

• 2.3 磁盤IO

• 2.4 網卡流量

• 2.5 大表

• 
  

• 2.6 大事務

• 
  

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<=100，但當我們100ms才處理一個sql，那么我們1s鐘才能處理10個sql，QPS<=10，這兩種情況是相差很大的，因此盡量優化sql性能。

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. 大表的歷史數據歸檔
   作用：減少對前后端業務的影響
   難點：

1. 歸檔時間點的選擇

2. 如何進行歸檔操作

2.6 大事務

2.6.1 什么是事務

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標準中定義的四種隔離級別

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

• 未提交讀 < 已提交讀 < 可重復讀 < 可串行化

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

• 比已提交讀更高一層的級別，在可重復讀的隔離級別事務中，一個未提交的事務中查詢表中的數據，在另外一個事務中向這張表插入一條數據并提交，但當回到剛剛未提交的事務中再查詢一次表的數據和上一次查詢到的結果是相同的，并沒有查詢到剛剛插入的那一條數據。

• 但在已提交讀的隔離級別中是可以查到剛剛的那條數據的

• 查看當前數據庫的隔離級別語句：
  show variables like '%iso%'

• 修改當前數據庫隔離級別語句：
  set session tx_isolation='read-committed'

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

• 未提交的事務對外可見，就是我們常說的臟讀，查詢到的數據稱之為臟數據。

• 未提交讀（READ UNCOMMITED）

• 已提交讀（READ COMMITED）

• 可重復讀（REPEATABLE READ）

• 可串行化（SERIALIZABLE）

• 隔離性：

• 并發性：

2.6.5 事務的持久性（DURABILITY）

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

2.6.7 什么是大事務

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

• 大事務會造成哪些風險：

1. 鎖定太多的數據，造成大量的阻塞和鎖超時

2. 回滾時所需的時間比較長

3. 執行時間長，容易造成主從延遲

• 如何避免大事務？

1. 避免一次處理太多的數據。

2. 移出不必要的事務中的SELECT操作。

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

以上是“MYSQL進階知識點有哪些”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！