ストレージエンジンを使用する前に、ストレージエンジンプラグイン共用ライブラリを　INSTALL PLUGIN ステートメントを利用してMySQLにロードしなければいけません。例えば、EXAMPLE エンジンプラグインが ha_example と名づけられ、共有ライブラリが ha_example.soと名づけられると、次のステートメントを利用してロードする事になります。

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

共有ライブラリはMySQLサーバプラグインディレクトリの中に無ければいけません。その場所はplugin_dirシステム変数によって指示されます。

ストレージエンジンをアンプラグするには、UNINSTALL PLUGIN ステートメントを利用します。

UNINSTALL PLUGIN ha_example;

もし、テーブルに必要なストレージエンジンをアンプラグすると、それらのテーブルはアクセス不可になりますが、ディスク上には存在し続けます。 (アクセス可能な場所)ストレージエンジンをアンプラグする前に、それらを利用しているテーブルが無い事を確認してください。

プラガブルなストレージエンジンをインストールするには、MySQLプラグインディレクトリの中にプラグインファイルがなければいけません。そして、INSTALL PLUGINステートメントを発行するユーザはmysql.pluginテーブルの為のINSERT権限を持つ必要があります。

静的フォーマットは MyISAM テーブルのデフォルトです。これは、テーブルが可変長カラムを持たない時に使用されます。 (VARCHAR、 VARBINARY、 BLOB、また TEXT)それぞれの行は固定バイト数を利用して格納されます。

3つの MyISAM ストレージフォーマットの中で、静的フォーマットが一番シンプルで安全です。(一番破損しにくい)これはまた、データファイル中の行がディスク上で見つけられるという簡単さゆえ、オンディスクフォーマットの中でも一番早いです。インデックスの中で行数に基づいて行の位置を探すには、行数に行長をかけてください。また、テーブルをスキャンする時には、それぞれのディスクの読み込み操作を使って簡単に行の定数を読む事ができます。

もしMySQLサーバーが固定フォーマットのMyISAMファイルに書き込んでいる最中にコンピュータがクラッシュしたら、そのセキュリティが証明されます。この場合、myisamchk はそれぞれの行がどこで始まりどこで終わるかを簡単に測定する事ができますので、部分的に書かれた物以外の全ての行を大概再生する事ができます。MyISAM テーブルインデックスは、データ行に基づいていつでも再配列できるという事を覚えておいてください。

静的フォーマットにはこれらの特徴があります。

もし MyISAM テーブルが可変長カラムを含んでいる場合(VARCHAR、 VARBINARY、 BLOB、または TEXT)、または ROW_FORMAT=DYNAMIC テーブルオプションで作成された場合は、動的ストレージフォーマットが使用されます。

動的フォーマットは、それぞれの行にその長さを示すヘッダーがあるので、静的フォーマットよりも少しだけ複雑です。更新の結果行が長くなった時には、フラグメント化する事ができます。(非連続単位での格納)

テーブルをデフラグメント化する為に OPTIMIZE TABLE か myisamchk -r を使用する事ができます。頻繁にアクセスや変更がある固定長カラムが、可変長カラムも含むテーブルの中にあるなら、フラグメント化を防ぐ為に、可変長カラムを他のテーブルに移動するとよいでしょう。

動的フォーマットにはこれらの特徴があります。

圧縮ストレージフォーマットはmyisampack ツールによって生成される読み取り専用のフォーマットです。圧縮テーブルは myisamchkを使って解凍する事ができます。

圧縮テーブルには次のような特徴があります。

注:? 圧縮テーブルは読み取り専用なので、テーブルを更新したり、行を追加したりはできません。DDL (データ定義言語) 操作は有効です。例えば、テーブルをドロップする為に DROP を利用したり、空にする為に TRUNCATE を利用する事もできます。

MyISAM は信頼性の高いテーブル形式ですが、(テーブルに対するすべての変更は SQL ステートメントから制御が戻る前に書き込まれます)それでも以下の状況が発生した場合はテーブルが破損するおそれがあります。

テーブルが破損すると通常次のような現象が見られます。

MyISAM テーブルが破損していないかどうか CHECK TABLE ステートメントを利用して確認する事ができます。また、REPAIR TABLEを利用して破損した MyISAM テーブルを修復する事ができます。また、mysqld が稼動していない時はmyisamchk コマンドを利用して確認や修復ができます。項12.5.2.3. 「CHECK TABLE 構文」、項12.5.2.6. 「REPAIR TABLE 構文」、項7.4. 「myisamchk ? MyISAM テーブル メンテナンス ユーティリティ」を参照して下さい。

もしテーブルが頻繁に破損する場合は、その原因を突き止める必要があります。最も重要なのは、サーバーのクラッシュによってテーブルが破損されたのかどうかを確認する事です。最近のエラーログの中の restarted mysqld メッセージを探せば簡単に検証する事ができます。もしそのようなメッセージがあれば、破損の原因はサーバーの破損によるものである可能性が高いでしょう。そうでなければ、破損は通常作業の最中に起きたという事になるでしょう。その場合はバグです。ですので、その破損のテストケースを作成してみる必要があります。項B.1.4.2. 「What to Do If MySQL Keeps Crashing」、Making a Test Case If You Experience Table Corruption を参照して下さい。

MySQL Enterprise 問題が発生する前にそれを発見します。サーバーの状態についての専門家のアドバイスを受ける為に、MySQL ネットワークモニタリングとアドバイザーサービスを購読してください。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

各 MyISAM インデックスファイル (.MYI ファイル)には テーブルが適切に閉じられているかをチェックする為のカウンタがあります。もし CHECK TABLE か myisamchkから次のような警告が表示されたら、このカウンタは同期されていない事を表します。

clients are using or haven't closed the table properly

この警告は、テーブルが破損されたという意味ではありませんが、少なくともテーブルを確認したほうがよいでしょう。

カウンターは次のように機能します。

つまり、カウンタがずれる可能性があるのは次のような場合です。

各 InnoDB テーブルとそのインデックスをそれ自体のファイル内に格納する事ができます。この特徴は、実際に各テーブルがそのテーブルスペースを持つ為 「multiple tablespaces」 と呼ばれています。

複数のテーブルスペースを利用する事は、特定のテーブルを別々の物理ディスクに移動したり、単一テーブルのバックアップを残りの InnoDB テーブルの利用を邪魔する事なく、素早く復元したいユーザにとって、有益な物です。

このラインを my.cnf の [mysqld] セクションに追加する事で、複数のテーブルスペースを有効にする事ができます:

[mysqld]
innodb_file_per_table

サーバを再起動した後、InnoDB はテーブルが属するデータベース ディレクトリ内にある、それ自体のファイル tbl_name.ibd 内に、それぞれの新しく作成されたテーブルを格納します。 これは MyISAM ストレージ エンジンが行う事と似ていますが、MyISAM はテーブルをデータ ファイル tbl_name.MYD と インデックス ファイル tbl_name.MYI に分割します。InnoDB には、データとインデックスは .ibd ファイル内で一緒に格納されます。tbl_name.frm ファイルはまだ通常通り作成されます。

もし innodb_file_per_table ラインを my.cnf から削除してサーバを再起動すると、InnoDB は共有テーブルスペース ファイル内にテーブルを再度作成します。

innodb_file_per_table はテーブル作成だけに影響を与え、既存テーブルにアクセスはしません。もしこのオプションを利用してサーバを起動すると、新しいテーブルは .ibd ファイルを利用して作成されますが、共有テーブルスペース内に存在するテーブルにアクセスする事もまだ可能です。もしオプションを削除してサーバを再起動すると、新しいテーブルは共有テーブルスペース内に作成されますが、複数のテーブルスペースを利用して作成されたテーブルにもまだアクセスする事ができます。

注意:InnoDB は、共有テーブルスペースに内部データ ディレクトリと取り消しログを置くので、いつもそれを必要とします。.ibd ファイルは InnoDB の作動に充分ではありません。

注意:MyISAM テーブル ファイルで行えるのと同じように、データベース ディレクトリ間で .ibd ファイルを自由に移動させる事はできません。これは、InnoDB 共有テーブルスペース内に格納されているテーブル定義がデータベース名を含み、そして InnoDB がトランザクション ID とログ シーケンス番号の一貫性を保持しなければいけない為です。

1つのデータベースから別のデータベースに .ibd ファイルとその関連テーブルを移動するには、RENAME TABLE ステートメントを利用してください:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

もし .ibd の 「空の」 バックアップを持っていれば、それを次のように、それが発生した場所から MySQL インストールに格納する事ができます:

このコンテキスト内で 「空の」 .ibd ファイル バックアップが意味する物は:

次の方法を利用して、空のバックアップ .ibd ファイルを作る事ができます:

.ibd ファイルの空のコピーを作成する別の方法は、商業 InnoDB Hot Backup ツールを利用する事です:

