14.6.1 InnoDB テーブルの作成

InnoDB テーブルを作成するには、特別な句を付けずに CREATE TABLE ステートメントを使用します。以前は、ENGINE=InnoDB 句が必要でしたが、InnoDB がデフォルトのストレージエンジンとなったため必要なくなりました。(デフォルトのストレージエンジンが MyISAM である MySQL 5.1 以前が実行されているサーバー上で、mysqldump またはレプリケーションを使用して CREATE TABLE ステートメントを再現する予定がある場合は、その句を引き続き使用できます。)

-- Default storage engine = InnoDB.
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a));
-- Backward-compatible with older MySQL.
CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

innodb_file_per_table の設定に応じて、InnoDB ではシステムテーブルスペース内またはテーブルごとの別個のテーブルスペース (.ibd ファイルで表されます) 内に、各テーブルおよび関連付けられた主キーインデックスが作成されます。MySQL では、MySQL データベースディレクトリの下にある test ディレクトリ内に、t1.frm および t2.frm ファイルが作成されます。内部的に、InnoDB 独自のデータディクショナリにテーブルのエントリが追加されます。このエントリには、データベース名が含まれます。たとえば、t1 テーブルが作成されるデータベースが test である場合、エントリは 'test/t1' となります。つまり、ほかのいくつかのデータベースに同じ名前 t1 のテーブルを作成でき、InnoDB 内部でテーブル名の競合は発生しません。

これらのテーブルのプロパティーを表示するには、SHOW TABLE STATUS ステートメントを発行します。

SHOW TABLE STATUS FROM test LIKE 't%' \G;

ステータスの出力には、これらの 1 番目のテーブルの行フォーマットプロパティーが「Compact」であることが表示されます。この設定は基本的な実験には適していますが、もっとも強力な InnoDB のパフォーマンス機能を活用するには、すぐに「Dynamic」や「Compressed」などのその他の行フォーマットの使用に進んでください。これらの値を使用するには、最初に少しの設定が必要です。

set global innodb_file_per_table=1;
set global innodb_file_format=barracuda;
CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) row_format=dynamic;
CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) row_format=compressed;

必ず、InnoDB テーブルごとに主キーを設定し、次のようなカラム (複数の場合あり) を指定してください。

• もっとも重要なクエリーで参照される。

• ブランクのままになっていない。

• 重複する値がない。

• 挿入後に値が変更されるとしても、きわめてまれである。

たとえば、人に関する情報を含むテーブルでは、複数の人が同じ名前を持つ可能性もあり、名字をブランクにしたり、名前を変更したりする人もいるため、(名、姓) 上には主キーを作成しません。制約が非常に多く、主キーとして使用する明確なカラムセットがないことも多い場合には、主キーの全部または一部として機能する数値 ID の新しいカラムを作成してください。行が挿入されると自動的に昇順の値が入力されるように、自動インクリメントカラムを宣言できます。

-- The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));
-- The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

主キーを定義しなくてもテーブルは正常に機能しますが、主キーは多くのパフォーマンス要素に関連し、大きなテーブルや頻繁に使用されるテーブルにとって重要な設計要素です。常に CREATE TABLE ステートメントで指定することを習慣にしてください。(テーブルを作成し、データをロードしてから、ALTER TABLE を実行してあとで主キーを追加する場合の操作は、テーブルの作成時に主キーを定義するよりも大幅に時間がかかります。)