概要
外部キー制約をかける時にMySQLはインデックスを暗黙的に作成しますがPostgreSQLは作成しません。この違いを知らないと、同じスキーマを作成したつもりであってもインデックス有無が異なるため、MySQLとPostgreSQLでパフォーマンスに大きな違いが生じるかも知れません。
前提ソフトウェア
| ソフトウェア | バージョン | 備考 |
|---|---|---|
| MySQL | 8.0.19 | - |
| PostgreSQL | 12.2 | - |
ドキュメントを確認する
ドキュメントにもそのように書いてあります。現時点で一番新しいバージョンの日本語と英語のドキュメントを引用します。(ドキュメントが変更されていて、日本語と英語は一部対応していないところがあります) 余談ですがPostgreSQLのユーザに選択を委ねる姿勢の方が私は好きです。
https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html
index_name は、外部キー ID を表します。外部キーをサポートできる子テーブル上に明示的に定義されたインデックスがすでに存在する場合、 index_name 値は無視されます。それ以外の場合、MySQL は、次のルールに従って名前が付けられた外部キーのインデックスを暗黙的に作成します。
定義されている場合は、CONSTRAINT symbol 値が使用されます。それ以外の場合は、FOREIGN KEY index_name 値が使用されます。
CONSTRAINT symbol と FOREIGN KEY index_name のどちらも定義されていない場合、外部キーのインデックス名は、参照している外部キーカラムの名前を使用して生成されます。
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
https://www.postgresql.jp/document/11/html/ddl-constraints.html#DDL-CONSTRAINTS-FK
外部キーは主キーであるかまたは一意性制約を構成する列を参照しなければなりません。 これは、被参照列は常に(主キーまたは一意性制約の基礎となる)インデックスを持つことを意味します。 このため、参照行に一致する行があるかどうかのチェックは効率的です。 被参照テーブルからの行のDELETEや被参照行のUPDATEは、古い値と一致する行に対して参照テーブルのスキャンが必要となるので、参照行にもインデックスを付けるのは大抵は良い考えです。 これは常に必要という訳ではなく、また、インデックスの方法には多くの選択肢がありますので、外部キー制約の宣言では参照列のインデックスが自動的に作られるということはありません。
https://www.postgresql.org/docs/12/ddl-constraints.html#DDL-CONSTRAINTS-FK
A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.
実験
では実際にMySQL, PostgreSQLの両方にスキーマを作成して試してみます。スキーマは共通で以下のSQLを使います。インデックスが作成されるかを確認するためのスキーマなので最小限のものです。
CREATE TABLE parents (
id INT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE children (
id INT NOT NULL,
parent_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES parents (id)
);
MySQL
MySQLでchildrenテーブルのインデックスを確認してみます。parent_idに対して暗黙的にインデックスが作成されていることがわかります。
mysql> SHOW INDEX FROM children;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| children | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| children | 1 | parent_id | 1 | parent_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
テーブルが空なのでなんともですが、ついでにparent_idでchildrenを絞り込むクエリの実行計画を見てみます。するとparent_idをキー(key)とする実行計画となっていて、暗黙的に作成されたインデックスが使われてる高速なクエリとなることがわかります。
mysql> EXPLAIN SELECT * FROM children WHERE parent_id = 999;
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | children | NULL | ref | parent_id | parent_id | 4 | const | 1 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
PostrgreSQL
同様にPostgreSQLに対するインデックスを確認してみます。Foreign-key constrainsはかかっていますが、Indexesはidに対するchildren_pkeyのみとなっています。
postgres=# \d children;
Table "public.children"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
id | integer | | not null |
parent_id | integer | | not null |
Indexes:
"children_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"children_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES parents(id)
こちらもparent_idでchildrenを絞り込むクエリの実行計画を見てみます。Seq Sacnですのでシーケンシャルスキャンになっています。
postgres=# EXPLAIN SELECT * FROM children WHERE parent_id = 999;
QUERY PLAN
----------------------------------------------------------
Seq Scan on children (cost=0.00..38.25 rows=11 width=8)
Filter: (parent_id = 999)
(2 rows)
念のためparent_idにインデックスを追加して試してみます。
CREATE INDEX ON children (parent_id);
実行計画もBitmap Heap Scanになりました。children_parent_id_idxが使われていることがわかります。
postgres=# EXPLAIN SELECT * FROM children WHERE parent_id = 999;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on children (cost=4.24..14.91 rows=11 width=8)
Recheck Cond: (parent_id = 999)
-> Bitmap Index Scan on children_parent_id_idx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (parent_id = 999)
(4 rows)
