Slide 1

Slide 1 text

YouTube Live (2020.09.24 Thur. 21:00~)

Slide 2

Slide 2 text

話す人 現役のエンジニア二人 赤貝が好きな CTO と デザイン勉強中のエンジニア @mu_vpoe 最近の仕事は figma で画 面設計をつくることです ムー zaru @zaru CTO, Love 赤貝, JavaScript, Firebase, Web Components. Twitter フォロー お願いします!

Slide 3

Slide 3 text

話すこと インデックスとは B-Tree 構造 インデックスを試す 複合インデックス インデックスの誤解 結合キーや実行計画、ORDER / GROUP に ついては今回は話しません。また次の機会に話したいなぁ

Slide 4

Slide 4 text

MySQL を前提とした解説をしますが 基本的なことは他の RDB も同じような感じです。

Slide 5

Slide 5 text

インデックスとは

Slide 6

Slide 6 text

SELECT * FROM ユーザテーブル WHERE ニックネーム = "zaru" 例えば、こんな SQL を実行したい

Slide 7

Slide 7 text

ニックネーム hoge piyo mu foo bar … baz zaru 大量のデータから zaru を探したい データベース君 インデックスがないと、順に見ていく 保存されているデー タ順に見て該当の データを探す

Slide 8

Slide 8 text

ニックネーム hoge piyo mu foo bar … baz zaru データ量が多いと探すのが大変 線形探索で計算量は O(n) 最悪、最大値まで繰り返す LIMIT が指定されていない場合や ユニーク制約がない場合は全データを見にいく

Slide 9

Slide 9 text

ここでインデックス

Slide 10

Slide 10 text

ニックネーム bar baz foo hoge mu … piyo zaru 電話帳のように予め並べて索引を作る b f h z アルファベット順に並べておい て、どこに何があるかを分かる ようにすると、見つけやすい ざっくりいうと、 これがインデックス 索引

Slide 11

Slide 11 text

B-Tree 構造

Slide 12

Slide 12 text

ノード ノード ノード 索引を効率的にする B-Tree 構造 foo mu bar baz fuga piyo zaru go hoge zaru の場所は? 各ノードがルールを持った階層構造を作る。ちなみに B-Tree の B は Binary (二 分) ではなく Balanced (バランス) 。ノードの階層が全ての要素で同じになり、計 算量が変わらないのが特徴。計算量は O(log n) になる。

Slide 13

Slide 13 text

https://www.cs.usfca.edu/~galles/visualization/BTree.html

Slide 14

Slide 14 text

け、計算量?! データ量が増えると計算し ないといけない量も同じよ うに増える データ量が増えても計算量 はそんなに増えない

Slide 15

Slide 15 text

ルートノード ブランチノード リーフノード RDB でよく使われる B+Tree B-Tree を改良したもの。リーフノード同士が双方向で連結している。ノード間の 移動が高速にでき範囲検索などに強い。B-Tree と違い、リーフノードに全ての データが存在している(B-Tree は1階層分しか存在しない) baz mu bar baz foo piyo zaru hoge hoge foo go mu piyo 横の移動が しやすい!

Slide 16

Slide 16 text

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

Slide 17

Slide 17 text

リーフノードは実データへのポインタ リーフノードが該当するデータの実際の場所(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 [email protected]

Slide 18

Slide 18 text

インデックスを試す

Slide 19

Slide 19 text

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 のデータを探すクエリ サンプルのテーブルとクエリ

Slide 20

Slide 20 text

インデックスなしパターン 意外と 速い? 速くは ない インデックスなしの場合、テーブルフルスキャンと言っ て、全てのデータを読み込んで必要なデータを抜き出し ている。データ量が増えると、かかる時間も増える。 小さいテーブルでアクセス頻度が少ないなら問題になり にくいが、システムが成長すると突然性能が劣化する。

Slide 21

Slide 21 text

インデックスありパターン 速くなった! ALTER TABLE items ADD INDEX idx_item_id (item_id); SQL item_id 列のインデックスを作成するクエリ。 これによって item_id の索引が作成され、探したいデータへの アクセスが効率的にできる。

Slide 22

Slide 22 text

スキャンしている レコード量を確認 SHOW SESSION STATUS LIKE 'Handler_read%_next' SQL SQL が実際にどれくらいのレコード量をスキャンしているのかを確認 することができる。実際に、1行しか出力しないような SQL でも、裏 では大量のレコードを読んでいるとパフォーマンスが悪い。

Slide 23

Slide 23 text

インデックスを使わないと 全レコードを読み込んでいる SQL 実行前と実行後の 読み込んだレコード量の比較 検証 SQL use index() はインデックスを使わないようにする指示 インデックスなしパターン

Slide 24

Slide 24 text

インデックスを使うと探した いレコードのみを読み込むの で済んでいる 検証 SQL force index() は指定インデックスを強制利用させる 分かりやすさのために使ってるだけで意味はない インデックスありパターン

Slide 25

Slide 25 text

複合インデックス

Slide 26

Slide 26 text

複合インデックス 複数のカラムを合わせた索引 そのまんまだ 例えば 商品 No と メーカー名 を組み合わせて並び順を作れる

Slide 27

Slide 27 text

単体インデックス B D A B D E C C RowID RowID RowID RowID RowID メーカー名 のみのインデックス例 ALTER TABLE テーブル ADD INDEX インデックス名 (メーカー名) SQL

Slide 28

Slide 28 text

複合インデックス メーカー名 と 商品 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 複合インデックスは、指定したカラムの値を配列の ような組み合わせ (タプル) で持っているようなもの

Slide 29

Slide 29 text

単体インデックスと   複合インデックスの相異 複合インデックス作成時に指定したカラムの並び順で作成される。この場合、メー カー名→商品 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

Slide 30

Slide 30 text

この構造理解は めちゃ重要! 意味のないインデックスを作らないように するために頭でイメージできるようにする

Slide 31

Slide 31 text

複合インデックスが使える例 SELECT * FROM 商品 WHERE メーカー名 = "A" AND 商品No = "03" SQL メーカー名と商品 No を指定しているので使える SELECT * FROM 商品 WHERE メーカー名 = "A" SQL メーカー名だけでも、複合インデックスの 最初のカラムなので単体インデックスの代わりに使える

Slide 32

Slide 32 text

複合インデックスが使えない例 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 どこに あるの?

Slide 33

Slide 33 text

インデックスの誤解

Slide 34

Slide 34 text

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%' 誤解 誤解 誤解 誤解

Slide 35

Slide 35 text

良いインデックス 悪いインデックス どのカラムをインデックスの対象にするかという観点で、 選択性 ( cardinality ) が重要になる。 ・選択性が高い = 種類が多い ・選択性が低い = 種類が少ない ただ、種類が少ない = ダメなのではなく実データの分布も考慮した方が良い 性別 カテゴリ フラグ 低い例 更新日時 誕生日 ユニーク ID 高い例 あんまり絞り 込めないなぁ めっちゃ 絞り込める!

Slide 36

Slide 36 text

EXPLAIN を読む SQL がインデックスを使うのかを事前に実行計画を確認することができる。 あくまで計画なので本当にこの通りになるわけではないのに注意。 EXPLAIN SELECT * FROM テーブル; SQL 使うであろうインデックス名 スキャンするレコード量の見積もり パフォーマンスに関する情報色々

Slide 37

Slide 37 text

ありがとうございました! 次回は... 未定! 質問感想など呟いていただけると嬉しいです! - ハッシュタグ #mu_zaru - ツイッター情報 @mu_vpoe , @zaru チャンネル登録 Good ボタン お願いします! ムーザルちゃんねる