MySQLの外部キー制約とインデックス

f:id:Juju_62q:20200810153315p:plain

TL;DR

外部キー制約をかけると暗黙的にインデックスが貼られる
外部キー制約がある場合、該当カラムに対してインデックスをなくすことができない
ORMなどでマイグレーションの管理をしている場合などは気をつけよう

ことの発端

以下のテーブルを考える（外部キーがあればよろしいです）

f:id:Juju_62q:20200810160645p:plain

このテーブルでインデックスを貼ることを考える。ブログ記事は "あるユーザの記事を作成日時に対して降順" に表示する場合が多いだろう。

そこで記事テーブルのindexとして user_id, created_at の順に貼ることにする。

そこで以下のDDLを実行した（実際にはORM経由）。

CREATE TABLE `User`(
  `id` bigint PRIMARY KEY,
  `display_name` varchar(256),
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
);

CREATE TABLE `Article`(
  `id` bigint PRIMARY KEY,
  `user_id` bigint,
  `content` text(65536),
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`),
  INDEX `index01` (`user_id`, `created_at`)
);

テーブルのマイグレーションをロールバックする際に以下のような順で処理が走った。

インデックス削除 -> テーブルの削除

すると下記のエラーが発生した。

mysql> DROP INDEX `index01` on `Article`;
ERROR 1553 (HY000): Cannot drop index 'index01': needed in a foreign key constraint

原因

MySQLは外部キー制約を追加すると暗黙的にそのカラムに対してインデックスを貼るような振る舞いをとる。（本例の場合以下のようにインデックスを指定していなければ user_id にindexが貼られる。）

mysql> CREATE TABLE `Article`(
    ->   `id` bigint PRIMARY KEY,
    ->   `user_id` bigint,
    ->   `content` text(65536),
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime NOT NULL,
    ->   CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW INDEX from `Article`;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Article |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Article |          1 | fk_user_id |            1 | user_id     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

ただし複合インデックスなどで対象のカラムに対して有効なインデックスが存在する場合には、暗黙的なインデックスの作成が行われない。（この場合複合主キーの1番目に指定されているので user_id に対してはインデックスが有効。）

mysql> drop table Article;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `Article`(
    ->   `id` bigint PRIMARY KEY,
    ->   `user_id` bigint,
    ->   `content` text(65536),
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime NOT NULL,
    ->   CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`),
    ->   INDEX `index01` (`user_id`, `created_at`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW INDEX from `Article`;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Article |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Article |          1 | index01  |            1 | user_id     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| Article |          1 | index01  |            2 | created_at  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

MySQLで外部キー制約をかける場合は対象カラムにインデックスがないという状態が許されない。結果として index01 の削除に失敗した。

その上で index01 を削除したい場合は、 user_id に対するインデックスを追加するか、外部キー制約を解除すると削除ができる。

終わりに

MySQLはかなりよく使っているし、RDBに関しては人並みには知識を持っているつもりだったので、今になってその表面的な振る舞いで躓くことになるとは正直思わなかった。もちろんMySQLのストレージエンジンなどバックエンドについて自分の知識が及ばない部分があるというのは知っているが今回の学びはかなり表面的な部分である。

そう考えると、僕が知ってると思い込んでいるもの（部分）にもまだまだ知らないことがたくさんあるんだろうなと思って少しワクワクした。なんとなく初心に返ることができたので、これからも驕らず、焦らず頑張っていきたいなと思った。

Juju-62q's blog

参加記録やメモ書き、思考のまとめをしています

MySQLの外部キー制約とインデックス

TL;DR

ことの発端

原因

終わりに