NULL許容した＆2つのテーブルから外部キーをもっているテーブルはインデックスが効くのか確認する

概要

• 2つのテーブルと関連づけるため、外部キーカラムを2つもっている
• ただ、一方が存在しているともう一方との関連はいらない。という仕様をもっている
  • NULL許容した外部キーカラムにしている
• この場合ってインデックスは効くの？ってなったので調べてみた
• MySQL5.7です

先人の知恵

効きそう

〜　END　〜

試してみる

まぁ大丈夫だとは思うけど、手元で確認してみた

準備

CREATE TABLE posts (
     id INT AUTO_INCREMENT PRIMARY KEY,
     title varchar(40),
     body varchar(40),
     index(title)
   );

CREATE TABLE tags (
     id INT AUTO_INCREMENT PRIMARY KEY,
     title varchar(40),
     body varchar(40),
     index(title)
   );

CREATE TABLE messages (
     id INT AUTO_INCREMENT PRIMARY KEY,
     title varchar(40),
     post_id INT,
     tag_id INT,
     FOREIGN KEY (post_id) REFERENCES posts(id),
     FOREIGN KEY (tag_id) REFERENCES tags(id),
     index(title)
   );
      
CREATE PROCEDURE create_test_data_posts(IN i INT)
BEGIN
  DECLARE cnt INT DEFAULT 0;
  DECLARE v VARCHAR(10);
  WHILE cnt < i DO
    SET cnt = cnt + 1;
    IF (cnt % 10) != 0 THEN 
      SET v = cnt;
    ELSE 
      SET v = NULL;
    END IF;
    INSERT INTO posts(title, body) VALUES(v, v);
  END WHILE;
END

CREATE PROCEDURE create_test_data_tags(IN i INT)
BEGIN
  DECLARE cnt INT DEFAULT 0;
  DECLARE v VARCHAR(10);
  WHILE cnt < i DO
    SET cnt = cnt + 1;
    IF (cnt % 10) != 0 THEN 
      SET v = cnt;
    ELSE 
      SET v = NULL;
    END IF;
    INSERT INTO tags(title, body) VALUES(v, v);
  END WHILE;
END

CREATE PROCEDURE create_test_data_message(IN i INT)
BEGIN
  DECLARE cnt INT DEFAULT 0;
  DECLARE v VARCHAR(10);
  DECLARE x INT DEFAULT 1;
  DECLARE y INT DEFAULT 1;
  WHILE cnt < i DO
    SET cnt = cnt + 1;
    IF (cnt % 10) != 0 THEN 
      SET v = cnt;
      SET x = FLOOR(1 + RAND() * 100000);
      SET y = NULL;
    ELSE 
      SET v = NULL;
      SET x = NULL;
      SET y = FLOOR(1 + RAND() * 100000);
    END IF;
    INSERT INTO messages(title, post_id, tag_id) VALUES(v, x, y);
  END WHILE;
END

CALL create_test_data_posts(100000);
CALL create_test_data_tags(100000);
CALL create_test_data_message(100000);

要はposts, tagsテーブルがあって、messagesが２つのテーブルから外部キーをもらっている構成です。テーブル名に意味はないです

確認してみる

mysql> EXPLAIN SELECT * FROM messages WHERE post_id = 3;

id|select_type|table   |partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--+-----------+--------+----------+----+-------------+-------+-------+-----+----+--------+-----+
 1|SIMPLE     |messages|          |ref |post_id      |post_id|5      |const|   1|   100.0|     |



mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE post_id IS NULL;

id|select_type|table   |partitions|type|possible_keys|key    |key_len|ref  |rows |filtered|Extra                   |
--+-----------+--------+----------+----+-------------+-------+-------+-----+-----+--------+------------------------+
 1|SIMPLE     |messages|          |ref |post_id      |post_id|5      |const|10000|   100.0|Using where; Using index|



mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE tag_id IS NULL;

id|select_type|table   |partitions|type|possible_keys|key   |key_len|ref  |rows |filtered|Extra                   |
--+-----------+--------+----------+----+-------------+------+-------+-----+-----+--------+------------------------+
 1|SIMPLE     |messages|          |ref |tag_id       |tag_id|5      |const|50159|   100.0|Using where; Using index|



mysql> EXPLAIN SELECT * FROM messages WHERE post_id = 3 AND tag_id IS NULL;

id|select_type|table   |partitions|type|possible_keys |key    |key_len|ref  |rows|filtered|Extra      |
--+-----------+--------+----------+----+--------------+-------+-------+-----+----+--------+-----------+
 1|SIMPLE     |messages|          |ref |post_id,tag_id|post_id|5      |const|   1|    50.0|Using where|



mysql> EXPLAIN SELECT * FROM messages WHERE tag_id IS NOT NULL;

id|select_type|table   |partitions|type |possible_keys|key   |key_len|ref|rows|filtered|Extra                |
--+-----------+--------+----------+-----+-------------+------+-------+---+----+--------+---------------------+
 1|SIMPLE     |messages|          |range|tag_id       |tag_id|5      |   |3342|   100.0|Using index condition|

大丈夫そう

なぜ確認しようと思ったのか

• 効くかわからなかった
• ドキュメント（？）確認したけど、ぱっと見わからなかった（そもそも知りたいことなのかもわからなかった）
  • 読んだやつ
• ChatGPTに確認すると、NULLが多い場合は検索効率に影響をきたすかも。的な回答もらった
  • ググってみた → MySQLのインデックスの貼っていいとき悪いときを原理から理解したいよ😭

NULLが多い列でのインデックス
インデックスはNULL値を除いて作成されるため、NULLが多い列からNULLではない値を取り出す処理が速くなる

なるほど。ということはNULLを検索するのは影響でそうだけど、非NULLであれば問題なさそう。って理解になりました