mysql主键的缺少导致备库hang住

最近线上频繁的出现slave延时的情况，经排查发现为用户在删除数据的时候，由于表主键的主键的缺少，同时删除条件没有索引，或或者删除的条件过滤性极差，导致slave出现hang住，严重的影响了生产环境的稳定性，也希望通过这篇博客，来加深主键在innodb引擎中的重要性，希望用户在使用rds，设计自己的表的时候，一定要为表加上主键，主键可以认为是innodb存储引擎的生命，下面我们就来分析一下这个案例（本案例的生产环境的binlog为row模式，对于myisam存储引擎也有同样的问题）：
(1).现象slave:

mysql> show slave status\g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: xxx.xx.xx.xx
master_user: replicator
master_port: 3006
connect_retry: 60
master_log_file: mysql-bin.000006
read_master_log_pos: 47465657
relay_log_file: slave-relay.100383
relay_log_pos: 251
relay_master_log_file: mysql-bin.000006
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: 18057461
relay_log_space: 29409335
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: 1339
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:

slave的seconds_behind_master一直在增加，slave出现hang住。
(2).解析当前slave执行到的位置的binlog：

mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000006 –start-position=18057461 >/tmp/2.log
### update qianyi.dmpush_message_temp
### where
### @1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* varstring(108) meta=108 nullable=1 is_null=0 */
### @2=133 /* longint meta=0 nullable=1 is_null=0 */
### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
### @4='0′ /* varstring(24) meta=24 nullable=1 is_null=0 */

(3)分析：
模拟场景：
1.表中无主键，全表进行更新：
master:
表结构：
create table `dmpush_message_temp` (
`clientid` varchar(36) default null,
`infoid` bigint(10) default null,
`endtime` varchar(14) default null,
`stat` varchar(8) default null
) engine=innodb default charset=utf8;

mysql> update dmpush_message_temp set stat=1 ;
query ok, 226651 rows affected (1.69 sec)
rows matched: 226651 changed: 226651 warnings: 0
a.binlog中第一个出现的update事务日志：
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000007 >/tmp/test.log

2281772 ### update qianyi.dmpush_message_temp
2281773 ### where
2281774 ### @1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* varstring(108) meta=108 nullable=1 is_null=0 */
2281775 ### @2=133 /* longint meta=0 nullable=1 is_null=0 */
2281776 ### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
2281777 ### @4='0′ /* varstring(24) meta=24 nullable=1 is_null=0 */
2281778 ### set
2281779 ### @1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* varstring(108) meta=108 nullable=1 is_null=0 */
2281780 ### @2=133 /* longint meta=0 nullable=1 is_null=0 */
2281781 ### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
2281782 ### @4='1′ /* varstring(24) meta=24 nullable=1 is_null=0 */

b.binlog中最后出现的update事务日志：
5313201 ### update qianyi.dmpush_message_temp
5313202 ### where
5313203 ### @1='fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* varstring(108) meta=108 nullable=1 is_null=0 */
5313204 ### @2=133 /* longint meta=0 nullable=1 is_null=0 */
5313205 ### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
5313206 ### @4='0′ /* varstring(24) meta=24 nullable=1 is_null=0 */
5313207 ### set
5313208 ### @1='fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* varstring(108) meta=108 nullable=1 is_null=0 */
5313209 ### @2=133 /* longint meta=0 nullable=1 is_null=0 */
5313210 ### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
5313211 ### @4='1′ /* varstring(24) meta=24 nullable=1 is_null=0 */
注意这里由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；

c.计算有多少事务条目：
root@xxxxxxxxx # cat /tmp/test.log|grep ‘update qianyi.dmpush_message_temp' -a 10 |wc -l
2521492

mysql> select 2521492/11;—-11为一个update事务条目占用的行数
+————-+
| 2521492/11 |
+————-+
| 229226.5455 |
+————-+

mysql> use qianyi
database changed
mysql> select count(*) from dmpush_message_temp;
+———-+
| count(*) |
+———-+
| 226651 |
+———-+
可以看到，binlog的条目数和该表的数据量查不多是一致，也就是在全表更新的时候(在row模式下)更新多少行，就有多少事务的事务条目；
2.为dmpush_message_temp表添加主键：
mysql> alter table dmpush_message_temp add column id int not null auto_increment,add primary key(id);
query ok, 226651 rows affected (1.09 sec)
records: 226651 duplicates: 0 warnings: 0

mysql> update dmpush_message_temp set stat=0 ;
query ok, 226651 rows affected (1.69 sec)
rows matched: 226651 changed: 226651 warnings: 0

解析binlog中的事务条目：
root@xxxxxxxxx # mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000008 >/tmp/test1.log

