前言

MySQL數據庫的主從延遲問題在生產上還是很經常出現的，從MySQL5.7以后開始出現基于write set的并行復制，主從延遲的問題得到的了大大的緩解，但是由于開發在使用MySQL的過程中，并不了解主從復制的原理，也不會遵循MySQL一些最佳實踐，導致主從延遲的問題依然大量存在。本文是生產上處理的一次主從延遲問題。文末總結給出了解決主從延遲的幾個關鍵點。

分析思路

對于主從復制延遲的問題，我的分析思路是這樣的：

1. 通過show slave status確認，延遲binlog的點，主要查看 Relay_Log_File、Relay_Log_Pos，重點關注Relay_Log_Pos是否變化。
2. 如果Relay_Log_Pos一直不變，說明可能是遇到了大事務?？梢越馕鱿嚓P的binlog、relaylog看看對哪個表在操作。
3. 從binlog/relaylog中找到表名，可以確定這個操作是delete還是update還是insert。
4. 再去相應的主庫的慢日志中，根據對應的時間點，找是否有相關慢查詢。此時可以確定該SQL的具體內容，看看是否需要優化SQL。
5. 如果第2步查到的Relay_Log_Pos一直在變，說明備庫一直在回放事務，那么可以看看操作系統的io情況是不是給力，是否卡其了并行。

問題分析

1、登錄備庫查看數據庫的復制情況，主從延遲已經達到了2269592s，且Relay_Log_Pos保持不變，應該是遇到大事務了。

                Connect_Retry: 60
              Master_Log_File: mysql-bin.002211
          Read_Master_Log_Pos: 51704210
               Relay_Log_File: mysqld-relay-bin.006395
                Relay_Log_Pos: 13999474
        Relay_Master_Log_File: mysql-bin.002134                
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
	      Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 2269592    
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 

2、利用mysqlbinlog解析當前的relaylog找到當前操作的表，確認為對表req_log的delete操作，時間為210422 2:18:07s發起的事務。

# at 13999474
#210422  2:18:07 server id 2  end_log_pos 13999395 CRC32 0x755796e1     Query   thread_id=107738405     exec_time=0     error_code=0
SET TIMESTAMP=1619029087/*!*/;
SET @@session.pseudo_thread_id=107738405/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 13999559
#210422  2:18:07 server id 2  end_log_pos 13999481 CRC32 0x651ea915     Table_map: `abc`.`req_log` mapped to number 286931
# at 13999645
#210422  2:18:07 server id 2  end_log_pos 14007611 CRC32 0xcd1abf13     Delete_rows: table id 286931
# at 14007775
#210422  2:18:07 server id 2  end_log_pos 14015737 CRC32 0x408739cf     Delete_rows: table id 286931
# at 14015901
#210422  2:18:07 server id 2  end_log_pos 14023927 CRC32 0x855917e7     Delete_rows: table id 286931
# at 14024091
#210422  2:18:07 server id 2  end_log_pos 14032103 CRC32 0x9adfe391     Delete_rows: table id 286931

3、根據表名和對應事務的時間點去主庫慢日志，看能否匹配到相關的SQL。找到了一個delete語句。在主庫執行了22s。

# Time: 210422  2:18:30
# User@Host: abc[abc] @  [21.123.119.129]  Id: 107738405
# Query_time: 22.972652  Lock_time: 0.000143 Rows_sent: 0  Rows_examined: 6241045 Logical_reads: 0 Physical_reads: 0
SET timestamp=1619029110;
DELETE from req_log
where date(sys_date_time)<=date_sub(curdate(),interval 3 day);

4、看下該delete語句的執行計劃是全表掃描。

explain select * from req_log
where date(sys_date_time)<=date_sub(curdate(),interval 3 day);

+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | req_log | ALL  | NULL          | NULL | NULL    | NULL | 4231103 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+

5、看下表的數據量，從統計信息上看，大概有4百萬。

