MySQL5.5复制变慢问题

小K大学毕业两年了，工作中踩过许多的坑，自己只是记录了下来，也没有做完整的总结，后续把小K和小K所在团队工作过程中踩过的坑和一点经验总结出来。

今天讲一下MySQL5.5复制变慢问题，这个坑在本周连续踩了两次，作为一个DBA，这是一个多么不可原谅的错误。

先分别描述一下这两次踩坑的情景：

共同环境：
1、MySQL5.5
2、row格式的binlog

第一次：在主机用delete删除了一个表三十多万行的数据，备机延时越来越大；

#删除Fbank_type以1开头的delete语句
delete from t_charge_info where Fbank_type like '1%';

#表结构
create table t_charge_info (
Flistid varchar(32) NOT NULL DEFAULT '',
Fnum bigint(20) DEFAULT NULL,
Fbank_type smallint(6) DEFAULT NULL,
KEY banktype (Fbank_type)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;</span>

第二次：重做订单数据库后，重做的备机追复制巨慢无比，延时越来越大。

#订单表结构
#单表记录一亿左右
create table t_user_order_9 (
Flistid varchar(32) NOT NULL DEFAULT '',
...
...
Fstandby5 datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</span>

上面就是这两次踩坑的情景，下面我们尝试来分析解决下这个问题，以第一次为例：
1、用show slave status查看，执行的点基本不动，
2、show innodb status\G查看innodb的信息

......
---TRANSACTION 322, ACTIVE 2725 sec
mysql tables in use 1, locked 1
492 lock struct(s), heap size 80312, 221350 row lock(s), undo log entries 110675
......
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 25355, id 1329416512, state: waiting for server activity
Number of rows inserted 2000000, updated 0, deleted 110675, read 1110675
0.00 inserts/s, 0.00 updates/s, 21.85 deletes/s, 21.85 reads/s</span>

从上面的信息可以看出如下现象：
系统锁住了大量的行(221350 row lock(s))，但是更新的行数非常少(21.85 deletes/s)；


结合这两点信息，我们猜测：
delete没有用到索引或用了一个过滤性很差的索引


在排查过程中，为了验证上面的猜测，我们还用到了perf查看系统函数的使用情况，发现row_sel_store_mysql_rec函数被调用的次数最多，然后找开发用gdb跟踪此函数，最终跟踪到是find_row函数应用的最多
find_row函数作用：根据relay log的信息来定位数据
那么mysql备机是如何通过relay log来定位数据的，通过我们组大牛同事分析源码和做实验得出结论如下：
1、首先通过主键或唯一键来定位数据；
2、如果没有主键和唯一键，则使用创建时间最早的索引来定位数据；
3、如果表没有索引，则每次delete或update都要做一次全表扫描。
表t_charge_info 只有Fbank_type列有索引，但是这个索引过滤性非常差，所以执行了一个delete语句后，备机在应用relay log的时候，每delete一条数据，就要扫描大量的数据（锁住大量行，但是只删除少量行），relay log满了，show slave status看到的复制点基本就不动，延时就越来越大


知道问题出在哪里，就可以解决这个问题了：
Flistid过滤性非常好，但不作为主键或唯一键，但是时间又要最早，那么可以先删除Fbank_type列索引然后再在Flistid列创建索引，再在Fbank_type列创建索引

#停备机复制
stop slave;
#调整索引
alter table t_charge_info  drop index banktype,add index (Flistid),add index (Fbank_type);
#启动复制
start slave;</span>

总结：
1、这两次都是因为索引不合理导致备机复制变慢，所以在设计表的时候要尽量遵循如下原则：
①、表必须有主键
②、如果业务不需要主键，也必须存在一个选择性高的索引（创建时间必须最早）

2、DBA解决问题有时候需要具备开发知识和代码调试，所以懂代码的DBA才是好项目经理啊