共有テーブルスペース内で未加工ディスク パーティションをデータ ファイルとして利用できます。未加工ディスクを利用する事で、Windows といくつかの Unix システム上でファイル システムの負荷が無い非バッファ I/O を実行する事ができ、それで操作性能が向上します。

新しいデータ ファイルを作成する時、innodb_data_file_path 内のデータ ファイル サイズの直後にキーワード newraw を置く必要があります。パーティションは、少なくても指定したサイズと同じである必要があります。ディスク仕様の 1MB は通常1,000,000バイトを意味するのに対して、InnoDB 内の1MB は1024 × 1024 バイトである事に注意してください。

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

次にサーバを起動する時、InnoDB は newraw キーワードに気付き、新しいパーティションを開始します。しかし、まだ InnoDB テーブルを作成したり変更したりしないでください。そうでなければ、サーバを次に再起動した時 InnoDB がパーティションを再開始し、変更が全て失われます。(安全策として、InnoDB は newraw を持つパーティションが指定された時ユーザがデータを更新する事を防ぎます。)

InnoDB が新しいパーティションを開始したら、サーバを停止し、データ ファイル仕様の中の newraw を raw に変更してください:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

そしてサーバを再起動させると、InnoDB は変更を許可します。

Windows 上では、次のようにディスク パーティションを割り当てる事ができます:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw

//./ は物理的ドライブにアクセスする為の Windows の構文 \\.\ に対応しています。

未加工ディスク パーティションを利用する時、MySQL サーバを起動するのに利用されるアカウントによって読み込み書き込みアクセスが許可されている事を確認してください。

もしファイル操作の最中に InnoDB が OS エラーをプリントすると、通常はその問題は次のうちどれかになります:

InnoDB がそのテーブルスペースかログ ファイルを初期化しようとした時に何かが失敗すると、InnoDB によって作成されたファイル全てを削除しなければいけません。これは、全ての ibdata ファイルと全ての ib_logfile ファイルの事です。既にいくつかの InnoDB テーブルを作成していた場合、MySQL データベース ディレクトリからも、それらのテーブルの対応する .frm ファイル (もし複数のテーブルスペースを利用していたら全ての .ibd ファイルも)を削除してください。そして、InnoDB データベースの作成にもう一度挑戦できます。何が起きているのか確認できるように、MySQL サーバをコマンド プロンプトから起動するのが一番良いです。

デフォルトによって、MySQL サーバに接続する各クライアントが、実行すると全ての SQL ステートメントを自動的にコミットするオート コミットが有効な状態で開始します。複数ステートメント トランザクションを利用する為に、SQL ステートメント SET AUTOCOMMIT = 0 を利用してオートコミットをオフにし、トランザクションをコミットまたはロールバックする為に COMMIT と ROLLBACK を利用する事ができます。オートコミットをオンの状態のままにしておきたければ、トランザクションを START TRANSACTION と、COMMIT か ROLLBACK のどちらかで囲む事ができます。次の例は2つのトランザクションを表しています。最初の物はコミットされ、2つ目の物はロールバックされています。

shell> mysql test

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

PHP、Perl DBI、JDBC、ODBC、または MySQL のスタンダード C 呼び出しインターフェースのような API 内では、COMMIT のようなトランザクション コントロール ステートメントを SELECT や INSERT のような別の SQL ステートメントのような文字列として、MySQL サーバに送る事ができます。いくつかの API は、別々の特別トランザクション コミットやロールバック機能または方法も提供します。

重要:mysql データベース(user や host のような)内の MySQL システム テーブルを InnoDB タイプに変換しないでください。これはサポートされていない操作です。システム テーブルは必ず MyISAM タイプの物でなければいけません。

もし全ての(非システムの)テーブルを InnoDB テーブルとして作成したければ、サーバ オプション ファイルの [mysqld] セクションにライン default-storage-engine=innodb を追加するだけでよいです。

InnoDB は、MyISAM ストレージ エンジンがするのと同じように、インデックスを別々に作成する為の特別な最適化を行いません。従って、テーブルをエクスポート、インポートしたり、後でインデックスを作成したりはしません。テーブルを InnoDB に変換する一番早い方法は、InnoDB テーブルに直接挿入する事です。それは、ALTER TABLE ... ENGINE=INNODB を利用する、または同一定義を利用して空の InnoDB テーブルを作成し、INSERT INTO ... SELECT * FROM ... を利用して行を挿入するという事です。

もし2番目のキー上に UNIQUE 制限があったら、インポート操作の最中に一時的に一意性チェックを切り、テーブル インポートのスピードを上げる事ができます:

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

大きいテーブルに対しては、InnoDB が2番目のインデックス レコードをバッチとして書く為にそれ自身の挿入バッファを利用する事ができるので、この作業をするとディスク I/O を大幅に節約する事ができます。データが複製キーを含んでいない事を必ず確認してください。UNIQUE_CHECKS はストレージエンジンが複製キーを無視する事を許可しますが、それを要求はしません。

挿入の操作性をあげる為には、大きいテーブルを細かく分けて挿入するのが良いでしょう:

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

全てのレコードが挿入された後で、テーブルをリネームする事ができます。

大きいテーブルの変換の最中に、ディスク I/O を減らす為に InnoDB バッファ プールのサイズを増やす必要があります。しかし、物質的メモリの80% 以上は利用しないでください。InnoDB ログ ファイルのサイズを増やす事もできます。

テーブルスペースを一杯にしないように注意してください:InnoDB テーブルは MyISAM テーブルよりも多くのディスク領域を必要とします。もし ALTER TABLE 操作で領域を使い切ってしまうと、それはロールバックを始め、それがディスクに頼っている場合何時間も時間がかかります。挿入には、InnoDB はバッチ内のインデックスに2つ目のインデックス レコードをマージする為に挿入バッファを利用します。それでディスク I/O を大幅に節約する事ができます。ロールバックにはそのような構造は利用されず、挿入の30倍の時間がかかります。

ロールバックが暴走した場合は、データベースに貴重なデータがなければ、膨大なディスク I/O の完了を待つよりも、データベース プロセスを強制終了したほうが良いでしょう完全な手順に関しては、項13.5.8.1. 「InnoDB 復旧の強制」 を参照してください。

もし InnoDB テーブルに AUTO_INCREMENT カラムを指定すると、InnoDB データ ディレクトリ内のテーブル ハンドルは、カラムに新しい値を割り当てるのに利用される自動インクリメント カウンタと呼ばれる特別なカウンタを含みます。このカウンタは、ディスク上には格納されず、主メモリ内だけに格納されます。

InnoDB は、ai_col を名づけた AUTO_INCREMENT カラムを含むテーブル T に自動インクリメント カウンタを初期化する為に、次のアルゴリズムを利用します:サーバの起動の後で、テーブル T への最初の挿入をする為に、InnoDB はこのステートメントと同等な物を実行します:

SELECT MAX(ai_col) FROM T FOR UPDATE;

InnoDB はステートメントによって値が取り出された物によってインクリメントし、それをカラムとテーブルの自動インクリメント カウンタに割り当てます。もしテーブルが空だったら、InnoDB は値 1 を利用します。もしユーザがテーブル T の為のアウトプットを表示する SHOW TABLE STATUS ステートメントを呼び出し、自動インクリメント カウンタがまだ初期化されていなかったら、InnoDB は値を初期化するがインクリメントはせず、そしてそれを後で挿入に利用する為に格納します。この初期化はテーブル上で通常の専用ロック読み込みを利用し、そのロックはトランザクションの最後まで続くという事に注意してください。

InnoDB は、作成されたばかりのテーブルの為に自動インクリメント カウンタを初期化するのと同じ手順に従います。

自動インクリメント カウンタが初期化された後、もしユーザが AUTO_INCREMENT カラムの値を明示的に指定しなければ、InnoDB はカウンタを1でインクリメントしカラムに新しい値を割り当てます。もしユーザがカラム値を明示的に指定する行を挿入し、それが現在のカウンタ値よりも大きければ、カウンタは指定されたカラムに設定されます。

もしカウンタを利用して生成された数値を持つトランザクションをロールバックすると、AUTO_INCREMENT に割り当てられた値のシーケンス内のギャップに気がつくでしょう。

もしユーザが NULL か 0 を INSERT 内の AUTO_INCREMENT カラムに指定すると、InnoDB は、値が指定されず、新しい値も生成されていないかのように行を扱います。

自動インクリメント構造の性能は、もしユーザがカラムにマイナスの値を割り当てたり、もし値が指定した整数タイプ内に格納する事ができる最大値を上回っていたりすると、定義できません。

