MySQL數(shù)據(jù)清理的需求分析和改進(jìn)
昨天幫一個(gè)朋友看了MySQL數(shù)據(jù)清理的問題，感覺比較有意思，具體的實(shí)施這位朋友還在做，已經(jīng)差不多了，我就發(fā)出來大家一起參考借鑒下。
為了保證信息的敏感，里面的問題描述可能和真實(shí)情況不符，但是問題的處理方式是真實(shí)的。
首先這位朋友在昨天下午反饋說他有一個(gè)表大小是近600G,現(xiàn)在需要清理數(shù)據(jù)，只保留近幾個(gè)月的數(shù)據(jù)。按照這個(gè)量級，我發(fā)現(xiàn)這個(gè)問題應(yīng)該不是很好解決，得非常謹(jǐn)慎才對。如果是通用的思路和方法，我建議是使用冷熱數(shù)據(jù)分離的方式。大體有下面的幾類玩法：
exchange partition，這是亮點(diǎn)的特性，可以把分區(qū)數(shù)據(jù)和表數(shù)據(jù)交換，效率還不錯(cuò)。
rename table，這是MySQL歸檔數(shù)據(jù)的一大利器，在其他商業(yè)數(shù)據(jù)庫里很難實(shí)現(xiàn)。
但是為了保險(xiǎn)起見，我說還是得看看表結(jié)構(gòu)再說。結(jié)果看到表結(jié)構(gòu)，我發(fā)現(xiàn)這個(gè)問題和我預(yù)想的完全不一樣。
這個(gè)表的ibd文件大概是600G,不是分區(qū)表，InnoDB存儲(chǔ)引擎。字段看起來也不多。需要根據(jù)時(shí)間字段update_time抽取時(shí)間字段來刪除數(shù)據(jù)。
我看了下這個(gè)表結(jié)構(gòu)，字段不多，除了索引的設(shè)計(jì)上有些冗余外，直接看不到其他的問題，但是根據(jù)數(shù)據(jù)的存儲(chǔ)情況來看，我發(fā)現(xiàn)這個(gè)問題有些奇怪。不知道大家發(fā)現(xiàn)問題沒有。
這個(gè)表的主鍵是基于字段id，而且是主鍵自增，這樣來看，如果要存儲(chǔ)600G的數(shù)據(jù)，表里的數(shù)據(jù)量至少得是億級別。但是大家再仔細(xì)看看自增列的值，會(huì)發(fā)現(xiàn)只有150萬左右。這個(gè)差別也實(shí)在太大了。
為了進(jìn)一步驗(yàn)證，我讓朋友查詢一下這個(gè)表的數(shù)據(jù)量，早上的時(shí)候他發(fā)給了我***的數(shù)據(jù)，一看更加驗(yàn)證了我的猜想。
- mysql> select max(Id) from test_data;
- +---------+
- | max(Id) |
- +---------+
- | 1603474 |
- +---------+
- 1 row in set (0.00 sec)
現(xiàn)在的問題很明確，表里的數(shù)據(jù)不到200萬，但是占用的空間近600G，這個(gè)存儲(chǔ)比例也實(shí)在太高了，或者說碎片也實(shí)在太多了吧。
按照這個(gè)思路來想，自己還有些成就感，發(fā)現(xiàn)這么大的一個(gè)問題癥結(jié)，如果數(shù)據(jù)沒有特別的存儲(chǔ)，200萬的數(shù)據(jù)其實(shí)也不算大，清理起來還是很容易的。
朋友聽了下覺得也有道理，從安全的角度來說，只是需要注意一些技巧而已，但是沒過多久，他給我反饋，說表里的數(shù)據(jù)除過碎片，大概也有100多G，可能還有更多。這個(gè)問題和我之前的分析還是有一些沖突的。至少差別沒有這么大。200萬的數(shù)據(jù)量，基本就在1G以內(nèi)。但是這里卻是100多個(gè)G,遠(yuǎn)遠(yuǎn)超出我的預(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)
這個(gè)問題接下來該怎么解釋呢。我給這位朋友說，作為DBA，不光要對物理的操作要熟練，還要對數(shù)據(jù)需要保持敏感。
怎么理解呢，update_time沒有索引，id是主鍵，我們完全可以估算數(shù)據(jù)的變化情況。
怎么估算呢，如果大家觀察仔細(xì)，會(huì)發(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)
按照這個(gè)思路，我提供了語句給朋友，他一檢查,和我初步的估算值差不了太多。
- 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ù)了，這個(gè)過程還是建議做到批量的刪除，小步快進(jìn) 。
前提還是做好備份，然后慢慢自動(dòng)化完成。
