MySQL資料清理的需求分析和改進

昨天幫一個朋友看了MySQL資料清理的問題，感覺比較有意思，具體的實施這位朋友還在做，已經差不多了，我就發出來大家一起參考借鑑下。

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

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

1. exchange partition，這是亮點的特性，可以把分割槽資料和表資料交換，效率還不錯。

2. rename table，這是MySQL歸檔資料的一大利器，在其他商業資料庫裡很難實現。

但是為了保險起見，我說還是得看看錶結構再說。結果看到表結構，我發現這個問題和我預想的完全不一樣。

這個表的ibd檔案大概是600G,不是分割槽表，InnoDB儲存引擎。欄位看起來也不多。需要根據時間欄位update_time抽取時間欄位來刪除資料。

我看了下這個表結構，欄位不多，除了索引的設計上有些冗餘外，直接看不到其他的問題，但是根據資料的儲存情況來看，我發現這個問題有些奇怪。不知道大家發現問題沒有。

這個表的主鍵是基於欄位id，而且是主鍵自增，這樣來看，如果要儲存600G的資料，表裡的資料量至少得是億級別。但是大家再仔細看看自增列的值，會發現只有150萬左右。這個差別也實在太大了。

為了進一步驗證，我讓朋友查詢一下這個表的資料量，早上的時候他發給了我最新的資料，一看更加驗證了我的猜想。

mysql> select max(Id) from test_data;

+---------+

| max(Id) |

+---------+

| 1603474 |

+---------+

1 row in set (0.00 sec)

現在的問題很明確，表裡的資料不到200萬，但是佔用的空間近600G，這個儲存比例也實在太高了，或者說碎片也實在太多了吧。

按照這個思路來想，自己還有些成就感，發現這麼大的一個問題癥結，如果資料沒有特別的儲存，200萬的資料其實也不算大，清理起來還是很容易的。

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

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，不光要對物理的操作要熟練，還要對資料需要保持敏感。

怎麼理解呢，update_time沒有索引，id是主鍵，我們完全可以估算資料的變化情況。

怎麼估算呢，如果大家觀察仔細，會發現兩次提供的資訊相差近半天，自增利的值相差是大概4000左右。一天的資料變化基本是1萬。

現在距離10月1日已經有24天了，就可以直接估算出資料大概是在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)

簡單調整一下，就可以完全按照id來過濾資料來刪除資料了，這個過程還是建議做到批次的刪除，小步快進 。

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