自動インクリメント カウンタにアクセスする時、InnoDB は、トランザクションの最後までではなく、現在の SQL ステートメントの最後まで続く、特別なテーブル レベル AUTO-INC ロックを利用します。AUTO_INCREMENT カラムを含んでいるテーブルへの挿入の並行処理を向上させる為に、特別ロック リリース戦略が紹介されました。それにもかかわらず、2つのトランザクションは AUTO-INC ロックが長時間保持されればパフォーマンス インパクトを与える事ができる AUTO-INC ロックを同じテーブル上で同時に持つ事ができません。 これは、1つのテーブルから全ての行を別のテーブルに挿入する INSERT INTO t1 ... SELECT ... FROM t2 のようなステートメントのような場合の事です。

InnoDB はサーバが起動している限り、メモリ内の自動インクリメント カウンタを利用します。サーバが停止し再起動した時、先ほど説明があったように、InnoDB は、テーブルへの最初の INSERT に対する各テーブルのカウンタを再初期化します。

InnoDB は、初期カウンタ値を設定したり、現在のカウンタ値を変更する為に、CREATE TABLE と ALTER TABLE ステートメント内の AUTO_INCREMENT = N テーブル オプションをサポートします。このセクションの最初の方で説明があったとおり、このオプションの効果はサーバの再起動によって無くなってしまいます。

InnoDB は 外部キー制約もまたサポートします。InnoDB 内の外部キー制約定義の構文は次のようになります:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

外部キー定義には次のような条件があります:

もし親テーブル内に適合する候補キー値が無ければ、InnoDB は子テーブル内に外部キー値を作成しようとする INSERT か UPDATE 操作を拒絶します。子テーブル内にいくつかの適合する行を持つ親テーブル内で、候補キー値を更新または削除しようとする UPDATE や DELETE 操作に対して InnoDB が取るアクションは、FOREIGN KEY 条項の　ON UPDATE と ON DELETE サブ条項を利用して指定された referential action 上で依存しています。ユーザが親テーブルから行を削除または更新しようとして、子テーブル内に1つ以上の適合する行がある時、InnoDB は取るべきアクションを考慮して5つのオプションをサポートします:

InnoDB がテーブル内で外部キー制約をサポートする事に注意してください。これらのような場合、「子テーブル レコード」 は本当に同じテーブル内で依存レコードを参照します。

InnoDB は、外部キー チェックが速くなり、テーブル スキャンを必要としないよう、外部キーと参照キー上にインデックスを要求します。外部キー上のインデックスは自動的に作成されます。これは、いくつかの古いバージョン内での、インデックスが明示的に作成される必要があり、そうでなければ外部キー制約の作成が失敗する、という物とは対照的です。

タイプ変換をせずに比較できるよう、外部キーと参照キー内の対応するカラムは InnoDB 内に類似内部データ タイプを持つ必要があります。整数タイプのサイズとサインは同じである必要があります。文字列タイプの長さは同じである必要はありません。もし SET NULL アクションを指定したら、 子テーブル内のカラムを NOT NULL として宣言していない事を確認してください。

もし MySQL が CREATE TABLEステートメントからエラー番号1005を報告し、そのエラーメッセージがエラー150を参照していたら、外部キー制約が正しく形作られていない為にテーブル作成は失敗します。 同じように、もし ALTER TABLE が失敗し、それがエラー150を参照していたら、それは変更したテーブルに対して外部キー制約が間違って形作られるという意味になります。 サーバ内に一番新しい InnoDB 外部キー エラーの詳細説明を表示する為に SHOW ENGINE INNODB STATUS を利用する事ができます。

注意:InnoDB は NULL カラムを含む外部キーや参照キー上で外部キー制約を確認しません。

注意:トリガは現在、転送された外部キー アクションによって有効化されません｡

内部 InnoDB カラムの名前と一致するカラム名を持つテーブルを作成する事はできません。(DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR そして DB_MIX_ID を含む)MySQL 5.1.10以前のバージョン内ではこれはクラッシュの原因となり、5.1.10からはサーバがエラー1005を報告し、エラーメッセージ内で errno -1 を参照します。

SQL スタンダードからの逸脱:InnoDB は同じ参照キー値を持つ親テーブル内にいくつかの行があると、外部キーチェック内で同じキー値を持つ別の親行がまるで存在しないかのように機能します。例えば、もし RESTRICT タイプ制約を定義し、いくつかの親行を持つ子行があれば、InnoDB はそれらの親行の削除を許可しません。

InnoDB は、外部キー制約に対応するインデックス内のレコードに基づいた、縦型アルゴリズムを通して転送操作を行います。

SQL スタンダードからの逸脱:非 UNIQUE キーを参照する FOREIGN KEY 制約はスタンダード SQL ではありません。それはスタンダード SQL への InnoDB 拡張子です。

SQL スタンダードからの逸脱:もし ON UPDATE CASCADE か ON UPDATE SET NULL が転送の最中に既に更新された 同じテーブル の更新を反復すると、それは RESTRICT のように機能します。これは、自己参照型 ON UPDATE CASCADE か ON UPDATE SET NULL 操作を利用する事ができないという意味です。これは転送更新の結果に起きる無限ループを防ぐ為の物です。反対に、自己参照型 ON DELETE SET NULL は、自己参照型 ON DELETE CASCADE と同様可能です。転送操作は15レベルより深くネスト化される事はないでしょう。

SQL スタンダードからの逸脱:通常の MySQL のように、挿入、削除、または多くの行の更新を行う SQL ステートメント内では、InnoDB は UNIQUE と FOREIGN KEY 制約を行ごとに行います。SQL スタンダードによると、デフォルト動作は据え置きチェックでなければいけません。それは、SQL ステートメント全体 が処理された後に制約の確認だけが行われるいう事です。InnoDB が据え置き制約チェックを実装するまでは、外部キーを通してそれ自身を参照するレコードを削除するというような、いくつかの操作を行う事が不可能になります。

ここに、単一カラム外部キーを通して parent と child テーブルを関連させるシンプルな例があります:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

product_order テーブルが別の2つのテーブルに外部キーを持つ、さらに複雑な例。1つの外部キーが product テーブル内の2段インデックスに参照をつけます。その他の物は customer テーブル内で単一カラム インデックスに参照をつけます:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;

InnoDB は ALTER TABLE を利用してテーブルに新しい外部キー制約を追加する事を許容します:

ALTER TABLE tbl_name
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

要求されたインデックスを最初に作成する事を忘れないでください。.ALTER TABLE を利用して、自己参照型外部キー制約をテーブルに追加する事もできます。

InnoDB は外部キーをドロップする為の ALTER TABLE の利用もサポートします。

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

もし外部キーを作成した時に FOREIGN KEY 条項が CONSTRAINT 名を含んでいたら、外部キーをドロップする為にその名前を参照する事ができます。そうでなければ、fk_symbol 値は外部キーが作成された時に InnoDB によって内部的に生成されます。 外部キーをドロップしたい時にシンボル値を見つけるには、SHOW CREATE TABLE ステートメントを利用してください。例:

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

単一 ALTER TABLE ステートメントの別々の条項の中に外部キーを追加したりドロップしたりはできません。別々のステートメントが要求されます。

InnoDB パーサは、FOREIGN KEY ... REFERENCES ... 条項内のテーブルとカラム識別子がバックフォート内で参照される事を許容します。(あるいは、もし ANSI_QUOTES SQL モードが有効であれば二重引用符を利用する事もできます。)InnoDB パーサは、lower_case_table_names システム変数の設定も考慮します。

InnoDB はテーブルの外部キー定義を SHOW CREATE TABLE ステートメントのアウトプットの一部として返します:

SHOW CREATE TABLE tbl_name;

mysqldump はダンプ ファイルのテーブルの正しい定義も作成し、外部キーの事も忘れません。

次のようにテーブルの外部キー制約を表示する事もできます:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

外部キー制約はアウトプットの Comment カラム内にリストされています。

外部キー チェックを行っている時、InnoDB はそれが見なければいけない子または親レコード上に共有行レベル ロックを設定します。 InnoDB は直ちに外部キー制約を確認します。その確認はトランザクション コミットに据え置きされません。

外部キー関係を持つテーブルのダンプ ファイルの再ロードを簡単にする為に、mysqldump は FOREIGN_KEY_CHECKS を0に設定する為に自動的にダンプ アウトプット内にステートメントを含みます。これは、ダンプが再ロードされた時にテーブルが特定の順番で再ロードされなければいけないという問題を防ぎます。この変数をマニュアルで設定する事も可能です:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

これは、もしダンプ ファイルが外部キーに対して正しい順番でオーダされていないテーブルを含んでいたら、テーブルをどんな順番でインポートしてもよいと許容します。これはインポート操作のスピードも上げます。FOREIGN_KEY_CHECKS を0に設定する事は、LOAD DATA と ALTER TABLE 操作の最中に外部キー制約を無視する為にも役に立ちます。しかし、FOREIGN_KEY_CHECKS=0 であったとしても、InnoDB は、カラムが非適合カラム タイプの参照をつける外部キー制約の作成を許容しません。

