MySQL數(shù)據(jù)清理的需求分析和改進

2024-07-24 12:32:18

字體：大中小

供稿：網(wǎng)友

昨天幫一個朋友看了MySQL數(shù)據(jù)清理的問題，感覺比較有意思，具體的實施這位朋友還在做，已經(jīng)差不多了，我就發(fā)出來大家一起參考借鑒下。

為了保證信息的敏感，里面的問題描述可能和真實情況不符，但是問題的處理方式是真實的。

首先這位朋友在昨天下午反饋說他有一個表大小是近600G,現(xiàn)在需要清理數(shù)據(jù)，只保留近幾個月的數(shù)據(jù)。按照這個量級，我發(fā)現(xiàn)這個問題應(yīng)該不是很好解決，得非常謹慎才對。如果是通用的思路和方法，我建議是使用冷熱數(shù)據(jù)分離的方式。大體有下面的幾類玩法：

exchange partition，這是亮點的特性，可以把分區(qū)數(shù)據(jù)和表數(shù)據(jù)交換，效率還不錯。
rename table，這是MySQL歸檔數(shù)據(jù)的一大利器，在其他商業(yè)數(shù)據(jù)庫里很難實現(xiàn)。
但是為了保險起見，我說還是得看看表結(jié)構(gòu)再說。結(jié)果看到表結(jié)構(gòu)，我發(fā)現(xiàn)這個問題和我預(yù)想的完全不一樣。

這個表的ibd文件大概是600G,不是分區(qū)表，InnoDB存儲引擎。字段看起來也不多。需要根據(jù)時間字段update_time抽取時間字段來刪除數(shù)據(jù)。

我看了下這個表結(jié)構(gòu)，字段不多，除了索引的設(shè)計上有些冗余外，直接看不到其他的問題，但是根據(jù)數(shù)據(jù)的存儲情況來看，我發(fā)現(xiàn)這個問題有些奇怪。不知道大家發(fā)現(xiàn)問題沒有。

這個表的主鍵是基于字段id，而且是主鍵自增，這樣來看，如果要存儲600G的數(shù)據(jù)，表里的數(shù)據(jù)量至少得是億級別。但是大家再仔細看看自增列的值，會發(fā)現(xiàn)只有150萬左右。這個差別也實在太大了。

為了進一步驗證，我讓朋友查詢一下這個表的數(shù)據(jù)量，早上的時候他發(fā)給了我最新的數(shù)據(jù)，一看更加驗證了我的猜想。

mysql> select max(Id) from test_data;

+---------+

| max(Id) |

+---------+

| 1603474 |

+---------+

1 row in set (0.00 sec)

現(xiàn)在的問題很明確，表里的數(shù)據(jù)不到200萬，但是占用的空間近600G，這個存儲比例也實在太高了，或者說碎片也實在太多了吧。

按照這個思路來想，自己還有些成就感，發(fā)現(xiàn)這么大的一個問題癥結(jié)，如果數(shù)據(jù)沒有特別的存儲，200萬的數(shù)據(jù)其實也不算大，清理起來還是很容易的。

朋友聽了下覺得也有道理，從安全的角度來說，只是需要注意一些技巧而已，但是沒過多久，他給我反饋，說表里的數(shù)據(jù)除過碎片，大概也有100多G，可能還有更多。這個問題和我之前的分析還是有一些沖突的。至少差別沒有這么大。200萬的數(shù)據(jù)量，基本就在1G以內(nèi)。但是這里卻是100多個G,遠遠超出我的預(yù)期。

mysql> select round(sum(data_length+index_length)/1024/1024) as total_mb,

-> round(sum(data_length)/1024/1024) as data_mb,

-> round(sum(index_length)/1024/1024) as index_mb

-> from information_schema.tables where table_name='hl_base_data';

+----------+---------+----------+

| total_mb | data_mb | index_mb |

+----------+---------+----------+

| 139202 | 139156 | 47 |

+----------+---------+----------+

1 row in set (0.00 sec)

這個問題接下來該怎么解釋呢。我給這位朋友說，作為DBA，不光要對物理的操作要熟練，還要對數(shù)據(jù)需要保持敏感。

怎么理解呢，update_time沒有索引，id是主鍵，我們完全可以估算數(shù)據(jù)的變化情況。

怎么估算呢，如果大家觀察仔細，會發(fā)現(xiàn)兩次提供的信息相差近半天，自增利的值相差是大概4000左右。一天的數(shù)據(jù)變化基本是1萬。

現(xiàn)在距離10月1日已經(jīng)有24天了，就可以直接估算出數(shù)據(jù)大概是在1363474附近。

mysql> select current_date-'20171001';

+-------------------------+

| current_date-'20171001' |

+-------------------------+

| 24 |

+-------------------------+

1 row in set (0.00 sec)

按照這個思路，我提供了語句給朋友，他一檢查,和我初步的估算值差不了太多。

mysql> select id , create_time ,update_time from test_data where id=1363474;

+---------+---------------------+---------------------+

| id | create_time | update_time |

+---------+---------------------+---------------------+

| 1363474 | 2017-09-29 10:37:29 | 2017-09-29 10:37:29 |

+---------+---------------------+---------------------+

1 row in set (0.07 sec)

簡單調(diào)整一下，就可以完全按照id來過濾數(shù)據(jù)來刪除數(shù)據(jù)了，這個過程還是建議做到批量的刪除，小步快進。

前提還是做好備份，然后慢慢自動化完成。

（編輯：武林網(wǎng)）

上一篇：mysql關(guān)于redo事務(wù)日志ib_logfile的了解

下一篇：在數(shù)據(jù)庫繁忙時如何快速有效的關(guān)閉MySQL服務(wù)