なんでこの記事を書いたのよ？
DB設計を勉強しているとインデックス設計の問題にぶち当たるわね。貼った方が効率が良いとか、データが少ないなら付けても意味ないとか、いろいろ書かれててけっこう混乱すると思うの。
そこで、この記事ではそもそもインデックスちゃんが何者なのか、どういうカラムに貼ったらいいかをざっくりと解説しようと思うの。あたしも勉強途中だから、何か間違ってる部分があったら遠慮なくマサカリを投げてほしいわ、真正面から素手で受け取らせていただくわ。
そもそもインデックスってなんなの？
インデックス、響きはかっこいいけど「とある」シリーズでしか馴染みがない読者ちゃんも多いんじゃないかしら。日本語にすると「索引」、こっちの方が分かりやすいわね。
インデックスっていうのは簡単に言うと、データに索引を付けてDBMSちゃんがデータを取ってくる時に分かりやすくするわよ！って仕組みよ。
あなたがSQL文を書く時を思い出してほしいんだけど、「なんのデータ」を取ってくるかは指示するけど、それを「どういう風に」取ってくるかまでは指示してないわよね。
-- usersテーブルからnameが「かねぞう」のレコードを取ってきなさい、あんたたち！！！
SELECT * FROM users WHERE name = 'かねぞう';
このSQLを受け取ったらDBMSちゃんはどういう風に探せば効率よくデータが取れるかを自分で考えるの、かしこい！
インデックスを貼るのはこの作業をお手伝いしてあげるようなものね。
本に索引を付けてあげれば、特定の言葉を探すにはまず索引から見ればいい。同じ要領で、DBMSちゃんも効率よくデータを探せるようになるわ。
インデックス = 索引、 DBMSちゃんのデータ探索を手助けする
どういうカラムに付けたらいいの？
上の説明でインデックスちゃんがだいたい何者かは理解してもらえたかしら。
ここではさっそく、どういうカラムに付けたらいいかをまとめるわ。
と、その前に
前提：テーブルのデータ量が多い場合
テーブルのデータ量が少なかったらインデックスちゃんの出番はなしよ。
難しいアルゴリズムの話を回避するためにさっきの索引の例えを出すと、
本のページ数が1ページ、文字数も少なくて一文字一文字がデカデカと書かれてるような時はわざわざ索引を見る時間は無駄よね、目視で確認すればいいんだもの。
まあ大げさだけどとにかく、
「データが少ない時はむしろインデックスを貼る方が検索速度が遅くなる」
ことがあるの。
じゃあ「少ない」って具体的にいくつよ！ってぷんぷんしてるかもしれないけど、目安は10,000件くらいを見ればいいらしいわ。まあざっくりよ、ざっくり。環境によって異なるから厳密さが求められる場合は実測よ、実測！
指標：カーディナリティ
インデックスを貼る際に見るべき指標はカーディナリティよ。
これは「特定のカラムがとる値の種類の多さ」を表す指標なの。例えばBOOLEAN型のカラムの場合は0,1だからカーディナリティは低いと言えるわ。
反対に、DATETIME型のカラムでレコードの作成日時を表すcreated_atカラムなんかを想定すると、カーディナリティはめちゃくちゃ高くなりそうね。値の種類の多さ＝カーディナリティの高さよ。
このカーディナリティが高い列に貼ることでインデックスちゃんはその真価を発揮するの。
具体的には「特定の値が占める割合が全体のレコードに対して5%以下」が目安よ。
BOOLEAN型で言うならもちろん50%、論外よ。
※注意！
カーディナリティが高くても特定の値にデータが集中しているとあまり効果は出ないわ。値が分散しているか、も一つの基準よ。
インデックスはカーディナリティが高い = 値の種類が多い列に貼ると良い
検索や結合の条件となるカラムに貼る
さっきはDATETIME型のcreated_atがカーディナリティが高そうって言ったけど、このカラムを検索条件にしたり結合条件にしないんだったらいくら貼っても意味はないの。
インデックスは検索、結合の条件になるカラムに貼らないと効果を発揮しないわ。
例えば、アプリのDBを想定すると、アプリ側で提供している検索機能で使うカラムを考えるといいかもしれないわね。さっきみたいに名前でユーザーを検索できるならnameカラムに貼るとか。
ちなみに、「検索や結合の条件となるカラム」って言われると、主キー、外部キーが真っ先に思い付くけど、DBMSちゃんは賢いから主キー、外部キーを作ると基本的に自動でインデックスも貼ってくれるわ。
一意制約を付けているカラムにも自動で貼るの、だからこれらの場合は自分で貼る必要はないわよ。
インデックスのデメリット
便利なインデックスちゃんだけど、デメリットもあるの。最初にインデックスは索引って言ったけど、本に索引を作るとなると本自体の内容を作るのと別で作業が必要よね。
それと同じで、挿入処理や更新処理のたびに索引の情報も追加、変更しなきゃいけないから、書き込み処理をする際には、インデックスの数に応じて動作が遅くなっちゃうの。
欠点のない人間はいない、そうでしょ？ でもそこがいいのよ。
まとめ
- インデックス = 索引、 DBMSちゃんのデータ探索を手助けする
- インデックスはカーディナリティが高い = 値の種類が多い列に貼ると良い
- 検索、結合の条件になるカラムに貼る
- 主キー、外部キー、一意制約を付けているカラムには自動で貼られる
- 貼りすぎ厳禁！本当に必要なカラムにだけ貼ろう！