意外と知らないMySQLの落とし穴ーDELETEする際の注意点

MySQLのInnodbのDELETE文で物理削除する際の注意点です。

物理削除ですので、基本的に復旧できないため、注意するのは当然ですが、今回記載するのは以下の3つです。

  • 物理削除してもディスク容量は空かないよ
    • ディスク容量削減のための物理削除の場合は特に要注意です
  • パフォーマンスが劣化することがあるよ
    • これはどんなアプリケーションにおいても要注意ですね
  • オートインクリメントが巻き戻ることがある
    • MySQL5.7以前で発生
      • 例えば、オートインクリメント値が100のデータを登録して、オートインクリメント100のデータを削除。そのあとMySQLサーバを再起動すると、オートインクリメントは99まで巻き戻ります。
      • 以下のような感じです
アクション操作するデータAUTO_INCREMENTの値
登録(INSERT)オートインクリメントの値が100で登録される100
削除(DELETE)オートインクリメントの値が100のデータを削除100
サーバ再起動99(100のデータが削除されているので99に巻き戻る)
登録(INSERT)オートインクリメントの値が100で登録される100

ということです。

スポンサーリンク

なぜDELETEしてもディスク容量は空かないのか

一度取得した領域はDELETEしても再利用するために保持しておくためです。

どうしてもディスク容量を空ける必要がある場合は

  • テーブルを最適化する際に使うOPTIMIZE TABLEを実行する

    • 「OPTIMIZE TABLE テーブル名」で実行できます
  • ALTER TABLEでテーブルを作り変える
    • ALTER TABLEはテーブルの作り替えです
      • 実行中はテーブルロックがかかります
        • 「ALTER TABLE テーブル名 ENGINE INNODB」で実行できます

DELETEのみ実行した場合(PTIMIZE TABLE/ALTER TABLEしない場合)、ディスク容量面でのメリットはないのか

DELETEした領域は、再度使われるタイミング(INSERTしたタイミング)で再利用されるため、ディスク容量の減りは緩やかになるメリットはあります。

DELETEするとパフォーマンスが劣化することがあるのはなぜか

レコード削除したテーブルの統計情報がおかしくなりSELECT文のパフォーマンスが劣化することがあります。

統計情報がおかしくなるというのはどういうことかというと、統計情報が正しく更新できなくなるということです。

MySQLは統計情報をもとに適切にインデックスを使用して、SELECT等の動作をしますが、統計情報がおかしくなっていると適切なインデックスが使用されなくなることがあり、この場合にSELECT等のパフォーメンスが劣化します。

こちらの対処法も以下の通りです。

  • テーブルを最適化する際に使うOPTIMIZE TABLEを実行する

    • 「OPTIMIZE TABLE テーブル名」で実行できます
  • ALTER TABLEでテーブルを作り変える
    • ALTER TABLEはテーブルの作り替えです
      • 実行中はテーブルロックがかかります
        • 「ALTER TABLE 対象テーブル名 ENGINE INNODB」で実行できます

なぜオートインクリメントが巻き戻ることがあるのか

オートインクリメントの値はメモリ上に保存されるため、サーバが再起動すると消えてしまいます。ので、オートインクリメントの値を再取得します。以下のSQLでオートインクリメントの値を再取得するイメージです。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

そのため、最後に登録したデータが削除されているとオートインクリメントが巻き戻る現象が発生します。

対応方法

  • MySQL8系にする
    • この問題はMySQL5.7以前で発生するため、8系にバージョンアップすることで解消できます
  • 物理削除をやめる
    • 物理削除をやめて、論理削除にします
  • オートインクリメント値をカウントするテーブルを作る
    • パフォーマンス・保守性を考慮すると、おすすめできない方法ですが、、、

まとめ

DELETEした場合、以下の点に気をつけよう、というのをみてきました。

  • 物理削除してもディスク容量は空かないよ
  • パフォーマンスが劣化することがあるよ
  • オートインクリメントが巻き戻ることがあるよ

対応を誤るとサービスの稼働に影響してきますので、慎重に対応していきましょー

DELETE以外の注意点について以下の記事に記載していますので、よろしければ!

MySQLの知らないと怖い話-注意点を記載
MySQLの怖い話について 「デフォルト設定では大文字と小文字を区別しません」 「NULLと空文字は別ものです」 「IN句の中に大量の値の入れるとパフォーマンスが劣化する」 「REPLACE句の挙動」 「CONCATで文字列連結時に、ひとつでもNULLな項目があると、結果はNULLになる」など