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中增添新用戶權限的方法...2007-03-03
MySQL 5.6 & 5.7最優(yōu)配置文件模板(my.ini)
這篇文章主要介紹了MySQL 5.6 & 5.7最優(yōu)配置文件模板(my.ini),需要的朋友可以參考下2016-07-07
這篇文章主要介紹了mysql 循環(huán)insert方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
這篇文章主要介紹了MySQL5綠色版windows下安裝總結,需要的朋友可以參考下2017-03-03
select?into?from和insert?into?select的區(qū)別舉例詳解
這篇文章主要介紹了SQL中的SELECT?INTO和INSERT?INTO?SELECT兩種語句的區(qū)別和用法,SELECT?INTO用于創(chuàng)建新表并復制數(shù)據(jù)到新表中,而INSERT?INTO?SELECT用于將數(shù)據(jù)從一個表插入到已存在的另一個表中,需要的朋友可以參考下2025-03-03
解決MySQL報錯incorrect?datetime?value?'0000-00-00?00:00
這篇文章主要給大家介紹了關于如何解決MySQL報錯incorrect?datetime?value?'0000-00-00?00:00:00'?for?column的相關資料,文中通過代碼示例介紹的非常詳細,需要的朋友可以參考下2023-08-08
這篇文章主要介紹了Win10下免安裝版MySQL5.7的安裝和配置教程詳解，本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-06-06
本篇文章是對mysql中的set與enum進行了詳細的分析介紹，需要的朋友參考下2013-06-06
mysql想必大家比較熟悉了,我們常見的crud sql想必大家也是手到拈來,下面這篇文章主要給大家介紹了關于mysql復制表的幾種常用方式,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2023-04-04
MySql數(shù)據(jù)庫之a(chǎn)lter表的SQL語句集合
mysql之a(chǎn)lter表的SQL語句集合，包括增加、修改、刪除字段，重命名表，添加、刪除主鍵等。本文給大家介紹MySql數(shù)據(jù)庫之a(chǎn)lter表的SQL語句集合，感興趣的朋友一起學習吧2016-04-04