SQLデータベースインデックスの基本

SQLデータベースを学び始めたばかりのあなたは、インデックスとは何か、そしてなぜそれについてこんなにも気を遣うのか疑問に思うかもしれません。

インデックスに関する話題は複雑になりがちですが、基本を学ぶことで道が拓けます。

インデックスって何？

本の後ろの索引のように、データベースのインデックスは理想のデータを探す場所を知っていることで、クエリのスピードを速める手助けをします。

もし本に索引がなければ、欲しい情報を見つけるまですべてのページを見なければならないでしょう。しかし本の後ろに索引があれば、何ページに何が書いてあるのかを探して、すぐにそのページに行くことができます。これにより、検索の時間が短縮されます。

これがデータベースでのインデックスの役割です。

どんな種類のインデックスがあるの？

インデックスには4種類あります。

プライマリ
プライマリインデックスはユニークなインデックスです。プライマリに設定されたフィールドは、テーブルの各行でユニークな値を持つことを保証します。

テーブルは1つのプライマリインデックスしか持てません。

ユニーク
プライマリインデックスと同様に、ユニークインデックスはテーブルの各行でフィールド値がユニークであることを保証します。

テーブルには多くのユニークインデックスがあり得ます。

インデックス
このタイプのインデックスは一般的なインデックスです。データベースにフィールドのデータをインデックス化するよう指示し、これを使用してクエリのスピードを上げる可能性があります。フィールドの値はユニークである必要はありません。

フルテキスト
他のインデックスがフィールドの値全体をインデックス化するのに対し、フルテキストインデックスはフルテキストに設定されたフィールドの各行のテキスト内にインデックスを作成します。これにより、部分的なテキストに対する検索でインデックスを機能させることができます。特にTEXTやBLOBデータ型として設定されたフィールドで、大量のデータがフィールドに保存されている場合に便利です。

いつインデックスを追加すべきか？

実行に時間がかかりすぎるクエリは、インデックスが必要な可能性が高いです。非効率なクエリはサーバーに負荷をかけ、サーバーの負荷増加、反応時間の長さ、サーバー性能の低下につながります。

少量のデータしか含まないテーブルにインデックスを追加してもクエリのスピードアップにはならないでしょう。おそらくインデックスが無視されることになるでしょう。

インデックスを追加する前に、本当にインデックスが必要かどうかを確認してください。インデックスはSELECTクエリを速くしますが、INSERTやUPDATEを遅くすることもあります。

テーブルのすべてのフィールドにインデックスを追加したくなるかもしれませんが、これは良い習慣ではなく、サーバー性能の低下につながる可能性があります。

クエリがインデックスなしにうまく実行される場合は、インデックスが不要かもしれませんが、もし実行が遅くシステムが重くなる場合は、インデックスを追加するのがおそらく良い考えです。

EXPLAINキーワードをクエリに使用して、クエリの実行方法についての洞察を得ることができ、これによりインデックスに関する情報を知った上で意思決定ができます。

EXPLAINとは？

EXPLAINは、クエリの結果を取得するためにデータベースが何を計画しているかを示します。これをクエリ実行計画と呼びます。

以下のようなクエリがある場合：

SELECT * FROM employees WHERE gender='f';

クエリプランを見るためにEXPLAINキーワードを使うには、クエリの前にEXPLAINを追加します：

EXPLAIN SELECT * FROM employees WHERE gender='f';

すると、次のような結果が表示されます：

[画像は削除されません]

一時テーブルとファイルソート

EXPLAINを使用すると、「Using Temporary」や「Using File Sort」といった項目が表示されることがあります。これらは注意が必要な項目です。クエリプランにこれらが含まれている場合は、クエリが十分に最適化されていない可能性が高いです。

一時テーブル
クエリ実行に一時テーブルが必要なことがあるものの、SQLが一時テーブルの作成が最善の選択だと判断した場合、さらに見直す価値があります。一時テーブルの必要性をなくす方法として、インデックスの追加が考えられますが、盲目的にインデックスを追加するのではなく、一時テーブルが必要なくなるような他のクエリの書き方がないかを最初に確認してください。

ファイルソート
ファイルソートは、SQLが結果を一時バッファでソートしようとしていることを意味し、これがクエリを遅くさせる可能性があります。

インデックスはファイルソートをなくす手助けになりますが、一時テーブルと同様に、インデックスを追加する前にクエリを改善できる方法がないかを探してください。

WHERE句にインデックスを使用する

このチュートリアルでは、https://github.com/datacharmer/test_dbから入手可能なサンプルデータベースを使用しています。

インデックスについて初心者なら、WHERE句の中にあるフィールドを見るのが良いスタートです。WHERE句で参照されているフィールドにインデックスがある場合、クエリはそのインデックスを使ってクエリのスピードアップを試みます。

SELECT * FROM employees WHERE first_name='mary';

このクエリのEXPLAINは以下のようになります：
[画像は削除されません]

