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

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

2012/06/22
日本アイ・ビー・エムソフトウェア事業部
下佐粉昭 (しもさこあきら) rev.Ver.
Ver.
3 1.2
1.2

自己紹介
下佐粉昭 ( しもさこあきら )
和歌山県生まれ
2001年 IBMに中途入社
以来、DB2関連の仕事多し
現在はビジネスパートナー様向け技術支援
■書籍
「即戦力のDB2管理術」
– http://db2.jugem.cc/?eid=2341 （書籍紹介）
「XML-DB開発実技コース」（共著）
「DB2 逆引きリファレンス」（共著)

■オンライン
Twitter - @simosako 全内容をWEBで公開しています
– http://twitter.com/simosako http://db2watch.com/
Unofficial DB2 Blog
– http://db2.jugem.cc/

2

今日のテーマ

RDBのインデックスって？
–インデクスを作成すると、速度が上がる！
–インデックス作成はパフォーマンスチューニングのキモ！

...でも、なぜ速くなるんでしょう？
... どういう仕掛けになっているかご存じですか？

【今日のテーマ】
•インデックスとは何か？を理解し、構造を知る
•メリット・デメリットを把握する
•DB2独自のインデックスについて知る

3

目次
インデックスとは？
– インデックスの目的
– インデックスの構造
– 制約とインデックス
– 良いインデックスとは？

実践編
– インデックスを使う？使わない？
– NULLとインデックス
– DB2独自のインデックス

この資料の記述は、DB2 10.1を対象にしています

4

DDLとデータはCLUB DB2ホームページからダウンロード可能です
https://www.ibm.com/developerworks/wikis/display/clubdb2/145
※copyrightは末尾ページに記載しています
サンプル表
この資料では、以下のサンプル表を使用します（約30万行）
CREATE TABLE emp (
emp_no INT NOT NULL, TITLE 人数
name VARCHAR(30) NOT NULL, Assistant Engineer 15128
gender CHAR(1) NOT NULL, Engineer 105710
hire_date DATE NOT NULL,
Manager 9
title VARCHAR(18) NOT NULL,
salary INT NOT NULL, Senior Engineer 30050
comm INT , Senior Staff 26590
CHECK (gender='F' OR gender='M') Staff 107385
); Technique Leader 15152
ALTER TABLE emp ADD CONSTRAINT IDX_PK PRIMARY KEY (emp_no);

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 -
：
5

RDBのインデックスとは？（目的）
DB2のマニュア
ルでは「索引」と
インデックス（索引）書かれています
–本でいうところの「目次」
–本もRDBもデータが大量にあるので、全部読んで探すと時間が掛かる

本の目次→単語で引くと、ページ数が書いてある
–内容を全部読まなくても、どのページにあるか分かる

RDBのインデックス→単語を引くと、その単語がどの行にあるか書いてある
–全ての行を読まなくても、必要な行が特定できる

... という事は？
–検索の前にインデックスを作成する必要がある
–表が更新されると、インデックスは必ず更新される必要がある

6

インデックスが無い場合：表スキャン

表全体を順に読んでいき、必要なデータを発見する

例）SELECT * FROM EMP WHERE TITLE='Engineer'
TITLE列が'Engineer'
順に読んで、データを探すの行を読み出す

レコードID EMP_NO NAME TITLE
0001 10001 Georgi Facello Senior Engineer
0002 10002 Bezalel Simmel Staff
0003 10003 Parto Bamford Senior Engineer
0004 10004 Chirstian Koblick Engineer
: : : :
0008 10008 Saniya Kalloufi Assistant Engineer
0009 10009 Sumant Peac Assistant Engineer
0010 10010 Duangkaew Piveteau Engineer

※列・行を省略しています
7

インデックスの作り方:超基本編
インデックスを作る:CREATE INDEX で表と列を指定すると、指定した列の情報を
持ったインデックスが作成される
CREATE INDEX インデックス名 ON 表名(列名)
例） CREATE INDEX IDX_TITLE ON EMP(TITLE)
–EMP表のTITLE列にIDX_TITLEインデックスが作成される
0002 10002 Bezalel Simmel Staff
0003 10003 Parto Bamford Senior Engineer
0004 10004 Chirstian Koblick Engineer
: : : :
0010 10010 Duangkaew Piveteau Engineer

