Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

mu_zaru
September 24, 2020

 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

mu_zaru

September 24, 2020
Tweet

More Decks by mu_zaru

Other Decks in Programming

Transcript

  1. 話す人 現役のエンジニア二人 赤貝が好きな CTO と デザイン勉強中のエンジニア @mu_vpoe 最近の仕事は figma で画

    面設計をつくることです ムー zaru @zaru CTO, Love 赤貝, JavaScript, Firebase, Web Components. Twitter フォロー お願いします!
  2. ニックネーム hoge piyo mu foo bar … baz zaru 大量のデータから

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

    線形探索で計算量は O(n) 最悪、最大値まで繰り返す LIMIT が指定されていない場合や ユニーク制約がない場合は全データを見にいく
  4. ニックネーム bar baz foo hoge mu … piyo zaru 電話帳のように予め並べて索引を作る

    b f h z アルファベット順に並べておい て、どこに何があるかを分かる ようにすると、見つけやすい ざっくりいうと、 これがインデックス 索引
  5. ノード ノード ノード 索引を効率的にする B-Tree 構造 foo mu bar baz

    fuga piyo zaru go hoge zaru の場所は? 各ノードがルールを持った階層構造を作る。ちなみに B-Tree の B は Binary (二 分) ではなく Balanced (バランス) 。ノードの階層が全ての要素で同じになり、計 算量が変わらないのが特徴。計算量は O(log n) になる。
  6. リーフノードは実データへのポインタ リーフノードが該当するデータの実際の場所(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]
  7. 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 のデータを探すクエリ サンプルのテーブルとクエリ
  8. インデックスありパターン 速くなった! ALTER TABLE items ADD INDEX idx_item_id (item_id); SQL

    item_id 列のインデックスを作成するクエリ。 これによって item_id の索引が作成され、探したいデータへの アクセスが効率的にできる。
  9. スキャンしている レコード量を確認 SHOW SESSION STATUS LIKE 'Handler_read%_next' SQL SQL が実際にどれくらいのレコード量をスキャンしているのかを確認

    することができる。実際に、1行しか出力しないような SQL でも、裏 では大量のレコードを読んでいるとパフォーマンスが悪い。
  10. 単体インデックス B D A B D E C C RowID

    RowID RowID RowID RowID メーカー名 のみのインデックス例 ALTER TABLE テーブル ADD INDEX インデックス名 (メーカー名) SQL
  11. 複合インデックス メーカー名 と 商品 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 複合インデックスは、指定したカラムの値を配列の ような組み合わせ (タプル) で持っているようなもの
  12. 複合インデックスが使える例 SELECT * FROM 商品 WHERE メーカー名 = "A" AND

    商品No = "03" SQL メーカー名と商品 No を指定しているので使える SELECT * FROM 商品 WHERE メーカー名 = "A" SQL メーカー名だけでも、複合インデックスの 最初のカラムなので単体インデックスの代わりに使える
  13. 複合インデックスが使えない例 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 どこに あるの?
  14. 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%' 誤解 誤解 誤解 誤解
  15. 良いインデックス 悪いインデックス どのカラムをインデックスの対象にするかという観点で、 選択性 ( cardinality ) が重要になる。 ・選択性が高い =

    種類が多い ・選択性が低い = 種類が少ない ただ、種類が少ない = ダメなのではなく実データの分布も考慮した方が良い 性別 カテゴリ フラグ 低い例 更新日時 誕生日 ユニーク ID 高い例 あんまり絞り 込めないなぁ めっちゃ 絞り込める!