mysql> select table_name,table_rows,data_length/1024/1024/1024 from tables where table_name in ('req_log');
+------------+------------+----------------------------+
| table_name | table_rows | data_length/1024/1024/1024 |
+------------+------------+----------------------------+
| req_log    |    4231227 |             0.876449584961 |
+------------+------------+----------------------------+
``
6、看下這個表是否有索引。沒有任何索引。
```language
mysql> show create table req_log\G
*************************** 1. row ***************************
       Table: req_log
Create Table: CREATE TABLE `req_log` (
  `id` varchar(40) DEFAULT NULL,
  `app_id` varchar(8) DEFAULT NULL,
  `res_json_param` text COMMENT '響應的json參數',
  `req_type` varchar(10) DEFAULT NULL COMMENT '請求類型',
  `req_json_param` text COMMENT '請求的json參數',
  `sys_date_time` datetime DEFAULT NULL COMMENT '系統時間',
  `attachments_path` varchar(100) DEFAULT NULL COMMENT '附件路徑',
  `result_type` varchar(10) DEFAULT NULL COMMENT '結果類型',
  `api_name` varchar(255) DEFAULT NULL COMMENT '調用接口名稱',
  `ask_resp_time` varchar(255) DEFAULT NULL,
  `tts_resp_time` varchar(255) DEFAULT NULL,
  `rtp_resp_time` varchar(255) DEFAULT NULL,
  `srp_resp_time` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (MONTH(sys_date_time))
(PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB) */
1 row in set (0.00 sec)

7、經過以上分析，發下出現問題的是一個delete語句，而且被清理的表上沒有索引，也沒有主鍵。那為什么我們要關注索引和主鍵呢，這里講一下，mysql的binlog在row模式下的記錄的格式，對于一個delete或者update語句：delete from big_table where id<10;binlog里實際上記錄的是如下的形式，也就是說如果主庫清理掉10條記錄，日志就會記錄10條delete，而且這個delete后面的where條件帶上了所有的字段值。如果表沒有索引或者主鍵，在主庫執行一次的全表掃描，備庫就要執行10次的全表掃描。這對于批量的清理或者更新就很恐怖了，在主庫執行1w次的清理，你可能感覺不太慢，那么備庫在重放的時候可能麻煩了。

### DELETE FROM `test`.`big_table`
### WHERE
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3='d3d9446802a44259755d38e6d163e820' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @4=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @6=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @7=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @8=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @9=NULL /* INT meta=0 nullable=1 is_null=1 */
### DELETE FROM `test`.`big_table`
### WHERE
###   @1=11 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3='6512bd43d9caa6e02c990b0a82652dca' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @4=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @6=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @7=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @8=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @9=NULL /* INT meta=0 nullable=1 is_null=1 */
### DELETE FROM `test`.`big_table`
### WHERE
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2=6 /* INT meta=0 nullable=1 is_null=0 */
###   @3='c51ce410c124a10e0db5e4b97fc2af39' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @4=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @6=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @7=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @8=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @9=NULL /* INT meta=0 nullable=1 is_null=1 */
### DELETE FROM `test`.`big_table`
### WHERE
###   @1=14 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3='aab3238922bcc25a6f606eb525ffdc56' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @4=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @6=NULL /* VARSTRING(40) meta=40 nullable=1 is_null=1 */
###   @7=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @8=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @9=NULL /* INT meta=0 nullable=1 is_null=1 */

8、基于以上的分析，對于這種情況，我們建議表上都加上主鍵，沒有業務字段可以做主鍵，可以添加自增列。再不濟就加個索引，也比啥也沒有強。我們的這個問題由于延遲實在是太久了，即使現在加上了主鍵或者索引，短期內也無法同步到備庫。我們建議先在表上加索引或主鍵，然后再重做備機，這樣備庫可以馬上就用上索引。

總結

1、對于MySQL種使用Innodb的表來說，一定要加上主鍵，這也是很多大公司的硬性要求。
2、對于批量操作，建議分批提交，減少事務量。
3、無法添加主鍵的，建議添加選擇性好的唯一索引或者普通索引。
4、如果以上均暫時無法滿足，可以嘗試調整參數slave_rows_search_algorithms，這個參數指定了備庫在掃描表的是時候的算法，默認是TABLE_SCAN,INDEX_SCAN，可以調整成HASH_SCAN,TABLE_SCAN。
5、如果對于大量的insert操作導致的備庫延遲，可以嘗試臨時調整innodb_flush_log_at_trx_commit和sync_binlog設置成非1，提高io的利用率。
6、以上操作均在生產環境做過實施，安全性還是可以保證的。
7、文章http://www.sunline.cc/db/47411?utm_source=index_ai分析了一些源碼級別的解釋，大家可以看看。