56246


インデックスはSELECTクエリを見て作成すると思うのですが、1つのテーブルに対して1つのSELECTクエリしか発行しないとは限らないと思います

例えば、下記のように1つのテーブルに対して複数のSELECTクエリを実行する(可能性がある)場合は、どのクエリを元にインデックスを作成するのですか？

[code]

    SELECT * FROM t WHERE a = 1 
    
    SELECT * FROM t WHERE a = 1 ORDER BY b
    
    SELECT * FROM t WHERE c = 1 ORDER BY d
    
[/code]

  * **案1.SELECT文の数だけ、それに応じたインデックスを作成する**  
この場合は、インデックス(a)と複合インデックス(a,b)と複合インデックス(c,d)を作成する？

  * **案2.1つのSELECT文にだけ対応したインデックスを作成する**  
該当テーブルに対して、最も参照回数が多いSELECT文をどうにかして特定して、それに応じたインデックスだけを作成する

  * **案3.計測した結果に基づきインデックスを作成する**  
（レコード数などによって）ケースバイケースなので、面倒でも総当たりで全部試していって計測するしかない？

その他の案は何かありますか？


CC BY-SA 4.0
2019-06-29T13:47:44.393
56231
2019-06-30T07:27:23.007
7886
question
mysql
インデックス作成について


インデックスを作成することで`SELECT`は性能向上が見込まれますが、逆に`INSERT` / `UPDATE` /
`DELETE`等の更新処理はインデックスの更新のため性能低下します。更新処理中のロック時間が長くなることで他の処理も遅延します。質問のように`SELECT`だけでは判断できず、テーブルに対するアクセス全体を踏まえての判断が必要です。


CC BY-SA 4.0
2019-06-30T03:41:48.393
56239
2019-06-30T03:41:48.393
4236
56231
answer


> インデックスはSELECTクエリを見て作成すると思うのですが、

いいえ。SELECT以外のクエリでもインデックスは使われます。典型的には

[code]

    UPDATE  ... WHERE id = 1
    
[/code]

とか。INSERTですらインデックスが使われる可能性があります。

どのようにインデックスを張るのがよいかは、テーブル構造、データ量と内容、クエリの種類と頻度、その他諸々、さまざまな状況によって異なります。典型的にインデックスを用意すべきパターンや、RDBMSの制約によりインデックスがあっても絶対に使われない場合もあるので、必ずしも全部計測する必要はないし、現実的に不可能です。

＃逆に、インデックスを張ったがそれが有効に使われているかは必ず確認した方がよいです

というわけで、

>
[code]

>     SELECT * FROM t WHERE a = 1
>  
>     SELECT * FROM t WHERE a = 1 ORDER BY b
>  
>     SELECT * FROM t WHERE c = 1 ORDER BY d
>  
[/code]

これだけ見ても、インデックスの要否は全く判断できません。a =
1で抽出されるレコードがテーブルの大半だったりするとaに対するインデックスは無駄になるしょう。逆に、WHEREで十分にレコードが絞り込めるなら、ORDER
BYで見るカラムにインデックスを張る意味はありません。複合インデックスがよいか、カラムごとにインデックスがある方がよいかも状況次第です。

* * *

インデックスは計測性能を上げる代わり更新時のためにコストがかかります。ただし、INSERT/UPDATE/DELETEで性能低下するのではなく、INSERT/UPDATE/DELETEの結果
**インデックスが更新されるとき** にコストがかかる、というのが正確です。

[code]

    UPDATE tbl SET col = 1 WHERE id = 1
    
[/code]

というクエリでidのインデックスが(あるとしたら)使われることと、colのインデックスが(あるとしたら)更新されることは基本的に独立しています。

インデックスが全くないのと`id`にだけインデックスがあるのを比べれば(インデックスが有効であれば)性能は向上するでしょう。`col`にだけインデックスがあれば更新コストにより性能は低下しそうだということになります。両方にあればどうでしょう。性能向上と低下は相殺され、トータルでどうなるかは状況次第です。


CC BY-SA 4.0
2019-06-30T07:27:23.007
56246
2019-06-30T07:27:23.007
5793
56231
answer