インデックスの削除はDROP INDEX
DROP INDEX インデックス名

8

インデックスがある場合：インデックス・スキャン
インデックスを作成すると、まずインデックスを読んでから表にアクセス
– インデックスには、対象のレコードID（RID）が記録される
> CREATE INDEX IDX_TITLE ON EMP(TITLE)
•インデックスを参照し、必要な行（レコードID）のデータだけを読む

インデックス IDX_TITLE
Assistant Engineer SELECT * FROM EMP WHERE TITLE='Engineer'
{0008}
{0009}
Engineer
{0004} 0002 10002 Bezalel Simmel Staff
{0010} 0003 10003 Parto Bamford Senior Engineer

Senior Engineer 0004 10004 Chirstian Koblick Engineer
: : : :
{0001}
{0003}
Staff 0010 10010 Duangkaew Piveteau Engineer
{0002}
9

インデックスの特性
インデックスは事前に作成しておく必要がある
–表が大きい場合、作成にはそれなりの時間がかかる
–ハードディスクを消費する（DB2ではインデックス圧縮機能によって縮小が可能）

検索に必要な情報が含まれるインデックスを（RDBが）自動的に使用する
–検索に必要な列とは？
• 条件検索の列 WHERE句に書かれた列
• ジョインのターゲット列

インデックスはメンテナンスが必要
–表が更新されると、インデックスは必ず（自動的に）更新される
→ インデックスは更新処理（INSERT,UPDATE,DELETE）を遅くする

つまり...
インデックスは、必要な列だけに作成し、不要な列からは削除する必要がある

10

インデックスの構造と検索①
内部構造：B+ Tree（B-Treeの一種）
–データをバランスさせながらリンク構造で「木」を構築
• 各キーは、それが指す次レベルノードに存在する最大のキー
–データ検索が一定時間で行える 'I' を探す例
①ルートノードを左から
見て、'I'以上のキーを探
②同様に'I'以上を探すすと'N'が該当する
と'L'が該当する
'E' 'N' 'Z' ルートノード

'A','C','E' 'F' 'L' 'N' 中間ノード
インデックス・
レベル数
(3レベル)
'F'->RID 'G'->RID 'M'->RID リーフノード
・・・・・・
'I'->RID 'N'->RID
'L'->RID
③リーフノードに到達すると、そ
の値のRIDが得られる
参考）
11 http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005300.html

インデックスの構造と検索②
インデックスは「範囲（レンジ）」の検索にも有効
–リーフは次のリーフへのポインタを持っている

例） WHERE c1 BETWEEN 'F' AND 'L'のケース

WHERE c1 < x でも同じ
①小さい方の値 ①最小値のリーフ（一番左ま
である'F'を含む
リーフに到達で辿る
②順にリーフを辿り、xが出
るまで検索

②リーフを順に辿り、 ③'L'を含むリーフに到達
RIDを順に集めるしたらそこで終了

④集めたRIDをソートし、表データ
を取り出す（リスト・プリフェッチ）

12

インデックスの更新
表データが更新（INSERT,UPDATE,DELETE）されるたびに、インデックスも
更新される
更新時に、バランスを取るようにキーが修正される
ページに収まり切らなくなった場合は分割（スプリット）される

'A' 'C' 'G' 'B' 'D' 'F'

'A'->RID 'B'->RID 'D'->RID 'A'->RID 'C'->RID 'F'->RID
'C'->RID 'F'->RID 'B'->RID 'D'->RID 'G'->RID
'G'->RID

バランスが崩れた例キーを変えて、バランスを取った例

13

制約を実現するためのインデックス①

制約を実現するために、自動的にインデックスが作成されます
–プライマリーキー（PK）を定義した場合
–ユニーク制約（一意性制約）を定義した場合

CREATE TABLEの列定義で制約を指定する場合
–インデックスの名前やスキーマは指定できず、自動生成される

CREATE TABLE emp (emp_no INT NOT NULL PRIMARY KEY)

