當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL-性能优化_大表和大事务的常用处理方案

發布時間：2025/3/21 数据库 30 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL-性能优化_大表和大事务的常用处理方案小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

生猛干貨
官方文檔
影響MySQL性能的幾大因素
大表帶來的風險
- 大表的定義
- 大表帶來的風險
- 如何應對大表？
大事務帶來的風險
- 基本特性：ACID
- SQL標準中的4個隔離級別
- Read Committed （不可重復讀） VS Repeatable Read （可重復讀） VS Read UnCommitted(臟讀)
- 總結下不同隔離級別的缺點
- MySQL修改隔離級別的方法
- MySql 的autoCommit設置
- 大事務的定義
- 大事務的潛在風險
- 如何處理大事務
搞定MySQL

生猛干貨

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

官方文檔

https://dev.mysql.com/doc/

如果英文不好的話，可以參考 searchdoc 翻譯的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

影響MySQL性能的幾大因素

通常來說，有以下幾點

慢SQL（重點）、主機的硬件資源（CPU、內存、磁盤I/O等）、網卡流量等等

超高的QPS和 TPS

QPS（Queries Per Second 每秒處理的查詢量）：假設處理一個SQL 需要 10ms , 1s 最多也就處理100個，那么QPS < = 100 ,如果 100ms處理一個呢？那 QPS <=10 ,可以推斷出SQL的執行效率隊QPS的影響很重要。一般來說，80%的數據庫問題都可以通過SQL優化來解決。

TPS(Transactions Per Second,事務數/秒，這個完整的事務包括了用戶請求服務器，服務器內部處理，服務器返回信息給用戶三個過程)

QPS和TPS高，說明應用的負載較高.

MySQL數據庫中的QPS和TPS的計算方法
Questions = SHOW GLOBAL STATUS LIKE 'Questions'; Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'; QPS=Questions/Uptime-----------------------Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit'; Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback'; Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'; TPS=(Com_commit + Com_rollback)/Uptime

高并發和高CPU使用率

高并發–>數據庫連接池被用光的幾率大增（max_connections默認100），超過的話，就會看到500子類的錯誤了

高CPU使用率—>響應慢，甚至導致宕機

磁盤I/O

磁盤I/O的性能突然下降—>使用更快的磁盤設備

其他大量消耗磁盤性能的計劃任務等 —> 可預期的高峰期，調整計劃任務的執行時間

網卡流量

比如我們常說的千兆網卡，這里的千兆其實是小b , 1Byte = 8 bit . bit 小b Byte 大B

1000Mb / 8 約等于 100MB (我們熟悉的帶寬)

網卡被占滿的風險增加，被占滿的話，肯定訪問不到數據庫了，如何避免呢？

通常來說，

1. 減少slave節點的數量，避免大量的復制，占用帶寬
2. 合理的使用多級緩存，避免大量緩存失效，請求到DB
3. 避免使用 select * 查詢，占用帶寬傳輸
3. 分離業務網絡和服務器網絡等等

大表帶來的風險

大表的定義

啥叫大表？粗略的定義，可以從兩個維度去考慮，僅供參考

記錄超過1千萬

表數據文件巨大，超過10G

大表帶來的風險

對查詢的影響

舉個例子：從超巨數據中，查找區分度不高的數據，將導致大量的磁盤I/O,有可能導致數據庫hang死，從而產生大量的慢查詢，需要特別關注解決。
對DDL的影響

建立索引耗時特別長，風險： MySQL 5.5 以前的版本，建立索引會鎖表。 5.5以后的版本雖然不會引起鎖表，但會引起主從延遲。

修改表結構的話，需要長時間鎖表 ，風險：1. 主從延遲 2. 影響正常的數據操作

如何應對大表？

1. 分庫分表（分表主鍵如何選擇，分表后跨分區的查詢和統計如何解決）慎重！！！
2. 對歷史數據進行歸檔（歸檔時間點的選擇、如何高效的歸檔）

大事務帶來的風險

基本特性：ACID

事務的4個特性： ACID

原子性 atomicity | 一致性 consistency | 隔離性 isolation | 持久性 durability

原子性（atomicity）
一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性。

舉個例子：轉賬 A 轉給B ， A賬戶扣減 2千， B賬戶增加兩千。這兩個必須在一個事務中，有任何一步出現問題，都不會提交，需要回滾，否則的話，A扣了2000成功，B增加2000沒成功，錢就莫名其妙的少了，誰能受得了？

一致性 consistency

一致性是指事務必須使數據庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之后都必須處于一致性狀態。

說實話，這個定義太難懂了。。。。

[1]Transactions are not a law of nature; they were created with a purpose, namely to simplify the programming model for applications accessing a database. By using transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead (we call these safety guarantees).

上述說出了為什么會出現事務：事務的產生,其實是為了當應用程序訪問數據庫的時候,事務能夠簡化我們的編程模型,不需要我們去考慮各種各樣的潛在錯誤和并發問題。因此事務本質上是為了應用層服務的。