InnoDB は、SET FOREIGN_KEY_CHECKS=0 を行わない限り、FOREIGN KEY 制約によって参照を付けられたテーブルをドロップする事を許容しません。テーブルをドロップする時、その作成ステートメント内で定義された制約もまたドロップされます。

それは、もしドロップされたテーブルを再作成すると、それに参照をつける外部キー制約と同一の定義を持つはずです。それは右側のカラム名とタイプを持ち、先に述べたように参照キー上にインデックスを持つはずです。もしそれらが満たされなければ、MySQL はエラー番号1005を返し、エラー メッセージ内で errno 150を参照します。

MySQL 複製は、MyISAM に対して機能するのと同じように InnoDB テーブルに機能します。スレーブ上のストレージ エンジンがマスタ上の元のストレージ エンジンと同じではない場合での方法で複製を利用する事も可能です。例えば、スレーブ上の MyISAM テーブルに、マスタ上の InnoDB テーブルへの修正を複製する事ができます。

マスタに新しいスレーブを設定するには、InnoDB テーブルの .frm ファイルと同様に、InnoDB テーブル スペースのコピーとログ ファイルのコピーを作成し、そのコピーをスレーブに移動させなければいけません。もし innodb_file_per_table 変数が有効であれば、.ibd ファイルもコピーする必要があります。これを行う為の正しい手順については 項13.5.8. 「InnoDB データベースのバックアップと復旧」 を参照して下さい。

もしマスタか既存スレーブを閉じる事ができるのであれば、InnoDB テーブル スペースとログ ファイルの完全なバックアップを取り、それをスレーブの設定の為に利用する事ができます。サーバを停止させずに新しいスレーブを作成するには、非フリー(商業用) InnoDB Hot Backup ツール を利用する事もできます。

MyISAM テーブルに対してだけ機能する LOAD TABLE FROM MASTER ステートメントを利用して InnoDB に複製を設定する事はできません。2つ可能な回避方法があります:

マスタ上で失敗するトランザクションは複製に全く影響を与えません。MySQL 複製は、MySQL がデータを変更する SQL ステートメントを書き込むバイナリ ログに基づいています。失敗するトランザクション(例えば、外部キー違反の為、またはロールバックされる為)はバイナリ ログに書き込まないので、スレーブに送られません。 詳しくは 項12.4.1. 「START TRANSACTION、COMMIT、そして ROLLBACK 構文」 を参照してください。

もしデータベース ページが破損したら、SELECT INTO OUTFILE を利用してデータベースからテーブルをダンプしたいかもしれません。通常、この方法で取得されたデータは無傷です。そうだとしても、破損によって SELECT * FROM tbl_name ステートメントや InnoDB バックグラウンド操作がクラッシュしたりアサートしたり、または InnoDB 前進復旧がクラッシュしたり、という事が起こります。 しかし、バックグラウンドの操作を防いでいる間に、テーブルをダンプする事ができるように InnoDB ストレージ エンジンの起動を強制する事ができます。例えば、サーバを再起動する前に、オプション ファイルの [mysqld] セクションに次のラインを追加する事ができます:

[mysqld]
innodb_force_recovery = 4

innodb_force_recovery のゼロではない許容値が続きます。大きい数字は小さい数字の全ての予防策を含んでいます。もし最大4のオプション値を利用してテーブルをダンプする事ができれば、破損した独立ページ上のいくつかのデータが失われるだけなので、比較的に安全です。データベース ページは既に廃止された状態で残されるので、6の値はさらに徹底的であり、Bツリーやその他のデータベース構造に更なる破損を引き起こす可能性があります。

それらをダンプする為にテーブルから SELECT する事ができ、または強制復旧が利用されたとしてもテーブルを DROP か CREATE する事ができます。もし与えられたテーブルがロールバック上でクラッシュを引き起こしていると知ったら、それをドロップする事ができます。大量の失敗インポートや ALTER TABLE によって引き起こされた暴走ロールバックを停止する為にもこれを利用する事ができます。ロールバックせずにデータベースを立ち上げる為に mysqld 処理を停止し、innodb_force_recovery を 3 に設定し、そして暴走ロールバックを引き起こしているテーブルを DROP する事ができます。

データベースはそれ以外の場合にゼロ以外の値の innodb_force_recovery と共に利用するべきではありません 。innodb_force_recovery が0よりも大きい場合、安全の為、InnoDB はユーザがINSERT、UPDATE、または DELETE 操作を行うのを防ぎます。

InnoDB は 「fuzzy」 チェックポイントとして知られているチェックポイント性能を実装します。InnoDB は小さいバッチ内のバッファ プールから変更されたデータベース ページをフラッシュします。チェックポイント処理の最中にユーザ SQL ステートメントの処理を実際に停止させる、バッファ プールを単一バッチ内でフラッシュする必要はありません。

クラッシュ復旧の最中に、InnoDB はログ ファイルに書き込まれたチェックポイント ラベルを探します。それは、ラベルの前のデータベースへの全ての変更がデータベースのディスク イメージ内に存在する事を知っています。そして、InnoDB はデータベースにログされた変更を適用しながら、チェックポイントから前方にログ ファイルをスキャンします。

InnoDB は交代制でそのログファイルに書き込みをします。バッファ プール内のデータベース ページがディスク上のイメージと異なるようにコミットされた全ての変更は、InnoDB が復旧を行わなければいけない場合の為にログ ファイル内で有効である必要があります。これは、InnoDB がログ ファイルを再利用し始めた時、ディスク上のデータベースのイメージが、InnoDB が再利用しようとしているログ ファイル内にログされた変更を確実に含むという事を意味します。言い換えると、InnoDB はチェックポイントを作成する必要があり、変更されたデータベース ページをディスクにフラッシュする事を含んでいる事が多いです。

前出の説明の中で、なぜログ ファイルをとても大きくする事がチェックポイントの中でディスク I/O を救うかも知れないのかが説明されています。ログ ファイル全体の大きさをバッファ プールと同じ、またはそれよりも大きく設定する事は意味を持つ事が多いです。大きいログ ファイルを利用する事の欠点は、データベースに適応させるログされた情報がより多くある為に、クラッシュ復旧に長時間かかるという事です。

InnoDB は2つのタイプのロックがあるスタンダード行レベル ロックを実装します:

もしトランザクション T1 が共有(S)ロックをタプル t 上で保持していたら、

もしトランザクション T1 が独特な(X)ロックをタプル t 上で保持していたら、その時は t 上のロックに対する独特なトランザクション T2 からのリクエストは直ちに認められません。代わりに、トランザクション T2 はトランザクション T1 がタプル t 上でそのロックをリリースするのを待たなければいけません。

さらに、InnoDB はレコード ロックの共存とテーブル全体のロックを許容する 複数粒度ロック をサポートします。複数粒度レベルでのロックを実用的にする為に、インテンション ロック と呼ばれるロックの追加タイプが利用されます。インテンション ロックは InnoDB 内のテーブル ロックです。インテンション ロックの裏にある考えは、後でトランザクションが、そのテーブル内でどのタイプ(共有か専用か)のロックを行の為に要求するのかを指示するという事です。InnoDB 内で利用されるインテンション ロックには2つのタイプがあります。(トランザクション T がテーブル R 上で指示されたタイプのロックをリクエストしたと仮定してください。):

インテンション ロック プロトコルは次のようになります:

これらのルールは ロック タイプ変換互換性マトリックス を用いて便利に要約する事ができます:

既存ロックと互換性があれば、リクエストしているトランザクションにロックが与えられます。既存ロックと対立すれば、リクエストしているトランザクションにロックは与えられません。トランザクションは、既存の対立中のロックがリリースされるまで待ちます。もしロックのリクエストが既存ロックと対立する為にデッドロックが起り、そのロックが与えられないとしたら、エラーが発生します。

従って、インテンション ロックはフル テーブル リクエスト以外は何もブロックしません。(例えば LOCK TABLES ... WRITE)IX と IS ロックの主な目的は、誰かが行をロックしている、またはテーブル内の行をロックしようとしている、という事です。

次の例は、ロック リクエストがデッドロックを引き起こす時にどのようにエラーが発生するかを表しています。この例には、A と B の2つのクライアントが登場します。

最初に、クライアント A が行を1つ含むテーブルを作成し、トランザクションを開始します。トランザクション内で、A は共有モードで選択した行に S ロックを獲得します:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.10 sec)

次に、クライアント B がトランザクションを開始し、テーブルから行を削除しようとします:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

削除作業は X ロックを必要とします。クライアント A が保持している S ロックと互換性が無い為にそのロックは与えられず、そのリクエストは行とクライアント B のロック リクエストの列に並びます。

