indexを理解しちゃおう

## はじめに
Railsなんかを触ってる時に ```index``` についてなんとなくわかるんだけど、なんとなくわからない状態だったので、今回学習した内容を残しておきます。

## indexとは
indexは**データベースのパフォーマンス向上の手段**としてよく使われているもの
例えるなら本の索引のようなもの

よく使われる理由として以下の三点
>・SQL文を変更しなくても性能改善できる
・テーブルのデータに影響を与えない
・それでいて一定の効果が期待できる

###indexの作り方

indexはSQLで簡単に作ることができる

```CREATE INDEX [インデックス名] ON [テーブル名]({列名})```

USERテーブルのname列に対してfind_nameというindexを作る例

```
mysql->CREATE INDEX find_name ON USER(name)
```

##フルスキャンとレンジスキャン

ユーザーがインデックスを作るだけで「このインデックスを使え」と言わずともDBは
「このSELECT文はフルスキャンよりもインデックス（レンジスキャン）を使ったほうが早い」
と判断して自動的にインデックスを使う

このSQLを実行する計画を立てる機能を **オプティマイザ** という

####フルスキャン
テーブルに含まれているレコードを最初から最後まで全部読み込む方法
書籍に例えると最初のページから最終ページまで1ページずつ調べていくイメージ

####レンジスキャン
テーブルの一部のレコードのみにアクセスする方法
書籍で例えると索引から目的の単語が現れているページだけを拾い読みしていくイメージ


##indexの構造
```show index```でテーブルにあるインデックスを確認することができる

```
mysql> show index from city(tabel name);

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY      |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode  |            1 | CountryCode | A         |         465 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | ind_destrict |            1 | District    | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
```

上記コマンドで表示したインデックス情報における「Index_type」列の```BTREE```がキーワード

これはインデックスが「ツリー」の形をしていることを表している。一般的にはB-treeと表記される

##B-treeとは

多数のデータがあるとき、B-treeはデータをあるルールを設けてその順序に並べる。
このように順序をつけて並べる行為を```ソート```と呼ぶ。

ソートの例

```
{たろう、りょうた、あきと、ふみや}
「あいうえお」順に学べる
{あきと、たろう、ふみや、りょうた}
```

B-treeはデータをソートして順序性を持たせツリー構造に展開することで、**どんな値に対しても同じぐらいの時間**で結果をえることができる

一方インデックスを使わない場合はどちらかの**端から一つずつ調べていく**ことになり、運が良ければ早く結果を得ることもできるし悪ければ最後までデータを確認する必要があり、場合によっては効率が悪い

###どの規模のデータ量であればindexを使うべきか

B-treeインデックスはデータ量が増えれば増えるほど優れた改善効果を発揮することができるが、逆に言えば**ある程度のボリュームが無ければ改善効果は期待できない**ということになる

ではどの程度の規模感であればインデックスを使ったほうがいいのか

「おうちで学べるデータベースのきほん」によれば、```100万行以下のデーブル```は「小〜中規模」と考えても良いとのこと
この規模以下のテーブルだとインデックスを使っても大きな改善効果は期待できない


## インデックスを使うときの注意点

ここまで便利でコスパの高いインデックスだから「使っちゃえ！！」と思いがちだけど、注意点がいくつかある

###１：インデックス更新のオーバーヘッドによって更新処理の性能が劣化する
テーブルに新しいデータが追加されたり既存のデータが更新されたりすると、インデックス自身も自動的に更新される

だから何千万行レベルのINSERTやUPDATEをしてしまうと、その分インデックス更新にも時間がかかってしまう

###2：意図したものと違うインデックスを使われしまう
大規模システムになれば一つのテーブルに10~20個のインデックスが作られることも珍しくない

インデックスの候補が多ければオプティマイザ（SQLの実行計画を立てているところ）も迷ってしまう


意図したものと違うインデッスが使われていることもあり、
「このインデックスの方が早く処理できるでしょ！！」
となる

##インデックスを作る基準
単にストレージだけを消費してしまう、使えないインデックスを作らないための指標が下のような感じです


###１：サイズの大きなテーブルにだけ作る
サイズの小さなテーブルでは**インデックスとフルスキャンの大差がない**ため、インデックスを作る考慮に入れる必要がない

###２：主キー制約や一意制約の付与されている列には不要
主キー制約が付与されている列(PRIMARY)には、自動的にインデックスが作成される
一意制約がついている列でもこれは同様

なぜ暗黙にインデックスが作成されるかというと**値の重複チェックをするためにはデータをソートする必要**があるので、インデックスを作っておくことでソート済みにしておくのが便利だから


###３：カーディナリティの高い列に作る
カーディナリティ（Cardinality）とは、「値の分散度」を示す

ガーディナリティの高い例>マイナンバー
ガーディナリティの低い例>性別

インデックスを作ることによる性能改善効果が期待できるのはガーディナリティが高い方

ガーディナリティが低い列であまりインデックスの効果が期待できないのは、**インデックスのツリーをたどる操作が増えるほどオーバーヘッドが増え**、インデックスを作った恩恵を受けられなくなるから


##参考
[おうちで学べるデータベースのきほん](https://www.amazon.co.jp/dp/B00TTRZFDC/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1)