MySQL & MariaDBの世界

MySQLのOPTIMIZE TABLEとは？テーブル最適化の方法・注意点・代替手法を徹底解説！

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

1. はじめに

MySQLのパフォーマンス低下に悩んでいませんか？データベースのサイズが肥大化すると、クエリの実行速度が遅くなり、アプリケーション全体のパフォーマンスに影響を及ぼす可能性があります。そのような状況を解決するための有効な手段が、「OPTIMIZE TABLE」コマンドです。

この記事では、MySQLの「OPTIMIZE TABLE」について、その基本的な使い方からベストプラクティスまでを詳しく解説します。初心者から中級者まで役立つ内容を提供し、データベースの効率的な管理を支援します。

2. OPTIMIZE TABLEとは？初心者にも分かりやすく解説

OPTIMIZE TABLEの基本概念

「OPTIMIZE TABLE」とは、MySQLのテーブルを最適化するためのコマンドです。このコマンドは、以下のような目的で使用されます：

  • ストレージスペースの回収: データが削除された後に残る未使用領域を回収します。
  • インデックスの再構築: データアクセス速度を向上させるため、インデックスを整理します。
  • 統計情報の更新: クエリの実行計画を最適化するための統計情報をリフレッシュします。

用語の簡単な説明

  • ストレージエンジン: MySQLがテーブルを管理する方法を定義します（例: InnoDB、MyISAM）。
  • デフラグ: ファイルの断片化を解消して、ストレージ効率を高める処理です。

基本的な使用例

以下は、「OPTIMIZE TABLE」を実行する基本的なSQLコマンドです：

OPTIMIZE TABLE table_name;

例えば、「users」というテーブルを最適化する場合、以下のように実行します：

OPTIMIZE TABLE users;

効果の概要

「OPTIMIZE TABLE」を実行することで、テーブルサイズが縮小し、クエリ速度が向上することがあります。これは、特に頻繁にデータを更新・削除するテーブルで有効です。

3. OPTIMIZE TABLEを実行する際のベストプラクティス

実行前の準備

「OPTIMIZE TABLE」を実行する前に、以下の準備を行うことが推奨されます：

  1. バックアップの取得
  • 万が一のデータ損失を防ぐために、テーブルやデータベース全体のバックアップを取得してください。
  • 以下は簡単なバックアップ例です：
    mysqldump -u username -p database_name > backup.sql
  1. ストレージエンジンの確認
  • 「OPTIMIZE TABLE」がサポートされているストレージエンジンを使用していることを確認します。
  • 実行例：
    SHOW TABLE STATUS WHERE Name = 'table_name';

実行中の注意点

  • テーブルロック
  • 実行中はテーブルがロックされるため、他のクエリの実行に影響を与える可能性があります。
  • ビジータイムを避け、深夜やメンテナンス時間帯に実行することが推奨されます。
  • 実行時間
  • テーブルサイズが大きい場合、最適化に時間がかかることがあります。
  • その場合、分割実行や部分的な最適化を検討してください。

実行後の確認

「OPTIMIZE TABLE」実行後に、効果を確認するためのコマンド例：

SHOW TABLE STATUS WHERE Name = 'users';

この結果から、データサイズやインデックスサイズの変化を確認できます。

4. 代替手法とOPTIMIZE TABLEの比較

代替手法の紹介

OPTIMIZE TABLEの代わりに使える手法には、以下のようなものがあります。

  1. ALTER TABLE … ENGINE=InnoDB を使った手動最適化
  2. mysqldumpを用いたデータのエクスポート＆インポート
  3. パーティショニングの活用
  4. テーブルのアーカイブと再作成

ALTER TABLE … ENGINE=InnoDB を使った手動最適化

OPTIMIZE TABLEの代替として、手動でALTER TABLEを実行することで、より細かい制御が可能になります。

実行方法

ALTER TABLE table_name ENGINE=InnoDB;

例えば、users テーブルを最適化する場合：

ALTER TABLE users ENGINE=InnoDB;

メリット

  • 「OPTIMIZE TABLE」とほぼ同じ効果が得られる。
  • 一部のMySQLバージョンでは、OPTIMIZE TABLEより安全に実行できる。

デメリット

  • テーブルサイズが非常に大きい場合、ダウンタイムが発生する可能性がある。

mysqldumpを用いたデータのエクスポート＆インポート

mysqldump を使ってデータを一度エクスポートし、その後インポートすることで、データベース全体をリフレッシュできます。

実行方法

mysqldump -u username -p database_name > backup.sql
mysql -u username -p database_name < backup.sql

メリット

  • すべてのテーブルに適用可能。
  • テーブルを完全に再構築できるため、最適化の効果が最大化される。

デメリット

  • 一時的にデータベースを停止する必要がある。
  • 大規模なデータベースでは時間がかかる。

代替手法との比較表

手法メリットデメリット適用シーン
OPTIMIZE TABLE簡単に実行可能テーブルロックが発生小～中規模のテーブル
ALTER TABLE ENGINE=InnoDBMySQLが内部的に実行する最適化と同様の効果テーブルサイズが大きいと時間がかかるMySQL 5.7以降のInnoDB
mysqldump + インポートデータベース全体の再構築が可能ダウンタイムが発生大規模データの最適化
パーティショニングクエリ速度の向上設定が複雑大規模データ管理
アーカイブと再作成データを整理し、最適化追加のデータ管理が必要古いデータが多いテーブル

5. トラブルシューティング: よくあるエラーと対処法

