インデックス設定でSQLを爆速にしよう

始めに

リリースから2年ほど経つシステムのパフォーマンスが悪いという声が入った。
正直、弊社は完全に人員不足で自分が複数の案件を見てるのでそこまで見る余力は無かった
色々調査したらSQLが超絶遅くなってるのが原因でした。

なので今回は自分がSQLを爆速にした時に調べた内容や実際にやったことを共有してみる

何か間違えがあればガンガンご指摘頂けると助かります！

インデックスとは

タイトルにあると通りインデックスとは何でしょう？
簡単にいうと本の索引です。
私たちが本を読む際に裏面にある目次などを参考にしながら読みたい部分を探すと思う。

こんな感じの

オブジェクト思考 ......... 10~20
カプセル化  ............ 21
抽象化 ................ 30
継承 .................. 40
ポリモーフィズム

最初から1ページずつ探してたら日が暮れる
SQLも同じで特定のデータを探す際に1から順に探して行ったら効率が悪い
これが数十、数百件ぐらいであれば問題ないけど、数千、数万のデータがあったら話が変わってくる。
私が遭遇したのもまさにこれで、2年が経ち当初想定したデータを上回るデータ量になってた

実はインデックスには複数の種類があり、

• B-treeインデックス
• ビットマップインデックス
• ハッシュインデックス

などのインデックスがある。
今回は実際の実務でSQLを爆速にしたB-treeインデックスについてフォーカスしてみる(B-treeが最も有名なインデックス)

B-treeインデックス

B-treeインデックスは名前の通り木構造でできていて、リーフノードに実データへのポインタがある。
なので実データが直接木構造で並べられている訳ではない。
この木構造を二分探索のようにしていけば検索データに辿り着くことができそう
さらにB-treeの計算量はO(log n)と非常に高速です。

これについては後ほどどれくらい早いのか説明します

二分探索って何？

木構造の頂点から始まり、現在のノードの値と検索対象を比べ、検索対象の方が大きければ右へ進み
小さければ左へ進むというアルゴリズム。これを繰り返すことでお目当ての値に辿り着ける訳だ
例えば30という値を検索する場合、

1. 30は15より大きいので25(右)へ
2. 30は25より大きいので30(右)へ

2回の計算で辿り着くことができた
もちろん10や25であれば1回で辿り着ける

        15
       /  \
     10    25
    /  \   / \
   8   12 20  30

B-treeを使わないとどうなる？

では上記の木構造を横並びにしてみるとこんな感じ。
30を探すには15->10->25->8,->12,->20->30とたどる必要があり、6回の計算が必要になる。
これを線形探索と言い計算量はO(n)となり、Nが増えれば増えるほど計算量は増えていく

15, 10, 25, 8, 12, 20, 30

O(n)とO(log n)の比較

線形探索はnの大きさに比例して計算量が増えているのに対して、B-treeの計算量の増加数はごくわずかです。
これだけでもB-treeの凄さがわかる

どこにインデックスを貼るべきか？

インデックスを貼ると処理が高速になることは分かったがどのテーブルのカラムに設定すれば良いのか？それは
カーディナリティの高い要素です
一般的には、カーディナリティが高いフィールドにインデックスを設定することで、データベースのクエリのパフォーマンスが向上するといわれている。

例えば
性別が持つ値は男性、女性、その他でカーディナリティは3ということになる。
これはカーディナリティが低いのでインデックスの設定をしても効果が薄いと言える。

逆に顧客の電話番号や口座番号などはかなりの数があることが分かります。
そのようなカラムにインデックスを設定することで効果が爆上がりする

インデックスを貼ってみる

実際にインデックスを貼るカラムが見つかったら設定する訳だけどとても簡単。
例として、employeesというテーブルはemployee_idというカーディナリティが高いフィールドが存在すると仮定
employee_idにインデックスを貼るためのSQL文は以下の通り

CREATE INDEX idx_employee_id ON employees (employee_id);

また、複数のフィールドを組み合わせてインデックスを作成することもできる。
例えば、last_nameとfirst_nameのフィールドの組み合わせでインデックスを作成したい場合は以下のようなSQL文を発行する

CREATE INDEX idx_name ON employees (last_name, first_name);

上記は複合インデックスといって複数のカラムを紐付けたインデックスも設定することができる

これだけでSQLのパフォーマンスは驚くほど向上する。

気を付けること

インデックスはやたらに設定しすぎるとかえってパフォーマンス低下リスクがあるので効果的なカラムにインデックスを貼ることを心がける必要がある。
また、B-treeインデックスは追加・削除などの更新作業のパフォーマンスが落ちる。
データが更新されれば、インデックスの値も更新する必要があるからです。

感想

実務でもなんとなくSQLを書いて、なんとなく運用していたがそれでは顧客に価値は提供できない。
DBMSはどんな構造なのか？どうやって検索してるのか？計算量は多いのか？少ないのか？
多くのことを熟考し、最適な状態を保ってこそ長く愛されるプロダクトになるんだと思った。
もっと知識を深めて実務で価値提供していきたい