このページをはてなブックマークに追加このページを含むはてなブックマーク このページをlivedoor クリップに追加このページを含むlivedoor クリップ

目次

インデックス

  • インデックスの長所として、検索速度が速くなる場合があるという点が挙げられる。
  • インデックスの短所として、インデックスを格納するためのディスク容量を必要とする点と更新処理の性能が悪くなるという点が挙げられる。
    • つまり、インデックスには必ず短所はあるが、必ずしも長所の恩恵を受けられるとは限らないといえる。
      • それでもインデックスを構築すべきか頭を悩ます現場が多いのは、必ずしも恩恵を受けることができないが、受けた場合に劇的な効果を生むからである。
  • あくまでSQL処理時に最適なインデックスを選択して初めて恩恵を受けられる。
    • この最適なインデックスを選択する役割を担うのが、SQLのプランナ部分である。
      • プランナはどのテーブルのどのカラムにインデックスが構築されているのかという情報を活用してインデックスを選択し、適用しようとする。
  • インデックスを設定しても必ず検索時にインデックスが使用されるとは限らない。
    • IS NULL,IS NOT NULL,<>,LIKE演算子などを使用した場合(ただし、LIKE演算子による前方一致検索では使用される)
      • インデックスのキー列そのものを検討すべき。
    • インデックス列に対して演算や関数を適用している。
      • 演算を右辺に集めておくことでインデックスが使用される。
    • 複合インデックスを設定していて、かつインデックスの先頭列がWHERE句に含まれていない場合。
      • インデックスのキー列そのものを検討すべき。
  • インデックスは全件総なめを防ぐことができる。
  • インデックスを使う場合は、常に順序通りに並んでなければならない。つまり、データに何らかの追加・更新があった場合は、即座に並び替えが行わなければならないということである。
    • 一般にインデックスを設定すると更新に負荷がかかるのはこのためである。
      • ただし、レコードの追加・更新・削除でも検索が重要な意味を持つため、インデックスが必ずしも更新処理を遅くするわけではない。
  • あくまでSQL処理時に最適なインデックスを選択して初めて恩恵を得られる。
    • この最適なインデックスを選択する役割を担うのが、SQLのプランナ部分である。
  • インデックスの数が多いとI/Oの回数が増える。
  • ストレージの仮想化により、「表とインデックスの物理ディスクをわけるべき」「ログファイルとデータのファイルは別々の物理ディスクにする」「ログファイルはRAID0かRAID1にする」などともいえなくなってきた。
    • ただし、インデックスの物理ディスクが壊れてもインデックスの再作成で済むというメリットは失われる。

メリット

  • インデックスを作るSQL文は単純。
    • CREATE INDEX命令でインデックス作成、DROP INDEX命令で既存のインデックスを削除できる。
    • 作成したインデックスの内容を確認するときは、MySQLであればSHOW命令を使う。
  • インデックスを検索することにより、検索パフォーマンスを向上させることができる。

デメリット

  • インデックスもデータの集合なので、ディスク領域を圧迫する。
  • 索引の更新が発生するため、挿入・更新・削除のパフォーマンスが低下する。
  • 索引のヒット件数が多い場合などは、場合によりインデックスを走査することでかえって非効率になることもある。

インデックスを作成・削除・確認するSQL

CREATE INDEX命令の書式

 CREATE INDEX命令の書式は次の通りである。

CREATE [UNIQUE] INDEX [インデックス名] ON [テーブル名]([列名], …)

 UNIQUEキーワードを指定したときは、インデックスに含まれる(複合)列の内容は重複を許さない。

インデックスの削除

 既存のインデックスはDROP INDEX命令によって削除できる。

DROP INDEX [インデックス名]

インデックスの確認

SQL Serverの場合

列情報を取得する

 システムストアドプロシージャsp_MShelpindexを用いた以下のクエリを実行する。

EXEC sp_MShelpcolumns N'[テーブル名]'

インデックス情報の確認

 システムストアドプロシージャsp_MShelpcolumnsを用いた以下のクエリを実行すると、テーブル構造を表示する。

EXEC sp_MShelpindex N'[テーブル名]'

インデックス作成の目安

 インデックスにもデメリットは存在するので何でもかんでも作成すればよいというわけではない。次の目安を参考にして欲しい。

  • テーブルに含まれるデータ件数が多い。
  • 検索頻度が多い。
  • 該当列が検索キーまたは外部キーである。
  • 該当列により、データ件数がテーブル全体の1割未満にまで絞り込める。

[注意]これらはあくまで目安であり、実際のパフォーマンスは環境やデータ件数、アクセス状況などを見つつ、レスポンス時間を計測しながら、適時チューニングすべきである。 ◇

インデックスの代表的なアルゴリズム

インデックスが使用されない例

 インデックスを設定しても、必ずしも検索時にインデックスが使用されるわけではない。例えば、次のケースの場合はインデックスが使用されない。

  1. IS NULL,IS NOT NULL,<>,LIKE演算子などを使用した場合
    • ただし、LIKE演算子による前方一致検索では使用される。
    • このケースではインデックスのキー列そのものをもう一度検討すべきである。
      • LIKE演算子については可能な限り、完全(前方)一致でニーズを満たさないか、要件から見直すことも重要である。
  2. インデックス列に対する演算や関数を使用している。
    • 演算を右辺で行うことで改善できる。
      • 例:「price * 1.05 < 3000」ならば、「price < 3000 / 1.05」のように計算式を右辺に集めることでインデックスが使用される。
  3. 複合インデックスを設定しており、かつインデックスの先頭列がWHERE句に含まれていない場合
    • このケースではインデックスのキー列そのものをもう一度検討すべきである。

参考文献

  • 『RDBMS解剖学』
  • 『楽々ERDレッスン』
  • 『書き込み式SQLのドリル』
  • 『絵で見てわかるOS/ネットワーク/ストレージ』