なぜSQLの"SELECT *"が遅くなるか
この記事を読みつつ、色々調べたことのまとめになります。間違い指摘・補足頂けたら有難いです。
導入
- PostgreSQLでは、ページをフェッチして共有バッファプールに配置すると、そのページ内のすべての行と列にアクセスできる。
RDBMSはページまたはブロックと呼ばれる決まった大きさ（以下、ページ）の単位ごとにデータを読み書きします。RDBMSのページのイメージは図1のようになっています。...この図を見ればわかると思いますが、RDBMSのI/Oの最小単位はレコード単位ではなくページ単位なのです。なお、MySQLの場合、デフォルトのページサイズは16KBとなっている
1: インデックスの利点を生かせない
- あるカラムにインデックスが張られているとき、すべてのフィールドを要求すると、ほかのカラムを取得するためにヒープデータページにアクセスする必要があり、ランダム読み取りが増加し、I/Oが大幅増加してしまう。
各インデックスはテーブルの主要なデータ領域（PostgreSQLの用語ではテーブルのヒープと呼ばれます）とは別に格納されています。 このことは、通常のインデックススキャンにおいて、各行の検索にはインデックスとヒープの両方からデータを取得する必要があることを意味します
SQLクエリで参照しているカラムに一つでもインデックスされていないデータが含まれているかが問題で、*を使う場合はそれが明らかであるということみたいですね。
2: 逆シリアル化のコストがある
- データベースからバイト列を取得し、プログラムのデータ型に変換するコストがある。
3: サイズの大きいデータ型
- TEXT形やBLOB形などの大きなカラムは外部テーブルに圧縮して保存され、要求したときのみ取得するように内部的になっているため、そのフェッチと解凍のコストが追加される
PostgreSQL
TOAST(過大属性格納技法)によって、固定長のページサイズを使用し、大規模なフィールドを直接格納しないような仕組みになっている。7.1以前は、テーブルの一行に格納できるデータの総量は1ページ以内という制約があった。
MySQL(InnoDB)
長すぎてBツリーページに収まらない可変長列は、オーバーフローページと呼ばれる、個別に割り当てられたディスクページに格納されます。このような列はオフページ列と呼ばれます。
4: ネットワークコスト
ネットワーク通信でも同様に通信プロトコルにおけるシリアル化のコストが増える。
5: データの予測不可能性
テーブルにカラムが元々一つしかなかった場合でも、あとから他のカラムを追加すると、コードが変わっていないにもかかわらず、それを追加で取得する分遅くなる
Discussion