CREATE TABLEでは指定せず、ALTER TABLEで指定する場合
–制約名と同じ名前でインデックスが作成される
CREATE TABLE emp (emp_no INT NOT NULL)
ALTER TABLE emp ADD CONSTRAINT IDX_PK PRIMARY KEY (emp_no)

IDX_PKという名前で、制約
とインデックスを作成
14

制約を実現するためのインデックス②
DB2では、ユニーク制約とユニーク・インデックスは異なる
どちらもインデックスが作成される

ユニーク制約ユニーク・インデックス
– 列にNULLを含められない – NULLは1つまで含めることが可能

CREATE TABLE t1 (c1 INT NOT NULL) CREATE TABLE t3 (c1 INT NOT NULL)
ALTER TABLE t1 ADD CONSTRAINT uni1 CREATE UNIQUE INDEX uni3 ON t3(c1)
UNIQUE (c1)

CREATE TABLE t2 (c1 INT) CREATE TABLE t4 (c1 INT)
ALTER TABLE t2 ADD CONSTRAINT uni2 CREATE UNIQUE INDEX uni4 ON t4(c1)
UNIQUE (c1)
エラー
SQL0542N "C1" という名前の列は、
NULL値を含む可能性があるので、主キー
およびユニーク・キー制約の列にすること
ができません。 SQLSTATE=42831

15

良いインデックスとは？
コンパクトで、インデックス・レベルが小さいインデックスが良い
– コンパクト＝更新が速く、メモリ使用量が少ない
– インデックス・レベル＝リーフにたどり着くまでに必要なI/O数

インデックスのリーフノード数、レベル数はSYSCAT.INDEXESで確認可能
– NLEAF（リーフノード数）
– NLEVELS（インデックスレベル数）
• もしくはMON_GET_INDEX表関数でも取得可能

例） SELECT INDNAME,NLEVELS,NLEAF FROM SYSCAT.INDEXES WHERE TABSCHEMA='SIM'
AND TABNAME='EMP'

INDNAME NLEVELS NLEAF
IDX_PK 3 1251 ← ※プライマリーキー
IDX_TITLE 3 693

（参考)SYSCAT.INDEXES
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001047.html

（参考）MON_GET_INDEX表関数
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0055026.html

16

事前に統計情報の更新
(RUNSTATS)が必要
インデックスのサイズ①
インデックスのサイズは、データの型やカーディナリティで大きく変わる
例：EMP表（300,024行）
– 表のサイズ
SELECT TABSCHEMA,TABNAME,NPAGES FROM SYSCAT.TABLES WHERE
TABSCHEMA='SIM' AND TABNAME='EMP'
• もしくは db2pd -db EMPLOYEE -tcbstats all
TABSCHEMA TABNAME NPAGES
SIM EMP 4449
• 上記はページ数なので、4KB x 4449 = 約17MB

