14.1.1 デフォルトの MySQL ストレージエンジンとしての InnoDB

MySQL は、使いやすく、高いパフォーマンスと拡張性を実現するという評価を勝ち得ています。MySQL 5.5 よりも前では、MyISAM がデフォルトのストレージエンジンでした。我々の経験上、ほとんどのユーザーはデフォルト設定を変更しませんでした。MySQL 5.5 以上では、InnoDB がデフォルトのストレージエンジンです。やはりほとんどのユーザーがデフォルト設定を変更しないと予想されます。ただし、InnoDB を使用すれば、デフォルト設定でもユーザーが RDBMS から期待する利点 (ACID トランザクション、参照整合性、およびクラッシュリカバリ) が得られます。InnoDB テーブルを使用して MySQL ユーザー、DBA、または開発者としての生活を改善する方法を探ってみましょう。

ストレージエンジンの使用傾向

MySQL の成長期の 1 年目には、初期の Web ベースのアプリケーションによって並列性と可用性の限界が押し広げられることはありませんでした。近年では、ハードドライブやメモリーの容量および価格性能比がすべて向上しています。MySQL のパフォーマンスの限界を押し広げているユーザーは、信頼性やクラッシュリカバリに多くの関心を持っています。MySQL データベースは、大規模で、高負荷で、強固で、分散型で、重要です。

InnoDB は、このようなユーザーの最優先事項に対処します。ストレージエンジンの使用傾向は、より拡張可能な InnoDB の方へシフトしています。したがって、MySQL 5.5 は、InnoDB をデフォルトのストレージエンジンにするための論理遷移リリースでした。

MySQL は、以前は MyISAM テーブルが必要だったユースケースへの対処に取り組み続けています。MySQL 5.6 以上には、次のような特性があります。

• InnoDB は、FULLTEXT インデックスタイプを使用した全文検索を実行できます。詳細は、セクション14.2.13.3「FULLTEXT インデックス」を参照してください。

• InnoDB は、読み取り専用または読み取りが大半のワークロードで、より適切に機能するようになりました。自動コミットモードでは、InnoDB クエリーに自動的な最適化が適用され、START TRANSACTION READ ONLY という構文を使用すると、読み取り専用としてトランザクションに明示的にマークできます。詳細は、セクション14.13.14「InnoDB の読み取り専用トランザクションの最適化」を参照してください。

• 読み取り専用メディア上に配布されたアプリケーションでは、InnoDB テーブルが使用されるようになりました。詳細は、セクション14.3.1「読み取り専用操作用の InnoDB の構成」を参照してください。

デフォルトの MySQL ストレージエンジンとしての InnoDB の重要性

MySQL 5.5.5 以降、新しいテーブル用のデフォルトのストレージエンジンは InnoDB です。この変更は、新たに作成されたテーブルの中で、ENGINE=MyISAM などの句を使用してストレージエンジンが指定されていないものに適用されます。このようにデフォルトの動作を変更すると、MySQL 5.5 は、MyISAM が使用されているテーブルが InnoDB に切り替えることによる利点を得られるかどうかを評価するための論理ポイントになる可能性があります。

MySQL 内部仕様の一部が実装されている mysql および information_schema データベースでは、引き続き MyISAM が使用されます。特に、付与テーブルを切り替えても、InnoDB を使用できません。

InnoDB テーブルの利点

MyISAM テーブルを使用しているが、技術的な理由でそれらに関与していない場合は、InnoDB テーブルを使用すると、さらに便利な点が数多く見つかるでしょう。

• ハードウェアまたはソフトウェアの問題が原因でサーバーがクラッシュした場合でも、その時点でデータベースに何が発生していたのかには関係なく、データベースの再起動後に特別なことは何もする必要がありません。InnoDB のクラッシュリカバリを使用すると自動的に、クラッシュ時の前にコミットされた変更はすべて完了し、処理中だったがコミットされなかった変更はすべて取り消されます。単に再起動し、終了した場所から続行するだけです。このプロセスは、MySQL 5.1 以前よりも大幅に高速になりました。

• テーブルおよびインデックスのデータにアクセスすると、そのデータは InnoDB のバッファープールにキャッシュされます。頻繁に使用されるデータは、直接メモリーから処理されます。このキャッシュは非常に数多くのタイプの情報に適用され、これにより処理速度が大幅に上がります。その結果、専用のデータベースサーバーでは、最大で物理メモリーの 80% が InnoDB のバッファープールに割り当てられます。

