EXPLAIN の読み方

1 件返すために、200 万行を読んでいた

WHERE email = 'alice@example.com' で 1 件だけ取り出すクエリが 3 秒かかっているとします。返ってくるのは 1 行です。それでも 3 秒かかるのは、その 1 行を見つけるまでにテーブルの全行を読んでいるからです。

これは EXPLAIN を付ければ、直す前に分かります。MySQL の出力は列が多いのですが、最初に見るのは 1 つだけです。

type が ALL で、key が NULL。これは「使える索引が見つからなかったので、全行読んで条件に合うものを拾う」という意味です。rows の 2013422 は、そのために読むと見込んでいる行数です。PostgreSQL では同じ状態が Seq Scan on users と表示されます。

一覧、検索、ログインのような画面でこれが出ていたら、まずここを直します。逆に ref や range、Index Scan が出ているなら、遅さの原因は別のところにあります。

索引が「無い」のか「使えない」のか

ALL になる理由は 2 つに分かれます。索引をそもそも作っていないか、作ってあるのに条件の書き方で使えなくなっているかです。

email に索引があっても、これは使われません。索引に並んでいるのは元の値であって、LOWER() を通した後の値ではないからです。プランナは全行に関数を適用してみるしかなくなります。possible_keys が空なら候補すら無かったということなので、この線を疑います。

見込みが外れていないかは、実測と並べれば分かる

EXPLAIN が出すのは見込みです。本当に速くなったかは、実際に走らせて測ります。

rows=8 が見込み、actual の側の rows=7 が実測です。loops=10 は、この部分が 10 回繰り返されたという意味で、1 回 1.3 ミリ秒でも 10 回なら 13 ミリ秒かかっています。時間を食っている場所は、1 回の速さではなくこの掛け算で決まります。

なお PostgreSQL の EXPLAIN ANALYZE は本当にクエリを実行します。UPDATE や DELETE に付けると、見るだけのつもりが更新されます。試すならトランザクションで囲んで、最後に巻き戻してください。