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

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


# 先人の知恵

https://qiita.com/omukaik/items/6c9d3018c6ab8fbef7bb

効きそう

〜　END　〜

# 試してみる

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

## 準備

```sql
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`が２つのテーブルから外部キーをもらっている構成です。テーブル名に意味はないです


## 確認してみる

```sql
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|
```

大丈夫そう

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

* 効くかわからなかった
* ドキュメント（？）確認したけど、ぱっと見わからなかった（そもそも知りたいことなのかもわからなかった）
    * [読んだやつ](https://dev.mysql.com/doc/refman/8.0/ja/is-null-optimization.html)
* ChatGPTに確認すると、NULLが多い場合は検索効率に影響をきたすかも。的な回答もらった
    * ググってみた → [MySQLのインデックスの貼っていいとき悪いときを原理から理解したいよ😭](https://zenn.dev/canalun/articles/all_about_mysql_index)

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

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