前言
在日常工作中經常會使用一些比較“大”的資料庫查詢和操作，這裡的“大”主要是指
- 執行時間長：含有較多的邏輯處理、存在較耗時操作等
- 操作資料多：需要查詢或更新操作的數量記錄較多，會鎖定大量資料造成阻塞和鎖逾時等。
本文會和大家一起探讨下，為什麼 在資料庫中要避免使用這些大查詢。
事務
大家都清楚事務具備ACID特性（即原子性、一緻性、隔離性、持久性），針對隔離性，在資料庫事務隔離标準中，定義了四種隔離級别：讀未送出、讀送出、可重複讀、串行化。MySQL預設的事務隔離級别是可重複讀，我們以此來展開分析
事務隔離的實作
多版本并發控制（MMVC）
每行記錄後面會有兩個隐藏列，記錄建立版本号及删除版本号。建立本本号記為row trx_id
對于一個事務來說，啟動時（申請完事務id後），MySQL會給此事務建立一個活躍事務（即已啟動但還未送出的事務）id數組。數組中的最小值記為minTid，最大值記為maxTid。
- 如果minTid > row trx_id，則資料是可見的。
- 如果maxTid < row trx_id，則資料是不可見的。
- 如果minTid <= row trx_id <= maxTid，且：
- row trx_id在數組中，則說明啟動時，此事務未送出，資料不可見
- row trx_id不在數組中，則說明啟動是，此事務已送出，資料可見
如：目前事務id為50，活躍id數組為[35, 43, 44, 45, 46, 50, 51, 52]則
- row trx_id小于35的資料為可見
- row trx_id大于52的資料不可見
- 35 <= row trx_id <= 52且在數組中的資料不可見，不在數組中的資料可見。
對于不可見的資料，則需要依次去資料上一個版本查詢，直到查詢到可用版本資料為止。
隻有在新的RW事務建立的時候 才會建立一個視圖 否則繼續使用上次建立的視圖。
復原日志（undo log）
上面提到對于不可見資料需要依次查詢上一版本來擷取到可用資料。
我們知道資料庫的資料更新是非常頻繁的，不可能将每一版本的資料都存下來，那樣資料量會巨大查詢也會非常的緩慢。
MySQL通過undo log來擷取曆史版本的資料。undo log不會記錄每個版本的最終資料，它是一個邏輯日志，是反向将之前的操作取消掉。比如對insert的會進行執行delete，delete的執行insert，對于update的資料會執行一個反向update，将之前修改的内容改回去。
例如：
- S1時刻，事務34啟動，進行insert i = 5 操作後，commit，資料記錄為D1：i = 5，row_id為34；
- S2時刻，事務36啟動；
- S3時刻，事務37啟動，進行update i + 3 操作後，commit，資料記錄為D3：i = 8，row_id為37；
- S4時刻，事務42啟動
- S5時刻，事務54啟動，進行update i * 2 操作後，commit，資料記錄為D5：i = 16，row_id為54
此時，如果事務42需要查詢i的資料，因為目前i = 16，row_id為54，資料不可見，是以需要根據undo log查詢上一版本的資料。update i / 2，得到row_id為37。可見，擷取i = 8
如果事務36需要查詢i的資料，需要update i / 2, 查到row_id = 37,不可見，繼續復原 update i - 3，查到row_id = 34，可見，擷取到i = 5
隻有當復原日志不再需要時，才會删除。系統會判斷，當沒有事務再需要這些復原日志的時候，才會删除。
是以長事務意味着系統裡面會存在很多非常老的事務視圖，因為這些事務可能會通路資料庫中的任何資料，是以在這個事務送出之前，系統不得不保留它之後可能用到的所有復原記錄。這就會占用大量的存儲空間。
事務啟動
autocommit參數控制事務是否自動送出，MySQL預設set autocommit=1，開啟自動送出，即每條select、update都會自動送出。是以我們日常使用的SQL語句其實等價于
begin;
select * from table where xxx;
commit; 但有些用戶端連接配接架構預設會在連接配接成功後執行一條set autocommit = 0，這樣會導緻你隻有執行一條select語句其實就開啟了事務。這樣會意外導緻長事務的出現。
是以還是建議set autocommit = 1配合begin來顯示的啟動事務。
鎖
大事務還會長時間、大量占用鎖資源，阻塞DML、DDL操作、造成鎖逾時影響系統并發能力，并且很容易引發死鎖問題。
連接配接數
大事務會長時間占用資料庫連接配接，并發情況下容易造成連接配接數滿的問題 拖垮整個應用
主備延遲
MySQL主備複制隻會在事務執行完畢後才會進行，即binlog在事務commit後才會生成（兩階段送出）。
大事務執行多久就會造成多長時間的主備延遲，主備延遲的時間越長帶來的風險也就越高
緩存
MySQL的buffer pool對查詢具有緩存效果，對于很多高頻查詢可以直接從緩存傳回不需要查找磁盤檔案。但是當有大量資料需要傳回時通常有很多順序查詢，記錄在同一磁盤頁中就會命中緩存機制 對緩存造成一定影響
MySQL buffer pool的緩存機制是使用的改良LRU算法（主要增加了通路時間控制）
記憶體&CPU
MySQL資料傳回預設是邊取邊發，是以資料較多，傳輸時間較長也也會引發長事務帶來的問題。
還有如果傳回大量資料給用戶端處理，對用戶端的記憶體及CPU也會帶來較大的壓力。
逾時和超出大小限制
容易引起逾時的問題和超出max_binlog_cache_size導緻執行失敗。（還要注意，避免出現為了讓主庫大事務順利進行，臨時調大主庫max_binlog_cache_size，忽略備庫導緻的服務宕掉等嚴重後果）
復原
復原大事務也是非常耗時和占用記憶體的，需要注意
總結
應該盡量避免使用大事務，開發時要注意盡量
- 如果可以，将一個大事務拆分成多個小事務執行
- 将事務中可以提出的select查詢放在事務外執行
更多文章
見我的部落格：
https://nc2era.comwritten by
AloofJr，轉載請注明出處