MySQL為什么要避免大事務以及大事務解決的方法

更新時間：2020年08月28日 10:26:24 作者：lyb-geek

這篇文章主要介紹了MySQL為什么要避免大事務以及大事務解決的方法，幫助大家更好的理解和學習MySQL，感興趣的朋友可以了解下

什么是大事務

運行時間比較長，長時間未提交的事務就可以稱為大事務

大事務產生的原因

操作的數據比較多
大量的鎖競爭
事務中有其他非DB的耗時操作
。。。

大事務造成的影響

并發(fā)情況下，數據庫連接池容易被撐爆
鎖定太多的數據，造成大量的阻塞和鎖超時
執(zhí)行時間長，容易造成主從延遲
回滾所需要的時間比較長
undo log膨脹
。。。

如何查詢大事務

**注**：本文的sql的操作都是基于mysql5.7版本

以查詢執(zhí)行時間超過10秒的事務為例：

select \* from information\_schema.innodb\_trx where TIME\_TO\_SEC(timediff(now(),trx\_started))>10

如何避免大事務

通用解法

在一個事務里面，避免一次處理太多數據
在一個事務里面，盡量避免不必要的查詢
在一個事務里面，避免耗時太多的操作，造成事務超時。一些非DB的操作，比如rpc調用，消息隊列的操作盡量放到事務之外操作

基于mysql5.7的解法

在InnoDB事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。**如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放**
通過SETMAX_EXECUTION_TIME命令，來控制每個語句查詢的最長時間，避免單個語句意外查詢太長時間
監(jiān)控 information_schema.Innodb_trx表，設置長事務閾值，超過就報警/或者kill
在業(yè)務功能測試階段要求輸出所有的general_log，分析日志行為提前發(fā)現(xiàn)問題
設置innodb_undo_tablespaces值，將undo log分離到獨立的表空間。如果真的出現(xiàn)大事務導致回滾段過大，這樣設置后清理起來更方便

附錄查詢事務相關語句

**注**：sql語句都是基于mysql5.7版本

# 查詢所有正在運行的事務及運行時間

select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t



# 查詢事務詳細信息及執(zhí)行的SQL

select now(),(UNIX\_TIMESTAMP(now()) - UNIX\_TIMESTAMP(a.trx\_started)) diff\_sec,b.id,b.user,b.host,b.db,d.SQL\_TEXT from information\_schema.innodb\_trx a inner join information\_schema.PROCESSLIST b

on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = 'Sleep'

inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID

inner join performance\_schema.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;



# 查詢事務執(zhí)行過的所有歷史SQL記錄

SELECT

 ps.id 'PROCESS ID',

 ps.USER,

 ps.HOST,

 esh.EVENT\_ID,

 trx.trx\_started,

 esh.event\_name 'EVENT NAME',

 esh.sql\_text 'SQL',

 ps.time 

FROM

 PERFORMANCE\_SCHEMA.events\_statements\_history esh

 JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id

 JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id

 LEFT JOIN information\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id 

WHERE

 trx.trx\_id IS NOT NULL 

 AND ps.USER != 'SYSTEM\_USER' 

ORDER BY

 esh.EVENT\_ID;

 

 # 簡單查詢事務鎖

 select \* from sys.innodb\_lock\_waits

 

 # 查詢事務鎖詳細信息

 SELECT

 tmp.\*,

 c.SQL\_Text blocking\_sql\_text,

 p.HOST blocking\_host 

FROM

 (

 SELECT

 r.trx\_state wating\_trx\_state,

 r.trx\_id waiting\_trx\_id,

 r.trx\_mysql\_thread\_Id waiting\_thread,

 r.trx\_query waiting\_query,

 b.trx\_state blocking\_trx\_state,

 b.trx\_id blocking\_trx\_id,

 b.trx\_mysql\_thread\_id blocking\_thread,

 b.trx\_query blocking\_query 

 FROM

 information\_schema.innodb\_lock\_waits w

 INNER JOIN information\_schema.innodb\_trx b ON b.trx\_id = w.blocking\_trx\_id

 INNER JOIN information\_schema.innodb\_trx r ON r.trx\_id = w.requesting\_trx\_id 

 ) tmp,

 information\_schema.PROCESSLIST p,

 PERFORMANCE\_SCHEMA.events\_statements\_current c,

 PERFORMANCE\_SCHEMA.threads t 

WHERE

 tmp.blocking\_thread = p.id 

 AND t.thread\_id = c.THREAD\_ID 

 AND t.PROCESSLIST\_ID = p.id

以上就是MySQL避免大事務以及大事務解決的方法的詳細內容，更多關于MySQL 大事務的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: