MySQL_INDEX_1

Transcript

1. MySQLのINDEX入門 前編 2012 年 8 月 24 日 たぬ吉
2. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. 前提 MySQL5.5 InnoDB
3. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. MySQLのチューニングで意識すること スキャン範囲を絞る → これらをindexによって実現します！ Ｉ／Ｏ回数を軽減する ※ディスク I/O = HDDへのデータの読み書き
4. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. そもそもindexってなに？(ふんわり概念) テーブル版の本の索引のようなもの
5. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexってなに？(ふんわり概念) もし、index無しだと・・・ 「user_id=1234567のnameは？」 user_id=1,name=亜土義男,name_kana=yoshio ado ,・・・・・・・ user_id=2,name=上井孝雄,name_kana=takao uei,・・・・・・・ ・・・・・・・ user_id=1234567,name=図志々雄,name_kana=shishio zu,・・・・・・ ユーザ情報管理ファイル
6. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexってなに？(ふんわり概念) この場合は、上から順番に検索することになります。 これをシーケンシャルアクセスと言います。 実⾏時間はデータサイズに⽐例します。
7. indexってなに？(ふんわり概念) index有りだと・・・ 「user_id=1234567のnameは？」 インデックスファイル ユーザ ID 格納先の位置 1 1 バイト目 2 73 バイト目 3 165 バイト目 ・・・・・・ 1,234,567 83,456,789 バイト目 インデックスファイルのユーザID(1234567)を⾒に⾏き、 格納先の位置(83456789バイト目)を覚える
8. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexってなに？(ふんわり概念) 1バイト目 73バイト目 165バイト目 ・・・・ 83,456,789 バイト目 index有りだと・・・続き user_id=1,name=亜土義男,name_kana=yoshio ado ,・・・・・・・ user_id=2,name=上井孝雄,name_kana=takao uei,・・・・・・・ ・・・・・・・ user_id=1234567,name=図志々雄,name_kana=shishio zu,・・・・・・ ユーザ情報管理ファイル 格納先の位置(83,456,789バイト目)を直に⾒る
9. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexってなに？(ふんわり概念) これをランダムアクセスと言います。 実⾏時間はデータサイズに⽐例しません。
10. リーフブロック ブランチブロック ルートブロック Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexの構造 RDBMSで通常採用されているindexは「B+Tree」 1000 999を探したい！ 500 1500 1(ID:0001) ・ ・ ・ 499(ID:0499) 500(ID:0500) ・ ・ ・ 999(ID:0999) 1000(ID:1000) ・ ・ ・ 1499(ID:1499) 1500(ID:1500) ・ ・ ・ 1999(ID:1999) 1000未満 1000以上 500未満 500以上 このIDでテーブルを検索する
11. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. indexの構造 InnoDBもB+Treeです。 ただし、ちょっと特殊で、indexが2種類あります。 １．クラスタインデックス ・・・主キーか一意キーをもとに 作成されるインデックス。 ２．セカンダリインデックス ・・・上記以外のインデックス。
12. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. クラスタインデックスの構造 主キーのリーフブロックにデータが直接格納されている。 リーフブロック ブランチブロック ルートブロック 1000 500 1500 主キー ＋ データ 1,鈴⽊,A型,からあげ・・・ 2,高橋,AB型,カレー・・・ ・ ・ ・ 主キー ＋ データ 11,原,O型,ハンバーグ・・・ 12,佐々木,A型,納豆・・・ ・ ・ ・ 主キー ＋ データ 21,小木,B型,チョコ・・・ 22,大森,O型,パスタ・・・ ・ ・ ・
13. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. クラスタインデックスの構造 データがソートされているので、 隣接したインデックスが同じブロックに格納されています。 ということは、 少ない I/O で検索できます！
14. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. クラスタインデックスの構造 ☆メリット ・主キーの値で検索をすると非常に⾼速 ☆デメリット ・インデックスのサイズが大きくなってしまうこと。 ・セカンダリインデックスを⽤いた検索が 遅くなってしまうこと(理由は次のページで)
15. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造 リーフブロックに主キーの値を格納している。 そのため、リーフブロックで主キーを取得し、そこから更に主キーで検索をする必 要がある。 リーフ ブロック ブランチ ブロック ルート ブロック 主キー + データ 66,阿部,B,煎餅・・・ セカンダリインデックス=30 主キー = 66 セカンダリインデックス クラスタインデックス
16. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造 ☆メリット 後述します。 ☆デメリット ・セカンダリで検索したあとにクラスタを検索するので、 検索コストが⾼いです。 単純計算でクラスタインデックスの二倍。 ・取得するレコード数が全体の15%以上なら、 フルテーブルスキャンの方が早い場合もあります。 ※フルテーブルスキャン ＝ インデックスを利⽤せずに、テーブル全体を読み込むこと
17. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造 なぜ、フルテーブルスキャンの方が早いことがあるのか ①セカンダリインデックスは、リーフブロックにある主キーの値を用いて、 クラスタへのランダムアクセスをする。 ②ランダムアクセスでは、データはバラバラのブロックに存在しているため、 そのたびに I/O が発生してしまう。 → このため、取得するレコード数が全体の15%以上(ランダムアクセスが ⼤量)の場合は、フルテーブルスキャンの方がデータをまとまって読み込め る分、高速になる。
18. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造 セカンダリインデックスってデメリットばっかりに思え る・・・(´・_・｀) ・・・そんなことないですよ！ 「複合インデックス」や「インデックスマージ」を使え ばとっても高速になります。 これらを使う手法を「カバリングインデックス」といい ます。
19. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. カバリングインデックスをするとなぜ早いのか クエリが必要とするカラムを すべてインデックスに含めることで、 検索をインデックスだけで 完結させることができるからです。
20. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. カバリングインデックスをするとなぜ早いのか リーフブロックに必要な情報が全て入っている ↓ クラスタまで検索しなくていい ↓ ディスク I/O を減らせる(*ﾟ∀ﾟ*)
21. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. 複合インデックスとは 複数のカラムを組み合わせて、１つのインデックスにする。 例） SELECT * FROM target WHERE target_id = 100 AND category_id = 60 AND ng_flag = 1; ALTER TABLE target ADD INDEX index_target(target_id,category_id,ng_flag); ↓複合インデックス作成↓
22. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. 複合インデックスとは 試してみよう！ ▪複合インデックスを作成する ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名1,カラム名2,カ ラム名3); ▪複合インデックスが作成されているか確認 SHOW INDEX FROM テーブル名; ▪EXPLAINで確認する EXPLAIN SELECT カラム名を指定 FROM テーブル名 WHERE 条件文; ※正しく検証するため、selectのたびにページキャッシュを解放しておくといいです。 echo 1 > /proc/sys/vm/drop_caches 解放されたかどうかはfree -mで確認できます。
23. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. EXPLAINで検証しよう 結果を確認しよう 単一インデックスとカバリングインデックスの性能を比較しましょう。 結果が以下のようになっていればＯＫですd(ﾟ∀ﾟd) -------------------------------------------------------------------- type : ref(一意キーまたは主キー以外のインデックスを使用している) rows : 単一インデックスより減っているか Extra : Using index (最適。インデックススキャンのみで済んでいる。 つまり、カバリングインデックスが使われている。) -------------------------------------------------------------------- EXPLAINについては、今後、機会があったらまたエンジニアブログで書きます┏◦))
24. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. インデックスマージとは 単一のインデックスを組み合わせて結合させたキーです。 複合インデックスに似てるけれど少し違います。 複合インデックス インデックスマージ (emp_1, emp_2, emp_3 の単体インデックス)
25. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. インデックスマージとは WHERE句に指定された 複数のカラムに対しての複合インデックスは無いが、 カラムの個々に対してのインデックスは作成されている場合に、 インデックスマージという処理が⾏われることがあります。
26. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. インデックスマージとは 確認方法： EXPLAINで、typeに「index_merge」と表示されていれば、 インデックスマージが使われています。 効果： 複数のインデックスを読み込んでから結合するため、 複合インデックスに⽐べると効率が良くないです。 なるべく複合インデックスを使いましょう☆
27. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. 複合インデックスとインデックスマージの違い 使用可能なインデックスについて インデックスマージ 複合インデックス ・emp_1 ・emp_1、 emp_2 ・emp_1、 emp_2、 emp_3 複合インデックスは、複数のインデックスを繋げているため、 先頭のインデックスから順に部分インデックスとして使用できる。 ・ ・ ・ ・ emp_1 ・ ・ ・ ・ emp_2 ・ ・ ・ ・ emp_3 ・ ・ ・ ・ emp_1 、 、 、 、 emp_3 インデックスマージは、単体インデックスを結合しているため、順番は関係なく使用できる。
28. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. おしまい 今回は、INDEXとは何か、どんなINDEXがあるかについてご 説明させていただきました。 次回は、後編としてINDEXの効率の良い使い⽅や注意点など をご紹介したいと思います(*´ｪ`*) 乞うご期待！
29. Copyright(C) ADWAYS CO., LTD. All Rights Reserved. おしまい 最後まで読んでくださって、 ありがとうございました！