こんにちは！アイスタイルのDBA、suzukitoです。
DBの構築、運用、レビューなど担当しています。DBに関係する質問に日々回答中です。
先日こんな質問がありました。
IS NULLってインデックス使いますか？
答えはYES。
実装依存かも知れませんが、MySQL、SQL Server、PostgreSQLは使います。
試してみました
実際に実行計画を見てみましょう。
インデックス作成前はテーブルスキャンでしたが、作成するとINDEX SCANに変わります。
検証内容
NULL許可された列を検索するクエリで、インデックスを作成する前後の実行計画を比較する。
レコード総数約22万件
検索対象列ほとんどの値はNULLが入っており、NULLではないレコード数は約6千件。
テーブル定義
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`created_at` datetime(6) NOT NULL,
`created_by` int(11) NOT NULL,
`updated_at` datetime(6) NOT NULL,
`updated_by` int(11) NOT NULL,
`updated_type` varchar(16) DEFAULT NULL,
`deleted_at` datetime(6) DEFAULT NULL,
`deleted_by` int(11) DEFAULT NULL,
`deleted_flag` tinyint(3) unsigned NOT NULL DEFAULT
PRIMARY KEY (`id`)
検証SQL
is null
SELECT COUNT(*)
FROM products
where deleted_at is null
is not null
SELECT COUNT(*)
FROM products
where deleted_at is not null
作成するインデックス
CREATE INDEX IX_products_deleted_at ON products (deleted_at)
検証結果
is nullもis not nullもインデックスが使用されました。
MySQLの実行計画
Full Table ScanからNon-Unique Key lookup、Index Range Scanに変化しました。
インデックス作成前
is null
インデックス作成後
is null
is not null
SQL Serverの実行計画
SQL Serverの場合、Clustered Index ScanがFull Table Scanの事になります。
Clustered Index ScanからIndex Seekに変化しています。
インデックス作成前
is null
インデックス作成後
is null
is not null
質問の背景
NULLは値じゃないのにインデックスにどうやって格納するのかな？
って疑問が前提にあるのだと思われます。
あとはこの記事の影響でしょうか。
第7回 性能改善の鍵，インデックスの特性を知る～B-treeとハッシュ （1）B-tree ：SQLアタマアカデミー｜gihyo.jp … 技術評論社
また一般的に，B-treeはNULLをキー値として保持しないため，IS [NOT] NULLを指定した場合もインデックスは使われません（リスト3）。
NULLはインデックスのどこに格納されるのか
NULLとの大小比較はNULLになるため、Bツリーの先頭か末尾のどちらかでしょう。実際、PostgreSQLのCreate IndexにはNULLの格納場所に関するパラメータが存在します。
CREATE INDEX – PostgreSQL 10.5文書
NULLS FIRST
NULLを非NULLより前にソートすることを指定します。 これはDESCが指定された場合のデフォルトです。NULLS LAST
NULLを非NULLより後にソートすることを指定します。 これはDESCが指定されない場合のデフォルトです。
このことから、NULLと非NULLの割合に偏りがある場合の少数派の検索は、インデックスが有効に機能すると考えられます。
公式ドキュメントも確認しました
動作確認も大切ですがドキュメントの裏付けも大切です。
どのDBMSも公式ドキュメントが充実しているので見てみましょう。
MySQL
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.8 IS NULL の最適化
MySQL は、col_name = constant_value に対して使用できる同じ最適化を col_name IS NULL に対しても実行できます。たとえば、MySQL は、インデックスと範囲を使用して、IS NULL を含む NULL を検索できます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.8 B ツリーインデックスとハッシュインデックスの比較
col_name IS NULL を使用した検索では、col_name にインデックスが設定されている場合にインデックスが使用されます。
SQL Server
SQL Server のインデックスのアーキテクチャとデザイン ガイド | Microsoft Docs
これはIS NOT NULLで検索する場合、かつ、IS NOT NULLでフィルター化されたインデックスの例なのでハッキリとは書かれていませんが、フィルター化しないとNULLはインデックスに含まれるよって読み取れます。
PostgreSQL
11.2. インデックスの種類 – PostgreSQL 10.5文書
インデックスの付いた列に対するIS NULLやIS NOT NULLでもB-treeインデックスを使用することができます。
まとめ
NULLはインデックスを使わないって書かれた記事もブログもたくさん見かけます。
過去にはそうだったのかも知れません。
でも、実装はどんどん変わって行きます。
過去に書かれたブログだけを読んでいると、今自分の使っている実装系とは合っていないって事もしばしばあります。
やっぱり自分で確かめないとですね！
Enjoy! Database Life!
