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

DBパフォーマンスチューニングの基礎
インデックス入門
2012/06/22
日本アイ・ビー・エムソフトウェア事業部
下佐粉昭 (しもさこあきら)
rev. 3
Ver
Ver.
. 1.
1.2
2

View Slide

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/

View Slide

3
今日のテーマ
RDBのインデックスって？
–インデクスを作成すると、速度が上がる！
–インデックス作成はパフォーマンスチューニングのキモ！
...でも、なぜ速くなるんでしょう？
... どういう仕掛けになっているかご存じですか？
【今日のテーマ】
•インデックスとは何か？を理解し、構造を知る
•メリット・デメリットを把握する
•DB2独自のインデックスについて知る

View Slide

4
目次
インデックスとは？
– インデックスの目的
– インデックスの構造
– 制約とインデックス
– 良いインデックスとは？
実践編
– インデックスを使う？使わない？
– NULLとインデックス
– DB2独自のインデックス
この資料の記述は、DB2 10.1を対象にしています

View Slide

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は末尾ページに記載しています

View Slide

6
RDBのインデックスとは？（目的）
インデックス（索引）
–本でいうところの「目次」
–本もRDBもデータが大量にあるので、全部読んで探すと時間が掛かる
本の目次→単語で引くと、ページ数が書いてある
–内容を全部読まなくても、どのページにあるか分かる
RDBのインデックス→単語を引くと、その単語がどの行にあるか書いてある
–全ての行を読まなくても、必要な行が特定できる
... という事は？
–検索の前にインデックスを作成する必要がある
–表が更新されると、インデックスは必ず更新される必要がある
DB2のマニュア
ルでは「索引」と
書かれています

View Slide

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
※列・行を省略しています

View Slide

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

View Slide

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

View Slide

10
インデックスの特性
インデックスは事前に作成しておく必要がある
–表が大きい場合、作成にはそれなりの時間がかかる
–ハードディスクを消費する（DB2ではインデックス圧縮機能によって縮小が可能）
検索に必要な情報が含まれるインデックスを（RDBが）自動的に使用する
–検索に必要な列とは？
• 条件検索の列
• ジョインのターゲット列
インデックスはメンテナンスが必要
–表が更新されると、インデックスは必ず（自動的に）更新される
→ インデックスは更新処理（INSERT,UPDATE,DELETE）を遅くする
WHERE句に書かれた列
つまり...
インデックスは、必要な列だけに作成し、不要な列からは削除する必要がある

View Slide

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が得られる

View Slide

12
インデックスの構造と検索②
インデックスは「範囲（レンジ）」の検索にも有効
–リーフは次のリーフへのポインタを持っている
例） WHERE c1 BETWEEN 'F' AND 'L'のケース
①小さい方の値
である'F'を含む
リーフに到達
②リーフを順に辿り、
RIDを順に集める
③'L'を含むリーフに到達
したらそこで終了
④集めたRIDをソートし、表データ
を取り出す（リスト・プリフェッチ）
WHERE c1 < x でも同じ
①最小値のリーフ（一番左ま
で辿る
②順にリーフを辿り、xが出
るまで検索

View Slide

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

View Slide

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という名前で、制約
とインデックスを作成

View Slide

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

View Slide

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
← ※プライマリーキー

View Slide

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

View Slide

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)が必要

View Slide

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

View Slide

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;

View Slide

21
ここまでのまとめ
インデックスにはメリットとデメリット（コスト）がある
–メリット
• 検索処理（SELECT）が高速になる ... ただし検索に使える場合のみ
–デメリット
• 更新処理（INSERT,UPDATE,DELETE）が遅くなる
• ディスクを消費する
設計アドバイザーを使って、適切なインデックスを計算する事ができる
インデックスの構造はB+ Tree
–検索が一定時間
良いインデックスとは？
–コンパクト＆インデックス・レベルが小さい

View Slide

