MySQL_INDEX_1 - Speaker Deck

Slide 1

Slide 1 text

MySQLのINDEX入門前編 2012 年 8 月 24 日たぬ吉

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

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,・・・・・・ユーザ情報管理ファイル

Slide 6

Slide 6 text

Slide 7

Slide 7 text

indexってなに？(ふんわり概念) index有りだと・・・「user_id=1234567のnameは？」インデックスファイルユーザ ID 格納先の位置 1 1 バイト目 2 73 バイト目 3 165 バイト目・・・・・・ 1,234,567 83,456,789 バイト目インデックスファイルのユーザID(1234567)を⾒に⾏き、格納先の位置(83456789バイト目)を覚える

Slide 8

Slide 8 text

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バイト目)を直に⾒る

Slide 9

Slide 9 text

Slide 10

Slide 10 text

リーフブロックブランチブロックルートブロック 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でテーブルを検索する

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Copyright(C) ADWAYS CO., LTD. All Rights Reserved. クラスタインデックスの構造主キーのリーフブロックにデータが直接格納されている。リーフブロックブランチブロックルートブロック 1000 500 1500 主キー＋データ 1,鈴⽊,A型,からあげ・・・ 2,高橋,AB型,カレー・・・・・・主キー＋データ 11,原,O型,ハンバーグ・・・ 12,佐々木,A型,納豆・・・・・・主キー＋データ 21,小木,B型,チョコ・・・ 22,大森,O型,パスタ・・・・・・

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造リーフブロックに主キーの値を格納している。そのため、リーフブロックで主キーを取得し、そこから更に主キーで検索をする必要がある。リーフブロックブランチブロックルートブロック主キー + データ 66,阿部,B,煎餅・・・セカンダリインデックス=30 主キー = 66 セカンダリインデックスクラスタインデックス

Slide 16

Slide 16 text

Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造 ☆メリット後述します。 ☆デメリット・セカンダリで検索したあとにクラスタを検索するので、検索コストが⾼いです。単純計算でクラスタインデックスの二倍。・取得するレコード数が全体の15%以上なら、フルテーブルスキャンの方が早い場合もあります。 ※フルテーブルスキャン＝インデックスを利⽤せずに、テーブル全体を読み込むこと

Slide 17

Slide 17 text

Copyright(C) ADWAYS CO., LTD. All Rights Reserved. セカンダリインデックスの構造なぜ、フルテーブルスキャンの方が早いことがあるのか ①セカンダリインデックスは、リーフブロックにある主キーの値を用いて、クラスタへのランダムアクセスをする。 ②ランダムアクセスでは、データはバラバラのブロックに存在しているため、そのたびに I/O が発生してしまう。 → このため、取得するレコード数が全体の15%以上(ランダムアクセスが⼤量)の場合は、フルテーブルスキャンの方がデータをまとまって読み込める分、高速になる。

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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); ↓複合インデックス作成↓

Slide 22

Slide 22 text

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で確認できます。

Slide 23

Slide 23 text

Copyright(C) ADWAYS CO., LTD. All Rights Reserved. EXPLAINで検証しよう結果を確認しよう単一インデックスとカバリングインデックスの性能を比較しましょう。結果が以下のようになっていればＯＫですd(ﾟ∀ﾟd) -------------------------------------------------------------------- type : ref(一意キーまたは主キー以外のインデックスを使用している) rows : 単一インデックスより減っているか Extra : Using index (最適。インデックススキャンのみで済んでいる。つまり、カバリングインデックスが使われている。) -------------------------------------------------------------------- EXPLAINについては、今後、機会があったらまたエンジニアブログで書きます┏○))

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

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 インデックスマージは、単体インデックスを結合しているため、順番は関係なく使用できる。

Slide 28

Slide 28 text

Slide 29

Slide 29 text