mysql 刪表引出的問題

weixin_30639719發表於2020-04-05
MySql

背景

將測試環境的表同步到另外一個資料庫伺服器中，但有些表裡面資料巨大，（其實不同步該表的資料就行，當時沒想太多），幾千萬的資料！！

 

步驟

1. 既然已經把資料同步過來的話，那就直接delete掉就行，多大的事呢？ 於是

delete from table_name where 1=1;

結果傻眼了，執行了一會兒就卡死了，對卡死了！

 

2. 那麼問題來了，是不是死鎖了呢？那怎麼判斷死鎖呢？

SHOW PROCESSLIST;

 

執行這個命令可以檢視資料庫當前的程式

 

3. 檢視當前的事物

#當前執行的所有事務
mysql> SELECT * FROM information_schema.INNODB_TRX;

#當前出現的鎖
mysql> SELECT * FROM information_schema.INNODB_LOCKs;

#鎖等待的對應關係
mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;

如果你是linux上直接執行mysql命令的話可以這樣執行

SELECT * FROM information_schema.INNODB_TRX\G
這樣的話會換行展示資料，更人性化

 

4. 可以通過kill命令來幹掉一些資料庫的程式

kill 2；

這樣既可，這樣只是解決了卡死的問題，執行同樣的命令還是會卡死的 ；

 

5. 那就想想為什麼會卡死呢 ？原來有個這麼一個引數

一般等待超時的時候會丟擲這樣的異常：

Lock wait timeout exceeded; try restarting transaction

是因為mysql的話，預設鎖等待時間是50s，就是說50s後就會報錯，所以需要修改下個時間

mysql的配置一般都是在my.cnf檔案中，自己找下

新增/修改innodb_lock_wait_timeout=500既可

 

6. 修改後重啟mysql，這裡面又涉及到mysql的關閉

bin/mysqladmin -uroot -p shutdown

就關閉了sql的，不建議直接kill -9 ，不知道會出現什麼么蛾子

重啟的話，還是用mysql_safe去啟動既可

 

7. 其實把，刪除表資料的話，用truncate就行的 ，

truncate table table_name;

 

8. 那麼就比較下 drop，truncate，delete的區別吧

　　a. drop是整個表都沒有了的，表結構也沒沒有了的，truncate和delete的表結構還在；

　　b. delete可以指定where條件刪除哪一行，truncate是整個表的；

　　c. 來百度一下，^_^

　　

1. truncate和 delete只刪除資料不刪除表的結構(定義) 
    drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的儲存過程/函式將保留,但是變為invalid狀態. 
2.delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發. 
   truncate,drop是ddl, 操作立即生效,原資料不放到rollback segment中,不能回滾. 操作不觸發trigger. 
3.delete語句不影響表所佔用的extent, 高水線(high watermark)保持原位置不動 
  顯然drop語句將表所佔用的空間全部釋放 
  truncate 語句預設情況下見空間釋放到 minextents個 extent,除非使用reuse storage;   truncate會將高水線復位(回到最開始). 
4.速度,一般來說: drop>; truncate >; delete 
5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及

 

轉載於:https://www.cnblogs.com/jwentest/p/8067614.html

相關文章