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

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

ただいまの
回答率

89.10%

MySQLに関して、インデックスの機能を確認したいです

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 160

yakan

score 16

知りたいこと

インデックスについて確認させていただきたいことがございます。

仮にインデックスを次のように3つセットした場合ですが、

CREATE TABLE search_cache (
    ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    service_id TINYINT(1) UNSIGNED NOT NULL,
    q VARCHAR(100),
    color VARCHAR(10),
    updated_at DATETIME(6) NOT NULL,
    PRIMARY KEY  (ID),

    # インデックスをセット
    INDEX idx_search_cache_01(service_id),
    INDEX idx_search_cache_02(q),
    INDEX idx_search_cache_03(color)
);


この場合、次のSELECTでインデックスは効かないのではないでしょうか?

SELECT * 
FROM cache_datas 
WHERE service_id=1 AND q='りんご いちご' AND color='red'

なぜこう考えたのか

インデックスは
idx_search_cache_01 (service_idが対象カラム)
idx_search_cache_02 (qが対象カラム )
idx_search_cache_03 (colorが対象カラム)
という3つでセットしています。

WHEREの最初にservice_idを指定した場合、その時点でまず、検索対象データはidx_search_cache_01となりますよね。

このidx_search_cache_01に対しさらにAND q='りんご いちご'と指定していますが、idx_search_cache_01のインデックスの中にはqに対するインデックスはありませんから、idx_search_cache_01の中ではqの並び順は不明瞭ですよね。

そうであれば、qに対してセットしたidx_search_cache_02は、上記SELECTのように

一番目にidx_search_cache_01を指定したSELECT
(つまり一番目にWHERE service_id=1と指定したSELECT)

では効かないのではないかと考えたのですが…

対策

上記の考え方があっていれば、ひょっとして、複合インデックスというのはこのための機能なのでしょうか?

つまりインデックスは3つでなく次のように1つに複合して、
INDEX idx_search_cache_01(service_id,q,color)
こうすることではじめて、上記SELECTでもインデックスが効くという仕組みですか?

さらなる疑問

上記の考え方と対策が正しい場合ですが、そうすると検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか?

たとえばSELECTしたいパターンとして次の【A】と【B】2つがあった場合

# パターン【A】
SELECT * 
FROM search_cache
WHERE service_id=1 AND q='りんご いちご' AND color='red'

# パターン【B】
SELECT * 
FROM search_cache
WHERE service_id=1 AND color='red'


この場合は複合インデックスも次のように貼るということになるのでしょうか?
【A】のためのインデックス → INDEX idx_search_cache_01(service_id,q,color)
【B】のためのインデックス → INDEX idx_search_cache_02(service_id,color)

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+2

FROM句の一つのテーブルに対して使用されるインデックスは一つです。
また、インデックスを使用するかどうかは、統計情報を元にオプチマイザーが判断します。

マルチカラムインデックスで項目が全て揃っているからと言って使用されるわけでもありません。
項目の並びが逆の場合に非効率と判断されて使用されない事もあります。

質問の例で使用されないのは、総じて件数が少ないから、全表検索が効率的とオプチマイザーが判断したのでしょう。

検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか?

SQLが違うなら、当然適切なインデックスも変わります。
但し、「大は小を兼ねる」事が出来ますので、兼用できるようにインデックスを構成します。
項目の並びは、カーディナリティの低いものから並べるのがコツです。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2020/07/01 17:10 編集

    full scanに対応したインデックスを作成しかつfull scanを指定するって事ですか。
    その上、後から早くしろって言う(w
    そのDBAが手抜きしたとしか思えませんね。

    キャンセル

  • 2020/07/01 18:39

    逐次処理の方で必要なインデックスはあるけど、バッチでそれを使うとかえって遅くなるから full scan 固定で、って話でしてね。full scan なら対象のレコード数の想定からだいたい性能が見積もれるので。
    でまあ……後から早くするのは DBA のせいというより全体の設計の話で。どうにか頑張って2時間ぐらいまで処理性能を上げて、余裕を作りましたけどね……処理の一部をオンメモリ化するとか苦労させられました。まだオンメモリDBのオプションがない時代だったんで、フルスクラッチですわ(汗

    なおそのときに DBA の手が足りないからと言うことで、なぜか自分が(チーム内どころか他チームまで)チェックとチューニングをする羽目になったという苦い思い出が(汗

    キャンセル

  • 2020/07/01 22:20 編集

    夜間バッチが1日じゃ終わらないというのは、あるあるですね。
    チューニングの際に、試行錯誤で無駄と思える処理が性能に貢献するというのを何度か経験しました。
    何れ改善されるだろうから無駄な知識でしかないですけどね。

    キャンセル

+1

3カラムにまたいだ検索が前提なら、3カラムを複合インデックスで貼ってください

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2020/07/01 13:33

    >q='りんご いちご'も微妙ですね。
    qは検索ワードを保存するカラムで、ソートされているので完全一致でいいかと思っています。

    キャンセル

  • 2020/07/01 13:56 編集

    別に3カラム複合で2カラムしか検索条件に使わなくてもインデックスは有効です

    キャンセル

  • 2020/07/01 14:06

    ありがとうございます

    キャンセル

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

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

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