人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

一般的なデータベースの本を読むと、
インデックスの中にNULLは含まれず、NULL述語を指定した検索はテーブルスキャンが実行されるそうです。
しかし、Microsoft SQL Server 2000やMySQL(InnoDB)で実際に検証したところ、
インデックスシークが行われているようでした。

下記の点について教えてください。

1. このような本の記述が正しいのか、
2. また、Oracleについてはそういえるのか、
3. そして、なぜそのような実装になっているか

●質問者: witt
●カテゴリ:コンピュータ
✍キーワード:innodb Microsoft MySQL Oracle SQL Server
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● ohmix1
●27ポイント

中途半端な回答で申し訳ありませんが...


>1. このような本の記述が正しいのか、


たぶん、概念的には正しいものと思われます。

なぜなら、NULLは\\\"値\\\"ではなく\\\"不定\\\"だから。


根本的には、

WHERE ID = NULL でなく、

WHERE ID IS NULL なのが理由になると思われ....説明できません。orz


詳しくは、以下の「NULLにまつわる諸問題」あたり。

http://www.geocities.jp/mickindex/database/idx_database.html




>2. また、Oracleについてはそういえるのか、


http://otn.oracle.co.jp/document/products/oracle9i/920/general.h...


Oracle9i データベース・パフォーマンス・チューニング・ガイドおよびリファレンス リリース2(9.2)


によると以下のような場合は全表スキャンになるようです。


>全表スキャンは、このリストの中で最も順位の低いアクセス・パスです。つまり、全表ス

>キャンの方が高速で実行できるとしても、索引を使用するアクセス・パスが使用可能であれ

>ば、RBO は常に索引を使用するアクセス・パスを選択します。

>次の条件は、索引アクセス・パスを使用不可にします。


... 中略 ...


> 列IS NULL

> 列IS NOT NULL

> 列NOT IN

> 列 != expr

> 列LIKE \\\'%pattern\\\'

>列に索引が作成されているかどうかは関係ありません。




>3. そして、なぜそのような実装になっているか


なぜ、索引シークが可能なDBがあるかは、NULLを値のようにあつかうような実装をしているのではないかと...


◎質問者からの返答

データベースの本は中立を装っていても、どうしてもOracleの実装を前提に書かれているので本質問をさせていただきました。

ohmix1さんの次のように回答されたように、

>>3. そして、なぜそのような実装になっているか

>なぜ、索引シークが可能なDBがあるかは、NULLを値のようにあつかうような実装をしているのではないかと...

このような実装が素直で納得ができるのですが、対して、Oracleの実装が素直に感じられず、なぜこうする理由があるのか知りたいです。


2 ● b-wind
●27ポイント

1.正しい

NULL は値ではなく不定値であり、SQLにおいて3値論理を実現するものだからです。

http://codezine.jp/a/article/aid/532.aspx

つまり特定の値ではないので

NULL = NULL

は偽です。

インデックスは特定の値からレコードを導き出すものなので本来は作成しようがありません。

たとえばインデックスの作成に良く使われる B-Tree では構築と検索処理に値の大小が深く関係しています。

http://www.mars.dti.ne.jp/~torao/program/structure/btree.html

NULL の大小比較は不可能なのでインデックスに入れられません。

理論上は、ですが。


2.YES

http://www.atmarkit.co.jp/fdb/rensai/orasql07/orasql07_1.html

少々古い記事ですが、IS NULL での EXPLAIN の結果が載っています。


3.すいませんがわかりません。

確かに MySQL では IS NULL はインデックス検索可能なようでドキュメントにも記載があります。

http://dev.mysql.com/doc/refman/4.1/ja/is-null-optimisation.html

ただ、これはあくまで特定の実装における現象であり、RDBMS 全般に適用するのは不適切だと考えます。(あくまで理論として)

実用上はインデックスが活用される機会が増えるので、便利ではありますが。

◎質問者からの返答

>NULL = NULL

>は偽です。

3値論理なので、unknownですよね?

さて、ご回答を受けて熟考した結果、私の質問におかしなところが

あると思いました。

>インデックスの中にNULLは含まれず、NULL述語を指定した検索はテーブルスキャンが実行されるそうです。

前半は理論上の話で、後半は実装上の話。

違うレベルの話がごちゃまぜになっていて不適切でした。

そして、前半は正しく、後半は実装による、というのがご回答を受けての私の結論です。

MySQLのドキュメントについては非常にうなづけました。

確かな情報ありがとうございます。

実用上、そう最適化するのが適当だろうと思います。

自分の考え方に自信が持てました。


3 ● b-wind
●26ポイント

3についての考察。

あまり SQL Server については詳しくないのですが。


http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=632765&...

Index を使用する場合でも Index Seek と Index Scan の2種類あるようです。

ただ、このリンク先の回答では「ペナルティがある」との発言から Seek と Scan を逆に書いてしまっているように受け取れます。


これらの違いについては以下に説明が有りました。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=844899

http://www.sqlpassj.org/conf/session/pdf/A3.pdf


理屈から言えば IS NULL の検索は Index Scan になるはずだと思いました。

◎質問者からの返答

この件については、動作検証したところ、Index Seekになります。

リンク先の「ペナルティがある」との発言が疑問です。

Index Seekが可能な検索ならば、Index Scanにする意味がないと思いました。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