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）とは、「値の分散度」を示す

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

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

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

参考

おうちで学べるデータベースのきほん