パフォチュー・コンサル日記その２

パフォチュー・コンサル日記その２

前回は、実態その壱として”インデックスは主キーだけ”を書きました。その
最後にも少し触れていましたが今回は、実態その弐 “カーディナリティが低い
インデックス”をお送りいたしましょう。

まずはカーディナリティとは何か？日本語にすると”集合の要素の数・基数”と
いうことになります。実際の意味合いを簡単にいってしまえば、どれだけのキー
の種類があるか、キーの偏りはないかといったことです。キーの種類が多い場
合を”カーディナリティが高い”、キーの種類が少ない場合を”カーディナリテ
ィが低い”と表現します。

問題:社員マスターの社員コードはどっち？

社員コードは普通、全て一意性があるので”カーディナリティが高い”です。
では、社員マスターの性別はどっち？
簡単すぎますね。男と女だけなんで当然の如く”カーディナリティが低い”です。

ではちょっと捻って質問です。上の社員マスターを持つ企業は日本の企業です。
しかも日本だけにあります。このとき、社員マスターにある国籍のカーディナ
リティはどっち？

この場合、国はたくさんあります(ソルトレーク・オリンピックに参加した国
と地域で確か80位はあったと思います)。しかし、国籍はほとんど日本になる
と思います。このようなケースでは種類がたくさんあっても、偏りがひどいの
で”カーディナリティが低い”です。

さて、”カーディナリティが低いインデックス”の本題に入りましょう。SQLを
作成して動作状況(アクセスパス)をチェックすると思います。アクセスパスを
チェックすることによってそのSQLがインデックスを使用して正しく(最適な)
稼動しているかを見るのがチューニングの一つです。以下に実例を示します。

SQL文:

DELETE FROM TABLE_A
WHERE COL1=10 AND COL2='A' AND COL3='Y' AND COL4='NO0121'

アクセスパス:(0)DELETE STATEMENT (Cost=4)
             (1)-- TABLE_A から削除(DELETE)
             (2)----UNIQUE索引 TABLE_A_IDX(複数行)を使用(RANGE SCAN)
                       (Cost=4 Card=1 Bytes=81 )

INDEX:TABLE_A_IDX ON TABLE_A(COL1,COL2,COL3,COL4)

といったSQL・アクセスパス・INDEXがあるとします。ぱっと見た感じはユニー
クインデックスを使用して削除処理を行っているので非常にいいSQLが作成で
きた様に見えます。でもこれだけの情報で最適なSQLができたことにはならな
いのです。実際のところこのケースでは数行を削除するのに700ブロックも読
み込んでいたのです。では何がいけないのでしょう？

SQLのパフォーマンスを向上させる為に一番重要なことは何かを考えてください。
そうです、いかにI/Oを少なくして求める行(レコード)を取得できるかです。
SQLは同じ結果を取得する場合でも色々な書き方ができます(これがSQLの利点
でもあり欠点でもあるのです)。データベース・エンジンの内部で1行を返すた
めに全件検索をしている場合もあれば、数件(正確には数ブロック)で1行を返
す場合もあります。

SQLのチューニングのチェックでは何を見ればいいのかです。上に挙げた情報
は必ず見る必要があります。それに加えてそのSQLがどれだけのDBブロックを
読み込んだかに注目する必要があるのです。

上の例では700ブロックも読み込んで数行を削除しています。どう考えても変
ですよね。では、なにが変なんでしょう。上の情報から疑わしい点を見つけま
しょう。

疑わしいのがどこか判りますでしょうか？答えは取得結果が複数行である事で
す。インデックスを複数行読んで処理をしていますよね。複数行の定義は最小
で2行、最大で全件の範囲になることに気づいて頂けましたでしょうか。そう
です、上のSQLはインデックスを限りなく全件に近い複数行を読み込んでいる
のです。RANGE SCANは曲者です。

では、なぜこのようなことが起こるのでしょうか。やっと本題に入ります。上
のSQLでは実際COL3をで条件指定しています。つまりインデックス TABLE_A_IDEX
はCOL2まででRANGE SCANを行っているのです。そしてCOL2までは非常にカーデ
ィナリティが低いインデックスでした。(実際はCOL2までで2種類でした・・・
つまり33%はインデックスを読んでいるのです)
また、SQLを見ていただければ判ると思いますが COL3=’Y’ が多分 ‘Y’ か ‘N’
がCOL3には設定されそうです。COL4は番号のように見えますので種類は多そう
です。そうです、このインデックスはCOL4によってカーディナリティが高まっ
ているのです。だからCOL4までを正しく条件設定して有効なインデックスとな
るのです。(COL1からCOL3は=条件で設定しなければ意味の無い状態です)
逆にいえば、COL4まで条件設定されてしまい、SQLでこの TABLE_A_IDX が使用
されたらパフォーマンスに負荷をかけてしまうことになるのです。

ではカーディナリティの見方です。とっても簡単です。
SQLに注目した場合(RANGE SCANしている)、使用されているインデックスで条件
に指定されている(インデックスの利用できる書き方をしていることが絶対条件
です)項目でチェックします。とりあえず上の SQL で例を書きます。

SQL>SELECT COUNT(*) from TABLE_A;
    (全件件数を確認)
SQL>SELRCT COL1,COL2,COUNT(*) FROM TABLE_A GROUP BY COL1,COL2;
    (キー種類数を確認)

このSQLでカーディナリティをチェックします。

キー種類数 / 全件件数 = カーディナリティ
この時の結果が30%以上のような場合は、意味の無い(負荷の低減どころか、負
荷を増加させている)インデックス利用といったことになります。

以上、今回もパフォチューコンサル日記はきっちんイーターがお届けしました。

パフォチュー・コンサル日記 その２