最後に、クライアント A もテーブルから行を削除しようとします:

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

クライアント A は行を削除する為に X ロックが必要なので、ここでデッドロックが発生します。しかし、クライアント B が既に X ロックへのリクエストを持ち、またその S ロックをクライアント A がリリースするのを待っている為に、ロック リクエストは与えられません。B による X ロックのリクエストの為に、A によって保持されている S ロックをX ロックにアップグレードするる事もできません。結果として、InnoDB はクライアント A に対してエラーを生成し、そのロックをリリースします。その時点で、クライアント B のロック リクエストが与えられ、B はテーブルから行を削除します。

InnoDB 内では、全てのユーザ行動はトランザクション内で起こります。もし自動コミット モードが有効なら、各 SQL ステートメントはそれ自体の上に単一トランザクションを形作ります。MySQL はデフォルトで自動コミットを有効にして新しい接続を始めます。

もし自動コミット モードが SET AUTOCOMMIT = 0 を利用してオフにされたら、ユーザは常にトランザクションを開いていると考える事ができます。SQL COMMIT か ROLLBACK ステートメントは現在のトランザクションを終了し新しい物を開始します。COMMIT は、現在のトランザクション内で行われた変更は永続的であり、別のユーザからも見る事ができるという事を意味します。ROLLBACK ステートメントは反対に、現在のトランザクションによって行われた全ての変更をキャンセルします。両ステートメントは、現在のトランザクションの最中に設定された全ての InnoDB ロックをリリースします。

もしその接続の自動コミットが有効であれば、ユーザは明示的な START TRANSACTION か BEGIN ステートメントを利用して複合ステートメント トランザクションを開始する事でそれを実行し、また COMMIT か ROLLBACK を利用して終了する事ができます。

SQL:1992 トランザクション分離レベルの観点では、InnoDB デフォルトは REPEATABLE READ です。InnoDB は SQL スタンダードによって説明されている4つの全てのトランザクション分離レベルを提供します。コマンド ライン上、またはオプション ファイル内で --transaction-isolation オプションを利用する事で、全ての接続にデフォルトの分離レベルを設定する事ができます。例えば、オプション ファイルの [mysqld] セクション内で次のようにオプションを設定する事ができます:

