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

Slide 1

Slide 1 text

YouTube Live (2020.09.24 Thur. 21:00~)

Slide 2

Slide 2 text

話す人現役のエンジニア二人赤貝が好きな CTO とデザイン勉強中のエンジニア @mu_vpoe 最近の仕事は ﬁgma で画面設計をつくることですムー 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 ボタンお願いします！ムーザルちゃんねる