「Table does not support optimize」エラー

エラー内容

Table does not support optimize, doing recreate + analyze instead

原因

  • InnoDBでは、MySQL 5.7以降でOPTIMIZE TABLEの動作が変更された。
  • MEMORYストレージエンジンでは使用できない。

対処法

  1. テーブルのストレージエンジンを確認
   SHOW TABLE STATUS WHERE Name = 'table_name';
  1. ストレージエンジンがInnoDBの場合
   ALTER TABLE table_name ENGINE=InnoDB;

または統計情報の更新：

   ANALYZE TABLE table_name;

「Lock wait timeout exceeded」エラー

エラー内容

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

原因

  • OPTIMIZE TABLE実行中にテーブルロックが発生し、タイムアウト。

対処法

  1. 負荷の少ない時間帯に実行
  2. タイムアウト値を増やす
   SET innodb_lock_wait_timeout = 100;

「Out of Disk Space」エラー

エラー内容

ERROR 1030 (HY000): Got error 28 from storage engine

原因

  • OPTIMIZE TABLE 実行時に一時ファイルを作成するためのディスク容量不足。

対処法

  1. ディスクの空き容量を確認
   df -h
  1. 一時ディレクトリの変更
    my.cnfを編集：
   [mysqld]
   tmpdir = /path/to/larger/tmp

まとめ

このセクションでは、「OPTIMIZE TABLE」のよくあるエラーとその対処法を紹介しました。エラー発生時は、ストレージエンジンの確認・ロック対策・ディスク容量の確保 を実施しましょう。

6. FAQ（よくある質問）

OPTIMIZE TABLEを実行するとデータが失われる可能性はありますか？

回答

通常、「OPTIMIZE TABLE」を実行してもデータが失われることはありませんが、処理中にエラーが発生するとデータが破損する可能性 があります。
そのため、事前にバックアップを取得することが推奨されます。

バックアップの取得方法

mysqldump -u username -p database_name > backup.sql

OPTIMIZE TABLEをどのくらいの頻度で実行するべきですか？

回答

データの削除頻度によりますが、一般的には 週1回～月1回 の実行が推奨されます。
以下のケースでは、より頻繁に実行すると効果的です：

  • データ削除が多いテーブル
  • インデックスが断片化している
  • クエリ実行速度が低下している

OPTIMIZE TABLEの自動化は可能ですか？

回答

MySQLの イベントスケジューラ や cronジョブ を利用することで、自動化が可能です。

MySQLイベントスケジューラの使用

CREATE EVENT optimize_tables
ON SCHEDULE EVERY 7 DAY
DO
OPTIMIZE TABLE table_name;

cronジョブを使用

crontab -e

次の行を追加（毎週日曜の午前3時に実行）：

0 3 * * 0 mysql -u username -p'yourpassword' -e "OPTIMIZE TABLE database_name.table_name;"

OPTIMIZE TABLEが効果を発揮しない場合の対策は？

回答

  1. ストレージエンジンを確認
   SHOW TABLE STATUS WHERE Name = 'table_name';
  1. クエリの実行計画を確認
   EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
  1. 統計情報の更新
   ANALYZE TABLE table_name;
  1. テーブルサイズが大きすぎる場合
  • mysqldump を使用してバックアップを取り、再インポート
  • パーティショニングを検討

このFAQでは、「OPTIMIZE TABLE」に関するよくある疑問とその解決策を紹介しました。

7. まとめ

本記事では、MySQLの 「OPTIMIZE TABLE」 について詳しく解説しました。
テーブル最適化はデータベースのパフォーマンス向上に欠かせない作業ですが、適切な場面で使用しないと効果が限定的 になることもあります。

OPTIMIZE TABLEの基本ポイント

項目内容
目的データベースのパフォーマンス向上、ストレージ最適化
動作データファイルのデフラグ、インデックスの再構築、統計情報の更新
推奨頻度週1回～月1回（データ削除が多いテーブルはより頻繁に）
ストレージエンジンMyISAM：効果大、InnoDB：効果が限定的

OPTIMIZE TABLEの適用が有効なケース

以下のような場合は、「OPTIMIZE TABLE」の実行を推奨します。

  • データの削除が頻繁に行われる
  • ディスクスペースを節約したい
  • SELECTクエリの速度が低下
  • インデックスの断片化が発生している

実行前の確認リスト

✅ バックアップを取得

mysqldump -u username -p database_name > backup.sql

✅ ストレージエンジンを確認

SHOW TABLE STATUS WHERE Name = 'table_name';

✅ 負荷の少ない時間帯に実行
✅ 統計情報の更新

ANALYZE TABLE table_name;

代替手法との比較

場合によっては、OPTIMIZE TABLEよりも他の手法が適している こともあります。

手法メリットデメリット適用シーン
OPTIMIZE TABLE簡単に実行可能テーブルロックが発生小～中規模のテーブル
ALTER TABLE ENGINE=InnoDB同様の最適化効果大規模テーブルでは時間がかかるMySQL 5.7以降のInnoDB
mysqldump + リストアテーブル全体の完全最適化ダウンタイムが発生大規模データの最適化

最終チェックリスト

✅ 適切なストレージエンジンを使用しているか？
✅ バックアップを取得したか？
✅ 負荷の少ない時間帯に実行するか？
✅ 代替手法が必要か検討したか？

おわりに

MySQLのパフォーマンスを維持するために、「OPTIMIZE TABLE」を適切に活用しましょう！
本記事がデータベース管理の参考になれば幸いです。