iT邦幫忙

1

是否資料庫頻繁的刪除數據會影響性能,請問其底層原理是什麼?

原問題 :

聽說MySQL頻繁的刪除數據會影響性能,請問原理是什麼? 能影響到什麼程度?

我有一個2千萬數據的表A,最近想拆分一下.

將一部分數據從表A複製到表B,同時從表A中刪掉該部分數據.
今後每日從表A中搬運一部分數據到表B

之前聽說這種高頻度的刪除操作極其影響MySQL的性能, 真的嗎?請問原理是什麼?能影響到什麼程度? 怎麼避開?


修改問題 :

情況1 :
一個大表幾千萬數據,想要拆分
需要將一部分數據從表A複製到表B,同時刪除A的部分數據,每天搬運N%數據

情況2 :
實體臨時表,每次執行一個SP的時候會先delete刪除,之後再執行SP新增資料

聽說以上這種高頻度的刪除操作非常影響資料庫性能
請問原理是什麼? 能影響到什麼程度? 如何避免呢?

這邊嘗試找到一篇文章 , 有解決我的疑惑 , 這是他的原文

  1. 產生大量碎片,影響磁盤IO;
  2. 另外會影響索引的基數Cardinality值,從而導致關聯sql時使用不當的索引;
  3. 如果數據庫拓撲中有做主從同步,一次性delete大量數據,會出現主從同步延遲

首先要了解下,對mysql進行刪除數據操作,磁盤空間並不會立即被回收,這裡的空間包括數據和索引空間,但是可能被後續的insert利用,也可能不會,就形成碎片。

怎麼測試刪除數據mysql沒有立即回收空間呢?
很簡單,首先創建一個innodb表tb,往裡插入大量數據(比如10w條),這個時候看下tb的數據文件tb.ibd的大小,記錄下來;此時再把tb表數據刪除(delete from tb),然後再看下tb.ibd的大小,會發現沒有變化,也就是沒被回收!

當然更關注的是怎麼解決。
問題1、2都可以通過執行OPTIMIZE TABLE 表名來優化表,重新組織表數據和關聯索引數據的物理存儲。(執行完再去看看.ibd的大小是不是變小了)
主從同步延遲的問題則是按照上面說的,分批間隔幾分鐘刪除,把大事務化成小事務去執行。

最後再說下,這種遷移數據到另外一張表,然後刪除大表數據,叫做MySQL歸檔,隨便搜下就有,給個傳送門MySQL大表數據歸檔的幾種方法介紹

看更多先前的討論...收起先前的討論...
你這樣一直修改 發問內容, 其實不太好.
很容易顯得先前回答你問題的人,好像不對題.你覺得這樣的互動方式,好嗎?

你最後 補的 這句 "最後再說下,這種遷移數據到另外一張表,然後刪除大表數據,叫做MySQL歸檔,隨便搜下就有,給個傳送門MySQL大表數據歸檔的幾種方法介紹"

是要來教我們嗎?
李正群 iT邦新手 5 級 ‧ 2020-09-10 11:40:11 檢舉
> 你這樣一直修改 發問內容, 其實不太好.

前輩你好,海綿前輩的發言有做引用
我這邊一開始發問方式的確用詞不好
所以做修改,但主要問題還是一致的

> 是要來教我們嗎?

不是,那個是文章原文...
我加上引用符號了
抱歉,造成誤解
你的方式,不是好的互動方式. 這裡不是IM. 搞得好像我們在修理你的樣子.
李正群 iT邦新手 5 級 ‧ 2020-09-10 11:50:18 檢舉
這邊修改備註原文了,這是我的錯才對,沒再三確認才發問
其實不用大哉問,什麼底層原理, 這種問法,很不好.
另外 空間碎裂 是很基本的東西, 這個觀念, 應該要有.
那篇大陸的資料, 不全然對! 例如這
2.另外會影響索引的基數Cardinality值,從而導致關聯sql時使用不當的索引;
那只要 做統計分析就可以了啦. 索引的判斷,跟刪除資料,並不是一定的關係啊!

所以你要真的懂,那就要靜心虛心的學,而不是東看西看, 然後這樣浮躁.
那是片段且容易看到一堆錯誤又愛講的奇怪觀念.
大陸那篇是東拼西湊的.不是很好
你若想了解 MySQL .
我以前有寫一系列,也是以前的資料,加減參考看看吧.
https://ithelp.ithome.com.tw/users/20050647/ironman/350
李正群 iT邦新手 5 級 ‧ 2020-09-10 12:43:22 檢舉
非常感謝

2 個回答

2
一級屠豬士
iT邦大師 1 級 ‧ 2020-09-10 11:28:51
最佳解答

江湖傳聞聽說....

你們公司應該要有技術方面的整體規劃,而不是這樣零散的問.
因為片段的發問,會與真正的情況,有偏差.
重要的是"目的" 或 "目標", 而非 "手段", 更不是某種手段的疑慮.

李正群 iT邦新手 5 級 ‧ 2020-09-10 11:34:09 檢舉

最近公司反映查詢 log 檔案太久
所以要搬運 log 檔案到一個 log 備份表
所以才有此問題

這種情況很常見,如同你說的, 網路上隨便搜搜就有了啦.

3
海綿寶寶
iT邦大神 1 級 ‧ 2020-09-10 11:18:18

https://ithelp.ithome.com.tw/upload/images/20200910/20001787p1zVoHfBZF.jpg
https://ithelp.ithome.com.tw/upload/images/20200910/20001787a098ho5r7f.jpg
https://ithelp.ithome.com.tw/upload/images/20200910/20001787WmvRp6WLHE.jpg
https://ithelp.ithome.com.tw/upload/images/20200910/200017872pNfbcAukD.jpg

聽說這種高頻度的刪除操作極其影響MySQL的性能

你聽誰說的,就去問他應該比較準

修改回答:

我的原則是
快問快答、簡問簡答、聽說問就聽說答

看來你在這裡已經得到答案了
但我還是不知道你從那裡聽說「高頻度的刪除操作極其影響MySQL的性能」的

所以我的答案還是一樣
你聽誰說的,就去問他應該比較準

李正群 iT邦新手 5 級 ‧ 2020-09-10 11:45:16 檢舉

感謝 , 我這邊修正不恰當發問方式

我以為是要貼這個 XDD

我以為是要貼這個 XDD

/images/emoticon/emoticon01.gif/images/emoticon/emoticon01.gif/images/emoticon/emoticon01.gif

沒想到方岑已經離開演藝圈12年了,還生了病
/images/emoticon/emoticon10.gif

我要發表回答

立即登入回答