【37】【Rails】インデックス、Rails学習者が陥るインデックスのジレンマ
この記事を読むとデータベースにおけるインデックスについて理解を深めることができます。
Railsでマイグレーションファイルを検証する際、インデックスをつけるかどうかいつも迷います。
再度、インデックスについて勉強し直しました。
狙い撃ちできる
インデックスを設定すると、SQL文はテーブルの特定のレコードを狙い撃ちできます。
知らなかったDBMSの仕事。
DBMSとはデーターベースを管理するためのシステムです。
DBMSはユーザーからSQL文を受け取ると、どのような経路でデータを探しに行くのが最も効率的か自分で判断します。
SQLはこんなデータが欲しいという条件が書かれてますが、この経路で取得して欲しいという条件は含まれてません。
インデックスを設定するとは、データを探しにいく効率的な経路の判断基準が増えることです。
以前はルールベースといって経路を指定する手法もありましたが、
現在は経路選択をDBMSに一任するアーキテクチャーが主流になってます。
これをコストベースといいます。
インデックスの特徴
インデックスの特徴を簡単に説明します。
コードやデータに影響を与えない透過性があり、性能改善が劇的に高いことです。
性能改善についての検証はこの記事で。
B-treeインデックス
インデックスの種類はいろいろありますが、非常にポピュラーなのかB-treeインデックスです
ほぼこのインデックスを使うと思うので、B-treeインデックの特徴や長所は割愛します。
ポイントはB-treeインデックスはどの列に作ればいいのかという点です。
B-treeインデックスをつくる3つの指針
大規模なテーブルに対して作成する。
カーディナリティの高い列に作成する
SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
大規模なテーブルに対して作成する。
データ量が少ない場合、B-treeインデックスを使わずにフルスキャンに任せた方が高速な領域があります。
この著書によると、レコードの数が1万以下の場合はほぼ効果がないそうです。
Rails学習者が陥るインデックスのジレンマ。
Railsを学習する際、当たり前のようにインデックスをつけますよね。
学習用のアプリはレコード数が1万以下なんで、実はインデックス不要なんです。
しかし、インデックスをつけないと指摘を受けると思います。
ポートフォリオもインデックスが全くないと指摘されるのではないかと思います。
この現象をRails学習者が陥るインデックスのジレンマ、といいます。
私が勝手に命名しました（笑）
カーディナリティの高い列に作成する
カーディナリティ
カーティナリティとはなんでしょうか？
具体例を挙げると理解できると思います。
性別のカーディナリティ
例えば、性別のカラムはカーティナリティが3になります。
男性、女性、その他・回答しない
この3つです。
名字のカーディナリティ
では、名字のカーディナリティはいくつでしょうか？
姓・苗字研究の第一人者である丹羽基二博士の『日本苗字大辞典』によると、
日本には姓が30万種もあるそうです。
つまり、名字のカーディナリティは30万です。（外国人の方も含めるともっと多くなります）
インデックスをつける時はカーティナリティが高い列につけるのが基本となります。
目安は全体のレコードの5％に絞り込めるだけのカーディナリティであることです。
カーディナリティ365はインデックスつけるべき？
例えば、受付日のカラムは年間の日数365のカーディナリティとします。
365日から1日選ぶことは、全体の約0.3％です。
5％以下になるので、インデックスを作る意味があるといえます。
性別のカラムは、絞り込んでも全体の33％にしかならないので、インデックスは不要です。
注意点
カーディナリティが高くても値が分散していなければインデックスは不要です。
分布に偏りのあるカラム
例えば1-100の範囲はカーディナリティは100です。
100を選択する割合が99％で、1-99を選択する割合が1％だったらどうでしょうか。
この場合はインデックスは不要です。
インデックスの検索性能が安定しないからです。
100を選択すると非常に広範囲に探索しなければならないのに対して、
1-99を選択するピンポイントにヒットし、経路の判断基準が不安定になります。
SQL文でWHERE句の選択条件、または結合条件に使用されていない列に作成する。
当たり前の話ですが、検索条件に含まないカラムにインデックスを作成しても無意味です。
この他に、indexが利用できない主なsqlを列挙していきます
インデックス列に演算を行いっている
SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100
この式の場合は下記のようにすればいい
SELECT *
FROM SomeTable
WHERE col_1 > 100/1.1
索引列に対してSQL関数を提供している
SELECT *
FROM SomeTable
WHERE SUBSTRING(col_1,1,1) = 'a';
IS NULL術後を使っている
SELECT *
FROM SomeTable
WHERE col_1 IS NULL;
否定形を用いている
SELECT *
FROM SomeTable
WHERE col_1 <> 100;
ORを用いている
SELECT *
FROM SomeTable
WHERE col_1 = 99 OR col_2 = 81;
OR はIN句を使えばいい
SELECT *
FROM SomeTable
WHERE col_1 IN(81,99);
後方一致、中間一致のLIKE述語を用いている。
SELECT *
FROM SomeTable
WHERE col_1 LIKE '%a';
SELECT *
FROM SomeTable
WHERE col_1 LIKE '%a%';
前方一致ならいい
SELECT *
FROM SomeTable
WHERE col_1 LIKE 'a%';
暗黙の型変換を行っている
SELECT *
FROM SomeTable
WHERE col_1 = 10;
下記のように書くこと
SELECT *
FROM SomeTable
WHERE col_1 = '10';インデックスの注意事項
主キー、一意制約の列には作成不要
インデックスは更新性能を劣らせる
定期的なメンテナンスを行うことが望ましい
主キー、一意制約の列には作成不要
主キー制約や、ユニークキー制約の際にインデックスが作られるので不要です。
注意！Railsでユニークキー制約をする際には、マイグレーションの際、インデックスをつけて、unique:trueとすることを忘れないようにしましょう。
インデックスは更新性能を劣らせる
インデックスはテーブルとは別のオブジェクトとしてDBMS内部に保存されています。
インデックスが対象としている列の値が変更すれば、インデックス内に保存している値も変更されます。
インデックスの数が増えるほど、更新性能が劣化していきます。
定期的なメンテナンスを行うことが望ましい
インデックスはテーブルのデータが増えていくと、緩やかですが、構造が崩れて性能が劣化していきます。
定期的なメンテナンス、具体的にはインデックスの再構築を行うことが望ましいとされています。
mysqlにおけるインデックスの再構築（バージョンが古いので注意して下さい）
2.11.3 テーブルまたはインデックスの再構築が必要かどうかのチェック
https://dev.mysql.com/doc/refman/5.6/ja/checking-table-incompatibilities.html
2.11.4 テーブルまたはインデックスの再作成または修復
https://dev.mysql.com/doc/refman/5.6/ja/rebuilding-tables.html
データーベースに関する記事の一覧
参考文献
達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ
最後に
私がブログを書く目的は、素晴らしい本や、素晴らしい方々の技術記事を知って頂きたいからです。ぜひ、上記の参考文献を見て下さい。（noteなので広告とかは一切ありません。）
現在、株式会社grabssに行くために最後の悪あがきをしています！！
現在の進行状況
この記事は37件目の投稿。目標まで後13件。
よろしければ、スキボタン及びサポートお願いします。勉強の励みになります。
この記事が気に入ったらサポートをしてみませんか？