內容提綱
一、簡介
二、Update 生命週期
2。1 聯結器
2。2 分析器
2。3 最佳化器
2。4 執行器
2。4。1 事務執行階段
2。4。2 事務提交階段
2。4。3 假設事務 ROLLBACK
三、影響事務提交延遲的幾種情況
四、Update 更新慢的排查思路
五、常見問題
一
簡介
在開始排錯Update之前，我們需要知道
Update 在 MySQL 中的生命週期是什麼
，
MySQL 如何執行一個事務的
。理解了如何執行，才知道如何去排查故障。
二
Update 生命週期
Server 層階段
2.1
●
聯結器
客戶端發起一個 TCP 請求後，MySQL Server 端會負責通訊協議處理、執行緒處理、賬號認證、安全檢查。
2.2
●
分析器
MySQL Server 端對一個 SQL 請求進行詞法分析（識別 select、from），然後會對語法 進行分析判斷語法是否正確。
2.3
●
最佳化器
最佳化器會分析 SQL 語句，選擇合適的索引，根據預結果集判斷是否使用全表掃描。
2.4
●
執行器
InnoDB引擎層階段
2.4.1 事務執行階段
1）
請求進入 InnoDB 引擎後，首先判斷該事務涉及到的資料頁是否在 BP 中，不存在則會從磁碟中載入此事務涉及的資料頁到 BP 緩衝池中，並對相應的索引資料頁加鎖
思考
資料是如何從磁碟載入到 BP 中的？
BP 中的新老生代是如何交替及回收？
如何對相應資料加鎖？
解答：
透過 B+Tree 讀取到磁碟的索引頁載入到 BP 緩衝池中。
1、
透過 space id 和 page no 雜湊計算之後把索引頁載入到指定的 buffer pool instance 中。
2、
判斷 Free List 是否有空閒頁可用（innodb_buffer_pool_pages_free、innodb_buffer_pool_wait_free），沒有 則淘汰髒頁或 LRU List 的 old。
3、
將資料頁載入到Free List 中，然後載入到 LRU List 的 old 區的 midpoint（頭部）。
4、
透過二分查詢法，找該頁對應的記錄，試圖給該事物涉及到的行記錄加上排他鎖。
判斷該事物當前記錄的行鎖被其他事物佔用的話，需要進入鎖等待。
進入鎖等待後，同時判斷會不會由於自己的加入導致了死鎖。
檢測到沒有鎖等待和不會造成死鎖後，行記錄加上排他鎖。
2）
將修改前的資料寫入到 Undo 中，修改後將回滾針執行 Undo log 中修改前的行
思考
為什麼要寫Undo 日誌？
Undo 的儲存方式是什麼？
解答：
Undo log 一般是邏輯日誌，記錄每行記錄。有兩個作用：提供回滾和 MVCC。
事務因為某些原因需要回滾時，可以藉助 Undo 日誌進行回滾，保證事務的一致性 在事務的不同隔離級別需要透過Undo log 實現。
當讀取某一行加鎖的資料時，可以透過 Undo log 實現（比 如：RR 級別），事務不結束，Undo log 就不刪除。
Undo log 的儲存方式是用段（segment）記錄在表空間中：
InnoDB 儲存引擎對 Undo 採用段方式管理，rollack segment 稱為回滾段，每個回滾段有 1024 個 。
Undo log segment，5。6 之後可以透過 innodb_undo_logs 自定義多少個回滾段，預設 128 個。
Undo log 預設儲存在共享表空間中，開啟了 innodb_file_per_table 將存在獨立表空間中。
3）
寫 redo log buffer 在 BP 中對資料進行修改操作，並將修改後的值寫入到 redo log buffer 中等待非同步 sync到磁碟
思考
什麼時候寫入 redo log buffer？
commit 後 log buffer 如何落盤到 redo log？
日誌刷盤規則是什麼？
解答：
什麼時候寫入 redo log buffer：
1、
先透過狀態值 Innodb_log_waits 判斷 redo log buffer 是否夠用，不夠用就等待。
2、
在 BP 緩衝池的 LRU List 中 old 區的 midpont 中對改資料頁的行記錄的欄位值做更新操作。
3、
把修改之後的欄位值寫入到redo log buffer 中，並給 LSN 加上當前 redo log 寫入的長度（寫入長度為length 的 redo log，LSN 就會加上 length）。
4、
因為 redo group commit，事務所產生的 redo log buffer 可能會和其他事務一同 flush 並且 sync 到磁碟上。
5、
欄位值在 BP 緩衝池更新成功後，對應的資料頁就是髒頁。
什麼時候落盤到 redo log 中：
1、
每次會將 log buffer 中的日誌寫入到 log file（這裡指 os buffer），然後在呼叫系統的 fsync 操作進行落盤。
在 commit 之後，透過 innodb_flush_log_at_trx_commit 來決定什麼時候將 log buffer 刷盤。
2、
值為 1（預設為 1）：
事務每次提交會寫入 log buffer——>然後寫入 os buffer——->呼叫系統 fsync 刷到 log file on disk。
3、
值為 0：
事務提交是先寫入 log buffer——>每秒寫入 os buffer 並呼叫 fsync 落盤（最多丟失 1s 資料）。
4、值為 2：
每次提交只寫入 os buffer，然後每秒呼叫 fsync（）將 os buffer 的日誌寫入到 log file on disk（最多丟失 1s 資料）。
日誌刷盤規則：
預設情況下事務每次提交會刷盤，是因為 innodb_flush_log_at_trx_commit 的值為 1。
這只是 InnoDB 在有 commit 動作後才會將日誌刷盤，屬於 InnoDB 儲存引擎刷盤規則之一。
日誌刷盤的幾種規則 ：
1、
發出 commit 動作之後。由 innodb_flush_log_at_trx_commit 控制。
2、
每秒刷一次，重新整理頻率由 innodb_flush_log_at_timeout 值決定，預設為 1，刷日誌頻率與 commit 動作無關。
3、
當 log buffer 中使用記憶體超過一半。
4、
當有 checkpoint 時，checkpoint（資料頁刷盤）在一定程度上代表刷盤時日誌所處的 LSN 位置。
Checkpoint 刷盤規則：
InnoDB 中，資料刷盤的規則只有一個：checkpoint，觸發 checkpoint 後，會將 BP 中髒資料和髒日誌頁都重新整理到磁碟。
Checkpoint 分為兩種：
Sharp checkpoint：
在重用 redo log 檔案時（切割日誌），將所有記錄到 redo log 中對應的髒資料重新整理到磁碟。
Fuzzy checkpoint：
一次重新整理一小部分日誌到磁碟，並非所有髒日誌。
1、
master thread checkpoint：
master 執行緒每秒或每 10 秒刷一定比例髒頁到磁碟。
2、
Flush_lru_list checkpoint
：
5。6 之後透過 innodb_page_cleaners 變數指定 page cleaner 執行緒個數。
3、
Async/sync/ flush checkpoint
，同步刷盤還是非同步刷盤。
4、
Dirty page too much checkpoint ：
髒頁怠惰強制觸發檢查點，保證快取空間空閒，由變數innodb_max_dirty_pages_pct 控制。
4）
寫 binlog cache
同時將修改的資訊按照 event 格式記錄到 binlog_cache 中，等待落盤。
如果 binlog cache 不夠用時，會寫入到 binlog 臨時檔案。
思考
事務 binlog event 的寫入流程是什麼？
解答：
一旦有事務提交，binlog cache 和 binlog 臨時檔案都會釋放（已經寫入 binlog file） 同一事務包含多個 DML 會共用同一個 binlog cache 和 binlog 臨時檔案。
1、事務開啟。
2、執行 dml 語句，dml 語句第一次執行時會分配 binlog cache。
3、執行 dml 語句期間生成的 event 不斷寫入 binlog cache。
4、binlog cache 滿了事務還沒執行完，會將 binlog cache 中的資料寫入到 binlog 臨時檔案同時清空 binlog cache，臨時檔案大小大於 max_binlog_cache_size 則報 error 1197。
5、事務提交，整個 binlog cache 和 binlog 臨時檔案資料全部寫入 binlog file，釋放 binlog cache（IO_CACHE） 和 binlog 臨時檔案 binlog 臨時檔案大小為 0，保留檔案描述符。
6、斷開連線，釋放 IO_CACHE。
5）
寫 change buffer
如果這個事務需要在二級索引上做修改，寫入到 change buffer page 中，等待之後，事務需要讀取該二級索引時進行 merge。
思考
什麼時候會用到 change buffer？
為什麼僅適用於普通索引頁？
哪些場景會觸發重新整理 change buffer？
什麼業務不適合/適合開啟 change buffer？
change buffer 相關引數有哪些？
解答：
什麼時候會用到 change buffer。
MySQL 5。5 之前叫 insert buffer，只針對 insert，之後叫 change buffer 對 delete 和 Update 也有效。
在對普通索引資料頁不在 BP 中，對頁進行寫操作，不會將磁碟資料載入到緩衝池中，僅僅記錄緩衝變更（可以理解為只記錄操作變更，不做真實資料操作）。
等待資料被讀取時，將資料 merge 到 BP 中，目的是降低寫操作磁碟 IO，提高效能。
為什麼僅適用於普通索引頁？
唯一索引或主鍵索引每次修改操作時，InnoDB 必須進行唯一性檢查。
即使索引頁不在緩衝池，也會去讀取磁碟頁，一次隨機 IO（透過 B+tree 查詢資料頁），一次順序 IO（寫 redo log）避免不了。
哪些場景會觸發重新整理 change buffer：
1、
資料頁被訪問。
2、
master thread 每隔 10s 會進行操作。
3、
資料庫 BP 不夠用時。
4、
資料庫正常關閉時。
5、
redo log 寫滿時（幾乎不會出現，redo log 被寫滿資料庫處於無法寫入狀態）。
什麼業務不適合/適合開啟 change buffer：
不適合：
1、資料庫都是唯一索引。
2、寫入一個數據後，立刻讀取上述場景，在寫操作之後，本來就要進入 BP 中，此時 change buffer 反而成了累贅。
適合：
1、資料庫大部分是非唯一索引。
2、業務是寫多讀少，或者寫後不是立刻讀。
3、讀寫分離下主庫可以使用。
可以使用 change buffer，減少一次隨機 IO，最佳化定期批次寫磁碟。
change buffer 相關引數有哪些：
show global variables like ‘%innodb_change_buffer%’；
innodb_change_buffer_max_size；
配置寫緩衝的大小，佔整個緩衝池的比例，預設值是 25%，最大值是 50%（寫多讀少才需調大，讀多寫少 25%就夠）。
innodb_change_buffering；
配置那些寫操作啟用寫緩衝，可以設定成 all/none/inserts/deletes 等。
2.4.2 事務提交階段
開啟 binlog 選項之後，執行事務提交會進入二階段提交模式（prepare 階段和 commit 階段。
兩階段涉及兩個引數（sync_binlog和innodb_flush_log_at_trx_commit）。
1）
事務提交分為 prepare 階段與 commit 階段（兩階段提交）
事務的 commit 操作在儲存引擎和 server 層採用內部 XA。
兩階段提交協議保證事務的一致性，主要保證 redo log 和 binlog 的原子性。
2）
Redo log prepare
寫入 redo log 處於 prepare 階段，並且寫入事務的 xid。
將redo log buffer重新整理到redo log磁碟檔案中，用於崩潰恢復。
刷盤的方式由innodb_flush_log_at_trx_commit 決定。
3）
Binlog write&fync: 執行器把 binlog cache 裡的完整事務和 redo log prepare 中的 xid 寫入到 binlog 中
Dump 執行緒會從 binlog cache 裡把 event 主動傳送給 slave 的 I/O 執行緒，同時執行 fsync 刷盤（大事務的話比 較耗時）並清空 binlog cache。
Binlog 刷盤的方式由 sync_binlog 決定。binlog 寫入完成，事務就算成功。
總結：
事務執行過程中，先把日誌寫到 binlog cache，事務提交的時候，再把 binlog cache 寫入到 binlog file 中。
當 sync_binlog 為 1 時，binlog 落盤後才會通知 dump thread 進行主從複製。
4）
Redo log commit commit
提交階段中，該事務產生的 redo log 已經 sync 到磁碟中，在 redo log 裡標記 commit，說明事務提交成功。
5）
事務提交，釋放行記錄持有的排它鎖
6）
Binlog 和 redo log 落盤後觸發重新整理髒頁操作
先把該髒頁複製到 doublewrite buffer 裡，再把 dobulewrite buffer 裡的資料，重新整理到共享表空間（ibdata），然後髒頁重新整理到磁碟中，此時記憶體頁和資料頁一致。
思考
BP 中的髒頁刷盤機制是什麼？
解答：
當 InnoDB 中髒頁比例超過 innodh_max_dirty_pages_pct_lwm 的值時，開始刷盤。
2.4.3 假設事務 ROLLBACK
因為系統異常或顯示回滾，所有資料變更會變成原來的，透過回滾日誌中資料進行恢復。
對於 in-place（原地）更新
，將資料回滾到最老版本；
對於 delete+insert 方式
，標記刪除的記錄清理刪除標記，同時把插入的聚集索引和二級索引記錄也會刪除。
三
影響事務提交延遲的幾種情況
在事務執行階段：
3.1
●
鎖等待
（1）
RR 模式下 insert 鎖等待 gap lock 鎖等待導致；
（2）
Insert 等待 MDL 鎖導致；
（3）
Table lock。
3.2
●
IO 方面
（1）
慢 sql 導致 io 高；
（2）
其他程式佔用比價高；
（3）
BP 命中率比較低；
（4）
併發導致；
（5）
innodb buffer pool 不夠用；
（6）
Update、delete 更新資料行數大（>W）。
3.3
●
Buffer 方面
（1）
redo log buffer 是否夠用透過 Innodb_log_waits 確認。
（2）
Redo log buffer 刷盤方式透過 innodb_flush_log_at_trx_commit。
（3）
Binlog cache 是否夠用，建立臨時檔案、消耗 IO。
（4）
Change buffer 是否夠用。
3.4
●
落盤延遲
（1）sync_binlog 引數。
（2）binlog_group_commit_sync_delay 引數。
（3）innodb_flush_commit 引數。
（4）檢視 innodb_buffer_pool 的命中率，檢視髒頁重新整理頻率效果。
四
Update 更新慢的排查思路
排查思路：
4.1
●
排查例項系統性能情況
（IO、CPU、memory），排除性能干擾
如果 CPU 高、IO 高、wa 大：
先排查慢 SQL，再查當前併發數，一般是大量併發慢 SQL 導致；
如果 CPU 高、IO 中、wa 小：
排查慢 SQL，在檢視當前併發數，一般是單個計算 SQL 導致；
如果 CPU 低、IO 高、wa 低：
排查當前佔用 io 高的執行緒，有可能是 page clean 導致或日誌重新整理頻繁導致。
4.2
●
檢查 MySQL狀態
檢視 mysql porcesslist，檢視當前是否有 wait lock（表鎖，行鎖，meata lock 等）；
檢視 mysql processlist，是否有大量 send data、init、commit、clean up 狀態；
檢視 mysql processlist，計算併發，排查是否有併發壓力；
檢視 innodb buffer pool 命中率，排查 buffer 是否夠用；
檢視 mysql tmp，是否夠用，open tables 是否等於 table_open_cache。
4.3
●
分析 SQL 語句
透過 explain 分析 SQL 的執行情況，是否走索引，是否存在 union；
透過 explain 分析 SQL 的執行情況，是否存在大表驅動小表，多表 join；
檢查 SQL 是否存在產生額外臨時表；
使用 profile 分析單條 SQL 語句。
4.4
●
分析應用程式執行 SQL 慢的時間
觀察是單個 SQL 執行慢，還是所有語句都慢；
慢的 SQL 的時間是否有規律，有助於排查 MysSQL 的相關引數。
4.5
●
抓包及 strace 分析
使用 tcpdump 進行抓包，分析是 MySQL 返回慢，還是網路慢；
使用 strace 分析 MySQL 內部哪裡慢，哪個函式導致的。
五
常見問題
1、
Update 全表更新一個欄位，資料量為 10w，更新特別慢；
2、
Update 引起死鎖問題；
3、
Update 幾百條資料消耗了 10s；
4、
Update 同一個表，有些更新快，有些更新慢。
NEWS
Update的問題還不止於此，透過閱讀本篇文章，相信您對如何發現、排查、解決Update可能引發的問題，有了更進一步的認識。
在本小節中，列舉了部分常見的案例，希望對您學習有所幫助。此外，也推薦您自己動手搭建測試環境，發現新的問題。
Enjoy GreatSQL :)