22
そのインデックス使われていますか？
使われていないインデックスは削除するのが理想
–更新が遅くなり、ディスク消費が増えるのに、メリットが無いため
使われているかどうかの確認が重要
–インデックスを使うか使わないかは、DB2が自動的に判断する
–アクセスプラン（実行計画）を取得することで分かる
アクセスプラン取得前には統計情報の更新が必要
–RUNSTATSコマンドで統計情報を最新に更新する
• 自動化も可能（デフォルトで自動実行される）
• コマンドの詳細は本資料の補足ページ、もしくはCLUB DB2「アクセス・
プラン編」や「運用管理編」の資料を参照
http://www.ibm.com/developerworks/wikis/display/clubdb2/materials

View Slide

23
アクセスプラン（実行計画）を確認するツール
db2exfmt
–もっとも詳細な情報が得られる
• これがお勧めです（次ページに詳細）
db2expln
–事前準備無しですぐ使える
例）
>db2expln -d DB名 -t -q "SELECT ..."
>db2expln -d DB名 -t -f ファイル
DataStudio (GUI)内蔵のVisual Explain
–GUIで操作可能

View Slide

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

View Slide

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'
インデックス・スキ
ャンになっている

View Slide

26
インデックスとアクセスプラン
インデックスが非効率と判断すれば使わない
–表から多くのデータを取得する場合、インデックス・スキャンより
も表スキャンの方が速い（事が多い）
平均処理時間
取得したいデータ量 [%]
表スキャン
０１００
インデックス・スキャン
ここで、インデックス・スキャンから表ス
キャンに切り替わる（理想）

View Slide

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
（管理用
領域）
（管理用
領域）

View Slide

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

View Slide

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

View Slide

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はプライマリキ
ーなので単一列のインデ
ックスが存在する

View Slide

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 □ ■ □

View Slide

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

View Slide

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

View Slide

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を付ける

View Slide

35
クラスター率とクラスター・インデックス
クラスター率とは
– データが「欲しい順番どおりに物理的に並んでいる率」
– 物理的に順番どおりに並んでいると、取り出すのが速い
クラスター・インデックスとは
– クラスター・インデックスが作成された表ではキー値が近いものでかたまるよう
にインサートされる
– 読み取ったページに次のキーが入っている確率が上がり、取り出しページ数
が少なく、効率が上がる
– 1つの表に1つだけ作成できる
CREATE INDEX indc ON T1(C1) CLUSTER
クラスター・インデックスを作成する例
非クラスター・
インデックスクラスター・インデックス
８データベージ（例：散らばっている）４データベージ（例：かたまっている）

View Slide

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'
=> インデックス・スキャン

View Slide

37
まとめ
インデックスはRDBパフォーマンスチューニングのキモ
– 適切なところにインデックスを作成できたのであれば、チューニングは半分
以上終わったようなもの
– 制約の実現にもインデックスが使われる
– 設計アドバイザーを活用
– アクセスプランで確認
DB2の特徴的な機能
– NULLへのポインタを含む
– INCLUDE
– クラスターインデックス
– リバーススキャンがデフォルト
– ジャンプスキャン
今日話せなかったこと
– MDCのブロック・インデックス,パーティション表とインデックス,インデックス
の圧縮 ... など

View Slide

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

View Slide

39
補足資料
以下のページは補足資料です
–良く使うDB2のコマンド
–DB2の構成とパラメタ
–REORGコマンド（表の再編成）
–RUNSTATSコマンド（統計情報の更新）

View Slide

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]
インスタンス停止

View Slide

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
システム全体も
しくはインスタン
ス内
レジストリ変数
更新
影響範囲

View Slide

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中のインデックスへのアクセスを禁止

View Slide

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%サンプリング

View Slide

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.

View Slide

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

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

Akira Shimosako

More Decks by Akira Shimosako

Other Decks in Technology

Featured

Transcript

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

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

Akira Shimosako

More Decks by Akira Shimosako

Other Decks in Technology

Featured

Transcript

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

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