ACID里的AID都是數據庫的特征,也就是依賴數據庫的具體實現.而唯獨這個C,實際上它依賴于應用層,也就是依賴于開發者.

一致性是指系統從一個正確的狀態,遷移到另一個正確的狀態.什么叫正確的狀態呢?就是當前的狀態滿足預定的約束就叫做正確的狀態.而事務具備ACID里C的特性是說通過事務的AID來保證我們的一致性.

CASE1: A要向B轉2000元,而A的賬戶中只有1800元,并且我們給定賬戶余額這一列的約束是,不能小于0.那么很明顯這條事務執行會失敗,因為1800-2000=-200,小于我們給定的約束了. -----------------> 這個例子里,支付之前我們數據庫里的數據都是符合約束的,但是如果事務執行成功了,我們的數據庫數據就破壞約束了,因此事務不能成功,這里我們說事務提供了一致性的保證 .

CASE2: A要向B支付2000元,而A的賬戶中只有1800元,我們的賬戶余額列沒有任何約束.但是我們業務上不允許賬戶余額小于0.因此支付完成后我們會檢查A的賬戶余額,發現余額小于0了,于是我們進行了事務的回滾. -------------------> 這個例子里,如果事務執行成功,雖然沒有破壞數據庫的約束,但是破壞了我們應用層的約束.而事務的回滾保證了我們的約束,因此也可以說事務提供了一致性保證. (事實上,是我們應用層利用事務回滾保證了我們的約束不被破壞)

CASE3: A要向B支付2000元,而A的賬戶中只有1800元,我們的賬戶余額列沒有任何約束.然后支付成功了.-------------------->這里,如果按照很多人的理解,事務不是保證一致性么?直觀上賬戶余額為什么能為負呢.但這里事務執行前和執行后,我們的系統沒有任何的約束被破壞.一直都是保持正確的狀態.

所以,綜上.我們可以理解一致性就是:應用系統從一個正確的狀態到另一個正確的狀態.而ACID就是說事務能夠通過AID來保證這個C的過程.C是目的,AID都是手段.

隔離性 isolation

一個事務所做的修改在最終提交以前，對其他事務是不可見的。

還是轉賬的例子，A轉賬給B，A扣減，B增加。假設在A扣減完成，B未增加時，有另外一個事務，統計A賬戶的余額，這個時候看到的應該是A扣減前的金額。

持久性 durability

事務的操作，一旦提交，對于數據庫中數據的改變是永久性的，即使數據庫發生故障也不能丟失已提交事務所完成的改變。

SQL標準中的4個隔離級別

Read UnCommitted 讀取未提交內容

在這個隔離級別，所有事務都可以“看到”未提交事務的執行結果。在這種級別上，可能會產生很多問題，除非用戶真的知道自己在做什么，并有很好的理由選擇這樣做。本隔離級別很少用于實際應用，因為它的性能也不必其他性能好多少，而別的級別還有其他更多的優點。讀取未提交數據，也被稱為“臟讀”

Read Committed 讀取提交內容 （oracle等大部分數據庫的隔離級別）

大多數數據庫系統的默認隔離級別（但是不是MySQL的默認隔離級別），滿足了隔離的早先簡單定義：一個事務開始時，只能“看見”已經提交事務所做的改變，一個事務從開始到提交前，所做的任何數據改變都是不可見的，除非已經提交。

這種隔離級別被稱為“不可重復讀”。這意味著用戶運行同一個語句兩次，看到的結果是不同的。

Repeatable Read 可重復讀（MySQL默認的隔離級別）

MySQL數據庫默認的隔離級別。該級別解決了READ UNCOMMITTED隔離級別導致的問題。它保證同一事務的多個實例在并發讀取事務時，會“看到同樣的”數據行。不過，這會導致另外一個棘手問題“幻讀”。InnoDB和Falcon存儲引擎通過多版本并發控制機制解決了幻讀問題。
Serializable 可串行化

該級別是最高級別的隔離級。它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡而言之，SERIALIZABLE是在每個讀的數據行上加鎖。在這個級別，可能導致大量的超時Timeout和鎖競爭Lock Contention現象，實際應用中很少使用到這個級別，但如果用戶的應用為了數據的穩定性，需要強制減少并發的話，也可以選擇這種隔離級

Read Committed （不可重復讀） VS Repeatable Read （可重復讀） VS Read UnCommitted(臟讀)

我們以MySQL數據庫為例子，對比下這兩種事務隔離級別對查詢數據的影響

打開兩個會話

會話一：

# 連接mysql[root@artisan ~]# mysql -u root -p Enter password:................# 切到artisan數據庫 mysql> use artisan; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> desc t_test; # 查看t_test表結構 +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)mysql> select * from t_test; # 查詢數據 +----+ | id | +----+ | 1 | | 3 | | 5 | | 7 | | 9 | +----+ 5 rows in set (0.00 sec)mysql> show variables like '%iso%' # 查看隔離級別 -> ; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec)mysql> begin; # 開啟事務 Query OK, 0 rows affected (0.00 sec)mysql> select * from t_test where id < 7; +----+ | id | +----+ | 1 | | 3 | | 5 | +----+ 3 rows in set (0.00 sec)mysql>

