818
MySQL row格式的兩個問題
記：今天講兩個不怎麼少見，但知道的人比較少的事情。
作者簡介：
劉偉 雲和恩墨開源解決方案事業部首席架構師
多年一線互聯網企業DBA經曆，對MySQL、NoSQL，PostgreSQL等各類開源數據庫均有涉獵，負責開發管理過數千實例規模數據庫項目，並帶領團隊開發了MySQL數據庫的監控、備份等自動化組件，對超大規模數據庫運維平台的開發及管理有豐富經驗。
在MySQL的一般場景中，通常我們推薦將複製格式設置為ROW格式，這樣所有變更的數據都會被記錄到binlog，可以對數據達到最好的保護，萬一發生DML誤操作，可以直接從binlog恢複數據。
但row格式的設置，會帶來許多問題，運維也會相對複雜些。接下來我們通過兩個真實的案例說明直接使用row格式複製出現的問題。
1
無索引表導致的延遲問題
row格式的binlog event，在執行的時候，對於每一條數據，是通過類似翻譯為對應SQL語句的方式逐條執行的。對於UPDATE及DELETE語句，如果表上麵沒有索引，或者已有索引區分度太低的話，執行速度會非常緩慢。
這是我們在為客戶排查數據庫主從延遲時發現的問題：客戶的核心數據庫對外提供在線訪問，並且應用係統的展示界麵是直接讀取數據庫從庫，數據庫主從延遲會直接導致頁麵展示錯誤，造成經濟損失。
在檢查到主從同步延遲當時我們使用show slave status命令，（而實際上用心跳表方式更準確），由於業務係統非常重要，多個從庫之間延遲嚴重，為了盡快解決問題，當時使用xtrabackup工具在線備份主庫，並替換掉線上從庫，暫時解決問題。
但延遲問題本身並沒有得到解決。在隨後的係統運行中，延遲仍然會產生，於是進行深入檢查。
因為客戶的binlog_format設置為row，複製進程也隻是延遲，show slave status的relay_master_log_file,exec_master_log_pos兩個變量一直沒有變化，暫時判斷為有操作大量數據的DML語句存在。
為了驗證，我們解析了對應的binlog日誌以及event。但實際結果，對應的event是一個delete event，涉及的數據隻有三千多條，這個數量本身的操作並不應該是導致延遲。
然後我們直接查看對應的表結構，以及表的數據量。
mysql
create table x
( i int,
b varchar(10),
v varchar(10),
x_type varchar(10),
index idx_type (x_type) );
表的數據量也隻有幾十萬條。但是經詢問開發人員得知，這個表每小時會插入幾千條數據，之後再刪除掉某一type的數據，這是表上新添加的規則，結合DBA的說法，這個增刪數據的邏輯的導致問題的根源。
解決辦法有以下幾種
1. 創建主鍵索引。如果目前表內數據本身支持創建唯一或者主鍵索引，可以直接建立索引解決問題。
2. 創建所有列的聯合索引。如果表確實沒有辦法創建主鍵或者唯一索引，則嚐試對所有列合並添加一個聯合索引。
3. 創建部分列的聯合索引。由於MySQL索引長度限製，如果確實沒有辦法創建所有列的索引，找出區分度比較大的幾列創建索引。
4. 為表建立自增主鍵。以上都不能實現的話，為表創建自增id列並設置為主鍵。這種情況有可能會對應用造成影響，需要預先測試。
1. 停止同步。這種情況除了會導致sql延遲，也會導致sql線程持有非常多的行鎖，stop slave語句的執行會比較慢需要等待，也可以直接kill掉線程，然後change master切換同步位點到問題event前的位置。
2. 為表加索引或者主鍵。
3. 啟動同步。
4. 觀察追上同步。
延伸討論 MySQL中有一個參數，slave_rows_search_algorithms 可以控製row格式下，mysql執行event時候，搜索對應行的方式。
很多ORM框架由於對MySQL兼容不足，沒有針對性的主鍵索引建立，在row格式下，會出現延遲。但在statement格式複製的情況下，未必會出現類似的問題。
常規建議：如果要使用row格式複製，需要保證自己數據庫裏麵所有的innodb都必須有主鍵或者唯一鍵，這樣才能避免由於沒有合適索引導致的從庫延遲問題。
另外，如果開發實用select以及insert語句的時候，都是明確指定列的方式使用的話，可以直接使用解決辦法4最簡單。這也是很多SQL規範推薦SQL語句需要明確指定列的原因之一。
2
從庫alter語句導致同步中斷
原因簡述 MySQL row格式複製下，主從庫之間同一個表如果列的類型不匹配，MySQL會嚐試轉碼，如果轉碼失敗（類型不兼容），則複製中斷。
案例分析 問題來源為，客戶主從複製中斷，SQL線程報錯信息為：
Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 'test.t' cannot be converted from type 'varchar' to type 'int'
這個錯誤明確說MySQL轉碼失敗，詢問DBA後，得知之前為了給表添加一列（開發要求列順序必須為指定順序）：
mysql
alter table a add column cl int
DBA試圖采用的方式為：
1. 先為從庫修改。
2. 在主庫設置session的sql_log_off為0，修改從庫。
然而執行完第一步後，就發現同步出現錯誤。直接原因確定為alter語句。經我們詢問得知，出於安全考慮，數據庫為最近才被修改為row格式，這次給表添加列是DBA在row環境下，初次執行這種類型的DDL語句。
新增的列由於被添加到列順序中間，導致主庫上是字符的列的位置，對應到從庫上成了一個數字類型的列，MySQL無法轉碼，隻能報錯。
最方便的辦法，也是最後采用的方法，就是從主庫直接xtrabackup備份，之後恢複作為從庫提供服務。
另外一個辦法是，在從庫上把對應表新增的列去掉，重新啟動同步。之後找合適的辦法進行DDL的執行。
由於row格式的event裏麵，隻會按照主庫的列順序保存數據，並不會保存列名稱，在從庫的執行也是按照列順序對應的，因此，row格式下修改列順序並不是一個安全行為。
DDL的執行，在MySQL 5.7的時候，基本上都做到了在線改表。但主庫在線修改完成之後，從庫上的執行也會導致從庫延遲，雖然不會阻塞從庫的SELECT語句，但延遲本身就很難接受，因此實際情況中，如果無法接受延遲，還是需要考慮pt-osc或者gh-osc工具。
3
總結
MySQL的row格式複製對數據安全的保護，以及主從數據一致的保證是非常重要的，一般來說都建議設置成row格式。在MySQL 5.7之後已經作為複製的默認格式，但帶來的運維方式方麵的變更，以及對運維手段人員的要求也增高不少，如果需要設置，還是需要多加注意的。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 10:33:03