DBパフォーマンスチューニングの基礎 インデックス入門 (Club DB2)

2012/6/22 DBパフォーマンスチューニングの基礎 インデックス入門 (Club DB2)
Club DB2 という、IBM DB2 の勉強会イベントで以前発表した資料です。かなり古いものなので情報が今とは異なる部分もあると思いますが、保存のためにアップロードしています。

Transcript

1. DBパフォーマンスチューニングの基礎 インデックス入門 2012/06/22 日本アイ・ビー・エム ソフトウェア事業部 下佐粉 昭 (しもさこ あきら) rev. 3 Ver Ver. . 1. 1.2 2
2. 2 自己紹介 下佐粉 昭 ( しもさこ あきら ) 和歌山県生まれ 2001年 IBMに中途入社 以来、DB2関連の仕事多し 現在はビジネスパートナー様向け技術支援 ▪書籍 「即戦力のDB2管理術」 – http://db2.jugem.cc/?eid=2341 （書籍紹介） 「XML-DB開発 実技コース」（共著） 「DB2 逆引きリファレンス」（共著) ▪オンライン Twitter - @simosako – http://twitter.com/simosako Unofficial DB2 Blog – http://db2.jugem.cc/ 全内容をWEBで公開しています http://db2watch.com/
3. 3 今日のテーマ RDBのインデックスって？ –インデクスを作成すると、速度が上がる！ –インデックス作成はパフォーマンスチューニングのキモ！ ...でも、なぜ速くなるんでしょう？ ... どういう仕掛けになっているかご存じですか？ 【今日のテーマ】 •インデックスとは何か？を理解し、構造を知る •メリット・デメリットを把握する •DB2独自のインデックスについて知る
4. 4 目次 インデックスとは？ – インデックスの目的 – インデックスの構造 – 制約とインデックス – 良いインデックスとは？ 実践編 – インデックスを使う？使わない？ – NULLとインデックス – DB2独自のインデックス この資料の記述は、DB2 10.1を対象にしています
5. 5 サンプル表 この資料では、以下のサンプル表を使用します（約30万行） CREATE TABLE emp ( emp_no INT NOT NULL, name VARCHAR(30) NOT NULL, gender CHAR(1) NOT NULL, hire_date DATE NOT NULL, title VARCHAR(18) NOT NULL, salary INT NOT NULL, comm INT , CHECK (gender='F' OR gender='M') ); ALTER TABLE emp ADD CONSTRAINT IDX_PK PRIMARY KEY (emp_no); 15152 Technique Leader 107385 Staff 26590 Senior Staff 30050 Senior Engineer 9 Manager 105710 Engineer 15128 Assistant Engineer 人数 TITLE EMP_NO NAME GENDER HIRE_DATE TITLE SALARY COMM ----------- ------------------------------ ------ ---------- ------------------ ----------- ----------- 10001 Georgi Facello M 1986-06-26 Senior Engineer 60117 864 10002 Bezalel Simmel F 1985-11-21 Staff 65828 - 10003 Parto Bamford M 1986-08-28 Senior Engineer 40006 898 10004 Chirstian Koblick M 1986-12-01 Engineer 40054 - 10005 Kyoichi Maliniak M 1989-09-12 Staff 78228 - 10006 Anneke Preusig F 1989-06-02 Senior Engineer 40000 1436 10007 Tzvetan Zielinski F 1989-02-10 Staff 56724 - 10008 Saniya Kalloufi M 1994-09-15 Assistant Engineer 46671 - ： DDLとデータはCLUB DB2ホームページからダウンロード可能です https://www.ibm.com/developerworks/wikis/display/clubdb2/145 ※copyrightは末尾ページに記載しています
6. 6 RDBのインデックスとは？（目的） インデックス（索引） –本でいうところの「目次」 –本もRDBもデータが大量にあるので、全部読んで探すと時間が掛かる 本の目次→単語で引くと、ページ数が書いてある –内容を全部読まなくても、どのページにあるか分かる RDBのインデックス→単語を引くと、その単語がどの行にあるか書いてある –全ての行を読まなくても、必要な行が特定できる ... という事は？ –検索の前にインデックスを作成する必要がある –表が更新されると、インデックスは必ず更新される必要がある DB2のマニュア ルでは「索引」と 書かれています
7. 7 インデックスが無い場合：表スキャン 表全体を順に読んでいき、必要なデータを発見する 順に読んで、データを探す 例）SELECT * FROM EMP WHERE TITLE='Engineer' TITLE列が'Engineer' の行を読み出す 0010 0009 0008 : 0004 0003 0002 0001 レコードID Engineer Duangkaew Piveteau 10010 Assistant Engineer Sumant Peac 10009 Assistant Engineer Saniya Kalloufi 10008 : : : Engineer Chirstian Koblick 10004 Senior Engineer Parto Bamford 10003 Staff Bezalel Simmel 10002 Senior Engineer Georgi Facello 10001 TITLE NAME EMP_NO ※列・行を省略しています
8. 8 インデックスの作り方:超基本編 インデックスを作る:CREATE INDEX で表と列を指定すると、指定した列の情報を 持ったインデックスが作成される 例） CREATE INDEX IDX_TITLE ON EMP(TITLE) –EMP表のTITLE列にIDX_TITLEインデックスが作成される CREATE INDEX インデックス名 ON 表名(列名) DROP INDEX インデックス名 インデックスの削除はDROP INDEX 0010 0009 0008 : 0004 0003 0002 0001 レコードID Engineer Duangkaew Piveteau 10010 Assistant Engineer Sumant Peac 10009 Assistant Engineer Saniya Kalloufi 10008 : : : Engineer Chirstian Koblick 10004 Senior Engineer Parto Bamford 10003 Staff Bezalel Simmel 10002 Senior Engineer Georgi Facello 10001 TITLE NAME EMP_NO
9. 9 Assistant Engineer {0008} {0009} Engineer {0004} {0010} Senior Engineer {0001} {0003} Staff {0002} インデックスがある場合：インデックス・スキャン インデックスを作成すると、まずインデックスを読んでから表にアクセス – インデックスには、対象のレコードID（RID）が記録される > CREATE INDEX IDX_TITLE ON EMP(TITLE) •インデックスを参照し、必要な行（レコードID）のデータだけを読む インデックス IDX_TITLE SELECT * FROM EMP WHERE TITLE='Engineer' 0010 0009 0008 : 0004 0003 0002 0001 レコードID Engineer Duangkaew Piveteau 10010 Assistant Engineer Sumant Peac 10009 Assistant Engineer Saniya Kalloufi 10008 : : : Engineer Chirstian Koblick 10004 Senior Engineer Parto Bamford 10003 Staff Bezalel Simmel 10002 Senior Engineer Georgi Facello 10001 TITLE NAME EMP_NO
10. 10 インデックスの特性 インデックスは事前に作成しておく必要がある –表が大きい場合、作成にはそれなりの時間がかかる –ハードディスクを消費する （DB2ではインデックス圧縮機能によって縮小が可能） 検索に必要な情報が含まれるインデックスを（RDBが）自動的に使用する –検索に必要な列とは？ • 条件検索の列 • ジョインのターゲット列 インデックスはメンテナンスが必要 –表が更新されると、インデックスは必ず（自動的に）更新される → インデックスは更新処理（INSERT,UPDATE,DELETE）を遅くする WHERE句に書かれた列 つまり... インデックスは、必要な列だけに作成し、不要な列からは削除する必要がある
11. 11 インデックスの構造と検索① 内部構造：B+ Tree（B-Treeの一種） –データをバランスさせながらリンク構造で「木」を構築 • 各キーは、 それが指す次レベルノードに存在する最大のキー –データ検索が一定時間で行える 参考） http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005300.html 'E' 'N' 'Z' 'F' 'L' 'N' 'A','C','E' 'F'->RID 'G'->RID 'I'->RID 'L'->RID 'M'->RID 'N'->RID ・・・ ・・・ ルートノード 中間ノード リーフノード インデックス・ レベル数 (3レベル) 'I' を探す例 ①ルートノードを左から 見て、'I'以上のキーを探 すと'N'が該当する ②同様に'I'以上を探す と'L'が該当する ③リーフノードに到達すると、そ の値のRIDが得られる
12. 12 インデックスの構造と検索② インデックスは「範囲（レンジ）」の検索にも有効 –リーフは次のリーフへのポインタを持っている 例） WHERE c1 BETWEEN 'F' AND 'L'のケース ①小さい方の値 である'F'を含む リーフに到達 ②リーフを順に辿り、 RIDを順に集める ③'L'を含むリーフに到達 したらそこで終了 ④集めたRIDをソートし、表データ を取り出す（リスト・プリフェッチ） WHERE c1 < x でも同じ ①最小値のリーフ（一番左ま で辿る ②順にリーフを辿り、xが出 るまで検索
13. 13 インデックスの更新 表データが更新（INSERT,UPDATE,DELETE）されるたびに、インデックスも 更新される 更新時に、バランスを取るようにキーが修正される ページに収まり切らなくなった場合は分割（スプリット）される バランスが崩れた例 キーを変えて、バランスを取った例 'A' 'C' 'G' 'B'->RID 'C'->RID 'A'->RID 'D'->RID 'F'->RID 'G'->RID 'B' 'D' 'F' 'C'->RID 'D'->RID 'A'->RID 'B'->RID 'F'->RID 'G'->RID
14. 14 制約を実現するためのインデックス① 制約を実現するために、自動的にインデックスが作成されます –プライマリーキー（PK）を定義した場合 –ユニーク制約（一意性制約）を定義した場合 CREATE TABLEの列定義で制約を指定する場合 –インデックスの名前やスキーマは指定できず、自動生成される CREATE TABLEでは指定せず、ALTER TABLEで指定する場合 –制約名と同じ名前でインデックスが作成される CREATE TABLE emp (emp_no INT NOT NULL PRIMARY KEY) CREATE TABLE emp (emp_no INT NOT NULL) ALTER TABLE emp ADD CONSTRAINT IDX_PK PRIMARY KEY (emp_no) IDX_PKという名前で、制約 とインデックスを作成
15. 15 制約を実現するためのインデックス② ユニーク制約 – 列にNULLを含められない ユニーク・インデックス – NULLは1つまで含めることが可能 CREATE TABLE t1 (c1 INT NOT NULL) ALTER TABLE t1 ADD CONSTRAINT uni1 UNIQUE (c1) DB2では、ユニーク制約とユニーク・インデックスは異なる どちらもインデックスが作成される CREATE TABLE t3 (c1 INT NOT NULL) CREATE UNIQUE INDEX uni3 ON t3(c1) CREATE TABLE t2 (c1 INT) ALTER TABLE t2 ADD CONSTRAINT uni2 UNIQUE (c1) エラー CREATE TABLE t4 (c1 INT) CREATE UNIQUE INDEX uni4 ON t4(c1) SQL0542N "C1" という名前の列は、 NULL値を含む可能性があるので、主キー およびユニーク・キー制約の列にすること ができません。 SQLSTATE=42831
16. 16 良いインデックスとは？ コンパクトで、インデックス・レベルが小さいインデックスが良い – コンパクト＝更新が速く、メモリ使用量が少ない – インデックス・レベル＝リーフにたどり着くまでに必要なI/O数 インデックスのリーフノード数、レベル数はSYSCAT.INDEXESで確認可能 – NLEAF（リーフノード数） – NLEVELS（インデックスレベル数） • もしくはMON_GET_INDEX表関数でも取得可能 例） SELECT INDNAME,NLEVELS,NLEAF FROM SYSCAT.INDEXES WHERE TABSCHEMA='SIM' AND TABNAME='EMP' （参考)SYSCAT.INDEXES http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001047.html 693 3 IDX_TITLE 1251 3 IDX_PK NLEAF NLEVELS INDNAME （参考）MON_GET_INDEX表関数 http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0055026.html ← ※プライマリーキー
17. 17 インデックスのサイズ① インデックスのサイズは、データの型やカーディナリティで大きく変わる 例：EMP表（300,024行） – 表のサイズ • もしくは db2pd -db EMPLOYEE -tcbstats all • 上記はページ数なので、4KB x 4449 = 約17MB – インデックスのサイズ(db2pdでも表示可能だが、誤差があるため非推奨） • 表の全インデックスサイズの合計[KB] • 例ではTITLE列とPK、2つのインデックスがある場合で、約8MB 4449 EMP SIM NPAGES TABNAME TABSCHEMA SELECT TABSCHEMA,TABNAME,NPAGES FROM SYSCAT.TABLES WHERE TABSCHEMA='SIM' AND TABNAME='EMP' 8064 8064 EMP INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE TABNAME 事前に統計情報の更新 (RUNSTATS)が必要 SELECT TABNAME,INDEX_OBJECT_P_SIZE,INDEX_OBJECT_L_SIZE FROM TABLE(ADMIN_GET_INDEX_INFO('I','SIM','IDX_TITLE')) AS T
18. 18 インデックスのサイズ② インデックスのサイズは、ほとんどがリーフページが占めるので、リーフページ の数でサイズの代わりにする事は可能 –インデックスのリーフページ数は、SYSCAT.INDEXESのNLEAF列で得ら れる • 上記はページ数なので • IDX_PK = 4KB x 1251 = 約5MB • IDX_PK = 4KB x 693 = 約3MB SELECT INDNAME,NLEAF FROM SYSCAT.INDEXES WHERE INDSCHEMA='SIM' 693 IDX_TITLE 1251 IDX_PK NLEAF INDNAME 事前に統計情報の更新 (RUNSTATS)が必要
19. 19 インデックスはどこに、どれだけ作成すべきか？① インデックスの位置を決めるのは難しい インデックスを付けるべき箇所 – 表にプライマリーキーは（ほぼ）必須 • とても小さい表は例外 – 読み込み中心のシステム（表）は、インデックスが多く ても問題無い – WHERE句で検索やジョインによく使用される列 – カーディナリティが高い列 • SYSCAT.COLUMNS表のCOLCARD列で確認 インデックスを避けた方が良い箇所 – 更新が多いシステム（表）はインデックスを控えめに – カーディナリティが低い列（フラグ列など） 7 TITLE EMP 51200 SALARY EMP 274432 NAME EMP 5632 HIRE_DATE EMP 2 GENDER EMP 300024 EMP_NO EMP COLCARD COLNAME TABNAME
20. 20 インデックスはどこに、どれだけ作成すべきか？② 設計アドバイザ（db2advis）を使う –実行するSQLの種類と頻度を与えると推奨され るインデックスが得られる • ファイルにSQLと頻度（FREQUENCY）を書く • db2advis -d DB名 -i ファイル名 –-gを指定すると、パッケージキャッシュに保存さ れたSQLを元に推奨値が得られる • db2advis -d DB名 -g ※（参考）パッケージキャッシュからあふれたSQLをイベントモニターで記録して、アドバイ ザに渡す方法もあります http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.a dmin.mon.doc/doc/t0057193.html --#SET FREQUENCY 2 SELECT * FROM EMP; --#SET FREQUENCY 10 SELECT EMP_NO,NAME FROM EMP WHERE HIRE_DATE < ?; -- 推奨される索引のリスト -- =========================== -- index[1], 12.345MB CREATE INDEX "SIM "."IDX1206180204400" ON "SIM "."EMP" ("HIRE_DATE" ASC, "NAME" ASC, "EMP_NO" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
21. 21 ここまでのまとめ インデックスにはメリットとデメリット（コスト）がある –メリット • 検索処理（SELECT）が高速になる ... ただし検索に使える場合のみ –デメリット • 更新処理（INSERT,UPDATE,DELETE）が遅くなる • ディスクを消費する 設計アドバイザーを使って、適切なインデックスを計算する事ができる インデックスの構造はB+ Tree –検索が一定時間 良いインデックスとは？ –コンパクト ＆ インデックス・レベルが小さい
22. 22 そのインデックス使われていますか？ 使われていないインデックスは削除するのが理想 –更新が遅くなり、ディスク消費が増えるのに、メリットが無いため 使われているかどうかの確認が重要 –インデックスを使うか使わないかは、DB2が自動的に判断する –アクセスプラン（実行計画）を取得することで分かる アクセスプラン取得前には統計情報の更新が必要 –RUNSTATSコマンドで統計情報を最新に更新する • 自動化も可能（デフォルトで自動実行される） • コマンドの詳細は本資料の補足ページ、もしくはCLUB DB2「アクセス・ プラン編」や「運用管理編」の資料を参照 http://www.ibm.com/developerworks/wikis/display/clubdb2/materials
23. 23 アクセスプラン（実行計画）を確認するツール db2exfmt –もっとも詳細な情報が得られる • これがお勧めです（次ページに詳細） db2expln –事前準備無しですぐ使える 例） >db2expln -d DB名 -t -q "SELECT ..." >db2expln -d DB名 -t -f ファイル DataStudio (GUI)内蔵のVisual Explain –GUIで操作可能
24. 24 db2exfmtの使い方 事前準備： 情報を格納するEXPLAIN表をDBに作成しておく必要がある 方法１）sqllib/misc/EXPLAIN.DDLを実行して作成する > db2 -tvf .../sqllib/misc/EXPLAIN.DDL 方法２） ストアドプロシージャを実行して作成する（DB2 9.5以降) > db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT SCHEMA)" db2 CONNECT TO EMPLOYEE db2 SET CURRENT EXPLAIN MODE EXPLAIN db2 "任意のSQL" db2 SET CURRENT EXPLAIN MODE NO db2exfmt -1 -d EMPLOYEE -o myexplain.txt EXPLAIN表へアクセスプランを書き出す EXPLAIN MODEなのでSQLは実行されない データーベース名 標準的なオプションで直近（最新）のEXPLAIN結果を出力 出力ファイル名 SET EXPLAIN MODE EXPLAIN後に実行したSQLのアクセスプランが EXPLAIN表に格納されるので、それをdb2exfmtコマンドで取り出す 例） 参考）SYSINSTALLOBJECTSプロシージャ http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0011876.html
25. 25 インデックスを使う？使わない？① 243145 TBSCAN ( 2) 3991.44 4071 | 300024 TABLE: SIM EMP Q3 表（SIM.EMP）の表ス キャン（TBSCAN）にな っている 15152 Technique Leader 107385 Staff 26590 Senior Staff 30050 Senior Engineer 9 Manager 105710 Engineer 15128 Assistant Engineer 数 TITLE 105710 FETCH ( 2) 2107.58 2067.03 /---+----¥ 105710 300024 IXSCAN TABLE: SIM ( 3) EMP 409.102 Q1 268.889 | 300024 INDEX: SIM IDX_TITLE Q1 SELECT * FROM EMP WHERE TITLE IN ('Staff','Engineer','Senior Engineer') SELECT * FROM EMP WHERE TITLE = 'Engineer' インデックス・スキ ャンになっている
26. 26 インデックスとアクセスプラン インデックスが非効率と判断すれば使わない –表から多くのデータを取得する場合、インデックス・スキャンより も表スキャンの方が速い（事が多い） 平均処理時間 取得したいデータ量 [%] 表スキャン ０ １００ インデックス・スキャン ここで、インデックス・スキャンから表ス キャンに切り替わる（理想）
27. 27 表の物理配置（概念図） 多くのデータベースでは、行単位ではなく、ページ単位でデータを格納する –DB2の場合は、4KB,8KB,16KB,32KBの4つから選択可能 • デフォルトは4KB 表スペース（ディスク領域）にページ単位でデータを格納していく –基本的に順番にデータを並べる、シンプルな配置 • スペース効率を最優先に考えた配置 –DB2は「ページをまたいだ形」では行を格納しない DB2はページ単位でディスクI/O処理をする –行単位、列単位のI/O処理ではない 表スペース ページ（ディスクIOの単位） T1表の行1 行2 行3 行4 行5(続き) 行5 T1表の行6 行7 T2表の行1 行2 行3 行4 行5 T2表の行6 T3表の行1 行2 行3 行4 （管理用 領域） （管理用 領域）
28. 28 複合インデックス（コンポジット・インデックス） インデックス作成時に複数の列を指定可能 → 複合インデックス 複合インデックスの使い道 例） CREATE INDEX IDX_COMP ON EMP(GENDER,SALARY) ①複合条件の高速化 • SELECT * FROM EMP WHERE GENDER='M' AND SALARY > 110000 ②インデックスのみのアクセスでデータを返す • SELECT AVG(SALARY) FROM EMP WHERE GENDER='M' CREATE INDEX インデックス名 ON 表名(列名,列名, ...) 0.599862 FETCH ( 2) 17.639 2.59986 /---+----¥ 0.599862 300024 IXSCAN TABLE: SIM ( 3) EMP 13.5753 Q1 2 | 300024 INDEX: SIM IDX_COMP Q1 1 GRPBY ( 2) 768.856 533.186 | 179973 IXSCAN ( 3) 723.256 533.186 | 300024 INDEX: SIM IDX_COMP Q1
29. 29 インデックスを使う？使わない？② 複合インデックスの先頭を条件に含まない場合 例） CREATE INDEX IDX_COMP ON EMP(GENDER,SALARY) A)SELECT AVG(SALARY) FROM EMP WHERE GENDER='M' <= ◦使える B)SELECT COUNT(*) FROM EMP WHERE SALARY>110000 <= ×使えない –複合インデックスの検索の基準は、定義の最初の列 DB2 10.1新機能： ”ジャンプ・スキャン” –上記B)のようなケースでもインデックスが使える - 列の「ギャップ」があるインデックスが存在した場合、そのギャップを取り得る 値全パターンで埋めながらインデックスを検索する • WHERE SALARY>110000 => WHERE (GENDER='F' OR GENDER='M') AND SALARY>110000 参考) http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.wn.doc/doc/c0058597.html
30. 30 インデックスのANDingとORing 検索に利用できるインデックスが複数ある場合 1. それぞれのインデックスをスキャンして、対象 のRIDを集める 2. その結果をAND（もしくはOR）処理をして、必 要なRID一覧を得る → インデックス ANDing （もしくはORing） – 複合インデックス無しでも、同様の検索が可能 886.206 FETCH ( 2) 635.609 559.787 /---+----¥ 886.206 300024 RIDSCN TABLE: SIM ( 3) EMP 238.221 Q1 112.351 | 886.206 SORT ( 4) 238.22 112.351 | 886.206 IXAND ( 5) 237.289 112.351 /-----+------¥ 9999.37 26590 IXSCAN IXSCAN ( 6) ( 7) 93.5571 139.527 44.2632 68.0879 | | 300024 300024 INDEX: SIM INDEX: SIM IDX_PK IDX_TITLE 例） SELECT NAME FROM EMP WHERE TITLE='Senior Staff' AND EMP_NO < 20000; TITLE列には単一列 のインデックスが作 成済み EMP_NOはプライマリキ ーなので単一列のインデ ックスが存在する
31. 31 [参考] ビットマップ・インデックス ビットマップインデックスとは？ –B-Treeではく、列のそれぞれの値ごとにビットマップ(0/1)を作成する –データ量（行）がとても多く、データの種類が少ない場合に有効 –複合条件の検索に特に有効（ビットマップのANDは高速に行える） DB2では明示的にビットマップ・インデックスを作成するのではなく、インデッ クスANDing処理時に内部で自動的に作成される –ダイナミック・ビットマップ・インデックス TITLE='Senior Staff'のビットマップ EMP_NO < 20000のビットマップ AND TITLE='Senior Staff' AND EMP_NO < 20000 のRID情報を持つビッ トマップインデックス EMP_NO NAME TITLE ----------- -------------------- --------------- 19995 Ziyad Schueller Senior Staff ▪ ▪ ▪ 19996 Berni Chinen Senior Engineer ▪ □ □ 19997 Otilia Zumaque Staff ▪ □ □ 19998 Fuqing Maksimenko Staff ▪ □ □ 19999 Jahangir Speer Engineer ▪ □ □ 20000 Jenwei Matzke Senior Engineer □ □ □ 20001 Atreye Eppinger Engineer □ □ □ 20002 Jaber Brender Staff □ □ □ 20003 Munehiko Coors Staff □ □ □ 20004 Radoslaw Pfau Senior Staff □ ▪ □
32. 32 INCLUDE句 DB2にはINCLUDE句によって、インデックスのリーフに対象列 以外の列を保存できる –条件：UNIQUEインデックスであること • 特定の条件に合致したINCLUDEを指定することで、イン デックスのみのアクセスで結果を返すことが可能になる 例） EMP_NOを検索条件にして、アンサーセットにNAMEを返 すクエリー > SELECT NAME FROM EMP WHERE EMP_NO=? • 通常 • EMP_NO列に作成されたインデックスをインデック・ススキャンして RIDを得た後に表からデータを取得 • INCLUDEを使った場合 > CREATE UNIQUE INDEX IDX1 ON EMP(EMP_NO) INCLUDE (NAME) • IDX1をインデックス・スキャンするだけでアンサーセットに必要なデ ータが得られる（表にアクセスしていない） 1 IXSCAN ( 2) 13.577 2 | 300024 INDEX: SIM IDX1 Q1
33. 33 NULLとインデックス NULLへのRIDをインデックスに含めるか？はRDBによっ て異なる –DB2 → 含める –Oracle→ 含めない NULLを含めるメリット –"WHERE c IS (NOT) NULL"でインデックスを使用した アクセスが可能 –例） CREATE INDEX IDX_COMM ON EMP(COMM) SELECT * FROM EMP WHERE COMM IS NOT NULL NULLを含めないメリット –NULLが多い列へのインデックスサイズが小さくなる 30059 FETCH ( 2) 951.497 917.005 /---+----¥ 30059 300024 RIDSCN TABLE: SIM ( 3) EMP 176.321 Q1 70.8569 | 30059 SORT ( 4) 176.32 70.8569 | 30059 IXSCAN ( 5) 143.41 70.8569 | 300024 INDEX: SIM IDX_COMM
34. 34 インデックスの順序と逆スキャン インデックスには順序がある > CREATE INDEX ind ON t(c1 [ASC|DESC]) • ASC = 昇順(ascending,小さい順) - デフォルト • DESC = 降順(descending,大きい順) ORDER BYやMIN/MAX処理に影響を与える –ASC →MIN(c1)と、ORDER BY c1 ASC時に有利 –DESC →MAX(c1)と、ORDER BY c1 DESC時に有利 DB2はインデックスの逆スキャン（リバース・スキャン）が可能 –ASCで定義したインデックスをDESCと同様に使用できる（逆もしかり） –デフォルトで逆スキャンが有効 • 逆スキャンを無効にしたい場合は、CREATE INDEX時にDISALLOW REVERSE SCANSを付ける
35. 35 クラスター率とクラスター・インデックス クラスター率とは – データが「欲しい順番どおりに物理的に並んでいる率」 – 物理的に順番どおりに並んでいると、取り出すのが速い クラスター・インデックスとは – クラスター・インデックスが作成された表ではキー値が近いものでかたまるよう にインサートされる – 読み取ったページに次のキーが入っている確率が上がり、取り出しページ数 が少なく、効率が上がる – 1つの表に1つだけ作成できる CREATE INDEX indc ON T1(C1) CLUSTER クラスター・インデックスを作成する例 非クラスター・ インデックス クラスター・インデックス ８データベージ（例：散らばっている） ４データベージ（例：かたまっている）
36. 36 関数インデックスは無いけれど DB2に関数インデックスは無い 関数が使われていても、インデッ クスは利用可能 –オプティマイザが解釈可能な 範囲に限られる SELECT * FROM TITLES WHERE SUBSTR(TITLE,1,1)='M' 24 FETCH ( 2) 23.1914 3.41205 /---+----¥ 24 443308 IXSCAN TABLE: SIM ( 3) TITLES 13.6134 Q1 2 | 443308 INDEX: SIM TITLES_TITLE Q1 インデックス･ スキャン SELECT * FROM EMP WHERE TITLE='Manager' => インデックス・スキャン
37. 37 まとめ インデックスはRDBパフォーマンスチューニングのキモ – 適切なところにインデックスを作成できたのであれば、チューニングは半分 以上終わったようなもの – 制約の実現にもインデックスが使われる – 設計アドバイザーを活用 – アクセスプランで確認 DB2の特徴的な機能 – NULLへのポインタを含む – INCLUDE – クラスターインデックス – リバーススキャンがデフォルト – ジャンプスキャン 今日話せなかったこと – MDCのブロック・インデックス,パーティション表とインデックス,インデックス の圧縮 ... など
38. 38 参考資料 DB2のオンラインドキュメント：インフォメーションセンター 常に最新の情報が閲覧できます。検索機能付き – DB2 10.1版 • http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp – DB2 9.7版 • http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp – DB2 9.5版 • http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp DB2のPDF版マニュアル 日本語、英語など各国語版がダウンロード可能です – DB2 9.7版 • http://ibm.com/support/docview.wss?rs=71&uid=swg27015149 – DB2 9.5版 • http://ibm.com/support/docview.wss?rs=71&uid=swg27009728 CLUB DB2の過去セミナー資料公開中！ – http://ibm.com/developerworks/wikis/display/clubdb2/materials DB2 Express-Cの導入方法解説（無料のDB2で試しましょう！） – http://www.ibm.com/developerworks/jp/offers/db2express-c/installwin_v10/ (Windows) – http://www.ibm.com/developerworks/jp/offers/db2express-c/installlin_v10/ (Linux) DB2の日本語ドキュメント一覧は以下の短縮URLからも辿れます http://j.mp/db2docsja
39. 39 補足資料 以下のページは補足資料です –良く使うDB2のコマンド –DB2の構成とパラメタ –REORGコマンド（表の再編成） –RUNSTATSコマンド（統計情報の更新）
40. 40 良く使うコマンド 良く使うDB2のコマンド （ファイルに;区切りでコマンドやSQLを記述しておいて） db2 -tvf ファイル名 ファイルに記録し たコマンドの実行 db2 "DROP DB db名" DB削除 db2 "CREATE DB db名 ..." DB作成 db2 "LIST DB DIRECTORY" DB一覧表示 db2 "LIST APPLICATIONS" 接続ユーザ一覧 db2 "任意のSQL" db2 +c "任意のSQL" ←AUTO COMMITをOFFにして実行 SQLを実行 db2start インスタンス開始 db2 "CONNECT TO db名 USER userid USING password" DBに接続 db2 "TERMINATE" 接続解除 db2stop [force] インスタンス停止
41. 41 構成パラメタ 設定は構成パラメタの変更で行う DB2の構成パラメタは３種類 –影響範囲が異なる –調整は、DBコンフィグが中心 システム（レジストリ） インスタンス (DBM CFG) データベース (DB CFG) GET DB CFG FOR db名 GET DBM CFG db2set [-all] 取得 UPDATE DB CFG FOR db名 USING cfg1 val1 [cfg2 val2 ..] データベース データベース (DB)構成パラ メーター UPDATE DBM CFG USING cfg1 val1 [cfg2 val2 ...] インスタンス内 データベースマ ネージャ(DBM) 構成パラメータ ー db2set REG1=VAL1 システム全体も しくはインスタン ス内 レジストリ変数 更新 影響範囲
42. 42 REORGコマンド（表の再編成） REORGはオンライン動作可能 – REORG中にユーザーが対象のテーブル、インデックスにアクセス 可能 テーブルのREORG REORG TABLE テーブル名 [INPLACE] [ALLOW {READ|WRITE|NO} ACCESS] INPLACEを指定すると、インプレース動作 •ALLOW READ ACCESS - REORG中のテーブルへのアクセスを読み取りのみ許可 •ALLOW WRITE ACCESS - REORG中のテーブルへの読み書きアクセスを許可（INPLACE指定時にのみ指定可能） •ALLOW NO ACCESS - REORG中のテーブルへのアクセスを禁止（INPLACEとの同時指定不可） REORG INDEXES ALL FOR TABLE テーブル名 [ALLOW {READ|WRITE|NO} ACCESS] インデックスのREORG（テーブル毎） •ALLOW READ ACCESS - REORG中のインデックスへのアクセスを読み取りのみ許可 •ALLOW WRITE ACCESS - REORG中のインデックスへの読み書きアクセスを許可 •ALLOW NO ACCESS - REORG中のインデックスへのアクセスを禁止
43. 43 RUNSTATSコマンド（統計情報の更新） RUNSTATSコマンドで統計情報を更新する – RUNSTATS実行中でも表に読み書きアクセス可能 少し進んだ使い方 – ①拡張統計で収集する – ②サンプリングでRUNSTATSの実行時間を短くする RUNSTATS ON TABLE スキーマ名.表名 RUNSTATS ON TABLE スキーマ名.表名 AND INDEXES ALL （※DB2 10.1からスキーマ名が省略可能になっています） 多くの場合、この 基本形でOK データに「偏り」がある場合、 拡張統計を試してください RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL RUNSTATS ON TABLE SIM.DEPARTMENTS WITH DISTRIBTION TABLESAMPLE BERNOULLI (5) 表を5%サンプリング
44. 44 補足：サンプル表で使用したデータについて サンプル表のDDLとデータはCLUB DB2ホームページからダウンロード可能です – https://www.ibm.com/developerworks/wikis/display/clubdb2/145 上記データは、以下の「Employees sample database」からダウンロードしたファイルを元に作成したものです。 – http://dev.mysql.com/doc/employee/en/employee.html この元ファイルのライセンスは、「Creative Commons Attribution-Share Alike 3.0 Unported License.」 ( http://creativecommons.org/licenses/by-sa/3.0/ )であるため、改変後のファイルも同じライセンスに従います。 以下は元ファイル（オリジナル）のcopyright表記です。 -- Sample employee database -- See changelog table for details -- Copyright (C) 2007,2008, MySQL AB -- -- Original data created by Fusheng Wang and Carlo Zaniolo -- http://www.cs.aau.dk/TimeCenter/software.htm -- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip -- -- Current schema by Giuseppe Maxia -- Data conversion from XML to relational by Patrick Crews -- -- This work is licensed under the -- Creative Commons Attribution-Share Alike 3.0 Unported License. -- To view a copy of this license, visit -- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to -- Creative Commons, 171 Second Street, Suite 300, San Francisco, -- California, 94105, USA. -- -- DISCLAIMER -- To the best of our knowledge, this data is fabricated, and -- it does not correspond to real people. -- Any similarity to existing people is purely coincidental.