共計 4590 個字符，預計需要花費 12 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章主要介紹 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，那么它會帶來一下幾點風險：
磁盤 IO 性能突然下降
這往往會發生在熱數據大于服務器可用內存的情況下。通常這種情況我們只能使用更快的磁盤設備來解決。
其他大量消耗磁盤性能的計劃任務
這一點我們上面也提到了，最好避免在大促之前在主數據庫上進行備份，或在從服務器上進行，調整計劃任務，做好磁盤維護。
2.4 網卡流量
顯而易見，網卡流量過大造成網卡 IO 被占滿的風險。
一般的網卡是千兆網卡（1000Mb/8 ≈ 100MB）
如果連接數超過了網卡最大容量的時候，就會出現的服務無法連接的情況，那么我們應該如何避免無法連接數據庫的情況：
減少從服務器的數量
因為每個從服務器都要從主服務器上面復制日志，因此從服務器越多，網卡流量就越大。
進行分級緩存
一定要避免前端緩存突然失效而導致的后端訪問量突然變大的情況。
避免使用 select * 進行查詢
這是一種最基本的數據庫優化的方法，查詢出沒有必要的字段也會消耗大量的網絡流量。
分離業務網絡和服務器網絡
這樣可以避免主從同步或網絡備份影響網絡的性能。
2.5 大表
什么樣的表可以稱之為大表？其實都是相對而言，對于不同存儲引擎會有不同的限制。像 nosql 的數據存儲，并沒有限制表的行數，理論上只要磁盤的容量允許，都可以進行存儲。但當一張表的行數超過千萬行的時候，就會對數據庫的性能產生很大的影響。那么我們可以總結大表的特點：
記錄行數巨大，單表超過千萬行
表數據文件巨大，表數據文件超過 10G
但就算符合了以上的特點，它也可能對我們數據庫性能不會產生很大的影響，因此這個說法是相對的，比如像一般數據庫的日志表，即使記錄行數很大，文件大小很大，但我們一般只對它進行增加和查詢，不涉及大量的 i 修改和刪除操作，因此不會對數據庫性能產生很大的影響。
但當有一天因為業務發生變更，需要對這張 10 個 G 的日志表進行列增加的時候，那么這個工程量無疑是巨大的。
2.5.1 大表對查詢的影響
大表往往代表著慢查詢的產生，慢查詢即是指很難在一定的時間內過濾出所需要的數據。
例如在一個上千萬條數據的日志表上，有一個叫做訂單來源的字段，它記錄著訂單是在哪一個平臺上進行生成的。在一開始業務不需要的情況下，是不會對數據庫性能造成影響的，但是后面由于業務需求，需要查看這上千萬條數據的具體來自于哪一個平臺的訂單量，這一下就產生了很大的問題。
因為由于這些訂單的來源渠道只有幾個，區分度很低，所以在上千萬的數據中查詢某一些數據的話，這會消耗大量的磁盤 IO，嚴重降低了磁盤的效率。在用戶每一次查看訂單的時候，都會從數據庫查詢一次訂單的來源，這樣會產生大量的慢查詢。
2.5.2 大表對 DDL 操作的影響
大表對 DDL 操作的影響，這會給我們帶來什么風險？
在建立索引上需要很長的時間
在 MySQL 的 5.5 版本之前，數據庫在建立索引的時候會進行鎖表，而在 5.5 版本之后，雖然不會鎖表，但是由于 MySQL 的復制機制是在新的主機上執行，然后才能通過日志方式發送給從機，這樣會引起長時間的主從延遲，影響正常的業務。
修改表結構需要長時間鎖表
在修改表的結構過程中進行鎖表，會給我們造成長時間主從延遲的風險。由于我們 MySQL 的主從復制機制，往往是所有的表結構操作是在主機上先執行完成再通過日志方式傳給從機進行相同的操作，然后才完成表結構的主從復制。
假設我們修改一個表的結構，在主服務器上修改的時間為 480s，那么我們在從數據庫上的修改時間也為 480s。由于現在 MySQL 的主從復制都是使用單線程，所以一旦有大表修改，在從服務器上沒有完成相關操作之前，其他的數據修改操作都無法執行，因此這會造成至少 480s 以上的主從延遲。
同時會影響數據的正常操作，這會造成所有的插入操作被阻塞，連接數會大額提高并占滿服務器，這時就會導致服務器出現 500 的連接錯誤。
2.5.3 如何處理數據庫中的大表
分庫分表，把一張大表分成多個小表
難點：
分表主鍵的選擇
分表后跨分區數據的查詢和統計
大表的歷史數據歸檔
作用：減少對前后端業務的影響
難點：
歸檔時間點的選擇
如何進行歸檔操作
2.6 大事務
2.6.1 什么是事務
事務是數據庫系統區別于其它一切文件系統的重要特性之一。
事務是一組具有原子性的 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 什么是大事務
講了這么多，那什么是大事務？
大事務就是指運行時間比較長，操作的數據比較多的事務。舉例來說，有一個理財產品每天都會統計每個用戶前一天的收入所得，那如果需要統計所有用戶的收入所得并更新到用戶余額中，這時數以億計的更新就需要數小時，如果中途出現的出現故障進行的回滾，事務需要進行的時間就更加不可估量，還不包括在更新過程中，會對用戶的余額進行加鎖，造成所有用戶都無法使用余額這樣的問題。
大事務會造成哪些風險：
鎖定太多的數據，造成大量的阻塞和鎖超時
回滾時所需的時間比較長
執行時間長，容易造成主從延遲
如何避免大事務？
避免一次處理太多的數據。
移出不必要的事務中的 SELECT 操作。
能做到以上的兩點基本可以避免大事務的產生。
以上是“MYSQL 進階知識點有哪些”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！
向 AI 問一下細節
