MySQL 刪除大表的性能問題解決方案
更新時間：2012年11月24日 18:51:56 作者：
MySQL在刪除大表engine=innodb（30G+）時，如何減少MySQL hang的時間,本為將提供詳細的解決方案,需要了解的朋友可以參考下
微博上討論MySQL在刪除大表engine=innodb（30G+）時，如何減少MySQL hang的時間，現(xiàn)做一下簡單總結：
當buffer_pool很大的時候（30G+），由于刪除表時，會遍歷整個buffer pool來清理數(shù)據(jù)，會導致MySQL hang住，解決的辦法是：
1、當innodb_file_per_table=0的時候，以上不是問題，因為采用共享表空間的時候，該表所占用的空間不會被刪除，buffer pool中的相關頁不會 被discard。
2、當innodb_file_per_table=1的時候，并且當buffer_pool比較大的時候，遍歷整個buffer pool 需要很多的時間(table_cache 會被鎖住，所有的DML操作被阻止)。
認識誤區(qū)：
將innodb 表改為 myisam表，這個是沒有效果的，該操作會刪除舊表，建立新表，依然會遍歷整個buffer_pool。
解決思路：
1、采用腳本形式，批量刪除部分記錄
2、可以再slave上進行操作，進行主備切換（成本高）
3、Percona 5.1.58以上版本都支持innodb_lazy_drop_table（bug不少，慎用）
4、與buffer pool無關，但是可以加快 刪除數(shù)據(jù)文件的速度，同樣能減少MySQL hang住的時間。即：對數(shù)據(jù)文件建立硬鏈接，（依賴原理：OS HARD LINK 當多個文件名同時指向同一個INODE時,這個INODE的引用數(shù)N>1, 刪除其中任何一個文件名只是刪除了一個指針而已，不會刪除數(shù)據(jù)文件。當INODE的引用數(shù)N=1時, 刪除文件需要去把這個文件相關的所有數(shù)據(jù)塊清除,所以會比較耗時）
當buffer_pool很大的時候（30G+），由于刪除表時，會遍歷整個buffer pool來清理數(shù)據(jù)，會導致MySQL hang住，解決的辦法是：
1、當innodb_file_per_table=0的時候，以上不是問題，因為采用共享表空間的時候，該表所占用的空間不會被刪除，buffer pool中的相關頁不會 被discard。
2、當innodb_file_per_table=1的時候，并且當buffer_pool比較大的時候，遍歷整個buffer pool 需要很多的時間(table_cache 會被鎖住，所有的DML操作被阻止)。
認識誤區(qū)：
將innodb 表改為 myisam表，這個是沒有效果的，該操作會刪除舊表，建立新表，依然會遍歷整個buffer_pool。
解決思路：
1、采用腳本形式，批量刪除部分記錄
2、可以再slave上進行操作，進行主備切換（成本高）
3、Percona 5.1.58以上版本都支持innodb_lazy_drop_table（bug不少，慎用）
4、與buffer pool無關，但是可以加快 刪除數(shù)據(jù)文件的速度，同樣能減少MySQL hang住的時間。即：對數(shù)據(jù)文件建立硬鏈接，（依賴原理：OS HARD LINK 當多個文件名同時指向同一個INODE時,這個INODE的引用數(shù)N>1, 刪除其中任何一個文件名只是刪除了一個指針而已，不會刪除數(shù)據(jù)文件。當INODE的引用數(shù)N=1時, 刪除文件需要去把這個文件相關的所有數(shù)據(jù)塊清除,所以會比較耗時）
相關文章
這篇文章主要介紹了MySQL中TEXT與BLOB字段類型的區(qū)別,本文總結了6大區(qū)別,需要的朋友可以參考下2014-08-08
這篇文章主要介紹了MySQL系列之十四 MySQL的高可用實現(xiàn),從工作原理到具體的技術實現(xiàn),本文詳細的講述了該項技術,以下就是詳細內容,需要的朋友可以參考下2021-07-07
本文給大家分享MySQL命令執(zhí)行sql文件的兩種方法,每種方法給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2023-11-11
天天和數(shù)據(jù)庫打交道,一天能寫上幾十條 SQL 語句,但你知道我們的系統(tǒng)是如何和數(shù)據(jù)庫交互的嗎?下面這篇文章主要給大家介紹了關于MySql中sql語句執(zhí)行過程詳細講解的相關資料,需要的朋友可以參考下2023-02-02
本篇文章是對Mysql中的臨時表以及特點進行了詳細的分析介紹，需要的朋友參考下2013-06-06
MySQL通配符與正則表達式搜過濾數(shù)據(jù)詳解
簡單來說,正則表達式就是用來匹配文本的特殊字符串,下面這篇文章主要給大家介紹了關于MySQL通配符與正則表達式搜過濾數(shù)據(jù)的相關資料,文中通過實例代碼以及圖文介紹的非常詳細,需要的朋友可以參考下2022-09-09
MySQL使用UNIQUE實現(xiàn)數(shù)據(jù)不重復插入
當unique列在一個UNIQUE鍵上插入包含重復值的記錄時，我們可以控制MySQL如何處理這種情況：使用IGNORE關鍵字或者ON DUPLICATE KEY UPDATE子句跳過INSERT、中斷操作或者更新舊記錄為新值。2017-05-05
這篇文章主要為大家詳細介紹了mysql 8.0.22 winx64安裝配置圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-11-11
Centos7.3下mysql5.7.18安裝并修改初始密碼的方法
這篇文章主要為大家詳細介紹了Centos7.3下mysql5.7.18安裝并修改初始密碼的方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-06-06
這篇文章主要介紹了mysql中如何把主鍵定義為自動增長標識符類型，下面有個不錯的示例，大家可以參考下2014-07-07