MyISAM テーブルから移行するときに、結果をキャッシュする際に必要でなくなったメモリーが解放されるように、key_buffer_size 構成オプションの値を小さくします。 InnoDB テーブル用のキャッシュメモリー割り当てと同様の役割を担う innodb_buffer_pool_size 構成オプションの値を大きくします。 InnoDB buffer pool では、テーブルデータとインデックスデータの両方がキャッシュされるため、クエリーのルックアップが高速化され、再利用のためにクエリー結果がメモリーに保持されます。 バッファープールサイズの構成に関するガイダンスについては、セクション8.12.3.1「MySQL のメモリーの使用方法」 を参照してください。

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

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

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

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

どちらの方法でも、デッドロックのために強制的に取り消されたトランザクションを再起動するには、アプリケーションにエラー処理ロジックが必要です。 以前と同じ SQL ステートメントを再発行すると、元のタイミングの問題は適用されなくなります。 他のトランザクションがすでに終了して続行できるか、他のトランザクションがまだ進行中で、トランザクションは終了するまで待機します。

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

詳細は、セクション15.7.5「InnoDB のデッドロック」を参照してください。

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

大規模で頻繁にアクセスされる MyISAM テーブルを変換し、重要なデータを保持する場合は、CREATE TABLE ステートメントの innodb_file_per_table および innodb_page_size の構成オプションと ROW_FORMAT and KEY_BLOCK_SIZE clauses を調査して検討します。

初期の実験時に、もっとも重要となる設定は innodb_file_per_table です。 この設定を有効にすると (デフォルト)、新しい InnoDB テーブルが file-per-table テーブルスペースに暗黙的に作成されます。 InnoDB システムテーブルスペースとは対照的に、file-per-table テーブルスペースを使用すると、テーブルの切捨てまたは削除時にオペレーティングシステムでディスク領域を再利用できます。 File-per-table テーブルスペースでは、テーブル圧縮、長い可変長カラムの効率的なオフページストレージ、大規模なインデックス接頭辞など、DYNAMIC および COMPRESSED の行形式および関連する機能もサポートされます。 詳細は、セクション15.6.3.2「File-Per-Table テーブルスペース」を参照してください。

複数のテーブルおよびすべての行形式をサポートする共有一般テーブルスペースに InnoDB テーブルを格納することもできます。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。

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

ALTER TABLE table_name ENGINE=InnoDB;

切り替える前に、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 は change buffer を使用してセカンダリインデックスレコードをバッチとして書き込むことができるため、これによりディスク 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_file_per_table 構成オプションが有効な場合 (デフォルト)、新しく作成された InnoDB テーブルは file-per-table テーブルスペースに暗黙的に作成されます。

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

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

PRIMARY KEY 句は、MySQL クエリーのパフォーマンスや、テーブルおよびインデックス用の領域使用量に影響を与える重要な要素です。 主キーは、テーブル内の行を一意に識別します。 テーブル内のすべて行が主キー値を持っている必要があり、2 つの行が同じ主キー値を持つことはできません。

これらは主キーのガイドラインで、その後に詳細な説明が続きます。

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

すでにテーブルの多少長いカラム (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.1.2「整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT」を参照してください。

InnoDB ファイルには、MyISAM ファイルよりも多くの注意と計画が必要です。