TL;DR
MySQLが提供するテーブル保守ステートメントについてまとめました(記事のアクセス日はすべて2024-12-08です)。
- ANALYZE TABLE：インデックス統計情報を更新し、クエリ最適化を支援。
- CHECK TABLE：テーブル構造やデータ整合性を検証。問題点があれば報告。
- CHECKSUM TABLE：テーブルデータの整合性比較用チェックサムを取得し、レプリケーションやバックアップの整合性確認に活用。
- OPTIMIZE TABLE：断片化を解消し、I/O効率を向上。
- REPAIR TABLE：破損したデータを修復。MyISAM、ARCHIVE、CSVテーブルのみ対応。
参考: MySQL :: MySQL 8.4 Reference Manual :: 15.7.3 Table Maintenance Statements
1. ANALYZE TABLE ステートメント
ANALYZE TABLEもいくつか種類(UPDATEやDROPと併用するもの)がありますが、今回はもっともかんたんなものについて説明をします。
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.1 ANALYZE TABLE Statementに以下の記載があります。
ANALYZE TABLE without any HISTOGRAM clause performs a key distribution analysis and stores the distribution for the named table or tables. For MyISAM tables, ANALYZE TABLE for key distribution analysis is equivalent to using myisamchk --analyze.
ここで、「キー分布の保存をして一体何が嬉しいのか？」という疑問が出てくるかもしれませんが、これはクエリの実行計画を最適化するための情報を提供するためです。以下では、クエリの実行計画を最適化するために、ANALYZE TABLEがどのように役立つのかについて説明します。
1.1. MySQLの最適化におけるクエリー実行計画
MySQL :: MySQL 8.4 Reference Manual :: 10.1 Optimization Overview に最適化に関する記載がありますが、MySQLの最適化の方法は、Databaseレベルでの最適化からハードウェアレベルでの最適化まで様々です。その中でも、クエリの実行計画を最適化することも重要な要素の一つです。
クエリの最適化の一つとして、MySQL :: MySQL 8.4 Reference Manual :: 10.2.1 Optimizing SELECT Statementsを読むとANALYZE TABLEを実行しておくことで、optimizerが最適なクエリ実行計画を構築するための情報を提供することができると記載があります。
Keep table statistics up to date by using the ANALYZE TABLE statement periodically, so the optimizer has the information needed to construct an efficient execution plan.
さて、optimizerとは何かというと、MySQL :: MySQL 8.4 Reference Manual :: MySQL Glossaryに次のように記載があります。
The MySQL component that determines the best indexes and join order to use for a query, based on characteristics and data distribution of the relevant tables.
つまり、クエリの実行を行う際には、optimizerが最適なインデックスや結合順序を決定します。そのため、ANALYZE TABLEを実行しておくことで、optimizerが最適なクエリ実行計画を構築するための情報を提供することができるのです。もし、これを実行しない場合は、optimizerが最適なクエリ実行計画を構築するための情報が不足してしまい、パフォーマンスが低下する可能性があります。
公式ドキュメントではないですが、次のブログなどは視覚的で理解しやすいかと思います。
- match1124さん, 2024, データベースにおけるオプティマイザー（optimizer）とは
2. CHECK TABLE ステートメント
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.2 CHECK TABLE Statementを読むと、CHECK TABLEはテーブルの構造やデータの整合性を検証するためのステートメントであることがわかります。
CHECK TABLE checks a table or tables for errors. CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.
さて、このクエリを実行して検査をしてくれることで、どのようなメリットがあるのでしょうか？
InnoDBの場合か、MyISAMの場合かによって異なりますが、MyISAMの場合でCheckの結果がOKでない場合、修復が必要であることがわかります（MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.2 CHECK TABLE Statement）。
If CHECK TABLE output does not return OK or Table is already up to date, you should normally run a repair of the table. See Section 9.6, “MyISAM Table Maintenance and Crash Recovery”.
2.1. コラム: MyISAMとInnoDBの違い
MySQL :: MySQL 8.4 Reference Manual :: 18 Alternative Storage Enginesに詳細は書かれていますが、デフォルトではInnoDBが設定されています。ロックの仕組みについて異なる記述があるため気になった方は参照してみてください。
3. CHECKSUM TABLE ステートメント
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.3 CHECKSUM TABLE Statementを読むと、CHECKSUM TABLEはバックアップした後などに２つのテーブルが同一であるかどうかを確認するためのステートメントであることがわかります。
CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to verify that the contents are the same before and after a backup, rollback, or other operation that is intended to put the data back to a known state.
ただ、大きなテーブルに対して実行すると時間がかかるため、注意が必要のようです（MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.3 CHECKSUM TABLE Statement）。
4. OPTIMIZE TABLE ステートメント
こちらのOPTIMIZE TABLEはANALYZE TABLEと違って物理的なテーブルの最適化を行うステートメントです（ANALYZE TABLEは飽くまでクエリの最適化のための情報を提供するステートメントであり、物理的なテーブルの最適化を行うものではありません）。
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.4 OPTIMIZE TABLE Statementを読むと、OPTIMIZE TABLEはI/Oの効率を向上化するために、フラグメント化されたテーブルを再構築するためのステートメントであることがわかります。
OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.
You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
5. REPAIR TABLE ステートメント
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.5 REPAIR TABLE Statementを読むと、REPAIR TABLEはInnoDBはサポートされていません。
REPAIR TABLE works for MyISAM, ARCHIVE, and CSV tables.
こちらを実行すると、破損したデータを修復することができるようです。
REPAIR TABLE repairs a possibly corrupted table, for certain storage engines only.
感想
普段は「SELECT」や「INSERT」などのクエリを実行さえできればよく、保守や最適化についてはあまり意識していなかったので、今回の調査を通じて、データベースの保守や最適化についての重要性を再認識しました。特にフラグメントなどは大学の授業で学んだことがあるものの、実際に使われている場面を見ることがなかったので、今回の調査を通じて、実務での活用方法を学ぶことができ良かったです。