😀
SQL インデックスの使い方
インデックスとは
- データの検索を高速化する機能
- 書籍でいうと、最後の方にある索引のようなものです。
どのページに調べたい内容があるか探すとき、1ページ目から順に探していくのではなく、索引のキーワードから対象ページを絞り込んで探していきますよね。 - ただし、メリットもあればデメリットもあります。テーブルとは別の領域にデータを保持することになるので、冗長な構造になります。
インデックス作成例
このような4つのカラムがあるテーブルがあるとします。
+----+-----------------------------+---------------------+---------------------+
| id | name | sex_type | birth_date |
+----+-----------------------------+---------------------+---------------------+
| 1 | tanaka | 1 | 2020-07-22 |
| 2 | yamada | 2 | 1995-04-26 |
| 3 | katou | 2 | 1984-12-13 |
| 4 | sasaki | 1 | 2000-03-02 |
| 5 | gotou | 3 | 1990-09-28 |
+----+-----------------------------+---------------------+---------------------+
nameカラムの値を検索しようとしたとき、テーブルに格納されているデータは順番に並んでいるわけではありません。なので、目的のデータがあるかどうか順に検索していくことになります。データがこの程度の量であれば問題はありませんが、数百万ものデータが格納されている場合に頭から順に調べていくのは非常に効率が悪いです。
このような場合にインデックスを作成すると検索を高速化することができます。
インデックスを作成すると、対象のカラムのデータを取り出し、高速に検索できるように手を加えて保存します。
例えば nameカラムの値を対象としたインデックスを作成するとこのようになります。
+----+-------------------------------------------------------------------------+
| id | name |
+----+-------------------------------------------------------------------------+
| 5 | gotou |
| 3 | katou |
| 4 | sasaki |
| 1 | tanaka |
| 2 | yamada |
+----+-------------------------------------------------------------------------+
このインデックスは idカラムと nameカラムの値だけをテーブルと別に保持し、nameカラムの値を昇順(アルファベットの昇順)に並び替えてデータを保存しています。
nameカラムの値を検索する時に、元のテーブルを検索するよりもデータ量が少なくまた並び替えも行われているので高速に検索することができます。
インデックスのメリット、デメリット
メリット
- 検索、ソート、結合で多用されるカラムに対して、インデックスを作成するとパフォーマンスが向上する。
デメリット
- データを追加する処理が重くなる
- テーブルととは別の領域にデータを保持するため、テーブルにデータを追加するとインデックスの方にもデータが追加されます。また、ソートなどを行なっている場合は、データを追加するごとにソートも再度実行されます。なので、結果としてデータを追加する処理が重くなってしまいます。
どんなときに使うのか？
インデックスの効果が高いカラム
- 検索
- WHERE句で多用するカラム
- ソート
- ORDER BY句で多用するカラム
- 結合
- JOINの結合条件で多用するカラム
インデックスを作成するべきではないパターン
下記のようなパターンでインデックスを作成すると、インデックスのデメリットの影響でパフォーマンスが低下してしまいます。
- 格納されているデータが少ないテーブル
- 格納されるデータの種類が少ないカラム
- 検索、ソート、結合があまり行われないカラム
Discussion