14.6.4 MyISAM から InnoDB へのテーブルの変換

信頼性および拡張性を改善するために、既存のテーブルを使用するアプリケーションを InnoDB に変換する場合は、次のガイドラインおよびヒントを使用します。このセクションでは、このようなテーブルの大部分が当初は、以前のデフォルトの MyISAM であったことが前提となっています。

MyISAM のメモリー使用量の減少、InnoDB のメモリー使用量の増加

MyISAM テーブルから移行するときに、結果をキャッシュする際に必要でなくなったメモリーが解放されるように、key_buffer_size 構成オプションの値を小さくします。InnoDB テーブル用のキャッシュメモリー割り当てと同様の役割を担う innodb_buffer_pool_size 構成オプションの値を大きくします。InnoDB のバッファープールには、テーブルデータとインデックスデータの両方がキャッシュされるため、クエリーの検索速度を上げることと、再使用するためにクエリー結果をメモリー内に保持することの 2 つ役割があります。

• このオプションには、できるかぎり多くのメモリー (多くの場合、最大でサーバー上の物理メモリーの 80% まで) を割り当てます。

• オペレーティングシステムでその他のプロセス用のメモリーが不足し、スワップが発生し始めた場合は、innodb_buffer_pool_size の値を小さくします。スワップとは、キャッシュメモリーの利点が大幅に減少するような負荷の高い操作です。

• innodb_buffer_pool_size 値が数ギガバイト以上である場合は、innodb_buffer_pool_instances の値を大きくすることを検討してください。これを行うと、同時に数多くの接続がキャッシュにデータを読み込む高負荷のサーバーで役立ちます。

• 高負荷のサーバーでは、クエリーキャッシュをオフにしてベンチマークを実行します。InnoDB のバッファープールでも同様の利点が得られるため、クエリーキャッシュを使用すると、不必要にメモリーが停止する可能性があります。

長すぎるまたは短すぎるトランザクションの監視

MyISAM テーブルではトランザクションがサポートされていないため、autocommit 構成オプションと、COMMIT および ROLLBACK ステートメントに多くの注意が払われていない可能性があります。これらのキーワードは、複数のセッションが並列して InnoDB テーブルの読み取りおよび書き込みを行うことを許可する際に重要となります。これにより、書き込み負荷の高いワークロードで十分な拡張性の利点が得られます。

トランザクションが開いている間は、トランザクションの開始時に見られるようなデータのスナップショットがシステムで保持されます。これにより、未処理のトランザクションが動作し続けている間に、システムで数百万行の挿入、更新、および削除が行われると、相当なオーバーヘッドが発生する可能性があります。そのため、動作時間が長すぎるトランザクションは回避するように注意してください。

• インタラクティブな実験で mysql セッションを使用している場合は、完了後に必ず、(変更を完了させる場合は) COMMIT、または (変更を取り消す場合は) ROLLBACK を実行します。誤ってトランザクションが長時間開いたままになることを回避するには、インタラクティブなセッションを長時間開いたままにせず、閉じてください。

• アプリケーション内の任意のエラーハンドラでも、不完全な変更の ROLLBACK が実行されるか、完了した変更の COMMIT が実行されることを確認します。

• INSERT、UPDATE、および DELETE 操作は、ほとんどの変更は正常にコミットされ、ロールバックはまれにしか発生しないという見込みで、COMMIT よりも前に InnoDB テーブルに書き込まれるため、ROLLBACK は比較的負荷の高い操作です。大量のデータを使用して実験する際は、多数の行に変更を加えてから、それらの変更をロールバックすることは回避してください。

• 一連の INSERT ステートメントを使用して大量のデータをロードする際は、トランザクションが数時間存続することを回避するために、定期的に結果の COMMIT を実行します。データウェアハウスでの一般的なロード操作では、何か問題が発生した場合に、ユーザーは ROLLBACK を行うのではなく、TRUNCATE TABLE を実行し、最初からやり直します。

前述のヒントを使用すると、長すぎるトランザクション中に無駄になる可能性のあるメモリーおよびディスク容量を節約できます。トランザクションが本来よりも短い場合は、過剰な I/O が問題となります。MySQL では、COMMIT が実行されるたびに、各変更が安全にディスクに記録されていることが確認されます。これには、多少の I/O が伴います。

