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
を実行してあとで主キーを追加する場合の操作は、テーブルの作成時に主キーを定義するよりも大幅に時間がかかります。)