– インデックスのサイズ(db2pdでも表示可能だが、誤差があるため非推奨）

SELECT TABNAME,INDEX_OBJECT_P_SIZE,INDEX_OBJECT_L_SIZE FROM
TABLE(ADMIN_GET_INDEX_INFO('I','SIM','IDX_TITLE')) AS T

• 表の全インデックスサイズの合計[KB]
• 例ではTITLE列とPK、2つのインデックスがある場合で、約8MB
TABNAME INDEX_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE

17 EMP 8064 8064

事前に統計情報の更新
(RUNSTATS)が必要
インデックスのサイズ②
インデックスのサイズは、ほとんどがリーフページが占めるので、リーフページ
の数でサイズの代わりにする事は可能
–インデックスのリーフページ数は、SYSCAT.INDEXESのNLEAF列で得ら
れる

SELECT INDNAME,NLEAF FROM SYSCAT.INDEXES WHERE INDSCHEMA='SIM'

INDNAME NLEAF
IDX_PK 1251
IDX_TITLE 693

• 上記はページ数なので
• IDX_PK = 4KB x 1251 = 約5MB
• IDX_PK = 4KB x 693 = 約3MB

18

インデックスはどこに、どれだけ作成すべきか？①

インデックスの位置を決めるのは難しい

インデックスを付けるべき箇所
– 表にプライマリーキーは（ほぼ）必須
• とても小さい表は例外
– 読み込み中心のシステム（表）は、インデックスが多く
ても問題無い
– WHERE句で検索やジョインによく使用される列
– カーディナリティが高い列
• SYSCAT.COLUMNS表のCOLCARD列で確認
TABNAME COLNAME COLCARD
EMP EMP_NO 300024
インデックスを避けた方が良い箇所 EMP GENDER 2
– 更新が多いシステム（表）はインデックスを控えめに EMP HIRE_DATE 5632
– カーディナリティが低い列（フラグ列など） EMP NAME 274432
EMP SALARY 51200
EMP TITLE 7
19

インデックスはどこに、どれだけ作成すべきか？②
設計アドバイザ（db2advis）を使う
–実行するSQLの種類と頻度を与えると推奨され
--#SET FREQUENCY 2
るインデックスが得られる SELECT * FROM EMP;
• ファイルにSQLと頻度（FREQUENCY）を書く
• db2advis -d DB名 -i ファイル名 --#SET FREQUENCY 10
SELECT EMP_NO,NAME FROM EMP
–-gを指定すると、パッケージキャッシュに保存さ WHERE HIRE_DATE < ?;
れたSQLを元に推奨値が得られる
• db2advis -d DB名 -g

-- 推奨される索引のリスト
-- ===========================
-- 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;

※（参考）パッケージキャッシュからあふれたSQLをイベントモニターで記録して、アドバイ
ザに渡す方法もあります
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.a
dmin.mon.doc/doc/t0057193.html
20

ここまでのまとめ

インデックスにはメリットとデメリット（コスト）がある
–メリット
• 検索処理（SELECT）が高速になる ... ただし検索に使える場合のみ
–デメリット
• 更新処理（INSERT,UPDATE,DELETE）が遅くなる
• ディスクを消費する
設計アドバイザーを使って、適切なインデックスを計算する事ができる

インデックスの構造はB+ Tree
–検索が一定時間

良いインデックスとは？
–コンパクト＆インデックス・レベルが小さい

21

そのインデックス使われていますか？

使われていないインデックスは削除するのが理想
–更新が遅くなり、ディスク消費が増えるのに、メリットが無いため

使われているかどうかの確認が重要
–インデックスを使うか使わないかは、DB2が自動的に判断する
–アクセスプラン（実行計画）を取得することで分かる

アクセスプラン取得前には統計情報の更新が必要
–RUNSTATSコマンドで統計情報を最新に更新する
• 自動化も可能（デフォルトで自動実行される）
• コマンドの詳細は本資料の補足ページ、もしくはCLUB DB2「アクセス・
プラン編」や「運用管理編」の資料を参照
http://www.ibm.com/developerworks/wikis/display/clubdb2/materials

22

アクセスプラン（実行計画）を確認するツール
db2exfmt
–もっとも詳細な情報が得られる
• これがお勧めです（次ページに詳細）

db2expln
–事前準備無しですぐ使える
例）
>db2expln -d DB名 -t -q "SELECT ..."
>db2expln -d DB名 -t -f ファイル

DataStudio (GUI)内蔵のVisual Explain
–GUIで操作可能

23

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)"

SET EXPLAIN MODE EXPLAIN後に実行したSQLのアクセスプランが
EXPLAIN表に格納されるので、それをdb2exfmtコマンドで取り出す

例）
db2 CONNECT TO EMPLOYEE
db2 SET CURRENT EXPLAIN MODE EXPLAIN
EXPLAIN表へアクセスプランを書き出す
db2 "任意のSQL" EXPLAIN MODEなのでSQLは実行されない
db2 SET CURRENT EXPLAIN MODE NO
db2exfmt -1 -d EMPLOYEE -o myexplain.txt

標準的なオプションで直近（最新）のEXPLAIN結果を出力データーベース名出力ファイル名

参考）SYSINSTALLOBJECTSプロシージャ
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0011876.html
24

インデックスを使う？使わない？①
SELECT * FROM EMP WHERE SELECT * FROM EMP WHERE TITLE IN
TITLE = 'Engineer' ('Staff','Engineer','Senior Engineer')

