在MySQL的運行中，經(jīng)常會遇到一些長事務(wù)。長事務(wù)意味著長時間持有系統(tǒng)資源，這在OLAP系統(tǒng)中很常見，但在OLTP系統(tǒng)中，長事務(wù)意味著爭用、并發(fā)降低，等待。長事務(wù)伴隨的典型現(xiàn)象就是經(jīng)常聽到開發(fā)人員說"xxx表被鎖住了…"

一、長事務(wù)的成因

長事務(wù)表面上來看都是運行時間過長。但其背地里的成因卻可能不同，我認為長事務(wù)的成因可以分為以下3類：

• 表、索引設(shè)計不合理，存在慢SQL
• 事務(wù)設(shè)計不合理，耦合度過高
• 事務(wù)未正常結(jié)束，例如忘記事務(wù)提交或事務(wù)執(zhí)行出錯后沒有后續(xù)處理。

第一類：表、索引設(shè)計不合理，這種就是常見的慢SQL導(dǎo)致事務(wù)執(zhí)行時間過長，有些慢SQL在數(shù)據(jù)量低的時候可能無法發(fā)現(xiàn)，當(dāng)生產(chǎn)數(shù)據(jù)逐漸增多，慢SQL的問題會越來越嚴(yán)重，最終導(dǎo)致長事務(wù)。這類長事務(wù)的解決方式是優(yōu)化SQL（可以通過慢查詢?nèi)罩咀ト÷齋QL）。

第二類：事務(wù)設(shè)計不合理是將大量的邏輯處理塞到一個事務(wù)中，導(dǎo)致事務(wù)過于臃腫。這種問題需要從業(yè)務(wù)層面分析，看是否可以將過大事務(wù)拆分成多個獨立事務(wù)，降低耦合，對于OLTP系統(tǒng)，大部分都應(yīng)該是短小的事務(wù)。

第三類：事務(wù)未正常結(jié)束，這種可能是忘記提交，或者事務(wù)處理中出錯，但用戶沒有后續(xù)處理。當(dāng)事務(wù)某條語句出錯時，其仍然處于活躍狀態(tài)，已成功執(zhí)行語句的鎖會繼續(xù)持有。某些人可能會直接殺死客戶端連接，但對數(shù)據(jù)庫來說，并沒有收到顯式結(jié)束事務(wù)的命令，它保持事務(wù)是活躍狀態(tài)，一直等待用戶的命令，直到互動超時（interactive_timeout 默認28800秒，即會話8小時沒活動，關(guān)閉會話）。

以上三類長事務(wù)中，第一二類屬于性能優(yōu)化問題，事務(wù)通常可以正常結(jié)束。危害最大的是第三類，這種被遺忘的事務(wù)會長時間占用系統(tǒng)資源（默認8小時），是不可接受的。在事務(wù)執(zhí)行出現(xiàn)問題時，需要顯式的rollback或commit來結(jié)束該事務(wù)，如果客戶端已經(jīng)殺死連接，無法控制事務(wù)，那么只能從服務(wù)端殺死該會話。

二、查找長事務(wù)

MySQL已提供了相關(guān)性能視圖幫助我們查詢活躍事務(wù)信息，通過performance_schema.events_transactions_current可以查詢所有當(dāng)前事務(wù)的event，配合其他視圖即可定位長事務(wù)及其會話信息，主要用到的視圖如下：

• performance_schema.events_transactions_current 查詢事務(wù)的線程ID，狀態(tài)，持續(xù)時間等信息
• performance_schema.threads 查詢線程類型，用戶，IP地址等信息（MySQL中一個線程對應(yīng)一個用戶會話）
• sys.processlist 查詢線程當(dāng)前的狀態(tài)，執(zhí)行的SQL等信息

各個視圖的關(guān)鍵字段，即要查詢的關(guān)鍵信息解釋如下：

performance_schema.events_transactions_current

• thread_id, event_id 事務(wù)線程ID，事件ID，這是一個聯(lián)合主鍵，唯一定位一行記錄
• state 事務(wù)的狀態(tài)，有ACTIVE, COMMITTED 或ROLLED BACK三種狀態(tài)，找長事務(wù)需要關(guān)注的是ACTIVE狀態(tài)
• timer_start, timer_end, timer_wait 事務(wù)起始，結(jié)束（未結(jié)束則是當(dāng)前）及持續(xù)時長，單位是皮秒(10的負12次方)，我們要關(guān)注的是timer_wait
• isolation_level 事務(wù)的隔離級別

注：如果是MySQL8.0.16之后的版本，可以直接用format_pico_time()函數(shù)將timer_wait轉(zhuǎn)換成易讀的格式。

performance_schema.threads

• thread_id 線程ID
• type 線程類型，分為BACKGROUND(后臺線程)和FOREGROUND(用戶線程)，我們要關(guān)注的是用戶線程
• processlist_id 用戶會話ID，只有用戶線程才有
• processlist_user 會話用戶名，只有用戶線程才有
• processlist_host 會話主機地址(IP)，只有用戶線程才有
• processlist_db 會話當(dāng)前操作的數(shù)據(jù)庫

sys.processlist

• thd_id 線程ID
• conn_id 會話ID
• user 用戶信息，user@host格式
• db 用戶操作數(shù)據(jù)庫
• command 當(dāng)前會話狀態(tài)
• time 線程處于當(dāng)前狀態(tài)的時長
• current_statement 當(dāng)前執(zhí)行SQL

了解了上面3個視圖提供的信息含義，我們可以很容易的找出當(dāng)前哪些事務(wù)執(zhí)行時間過長，及這些事務(wù)當(dāng)前在做什么：

select
t.thread_id 線程ID,
t.processlist_id 會話ID,
t.processlist_user 用戶,
t.processlist_host 用戶地址,
t.processlist_db 數(shù)據(jù)庫,
p.command 會話狀態(tài),
e.state 事務(wù)狀態(tài),
format_pico_time(e.timer_wait) 事務(wù)持續(xù)時長,
p.current_statement 執(zhí)行SQL
from performance_schema.events_transactions_current e
join performance_schema.threads t on t.thread_id=e.thread_id
left join sys.processlist p on p.thd_id=t.thread_id
where t.type='FOREGROUND'
and e.state='ACTIVE'
order by e.timer_wait desc;

• 這里提前開了2個會話，通過begin手動開啟事務(wù)，一個會話執(zhí)行select sleep(10000)，另一個執(zhí)行了一條普通的insert into語句。
• 第一個會話模擬了大事務(wù)/慢SQL的狀態(tài)，會話的狀態(tài)是Query，且執(zhí)行SQL有內(nèi)容，表示事務(wù)在運行中
• 第二個會話模擬了事務(wù)未正常結(jié)束的狀態(tài)，會話的狀態(tài)是Sleep，執(zhí)行SQL為NULL，表示事務(wù)處于空閑狀態(tài)，這類事務(wù)需要重點關(guān)注
• 第三條記錄是這個查詢本身

定位到長事務(wù)后，分析長事務(wù)屬于哪一類，決定是否需要優(yōu)化事務(wù)或人工介入。例如上面第二個事務(wù)，如果判斷會話異常，可以通過殺死會話ID來結(jié)束該會話（事務(wù)）;

kill 451;

到此這篇關(guān)于MySQL定位長事務(wù)(Identify Long Transactions)的文章就介紹到這了,更多相關(guān)MySQL定位長事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論