• InnoDB テーブル上のほとんどの操作では、autocommit=0 の設定を使用するようにしてください。効率性の観点から見ると、これにより、多数の連続した INSERT、UPDATE、または DELETE ステートメントを発行したときの不要な I/O が回避されます。安全性の観点から見ると、これにより、mysql コマンド行またはアプリケーションの例外ハンドラに誤りがあった場合に、ROLLBACK ステートメントを発行することで、失ったデータや文字化けしたデータをリカバリできます。

• InnoDB テーブルに autocommit=1 を設定することが適している状況は、レポートの生成または統計の分析を行うために一連のクエリーを実行するときです。このような状況では、COMMIT または ROLLBACK に関連する I/O ペナルティーが発生せず、InnoDB は自動的に読み取り専用のワークロードを最適化できます。

• 関連する一連の変更を行う場合は、最後に 1 回 COMMIT を実行して、これらのすべての変更を一度に完了させます。たとえば、情報の関連部分を複数のテーブルに挿入する場合は、すべての変更を行なったあとに、COMMIT を 1 回実行します。また、連続する多数の INSERT ステートメントを実行する場合は、すべてのデータがロードされたあとに、COMMIT を 1 回実行します。何百万もの INSERT ステートメントを実行する場合は、一万または一千レコードごとに COMMIT を発行することで、巨大なトランザクションを分割することがあります。

• SELECT ステートメントでもトランザクションが開かれるため、インタラクティブな mysql セッションで一部のレポートを実行したり、クエリーをデバッグしたりしたあとは、COMMIT を発行するか、または mysql セッションを閉じます。

デッドロックを心配しすぎないこと

MySQL のエラーログまたは SHOW ENGINE INNODB STATUS の出力に、「デッドロック」に言及する警告メッセージが表示されることがあります。デッドロックは、恐ろしい響きの名前にもかかわらず、InnoDB テーブルにとっては重大な問題でなく、修正アクションは何も必要ありません。2 つのトランザクションが複数のテーブルを変更し、そのテーブルに別々の順序でアクセスし始めると、各トランザクションが相互に待機し合って、どちらも処理できない状態に達する可能性があります。すぐに MySQL によって、この状況が検出され、「小さい方の」トランザクションが取り消され (ロールバックされ)、他方が処理できるようになります。

アプリケーションには、このように強制的に取り消されたトランザクションを再開するためのエラー処理ロジックが必要です。以前と同じ SQL ステートメントを再発行するときは、元のタイミングの問題は適用されません。他方のトランザクションがすでに完了したため一方を処理できるか、他方のトランザクションがまだ処理中で、これが完了するまで一方が待機しているかのいずれかです。

デッドロックの警告が常に発生する場合は、アプリケーションコードを再確認して、一貫性のある方法で SQL 操作を再指示したり、トランザクションを短くしたりすることがあります。innodb_print_all_deadlocks オプションを有効にしてテストすれば、SHOW ENGINE INNODB STATUS 出力の最後の警告だけでなく、MySQL のエラーログにもすべてのデッドロックの警告を表示できます。

ストレージレイアウトの計画

InnoDB テーブルから最高のパフォーマンスを引き出すために、ストレージレイアウトに関連する数多くのパラメータを調整できます。

頻繁にアクセスされ、重要なデータが保持されている大きな MyISAM テーブルを変換する際は、innodb_file_per_table、innodb_file_format、innodb_page_size 構成オプション、および CREATE TABLE ステートメントの ROW_FORMAT と KEY_BLOCK_SIZE 句を調査および検討してください。

初期の実験時に、もっとも重要となる設定は innodb_file_per_table です。新しい InnoDB テーブルを作成する前に、このオプションを有効にすると、InnoDB のシステムテーブルスペースファイルを使用して、すべての InnoDB データ用のディスク領域が永続的に割り当てられなくなります。innodb_file_per_table を有効にすると、DROP TABLE および TRUNCATE TABLE を発行することで、要求どおりにディスク領域が解放されます。

既存テーブルの変換

InnoDB を使用するように InnoDB 以外のテーブルを変換するには、ALTER TABLE を使用します。

ALTER TABLE table_name ENGINE=InnoDB;

重要

mysql データベース内の MySQL システムテーブル (user や host など) を InnoDB 型に変換しないでください。これはサポートされていない操作です。システムテーブルの型は、必ず MyISAM にする必要があります。

