MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.6.1.5 MyISAM から InnoDB へのテーブルの変換

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / MyISAM から InnoDB へのテーブルの変換

このページは機械翻訳したものです。

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

信頼性とスケーラビリティを向上させるために InnoDB に変換する MyISAM テーブルがある場合は、変換する前に次のガイドラインとヒントを確認してください。

注記

以前のバージョンの MySQL で作成されたパーティション化された MyISAM テーブルは、MySQL 8.0 と互換性がありません。このようなテーブルは、パーティション化を削除するか、InnoDB に変換して、アップグレード前に準備する必要があります。詳細は、セクション24.6.2「ストレージエンジンに関連するパーティショニング制限」を参照してください。

MyISAM および InnoDB のメモリー使用量の調整

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

Too-Long または Too-Short トランザクションの処理

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 は自動的に読み取り専用のワークロードを最適化できます。
一連の関連する変更を行う場合は、最後に単一の COMMIT を使用して、すべての変更を一度に確定します。たとえば、情報の関連部分を複数のテーブルに挿入する場合は、すべての変更を行なったあとに、COMMIT を 1 回実行します。また、連続する多数の INSERT ステートメントを実行する場合は、すべてのデータがロードされたあとに、COMMIT を 1 回実行します。何百万もの INSERT ステートメントを実行する場合は、一万または一千レコードごとに COMMIT を発行することで、巨大なトランザクションを分割することがあります。
SELECT ステートメントでもトランザクションが開かれるため、インタラクティブな mysql セッションで一部のレポートを実行したり、クエリーをデバッグしたりしたあとは、COMMIT を発行するか、または mysql セッションを閉じます。

デッドロックの処理

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 の定義

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.1.2「整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT」を参照してください。

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

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

InnoDB のシステムテーブルスペースを表す ibdata ファイルは削除しないでください。
InnoDB テーブルを別のサーバーに移動またはコピーする方法については、セクション15.6.1.4「InnoDB テーブルの移動またはコピー」を参照してください。

PREV HOME UP NEXT