MySQLにおける外部キー作成時の自動インデックス生成

MySQLの外部キー制約において、外部キーと参照キーにはインデックスが必要です。

下記はMySQL 8.0のドキュメントですが、5.7でも同じような内容となっています。

dev.mysql.com

参照元のテーブルには、外部キーのカラムが同じ順序で最初のカラムとしてならぶインデックスが必要です。もし該当するindexがなければ、MySQLは自動でインデックスを作成します。この自動で作られたインデックスは後で追加したインデックスが外部キーのインデックス要件を満たす場合、暗黙的に削除されることがあります。

これを実際に試してみようと思います

確認したMySQLのバージョンは 8.0.29 です。

まずテーブルを作ってみます

mysql> CREATE TABLE `users` (
    ->   `id` int NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `user_foo` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int NOT NULL,
    ->   `foo` int  NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

これでテーブル定義を見てみます。

mysql> SHOW CREATE TABLE user_foo;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                         |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_foo | CREATE TABLE `user_foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `foo` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`), <----- CREATE文にないindexが追加されている
  CONSTRAINT `user_foo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

CREATE文にないインデックスが自動で作られます。

外部キーのcolumnが同じ順序で最初のカラムとしてならぶインデックスが必要です。 これを確認してみましょう。

外部キーを含む複数カラムに対してセカンダリインデックスを追加してみます。

mysql> CREATE TABLE `users` (
    ->   `id` int NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `user_foo` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int NOT NULL,
    ->   `foo` int  NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY (`user_id`, `foo`),
    ->   CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE user_foo;                                                                                                                                                                                     +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                               |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_foo | CREATE TABLE `user_foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `foo` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`foo`),
  CONSTRAINT `user_foo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

今度は自動でのインデックスが追加されていません。

user_idとfooのセカンダリインデックスで外部キー制約のインデックスの要件を満たしたためですね。

では今度はuser_idとfooを逆にしてみます。

mysql> CREATE TABLE `users` (
    ->   `id` int NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `user_foo` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int NOT NULL,
    ->   `foo` int  NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY (`foo`, `user_id`),
    ->   CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE user_foo;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                        |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_foo | CREATE TABLE `user_foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `foo` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo` (`foo`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_foo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

今度は外部キーのcolumnが同じ順序で最初のカラムとしてならぶインデックスにならなかったため、user_id単独のセカンダリインデックスが自動で追加されました。

今度は外部キー制約を満たすために追加したインデックスが不要になるインデックスが追加されたときの挙動を見てみます。

mysql> CREATE TABLE `users` (
    ->   `id` int NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `user_foo` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int NOT NULL,
    ->   `foo` int  NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE user_foo;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                         |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_foo | CREATE TABLE `user_foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `foo` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`), <---- 自動で追加されたインデックス
  CONSTRAINT `user_foo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

自動でインデックスが追加されています。

では、新たにインデックスを追加してみます。

mysql> ALTER TABLE user_foo ADD INDEX (`user_id`,`foo`); 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE user_foo;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                               |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_foo | CREATE TABLE `user_foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `foo` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`foo`),
  CONSTRAINT `user_foo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

user_id単体ではられていたインデックスが削除され、ALTERによって追加されたインデックスのみが残りました。

ではこのインデックスを削除してみるとどうなでしょう。

mysql> ALTER TABLE user_foo DROP INDEX `user_id`;
ERROR 1553 (HY000): Cannot drop index 'user_id': needed in a foreign key constraint

この場合は、自動でインデックスが追加されるわけではなく、外部キー制約に必要なるということでエラーになります。

CubicLouve

Spring_MTの技術ブログ

MySQLにおける外部キー作成時の自動インデックス生成