RDBMS のインデックスの話

以前どっかで読んで、「なるほど〜」「でも必ずしも当てはまるとは限らないんじゃない」と思ったこと。

プライマリキーは HDD 上にシーケンシャルに保存されることが期待できるけど、他のインデックスはシーケンシャルになることは少ない (基本的にそうならない)。よって、HDD に格納されているデータを使って集約 (range) 演算を行う場合はプライマリキーを使うケースのほうがセカンダリインデックスを使うよりも速くなる

実際には、「プライマリキーはシーケンシャルに保存されることが期待」できるのは

• データの削除が発生しない
• データの追記が発生するテーブルが、ディスクに１個しかない
• プライマリキーは auto_increment カラムである

の３条件が満たされないとダメなんじゃないかなぁ。ただ、ディスク配置も含めてスキーマ設計あるいは SQL の最適化をすべきケースはあるだろうなと思った。

追記: メモリの場合でも TLB とかで似たような話があったりするんですかね。誰か試してないかなぁ

追記2: 自分の環境だと、InnoDB の場合、HDD のインデックスがシーケンシャルであることを期待できない場合、HDD I/O の速度は 2-3 MB程度になりるようです。シーケンシャルな場合とでは、オーダーが変わるんですね。