沖縄チームのキリです。データベース設計している際にどのカラムのインデックスを作成するか迷うことありますよね。今回はDB設計初心者に向けてインデックス設計の指針を紹介したいと思います。あらかじめ断っておきますが、私自身DBの動作に詳しいとはいえないのであくまで参考程度にしてください。実際の業務ではボトルネックとなっているSQLを検出しテーブルやインデックスの設計を見直してパフォーマンスチューニングを繰り返し行っていくことが必要になるかと思います。
クエリ実行時にインデックスが使われるかどうかはEXPLAINステートメントを使用して調べることができます。
https://dev.mysql.com/doc/refman/8.0/ja/explain.html
インデックスとは何なのか
日本語では索引や見出しという意味になるそうです。イメージしやすいものでいうと国語辞典の側面にあかさたな、と色分けされたものがわかりやすいでしょうか。（日本語でなんと呼ぶのか分からなかったのですが、本の開く側を小口といい、小口側の側面に印刷することを小口印刷と呼ぶそうです）
用途も辞書のインデックス同様、目的のデータを検索する際の目印として使われます。その性質上、インデックスに使用する項目の値の範囲が広く、偏りが少ない方がより効果を発揮します。あるテーブルのユニークなデータの粒度をカーディナリティといい、性別のように値の範囲が狭い場合はカーディナリティが低い、メールアドレスや電話番号のように一意な値が多い場合はカーディナリティが高いと言われます。
なぜカーディナリティが高い方が効果的？
私もはじめはあまりピンと来ていませんでしたが、1万人の名簿から特定の1人、例えば山田太郎さんを見つけようと思った時にどうやって探せば効率的なのかを考えたら理解しやすいかと思います。性別のみでインデックスが作られていた場合、男女比率が同じなら5,000件を総当たりで探す必要があるでしょう。フルネームでインデックスが作られている場合は「や」→「やま」→「やまだ」というように用意に絞り込むことが出来ます。
ここで重要な点があります。名字がわからない場合はどうなるでしょう？ほにゃらら太郎さんを名簿から探そうと思うと一つ一つ全件のデータを調べなくてはなりません。つまり後方一致ではインデックスが無効になってしまうということです。せっかく作成したインデックスを活用したい場合は完全一致あるいは前方一致で検索する必要があります。SQLでいうとWHERE name LIKE '%太郎'ではインデックスが効かずに全件検索になりますが、name = '山田太郎'やname LIKE '山田%' であれば高速に検索することができます。（今どきのコンピュータの性能なら1万件程度であれば遅延は気にならない程度ですが件数が増えれば増えるほどパフォーマンスに影響してきます。）難しいようで人間がたくさんの情報の中から目的のデータを探すのと同じことをコンピュータ上でやっているということですね。
インデックス効いてないよ！
さてカーディナリティの高いカラムにインデックスを作成していざEXPLAINで調べてみたところ全件検索になっている、というケースがあります。DBのオプティマイザは賢いので、データ数が少ない場合は全件検索した方が早いということでインデックスが使われないそうです。
https://dev.mysql.com/doc/refman/8.0/ja/mysql-indexes.html
小さなテーブルまたは、レポートクエリーが行の大半またはすべてを処理する大きなテーブルに対するクエリーでは、インデックスはあまり重要ではありません。 クエリーで行の大半にアクセスする必要がある場合は、順次読み取る方が、インデックスを処理するより高速です。
結論
ざっくりまとめると
・絞り込みに使われるかつ値のばらつきが多い項目（本のタイトル、人名、メールアドレス、公開日時など）
・他テーブルと結合する項目（ユーザー投稿テーブルのユーザーIDなど）
このあたりにインデックスを貼っておくと有効な場合が多いでしょう。