【MySQL】短縮URLテーブルのレコード大量削除と断片化解消

こんにちは、情報システム部サービス開発チームの石川です。

長年システムを稼働していると、いつの間にかテーブルに大量のデータが溜まってしまうこともあります。
はじめから溜まりすぎないように設計できればベストですが、溜まってしまったものは仕方ないので削除しましょう。

ターゲット

今回整理するのは、タイトルにもある通り、短縮URLを管理するテーブル slugs （仮名）です。

溜まりに溜まったレコード数、約1億6870万件。

中々のものです。

レコードを削除する

今回はジャンプ先のURLをもとに削除するレコードを決めました。

巨大になってしまったテーブルは扱い方によっては簡単にスロークエリになってしまいます。
はやる気持ちを抑えて少しずつ消しながら、逐次状況も確認できるようにRuby on Railsでスクリプトを組んで、アクセスの少ない週末に流しました。

slug_count = 0
loop do
  count = Slug.where('target LIKE "https://linkers.net/xxxxx%"').limit(1000).delete_all
  slug_count += count
  p "/xxxxx: #{slug_count}"

  break if count.zero?
end

数十時間かけてスクリプトが終わった後で、テーブルの状況を見てみます。

SHOW TABLE STATUS WHERE NAME = "slugs";

Name	Auto Increment	Rows	Data Length	Index Length	Data Free
slugs	168,673,766	868,102	954.0MiB	2.0GiB	66.9GiB

残ったレコード数、約86万件。

実に1億6800万件近いレコードを掃除できました！
AutoIncrementとRowsの差分が今回消したレコード数なので、まさに桁違いなのがわかります。

スッキリ。

これでは終わらなかった

ここで、もう一度先ほどのテーブルの状況を思い出してみましょう。

Name	Auto Increment	Rows	Data Length	Index Length	Data Free
slugs	168,673,766	868,102	954.0MiB	2.0GiB	66.9GiB

注目するのは、DataFree: 66.9GiB。

66.9GiB！

DataFreeとは割り当てられているけれど利用されていない領域のことです。
データが歯抜けになった結果断片化を引き起こしています。
DataFree領域は新しくレコードを作った際に使われますが、単純に考えて1億6800万件のレコードが新規に作られるまで使いきれないサイズです。

無駄なので解消しましょう。

断片化を解消する

InnoDBテーブルの断片化の解消は簡単です。

OPTIMIZE TABLE slugs;

量が量なのと、HDDのデフラグのイメージから、長時間かかることを覚悟していましたが、10秒程度で終わりました。
内部的には残すデータだけを新しいテーブルにコピーして差し替える、ということをやっていて、テーブルロックがかかるのは最後の一瞬だけです。

さて、再度テーブルの状況を見てみましょう。

Name	Auto Increment	Rows	Data Length	Index Length	Data Free
slugs	168,673,766	868,102	143.0MiB	115.0MiB	2.0MiB

スッキリ。

DataFreeだけでなくDataLengthやIndexLengthも小さくなっています。

おわりに

巨大になり過ぎて手のつけられないテーブルも掃除できる！ということがわかりました。
他にも昔ながらの巨大なテーブルがあるので、順次整理していきます。