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