DB2-SQLチューニング　想定通りのインデックスに当たらない時に確認すること #SQL

DB2でSQLチューニングをしていた際にはまったことを書き溜めていきます。

SQLチューニングをいきなり任されて全然わからん、インデックスを作ったはいいが全然当たらないよ！、という方のお役に立てればと思います。

※もし、情報が間違っていたらご指摘いただけると幸いです。

Q.そもそもインデックスとは

索引のこと。本の冒頭に書かれている目次を想像してもらえればいいです。

Q.インデックスにはどんな役割があるのか

データに高速でアクセスができる。
どういうことかというと、
例えば、あなたはある本の中から必要な情報を見つけようとしています。
目次がなかった場合、最初のページから必要な情報があるページまですべてに目を通す必要があります。
これでは物凄い時間と労力がかかってしまいます。

これに対して目次がある場合はどうでしょうか。
下記のような目次があるとします。

生きる方法
1. 食べる
2. 寝る
3. あきらめる
稼ぐ方法
1. 投資をする
2. スキルアップする
  1. 資格を取る
  2. 講習に参加する

もし”資格を取る”という情報が欲しい場合は”稼ぐ方法 - スキルアップをする - 資格を取る"というように辿れば、短時間かつ労力を使わずに必要な情報にたどり着けます。

これと同じでデータベース（本）にアクセスし、必要な情報（資格を取る）を取得する際に、インデックス（目次）を使用することで高速（短時間）かつCPU負荷（労力）を抑えて取得することができます。

Q.インデックスの作り方

基本的には上で説明したような目次を作っていけばいいです。
例えば、下記のようなSQLがあったとします。

findMember.sql

SELECT TC.MEMBER_NAME FROM T_COMPANY TC 
 WHERE TC.COMPANY = ? 
  AND TC.BUSHO = ?
  AND TC.MEMBER_BIRTHDAY >= ?
  AND TC.MEMBER_BIRTHDAY <= ?
;

各項目のデータバリエーション数は下記の通りとします。
- 全レコード件数：100,000
- COMPANY(会社名)：10,000
- BUSHO(部署名)：20
- MEMBER_BIRTHDAY：18,250(50年分)

この場合は、データがより絞れる順 = データバリエーションが多い順に項目を並べたインデックスを作成すればいいです。
・INDEX(COMPANY,BUSHO,MEMBER_BIRTHDAY)

このインデックスにより、データの絞り込みは下記のように進みます。
100,000 × (1/10,000) × (1/20) × (xxx/18,250)
※日付は入力値に依る

Q.インデックスを作ったけど当たらないんだけど！

インデックスが当たらない原因はいくつかありますが、ざっと以下の通りです。（他にもあるかもしれません）
①データバリエーション、データ件数が少ない
②データの作り方が悪い
③絞り込まれる順にインデックス項目が並んでいない
④インデックス項目が多すぎる

ひとつずつ解説していきます。

①データバリエーション、データ件数が少ない

データのバリエーションが少ないとインデックス項目が当たった時に絞り込める割合も必然的に少なくなります。
先ほどのSQLの例で行けば、COMPANY(会社名)の種類が3つしかない場合は、全体の33%にしかなりません。この絞り込める割合が小さいとDB2が「インデックス使っても大して絞り込めないから、使わなくていいや」と判断し、使われなくなります。
インデックスが使われるかどうかはDB2しかわかりませんが、目安として全体の25%以下に絞り込めると使用されるみたいです。

②データの作り方が悪い

インデックス項目で絞り込んでいった時に、データバリエーションが少なくなると当たらなくなります。例えば下記のようなデータを作成した場合です。
・理想データ：COMPANY(10,000種類)で絞り込んだ時点でBUSHOが20種類存在。
・ダメなデータ：COMPANY(10,000種類)で絞り込んだ時点でBUSHOが2種類存在。(本来は20種類あるはず)
ダメなデータで作成した場合はCOMPANYで絞った時点で2種類しかないので50%にしかならず、BUSHOというインデックス項目にはヒットしません。

なので、インデックス項目全部に当てたい場合は絞り込まれた時点でもデータ種類が多く存在するデータを作成する必要があります。
もちろん、絞りこまれた時点で2種類になるのがデータとして正しい、という場合は問題ありませんが、インデックス項目を見直す必要があります。

③絞り込まれる順にインデックス項目が並んでいない

これはそのままの意味です。
DB2のオプティマイザは"より絞り込まれる項目を選ぶ"という性質があるため、その通りにインデックス項目が並んでいないと、うまく当たらないことになります。

④インデックス項目が多すぎる

インデックスの項目が多すぎると当たりにくくなるみたいです。これには私も驚きでした。
例えば下記のような場合は当たりません。
当てたい項目:COMPANY,BUSHO
想定：INDEX(COMPANY,BUSHO,BUSHO_SUB,YOSAN,YOSAN_SUB,MEMBER_BIRTHDAY)
実際：INDEX(COMPANY,MEMBER_BIRTHDAY)

上記のように項目が多いとダメで下記のように項目を減らすと当たります。
INDEX(COMPANY,BUSHO,MEMBER_BIRTHDAY)

以上です。

DB2-SQLチューニング 想定通りのインデックスに当たらない時に確認すること