Primary Indexは一意に近いほどよい、わけではない
Teradataはいわゆるshared-nothingアーキテクチャを採用していて、1つのテーブルのデータは、AMPと呼ばれる数十台の仮想マシンに分散して格納される。
Teradataは各レコードのキーすなわちPrimary Index(=PI)をハッシュ関数に食わせて、出てきた値からそのレコードを格納するAMPを決める。PIの値が同じレコードは必ず同じAMP(の、極力同じブロック)に配置されることになっている。
よって、PIの値が十分にばらけていないと、各AMPが持つデータ件数が偏り、テーブルスキャンの性能が下がるなどの悪影響が出る。
例えば出荷伝票テーブルのPrimary Indexが都道府県コードで、なおかつ出荷先の5割が東京都だったら、東京のデータを持つAMPにデータが偏って配置されてしまう。
逆にPIの値が分散するほどに、各AMPのデータ件数は均一に近づいていき、効率よく並列処理できるようになる。
今日は何の話をしたいのかというと、だからって複合キーを作ってまでPIを一意に近づけようとしてはならない、ということだ。
3億件のテーブルのPrimary Indexが顧客コード1列だけになっているのを指して「データに偏りが出ているはずだからPIを { 日付, 顧客コード, ... } にして一意にするべきだ」みたいな話してる人が居たんだけど、そんなことして大丈夫ですか、という話だ。
何がまずいかというと、PIを複合キーに変えることで倍以上遅くなってしまうクエリがあるかもしれないのだ。
TeradataのJOIN処理は各AMPで独立して行われる。そして、結合されるデータのペアは必ず同じAMP上になくてはならない。
だから、顧客コードをPIとする10万件の顧客マスタに、200万件の出荷伝票を結合するクエリ：
select count(*) from 顧客マスタ c /* 10万件 */ inner join 出荷伝票 t /* 200万件 */ on t.顧客コード=c.顧客コード
は、
- 出荷伝票のPIも顧客コードだけなら、結合相手のレコードが必ず同じAMP上にあるので、すぐに結合処理を開始できる
- 出荷伝票のPIが { 顧客コード, 日付 } だったら、結合相手のレコードは必ずしも同じAMP上にない。だから、200万件の出荷伝票を顧客コードに基づいて全AMPに再配置してから*1結合処理をしなくてはならない
ということになる。
1.は200万件を1回だけ読めばよいが、2.だと200万件を 読む->中間ファイルに書く->それをまた読む の3ステップで処理しなくてはならない。
実際、PIの定義だけが異なる200万件のテーブルを2本作ってexplainしてみると、予測実行時間が倍以上違う。
実行計画が違うところだけ切り出すとこんな感じ：
顧客コードだけがPI
4) We do an all-AMPs JOIN step from TST.c by way of a RowHash match
scan with no residual conditions, which is joined to TST.t by way
of a RowHash match scan with no residual conditions. TST.c and
TST.t are joined using a merge join, with a join condition of (
"TST.t.kcd = TST.c.kcd"). The result goes into Spool 3
(all_amps), which is built locally on the AMPs. The size of Spool
3 is estimated with low confidence to be 2,075,377 rows. The
estimated time for this step is 3.78 seconds.
日付＋顧客コードがPI
4) We do an all-AMPs RETRIEVE step from TST.c by way of an all-rows
scan with no residual conditions into Spool 4 (all_amps) fanned
out into 4 hash join partitions, which is built locally on the
AMPs. The size of Spool 4 is estimated with high confidence to be
277,832 rows. The estimated time for this step is 0.64 seconds.
5) We do an all-AMPs RETRIEVE step from TST.t by way of an all-rows
scan with a condition of ("NOT (TST.t.kcd IS NULL)") into
Spool 5 (all_amps) fanned out into 4 hash join partitions, which
is redistributed by hash code to all AMPs. The size of Spool 5 is
estimated with high confidence to be 2,035,056 rows. The
estimated time for this step is 4.49 seconds.
6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a hash join
of 4 partitions, with a join condition of ("kcd = kcd").
The result goes into Spool 3 (all_amps), which is built locally on
the AMPs. The size of Spool 3 is estimated with low confidence to
be 2,035,056 rows. The estimated time for this step is 2.47
seconds.
Teradataの奇妙な実行計画の読み方についてはいずれ書きます、ということで説明は省略させていただいて、ポイントは太字の部分。これは200万件のデータを全AMPに配置し直すことを示している。
TeradataでSQLのパフォーマンスチューニングをする場合、この再配置をどうやって殺すかがポイントになる。
PIはテーブルごとに1回しか指定できないので、大事に使わなくてはならない。
結合条件に使われないような複合キーをPIにしてしまうと、あらゆるJOINで再配置やファンアウト*2といった重い処理が発生する。このことへの手当てを考えずにPIを複合キーにしてはならない。
