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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

3回答

8623閲覧

インデックスが効く例と効かない例を整理したい

mikan1998

総合スコア7

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2020/08/04 07:04

編集2020/08/04 12:31

###前提
こちらのstudentsテーブルについてお尋ねします。

SQL

1CREATE TABLE students ( 2 `ID` INT NOT NULL AUTO_INCREMENT, 3 `class_id` INT NOT NULL, 4 `type_id` INT NOT NULL, 5 `content_id` INT NOT NULL, 6 PRIMARY KEY (ID), 7 UNIQUE unique_students_1 (`class_id`,`type_id`,`content_id`) 8); 9INSERT INTO students (`ID`,`class_id`,`type_id`,`content_id`) 10VALUES (1,10,100,1000),(2,20,200,2000),(3,30,300,3000),(4,40,400,4000); 11

###ケースと理由
SELECTでインデックスが効くケースと効かないケースをまとめ、その理由を明示しました

効く効かないの判断ならびに、その理由が以下であっているか教えて頂けませんでしょうか

SQL

1# ケース1:インデックスが効く 2# 理由:「index_students_1」の順番通りなので 3SELECT * 4FROM students 5WHERE class_id=10 AND type_id=100 AND content_id=1000 6 7# ケース2:インデックスが効かない 8# 理由:「index_students_1」の順番通りだが、最初の1つが抜けているため 9SELECT * 10FROM students 11WHERE type_id=100 AND content_id=1000 12 13# ケース3:インデックスが効かない 14# 理由:「index_students_1」の順番通りだが、最初の2つが抜けているため 15SELECT * 16FROM students 17WHERE content_id=1000 18 19# ケース4:インデックスが効かない 20# 理由:「index_students_1」の順番通りでないため 21SELECT * 22FROM students 23WHERE content_id=1000 AND type_id=100 24 25# ケース5:インデックスが効かない 26# 理由:「index_students_1」の順番通りでないため 27SELECT * 28FROM students 29WHERE class_id=10 AND content_id=1000 30

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

認識が違うと思われる箇所だけコメント。

ケース4:インデックスが効かない
理由:「index_students_1」の順番通りでないため

理由が違います。理由はケース2,3と同様

ケース5:インデックスが効かない
理由:「index_students_1」の順番通りでないため

インデックスが使用される場合の方が多いと思います。
(というか使用される前提で設計しますが、使用されない場合があるかどうかを確認したことが無いですね)
但し、(class_id,content_id)のインデックスより効率は悪い

以下を一読される事をお薦めします。
8.3.1 MySQL のインデックスの使用の仕組み

質問はインデックスの使用/未使用ですけど、言葉通りに効く/効かないで言うと、母集合に対しての抽出割合次第です。
割合が大きい場合にはインデックスがあっても使用する方がコスト高になり使用されないので、インデックスは効きません(効果が無い)

投稿2020/08/04 08:18

編集2020/08/04 11:55
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mikan1998

2020/08/04 09:41 編集

■ケース4につきまして 「>ケース2,3と同様」ということは、ケース4の理由は「class_idが抜けているから」ということなのでしょうか?それでは、3つが揃った以下ケース6は効くのでしょうか? # ケース6:インデックスが…効く? # 順番通りではないが、3つ揃っているため SELECT * FROM students WHERE content_id=1000 AND type_id=100 AND class_id=10 index_students_1 の順番と異なるため、content_id=1000はフルスキャンしなければならないように思うのですが。
mikan1998

2020/08/04 09:42

■ケース5につきまして 「>インデックスが使用される場合の方が多い」というのは、「指定したcontent_id=1000を探すのには効かないが、class_id=10を探すのには効く」という意味でよろしいでしょうか?
sazi

2020/08/04 11:22

where条件の記述の並びは関係ありません。 インデックスの使用条件はwhere条件だけではなくorder byや結合条件なども含まれ、どういった項目が条件に含まれるかです。
sazi

2020/08/04 11:24

ケース5はclass_idの条件によりインデックスを使用する事が決定され、結果的にcontent_idの条件もインデックスを参照することになります。
sazi

2020/08/04 11:29 編集

複雑なSQLだとチューニングに手間取ったりしますが、質問にあるSQL程度なら、実行計画で試した方が早いですよ。 > EXPLAINができない どんな理由ですか?
mikan1998

2020/08/04 13:52 編集

