## ## 需求場景： 由於MySQL沒有類似於SQL SERVER那樣的系統表來存放備份記錄，且大規模的MySQL伺服器需要集中管理和查看。 伺服器出現性能問題或複製延遲時，需要先判斷是否由數據備份引起。 ## ## 第一版 按照需求，考慮到需要記錄的備份信息有備份伺服器信息、備份開始結束時間、備 ...
##================================================================##
需求場景：
由於MySQL沒有類似於SQL SERVER那樣的系統表來存放備份記錄，且大規模的MySQL伺服器需要集中管理和查看。
伺服器出現性能問題或複製延遲時，需要先判斷是否由數據備份引起。
##================================================================##
第一版
按照需求，考慮到需要記錄的備份信息有備份伺服器信息、備份開始結束時間、備份是否成功等消息，於是設計出第一版表：
create table full_backup_log ( id bigint auto_increment primary key, ## 自增主鍵，業務無關 host_ip varchar(50), ## 備份機IP host_port int, ## 備份機埠 backup_type varchar(50), ## 備份類型，mysqldump和xtrabackup start_time datetime, ## 備份開始時間 end_time datetime, ## 備份結束時間 is_success int, ## 備份是否成功 backup_message varchar(5000), ## 備份消息 check_time datetime ##寫入或更新記錄的時間 );
##================================================================##
第二版
將backup_message弄得比較大， 主要是先把備份過程中的一些信息寫進去，但仔細想想，該表不能很好地記錄備份過程中的每一步，將所有信息放入到backup_message列中不利於查看，於是新增一個詳細信息表：
create table full_backup_log_detail ( id bigint auto_increment primary key, ## 自增主鍵，業務無關 full_backup_log_id bigint, ##關聯full_backup_log表主鍵 host_ip varchar(50), ## 備份機IP host_port int, ## 備份機埠 backup_type varchar(50), ## 備份類型，mysqldump和xtrabackup backup_message varchar(5000), ## 備份消息 check_time datetime ##寫入或更新記錄的時間 );
雖然full_backup_log表中存放有備份機和備份類型數據，可以通過full_backup_log_id關聯來獲取到，但是考慮full_backup_log_detail表數據數據日誌性數據，寫入後不會發生變化，因此通過冗餘來減少關聯，僅查詢full_backup_log_detail即可看某台伺服器的備份詳情。
##================================================================##
第三版
通常DBA關心每個資料庫最後一次備份成功時間，而表full_backup_log中存有is_success欄位用來標識備份成功，可以通過以下SQL來獲取：
select t1.* from full_backup_log as t1 inner join ( select host_ip,host_port,max(id) as max_id from full_backup_log where is_success=1 group by host_ip,host_port ) as t2 on t1.id=t2.max_id
如果full_backup_log表數據量較大時，比如存放幾千個實例的幾年數據，表中數據幾百萬上千萬時，上面查詢即使有合適索引也不能高效執行。
由於DBA並不關心早前數據，可以通過數據結轉來實現，但如果偶爾查詢早前數據則需要當前表和歷史表進行UNION，程式實現上還得判斷數據是否結轉，於是新增一表來存放最後一次成功備份記錄：
## full_backup_info用來存放備份機最後一次成功備份的記錄 create table full_backup_info ( id bigint auto_increment primary key, ## 自增主鍵，業務無關 host_ip varchar(50), ## 備份機IP host_port int, ## 備份機埠 backup_type varchar(50), ## 備份類型，mysqldump和xtrabackup start_time datetime, ## 備份開始時間 end_time datetime, ## 備份結束時間 backup_message varchar(5000), ## 備份消息 check_time datetime ##寫入或更新記錄的時間 );
同樣數據容易來減少表關聯，雖然最後一次成功的備份記錄肯定和full_backup_log表中的備份記錄對應，但是因為保存數據已經全部冗餘，就無需在表full_backup_info中增加欄位與表full_backup_log進行關聯
##================================================================##
第四版
當備份進程過度使用CPU和IO資源導致性能問題並報警後，DBA需要第一時間判斷報警伺服器是否處於備份過程中，需要查看那些伺服器正在進行備份：
方法1：通過full_backup_log表的start_time和end_time來獲取當前正在備份的伺服器，需要對end_time來建索引，如果end_time預設為NULL，則WHERE end_time is null or end_time >now, 性能很容易因OR而受影響，可以考慮給end_time設置一個預設值如2199-01-01啥的，將查詢改為 where end_time >now
方法2：將full_backup_log表中is_success列擴展來標識備份狀態，如果1表示成功0表示失敗-1表示正在備份，查詢條件為where is_success=-1，需要為is_success列建索引，但是is_success列選擇性太低，而MySQL又不支持過濾索引，容易生成不高效的執行計劃。
解決辦法：
新建一個表，專門存放正在備份的伺服器記錄，這樣只需要查詢該表便可以獲取到所有正在備份的伺服器列表，備份成功後立即刪除該表記錄。
## full_backup_in_process用來存放正在備份的伺服器信息 create table full_backup_in_process ( id bigint auto_increment primary key, ## 自增主鍵，業務無關 host_ip varchar(50), ## 備份機IP host_port int, ## 備份機埠 backup_type varchar(50), ## 備份類型，mysqldump和xtrabackup start_time datetime, ## 備份開始時間 check_time datetime ##寫入或更新記錄的時間 );
##================================================================##
總結：
部分研發同事在進行設計時，隨著需求變化不停地修改表，通過在原表上新增欄位來解決新需求，導致表欄位過多，同一表處理不同需求，或通過複雜的SQL來實現，逼著DBA去優化SQL或創建一堆的低效索引，且美名其曰“業務需求”。但很多需求其實可以曲線處理，往往優化業務需求和優化實現方式才能最終解決性能問題。
曾經有研發同事讓幫其優化SQL，發現其業務需求是對幾千萬數據進行排序分頁然後取TOP，幾十秒都無法返回結果，建議其去除排序，被告知部分數據需要優先處理，而這部分需要優先處理的數據極少極少，最終解決辦法是將優先處理的數據分拆出來讓單獨的程式進行處理，其他普通數據不排序查詢正常處理，完美解決。
雖然開個拖拉機，可以拉貨，可以耕田，也能代步，家裡沒電還能當個發電機，但是人生不能一個拖拉機就解決了吧！
##================================================================##
依舊是妹子鎮壓帖子！
推女郎艾慄慄，拿走不謝！
