ほんの数行しかないテーブルにindexを張っても倍以上速度が違うのはなぜですか。

Question

mysqlでselect * from Customer where Telephone = ANY(select telephone from Teletype where type ='fix');のようなクエリを流したとき、Teletypeのレコードはほんの１２行しかないのに、インデックスを貼ると結果は倍以上速いです。

Customerレコードは１００万件程度ありますが、インデックスがないときは7秒以上かかり、Teletypeにインデックスを貼ったときは2.5秒程度です。

また、Customerテーブルのtelephoneフィールドに貼ったインデックスは上のクエリだとExplainの結果、使われていませんでした。これはなぜでしょうか。

インデックスを解説しているサイトだと、Last_nameフィールドによくインデックスをはるように記述されていますが、インデックスを貼るとよいフィールドはあらかた決まっているものなのでしょうか。
それとも、どれでもよいのでしょうか。

Answer 1

インデックスというのはその名の通り「索引」にあたるんですが、
ある列に対してインデックスを張ると、
レコードをINSERTする度に
どこに何が入っているか、という索引情報が別に作られます。
これはUPDATE、DELETEの度に更新されます。

普通にSELECT文を発行する場合にはどこに何が入っているか分からないので、
テーブルの全ての行を１行づつ検査していきます。
ストレージにもよりますが、やはり、データを読み込むのは遅いので、
該当しない不必要な行の読み込みは速度低下につながります。

一方、インデックスを張ってある場合は索引が使われ、
索引というからにはソートが行われていて、
検索文字がどの行に収録されているか、全ての行を検査しなくてもピンポイントでわかるので
無駄な読み込みが発生しないため速い
ということです。
http://www.hi-ho.ne.jp/tsumiki/doc_1.html
ここのB-TREEのような検索方法になるようです。

ただし、SELECT文は速くなるのですが、

レコードをINSERTする度に
どこに何が入っているか、という索引情報が別に作られます。
これはUPDATE、DELETEの度に更新されます。

のように、索引が別途作られて更新されるため、
INSERT、UPDATE、DELETE文は遅くなります。

また、Customerテーブルのtelephoneフィールドに貼ったインデックスは上のクエリだとExplainの結果、使われていませんでした。これはなぜでしょうか。

オプティマイザの仕様によるものだとおもいます。

例えばMySQL 5.5以前ですと、

select * from Customer where Telephone = ANY(select telephone from Teletype where type ='fix');

のようなサブクエリを含んだSELECT文を実行した場合、
Telephoneとの比較は、Customerの行数 × Teletypeの検索結果行数分だけ行われるという仕様でした。
つまり、Customerの行数回、Teletypeの検索が行われます。

Teletypeの検索結果が分からないと、CustomerのどのINDEXを参照したら良いかわからないので使いようがない、ということだと思います。

ですので、「MySQLのサブクエリは遅い」というレッテルが貼られていた背景があります。

この場合、例えば

select * from Customer as A
left join Teletype as B
on A.Telephone = B.telephone
where B.type ='fix';

のようにJOINを行うとINDEXが利用されるので、こちらの方が圧倒的に速くなります。
(一例です。ANYのサブクエリと等価ではありません。
構文を精査していないので、検索結果は変わると思います。）

しかし、MySQL 5.6ではオプティマイザが改良され、この点が改善されていますので、
サブクエリを使ったSELECTでもINDEXが適切に利用されるかと思います。

INDEXを張ると良い列ですが、
・カーディナリティが高い列
・プログラムの仕様上、検索に使われる列
に張ると効果が得られます。

「カーディナリティが高い」とは、列に格納されるデータの種類が多いことを指すのですが、
例えばフラグを格納した列のように0か1しかない列の場合、
10000行に対して0を検索したら5000行が該当してしまいました、
ところが検索条件にもう一つtelephone = 'a'があったら、
残りの5000行については一行ずつ検査しなければなりません、
となって、あまり速度向上が望めなくなるからです。

「Last_nameフィールドによくインデックスをはるよう」というのは見たことが無いのですが、

上述したとおり、プログラムの仕様上検索に使われる、収録データ種類の多い列にINDEXを張るべきで、
それは多くの場合、プライマリキーになりえます。

こんな感じでよろしいでしょうか？

Answer 2

補足すると、

select * from Customer where Telephone = ANY(
  select telephone from Teletype where type ='fix'
);

このような非相関なサブクエリが、実質次のような相関サブクエリに書き換えられるらしいです。

select * from Customer where exists (
  select * from Teletype
  where Customer.Telephone = Teletype.telephone and Teletype.type ='fix'
)