### update qianyi.dmpush_message_temp
### where
### @1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* varstring(108) meta=108 nullable=1 is_null=0 */
### @2=133 /* longint meta=0 nullable=1 is_null=0 */
### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
### @4='1′ /* varstring(24) meta=24 nullable=1 is_null=0 */
### @5=1 /* int meta=0 nullable=0 is_null=0 */
### update qianyi.dmpush_message_temp
### where
### @1='fb5bdc4f-da8a-4f03-aa5e-27677d7c8ac3′ /* varstring(108) meta=108 nullable=1 is_null=0 */
### @2=133 /* longint meta=0 nullable=1 is_null=0 */
### @3='20121012220000′ /* varstring(42) meta=42 nullable=1 is_null=0 */
### @4='1′ /* varstring(24) meta=24 nullable=1 is_null=0 */
### @5=2 /* int meta=0 nullable=0 is_null=0 */
可以看到这里的事务条目中由于已经有了主键，也就是@5（第一个事务条目更新和第二个事务条目更新的@5是递增的，即主键）,这样事务日志就会根据主键来更新，备库执行则不会卡住；

解决：
问题的原因已经找到了，由于表中没有主键，row模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫。。。。所以slave一直卡住；
1.slave:停止slave；
mysql> stop slave;
ctrl-c — sending “kill query 59028” to server …
ctrl-c — query aborted.
ctrl-c — sending “kill 59028” to server …
ctrl-c — query aborted.
ctrl-c — exit!
aborted
2.这个时候，发现slave已经卡住，无法进行任何操作，这个时候只有强行kill掉mysql进程
[e-mail] # ps -ef|grep 3006
root 4456 1 0 oct11 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe –defaults-file=/etc/my3006.cnf
mysql 6828 4456 0 oct11 ? 00:39:27 /usr/sbin/mysqld –defaults-file=/etc/my3006.cnf –basedir=/ –datadir=/home/mysql/data3006/dbs3006 –user=mysql –log-error=/home/mysql/data3006/mysql/master-error.log –open-files-limit=8192 –pid-file=/home/mysql/data3006/dbs3006/xxxxxxxx.com.pid –socket=/home/mysql/data3006/tmp/mysql.sock –port=3006

kill -9 4456 6828

由于我们的slave复制是在mysqld启动的时候自动启动，所以这里我们需要将其关闭：
vi /etc/my3006.cnf中加入：skip-slave-start，在用mysqld_safe启动；

2.由于主库的binlog已经传入备库，这个时候，slave执行没有主键更新的事务日志就会hang住，这个时候可以采取一种巧妙的方法来避过，就是将备库中的这张表进行数据清空，slave在执行realy log的时候，就会报1032错误，我们写一个脚本进行skip掉这些错误，当备库追赶上主库后，我们在把主库的表通过mysqldump，或者insert select 还原到备库，这样就可以让slave正常的运行起来，然后在通知客户进行主键的改造；
a。slave上执行以下命令：
slave:清空备库上有问题的表
set sql_log_bin=off;
truncate table qianyi.dmpush_message_temp;
start slave;

跳过该表上的错误：
sh /tmp/skip.sh 3006 dmpush_message_temp

b.等备库追上主库后，执行以下命令：
master：

lock tables qianyi.dmpush_message_temp read;

create table a2 like qianyi.dmpush_message_temp ;

lock tables a2 write, qianyi.dmpush_message_temp read;

insert into a2 select * from qianyi.dmpush_message_temp ;

slave:

set sql_log_bin=off;

drop table qianyi.dmpush_message_temp;

create table qianyi.dmpush_message_temp like a2;

insert into qianyi.dmpush_message_temp select * from a2;

c.最后让应用改造，添加上主键：
mysql> alter table dmpush_message_temp add column id int not null auto_increment,add primary key(id);

3.当slave卡住的时候，可以通过解析binlog来看看，slave到底卡住在那里，是那个事务，下面是一个简单的方法，来看当前salve打开的表：
mysql> show open tables;
+———-+———————+——–+————-+
| database | table | in_use | name_locked |
+———-+———————+——–+————-+
| qianyi | dmpush_message_temp | 1 | 0 |
| qianyi | test | 0 | 0 |
| qianyi | anson | 0 | 0 |
| mysql | db | 0 | 0 |
| mysql | slow_log | 0 | 0 |
| mysql | event | 0 | 0 |
+———-+———————+——–+————-+
可以看到这里dmpush_message_temp一直处于打开状态，这里就可以直接定位问题的根源了；

总结：主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他，而且主键的设计最好选择自增型的主键，这里也可以略提一下自增主键的好处：
a.自增型主键以利于插入性能的提高；
b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；
c.自增型的主键可以减小page的碎片，提升空间和内存的使用。