Zenn
📌

【MySQL】テーブル作成時に自動生成されるIndexたち

2022/02/27に公開
MySQL
SQL
tech

はじめに

MySQLでインデックスを使用しているかを尋ねられる機会がありました．
PRIMARY KEYやFOREIGN KEYを用いての検索がほとんどだったので大丈夫だという認識でしたが，明示的に設定していないだけで自動生成されていたのかな，，，と疑問が湧いたので実際にSHOW INDEXで確認しました．

携わったサービスでは，mysql:8.0を使用しており，mysqldefを用いてschema.sqlでスキーマを管理していました．

調査結果

今回の調査では，

  • PRIMARY KEY制約
  • UNIQUE制約
  • FOREIGN KEY制約

でindexが自動生成されるを確認しました！

また，公式ドキュメントでは，全文検索に用いられるFULLTEXTでも生成されることが示されていました．

ほとんどの MySQL インデックス (PRIMARY KEY、UNIQUE、INDEX、および FULLTEXT) は B ツリーに格納されます。 例外: 空間データ型のインデックスは R ツリーを使用します。MEMORY テーブルはハッシュインデックスもサポートします。InnoDB は FULLTEXT インデックスの逆のリストを使用します。

調査内容

1. 何も制約をつけない

はじめに定義したスキーマはこちらです．

schema.sql
CREATE TABLE `users` (
  `id` int
) ENGINE=InnoDB;

結果

MySQL root@127.0.0.1:example> SHOW INDEX FROM users
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

0 rows in set

当然何も表示されませんでしたネ．

2. PRIMARY KEY制約

idを主キーに設定しました．

schema.sql
CREATE TABLE `users` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

結果

MySQL root@127.0.0.1:example> SHOW INDEX FROM users
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0          | PRIMARY  | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1 row in set

PRIMARY KEYを設定した場合は，indexが自動生成されました！

3. UNIQUE制約

UNIQUE制約をつけたfirebase_uidカラムを追加しました．

schema.sql
CREATE TABLE `users` (
  `id` int NOT NULL,
  `firebase_uid` varchar(255) UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

結果

MySQL root@127.0.0.1:example> SHOW INDEX FROM users
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0          | PRIMARY      | 1            | id           | A         | 0           | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
| users | 0          | firebase_uid | 1            | firebase_uid | A         | 0           | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

2 rows in set

UNIQUE制約でも，indexが自動生成されました！

3. FOREIGN KEY制約

新たにitemsテーブルを追加し，user_idカラムにusersテーブルのidのFOREIGN KEY制約を設定しました．

schema.sql
CREATE TABLE `users` (
  `id` int NOT NULL,
  `firebase_uid` varchar(255) UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `items` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_items_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

結果

MySQL root@127.0.0.1:example> SHOW INDEX FROM items
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items | 0          | PRIMARY          | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
| items | 1          | fk_items_user_id | 1            | user_id     | A         | 0           | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

2 rows in set

FOREIGN KEY制約でも，indexが自動生成されました！

おわりに

「PRIMARY KEYやFOREIGN KEYを使ってるからエエやろ」のなんとなくの理解が，ちゃんとindexの恩恵を授かっていたことを理解できて勉強になりました！

また，今回携わったサービスでは，複合インデックス等，パフォーマンスを見てのチューニングは行っていなかったのですが，サービスのグロースに伴って必ず必要になるところなので，意識して設計しないとなと自分を戒める良い機会になりました．
ちなみに，チューニングを行っていなかった理由としては，スロークエリログで致命的に遅いクエリがなかった，パフォーマンス的に気になるとこまでデータ増えなかったためタスクとしての優先度が低かったため(あと面倒)でした．
サービスが成長しないと技術的にも成長しないんやなと思いましたネ(悲しい)

今後，とりあえずやってみようということで，EXPLAIN等用いながら手元でパフォーマンスチューニングやってみようと思います！

Discussion