初めに
こんにちは。BFT名古屋支店新人エンジニアのないとうです。
先日SQLでの性能向上を行った際INDEXを用いました。
このINDEXを使うにあたって理解を深めるために調査をしたので今回はそれを紹介したいと思います。
INDEXの仕組み
INDEXの概要
INDEXはテーブルに保存されているデータを一部抜き出して検索がしやすいように最適化したものです。
データは列ごとに抜き出しており、一列または複数列を抜き出すことができます。
INDEXでデータ検索を行う仕組み
SELECT文では上から１行ずつ検索するため、データが多くなった場合に処理時間が長くなります。
しかしINDEXではB-tree構造で作成されているため、データが多くなった場合の処理時間を短縮することができます。
B-tree構造とは
B-tree構造はルートノードとブランチノード、リーフノードで作成されており、
リーフノード以外のノードは自分持つブランチノードに移動させるポインターをもっています。
図2のようにルートからブランチ、リーフへと検索をするごとに範囲を絞っていくことで探索を行います。
そしてリーフノードはINDEXで抽出したデータへのポインターを持っており、探索した行数のデータをテーブルから直接取得することができます。
B-tree構造はどの場所を探索した場合でも、処理時間が変化しにくい特徴があります。
また単純に上から順に探索する場合より処理速度が向上する傾向にあります。
INDEXの欠点
INDEXはテーブルから情報をとりだして新しいテーブルを作成しているためINDEX用の領域が必要があります。
また抽出元のテーブルの更新があった場合INDEXの更新も行う必要があるためUPDATE文やINSERT文でデータを追加するのにINDEXがないテーブルと比べると時間がかかってしまいます。
INDEXの使い時
これまでのことからINDEXの特徴として
・テーブルから一部の列を取り出しB-tree構造 で新しいテーブルを作成する
・B-tree構造は検索する行数によって処理時間があまり変動しない
・テーブルのデータ更新処理に時間がかかる
以上のものがあることがわかりました。
これらのことから次のようなテーブルはINDEXの効果が高くなります。
・データ数の多いテーブル
・WHERE句が多く利用されているカラム
逆に次のようなテーブルはINDEXの効果があまり高くありません。
・データ数の少ないテーブル
・頻繁にデータが更新されるテーブル
終わりに
今回はINDEXについて調べたことを記事にしました。
INDEXを適切に使えるようにこれからも学習していければと思います。
それでは。