105710
表（SIM.EMP）の表ス
FETCH キャン（TBSCAN）にな
( 2) っている
2107.58 243145
2067.03 TBSCAN
/---+----¥ ( 2)
105710 300024 3991.44
IXSCAN TABLE: SIM 4071
TITLE 数
( 3) EMP |
300024 Assistant Engineer 15128
409.102 Q1
TABLE: SIM Engineer 105710
268.889
| EMP Manager 9
インデックス・スキ
300024 ャンになっている Q3 Senior Engineer 30050
Senior Staff 26590
INDEX: SIM
Staff 107385
IDX_TITLE
Q1 Technique Leader 15152
25

インデックスとアクセスプラン
インデックスが非効率と判断すれば使わない
–表から多くのデータを取得する場合、インデックス・スキャンより
も表スキャンの方が速い（事が多い）
平均処理時間
インデックス・スキャン

表スキャン

ここで、インデックス・スキャンから表ス
キャンに切り替わる（理想）

０１００
取得したいデータ量 [%]
26

表の物理配置（概念図）
多くのデータベースでは、行単位ではなく、ページ単位でデータを格納する
–DB2の場合は、4KB,8KB,16KB,32KBの4つから選択可能
• デフォルトは4KB
表スペース（ディスク領域）にページ単位でデータを格納していく
–基本的に順番にデータを並べる、シンプルな配置
• スペース効率を最優先に考えた配置
–DB2は「ページをまたいだ形」では行を格納しない
DB2はページ単位でディスクI/O処理をする
–行単位、列単位のI/O処理ではない

T1表の行1 T1表の行6 ページ（ディスクIOの単位）
（管理用（管理用行2 行3 行7

領域）領域）行4 行5
行5(続き)

T2表の行1 T3表の行1 T2表の行6
行2 行3 行2 行3
行4 行4
行5

表スペース
27

複合インデックス（コンポジット・インデックス）
0.599862
インデックス作成時に複数の列を指定可能 FETCH
→ 複合インデックス ( 2)
17.639
2.59986
CREATE INDEX インデックス名 ON /---+----¥
表名(列名,列名, ...) 1
0.599862 300024 GRPBY
IXSCAN TABLE: SIM ( 2)
複合インデックスの使い道 ( 3) EMP 768.856
13.5753 Q1 533.186
例） CREATE INDEX IDX_COMP ON 2 |
EMP(GENDER,SALARY) | 179973
300024 IXSCAN
INDEX: SIM ( 3)
IDX_COMP 723.256
①複合条件の高速化 Q1 533.186
• SELECT * FROM EMP WHERE GENDER='M' |
AND SALARY > 110000 300024
INDEX: SIM
IDX_COMP
②インデックスのみのアクセスでデータを返す
Q1
• SELECT AVG(SALARY) FROM EMP WHERE
GENDER='M'

28

インデックスを使う？使わない？②

複合インデックスの先頭を条件に含まない場合
例） 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
29

886.206
インデックスのANDingとORing FETCH
( 2)
635.609
検索に利用できるインデックスが複数ある場合 559.787
/---+----¥
1. それぞれのインデックスをスキャンして、対象 886.206 300024
のRIDを集める RIDSCN TABLE: SIM
( 3) EMP
2. その結果をAND（もしくはOR）処理をして、必 238.221 Q1
112.351
要なRID一覧を得る |
886.206
→ インデックス ANDing （もしくはORing） SORT
( 4)
– 複合インデックス無しでも、同様の検索が可能 238.22
112.351
|
例） 886.206
SELECT NAME FROM EMP IXAND
( 5)
WHERE TITLE='Senior Staff' AND EMP_NO < 20000; 237.289
112.351
/-----+------¥
9999.37 26590
IXSCAN IXSCAN
TITLE列には単一列 EMP_NOはプライマリキ ( 6) ( 7)
のインデックスが作ーなので単一列のインデ 93.5571 139.527
成済みックスが存在する 44.2632 68.0879
| |
300024 300024
INDEX: SIM INDEX: SIM
IDX_PK IDX_TITLE
30

