はじめに
クエリを高速化しようとしてINDEXをいじってる際に起きた話＋その時知ったtipsを書いてみます。
穴ぼこだらけかもしれませんが、この辺を完璧なまでに深めるにはまだまだ経験と知識が足りてないので、この記事自体は口語的に割り切っています。
環境
RDBMS：MySQL 5.6
DBエンジン：innoDB
クエリのオプティマイズについて
先置でまず、クエリ実行時の処理の話。
MySQLのクエリ実行時にはオプティマイズといって、クエリとテーブルの状態に応じて「実行計画」が立ち上げられ、それに準じてデータのSELECT等が行われます。EXPLAIN 〜 で見れるアレです。
ミソなのは、テーブルの状態とクエリのみならず、レコードの状態に応じてオプティマイズ結果は変わるということです。
同じCREATE文で生成したテーブルで同じSELECT文を実行しても、どのようなレコード内容・数で運用されるのかによって実行計画は変わる可能性がある。
このため、実行計画の確認とクエリの設計は、実際に運用している環境、あるいは可能な限りその環境に近いものを用いて行うべきです。
規模が小さいならいざ知らず、日夜膨らむことが予見される巨大テーブル等にはどんな運用がされるかを予見して設計するのがマストです。（INDEXなら後付も出来ますが。
カーディナリティ
聞き慣れない単語（私はそうでした）ですが、そのカラムにおけるレコード内容の種類の絶対値という、カラム固有のパラメータです。インデックスを張る上で指標になるものです。
SHOW INDEX FROM table_name;
上記でINDEX情報を確認した時、Cardinalityカラムがその値です。
例えば、PRIMARYなidカラムとかっていうのはUNIQUEカラムのため、カーディナリティ＝レコード数となります。これはそのテーブルのカーディナリティとして最大値となります。このため、PRIMARYなキーにはデフォルトでINDEXが張られます。
対して、フラグ用途カラムなんかは極めて小さくなります（なぜ「カーディナリティは2です」と言わないのかは後述）
INDEXが有効に働くのは(基本的には)カーディナリティが大きなカラムです。
このため、PRIMARYなKEYでのSELECTは安定して最も速い、ということになります（たぶん）。
でも…
PRIMARY KEYが使えずに検索する状況なんてザラです。ではカーディナリティの高いカラムにINDEXを貼っておけば万事解決…するでしょうか？
この考え方は概ね正しいですが、カーディナリティの大小がINDEXの有効性を保証しない場合があります。
実際にこんなテーブルがありました。（実際のテーブルから余計なカラムを除去した感じのボヤいEXPLAINです）
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| something_table | 0 | PRIMARY | 1 | id | A | 6543210 | NULL | NULL | BTREE | |||
| something_table | 1 | hoge_flg | 1 | hoge_flg | A | 4 | NULL | NULL | BTREE | |||
| something_table | 1 | huga_param | 1 | huga_param | A | 50 | NULL | NULL | YES | BTREE | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
フラグにINDEXって！！とは思う方も居るかと思います。しかしこのテーブルの運用、hoge_flgは常に=1でしか検索せず、またその時の対象件数が少ないという運用をしています。このため、カーディナリティは明らかにhuga_paramの方が高いのに、検出される件数はhuga_paramよりもhoge_flgでSELECTした方が少なくなるという状態がほぼ常なのでした。
ちなみにこの時、実際に実行したいクエリの実行計画ではhuga_paramにINDEXを張るようオプティマイズされていました。恐らくはこのテーブルのカーディナリティの影響。
このとき、レコード数は600万件ほどで、何も考慮しないで実行すると完了まで35秒程度でした。（実際はその他の条件も連なっていますが）
レコード数のせいにしても遅い。
試しにクエリにhoge_flgのインデックスヒントを与えます。hoge_flgのカーディナリティは4です。
ここで改めて、「フラグ値のカーディナリティがなんで2じゃない？？」という話。
どうやら、MySQLにおいてはカーディナリティは計測されず、精密さを犠牲にした高速処理のために推定しているらしい。カーディナリティのパラメータは値を用いて何かを行うのではなく、あくまで「指標」としての役割にとどまるので、MySQLのテーブルアナライズは正確な値が出ていない…というのがカラクリのようでした。
閑話休題。
カーディナリティ極低のフラグカラムhoge_flgにインデックスヒントを与えて実行した所、ms単位にまで落ちました。はやい
上記のように、レコードの状態や、カラムの用途に応じて、時にはインデックスヒントを活用することで、問題が解決することもあるかも。
メインの話は終わったので余談
ANALYZE TABLE
「テーブル分析」。この表題自体がMySQLクエリの一部です。
ANALYZE TABLE tabl_name;
何が起きるか？オプティマイズに必要なテーブルの情報を再分析します。これにより、各カラムのカーディナリティを再推定します。innoDBだと、オプションの設定に応じて「テーブルの10%に変更が加えられたらアナライズする」という設定になっているようです。
大きめのテーブルで実行してみると判りますが、推定なので実行のたびに結果が変わります。（小さいパラメータは殆ど動きませんが理由は…？
idカラムの意義
テーブルによくついてくるPRIMARYなidカラムですが、「無いよりあったほうが良いよね。後から付けるの面倒だし、何かに使えるっしょ」みたいな考えでとりあえず付与しちゃうという考え方は少し問題かもしれません。
何の考慮もなしにPRIMARY idカラムがあると、idカラムがレコードの制約を保証してしまい、本来そのPRIARY性を保証しなくてはいけなかったカラムに制約が与えられない恐れがあります。
必要な制約が何か、UNIQUEでなくてはならないカラムはどれか、それをよく考えた上でidは与えましょう。本来必要な制約を正しく与えることが出来ていれば何も問題ない話です。
終わりに
より理解がフワフワになってしまった場合はより論理的・技術的な先人の資料で詰めて腹落ちさせてください（