MySQL主從延遲解決方法的歸納和總結

MySQL主從延遲情況共分為以下四類
一.MIXED模式無索引或SQL慢
在從庫上
pager grep -v Sleep;show processlist 檢視到正在執行的SQL
解決方法:
1.SQL比較簡單，則檢查否缺少索引，並新增索引。例如update操作where條件無索引。
2.SQL比較複雜，例如insert into select from語句，如果select裡包含group by,多表關聯，可能效率會比較低。這類可以到主庫把binlog_format改為row.

二.主庫上有大事務，導致從庫延時
解決方法：
找開發人員溝通，增加快取，非同步寫入資料庫，減少對DB的大量寫入。

三.主庫寫入頻繁，從庫壓力跟不上主庫導致延時
此類原因的主要現象是資料庫的IUD（增改刪）操作非常多，slave由於sql_thread單執行緒的原因追不上主庫的寫操作。
解決方法：
1.升級主庫的硬體配置,比如ssd、fio
2.設定日誌重新整理頻率:
set global_sync_binlog=0;

•   innodb_flush_log_at_trx_commit=0每秒將log_buffer的內容重新整理到logfile,並且將日誌同步重新整理到磁碟。該模式下，在事務提交的時候，不會主動觸發寫入磁碟的操作。
•   innodb_flush_log_at_trx_commit=1每次commit將log_buffer重新整理到logfile,並且將日誌同步重新整理到磁碟。
•   innodb_flush_log_at_trx_commit=2每次commit將log_buffer重新整理到logfile,但是flush(刷到磁碟)操作並不會同時進行，每秒將日誌重新整理到磁碟。

四.資料庫中存在大量myisam表，在備份的時候導致從庫延遲
由於xtrabackup工具備份到最後會執行flush tables with read lock,對資料庫進行鎖表以便進行一致性備份，然後對於myisam表鎖，會阻礙slave sql_thread執行緒停滯執行而導致hang。
該問題目前比較好的解決方法是修改表結構為innod儲存引擎的表。