mysql主键的缺少导致备库hang住
最近线上频繁的出现slave延时的情况，经排查发现为用户在删除数据的时候，由于表主键的主键的缺少，同时删除条件没有索引，或或者删除的条件过滤性极差，导致slave出现hang住，严重的影响了生产环境的稳定性，也希望通过这篇博客，来加深主键在innodb引擎中的重要性，希望用户在使用RDS，设计自己的表的时候，一定要为表加上主键，主键可以认为是innodb存储引擎的生命，下面我们就来分析一下这个案例（本案例的生产环境的binlog为row模式，对于myisam存储引擎也有同样的问题）：
(1).现象slave:
mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent 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 ###UPDATEqianyi.dmpush_message_temp ###WHERE ###@1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/*VARSTRING(108)meta=108nullable=1is_null=0*/ ###@2=133/*LONGINTmeta=0nullable=1is_null=0*/ ###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/ ###@4='0′/*VARSTRING(24)meta=24nullable=1is_null=0*/
(3)分析：
模拟场景：
1.表中无主键，全表进行更新：
master:
表结构：
CREATETABLE`dmpush_message_temp`(
`clientid`varchar(36)DEFAULTNULL,
`infoid`bigint(10)DEFAULTNULL,
`endtime`varchar(14)DEFAULTNULL,
`stat`varchar(8)DEFAULTNULL
)ENGINE=innodbDEFAULTCHARSET=utf8;
mysql>updatedmpush_message_tempsetstat=1;
QueryOK,226651rowsaffected(1.69sec)
Rowsmatched:226651Changed:226651Warnings:0
a.binlog中第一个出现的update事务日志：
mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000007>/tmp/test.log
2281772###UPDATEqianyi.dmpush_message_temp
2281773###WHERE
2281774###@1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/*VARSTRING(108)meta=108nullable=1is_null=0*/
2281775###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
2281776###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
2281777###@4='0′/*VARSTRING(24)meta=24nullable=1is_null=0*/
2281778###SET
2281779###@1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/*VARSTRING(108)meta=108nullable=1is_null=0*/
2281780###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
2281781###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
2281782###@4='1′/*VARSTRING(24)meta=24nullable=1is_null=0*/
b.binlog中最后出现的update事务日志：
5313201###UPDATEqianyi.dmpush_message_temp
5313202###WHERE
5313203###@1='fffff4fc-0b72-4ba2-9749-0189658af6d5′/*VARSTRING(108)meta=108nullable=1is_null=0*/
5313204###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
5313205###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
5313206###@4='0′/*VARSTRING(24)meta=24nullable=1is_null=0*/
5313207###SET
5313208###@1='fffff4fc-0b72-4ba2-9749-0189658af6d5′/*VARSTRING(108)meta=108nullable=1is_null=0*/
5313209###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
5313210###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
5313211###@4='1′/*VARSTRING(24)meta=24nullable=1is_null=0*/
注意这里由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；
c.计算有多少事务条目：
root@xxxxxxxxx#cat/tmp/test.log|grep‘UPDATEqianyi.dmpush_message_temp'-A10|wc-l
2521492
mysql>select2521492/11;—-11为一个update事务条目占用的行数
+————-+
|2521492/11|
+————-+
|229226.5455|
+————-+
mysql>useqianyi
Databasechanged
mysql>selectcount(*)fromdmpush_message_temp;
+———-+
|count(*)|
+———-+
|226651|
+———-+
可以看到，binlog的条目数和该表的数据量查不多是一致，也就是在全表更新的时候(在row模式下)更新多少行，就有多少事务的事务条目；
2.为dmpush_message_temp表添加主键：
mysql>altertabledmpush_message_tempaddcolumnidintnotnullauto_increment,addPRIMARYKey(id);
QueryOK,226651rowsaffected(1.09sec)
Records:226651Duplicates:0Warnings:0
mysql>updatedmpush_message_tempsetstat=0;
QueryOK,226651rowsaffected(1.69sec)
Rowsmatched:226651Changed:226651Warnings:0
解析binlog中的事务条目：
root@xxxxxxxxx#mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000008>/tmp/test1.log
###UPDATEqianyi.dmpush_message_temp
###WHERE
###@1='fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/*VARSTRING(108)meta=108nullable=1is_null=0*/
###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
###@4='1′/*VARSTRING(24)meta=24nullable=1is_null=0*/
###@5=1/*INTmeta=0nullable=0is_null=0*/
###UPDATEqianyi.dmpush_message_temp
###WHERE
###@1='fb5bdc4f-da8a-4f03-aa5e-27677d7c8ac3′/*VARSTRING(108)meta=108nullable=1is_null=0*/
###@2=133/*LONGINTmeta=0nullable=1is_null=0*/
###@3='20121012220000′/*VARSTRING(42)meta=42nullable=1is_null=0*/
###@4='1′/*VARSTRING(24)meta=24nullable=1is_null=0*/
###@5=2/*INTmeta=0nullable=0is_null=0*/
可以看到这里的事务条目中由于已经有了主键，也就是@5（第一个事务条目更新和第二个事务条目更新的@5是递增的，即主键）,这样事务日志就会根据主键来更新，备库执行则不会卡住；
解决：
问题的原因已经找到了，由于表中没有主键，ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫。。。。所以slave一直卡住；
1.slave:停止slave；
mysql>stopslave;
Ctrl-C—sending“KILLQUERY59028”toserver…
Ctrl-C—queryaborted.
Ctrl-C—sending“KILL59028”toserver…
Ctrl-C—queryaborted.
Ctrl-C—exit!
Aborted
2.这个时候，发现slave已经卡住，无法进行任何操作，这个时候只有强行kill掉mysql进程
root@xxxxxxxx.com#ps-ef|grep3006
root445610Oct11?00:00:00/bin/sh/usr/bin/mysqld_safe–defaults-file=/etc/my3006.cnf
mysql682844560Oct11?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-944566828
由于我们的slave复制是在mysqld启动的时候自动启动，所以这里我们需要将其关闭：
vi/etc/my3006.cnf中加入：skip-slave-start，在用mysqld_safe启动；
2.由于主库的binlog已经传入备库，这个时候，slave执行没有主键更新的事务日志就会hang住，这个时候可以采取一种巧妙的方法来避过，就是将备库中的这张表进行数据清空，slave在执行realylog的时候，就会报1032错误，我们写一个脚本进行skip掉这些错误，当备库追赶上主库后，我们在把主库的表通过mysqldump，或者insertselect还原到备库，这样就可以让slave正常的运行起来，然后在通知客户进行主键的改造；
a。slave上执行以下命令：
slave:清空备库上有问题的表
setsql_log_bin=off;
truncatetableqianyi.dmpush_message_temp;
startslave;
跳过该表上的错误：
sh/tmp/skip.sh3006dmpush_message_temp
b.等备库追上主库后，执行以下命令：
master：
locktablesqianyi.dmpush_message_tempread;
createtablea2likeqianyi.dmpush_message_temp;
locktablesa2write,qianyi.dmpush_message_tempread;
insertintoa2select*fromqianyi.dmpush_message_temp;
slave:
setsql_log_bin=off;
droptableqianyi.dmpush_message_temp;
createtableqianyi.dmpush_message_templikea2;
insertintoqianyi.dmpush_message_tempselect*froma2;
c.最后让应用改造，添加上主键：
mysql>altertabledmpush_message_tempaddcolumnidintnotnullauto_increment,addPRIMARYKey(id);
3.当slave卡住的时候，可以通过解析binlog来看看，slave到底卡住在那里，是那个事务，下面是一个简单的方法，来看当前salve打开的表：
mysql>showopentables;
+———-+———————+——–+————-+
|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的碎片，提升空间和内存的使用。