[参考] ビットマップ・インデックス
ビットマップインデックスとは？
–B-Treeではく、列のそれぞれの値ごとにビットマップ(0/1)を作成する
–データ量（行）がとても多く、データの種類が少ない場合に有効
–複合条件の検索に特に有効（ビットマップのANDは高速に行える）
EMP_NO < 20000のビットマップ
EMP_NO NAME TITLE TITLE='Senior Staff'のビットマップ
----------- -------------------- ---------------
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 □ □ AND □ TITLE='Senior Staff'
20001 Atreye Eppinger Engineer □ □ □ AND EMP_NO < 20000
20002 Jaber Brender Staff □ □ □ のRID情報を持つビッ
20003 Munehiko Coors Staff □ □ □ トマップインデックス
20004 Radoslaw Pfau Senior Staff □ ■ □

DB2では明示的にビットマップ・インデックスを作成するのではなく、インデッ
クスANDing処理時に内部で自動的に作成される
–ダイナミック・ビットマップ・インデックス
31

INCLUDE句
DB2にはINCLUDE句によって、インデックスのリーフに対象列
以外の列を保存できる
–条件：UNIQUEインデックスであること
• 特定の条件に合致したINCLUDEを指定することで、イン
デックスのみのアクセスで結果を返すことが可能になる

1
IXSCAN
例） EMP_NOを検索条件にして、アンサーセットにNAMEを返 ( 2)
すクエリー 13.577
> SELECT NAME FROM EMP WHERE EMP_NO=? 2
• 通常 |
• EMP_NO列に作成されたインデックスをインデック・ススキャンして
RIDを得た後に表からデータを取得
300024
• INCLUDEを使った場合 INDEX: SIM
IDX1
> CREATE UNIQUE INDEX IDX1 ON EMP(EMP_NO) INCLUDE (NAME)
• IDX1をインデックス・スキャンするだけでアンサーセットに必要なデ
Q1
ータが得られる（表にアクセスしていない）

32

NULLとインデックス 30059
FETCH
( 2)
951.497
NULLへのRIDをインデックスに含めるか？はRDBによっ 917.005
/---+----¥
て異なる 30059 300024
–DB2 → 含める RIDSCN TABLE: SIM
–Oracle→ 含めない ( 3) EMP
176.321 Q1
70.8569
NULLを含めるメリット |
–"WHERE c IS (NOT) NULL"でインデックスを使用した 30059
アクセスが可能 SORT
( 4)
–例） 176.32
CREATE INDEX IDX_COMM ON EMP(COMM) 70.8569
|
SELECT * FROM EMP WHERE COMM IS NOT NULL
30059
IXSCAN
( 5)
NULLを含めないメリット 143.41
–NULLが多い列へのインデックスサイズが小さくなる 70.8569
|
300024
INDEX: SIM
IDX_COMM

33

インデックスの順序と逆スキャン

インデックスには順序がある
> 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を付ける

34

クラスター率とクラスター・インデックス
クラスター率とは
– データが「欲しい順番どおりに物理的に並んでいる率」
– 物理的に順番どおりに並んでいると、取り出すのが速い
クラスター・インデックスとは
– クラスター・インデックスが作成された表ではキー値が近いものでかたまるよう
にインサートされる
– 読み取ったページに次のキーが入っている確率が上がり、取り出しページ数
が少なく、効率が上がる
– 1つの表に1つだけ作成できる

クラスター・インデックスを作成する例
非クラスター・
インデックス CREATE INDEX indc ON T1(C1) CLUSTER クラスター・インデックス

８データベージ（例：散らばっている）４データベージ（例：かたまっている）

35

関数インデックスは無いけれど
DB2に関数インデックスは無い
関数が使われていても、インデッ
クスは利用可能
–オプティマイザが解釈可能な 24
範囲に限られる FETCH
( 2)
23.1914
3.41205
SELECT * FROM EMP WHERE TITLE='Manager' /---+----¥
=> インデックス・スキャン 24 443308
IXSCAN TABLE: SIM
( 3) TITLES
13.6134 Q1
SELECT * FROM TITLES WHERE 2
SUBSTR(TITLE,1,1)='M' |
443308 インデックス･
INDEX: SIM スキャン
TITLES_TITLE
Q1

36

