津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第6回 パフォーマンスの基礎である索引について

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF
今回は、パフォーマンスに一番影響する索引について説明します。

「パフォーマンスを向上するためには効率よい索引の設計を行って下さい」と言われますが、具体的にどのように設計すればよいか分からない方も多いと思います(多く作成すればオーバーヘッドになりますし、逆に必要なSQL文で索引が使用されないことがありますよね)。そこで今回はデータベースの原点に戻って索引について説明しようと思いますので、参考にして下さい。

Oracle Databaseには、いくつかの索引の種類があります。こちらを押さえておくと、さらに理解が進むと思います。

オラクルエンジニア通信:「Oracle Databaseの索引の種類 - Bツリー、ビットマップ、索引構成表、索引クラスタ

■1.索引とは
先ずは、パフォーマンスに関する基本中の基本である「索引とは」について簡単に説明します。皆さんは当然ご存知かと思いますが、復習のつもりで読んでください(もしかして知らなかったこともあるかもしれませんよ)。

テーブル内の特定のデータだけにアクセスする場合に、索引を使用することで効果的にアクセスできます。以前に説明したかと思いますが、数%のデータにアクセスする場合は索引スキャンは非常に高速です。しかし、アクセスするデータが多いと逆にフル・スキャンより遅くなります。これはデータ(索引)にアクセスする回数と1回の平均アクセス時間(平均サービスタイムとも言う)によって決まります(つまり、ストレージの性能によって索引スキャンとフル・スキャンのどちらが効率良いかの閾値は異なってきます。そのため、Oracleはオプティマイザ統計にシングルブロック読み取り性能とマルチブロック読み取り性能を入れて、より正確に判断するようにしています)。

以下のように同じデータ量にアクセスする場合には、フル・スキャンのシーケンシャル・アクセスの方が索引スキャンのランダム・アクセスより平均アクセス時間は速いです(シーケンシャル・アクセスは1回で読み込むサイズを大きくできるため)が、全件データにアクセスするためアクセス回数が多くなってしまいます。これは、大規模なテーブルでは更に増えます。

索引の良し悪しは、できるだけ少ないI/O(索引ブロックとデータブロックのアクセス)で目的のデータにアクセスできるかです。いくつかの索引タイプがありますが、基本はみな同じです。
img_tsushima_110420_01.png
■2.どのように作成するのか
それでは、どのように索引を作成すれば良いか説明します。基本は、主キー(自動的に一意索引が作成されます)以外で頻繁にWHERE句に指定されている列に作成します。ただし、索引が多すぎると更新のオーバーヘッドになるので最小限の索引にするようにします。そのためには、複数索引(複数列索引)を効率よく活用する必要があります、次のようなガイドラインを参考にしてください。(ここではBツリー索引をメインに説明します)。
  • 一意性が高い索引を
  • 使用頻度の高い列を先頭に
  • 選択率の低い列を先頭に
  • 非ユニーク索引にはキー圧縮
(1)一意性の高い索引を
Bツリー索引はテーブルに対して最適な一つの索引を使用します(ビットマップ索引は複数の索引が論理演算されますので、複数の索引を利用できます)。そのため、複数の列を条件に指定している場合はできるだけ全ての列で複数索引を作成して下さい(ただし、一意性の高くない列は指定しても効果が無いので注意して下さい)。そのように指定するのが一意性が高いのでアクセスするブロック数を少なくできます(つまり速くアクセスできます)。

(2)使用頻度の高い列を先頭に
全ての列の組合せの索引を作成すると数が多くなりますので、あまり使用頻度の多くない組合せ(または性能要件が高くない条件)は省くようにします。そのとき、作成する索引の先頭には頻繁に使用する列を持ってきて、多くのSQL文で使用されるようにします(索引は、大小比較でデータを絞り込みますので、先頭の値が決まらないと絞り込むことができません。そのため、索引の先頭の列は条件で使用されている必要があります)。Oracle9iからは索引スキップ・スキャン(指定されていない先頭の列をスキップして索引スキャンする)機能がありますので、使用されるようになりました。ただし、絞り込みの効果は悪いです(当然アクセスするブロックは多くなります)が、フル・スキャンより効率が良いときは使用できるようになっています。

(3)選択率の低い列を先頭に
複数索引を作成する場合に列の順番をあまり気にしていない方もいるかと思いますが、選択率の低い列(ユニーク性の高い列)から指定して下さい(これは、索引は先頭から比較していきますので、目的のキーにたどり着くまでのキー比較を少なくできるからです)。以下の例では、列c2の方がユニーク性が高いとすると、索引(c2,c1)が効率良いことになります。範囲条件(<,>など)を使用しているSQL文では、列のデータがユニーク性が高くても選択率が高くなりますので注意して下さい(以下の例で、列c1の方がユニーク性が高いとしても選択率が高い可能性があります)。この場合は、アクセスするキーが連続していないので、1回の索引レンジ・スキャンで効率よくアクセスできなくなります。
img_tsushima_110420_02.gif
(4)非ユニーク索引にはキー圧縮
キー圧縮(索引圧縮)は、以下のようにリーフ・ブロックの重複部分のみが圧縮されます。これはブロック内での重複データのみが対象になります。そのため、重複データが多い非一意索引の場合には圧縮効果が高くなりますので、使用すると良いと思います。非一意索引ではCOMPRESSオプションを指定するとデフォルトで全ての列が圧縮の対象になります。ただし、キー圧縮にはオーバーヘッド(各キーに2バイトと重複データに4バイト)がありますので、キーサイズが小さい索引にはあまり効果はありませんので注意して下さい。
img_tsushima_110420_03.png
 一意索引でもキー圧縮は使用できますが、先頭に重複度の高い列を指定しないと圧縮の効果が少なくなります。これは、先頭の列から圧縮対象を指定する必要があるからです。 (3)で説明したように、索引の先頭にユニーク性の高い列を指定した方が索引としては効果的ですが、圧縮は先頭に重複度の高いデータを指定した方が効果的です(全ての列が圧縮の対象である場合は変わりません)。索引は、アクセス効率を優先に検討するべきですので、一意索引などの全ての列で圧縮しないときは注意が必要です(圧縮によりBツリーの階層数が削減されないのであれば圧縮しない方が良いと思います)。