テーブル構造のクローニング

古いテーブルと新しいテーブルを切り替える前に並列してテストする際に、ALTER TABLE 変換を行うのではなく、MyISAM テーブルのクローンである InnoDB テーブルを作成することがあります。

同じカラムとインデックスの定義を持つ空の InnoDB テーブルを作成します。show create table table_name\G を使用して、使用される完全な CREATE TABLE ステートメントを表示します。ENGINE 句を ENGINE=INNODB に変更します。

既存データの転送

前のセクションで示したように、作成された空の InnoDB テーブルに大量のデータを転送するには、INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns を使用して行を挿入します。

データを挿入したあとに、InnoDB テーブル用のインデックスを作成することもできます。従来、新しいセカンダリインデックスを作成することは、InnoDB にとって低速な操作でしたが、現在は、インデックスの作成ステップで比較的小さいオーバーヘッドでデータがロードされたあとに、インデックスを作成できるようになりました。

副キー上に UNIQUE 制約がある場合は、インポート操作中に一意性チェックを一時的にオフにすることで、テーブルインポートの速度を上げることができます。

SET unique_checks=0;... import operation ...
SET unique_checks=1;

大きいテーブルの場合、InnoDB はその挿入バッファーを使用して、一括してセカンダリインデックスレコードを書き込むことができるため、これにより、大量のディスク I/O が節約されます。データに重複キーが含まれないようにします。unique_checks では、ストレージエンジンが重複キーを無視することが許可されていますが、必須ではありません。

挿入プロセスをより適切に制御するために、大きなテーブルを分割して挿入することがあります。

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

すべてのレコードが挿入されたあとに、テーブルの名前を変更できます。

ディスク I/O を削減するには、大きなテーブルの変換時に、最大で物理メモリーの 80% まで InnoDB バッファープールのサイズを大きくします。InnoDB ログファイルのサイズを大きくすることもできます。

ストレージ要件

すでに説明したように、この時点で、すでに innodb_file_per_table オプションが有効になっている必要があります。これにより、InnoDB テーブル内にデータの複数のコピーを一時的に作成している場合は、あとで不要なテーブルを削除することで、そのディスク領域をすべてリカバリできます。

MyISAM テーブルを直接変換するのか、クローンの InnoDB テーブルを作成するのかには関係なく、プロセス中に古いテーブルと新しいテーブルの両方を保持するのに十分なディスク領域があることを確認します。InnoDB テーブルには、MyISAM テーブルよりも多くのディスク領域が必要です。ALTER TABLE 操作によって領域が使い果たされると、ロールバックが開始されますが、ディスクバウンドの場合は、数時間かかる可能性があります。挿入の場合、InnoDB はバッチ内のインデックスにセカンダリインデックスレコードをマージする際に、挿入バッファーを使用します。これにより、大量のディスク I/O が節約されます。ロールバックでは、このようなメカニズムは使用されません。ロールバックは挿入よりも、30 倍長い時間がかかる可能性があります。

ランナウェイロールバックの場合は、データベースに貴重なデータがなければ、何百万ものディスク I/O 操作が完了するまで待機するのではなく、データベースプロセスを強制終了することをお勧めします。完全な手順については、セクション14.19.2「InnoDB のリカバリの強制的な実行」を参照してください。

テーブルごとの主キーの慎重な選択

PRIMARY KEY 句は、MySQL クエリーのパフォーマンスや、テーブルおよびインデックス用の領域使用量に影響を与える重要な要素です。おそらく、金融機関に電話をかけ、口座番号を求められた経験があるでしょう。その番号を持っていない場合は、自分自身を「一意に識別する」ために、多種多様な情報が求められます。主キーは、テーブル内の情報を問い合わせたり、変更したりする際に、すぐに仕事に取りかかるための一意の口座番号のようなものです。テーブル内のすべて行が主キー値を持っている必要があり、2 つの行が同じ主キー値を持つことはできません。

次に、主キーに関するいくつかのガイドラインに続き、さらに詳細な説明を示します。

• テーブルごとに PRIMARY KEY を宣言します。一般に、単一の行を検索するときに参照される WHERE 句内のカラムの中で、もっとも重要なものです。

• あとで ALTER TABLE ステートメントを使用して追加するのではなく、元の CREATE TABLE ステートメントで PRIMARY KEY 句を宣言します。