まとめ
インデックスはRDBパフォーマンスチューニングのキモ
– 適切なところにインデックスを作成できたのであれば、チューニングは半分
以上終わったようなもの
– 制約の実現にもインデックスが使われる
– 設計アドバイザーを活用
– アクセスプランで確認

DB2の特徴的な機能
– NULLへのポインタを含む
– INCLUDE
– クラスターインデックス
– リバーススキャンがデフォルト
– ジャンプスキャン

今日話せなかったこと
– MDCのブロック・インデックス,パーティション表とインデックス,インデックス
の圧縮 ... など

37

参考資料
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
38

補足資料

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

39

良く使うコマンド

良く使うDB2のコマンド
インスタンス開始 db2start
インスタンス停止 db2stop [force]
DB作成 db2 "CREATE DB db名 ..."
DB削除 db2 "DROP DB db名"
DB一覧表示 db2 "LIST DB DIRECTORY"
接続ユーザ一覧 db2 "LIST APPLICATIONS"
DBに接続 db2 "CONNECT TO db名 USER userid USING password"
接続解除 db2 "TERMINATE"
SQLを実行 db2 "任意のSQL"
db2 +c "任意のSQL" ←AUTO COMMITをOFFにして実行
ファイルに記録し（ファイルに;区切りでコマンドやSQLを記述しておいて）
たコマンドの実行
db2 -tvf ファイル名

40

構成パラメタ
設定は構成パラメタの変更で行うシステム（レジストリ）
DB2の構成パラメタは３種類インスタンス (DBM CFG)
–影響範囲が異なるデータベース (DB CFG)
–調整は、DBコンフィグが中心

影響範囲取得更新
レジストリ変数システム全体も db2set [-all] db2set REG1=VAL1
しくはインスタン
ス内
データベースマインスタンス内 GET DBM CFG UPDATE DBM CFG USING
ネージャ(DBM) cfg1 val1 [cfg2
構成パラメータ val2 ...]
ー
データベースデータベース GET DB CFG FOR db名 UPDATE DB CFG FOR db名
(DB)構成パラ USING cfg1 val1 [cfg2
メーター val2 ..]
41

REORGコマンド（表の再編成）

REORGはオンライン動作可能
– REORG中にユーザーが対象のテーブル、インデックスにアクセス
可能
INPLACEを指定すると、インプレース動作
テーブルのREORG
REORG TABLE テーブル名 [INPLACE] [ALLOW {READ|WRITE|NO} ACCESS]

•ALLOW READ ACCESS - REORG中のテーブルへのアクセスを読み取りのみ許可
•ALLOW WRITE ACCESS - REORG中のテーブルへの読み書きアクセスを許可（INPLACE指定時にのみ指定可能）
•ALLOW NO ACCESS - REORG中のテーブルへのアクセスを禁止（INPLACEとの同時指定不可）

インデックスのREORG（テーブル毎）
REORG INDEXES ALL FOR TABLE テーブル名 [ALLOW {READ|WRITE|NO} ACCESS]

•ALLOW READ ACCESS - REORG中のインデックスへのアクセスを読み取りのみ許可
•ALLOW WRITE ACCESS - REORG中のインデックスへの読み書きアクセスを許可
•ALLOW NO ACCESS - REORG中のインデックスへのアクセスを禁止

42

RUNSTATSコマンド（統計情報の更新）多くの場合、この
基本形でOK
RUNSTATSコマンドで統計情報を更新する
RUNSTATS ON TABLE スキーマ名.表名
RUNSTATS ON TABLE スキーマ名.表名 AND INDEXES ALL
（※DB2 10.1からスキーマ名が省略可能になっています）

– RUNSTATS実行中でも表に読み書きアクセス可能

データに「偏り」がある場合、
少し進んだ使い方拡張統計を試してください
– ①拡張統計で収集する
RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION
RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION AND SAMPLED
DETAILED INDEXES ALL

表を5%サンプリング
– ②サンプリングでRUNSTATSの実行時間を短くする
RUNSTATS ON TABLE SIM.DEPARTMENTS WITH DISTRIBTION TABLESAMPLE
BERNOULLI (5)
43

補足：サンプル表で使用したデータについて
サンプル表の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.

44

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

More Related Content

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