[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

ユーザは SET TRANSACTION ステートメントを利用して単一セッションや全ての新しい接続の分離レベルを変更する事ができます。その構文は次のようになります:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

--transaction-isolation オプションのレベル名内にはハイフンがありますが、SET TRANSACTION ステートメントには無い事に注意してください。

デフォルト動作は、次の（まだ始まっていない）トランザクションの分離レベルを設定する事です。もし GLOBAL キーワードを利用すると、そのステートメントはその時点以降に作成された全ての新しい接続に対して、デフォルトのトランザクション レベルをグローバルに設定します。(既に存在する接続には設定しません。)これには SUPER 権限が必要です。SESSION キーワードの利用は、現在の接続上で今後行われる全てのトランザクションに対して、デフォルト トランザクション レベルを設定します。

全てのクライアントは自由にセッションの分離レベル(トランザクションの最中だとしても)、または次のトランザクションの分離レベルを変更する事ができます。

次のステートメントを利用して tx_isolation システム変数の値を確認する事で、グローバル、またセッション トランザクションの分離レベルを決定する事ができます:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

行レベル ロック内では、InnoDB はネクスト キー ロックを利用します。これは、インデックス レコード以外に、InnoDB が、別のユーザによってインデックス レコードの直前に挿入される事を防ぐ為に、インデックス レコードに先行する 「ギャップ」 をロックする事もできるという事を意味します。ネクスト キー ロックは、インデックス レコードとその前のギャップをロックするロックを参照します。ギャップ ロックは、ただ単に、いくつかのインデックス レコードの前にギャップをロックするだけのロックを参照します。

InnoDB 内の各分離レベルに関する詳細説明は次の物です:

一貫した読み取りとは、InnoDBがそのマルチ バージョニング機能を使用して、ある時点でのデータベースのスナップショットをクエリに提示する事を意味します。クエリには、その時点より前にコミットされたトランザクションによる変更のみが示され、その時点より後のトランザクションまたはコミットされていないトランザクションによる変更は示されません。例外として、クエリを発行したトランザクション自体による変更はクエリに示されます。 このルールに対する例外によって次のような事が起こると覚えておいてください:テーブル内のいくつかの行を更新すると、SELECT は他の行の古いバージョンを確認すると同時に、更新された行の最新のバージョンを確認します。もし別のユーザが同時に同じテーブルを更新すると、今までとは違う状態のテーブルをデータベース内で確認するかもしれないいう例外があるかもしれません。

デフォルトの REPEATABLE READ 分離レベルで起動しているなら、同じトランザクション内の全ての一貫した読み取りは、そのトランザクション内の最初の読み取りで確立されたスナップショットを読み取ります。 現在のトランザクションをコミットしその後新しいクエリを発行する事で、クエリにより新鮮なスナップショットを得る事ができます。

InnoDB が READ COMMITTED と REPEATABLE READ 分離レベル内で SELECT ステートメントを処理する中で、一貫した読み取りはデフォルトのモードです。一貫した読み取りはそれがアクセスするテーブル上に一切ロックを設定しないので、別のユーザは、そのテーブル上で一貫した読み取りが行われているのと同時にそれらのテーブルを自由に変更する事ができます。

一貫した読み取りは DROP TABLE と ALTER TABLE 全体には機能しない事に注意してください。MySQL がドロップされたテーブルを利用する事ができず、InnoDB がそのテーブルを破壊する為、一貫した読み取りは DROP TABLE 全体には機能しません。ALTER TABLE は元テーブルのテンポラリ コピーを作成し、それができた時に元テーブルを破壊する事で機能する為、一貫した読み取りは ALTER TABLE 全体には機能しません。トランザクション内で一貫した読み取りを再発行する時、新しいテーブル内の行はトランザクションのスナップショットが撮られた時に存在していなかった為に見る事ができません。

場合によっては、一貫した読み取りは便利ではありません。例えば、テーブル child に新しい行を追加したければ、子供がテーブル parent に親を持っている事を確認するでしょう。次の例は、応用コード内でどのように参照整合性を実装するのかを表しています。

テーブル parent を読み取る為に一貫した読み取りを利用し、実際にテーブル内に子供の親を確認したと仮定してください。テーブル child に子供行を安全に追加する事ができますか?別のユーザが知らない間にテーブル parent から親行を削除する可能性があるので、答えはノーです。

この解決法は、LOCK IN SHARE MODE を利用して SELECT をロック モードで実行する事です:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

共有モードで読み取りを行うというのは、まず最新の有効なデータを読み取り、そして読んだ行に共有モードを設定するという意味です。共有モード ロックは、読み取った行が別の人によって更新されたり、削除されたりする事を防ぎます。また、もし最新データが別のクライアント接続のコミットされていないトランザクションに属していたら、そのトランザクションがコミットされるまで待ちます。先行クエリが親 'Jones' を返すのを確認した後、child テーブルに子レコードを安全に追加し、トランザクションをコミットする事ができます。

別の例を見てみましょう:テーブル child に追加された各子供に固有識別子を割り当てる為に利用する整数カウンタ フィールドが、テーブル child_codes 内にあります。カウンタの現在値を読む為に、一貫した読み取りや、共有モード読み取りを利用する事は、そのデータベースの2ユーザが同じカウンタ値を確認する可能性があり、またその2ユーザが同じ識別子を利用してテーブルに子供を追加しようとすると複製キーエラーが発生する為、良い考えとは言えません。

もし2ユーザがカウンタを同時に読むと、少なくても1ユーザはカウンタを更新しようとする時にデッドロックになってしまう為、 LOCK IN SHARE MODE はこの場合良い解決法とはいえません。

この場合、カウンタの読み取りとインクリメントを実装する為の良い方法が2つあります:(1) カウンタを1でインクリメントする事で更新し、その後にだけ読み取る、または、 (2) ロックモード FOR UPDATE を利用してまずカウンタを読み取り、その後にインクリメントする。後者の方法は、次のように実装できます:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT ... FOR UPDATE は、読み取る各行上に専用ロックを設定し、最新の有効データを読み取ります。従って、それは SQL UPDATE が行上に設定する物と同じロックを設定します。

前出の例は、ただ単に SELECT ... FOR UPDATE がどのように機能するかを表す例です。MySQL 内では、固有識別子を生成する特定のタスクは、実際にはテーブルへの単一アクセスの利用だけで達成する事ができます:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT ステートメントはただ単に識別子情報を検索するだけです。(現在の接続特有の物)これはテーブルにアクセスしません。

IN SHARE MODE と FOR UPDATE 読み取りによって設定されたロックは、トランザクションがコミットされたりロールバックされたりした時にリリースされます。

行レベル ロック内では、InnoDB は ネクスト キー ロック と呼ばれるアルゴリズムを利用します。InnoDB は、それがテーブルのインデックスを検索やスキャンする時に、遭遇したインデックス レコード上で共有、または専用ロックを設定する、という方法で行レベル ロックを実行します。従って、行レベル ロックは実際はインデックス レコード ロックであるという事になります。

InnoDB がインデックス レコード上で設定するロックは、そのインデックス レコードの前の 「ギャップ」 にも影響を与えます。もしユーザがインデックス内のレコード R 上に共有または専用ロックを持っていたら、別のユーザはインデックスの順番で R の直前に新しいインデックス レコードを挿入する事はできません。このようなギャップのロックは、一般的に 「バグの問題」 と呼ばれる物を防ぐ為に行われます。後で選択した行内のいくつかのカラムを更新するつもりで、100よりも大きい値の識別子を持つ child テーブルから全ての子供を読み、ロックしたいと仮定します:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

id カラム上にインデックスがあると仮定してください。クエリは、id が100以上の最初のレコードから、そのインデックスをスキャンします。もしインデックス レコード上に設定されたロックがギャップに挿入された物をロックしなければ、その一方で新しい行がテーブルに挿入されるでしょう。 もし同じトランザクション内で同じ SELECT を実行すると、クエリから返された結果セット内に新しい行を見つける事ができます。これは、トランザクションの分離原理に反しています:トランザクションは、読み取ったデータをトランザクションの最中に変更させない為に起動する必要があります。行セットをデータ項目であるとみなすと、新しい 「バグ」 の子供はこの分離原理に違反します。

When InnoDB がインデックスをスキャンする時、インデックス内の最後のレコードの後のギャップをロックする事もできます。それは前出の例の中で起きています:InnoDB によって設定されるロックは、id が100以上になるテーブルへの挿入を防ぎます。

アプリケーション内に一意性チェックを実装する為にネクスト キー ロックを利用する事ができます:共有モードでデータを読み取り、挿入しようとする行に重複が見られなければ、行を確実に挿入できます。また、読み取り中は対象となる行の後続の行にネクスト キー ロックが設定されて、第三者による重複行の挿入を防ぎます。このように、ネクスト キー ロックによって、テーブル内に存在しない物を 「ロック」 する事ができます。

デフォルトの REPEATABLE READ 分離レベルで起動していると仮定して下さい。一貫した読み取り(通常の SELECT ステートメント)では、InnoDBは、クエリがデータベースを参照する時の基準となるタイムポイントをトランザクションに与えます。こうして、タイムポイントが割り当てられた後に、他のトランザクションが行を削除してコミットしたとしても、一度読み取った内容は変わりません。挿入と更新も同じように扱われます。

割り当てられたタイムポイントを先に進めるには、トランザクションをコミットし、新たな SELECT を実行します。

これは、マルチ バージョン並行処理制御 と呼ばれています。

               User A                 User B

           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------
           1 row in set

この例の中では、ユーザ A は、ユーザ A と B の両方が挿入をコミットした時だけ、B によって挿入された行を確認する事ができ、それによってタイムポイントは B のコミットよりも先に進みます。

データベースの 「最新の」 状態を確認したければ、READ COMMITTED 分離レベルかロック読み取りのどちらかを利用しなければいけません:

SELECT * FROM t LOCK IN SHARE MODE;

ロックする読み取り、UPDATE、または DELETE は通常、SQL ステートメントの処理の中でスキャンされる全てのインデックス レコード上にレコード ロックを設定します。行を排除するステートメント内に WHERE 条件があればそれは問題ではありません。InnoDB は正確な WHERE 条件を記憶しませんが、どのインデックス範囲がスキャンされたのかは分かっています。レコード ロックは通常、レコードの前に 「ギャップ」 への挿入も速やかにブロックするネクスト キー ロックです。

もし設定されるロックが専用であれば、InnoDB は常にクラスタ化されたインデックス レコードの検索と、それに対するロックの設定もします。

もしご自分のステートメントに適応したインデックスがなく、MySQL がステートメントを処理する為にテーブル全体をスキャンしなければいけないなら、テーブルの全ての行がロックされ、それはその代わりにテーブルへの別のユーザによる全ての挿入をブロックします。クエリが不必要にたくさんの行をスキャンする必要がなくなるように、よいインデックスを作成する事が重要です。

InnoDB は次のように特定のロック タイプを設定します:

デフォルトにより、MySQL は自動コミット モードが有効な状態で各クライアント接続を起動します。自動コミットが有効な時、もしステートメントがエラーを返さなければ MySQL は各 SQL ステートメントの後にコミットを行います。もし SQL ステートメントがエラーを返したら、コミットやロールバック性能はそのエラーによって決まります。詳しくは 項13.5.15. 「InnoDB エラー処理」 を参照してください。

もし自動コミット モードがオフで、最後のトランザクションを明示的にコミットせずに接続を閉じると、MySQL はそのトランザクションをロールバックします。

次の各ステートメント(そしてそれらの同義語)は、まるでステートメントを実行する前に COMMIT を行ったかのように、暗黙にトランザクションを終了します。

トランザクションはネスト化されません。これは、START TRANSACTION ステートメントかその同義語の1つを発行する時点でのトランザクションに対して実行される、暗黙的な COMMIT の結果です。

トランザクションが ACTIVE 状態の間は、暗黙的なコミットを引き起こすステートメントは XA トランザクションでは利用する事はできません。

InnoDB は自動的にトランザクションのデッドロックを検出し、デッドロックを破壊する為にトランザクションをロールバックします。InnoDB は、トランザクションのサイズが挿入、更新、または削除された行数によって決定される小さいトランザクションを選んでロールバックしようとします。

InnoDB は innodb_table_locks=1 (デフォルト)と AUTOCOMMIT=0 であればテーブル ロックを認識しており、またそれより上位の MySQL レイヤは行レベル ロックを識別します。そうでなければ、InnoDB は MySQL LOCK TABLES ステートメントによるテーブル ロック セットや InnoDB 以外のストレージ エンジンによるロック セットが関連しているデッドロックを検出する事ができません。innodb_lock_wait_timeout システム変数の値を設定する事によって、これらの状況を解決しなければいけません。

InnoDB がトランザクションの完全なロールバックを実行する時、トランザクションによって設定される全てのロックはリリースされます。しかし、もし単一 SQL ステートメントだけがエラーの結果ロールバックされると、ステートメントによって設定されたいくつかのロックは維持されるかもしれません。これは、InnoDB が、後でどの行がどのステートメントによって設定されたのかという事を確認する事ができないようなフォーマットで行ロックを格納する為に起こります。

デッドロックはトランザクション データベースの中ではよく知られている問題ですが、ある特定のトランザクションを全く起動できないほど頻繁に起きる訳ではないのならば危険では有りません。 通常は、トランザクションがデッドロックの為にロールバックされたらそれを再発行できる準備が常にできているように、アプリケーションを書き込まなければいけません。

InnoDB は自動行レベル ロックを利用します。単一行を挿入または削除したばかりのトランザクションの場合でもデッドロックを得る事ができます。これは、これらの操作は実際は 「アトミック」 ではないからです。それらは自動的に挿入または削除された行の(可能であればいくつかの)インデックス レコード上にロックを設定します。

次のテクニックを利用して、デッドロックに対処し、それらの発生の可能性を減らす事ができます:

InnoDB は InnoDB 内部の状態についての情報をプリントする InnoDB モニタを含んでいます。ご自分の SQL クライアントにスタンダード InnoDB モニタのアウトプットをフェッチする為に、いつでも SHOW ENGINE INNODB STATUS SQL ステートメントを利用する事ができます。 この情報は性能調整をするうえで役立ちます。(もし mysql インタラクティブ SQL クライアントを利用しているなら、\G を通常のセミコロン ステートメント ターミネータと置き換えれば、アウトプットはより読みやすくなります。)InnoDB ロック モードの説明に関しては、項13.5.10.1. 「InnoDB ロック モード」 を参照してください。

mysql> SHOW ENGINE INNODB STATUS\G

InnoDB モニタを利用する別の方法は、それらが mysqld サーバのスタンダード アウトプットにデータを定期的に書き込む事を許可する事です。この場合、アウトプットがクライアントに送られる事はありません。スイッチが入ると、InnoDB モニタは大体15秒毎にデータをプリントします。サーバ アウトプットは通常 MySQL データ ディレクトリ内の .err ログに導かれます。このデータは性能調整をするうえで役立ちます。Windows 上では、もしアウトプットをエラー ログではなくウィンドウに導きたければ、--console オプションを利用して、コンソール ウィンドウ内のコマンド プロンプトからサーバを起動しなければいけません。

モニタ アウトプットは次のタイプの情報を含んでいます:

スタンダード InnoDB モニタに、mysqld のスタンダード アウトプットへの書き込みをさせる為、次の SQL ステートメントを利用してください:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

次のステートメントを発行する事でモニタを停止する事ができます:

DROP TABLE innodb_monitor;

CREATE TABLE 構文は、MySQL の SQL パーサを通してコマンドをInnoDB エンジンに渡す手段に過ぎません:唯一問題となるのは、テーブル名 innodb_monitor と、InnoDB テーブルです。テーブルの構造は InnoDB モニタとまったく無関係です。サーバを一度シャットダウンして、再度サーバを起動しても、モニタは自動的に起動しません。再びモニタを起動するには、まずモニタ テーブルをドロップし、そして新しい CREATE TABLE ステートメントを発行しなければいけません。(この構文は今後のリリース版で変更される可能性があります。)

innodb_lock_monitor を似たような方法で利用する事ができます。これは、大量のロック情報の提供もするという事以外、innodb_monitor と同じです。別々の innodb_tablespace_monitor は、テーブル スペース内に存在し、テーブル スペース割り当てデータ構造を認証する、作成されたファイル セグメントのリストをプリントします。さらに、InnoDB 内部データ ディレクトリの内容をプリントする事ができる innodb_table_monitor もあります。

InnoDB モニタ アウトプットの例:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the
semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits
375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831
inserting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,
  `D`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:
 len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:
 len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex
80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex
0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:
len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733
inserting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t',
'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782
inserting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',
NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention
waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;
asc aa35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id
38929 inserting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg
jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id
28684 committing
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj
gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id
36880 starting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for
update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index
PRIMARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;
asc supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id
34831 rollback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id
32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t',
'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id
30733 inserting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',
NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to   18 1212665295
Last checkpoint at  18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

アウトプットの注意点:

InnoDB は、バッファ オーバ フローの可能性を避ける為に、stdout や固定サイズ メモリ バッファではなく、stderr やファイルに診断アウトプットを送ります。副作用として、15秒ごとに MySQL データ ディレクトリ内のステータス ファイルに SHOW ENGINE INNODB STATUS のアウトプットが書き込まれます。ファイルの名前は innodb_status.pid で、pid はサーバ プロセス ID です。InnoDB は通常のシャットダウンの為にファイルを削除します。もし異常なシャットダウンが起きたら、これらのステータス ファイルが存在する可能性があり、それはマニュアルで削除する必要があります。それらを削除する前に、異常シャットダウンの原因に関する有益な情報を含んでいるかどうかを確認した方が良いでしょう。innodb_status.pid ファイルは設定オプション innodb_status_file=1 が設定された場合のみ作成されます。

全ての InnoDB インデックスは,インデックスのレコードがツリーのリーフページに格納されるB ツリーです。インデックス ページのデフォルト サイズは16 KB です。新しいレコードが挿入されると、InnoDB はページの1/16 を、将来のインデックス レコードの挿入や更新に備えて空けようとします。

インデックス レコードがシーケンシャル（昇順または降順）に挿入されると、インデックスページの約15/16 までがいっぱいになります。レコードがランダムに挿入された場合は、ページの1/2から15/16までがいっぱいになります。インデックス ページの使用容量が1/2未満になると、InnoDB はインデックス ツリーを縮小してページを解放しようとします。

データベース アプリケーションでは、主キーが固有の識別子であり、新しい行が主キーの昇順で挿入される事が一般的です。したがって、集合インデックスへの挿入では、ディスクからのランダムな読み取りを必要としません。

一方、セカンダリ インデックスは通常固有ではなく、セカンダリ インデックスへの挿入は比較的ランダムな順番で行われます。 この為、InnoDB で特別な構造が使用される事なく、多数のランダムなディスク I/O が発生します。

固有でないセカンダリ インデックスにインデックス レコードが挿入される場合は、セカンダリ インデックス ページがすでにバッファ プール内にあるかどうかが InnoDB によってチェックされます。すでにある場合は、InnoDB によってインデックス ページに直接レコードが挿入されます。バッファ プール内にインデックス ページがなかった場合は、InnoDB によって特別な挿入バッファ構造にレコードが挿入されます。挿入バッファは、その全体がバッファ プール内に収まるように小さくしてある為、このバッファへの挿入はきわめて高速です。

挿入バッファは、データベース内のセカンダリ インデックス ツリーに定期的にマージされます。インデックス ツリーの同じページ上で複数の挿入をマージする事で、ディスク I/O を削減できます。 挿入バッファによってテーブルへの挿入速度が最大15倍に高められる事が測定されています。

挿入バッファ マージは、挿入トランザクションがコミットされた 後 まで発生し続けるでしょう。実際、これはサーバがシャットダウンし、再起動する後まで発生し続けます。(項13.5.8.1. 「InnoDB 復旧の強制」 を参照してください。)

挿入バッファ マージは、多くのセカンダリ インデックスが更新される必要があり、多くの行が挿入された時に、何時間もかかる可能性があります。この間にディスク I/O が削減され、ディスクに頼っているクエリの速度を大幅に下げる事ができます。その他の有効なバックグラウンド I/O 操作はパージ スレッドです。(項13.5.12. 「マルチバージョンの実装」 を参照してください。)

データベースのほぼ全体がメイン メモリ内に収まる場合に、そのデータベースで最も速くクエリを実行するには、ハッシュ インデックスを使用します。InnoDB には、テーブルに定義されたインデックスで実行される検索を監視する構造があります。ハッシュ インデックスの構築がクエリにとって有益であると InnoDB が判断した場合は、自動的にそのインデックスが構築されます。

ただし、ハッシュ インデックスは常にテーブルに存在する B ツリー インデックスを基に構築されるので注意してください。InnoDB は、B ツリー インデックスに対して InnoDB が検出した検索パターンに応じて、任意の長さのB ツリーに定義されたキーの先頭部分に、ハッシュ インデックスを構築できます。ハッシュ　インデックスは部分的であってもかまいません:つまり、B ツリー インデックス全体をバッファ プールにキャッシュする必要はありません。InnoDB は、頻繁にアクセスされるインデックス ページへの要求に応じてハッシュ インデックスを構築します。

ある意味では、柔軟なハッシュ インデックスの構造を利用して、InnoDB が十分に余裕のあるメイン メモリに適応する事で、メイン メモリ データベースの構造に近づいています。

InnoDB テーブルの物理的なレコード構造は、テーブルが作成された時に指定された行フォーマットによって決まります。MySQL 5.1 ではデフォルトで InnoDB が COMPACT フォーマットを利用しますが、MySQL の古いバージョンとの互換性を保持する為にはREDUNDANT フォーマットが有効です。

InnoDB ROW_FORMAT=REDUNDANT テーブル内のレコードは、次の特徴を持っています:

InnoDB ROW_FORMAT=COMPACT テーブル内のレコードには次の特徴があります:

InnoDB は、擬似非同期 I/O を使用します:InnoDB は、多数の I/O スレッドを作成して、先読みなどの I/O 操作に対応します。

InnoDB には2つの先読みヒューリスティックがあります:

InnoDB は 二重書き込み と呼ばれる新しいファイル フラッシュ テクニックを利用します。.これは、OS のクラッシュや停電後の復旧に安全性を追加し、また fsync() オペレーションの必要性を削減する事で ほとんどの種類の Unix の性能を向上させます。

二重書き込みとは、データ ファイルにページを書き込む前に、InnoDB が最初にそれらを二重書き込みバッファと呼ばれる隣接するテーブルスペース エリアに書き込む事を意味します。二重書き込みバッファへの書き込みとフラッシュが完了した後に InnoDB はデータ ファイル内の正しい位置にページを書き込みます。もし OS がページ書き込みの最中にクラッシュしたら、InnoDB は復旧の最中に二重書き込みバッファからページの有効なコピーを見つける事ができます。

設定ファイルに定義するデータ ファイルから、InnoDB のテーブル スペースが構成されます。これらのファイルは、単純に連結されてテーブルスペースになります。ストライピングは使用されません。現時点では、テーブルスペースのどの位置にテーブルが割り当てられるかを定義できません。しかし、新たに作成されるテーブルスペース内では、InnoDB が最初のデータ ファイルから領域を割り当てます。

テーブルスペースは、デフォルト サイズが16 KB のデータベース ページで構成されます。 これらのページは、64個の連続するページから成るエクステントにグループ化されます。InnoDB では、テーブルスペース内部の 「files」 を セグメント と呼びます。これは実際には多くのテーブルスペース セグメントを含んでいる為、「ロールバック セグメント」という名前は、多少誤解を招くおそれがあります。

InnoDB では、各インデックスに2つのセグメントが割り当てられます。1つは B ツリーの非リーフ ノード用、もう1つはリーフ ノード用です。これには、データを含んでいるリーフ ノードで連続性を高める意図があります。

テーブルスペース内でセグメントが大きくなると、InnoDB はそのセグメントに最初の32ページを個別に割り当てます。InnoDB はその後、エクステント全体をセグメントに割り当て始めます。InnoDB では、データの連続性を確保する為に、大きなセグメントに一度に最大4つのエクステントを追加できます。

テーブルスペースには、他のページのビットマップを含んだページがある為、InnoDB テーブルスペース内のいくつかのエクステントは、全体としてではなく個別のページとしてのみセグメントに割り当てる事ができます。

SHOW TABLE STATUS ステートメントを発行してテーブルスペース内の空き領域を照会すると、InnoDB からテーブルスペース内の完全に空いているエクステントが報告されます。InnoDB は、常にいくつかのエクステントをクリーンアップとその他の内部的な用途の為に確保しており、これらのエクステントは空き領域に含まれません。

テーブルからデータを削除すると、InnoDB によって対応する B ツリー インデックスが縮小されます。これによって、他のユーザが開放された領域を利用できるようになるかどうかは、削除のパターンがテーブルスペースの個々のページやエクステントを開放するかどうかによって異なります。テーブルを破棄したり、またはテーブルからすべての行を削除すると、他のユーザに確実に領域が解放されますが、削除された行は、トランザクション ロールバックまたは一貫した読み取りでそのレコードが必要なくなった後のパージ操作で初めて物理的に削除されるという事に注意してください（詳しくは 項13.5.12. 「マルチバージョンの実装」 を参照してください。）

テーブルのインデックスでランダムな挿入または削除が行われると、インデックスがフラグメント化される事があります。フラグメント化とは、ディスクでのインデックス ページの物理的な順序が、ページでのレコードのインデックス順とかけ離れている事、またはインデックスに割り当てられた64ページのブロック内に多数の未使用ページがある事を意味します。

フラグメント化の兆候は、テーブルが 「必要とする」 以上の領域を取るという事です。それがどの程度なのかという事を正確に決めるのは困難です。全ての InnoDB データとインデックスは B ツリー内に格納され、それらの充てん比は50% から100% で異なっています。

その他のフラグメント化の兆候は、このようなテーブルスキャンに 「必要」 以上の時間がかかるという事です。

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

(前出のクエリの中で、SQL オプチマイザはセカンダリ インデックスではなく、集合インデックスのスキャンの中で 「欺かれて」 います。)ほとんどのディスクが1秒に付き10から50 MB で読み込む事ができ、それでテーブル スキャンがどれくらいのスピードで起動しなければいけないかを概算します。

それは 「null」 ALTER TABLE 操作を定期的に実行すればインデックス スキャンの速度を上げる事ができます:

ALTER TABLE tbl_name ENGINE=INNODB

それによって MySQL はテーブルを再構成します。デフラグ操作を行う別の方法は、テーブルをテキスト ファイルにダンプし、テーブルをドロップし、そしてそれをダンプ ファイルから再ロードする為に mysqldump を利用する事です。

インデックスへの挿入が常に昇順で行われ、レコードが必ず末尾から削除される場合は、InnoDB のファイル領域管理アルゴリズムによってインデックスのフラグメント化が発生しない事が保証されます。

次にある物は、今後直面するであろう主な InnoDB 特有エラーの非消耗リストと、それらがなぜ起きるのか、そしてその問題をどのように解決するのかについての情報です。

OS エラー番号の意味をプリントする為に、MySQL ディストリビューションに付属している perror プログラムを利用してください。

次のテーブルにはいくつかの主な Linux システム エラー コードが紹介されています。完全なリストに関しては、Linux ソース コード を参照してください。

次のテーブルにはいくつかの主な Windows システム エラー コードが紹介されています。完全なリストに関しては Microsoft ウェブ サイト を参照してください。

テーブルに関する特定の問題点は、MySQL サーバが、データベース ディレクトリ内に格納する .frm ファイル内にデータ ディクショナリ情報を保存する一方、InnoDB もまたテーブルスペース ファイル内にあるそれ自体のデータ ディクショナリに情報を格納するという事です。もし .frm ファイルを移動させたり、サーバがデータ ディクショナリ操作の最中にクラッシュしたりすると、.frm ファイルの場所は、InnoDB 内部データ ディクショナリ内に記録された場所と同期しなくなってしまうかもしれません。

同期していないデータ ディクショナリの兆候は、CREATE TABLE ステートメントが失敗する事です。もしこれが起こったら、サーバのエラー ログを確認する必要があります。もしログが、テーブルは既に InnoDB 内部データディクショナリ内に存在すると報告すると、対応する .frm ファイルを持たない InnoDB テーブルスペース ファイル内に孤立テーブルを持つという事になります。エラー メッセージはこのようになります:

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

エラー メッセージで指示された方法に従えば、孤立テーブルをドロップする事ができます。もしまだ DROP TABLE を無事にに利用できないのであれば、その問題は mysql クライアント内での名前の完了に原因があるかもしれません。この問題を解決するには、--skip-auto-rehash オプションを利用して mysql クライアントを開始し、もう一度 DROP TABLE を実行してみてください。(名前の完了がオンになっていると、mysql は今説明したような問題が存在する時に失敗する、テーブル名のリストを構築しようとします。)

同期していないデータ ディクショナリのその他の兆候は、.InnoDB ファイルを開く事ができないエラーを MySQL がプリントする事です:

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

エラー ログ内に次のようなメッセージが表示されます:

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

これは InnoDB 内に、対応するテーブルを持たない孤立した .frm ファイルがある事を意味します。孤立した .frm ファイルは、マニュアルで削除する事でドロップできます。

もし MySQL が ALTER TABLE 操作の最中でクラッシュしたら、InnoDB テーブルスペースの中に孤立したテンポラリ テーブルができてしまうかもしれません。innodb_table_monitor を利用して、#sql-... が誰の名前なのかが表示されたテーブルのリストを確認する事ができます。名前をバックフォートで囲めば、文字 ‘#’ を名前に含んでいるテーブル上で SQL ステートメントを実行する事ができます。したがって、先ほど説明した方法で、そのような孤立テーブルを他の孤立テーブルと同じようにドロップする事ができます。Unix シェル内でファイルをコピーしたりリネームしたりするには、もしファイル名が ‘#’ を含んでいたら、ファイル名を二重引用符で囲まなければいけない事を覚えておいてください。

最初の方法を使うには、CREATE TABLE ステートメントのエンジンタイプの後ろに CONNECTION 文字列を指定する必要があります。例:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

CONNECTION 文字列は データを物質的に格納する為に利用されるテーブルを含む、リモートサーバに接続する為に要求される情報を含んでいます。接続文字列は、サーバ名、ログイン実績、ポート番号、そしてデータベースとテーブルの情報を指定します。 例の中では、リモートテーブルはポート9306を利用しているサーバ remote_host 上にあります。名前とポート番号は、リモートテーブルとして利用するリモートMySQLサーバインスタンスのホスト名(またはIPアドレス)とポート番号に適合する必要があります。

接続文字列のフォーマットは次のようになります。

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

場所:

接続文字列の例

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

同じサーバ上にいくつかのFEDERATEDテーブルを作成する場合や、FEDERATEDテーブルの作成プロセスをシンプルにしたい場合には、CONNECTION 文字列の時と同じように、サーバ接続パラメータを定義するために CREATE SERVER ステートメントを利用する事ができます。

CREATE SERVER ステートメントのフォーマットは

CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option ...)

server_name は新しいFEDERATEDテーブルを作成する時、接続文字列として利用されます。

例えば、CONNECTION 文字列と同一のサーバ接続を作成するには

CONNECTION='mysql://root@remote_host:9306/federated/test_table';

以下のステートメントを利用する必要があります。

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');

この接続を利用した FEDERATED テーブルを作成するには、CONNECTIONキーワードも使うのですが、CREATE SERVERステートメントで使用した名前を指定してください。

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
CONNECTION='fedlink.test_table';

この例の中の接続名には、(fedlink) 接続名と、リンクされる(test_table)テーブル名が、ピリオドで区切られて含まれています。この方法で明確にテーブル名を指定しないと、代わりにローカルテーブルのテーブル名が使用されます。

CREATE SERVERについては 項12.1.11. 「CREATE SERVER 構文」を参照してください。

CREATE SERVER ステートメントは、CONNECTION文字列と同じ引数を受け入れます。CREATE SERVER ステートメントは mysql.servers テーブルの中のレコードをアップデートします。接続文字列を利用する時、CREATE SERVERステートメントのオプションと、mysql.serversテーブルカラムなど、テーブルのパラメータ情報を見てください。次にあるのが、参照用のCONNECTION 文字列のフォーマットです。

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name