• 関連データをさまざまなテーブルに分割すると、強制的に参照整合性が適用される外部キーを設定できます。データを更新または削除すると、ほかのテーブル内の関連データも自動的に更新または削除されます。プライマリテーブル内に対応するデータが存在しないセカンダリテーブルにデータを挿入しようとすると、自動的に不正なデータが除外されます。

• ディスク上またはメモリー内のデータが破損した場合は、偽のデータを使用する前に、チェックサムメカニズムによって警告が発行されます。

• テーブルごとに適切な主キーカラムを持つデータベースを設計すると、これらのカラムが関与する操作が自動的に最適化されます。WHERE 句、ORDER BY 句、GROUP BY 句、および結合操作では、主キーカラムへの参照が非常に高速です。

• 挿入、更新、および削除は、変更バッファリングと呼ばれる自動化メカニズムによって最適化されます。InnoDB では、同じテーブルへの並列読み取りおよび書き込みアクセスが許可されているだけでなく、ディスク I/O が効率化されるように変更されたデータがキャッシュに入れられます。

• パフォーマンスの利点は、長時間実行されるクエリーを含む巨大なテーブルだけに限定されません。同じ行が 1 つのテーブルから何度もアクセスされると、適応型ハッシュインデックスと呼ばれる機能に引き継がれ、ハッシュテーブルから読み取られたかのように、これらの検索がさらに高速になります。

InnoDB テーブルのベストプラクティス

長期間 InnoDB を使用していれば、すでにトランザクションや外部キーなどの機能について理解できています。そうでない場合は、この章全体でこれらについて参照してください。手短に言えば、次のとおりです。

• もっとも頻繁にクエリーが実行されるカラム (複数の場合あり) を使用しているすべてのテーブルに、主キーを指定します。明示的な主キーが存在しない場合は、自動インクリメント値を指定します。

• 複数のテーブルにある同じ ID 値に基づいて、それらのテーブルからデータを抽出する場合は、結合の概念を取り入れます。結合のパフォーマンスを高速にするには、結合カラム上に外部キーを定義し、各テーブル内でそれらのカラムを同じデータ型で宣言します。また、外部キーを使用すると、影響を受けるすべてのテーブルに削除または更新が反映され、親テーブルに対応する ID が存在しない場合は、子テーブル内のデータの挿入が回避されます。

• 自動コミットをオフにします。1 秒間に何百回もコミットすると、パフォーマンスに上限が設定されます (これは、ストレージデバイスの書き込み速度で制限されます)。

• 関連する DML 操作のセットを START TRANSACTION と COMMIT ステートメントで囲むことで、トランザクションにグループ化します。頻繁にはコミットしたくない一方で、コミットなしで何時間も実行される INSERT、UPDATE、または DELETE ステートメントの巨大なバッチも発生させたくありません。

• LOCK TABLE ステートメントの使用を停止します。InnoDB は、一度に同じテーブルへのすべての読み取りおよび書き込みを行うことで、信頼性や高パフォーマンスを犠牲にせずに、複数のセッションを処理できます。行のセットへの排他的な書き込みアクセス権を取得するには、SELECT ... FOR UPDATE という構文を使用して、更新対象の行のみをロックします。

• innodb_file_per_table オプションを有効にして、単一の巨大なシステムテーブルスペース内の代わりに、個別のファイルに各テーブル用のデータおよびインデックスを配置します。この設定は、テーブルの圧縮および高速の切り捨てなどのその他の機能の一部を使用する際に必要となります。

• 使用中のデータおよびアクセスパターンによって、CREATE TABLE ステートメントで新しい InnoDB テーブルの圧縮機能 (ROW_FORMAT=COMPRESSED) からの利点が得られるかどうかを評価します。読み取りおよび書き込みの機能を犠牲にせずに、InnoDB テーブルを圧縮できます。

• オプション --sql_mode=NO_ENGINE_SUBSTITUTION を付けてサーバーを実行して、CREATE TABLE の ENGINE= 句で指定されたストレージエンジンで問題が発生した場合に、別のストレージエンジンを使用してテーブルが作成されないようにします。

InnoDB テーブルに対する最近の改善点

• テーブルおよび関連付けられたインデックスを圧縮できます。

• 以前よりも大幅に小さいパフォーマンスや可用性への影響度で、インデックスを作成および削除できます。