• カラムとそのデータ型は慎重に選択してください。文字または文字列のカラムよりも、数値のカラムを優先してください。

• 別の安定していて、一意で、非 NULL で、数値のカラムが使用できない場合は、自動インクリメントカラムを使用することを検討してください。

• 主キーカラムの値が変更されたかどうかが疑わしい場合にも、自動インクリメントは適切な選択です。主キーカラムの値を変更することは、負荷の高い操作であり、テーブル内および各セカンダリインデックス内でデータの再編成が伴う可能性があります。

主キーがまだ存在しないテーブルには、追加することを検討してください。計画されたテーブルの最大サイズに基づいて、現実的な最小の数値型を使用します。これにより、各行をわずかにコンパクトにすることができ、大きなテーブル用に相当な領域を節約できます。主キー値は、セカンダリインデックスが入力されるたびに繰り返されるため、テーブルが任意のセカンダリインデックスを持っている場合は、領域の節約も倍増します。小さな主キーを使用すると、ディスク上のデータサイズが削減されることに加えて、より多くのデータをバッファープール内に収容できるため、すべての種類の操作の速度が上がり、並列性が改善されます。

すでにテーブルの多少長いカラム (VARCHAR など) 上に主キーが存在する場合は、そのカラムがクエリーで参照されていなくても、新しい符号なし AUTO_INCREMENT カラムを追加し、主キーをそのカラムに切り替えることを検討してください。このような設計の変更によって、セカンダリインデックス内の相当な領域を節約できます。以前の主キーカラムを UNIQUE NOT NULL として指定すると、PRIMARY KEY 句と同じ制約を強制的に適用できます (つまり、これらのすべてのカラムにわたって重複する値や NULL 値を回避できます)。

関連する情報を複数のテーブルに分散させる場合は、一般に各テーブルで、その主キー用に同じカラムが使用されます。たとえば、人事部のデータベースには複数のテーブルが含まれ、各テーブルには従業員番号の主キーが含まれている場合があります。営業部のデータベースには、顧客番号の主キーを含むテーブルや、注文番号の主キーを含むテーブルが含まれている場合があります。主キーを使用した検索は非常に高速であるため、このようなテーブルには効率的な結合クエリーを構築できます。

PRIMARY KEY 句を完全に削除すると、MySQL によって自動的に非表示の主キーが作成されます。これは、必要以上に長くなる可能性のある 6 バイトの値であるため、領域が無駄になります。これは非表示であるため、クエリーで参照できません。

アプリケーションのパフォーマンスに関する考慮事項

InnoDB の追加の信頼性および拡張性機能を使用するには、同等の MyISAM テーブルよりも多くのディスクストレージが必要となります。領域の使用率を改善し、結果セットを処理する際の I/O およびメモリーの消費を削減し、インデックス検索を効率的に使用するクエリーの最適化計画を改善するために、カラムおよびインデックスの定義をわずかに変更することがあります。

主キーに数値の ID カラムを設定する場合 (特に、結合クエリーの場合) は、その値を使用して、その他の任意のテーブル内の関連する値と相互参照します。たとえば、入力として国名を受け入れ、同じ名前を検索するクエリーを実行するのではなく、国 ID を確認するための検索を 1 回実行してから、複数のテーブルにわたって関連情報を検索するための別のクエリー (または 1 回の結合クエリー) を実行します。顧客番号またはカタログ項目番号を数字の文字列として格納すると、数バイトを使い果たす可能性があるため、その代わりに、格納およびクエリー用に数値の ID に変換します。4 バイトの符号なし INT カラムでは、40 億を超える項目 (アメリカ合衆国での billion の意味: 10 億) にインデックスを付けることができます。さまざまな整数型の範囲については、セクション11.2.1「整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT」を参照してください。

InnoDB テーブルに関連付けられたファイルの理解

InnoDB ファイルを使用する際は、MyISAM ファイルよりも多くの注意および計画が必要となります。

• InnoDB のシステムテーブルスペースを表す ibdata ファイルは削除しないでください。

• あるサーバーから別のサーバーに InnoDB テーブルをコピーするには、まず FLUSH TABLES ... FOR EXPORT ステートメントを発行してから、table_name.ibd ファイルとともに table_name.cfg ファイルをコピーする必要があります。