アクセスプラン解析とインデックス作成
開発時に表のアクセスがインデックスを使ったアクセスになるように最適化する必要がある。
運用時にはインデックスと表領域と統計情報のメンテナンスが必要になる。
開発時には発行するSQLが最適化し、テーブルスキャンの変わりインデックススキャンが使われるように
表にインデックスを張らなければならない。
インデックススキャンがテーブルスキャンに変わることによって行ロックが表全体のロックにかわり
結果的にデッドロックが発生する確率が増加してしまう。
インデックスを使うメリットは
1)インデックスページはデータページより小さいのでディスクI/Oの回数が少なくて済む。
2)インデックスはすでにソートされているので、ORDER BY、GROUP BY、DISTINCTを使ったときに改めてソートする必要がない。
3)インデックスはすでにソートされているので、JOINの時にソートする必要がない。
SQLのアクセスプランを調べるツールにはEXPLAINがあり、Visual Explainはグラフィカルにアクセスパスを表示してくれる。
クラスター化
DB2はレコードをINSERTするときにインデックス値が同じデータページに
データを追加しようとする。
インデックスの値からデータページの場所が特定されるように配置するのである。
インデックスの値に沿ってデータページを配置することをクラスター化と呼んでいる。
アクセスプランは統計をもとに最適化される。
逆に統計情報がなければオプティマイザはデータがインデックスに対して
うまくクラスター化されてないと想定した値をデフォルト値に選んでしまうので
パフォーマンスは極端に悪くなる。
統計情報はDB2にとって必ず必要である。
表にインデックスを張ったあとは RUNSTATSコマンドを実行して統計情報を更新する必要がある。
RUNSTATS ON TABLE オーナ.表名 WITH DISTRIBUTION AND DETAILED INDEXES ALL;
インデックスのクラスター率の低下
最初にインデックススキャンだったものが時間の経過とともにテーブルスキャンになることがある。
インデックス値に沿ったデータページに値を追加できないときは
新しくデータページを割り振ってデータを
インデックスに対する表のクラスター率が低下するためである。
このような場合は表のREORGを掛ける必要がある。
REORGによってインデックスに従ったデータページのクラスター化が行われる。
運用時に表のインデックスのどれか１つは必ず１００％近い値になるように気をつける。
インデックスカバーリング
LIKE句、ORDER BY、JOINでは
抽出条件(WHERE文)に登場する列にインデックスを張っただけではインデックススキャンが使われない場合がある。
抽出する(SELECTする)列項目のすべてにインデックスを張ることによって
インデックススキャンが使われパフォーマンスが向上する。
LIKE句
SELECT LAST_NAME, FIRST_NAME FROM CUSTOMER_DATA WHERE LAST_NAME LIKE '%STONE'
インデックスは使わない
CREATE INDEX DC_LN ON CUSTOMER_DATA(LAST_NAME)
インデックススキャンが使われる
CREATE INDEX DC_LN ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME)
ORDER BY句
SELECT LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER FROM CUSTOMER_DATA ORDER BY LAST_NAME, FIRST_NAME
インデックスは使われない
CREATE INDEX DC_LN ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME)
インデックススキャンが使われる
CREATE INDEX DC_LN ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER)
JOIN句
SELECT CUSTOMER_DATA.FIRST_NAME, CUSTOMER_DATA.LAST_NAME,
CUSTOMER_DATA.PHONE_AREA, CUSTOMER_DATA.PHONE_NUMBER,
CUSTOMER_ORDER.ORDER_NUMBER, CUSTOMER_ORDER.ORDER_DT,
CUSTOMER_ORDER.AMOUNT
FROM CUSTOMER_DATA JOIN CUSTOMER_ORDER ON ID = CUSTOMER_ID
CUSTOMER_DATA表のID列はプライマリーキー
CUSTOMER_ORDER表の(CUSTOMER_ID, ORDER_NUMBER)はプライマリーキー
CUSTOMER_ORDER表のCUSTOMER_IDはFOREIGN KEY REFERENCES CUSTOMER_DATA(ID)
なのでインデックススキャンが使われそうだが
以下のインデックスを作ってやらないとパフォーマンスがでない。
CREATE INDEX DATA_IND ON CUSTOMER_DATA(ID, FIRST_NAME, LAST_NAME, PHONE_AREA, PHONE_NUMBER);
CREATE INDEX ORDER_IND ON CUSTOMER_ORDER(CUSTOMER_ID, ORDER_NUMBER, ORDER_DT, AMOUNT);
RUNSTATSやREORGをやらないで放っておくと
オプティマイザがインデックススキャンよりテーブルスキャンの方が速く
アクセスできると判断しテーブルスキャンが使われようになったり
あるレコードは速くアクセスできるのに、あるレコードは遅くなったり。
インデックススキャンよりテーブルスキャン
抽出条件によってはレコードが表全体に分散していて、クラスター率が低くなっている場合は
インデックススキャンよりテーブルスキャンのほうが速いとオプティマイザが決定する。
テーブルスキャンでもプリフェッチして表全体を読むのでインデックススキャンより
速くなる場合がある。
MINがMAXより速い
デフォルトでインデックスはリバーススキャンを許してない。
CREATE TABLE文の中で、PRIMARY KEY/FOREIGN KEY/UNIQ制約を書いて
自動的に作成されるインデックスもリバーススキャンを許してない。
明示的にリバーススキャンを定義することでMIN/MAXが早く動作する。
ALTER TABLE CUSTOMER DROP PRIMARY KEY;
CREATE UNIQUE INDEX CUSTOMER_ID ON CUSTOMER(ID) ALLOW REVERSE SCANS;
ALTER TABLE CUSTOMER ADD PRIMARY KEY(ID);
JOINよりFOREIGN KEY
トリガーで表の整合性をとる方式よりはFOREIGN KEYを定義すべきである。
アプリケーションのロジックで親子の表の整合性を取るより
FOREIGN KEYを使うことで
オプティマイザは２つの表の間に参照の整合性が取れていることがわかり
無駄な検索をするのをやめる。
最適レベル５がデフォルト
最適化レベルが１のときは最適化する情報にFOREIGN KEYを使わない。
SYSTATS.COLDISTをいじくってオプテマイザのテスト行える。
Essential Facts about Index Covering in DB2 Universal Database
http://www7b.software.ibm.com/dmdd/library/techarticle/0303kuznetsov/0303kuznetsov.html
When We Think the Optimizer Doesn't Get it Right
http://www7b.software.ibm.com/dmdd/library/techarticle/0302kuznetsov/0302kuznetsov.html
Guidelines for analyzing explain information
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8document.d2w/report?fn=c0005151.htm
DB2 Infocenter → Tutorials → Visual Explain Tutorial
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8document.d2w/report?fn=v8db2tvindex.htm
DB2v8 Javaのページ
http://www-3.ibm.com/software/data/db2/udb/ad/v8/java/
JDBCは動的なSQL呼び出し
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8document.d2w/report?fn=c0006269.htm
2003/08/14-15,19
ugya@lycos.com