概要
DBの read のパフォーマンスを改善したいとき、候補となる改善策をいくつか挙げたいと思います。
他の DB タイプへの移行(Amazon Redshift や Elasticsearch を使うとか)やテーブルスキーマの見直しなどは考えません。
候補
index
真っ先に思い浮かぶ選択肢で、最もシンプルで効果を発揮してくれるものです。
index が貼ってあるカラムを使った検索において、通常の探索よりも高速になります。index によって並び替えられた B-Tree を用意しておきます。検索時にはまず、B-Tree を探索して、該当するリーフノードを特定します。B-tree のリーフノードは、実データを持たず、実データへのポインタを持っているので、リーフノードを特定するだけでは、データの取得はできません。リーフノードが持つ実データへのポインタを使うことで、実データを取得します。この実データを取得する作業はディスクI/O です。
clustered index
物理的なデータ順序を利用している点で、通常の index と異なります。
リーフノードの特定までは同じです。そしで、リーフノードが持つ実データへのポインタを使って実データを取得するのも一緒なのですが、効率が異なります。clustered index において、実データは物理的に index の順番で並んでいます。なので、リーフノードが特定されたら、一回のディスクI/O でまとめてごっそり実データを取得可能です。一方、通常の index は、リーフノードが特定されたあとに、「リーフノード→実データ」のディスクI/O がデータの数だけ発生してしまうので、効率が悪いです。ディスクI/Oはパフォーマンス悪化の原因になりやすく、clustered index で大幅にパフォーマンスが大幅に改善することがあります。
物理的な順番なので、テーブル一つにつき一つの clustered index しか持てません。デフォルトでは PK が clustered index となっています。clustered index を PK 以外に貼ると、当然ながら PK の clustered index の恩恵が受けられなくなります。PK は頻繁に使われるカラムであり、clustered index が貼ってある効果は大きいと思うので、PK から clustered index をはがすときはしっかり検討する必要があります。集計のテーブルとかは、PK に貼ってあってもあまり意味がなかったりするので、別のカラムに貼ってもいいかもしれません。
index only scan (conveing index)
ディスクI/Oがパフォーマンスのボトルネックになり得る話をしましたが、index only scan はディスクI/O をゼロにする戦略です。取得したいカラムを全て index に含めます。こうすることで、リーフノードに到達した時点で、必要なデータがすべてある状態になります。なので、「リーフノード→実データ」のディスクI/O がなくなります。
この話を聞くと、conveing index にするために、index に含めるカラムを増やしたくなると思いますが、注意が必要です。そもそも、index は書き込みのパフォーマンスが落ちます。これは、convering index に限った話ではないですが、書き込みの負荷が高いのに、index を増やしすぎるのは注意しましょう。また、index が含むことのできるカラム数には上限があるので、それも気を付けましょう。
materialized view
集計テーブルを事前に作っておくイメージです。A テーブルと B テーブルと C テーブルを JOIN して...みたいな操作を毎回やってたら時間がかかるので、事前に集計したテーブルを作っておきます。materialized view は作成された時点でのデータを保持するので、リアルタイムで更新されていく実データとはずれてしまいます。なので、時々、refresh 操作が必要です。
materialized view ではなくて、普通に新しい集計テーブルを作ってしまうのも一つの方法です。集計には時間がかかるので、メインのテーブルの更新と同時にジョブを積んでおいて、非同期で集計テーブルを更新します。
partitioning
垂直パーティショニングと水平パーティショニングがあります。ほとんどの場合、水平パーティショニングです。水平パーティショニングは、ある基準に基づいて、テーブルを水平に分割します。例えば、一年ごとにテーブルを分割します。単純にレコード数を減らすことで、パフォーマンスを改善しようというものです。
(見たことないので、詳しくは知らない。すみません)
参考