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

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

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

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

ということです。

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

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

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

テーブルを最適化する際に使うOPTIMIZE TABLEを実行する
- 「OPTIMIZE TABLE テーブル名」で実行できます
ALTER TABLEでテーブルを作り変える
- ALTER TABLEはテーブルの作り替えです
  - 実行中はテーブルロックがかかります
    - 「ALTER TABLE テーブル名 ENGINE INNODB」で実行できます

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

レコード削除したテーブルの統計情報がおかしくなり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;

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

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

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

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