然后切到會話二 ，插入幾條數據

[root@artisan ~]# mysql -u root -p Enter password: ..... ..... ..... mysql> use artisan; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> select * from t_test; +----+ | id | +----+ | 1 | | 3 | | 5 | | 7 | | 9 | +----+ 5 rows in set (0.00 sec)mysql> begin ; # 開啟事務 Query OK, 0 rows affected (0.00 sec)mysql> insert into t_test values(2); #插入數據 Query OK, 1 row affected (0.00 sec)mysql> commit; #提交事務 Query OK, 0 rows affected (0.00 sec)mysql> select * from t_test; +----+ | id | +----+ | 1 | | 3 | | 5 | | 7 | | 9 | | 2 | +----+ 6 rows in set (0.00 sec)mysql>

重新切回到會話一，重復執行剛才的SQL （此時，會話一這個事務還未提交，還在事務中）

可以看到，在 **REPEATABLE-READ （可重復讀）**這種隔離級別下， 事務一在事務內，每次查詢到的數據都是一樣的，而且也無法讀取到事務二已經提交的數據。

這也就理解了為啥叫 “可重復讀” ：因為它保證同一事務的多個實例在并發讀取事務時，會“看到同樣的”數據行 。

那這種事務級別潛在的問題是啥呢？ --------> 會導致另外一個棘手問題“幻讀”。InnoDB和Falcon存儲引擎通過多版本并發控制機制解決了幻讀問題。

幻讀是事務非獨立執行時發生的一種現象，例如事務T1批量對一個表中某一列列值為1的數據修改為2的變更，但是在這時，事務T2對這張表插入了一條列值為1的數據，并完成提交。此時，如果事務T1查看剛剛完成操作的數據，發現還有一條列值為1的數據沒有進行修改，而這條數據其實是T2剛剛提交插入的，這就是幻讀

我們修改下 MySQL的隔離級別為 read-committed

我們把會話一的事務先提交了，然后修改下隔離級別

mysql> commit ; Query OK, 0 rows affected (0.00 sec)mysql> set session tx_isolation='read-committed'; # 設置隔離級別 Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show variables like '%iso%' -> ; #查看隔離級別 +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | | tx_isolation | READ-COMMITTED | +-----------------------+----------------+ 2 rows in set (0.01 sec)mysql>

然后切到會話二

回切到會話一，重新查詢

所以 READ-COMMITTED 又被稱為不可重復讀，因為對于數據庫中的某個數據，一個事務執行過程中多次查詢返回不同查詢結果，這就是在事務執行過程中，數據被其他事務提交修改了。每次查詢都有可能查詢到其他事務修改過的數據，所以稱為不可重復讀。

不可重復讀 VS 臟讀 VS 可重復讀

不可重復讀同臟讀的區別在于，臟讀是一個事務讀取了另一未完成的事務執行過程中的數據，而不可重復讀是一個事務執行過程中，另一事務提交并修改了當前事務正在讀取的數據。
幻讀和不可重復讀都是讀取了另一條已經提交的事務（這點同臟讀不同），所不同的是不可重復讀查詢的都是同一個數據項，而幻讀針對的是一批數據整體（比如數據的個數）。

總結下不同隔離級別的缺點

隔離行由低到高： Read UnCommitted —> Read Committed —> Repeatable Read -----> Serializable

并發性由高到低： Read UnCommitted —> Read Committed —> Repeatable Read -----> Serializable

MySQL修改隔離級別的方法

全局修改需要修改MySql的全局文件my.cnf (linux操作系統)

#可選參數有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld] transaction-isolation = REPEATABLE-READ

修改當前會話Session的隔離級

mysql> set session tx_isolation = 'REPEATABLE-READ'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show variables like '%iso%'-> ; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec)mysql>

MySql 的autoCommit設置

另外MySql中有autoCommit參數，默認為on，也就是開啟狀態

如果需要關閉autocommit，我們可以使用下面語句設置

mysql> set autocommit=0;

0就是OFF，1就是ON。設置為OFF之后，則用戶執行語句之后，將一直處于一個事務中，直到執行commit或者rollback，才會結束當前事務，重新開始新的事務。

大事務的定義

定義：運行時間比較長，操作數據比較多的事務

大事務的潛在風險

鎖定太多的數據，容易造成阻塞和超時。
回滾時間耗時較長，回滾過程中也容易阻塞
容易造成主從延遲
…

如何處理大事務

避免一次處理太多數據
移除事務中不必要的 select操作

搞定MySQL

總結

以上是生活随笔為你收集整理的MySQL-性能优化_大表和大事务的常用处理方案的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MySQL-主从架构的搭建
下一篇： MySQL-性能优化_影响MySQL性能