mysql主鍵的缺乏招致備庫hang住。本站提示廣大學習愛好者：（mysql主鍵的缺乏招致備庫hang住）文章只能為提供參考，不一定能成為您想要的結果。以下是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過程
[email protected] # 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的碎片，晉升空間和內存的應用。
