SQLを速くするインデックスとは

Transcript

1. SQLを速くする Indexとは
2. もくじ 2 1 3 5 6 4 2 インデックスとは B+Tree構造とは まとめ B-Tree構造とは インデックス使用時に 気をつけること 　　　　　問題
3. 1. インデックスとは 3
4. “ たとえば、こんなSQLを実行したい 4
5. インデックスがない場合は、上から順に見て いくことになる 5 　　JPN 　　USJ 　　ISL 　　EGY 　　・・・ 　　・・・ 　　CHN 　　GBR LIMITの指定やユニーク制約がされていない場合は、全て のデータを見ていくことになり、データ量によっては時間が かかる、、、
6. インデックスは、検索しやすいように索引を作る 6 　　AZE 　　AFG 　　・・・ 　　BDI 　　BEL 　　・・・ 　　GAB 　　GBR アルファベット順にあらかじめ並べておくことで、検索ス ピードが速くなる A G B インデックスとは、全体の内容の中から特定の内容 を探すために使用する、本の索引のような概念のこ と
7. SELECT文の実行計画を確認する 7 実際にインデックスが使用されているかは SQL文の頭に”explain”をつけることで確認ができる type → テーブルに対するアクセス方法。ALLの場合はフルスキャン、refはindexを使用した検索を表す rows → 取得件数の見積もり Extra → Using indexはインデックスが使用されていることを表す
8. インデックスが使用されていない例 8 ヒットした件数は4件だが、typeはフルスキャンを表す ALLとなっており、rowsは29670件、ExtraもUsing whereとなっていることから indexが使用されていないことがわかる ただし、indexが使用できる場合も、オプティマイザがフルスキャンの方が早いと判断した場合は、 あえて インデックスを使用しないという判断をする場合もある ので注意
9. インデックスが人気の理由 ・SQL文を変更しなくても性能改善できる ・テーブルのデータに影響を与えない ・それでいて一定の効果が期待できる 9
10. 実際にRDBはどのようにインデックスを管理しているのか インデックス情報は”SHOW INDEX FROM テーブル名”で確認できる 10 インデックスの管理方法には様々なものがあるが、現在のほとんどの RDBではB+Tree構 造を使用している(B+TreeとはB-Treeを改良したもの)
11. 2. B-Tree構造 11
12. B-Tree構造とは、1つのノードが2つ以上の子ノードを持つことができる ツリー構造のこと 12 https://www.cs.usfca.edu/~galles/visualization/BTree.html ノードは上からルートノード、ブランチノード、リーフノードと呼ばれている
13. ツリー構造の優位性 ツリー構造はデータベースに限らず、 システムの世界ではデータを保持するためによく使用され る構造 13 B-Treeのよい点として、「どんな値に対しても同じくらいの時間で結果を得られる」 というものがある これを「均一性」とよぶ
14. フルスキャンがテーブルのサイズにほぼ比例する形で実行時間が伸 びるのに対して、インデックスを使用した場合はデータ量に左右され ない 14
15. 3. B+Tree構造 15
16. B+Treeの特徴 MySQLで採用されているのがB-Treeの改良版であるB+Tree B-Treeとの違い 16 ・リーフノードが双方向で連結している → 範囲検索などに強い
17. 17 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html データは必ずリーフノードに格納されるようになっている
18. その他の副次的な効果としても重宝される ・GROUP BY句 ・集約関数(COUNT/SUM/AVGなど) ・集合演習(UNION/INTERSELECT/EXCEPT) 18 これらを利用する際にはソートのキーとする列が必要となるため、そのキー列にイン デックスが存在すると、オプティマイザがこれをうまく利用してくれる
19. 4. 使用時の注意点 19
20. インデックスを使用するときの注意点 無駄なインデックスを多く作りすぎるというのは、データ ベース設計のアンチパターンの1つ 20 ・INSERTとUPDATE時にはインデックスの更新も発生するため、処理時間に影響が出る ・1つのテーブルに複数のインデックスを指定した場合、オプティマイザが どのイン デックスを使用するべきか判断できない ことがある → 意図したものとは異なるインデックスが使用され、処理時間が遅くなることがある 不要なインデックスがあると
21. 1, サイズが大きいテーブルにだけ作る 規模が小さいテーブルにインデックスを指定してもあまり 意味がない 21 大きい、小さいの基準とは？ 具体的な数字はわかっていないが、、、 「100万以下のテーブルは「小~中規模」と考えていい この規模のテーブルにインデックスを使用してもあまり効果がない」(おうち で学べるデータベースのきほん)
22. 2, カーディナリティの高い列に作る カーディナリティとは「値の分散度」を表す言葉 (都道府県の場合は47、アルファベットの場合は27になる) 22 カーディナリティが低いキーにインデックスを使用しても、検索時にオプティマイザが フルス キャンの方が早いと判断をし、インデックスが使用されない可能性が高い 複合インデックスを使用する際にも、 カーディナリティが高いものを先頭に持ってくることが 大切
23. 5. まとめ 23
24. SQLのチューニングにはイ ンデックスが便利！ ただし、むやみに使用するのは逆効果！ どこに使用するかを見極めるのが大切 24
25. 6. 問題 25
26. 次のうち、インデックスを使用するのに最も適していな いのは？ 1, 干支 26 2, 保険番号 3, 年齢
27. 次のうち、インデックスを使用できないものは？ 1, WHERE mail IS NULL (NULL検索) 27 2, WHERE id != 100 (否定形) 3, WHERE name LIKE “%鈴木” (LIKE検索)
28. (last_name, first_name, age)という順の複合イン デックスが使用されていた場合、インデックスが利用で きる検索条件は？ 1, age 28 2, last_name, first_name 3, last_name, age 4, first_name, age
29. 参考文献 ◎ おうちで学べる データベースのきほん ◎ B-treeインデックス入門 ◎ 漢のコンピューター道 29