MySQL_INDEX_2.pdf

Transcript

1. 2012年11月30日 たぬ吉 MySQLのINDEX入門 後編
2. Copyright (C) ADWAYS INC. All Rights Reserved. 前提 前提 前提 前提 MySQL5.5 InnoDBストレージエンジン
3. Copyright (C) ADWAYS INC. All Rights Reserved. 今回ご紹介するのは 今回ご紹介するのは 今回ご紹介するのは 今回ご紹介するのは インデックスを使用するときに気をつけること。 せっかくインデックスを張っていても、 正しく指定しなければ性能は発揮されません；
4. Copyright (C) ADWAYS INC. All Rights Reserved. と、いうことで と、いうことで と、いうことで と、いうことで まずは、WHERE句編です☆
5. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること 否定( != )と 範囲指定(< >) はインデックスが使用できない × WHERE age != 27 and age != 33 × WHERE age >= 28 and age <= 32 ◦ WHERE age IN (28,29,30,31,32)
6. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること 否定( != )と 範囲指定(< >) はインデックスが使用できない。 INやORで置き換える。 × WHERE age != 27 and age != 33 ◦ WHERE age IN (28,29,30,31,32) × WHERE age >= 28 and age <= 32 ◦ WHERE age=28 OR age=29 OR age=30 OR age=31 OR age=32 ア ラ サ ー ア ラ サ ー ア ラ サ ー ア ラ サ ー
7. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること WHERE句の全てのANDにかかっていないインデックスは 使用できない WHERE emp_1 = 1 AND emp_2 = 2 OR emp_1 = 3 AND emp_3 = 4 ◦ emp_1 × emp_2 × emp_3
8. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること 曖昧検索時は「定数⽂字列＋前⽅⼀致」のみインデックスが使用可能。 それ以外では使用できない。 ◦ WHERE emp_1 LIKE ‘⽂字列%’ ←定数⽂字列＋前⽅⼀致 × WHERE emp_1 LIKE ‘%⽂字列’ ←定数⽂字列＋後⽅⼀致 × WHERE emp_1 LIKE ‘%⽂字列%’ ←定数⽂字列＋前⽅と後⽅⼀致 × WHERE emp_1 LIKE moji ←変数
9. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること NULLレコードもインデックスされる。 これは、MySQL(InnoDBストレージエンジン・MyISAMストレー ジエンジン)の特有の動きです ⼀般的にはB-treeではNULLをキー値として保持しないため、 column_name IS[NOT] NULLを使用した検索でも インデックスは使用されません。
10. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること [複合インデックスの場合] 前のキーが範囲検索だと、それ以降のキーが使用されないことがある。 WHERE emp_1 > 1 AND emp_2 = 2 AND emp_3 = 3 ◦ emp_1 × emp_1, emp_2, emp_3
11. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること ので、インデックスが使用されるように書き換えましょう！ WHERE emp_1 > 1 AND emp_2 = 2 AND emp_3 = 3 WHERE emp_1 IN (2,3) AND emp_2 = 2 AND emp_3 = 3
12. Copyright (C) ADWAYS INC. All Rights Reserved. WHERE句編おしまい 句編おしまい 句編おしまい 句編おしまい 次は、ORDER BY句編です☆
13. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること 連続しないキーはインデックスが使用できない。 ORDER BY emp_1, emp_3 ↓ ↓ ので、連続するように書き換えましょう ↓ ↓ ORDER BY emp_1, emp_2, emp_3 複合インデックス
14. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること 複数のキーがある場合はインデックスが使用できない。 ORDER BY emp_1, emp_2 × emp_1 × emp_2 単体インデックス
15. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること ASCとDESCが混在している場合はインデックスが使用できない。 ORDER BY emp_1 DESC, emp_2 ASC × emp_1 × emp_2 × emp_1, emp_2 複合インデックス
16. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること ☆もう１つ注意点 DESCを指定する場合は、全てのカラムに対してDESCを指定する必 要があります。 (デフォルトのソート順はASCのため) ◦ ORDER BY emp_1 DESC, emp_2 DESC × ORDER BY emp_1 , emp_2 DESC ↑ASCとDESCが混在してしまい、インデックスが使われません
17. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること WHERE句で使用されているものと別のキーの場合は、 インデックスが使用されない。 WHERE emp_1 = 1 ORDER BY emp_2 △ emp_1 △ emp_2 ※どちらか片⽅のみ使用可能 WHERE句とORDER BY句で同じキーを使用するか、 複合インデックスで解決しましょう☆
18. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句で気をつけること 句で気をつけること 句で気をつけること 句で気をつけること ORDER BY句とGROUP BY句で使用している項目が異なる場合はイ ンデックスが使用できない GROUP BY emp_1 ORDER BY emp_2 ◦ emp_1 × emp_2 ※EXPLAINをすると、ExtraにUsing temporaryと表示されます。 これを解決するには、サーバ変数のtmp_table_sizeのサイズを変更するな ど、設定レベルでの対応が必要になり、おススメできません＞＜
19. Copyright (C) ADWAYS INC. All Rights Reserved. ORDER BY句編おしまい 句編おしまい 句編おしまい 句編おしまい まだまだ続きますよー！ 次は、その他編です☆
20. Copyright (C) ADWAYS INC. All Rights Reserved. その他 その他 その他 その他 気をつけること 気をつけること 気をつけること 気をつけること HAVING句はインデックスが使用されない。 WHERE句でインデックスが使えるなら、なるべくHAVING句は使わないよ うにしましょう。 速度に⼤きな差が出ます。
21. Copyright (C) ADWAYS INC. All Rights Reserved. その他 その他 その他 その他 気をつけること 気をつけること 気をつけること 気をつけること 速度を求める場合は、 distinct や unionをなるべく使用しない。 distinct や unionは、重複句を排除するために内部的にソート処理 を⾏うため遅くなる。
22. Copyright (C) ADWAYS INC. All Rights Reserved. その他 その他 その他 その他 気をつけること 気をつけること 気をつけること 気をつけること あれっ、意図したインデックスが使われてなくて遅い・・・； そんなときは、 FORCE INDEXで利用するインデックスを強制指定 しましょう！ FROM テーブル名 FORCE INDEX(キー名) ※MYSQLオプティマイザを無視して、指定されたインデックスを使用します。 ORDER BY や GROUP BY の解決時にインデックスを使用するかどうかには 影響しません。
23. Copyright (C) ADWAYS INC. All Rights Reserved. その他編おしまい その他編おしまい その他編おしまい その他編おしまい ふぅ・・・ あともう少しお付き合いください！ 次は、効果UP編です☆
24. Copyright (C) ADWAYS INC. All Rights Reserved. 効果 効果 効果 効果UP編 編 編 編 絞り込んだときに結果件数が少ないカラムをインデックスにする。 このようなインデックスを「選択性の高いインデックス」と言います。 最強に選択性が高いインデックスは、PRIMARY KEYです。 逆に選択性が低いのは、二種類しか値を持たないようなインデックス です。 IDや誕生日 性別やON/OFF
25. Copyright (C) ADWAYS INC. All Rights Reserved. 効果 効果 効果 効果UP編 編 編 編 また、複合インデックスを作成するときは、 選択性が高いカラムを先頭にする。 ↓複合インデックスを作成するSQL⽂ ALTER TABLE target ADD INDEX index_target(target_id,category_id,ng_flag); 最強 まあまあ OKかNGしか無い
26. Copyright (C) ADWAYS INC. All Rights Reserved. 効果 効果 効果 効果UP編 編 編 編 WHERE句に単独で使われることもあるカラムは、 インデックスの最初に指定する。 複合インデックスは、先頭から順に部分インデックスとしても使用で きるため、単独で使われるカラムは最初に指定してください。 まとめると、 選択性が高い もしくは 単独で使われることもあるカラム から順に指定しましょう☆
27. Copyright (C) ADWAYS INC. All Rights Reserved. 効果 効果 効果 効果UP編おしまい 編おしまい 編おしまい 編おしまい もういっちょおまけに インデックスサイズについてです☆
28. Copyright (C) ADWAYS INC. All Rights Reserved. インデックスサイズについて編 インデックスサイズについて編 インデックスサイズについて編 インデックスサイズについて編 効率UPして便利なインデックスですが、貼りまくればいいというわ けではありません・・・(´・_・｀) 貼りすぎると以下のような悪影響が出てしまいます。 ・INSERT, UPDATE, DELETEなどでデータに変更があるたびに、 インデックスも更新されるため、更新性能が低下する。 ・インデックス情報を格納するため、ディスクを圧迫する。
29. Copyright (C) ADWAYS INC. All Rights Reserved. インデックスサイズについて編 インデックスサイズについて編 インデックスサイズについて編 インデックスサイズについて編 これらを防ぐには「データ⾃体を減らす」「インデックスのキー⻑を 短くする」などがありますが、 ⼀番は必要な分だけのインデックスを貼ってスマートにです！
30. Copyright (C) ADWAYS INC. All Rights Reserved. おしまい おしまい おしまい おしまい 今回は、INDEXの効率の良い使い⽅や注意点などを説明させ ていただきました。 これにて「MySQLのINDEX入門 前編 / 後編」完結です！
31. Copyright (C) ADWAYS INC. All Rights Reserved. おしまい おしまい おしまい おしまい 後編から⾒てくださった⽅も、 前編から通して⾒てくださった⽅も、 お疲れ様でした＆ありがとうございました(m。_。)m