■3.索引のメンテナンスについて
索引を効果的に使用するには、次のことを定期的に監視してメンテナンスする必要があります。
  • 索引の断片化
  • 未使用索引
(1)索引の断片化
これは第1回でも触れましたが、索引はBツリーのレベル数(ルート・ブロックからリーフ・ブロックまでの階層)が高くなるとアクセス効率が低下します。データの削除が多く行われるとブロックの使用効率が低下してBツリーのレベル数が高くなります。その場合は索引を再構築(REBUILD)する必要があります。

索引のアクセス効率が悪いかは、以下のようにINDEX_STATSビューのHEIGHT(Bツリーの高さ)とDEL_LF_ROW/LF_ROWS(削除されたエントリーの割合)を確認します(INDEX_STATSビューはANALIZE INDEX >索引名< VALIDATE STRUCTUREで作成されます )。HEIGHT が4以上でDEL_LF_ROW/LF_ROWSが0.2を超える場合は効率が悪いので、索引の再構築を行うことを検討して下さい。また、データ件数が多いためにBツリーのレベル数が高い場合がありますので、それもアクセス効率が悪いです(Bツリーのレベル数は、キーサイズとデータ件数によります。つまり、リーフ・ブロックが多くなると階層が増えてしまいます)。その場合は索引をパーティション化することを検討して下さい。
img_tsushima_110420_04.gif
(2)未使用索引の削除
テーブルを更新する度に索引も更新されますので、索引が多く作成されていると索引の更新のためのオーバーヘッドを軽視することができなくなります。そのため、あまり作成し過ぎるのは良くありませんので、必要最小限にして下さい、とは言ってもどれが不要か分からなくなる場合もあると思います(運用していて「あるSQL文がパフォーマンス問題になりチューニングで索引を作成する」などを繰り返し行うと分からなくなる場合もありますね)。そのような場合には、以下のように使用していない索引を調べて削除すると良いと思います。

ある一定の期間、調査したい索引にMONITORING USAGEを指定します。その後に使用されたかをv$object_usageで確認します。列monitoringの値が"YES"であることは、現在監視中であることを示します。列usedの値が"NO"であることは、この索引は監視中にまだ使用されたことが無いことを示します。これは、EM(Oracle Enterprise Manager)からはもっと簡単に行うことができます。
img_tsushima_110420_05.gif
オラクルエンジニア通信:「使用していない索引(インデックス)の検出方法|オラクルエンジニア通信

ここまで、索引についていろいろ説明してきましたが、難しいという方もいると思います。そのような方のために、SQLアクセス・アドバイザ(効果的な索引の作成などをアドバイスしてくれる便利な機能)がありますので使用して下さい。

SQLアクセス・アドバイザは、アドバイス期間に実行したSQL文を基に、索引、マテリアライズド・ビュー、パーティションなどをアドアイスしますので(索引だけをアドバイス対象にもできます)、その期間に実行していないSQL文については実施してくれませんので注意して下さい。ただし、索引設計のノウハウがあまりない方には便利な機能だと思いますので、活用して下さい。

おさらいの意味でも、下記も併せてご覧ください。

オラクルエンジニア通信:「Oracle Databaseの索引の種類 - Bツリー、ビットマップ、索引構成表、索引クラスタ

4.おわりに
今回は索引についていろいろと説明しました。これまで連載を6回行ってきましたが少々疲れてきましたので、次回からは公開する間隔をもう少しあけさせていただきます(だいたい1カ月を予定しております)。ただし、ご質問いただければ1カ月しなくても公開する場合もありますので、質問をお待ちしています。今後ともよろしくお願いします。

それでは、次回まで、ごきげんよう。
img_tsushima.gif ■津島博士より
長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い、この連載を始めることに致しました。できるだけ長く続けたいと思いますのでよろしくお願いいたします。

Oracle Databaseは、技術の進化により非常に扱いやすくなったと思います。私自身も昔のバージョンを使用したころに比べると非常に楽になったと感じています。いろいろと進化したとはいえパフォーマンス問題が発生しなくなった訳ではありません。今でも多くの担当者が色々と苦労していると思います。その中でスキルや機能を知らずに苦労している場合もあるように思いますので、ここで紹介していけたらと考えています。

この連載では、このようなOracle技術者(データベース技術者)の方へのアドバイスとして様々なパフォーマンス問題を題材に解説していこうと考えています。既にデータベース運用を行っている管理者、これから管理者を目指す方までを対象に、様々な疑問に対して少しでも何かの手助けになればと願っています。できるだけ読者の皆様からの疑問に答えていきたいと思っておりますので(問合せなどの具体例を使用して説明した方が分かりやすいと思いますので)、パフォーマンス問題に関する様々な質問をお願いいたします。

津島博士の記事についてのご質問はこちらまでお願いいたします。

津島博士のパフォーマンス講座 indexページ▶▶