WHEREの並び順がインデックスに関係ないとは驚きです。今知れて本当によかったです。どうもありがとうございました。 そしてただいまEXPLAINできる状況になったので確認しました。 まず並び順は仰る通り関係なく、3つのWHEREをどう組み合わせても同じEXPLAINでした。 ただし「WHERE content_id=1000」という1つだけの場合はフルスキャンとなりました。 そしてEXPLAINのrows項目について確認させていただきたいのですが、 ・ケース1はrows項目が『1』だった ・ケース4はrows項目が『777』だった ので、これはtype_id=100のものがstudentsテーブルに『777』件あり、ケース4はその『777』件から探すという点で、ケース1の『1』件から探すよりも遅い。 という解釈であっているでしょうか。
sazi

2020/08/04 13:36

rowsはfetchする推定行数ですから、基本的には値が少ない方が高速ですが、アクセスプラン(type)によっては逆になる場合もあるでしょう。
mikan1998

2020/08/04 13:52

奥が深いですね………… ありがとうございます。修行します。
mikan1998

2020/08/04 14:02

逆になる場合ございますか?ALLからconstまで考えたのですが、rowsの値が多い方が高速だという場合が想定できません…
sazi

2020/08/04 14:17 編集

rowは結果ではなく推定(結果ではないので、実際より多かったり少なかったり)ですし、typeがconstとindexだったりしたら当然違いは出ると思います。 ただ、うまい具合にその比較となる状況が作れるかどうかは分かりませんが。
sazi

2020/08/04 14:21 編集

上記は同じSQLであることは想定していませんよ。 SQLが違っても同じ結果になるような場合に、rowsの件数だけ見て判断するものではないという事です。
mikan1998

2020/08/04 23:52

なるほど今後そういった事態に遭遇したときに気をつけてチェックします。ありがとうございました。
guest

0

今回の質問のインデックスは

INDEX index_students_1 (class_id,type_id,content_id)

の順に並んでいます。ですから、WHERE句はインデックスの並びを意識して記述し、EXPLAINによるMySQLの実行計画の確認 で有効にインデックスが使われているか確認します。
必要なら別のインデックスを作ることもありますが、インデックスが増えると INSERT, UPDATE, DELETE でインデックスの更新に負荷がかかる事は認識しておきましょう。

投稿2020/08/04 07:11

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mikan1998

2020/08/04 07:12

EXPLAINができないので知りたいのですが、教えていただけませんでしょうか?
sazi

2020/08/04 11:30

> WHERE句はインデックスの並びを意識して記述 そんな事は無いと思うのですが?
guest

0

効く効かないの判断

これは実際にEXPLAINで確認しましょう。データ量が少ない場合には、「全部読んでもコストはそう掛からない」とインデックスを使わない動作をすることも考えられます。

なお、「ケース5」では、class_idの条件だけにインデックスが適用される、ということも考えられます。


そもそも論にはなりますが、INDEX index_students_1 (class_id,type_id,content_id)はどのような意図で設定したものでしょうか。特定のインデックス(しかも、複合インデックス)を掛けるからには、その理由が必要です(役に立たないインデックスがあると容量や更新速度でマイナスになるだけです)。

投稿2020/08/04 07:08

maisumakun

総合スコア145123

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mikan1998

2020/08/04 07:15

ありがとうございます。ケース5は確かに、最初のclass_idにだけ効いて、content_idには効かないわけですね。なるほど気づきませんでした。他のケースはあっていますよね?
maisumakun

2020/08/04 07:18

> 他のケースはあっていますよね? 効くかどうかは状況依存です。ほぼすべての行をSELECTしないといけなくなるというように、データ分布が偏っていた場合にはインデックスが利かないこともあります(「利かない」というほうは利かないで間違いありません)。
mikan1998

2020/08/04 07:34

なるほど状況によるのですね。思ってもみませんでした。 ただ、仰る分布性に鑑みるなら大丈夫そうに思います まずデータ数は次の想定で class_id:10種類 type_id:10種類 content_id:100万レコード そして分布も次のように均等に近くなる想定です content_id1~10000 までは、class_id1 で type_id1 content_id10001~20000 までは、class_id1 で type_id2 content_id20001~30000 までは、class_id1 で type_id3 ・ ・ content_id990000~1000000 までは、class_id10 で type_id10 もしかしたら content_id1~100000 までは、class_id1 で type_id1 のように、他は10000件なのに100000件ある、程度の分布の偏りはあるかもしれませんが・・
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問