• テーブルの切り捨てが大幅に高速になり、InnoDB でのみ再使用される可能性のあるシステムテーブルスペース内の領域を解放するのではなく、オペレーティングシステムで再使用されるディスク領域を解放できます。

• DYNAMIC 行フォーマットを使用することで、テーブルデータのストレージレイアウトが BLOB および長いテキストフィールドでより効率的になりました。

• INFORMATION_SCHEMA テーブルでクエリーを実行することで、ストレージエンジンの内部動作をモニターできます。

• performance_schema テーブルでクエリーを実行することで、ストレージエンジンのパフォーマンスを詳細にモニターできます。

• パフォーマンスに関して多くの改善点があります。特に、クラッシュリカバリ、つまりデータベースが再起動するときにすべてのデータを自動的に整合させる処理の速度および信頼性が向上しました (InnoDB ユーザーが従来経験してきた速度よりずっと高速です)。データベースが大きいほど、大幅に速度が向上します。

  ほとんどの新しいパフォーマンス機能は自動的です。そうでない場合でも、必要なことは、多くても構成オプションの値を設定するだけです。詳細は、セクション14.13「InnoDB のパフォーマンス」を参照してください。アプリケーションコードで適用できる InnoDB 固有のチューニング技術については、セクション8.5「InnoDB テーブルの最適化」を参照してください。上級ユーザーは、セクション14.12「InnoDB の起動オプションおよびシステム変数」を再確認してください。

デフォルトのストレージエンジンとして InnoDB を使用したテストおよびベンチマーク

MySQL 5.1 以前から MySQL 5.5 以降へのアップグレードが完了する前でも、データベースサーバーまたはアプリケーションでデフォルトのストレージエンジンとして、InnoDB が正常に動作するかどうかをプレビューできます。以前の MySQL リリースでデフォルトのストレージエンジンとして InnoDB を設定するには、コマンド行で --default-storage-engine=InnoDB を指定するか、または my.cnf ファイルの [mysqld] セクションに default-storage-engine=innodb を追加してから、サーバーを再起動します。

デフォルトのストレージエンジンを変更しても、新たに作成されたテーブルしか影響を受けないため、アプリケーションのインストールおよび設定ステップをすべて実行して、すべてが正しくインストールされたことを確認します。次に、すべてのアプリケーション機能を実行して、データのロード、編集、およびクエリー機能がすべて動作することを確認します。テーブルが一部の MyISAM 固有の機能に依存している場合は、エラーが受信されます。エラーを回避するには、ENGINE=MyISAM 句を CREATE TABLE ステートメントに追加します (たとえば、全文検索に依存するテーブルは InnoDB テーブルではなく、MyISAM テーブルにする必要があります)。

ストレージエンジンについて慎重な決定を行わなかった場合に、特定のテーブルが InnoDB で作成されたときにどのように動作するのかをプレビューするには、テーブルごとに ALTER TABLE table_name ENGINE=InnoDB; コマンドを発行します。また、元のテーブルを配布せずに、テストクエリーおよびその他のステートメントを実行するには、次のようなコピーを作成します。

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;

MySQL 5.5 以上には、非常に多くの InnoDB のパフォーマンス拡張機能があるため、現実的なワークロードで完全なアプリケーションを使用したときのパフォーマンスについて正確な考察を得るには、最新の MySQL サーバーをインストールして、ベンチマークを実行してください。

完全なアプリケーションのライフサイクル (インストールから頻繁な使用まで)、およびサーバーの再起動をテストします。電源障害のシミュレーションを行うために、データベースの負荷が高いときにサーバープロセスを強制終了し、サーバーの再起動時にデータが正常にリカバリされるかどうかを確認します。

特に、マスターおよびスレーブ上でさまざまな MySQL バージョンやオプションを使用している場合は、レプリケーション構成をテストします。

InnoDB がデフォルトのストレージエンジンであるかどうかの確認

古い MySQL を使用して what-if テストを行うのか、最新の MySQL を使用して包括的なテストを行うのかに関係なく、InnoDB のステータスを確認する方法は、次のとおりです。

• SHOW ENGINES; コマンドを発行して、さまざまな MySQL ストレージエンジンをすべて表示します。InnoDB 行で DEFAULT を探します。

• InnoDB がまったく存在しない場合は、InnoDB のサポートなしでコンパイルされた mysqld バイナリがあるため、別のバイナリを入手する必要があります。

• InnoDB は存在するが、無効になっている場合は、起動オプションおよび構成ファイルまで戻って、すべての skip-innodb オプションを削除します。