EXPLAINは、使用可能なインデックスがないためWHEREに基づいてクエリを実行したことを示しています。また、データを見つけるために299556行を見ていたことを示しています。これはフルテーブルスキャンです。すべての行を見て、first_nameフィールドの値が_Mary_に等しいかどうかを確認しています。

フルテーブルスキャンは、特に大きなデータセットでインデックスが必要かもしれない良い兆候です。

CREATE INDEX first_name_idx ON employees (first_name);

[画像は削除されません]

EXPLAINを見ると、今度はインデックスを使用しており、全299556行ではなく224行だけを見ていることがわかります。

これにより、クエリがかなり速くなります。

これがインデックスの利点です。

マルチフィールドインデックス

インデックスは複数のフィールドを組み合わせて作成することができます。この例では、ファーストネームとラストネームの両方でクエリを実行したいかもしれません。

SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

インデックスがないとテーブルスキャンが行われ、EXPLAINは前のテーブルスキャンに似た結果になります。
[画像は削除されません]

ファーストネームとラストネームの両方のフィールドにそれぞれインデックスを追加することができます。

ALTER TABLE employees
ADD INDEX first_name (first_name),
ADD INDEX last_name (last_name);

これは実行しているクエリにとってまずまずのインデックスになりますが、最高のインデックスとは言えません。

EXPLAINは興味深い結果を提供します。

[画像は削除されません]

データベースは2つのインデックスを結合してから、両方のフィールドにマッチする行を探しました。

代わりに、2つのフィールドを組み合わせたインデックスを作成すると、データベースはインデックスを結合する必要がなく、より負担の少ないクエリになります。

ALTER TABLE employees
ADD INDEX full_name (first_name,last_name);

[画像は削除されません]

これははるかによい形のクエリ実行計画で、別々のインデックスを使用した場合よりも速く実行されます。インデックスを組み合わせるためのオーバーヘッドには時間がかかるので、データベースがより速くクエリを実行するのを助けることができるのは良いことです。

特記事項: マルチカラムインデックスにフィールドを追加する順序は場合によって重要です

SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

は以下のように書き換えられるかもしれません：

SELECT * FROM employees 
WHERE last_name='Cooley' 
AND first_name='Mary'

どちらもインデックスを使用します。ただし、WHERE句に両方のフィールドが存在する限りです。

では、ファーストネームだけでクエリを実行したい場合はどうなるでしょうか？

SELECT * FROM employees 
WHERE first_name='Mary'

[画像は削除されません]

インデックスの1つのフィールドしかクエリで使用されていないにも関わらず、まだインデックスを使用しています。

では、ラストネームでクエリを実行したい場合はどうでしょうか？

SELECT * FROM employees 
WHERE last_name='Cooley'

[画像は削除されません]

インデックスを使用していないことがわかります。

では、何が起きたのでしょうか？

マルチフィールドインデックスは左から右へとカスケードします。インデックスを使用するためには、クエリにインデックスの最初のフィールドが存在する必要があります。それに続いてインデックスの2番目のフィールドがクエリにあるかどうかを探します。

マルチフィールドインデックスの利点を得つつ、ラストネームだけでクエリを実行するためには、ラストネーム用の追加のインデックスを追加することができます。

ALTER TABLE employees
ADD INDEX last_name (last_name);

注：マルチフィールドインデックスはファーストネームだけで動作するため、ファーストネームのためだけにインデックスを追加する必要はありません。

もとのクエリを再実行してみます。

SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

[画像は削除されません]

Possible KeysとKeyを見ると、両方のインデックスが検討されたけど、マルチフィールドインデックスが選ばれたことがわかります。

ラストネームだけでクエリを再実行すると、

SELECT * FROM employees 
WHERE last_name='Cooley'

[画像は削除されません]

今回は、ラストネーム専用に作成したインデックスが使われます。

ORDER BYでのインデックス化

WHERE句だけでなく、クエリ内のデータの順番付けでインデックスが使用されます。

SELECT * FROM employees 
ORDER BY last_name LIMIT 100

[画像は削除されません]

WHERE句がなくても、データをより速く並べ替えるためにインデックスが使われます。

GROUP BYでのインデックス化

GROUP BYクエリでもインデックスが使われます。

SELECT COUNT(*),last_name 
FROM employees 
GROUP BY last_name 

インデックスなしでこのクエリを実行すると、Explainは次の結果を与えます。
[画像は削除されません]

これはおそらく最悪のクエリです。ファイルソートを使用し、内部的に一時テーブルを作成してクエリを実行しています。これにより、クエリがかなり遅くなります。

ラストネームにインデックスを追加してクエリを再実行すると、
[画像は削除されません]

今はインデックスを使用していて、はるかに速いクエリになります。

LIKEでのインデックス化

場合によっては、LIKEキーワードにインデックスは使われません。

以下のようなクエリの場合、インデックスを使うこ

こちらの記事はdev.toの良い記事を日本人向けに翻訳しています。
https://dev.to/mrpercival/sql-database-index-basics-120c