質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.93%

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

解決済

回答 2

投稿

  • 評価
  • クリップ 9
  • VIEW 5,247

score 1086

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

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

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

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

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+7

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

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

一方、インデックスを張ってある場合は索引が使われ、
索引というからにはソートが行われていて、
検索文字がどの行に収録されているか、全ての行を検査しなくてもピンポイントでわかるので
無駄な読み込みが発生しないため速い
ということです。
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を張るべきで、
それは多くの場合、プライマリキーになりえます。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

補足すると、

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'
)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.93%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る