表上无索引导致MySQL备库复制延迟

早上发现一套内网mysql库主备复制延迟很大
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.65
                  Master_User: backup
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: mysql-3301-bin.002394
          Read_Master_Log_Pos: 588241133
               Relay_Log_File: mysql_3302-relay-bin.000382
                Relay_Log_Pos: 575560494
        Relay_Master_Log_File: mysql-3301-bin.002394
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 575560343
              Relay_Log_Space: 588241493
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 56626
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100

查看一下SQL线程目前正在做什么操作
mysql> select * from information_schema.processlist where Command <>'Sleep' and user='system user'\G
*************************** 1. row ***************************
           ID: 51357
         USER: system user
         HOST: 
           DB: crm
      COMMAND: Connect
         TIME: 56742
        STATE: updating
          INFO: delete from actallot where actid=46 and studentid=2939784
      TIME_MS: 44
    ROWS_SENT: 0
ROWS_EXAMINED: 0
    ROWS_READ: 1
*************************** 2. row ***************************
           ID: 51356
         USER: system user
         HOST: 
           DB: NULL
      COMMAND: Connect
         TIME: 3696408
        STATE: Waiting for master to send event
         INFO: NULL
      TIME_MS: 3696407772
    ROWS_SENT: 0
ROWS_EXAMINED: 0
    ROWS_READ: 1
2 rows in set (0.00 sec)

多次查询发现大量 delete from actallot where actid=46 and studentid=2939784语句，而且每一句执行时间也很慢

检查磁盘IO，没什么问题，IO压力很小，怀疑是这个表上没有创建索引导致的
mysql> show variables like 'binlog_format'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show index from actallot;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actallot |          0 | PRIMARY  |            1 | id          | A         |     6195309 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.04 sec)

发现除了主键，确实没有其他索引

解决：
mysql> stop slave;        +++停止备库复制
Query OK, 0 rows affected (1.97 sec)

mysql> create index idx_abc on actallot(actid,studentid);        +++在备库创建一个索引
Query OK, 0 rows affected (1.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> start slave;                +++启动slave复制
Query OK, 0 rows affected (0.00 sec)

过几秒再查看复制情况：
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.65
                  Master_User: backup
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: mysql-3301-bin.002394
          Read_Master_Log_Pos: 588267746
               Relay_Log_File: mysql_3302-relay-bin.000383
                Relay_Log_Pos: 7764
        Relay_Master_Log_File: mysql-3301-bin.002394
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 588267746
              Relay_Log_Space: 588268209
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
1 row in set (0.00 sec)

观察到复制已经恢复正常。

mysql> drop index idx_abc on actallot;        +++备库上删除该索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

结合实际情况，在主库上创建更合适的索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1411719/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1411719/