概要
SQLの検索条件に IS NULL や IS NOT NULL を使用する際にはインデックスは使用されないというものだと思ったが、実際にはインデックスが使用されるようなので確認してみた
環境
- mysql 8.0.23
テストデータの作成
- title（インデックスあり）と body（インデックスなし）のカラムを作成し、両者とも同じ値を格納
- ストアドプロシージャを使用してテストデータを作成
- title・bodyカラムに入れる値は1から始まり、10件数ごとにnull値を入れることを繰り返して100000レコード作成する
mysql> CREATE DATABASE sample character SET utf8mb4;
mysql> CREATE TABLE messages (
id INT AUTO_INCREMENT PRIMARY KEY,
title varchar(40),
body varchar(40),
index(title)
);
mysql> DELIMITER //
mysql> CREATE PROCEDURE create_test_data(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 messages(title, body) VALUES(v, v);
END WHILE;
END
//
mysql> DELIMITER ;
mysql> CALL create_test_data(100000);
テーブルの中身
mysql> SELECT * FROM messages limit 10;
+----+-------+------+
| id | title | body |
+----+-------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
| 8 | 8 | 8 |
| 9 | 9 | 9 |
| 10 | NULL | NULL |
+----+-------+------+
10 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM messages;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(*) FROM messages WHERE title IS NULL;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM messages WHERE body IS NULL;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.03 sec)
IS NULL
インデックスなしの場合
- typeはALL、rowsは 約100000 件のためテーブルフルスキャンが行われている
mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE body IS NULL;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | messages | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
インデックスありの場合
- typeはrefでpossible_keysにtitle、ExtraにもUsing indexとあり、indexが使用されていることが確認できる
- rowsは 10000 件であり、テーブルに存在するNULLレコードの件数と一致する
mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE title IS NULL;
+----+-------------+----------+------------+------+---------------+-------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+-------+----------+--------------------------+
| 1 | SIMPLE | messages | NULL | ref | title | title | 163 | const | 10000 | 100.00 | Using where; Using index |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
IS NOT NULL
インデックスなしの場合
- typeはALL、rowsは 約100000 件のためテーブルフルスキャンが行われている
mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE body IS NOT NULL;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | messages | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 90.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
インデックスありの場合
- typeはrangeでpossible_keysにtitle、ExtraにもUsing indexとある
- rowsは 50000 件
- どのように探索範囲を決めているかわからなかったが、IS NOT NULL が検索条件にある際もインデックスが使用されていることが確認できた
mysql> EXPLAIN SELECT COUNT(*) FROM messages WHERE title IS NOT NULL;
+----+-------------+----------+------------+-------+---------------+-------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+-------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | messages | NULL | range | title | title | 163 | NULL | 50040 | 100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
MySQL以外のDBについて
- 実際に確認したわけではないですが、MySQLに限らず、PostgreSQLでも検索条件にNULLを指定した際にはインデックスが使用されているらしい
PostgreSQL
インデックスの付いた列に対するIS NULLやIS NOT NULLでもB-treeインデックスを使用することができます。