SQLを速くする！インデックス入門

配信動画はこちら
https://www.youtube.com/watch?v=OsIxUT7D728&feature=youtu.be

もし良かったらムーザルちゃんねるのチャンネル登録お願いします！
https://www.youtube.com/channel/UCLPHXwLp90A5R69Eltxo-sg

Twitter でもプログラミングネタをつぶやいているのでフォローお待ちしております。
ムー
https://twitter.com/mu_vpoe
zaru
https://twitter.com/zaru

Transcript

1. YouTube Live (2020.09.24 Thur. 21:00~)
2. 話す人 現役のエンジニア二人 赤貝が好きな CTO と デザイン勉強中のエンジニア @mu_vpoe 最近の仕事は figma で画 面設計をつくることです ムー zaru @zaru CTO, Love 赤貝, JavaScript, Firebase, Web Components. Twitter フォロー お願いします！
3. 話すこと インデックスとは B-Tree 構造 インデックスを試す 複合インデックス インデックスの誤解 結合キーや実行計画、ORDER / GROUP に ついては今回は話しません。また次の機会に話したいなぁ
4. MySQL を前提とした解説をしますが 基本的なことは他の RDB も同じような感じです。
5. インデックスとは
6. SELECT * FROM ユーザテーブル WHERE ニックネーム = "zaru" 例えば、こんな SQL を実行したい
7. ニックネーム hoge piyo mu foo bar … baz zaru 大量のデータから zaru を探したい データベース君 インデックスがないと、順に見ていく 保存されているデー タ順に見て該当の データを探す
8. ニックネーム hoge piyo mu foo bar … baz zaru データ量が多いと探すのが大変 線形探索で計算量は O(n) 最悪、最大値まで繰り返す LIMIT が指定されていない場合や ユニーク制約がない場合は全データを見にいく
9. ここでインデックス
10. ニックネーム bar baz foo hoge mu … piyo zaru 電話帳のように予め並べて索引を作る b f h z アルファベット順に並べておい て、どこに何があるかを分かる ようにすると、見つけやすい ざっくりいうと、 これがインデックス 索引
11. B-Tree 構造
12. ノード ノード ノード 索引を効率的にする B-Tree 構造 foo mu bar baz fuga piyo zaru go hoge zaru の場所は？ 各ノードがルールを持った階層構造を作る。ちなみに B-Tree の B は Binary (二 分) ではなく Balanced (バランス) 。ノードの階層が全ての要素で同じになり、計 算量が変わらないのが特徴。計算量は O(log n) になる。
13. https://www.cs.usfca.edu/~galles/visualization/BTree.html
14. け、計算量？！ データ量が増えると計算し ないといけない量も同じよ うに増える データ量が増えても計算量 はそんなに増えない
15. ルートノード ブランチノード リーフノード RDB でよく使われる B+Tree B-Tree を改良したもの。リーフノード同士が双方向で連結している。ノード間の 移動が高速にでき範囲検索などに強い。B-Tree と違い、リーフノードに全ての データが存在している（B-Tree は1階層分しか存在しない） baz mu bar baz foo piyo zaru hoge hoge foo go mu piyo 横の移動が しやすい！
16. https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
17. リーフノードは実データへのポインタ リーフノードが該当するデータの実際の場所（RowID ) をポイン タのように持っている。これをヒントに実データを取得しにい く。 なお SQL によってはインデックスのみでレスポンスできるものが あり、その場合は実データにアクセスをしなくて良いので高速に なる（SQL に登場するカラムが全てインデクスに含まれているパ ターン） ルートノード ブランチノード リーフノード baz mu bar baz foo piyo zaru hoge hoge foo go mu piyo RowID RowID RowID RowID RowID RowID RowID RowID RowID Name Email 1234 bar bar@example.com
18. インデックスを試す
19. CREATE TABLE items ( item_id INT NOT NULL ); item_id 1 2 … 50000 … 99999 100000 SQL SELECT * FROM items WHERE item_id = 50000; SQL 50000 のデータを探すクエリ サンプルのテーブルとクエリ
20. インデックスなしパターン 意外と 速い？ 速くは ない インデックスなしの場合、テーブルフルスキャンと言っ て、全てのデータを読み込んで必要なデータを抜き出し ている。データ量が増えると、かかる時間も増える。 小さいテーブルでアクセス頻度が少ないなら問題になり にくいが、システムが成長すると突然性能が劣化する。
21. インデックスありパターン 速くなった！ ALTER TABLE items ADD INDEX idx_item_id (item_id); SQL item_id 列のインデックスを作成するクエリ。 これによって item_id の索引が作成され、探したいデータへの アクセスが効率的にできる。
22. スキャンしている レコード量を確認 SHOW SESSION STATUS LIKE 'Handler_read%_next' SQL SQL が実際にどれくらいのレコード量をスキャンしているのかを確認 することができる。実際に、1行しか出力しないような SQL でも、裏 では大量のレコードを読んでいるとパフォーマンスが悪い。
23. インデックスを使わないと 全レコードを読み込んでいる SQL 実行前と実行後の 読み込んだレコード量の比較 検証 SQL use index() はインデックスを使わないようにする指示 インデックスなしパターン
24. インデックスを使うと探した いレコードのみを読み込むの で済んでいる 検証 SQL force index() は指定インデックスを強制利用させる 分かりやすさのために使ってるだけで意味はない インデックスありパターン
25. 複合インデックス
26. 複合インデックス 複数のカラムを合わせた索引 そのまんまだ 例えば 商品 No と メーカー名 を組み合わせて並び順を作れる
27. 単体インデックス B D A B D E C C RowID RowID RowID RowID RowID メーカー名 のみのインデックス例 ALTER TABLE テーブル ADD INDEX インデックス名 (メーカー名) SQL
28. 複合インデックス メーカー名 と 商品 No の複合インデックス例 ALTER TABLE テーブル ADD INDEX インデックス名 (メーカー名, 商品No) SQL B, 02 D, 02 A, 03 B, 02 D, 02 E, 01 C, 01 C, 01 RowID RowID RowID RowID RowID 複合インデックスは、指定したカラムの値を配列の ような組み合わせ (タプル) で持っているようなもの
29. 単体インデックスと 　　複合インデックスの相異 複合インデックス作成時に指定したカラムの並び順で作成される。この場合、メー カー名→商品 No の順にインデックスを作成した。そのため、最初に指定したメー カー名の単体インデックスと、同じような構造になっている。 複合インデックスが嬉しくなるのは、第一カラムが同一のレコード、この例だと同じ メーカー名の色んな商品データが増えてから。 B D A B D E C C RowID RowID RowID RowID RowID B, 02 D, 02 A, 03 B, 02 D, 02 E, 01 C, 01 C, 01 RowID RowID RowID RowID RowID
30. この構造理解は めちゃ重要！ 意味のないインデックスを作らないように するために頭でイメージできるようにする
31. 複合インデックスが使える例 SELECT * FROM 商品 WHERE メーカー名 = "A" AND 商品No = "03" SQL メーカー名と商品 No を指定しているので使える SELECT * FROM 商品 WHERE メーカー名 = "A" SQL メーカー名だけでも、複合インデックスの 最初のカラムなので単体インデックスの代わりに使える
32. 複合インデックスが使えない例 SELECT * FROM 商品 WHERE 商品No = "03" SQL 複合インデックスの最初に指定したカラムがないと インデックスは使えない B, 02 D, 02 A, 03 B, 02 D, 02 E, 01 C, 01 C, 01 RowID RowID RowID RowID RowID メーカ名の A から E まで順に並べている ので、全体から商品 No を効率的に探す ことができない（結局、全データスキャ ンになる） 03 どこに あるの？
33. インデックスの誤解
34. NULL はインデックスが使えない WHERE name IS NULL , IS NOT NULL は普通にインデックスが使える NOT IN はインデックスが使えない WHERE id NOT IN ( 1, 5, 10 ) は普通にインデックスが使える 否定形はインデックスが使えない WHERE id != 100 などの否定でもインデックスは使える ただ使ってもパフォーマンスが上がらないケースはある WHERE の条件順は、インデックス指定順と同じにする必要がある オプティマイザが自動で最適化してくれるので気にしなくて良い WHERE name LIKE '%ru%' 誤解 誤解 誤解 誤解
35. 良いインデックス 悪いインデックス どのカラムをインデックスの対象にするかという観点で、 選択性 ( cardinality ) が重要になる。 ・選択性が高い = 種類が多い ・選択性が低い = 種類が少ない ただ、種類が少ない = ダメなのではなく実データの分布も考慮した方が良い 性別 カテゴリ フラグ 低い例 更新日時 誕生日 ユニーク ID 高い例 あんまり絞り 込めないなぁ めっちゃ 絞り込める！
36. EXPLAIN を読む SQL がインデックスを使うのかを事前に実行計画を確認することができる。 あくまで計画なので本当にこの通りになるわけではないのに注意。 EXPLAIN SELECT * FROM テーブル; SQL 使うであろうインデックス名 スキャンするレコード量の見積もり パフォーマンスに関する情報色々
37. ありがとうございました！ 次回は... 未定！ 質問感想など呟いていただけると嬉しいです！ - ハッシュタグ #mu_zaru - ツイッター情報 @mu_vpoe , @zaru チャンネル登録 Good ボタン お願いします！ ムーザルちゃんねる