Amazon Redshift DB開発者ガイド – テーブル設計のベストプラクティス
データベース設計を考える上で、あなたが下さなければならない重要な決定があります。その決定はクエリパフォーマンス全体に影響を与える可能性があります。これら設計に関する決定はまた、I/Oオペレーションの数を削減したりクエリを処理するのに必要なメモリを最小化する事でクエリのパフォーマンスに影響を与えるストレージ要件に大きな影響を及ぼします。
テーブル作成の際にクエリのパフォーマンスに最も大きな影響を与えるであろう決定次項は以下のとおりです。
- 最善のソートキー(sort key)を選択する
- 最善の分散キー(distribution key)を選択する
- 最善の圧縮戦略(compression strategy)を選択する
- 制約を定義する
あなたが下す決定は、データベースが行なっている作業の種類に依存して来ます。全ての状況に効果的な『最高のソートキー』は無いのです。
このセクションでは最も重要な設計上の決定をまとめ、クエリのパフォーマンスを最適化する為の『ベストプラクティス』をご紹介します。その後のセクションでは、より詳細な説明やテーブルの設計オプションについて例示して行きます。
目次
- 最善のソートキー(sort key)を選択する
- 最善の分散キー(distribution key)を選択する
- 制約を定義する
- 行のサイズは可能な限り小さく
- 日付列には日付/時刻データ型を用いる
- ソート列に冗長な述語を指定する
最善のソートキー(sort key)を選択する
Amazon Redshiftは、ソートキーに従ってソートされた順序でディスク上にデータを格納します。Amazon Redshiftクエリオプティマイザ(Optimizer=クエリの最適化機構)は最適なクエリプランが決定された時に、そのソート順序を使用します。
直近のデータが最も頻繁に照会されている場合、ソートキーとしてタイムスタンプ列を指定します。クエリは時間の範囲外のブロックをスキップする事が出来るので、クエリがより効果的なものとなります。
もし頻繁にある列で範囲のフィルタリング、または同値かどうかのフィルタリングを行う場合、ソートキーとしてその列を指定します。各ブロックに格納されている最小値と最大値の値を追跡し、指定の範囲外に適用されないブロックをスキップする事が出来るので、Amazon Redshiftはその(範囲外となった)列のデータのブロック全体を読み飛ばす事が出来ます。
頻繁にテーブルを結合するような場合は、ソートキーと分散キーの両方をその結合列に指定します。これにより、クエリオプティマイザがハッシュ結合の代わりにソート結合を選択出来るようになります。既にデータが結合キーでソートされているので、クエリオプティマイザはソートマージ結合のソートフェーズをバイパス(迂回、避けて通る)する事が出来ます。
ソートキーの選択・指定の詳細については、Choosing sort keysをご参照ください。
最善の分散キー(distribution key)を選択する
分散キーは、どのようにテーブルのデータを計算ノードに分散させるかの方法を決定します。
良いデータ分散には、以下2つの目安があります。
- クラスタ内のノードとスライス間で均等にデータを分散させる：偏在、またはデータ分散が歪んだ(skew)状態だと、幾つかのノードはダウンし、全体のプロセスを遅くさせ、却って多くの作業を強いてしまいます。
- データ移動を最小化する為に、結合するためのデータを連結する。
頻繁にテーブルを結合する場合は、分散キーとして結合列をしていします。テーブルが復数の他テーブルと結合する場合、テーブルと結合する最も大きなディメンションの外部キーを分散キーとして指定します。(※商品マスタなら、商品名や商品群など、分析の切り口となるマスタデータを含むテーブル)
もしディメンションテーブルが結合の一部としてフィルタリングされる場合、最も大きなディメンションの選択時にフィルタリングした後でデータのサイズを比較します。これで最も大きな結合に関わった行は、一般的に同じ物理ノードに配布されるようになります。ローカル結合ではデータ移動を回避しているのでネットワーク結合よりもパフォーマンスが向上します。
主に列に等式フィルタを使用している場合、マッチする全てのデータが単一のノードに格納されてしまうので分散キーにその列を指定しないでください。その列をソートキーとして指定している場合、状況は更に悪化します。全ての処理は、そのノード内の単一スライスに集中してしまいます。
テーブルが非正規化されており、結合に関与していない場合は分散キーは指定しないでください。Amazon Redshiftは、均等にラウンドロビン方式でノード間分散を行います。
分散キーの選択や指定の詳細についてはChoosing a data distribution methodをご参照ください。
制約を定義する
主キーとテーブル間の外部キーは適切な場所に定義しましょう。それらはキー情報という側面の他に、クエリオプティマイザがそれら制約を使用してより効率的なクエリプランを生成しています。
Amazon Redshiftは一意キー、主キー、及び外部キーの制約を強制しません。アプリケーションは一意性を確保し、DML操作の冪等性(べきとうせい＝ある操作を1回行っても複数回行っても結果が同じであることをいう概念)を管理する責任があります。
Amazon Redshiftがどのように制約を使用しているかに関する追加情報は、Defining constraintsをご参照ください。
行のサイズは可能な限り小さく
列のサイズを可能な限り小さくする事により、クエリのパフォーマンスは向上します。
Amazon Redshiftがとても効率良く列データを圧縮するので、列を必要以上に大きく作ってしまうとデータテーブルのサイズに少なからず影響を与えてしまいます。
しかしながら、複雑なクエリの処理時にはクエリの中間結果は一時テーブルに格納する必要性が出てくるかも知れません。ただ一時テーブルは圧縮されていない為、不必要に大きな列はメモリやクエリのパフォーマンスに影響を与える一時ディスク領域を消費してしまいます。
利便性の為に、最大列サイズを使って練習するような事は避けてください。その代わり、VARCHARカラムに格納する可能性がある最大値を考慮し、それに応じてカラムのサイズを調整しましょう。
日付列には日付/時刻データ型を用いる
Amazon Redshiftはより良いクエリパフォーマンスで結果を出すCHARやVARCHARよりも効率的に、DATEやTIMESTAMPデータを保存します。
日付/時刻の情報を格納する際は、必要とする解像度に応じて、文字列型よりむしろDATE又はTIMESTAMP型を使用していきましょう。詳細については、Datetime typesをご参照ください。
ソート列に冗長な述語を指定する
結合の際は、ファクトテーブル、または大きなテーブルの主要なソート列に対して、述語(predicate)を使いましょう。述部が冗長である場合でも、結合に使っている他のテーブルをフィルタリングするために述語を追加します。
スタースキーマ *1もしくは類似の設計(大きなファクトテーブルが復数のディメンションテーブルに結合されている形)で、最も大きなテーブルのソート列をフィルタリングする為にWHERE句で述語を追加する時は、クエリプランナはディスクブロックの多数のスキャンをスキップさせる事が出来ます。フィルタ無しで、クエリ実行エンジンはテーブルが大きくなるにつれて時間を掛けてクエリパフォーマンスが低下しているテーブル全体に対してスキャンする必要があります。
述語が既に結合クエリでテーブルの上に適用されますが、推移的述語内の列でソートされたクエリで別のテーブルに適用された場合でも、冗長な述語を指定する事により、パフォーマンスを向上させる事が出来ます。そうすれば、他のテーブルをスキャンする際、Amazon Redshiftは効率的にだけではなく、そのテーブルからブロックをスキップさせる事が出来ます。
例えば、TAB1とTAB2を結合させたいとします。TAB1の為のソートキーはtab1.timestampであり、TAB2のソートキーはtab2.timestampです。 以下のクエリではテーブルを共通のキーで結合し、tab1.timestamp > '1/1/2013'でフィルタリングしています。
SELECT * FROM tab1, tab2 WHERE tab1.key = tab2.key AND tab1.timestamp > '1/1/2013';
もしWHERE句がtab2.timestampに対する述語を含んでいない場合、実行エンジンはテーブル全体をスキャンする事を強制されます。もし結合の結果がtab2.timestampからの値に成り得る場合、また、結果が1/1/2013より大きい場合、それが冗長であっても、そのフィルタを追加します。
SELECT * FROM tab1, tab2 WHERE tab1.key = tab2.key AND tab1.timestamp > '1/1/2013' AND tab2.timestamp > '1/1/2013';
