Oracleのインデックスについて
はじめに
こんにちは。株式会社SHIFTの小澤です。
Oracleのインデックスについて学ぶ場合、該当するOracle製品のマニュアルを通して読んでみるのが一番良いのではないかと思います。
ただ、英語版だとそもそも英語が分からないので読んでもなかなか理解できないですし、日本語版だと本来の英語の文から少し意味合いが変わってしまったり、日本語としてなかなか理解しづらいものになってしまっていたりするため、あまり好きではないなという方もいるかと思います。
本ブログでは、Oracleのインデックスについて、Oracleの性能問題を扱う上で最低限知っておいていただきたい内容を、マニュアルよりも分かりやすい日本語を目標にして書いていこうと思います。
この記事を読むとできるようになること
Oracleのインデックスを使ってどのように性能を良くできるのかを説明できるようになる(Oracle以外のデータベースでも基本的には同じ考え方です)
Oracleをバージョンアップして性能が良くなると思ったのに、なぜ性能が悪くなってしまうことがあるのかを説明できるようになる
Oracleのインデックスを張って性能が良くなると思ったのに、なぜ処理が遅くなることがあるのかを説明できるようになる
Oracleのインデックスがどのように張ってあるかを聞かれたときに、さっと確認して説明できるようになる(今後このブログ内でも繰り返し使っていきます)
基本的な考え方
Oracleデータはテーブル保存
Oracleのデータはテーブルに保存されています。
Oracleに限らずデータベースのテーブルに保存されているデータを取得したい時は、SQLを実行してテーブルのデータをSelectすればデータを取得できます。テーブルのデータを取得する方法は主に2通りあります。
1つはテーブルのデータ全体をスキャンする方法(テーブルフルスキャン)で、もう1つは、まずインデックスを使ってテーブルの行の番地(ROWID)を調べてから、その行の番地(ROWID)に対応するテーブルのデータだけを取得する方法です。
あるテーブルのデータ全体のうち、最終的に取得したいのはそのテーブルの一部の行だけだったとします。この場合、もしそのテーブルが小さければ(例えばテーブルのブロック全体が数メガで、メモリが数百ギガの場合)、ディスク上に保存しれているテーブルのデータブロック全体に対してテーブルフルスキャンを行ってデータブロック全体をメモリに読み込み、次にメモリ上の必要なデータに絞り込む、という方法を使ってもあまり問題にならないです。
一方で、もしそのテーブルが巨大な場合は(例えばディスク上のデータブロックが数テラで、メモリが数百ギガの場合)、最初のテーブルフルスキャンの実行の段階で非常に長い時間がかかりますし、その後メモリに全部のデータブロックは載せきれないので、何回にも分けて必要なデータへと絞り込んでいくことになるのでメモリ上でのデータの絞り込みにも長い時間がかかります。
ですので、テーブル全体のデータブロックが多い場合は、インデックスを使って先に目的のデータ行のROWIDを取得して、その次にデータブロックのROWIDに対応する行だけに絞って取得する方がサーバリソースの消費も少なく、かつ高速に処理できます。先にインデックスを調べてROWIDを調べてからテーブルのデータを取りに行くのでその分の手間はかかっていますが、巨大な表になればなるほど、テーブルフルスキャンでデータブロック全体を読み取るよりも、むしろひと手間かけてインデックスでROWIDを絞り込んでからテーブルのデータブロックの一部の行だけを取ってくるやり方の方が結果的にデータの取得が速く終わるということになります。
｜「インデックス・スキャン＋ROWIDによるテーブルアクセス」が高速
基本的に、「テーブルフルスキャン」よりも「インデックス・スキャン＋ROWIDによるテーブルアクセス」の方が高速、と思うようにしてください。(条件がそろえばテーブルフルスキャンが良いこともあるのですが上級者向けです)
皆さんが扱うシステムでOracleを使ったものというと、データベースサーバでOracleが動いていて、アプリケーションサーバが裏でOracleにSQLを実行しつつ、ユーザがWebブラウザで操作できる画面を提供している、というような3層構造のWebアプリケーションシステムが思い浮かぶかと思います。
ユーザがブラウザ画面で何らかの検索を行うとして、アプリケーションがOracleにSQLを発行してOracleがデータを取って来る方法としてテーブルフルスキャンを実行するケースを考えます。
まずアプリケーションサーバはSQLのSelectを発行してネットワーク越しにOracleにSelect文を送ります。OracleはSQLを受け取り、テーブルフルスキャンを実行してサーバのディスクからテーブルの全データブロックを取得してメモリに載せ、アプリケーションサーバに対してネットワーク越しにデータを送り返します。
アプリケーションサーバは返ってきたデータを受け取り、メモリに載せ、その中からユーザが検索で欲しかったデータに絞り込んでからHTMLを組み立て、ネットワーク越しにユーザのWebブラウザに送ります。
この例の中で、アプリケーションサーバが受け取ったデータの中からユーザが検索で欲しかったデータに絞り込むところがありました。もし最初からOracleが、ユーザが検索で欲しかったデータだけをテーブルから取るようにすれば、つまり、テーブルフルスキャンの代わりにインデックス・スキャンでユーザが検索で欲しいデータ行のROWIDに絞ってテーブルアクセスを実行すれば、Oracleのメモリにはユーザが検索で欲しいデータだけが載り、ユーザが検索で欲しいデータだけがネットワーク越しにアプリケーションサーバに転送され、ユーザが検索で欲しいデータだけがアプリケーションサーバのメモリに載り、アプリケーションサーバが必要なデータに絞り込む処理は省略できます。
つまり、Oracleに「テーブルフルスキャン」の代わりに「インデックス・スキャン＋ROWIDによるテーブルアクセス」をさせることにより、Oracleがディスクから読み込むI/O量を削減し、Oracleのメモリに読み込むデータブロック数を削減し、アプリケーションサーバへのネットワーク転送量を削減し、アプリケーションサーバのメモリ使用量とCPU使用量を削減し、ユーザのWebブラウザにより速くHTMLを転送できる、ということになります。
このように、インデックスを使うとテーブルフルスキャンをするよりも性能向上が期待できるということなのですが、インデックスを使ってROWIDを調べる手間はあるのでこの手間をどのくらい少なくできるのかというのが大事になってきます。インデックス使ってROWIDを調べる手間が多くなってしまうと、インデックスを使った割には性能は大して良くならない、という結果もあり得ます。
｜データを効率よく取るには
インデックスを使ってデータを効率よく取ってくるためには、インデックスを少し読んだだけで必要なROWIDが取ってこれる、という状況を目指すことになります。テーブルフルスキャンでデータを取ってくるときに、必要でないデータも含めて全部取ってきてしまうのでサーバリソースをいろいろと無駄に使ってしまって時間がかかるということが起きていましたが、インデックスについても同じように、必要でない情報も含めていろいろと取ってきてしまうとサーバリソースを無駄に使ってしまうということが起きます。
インデックスの情報もテーブルと同じようにディスクに保存されているので、インデックスを必要でないブロック含めて全体を読み取ってしまうとディスクI/Oが多くなりますし、そうではなくてインデックスの読み込み量を最小限に抑えられればそれだけ性能が良くなります。
例えば、会社のデータベースに、全社員のデータがマイナンバーや住所を含めてテーブルに保存してあったとします。この全社員のテーブルから、ある3人の社員のデータを効率的に取ってくる方法を考えてみます。
全社員が10人以下なら、インデックスを調べる手間をかけるよりはテーブルフルスキャンの方がむしろ速いかもしれません。非常に大きい会社で、全社員のテーブルのレコード数が数10万件になっている場合は、テーブルフルスキャンを実行するのではなく、例えばマイナンバーにインデックスを張っておいて、そのインデックスで3名分のROWIDを調べてから社員テーブルをROWIDアクセスで3件だけ取ってくる方が圧倒的に速いはずです。
同じインデックス利用でも、都道府県名にインデックスを張ってそのインデックスを利用しようとすると今度はインデックスを使ったのにも関わらず遅くなってしまうかもしれません。都道府県名にインデックスを張ってこれを使ってしまうと、例えば住所が東京都の社員が全社員30万人のうち5万人もいたとなると、都道府県名のインデックスから5万件のROWIDが見つかってしまい、テーブルから5万件のROWIDアクセスでデータを取得してメモリに載せた後、目的の3名に絞り込む処理が必要になってしまうのでテーブルフルスキャンより遅くなってしまうこともあり得ます。
このように、インデックスを使ってどのくらい効率的にROWIDを調べることができるか、ということが性能を出すためにはポイントになります。
先ほどのマイナンバーのケースのように、インデックスの値でテーブルの行を少数まで絞り込める場合は(マイナンバーは国民一人一人違う番号ですのでマイナンバーが3件わかれば全従業員から3名を特定できるはずです)、マイナンバーは選択的であると言い、読み込みブロック数が少なくなります。「選択的」とは普段聞かない用語だと思いますが、マイナンバーのような社員一人一人に別々の数字が割り振られていて、マイナンバーを見れば社員が特定できるような状況では、マイナンバーは選択的(Selective)である、とかマイナンバーの選択性(Selectivity)が高い、とかマイナンバーのカーディナリティが高い、などと言います。
カーディナリティが高い値のインデックスを使えば、ROWIDを高速に調べられます。調べたROWIDでテーブルアクセスしますが、ROWIDでテーブルアクセスすると、ROWIDに対応する行が入っているデータブロックのみをディスクに取りにいきます。行が1ブロックに収まっていれば1ブロックだけ読み込んでディスク読み込みを終了できます。
もし運悪く100ブロックに散らばってしまっていたら、100ブロック読む必要があります(そのようなことはあまりないとは思いますが)。つまり、テーブルのデータブロックの物理的な格納状況も処理性能に影響します。
テーブルのそれぞれの行はできるだけ同じブロックに格納するようにしておくのが、インデックスの効果を最大限引き出すためには重要です(基本的にOracleが自動で面倒を見てくれるので普段はあまり気にすることはありませんが)。
｜ここまでのポイントを整理
大きなテーブルには「インデックス・スキャン＋ROWIDによるテーブルアクセス」が良い
大きなテーブルには「テーブルフルスキャン」は実行しないほうが良い
性能が良いSQLは、ディスクI/O、メモリ使用量、ネットワーク転送量、CPU使用時間が少ない。サーバーリソース使用量が少ないSQLは性能が良い。
カーディナリティが高い列にインデックスを張り、これを使って検索すると速い
テーブルの行はなるべく同じデータブロックに入れる(基本的にOracleが面倒を見てくれている)
テーブルフルスキャンでも工夫の余地はある
テーブルフルスキャンでは、Oracleはマルチブロック読み込み(1回のI/Oで複数のブロックを読み込む)を使い、テーブルのスキャンを効率化します。
インデックスを使って特定のデータのROWIDを調べるケースではシングルブロック読み込み(1回のI/Oで1つのブロックを読み込む)になるので、インデックスを利用する時は処理に必要なシングルブロック読み込み数を減らすのがポイントになります。
基本的にはテーブルフルスキャンを避けてインデックス検索を行ったほうが効率が良いケースが多いと思います。
ただ、取得したい行数が多いなどの理由でテーブルフルスキャンが必要なケースは実際にあるため、Oracleではパーティショニング、パラレルクエリーなどの技術を用意していますし、ディスク側でもディスクキャッシュ、高速大容量のメモリー、フラッシュディスクの利用による高速化、といったこともできるようになっています。
こういった様々な技術をどう組み合わせたら要件にあった性能が出せるのかを実際に検証して知見を蓄積していくことが重要です。
基本をおさえた上で、こういった、万人向けではないかもしれないものの、条件が揃えば効果絶大ということもあるので、「どうにもうまくいかない」という時にもあきらめずにいろいろ試してみましょう。
バージョンアップはすべきだが性能面の検証が重要
アプリケーションがOracleからデータを取得する時はOracleに対してSelect文を投げますが、このSelect文には「このSelect文を最も効率的に実行するためにはテーブルフルスキャンを使うとサーバーリソースを大量消費してしまって遅くなってしまうので、まずこのインデックスでWhere句の条件にマッチするROWIDを1個に絞り込んで、次にROWIDを使ってテーブルを1件だけ取得しましょう」のようなことは書いてありません(取得したいデータはSQLに表現しますが、データブロックのアクセス方法はSQLに表現しません。ヒント句の指定である程度指定できますがここでは考えないことにします)。
Select文を実行する時、テーブルフルスキャンでデータを全件持ってくるのか、最初にインデックスでROWID調べてからそのROWIDを使ってテーブルアクセスするのか、といったデータブロックへのアクセス方法とその順序はOracle自身があるルールに従って計算をして決定します。
ここで言う「データブロックへのアクセス方法とその順序」を「実行計画」と言いいます。「実行計画」を決めるプログラムのことを「オプティマイザ」と言います。また、基本的な考え方として「サーバーリソースを大量消費するようなデータ取得方法は性能が悪い」というのがあるので、「オプティマイザ」は「なるべくサーバーリソースの消費が少なくなるような手順でSQLを実行する方法を採用しよう」とします。
｜ここまでのポイントを整理
一旦整理すると、SQL文を実行する時には以下のようなことが行われています。
アプリケーションがSQL文をOracleに送る
OracleのオプティマイザがSQL文を確認して、性能が良い実行計画を決定する
Oracleが実行計画に従ってデータを取得する
Oracleが実行結果をアプリケーションに送り返す
つまり、インデックスを作成したとして、Select文を実行する時に実際にインデックスを使うか使わないかは、Oracleのオプティマイザが決定します。
このオプティマイザですが、Oracleのバージョンが上がるごとに少しずつ改訂が行われていますので、Oracleをバージョンアップすると、古いバージョンではインデックスを使って高速だったのに、新しいバージョンではなぜかオプティマイザがインデックスをうまく活用できず性能が落ちてしまった、ということがあり得ます。
こういった性能面でデグレするのが嫌なので一度本番稼働を始めたOracleはバージョンアップをしてはいけない、というルールの現場の方もいらっしゃると思います。
ただ、Oracle製品としては、古いバージョンで存在していた問題が新しいバージョンで修正されていたり、新しいバージョンでは性能面で機能アップしていたり便利な機能が新規追加されていたりすることがあるため、基本的には新しいバージョンにバージョンアップするのが良いはずです(新しいバージョンにバージョンアップすると機能や性能がアップするが、一定のデグレのリスクはある)。
バージョンアップのリスクを軽減するための基本的な考え方ですが、まずはアプリケーションのすべてのSQLの実行計画をバージョンアップ前後で比較し、古いバージョンで使用されていたインデックスを新しいバージョンでも引き続き利用して処理性能が落ちていないかを確認するのが大事なポイントです。また、インデックスが同じでもデータのカーディナリティやデータブロックの物理配置もSQL性能に影響するので、単にすべてのSQLの実行計画を確認しても不十分で、データの質と量、物理配置について入念に準備した上で本番で起こり得るあらゆるパターンを網羅的に検証するのが大事です。
と、教科書的に「あるべき論」を書くだけならすぐできますが、実際に本番稼働しているOraceで起こり得るデータ(パターンや量、物理配置)をすべて準備したうえでありとあらゆるSQLの実行パターン(パラメータも無数にあると思います)を漏れもなく検証して万が一にも性能問題が起きないように事前にすべての対策を打つ、というのは相当なコストと手間がかかる作業です。
そこで、実際の現場では、基本的には本番相当のデータ(パターンと量)を準備してSQLの全パターン(パラメータの種類と組み合わせ)を網羅的に検証するのが理想というのを念頭に置きつつも、実際には、売上に直結したり社外影響が大きいなどの高リスク処理に対してはコストと手間をかけてできる限りの検証を行い、逆に最悪性能劣化しても後からリカバリ可能な処理を低リスクとして少し妥協した検証にする、ということでコストと品質のバランスを取りながら最大限の効果が得られるようにするという考え方をします。
インデックスを必要以上に張るのは良くない
基本的にはインデックスは、テーブルからカーディナリティが高い列値を検索する時に高速化するためのものなので、Where句があるSelect/Update/Delete文の処理性能が上がります。
一方で、インデックスを利用する時に効率的に利用できないと意味が無いので、Insert/Update/Delete文を実行すると、値の挿入・更新の影響を受けるインデックスのデータブロックに対する更新が行われます(新たなデータを順番通りの位置に挿入したり、不要になったデータを削除したりします)。
つまり、インデックスが張られたテーブルに対してInsert/Update/Deleteを行ってインデックスのデータブロックの更新が必要になると、Insert/Update/Delete文自体の性能は低下します(インデックスのデータブロックの更新にかかる時間が余計にかかる)。
つまり、インデックス追加によりWhere句を持つSelect/Update/Delete文の検索については性能向上するが、Insert/Update/Deleteのデータ更新としての性能は劣化するという関係になりますので、必要な列にはインデックスを張るべきですが、不要なインデックスは削除するようにし、全体として最大限の性能が出るようにバランスを取っていくのが大事です。
インデックスの確認方法について
Oracleでは、インデックスの一覧はDBA_INDEXESビューで参照できます。
同じようなビューで、USER_INDEXESでは、アクセスしているユーザ自身のスキーマのインデックスを参照できますし、ALL_INDEXESでは、ユーザがアクセス権がある全てのインデックスを参照できます。
例えば、以下のようにしてSCOTTスキーマのEMPテーブルにインデックスを作成してみます。
sqlplus scott/tiger@orclpdb
create index emp_id1 on emp(empno, ename, deptno);
create index emp_id2 on emp(sal);
こうすると、Oracle側ではEMP表に2つのインデックスが作成されます。
各インデックスは、EMP表から指定された列値とそれに対応する行のROWIDを持っています。
SAL値が1000のEMPレコードを検索すると、OracleはEMP_ID2インデックスを使用してその値を検索してROWIDを見つけて、そのROWIDを使用してテーブルのレコードを取ってくることができます。
以下のようにして、USER_INDEXESでEMPテーブルに作ったインデックスを確認できます。
SQL> col table_name for a10
SQL> col index_name for a10
SQL> r
1 select table_name, index_name
2 from user_indexes
3* where table_name = 'EMP'
TABLE_NAME INDEX_NAME
---------- ----------
EMP PK_EMP
EMP EMP_ID1
EMP EMP_ID2インデックスが、テーブルのどの列に作成されているかを見たい場合は、USER_IND_COLUMNSでユーザー自身のスキーマのインデックスについて確認できます。
やはりALL_IND_COLUMNSはアクセス権があるインデックスについて、DBA_IND_COLUMNSでは自身のアクセス件に関係なくすべてのインデックスについて確認できます。
SQL> col index_name for a12
SQL> col column_name for a8
SQL> col table_name for a8
SQL> select table_name, index_name, column_name, column_position
2 from user_ind_columns
3 order by table_name, index_name, column_position;
TABLE_NA INDEX_NAME COLUMN_N COLUMN_POSITION
-------- ------------ -------- ---------------
DEPT PK_DEPT DEPTNO 1
EMP EMP_ID1 EMPNO 1
EMP EMP_ID1 ENAME 2
EMP EMP_ID1 DEPTNO 3
EMP EMP_ID2 SAL 1
EMP PK_EMP EMPNO 1
6 rows selected.EMP_ID1はEMPNO、ENAME、DEPTNOの各列にインデックスを付ける複合インデックスです。
EMP_ID2はSAL列のみのインデックスです。リストに表示されるCOLUMN_POSITIONは複合インデックス内の列の順序を示しています（この場合はEMPNO、ENAME、DEPTNOの順のインデックスになっています）。
【この公式ブロガーの他の記事を読む】
執筆者プロフィール：小澤 雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。
お問合せはお気軽に
https://service.shiftinc.jp/contact/
SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/
SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/
SHIFTの導入事例
https://service.shiftinc.jp/case/
お役立ち資料はこちら
https://service.shiftinc.jp/resources/
SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/