$30 off During Our Annual Pro Sale. View Details »

RDB勉強

tom--bo
June 17, 2016
300

 RDB勉強

tom--bo

June 17, 2016
Tweet

Transcript

  1. RDBの勉強 part  1 ~∼  達⼈人に学ぶDB設計徹底指南書 ~∼ ~∼  SQLパフォーマンス詳解 ~∼ id:

     tom_̲_̲bo
  2. ֓ཁ • RDBの全容では無い • この勉強会⽤用(かなり限定的) ࡭ͷཁ໿ 参考

  3. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  4. ςʔϒϧͱ͸ • 2次元表 !=  テーブル • 共通点をもったレコードの集合 • テーブル名は複数形か複数名詞であらわされるべき •

    重複⾏行は存在できない ςʔϒϧ ࣍ݩද
  5. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  6. 42-࣮ߦ·ͰͷྲྀΕ ਤͰͬ͞ͱ֬ೝ

  7. 42-࣮ߦ·ͰͷྲྀΕ • Parser • Optimizer • Catalog  Manager • Plan

     Evaluation େࡶ೺ʹ MySQLだと
  8. 42-࣮ߦ·ͰͷྲྀΕ • パーサ(parser) • 構⽂文解析(パース)する • エラーがあれば弾く • オプティマイザ(optimizer) •

    実⾏行計画の作成 • プラン⽣生成・コスト評価 • カタログマネージャに問い合わせ、インデックス・分散・選択度など を参照する 1BSTFS͔Β0QUJNJ[FS
  9. 42-࣮ߦ·ͰͷྲྀΕ • カタログマネージャ(catalog   manager) • 主に以下の情報を保持している • 各テーブルのレコード数 •

    各テーブルの列数と列のサイズ • 列値のカーディナリティ(ユニークな値の個数) • 列値のヒストグラム(値の分布) • 列内のNULLの数 • インデックス情報 • プラン評価(plan  evaluation) • 実⾏行計画を評価する • 実⾏行計画は⼈人間が読める状態なままで、実⾏行コードになっていないの で、変換する $BUBMPHNBOBHFSͱ1MBO&WBMVBUJPO
  10. 42-࣮ߦ·ͰͷྲྀΕ ࠶ܝ

  11. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  12. Ωʔͱ੍໿ • 主キー • primary  key(PK) • 1⾏行のレコードを⼀一意に特定できるカラム • テーブルにおいて必ず1つ存在していなければならず、かつ1つしか

    存在しない(複数カラムからなっていても良い) • NULLを含まない • 重複する値を含まない • 候補キー • 主キーとして利⽤用可能なキーの候補 • スーパーキー • 主キーに⾮非キー列を付加した場合のキーの組み合わせ ओΩʔɾީิΩʔɾεʔύʔΩʔ
  13. Ωʔͱ੍໿ • 外部キー • 外部のテーブルのカラムを参照するカラム • テーブルに対して制約をつけるために作成する(ex)参照整合性制約 • 制約 •

    NOT  NULL制約 • ⼀一意制約 • CHECK制約 ֎෦Ωʔͱ੍໿
  14. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  15. ਖ਼نԽ • 第1正規形 • 1つのセル(1つの列と⾏行で特定できる項⽬目)には1つの値(スカラ値)しか 含まない • 第2正規形 • 部分関数従属を排除する

    • 第1正規形を満たす • 部分案数従属を排除する(完全関数従属の状態にする) • 部分関数従属:  主キーの⼀一部の列に対して従属する列がある場合 • 完全関数従属:  主キーを構成する全ての列に従属性がある場合 • 異なるレベルの実体(エンティティ)をテーブルとしても分離できる ୈҰਖ਼نܗɾୈೋਖ਼نܗ
  16. ਖ਼نԽ • 第3正規形 • 第2正規形を満たす • 推移的関数従属を排除する • 推移的関数従属:  関数従属が連結したもの

    • ボイスコット正規形(BCNF) • 第3正規形を満たす • ⾮非キーからキーへの関数従属をなくした状態 • キーを分解せずにBCNFにしよとすると、不可逆な正規化をしてしまうことが多 いので、気をつける ୈࡾਖ਼نܗɾϘΠείοτਖ਼نܗ
  17. ਖ਼نԽ • 第4正規形 • 第3正規形を満たす • 第4,5正規形は関連エンティティに関するもの • 多値従属性が複数存在するテーブルを分解する •

    多値従属性:  キーに対して、カラムの要素の集合が決定する • 多値従属性はキーと集合の関係におけるもので、関数従属性のように、キーと ⾮非キーの間の関係ではない • 第5正規形 • 第4正規形を満たす • 関連ごとにエンティティを1つ作る ୈ࢛ਖ਼نܗɾୈޒਖ਼نܗ
  18. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  19. ΠϯσοΫε • B(+)tree  Index • Balanced  tree  (Not  Binary  Tree)

    • Btreeではインデックスの途中でもレコードの情報を持つ • B+treeではリーフノードでしかレコードの情報を持たず、リーフノー ドはlinkListで結合されている • B+tree仕組み • SQLパフォーマンス詳解の図 ߏ଄ #USFFJOEFY
  20. ΠϯσοΫε • Hash  Index • 指定したカラムでハッシュ表を作る • 完全⼀一致検索にしか使えない • MySQLに純粋なHash

     Indexはない • Binary  Index • 複数カラムでbit列を作ってハッシュ化 • MySQLにはない • Functional  based  Index  (関数Index) • 関数で変換した後の値でIndexを作成する • MySQLでは5.7以上で対応 • ユーザ定義関数でインデックスを作成するにはUDFが確定的である必 要がある(PostgreSQL,Oracleの場合) • 確定的:  同じパラメータに対して同じ結果が常に帰る ଞ༗໊Ͳ͜Ζ
  21. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  22. ΠϯσοΫεͷ૸ࠪॱং • INDEX  UNIQUE  SCAN • ⽊木の⾛走査のみ • INDEX  RANGE

    SCAN • ⽊木の⾛走査とリーフノードチェーンを辿った検索 • 検索条件に対して複数のエントリが存在する場合使われる • TABLE  ACCESS  BY  INDEX  ROWID • インデックススキャンの結果を元にテーブルからデータを読みだす • TABLE  ACCESS  FULL • 全テーブルのデータを読み込む • マルチブロックリード(⼀一括でブロックを読み込む)を使うので、頻繁 にストレージへのアクセスをしなくて済むテーブルの⼤大半のデータを 取得するときはこちらのほうが効率的 ༗໊Ͳ͜Ζ
  23. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  24. 8)&3&۟ͷύϑΥʔϚϯε • <,  >,  BETWEEN,  LIKEを使った検索 • INDEX  RANGE  SCANがどの範囲になるかを考える

    • 選択性の⾼高いカラムを左側にすれば良いというわけではない • 次の2ページ • 述語情報まで⾒見ることでプランの様⼦子をより詳細に⾒見ることができる • アクセス述語:インデックスの⾛走査の始めと終わりをいめる,  インデックスのス キャン範囲を決める • フィルタ述語:リーフノードの⾛走査時にのみ適⽤用される,  スキャン範囲の削減に は使われない ൣғݕࡧ
  25. 8)&3&۟ͷύϑΥʔϚϯε ൣғݕࡧ

  26. 8)&3&۟ͷύϑΥʔϚϯε ൣғݕࡧ

  27. 8)&3&۟ͷύϑΥʔϚϯε • インデックスの⾛走査においてLIKEが有効なのは、最初のワイルドカー ドまで • 残りはフィルタ述語になる • ワイルドカードから始まるLIKE句は避けるようにする -*,&ϑΟϧλ

  28. 8)&3&۟ͷύϑΥʔϚϯε • ⽇日付型 • 絡むと同じ型で範囲指定をするようにする。でないとINDEXが効かない • 秒数まで持つデータ型に対して、⽉月・⽇日だけで絞り込むときは、00:00秒までを 加えて型を合わせる • 数値⽂文字列

    • 数値⽂文字列:テキストの列に保存される数字⽂文字列 • 暗黙的な型変換が⾏行われるとインデックスが使えないことがあるので、条件⽂文 に指定する値の型をカラムに合わせる • 列の連結 • 関数インデックスを作成することで対処 • 数式 • 極⼒力使わないようにする(絶対値で指定) • 左辺に数式をまとめて、関数インデックスを作って対処 ॲཧ͠ʹ͍͘৚݅
  29. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  30. ݁߹ॲཧ • ⼊入れ⼦子ループ(Nested-‑loop  join) • 外部(駆動)テーブルから結果を取り出し、2つめのクエリがその結 果のそれぞれの⾏行に対して、別のテーブルの対応するデータを取り出 す • MySQLにはこれとBKA

     join(⼊入れ⼦子ループの改良のようなもの)しかな い • ハッシュ結合(Hash  join) • 結合の⽚片側のクエリの候補をハッシュテーブルに読み込むことで結合 の⾼高速化 • MySQLにはない • MariaDBは5.3(?)からある様⼦子 • マージ結合((Sort)  Merge  join) • 結合する両⽅方のテーブルのカラムをソートし、マージ結合する • すでに並び替えされたカラムではHash  joinよりも有利 ݁߹ॲཧͷλΠϓ
  31. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  32. ΠϯσοΫεͷԠ༻ • インデックスのクラスタ化係数:インデックスの2つの連続し たエントリが同じテーブルブロックにある可能性を表す • オプティマイザはTABLE  ACCESS  BY  INDEX  ROWID処理のコスト値

    の計算にこれを考慮する • クラスタ化係数の良いインデックスは⾏行を取り出すときに少数のブ ロックのみを読めばよくしてくれる • SQLパフォーマンス詳解のp113の例 • フィルタ述語を意図的にINDEXに含めることでクラスタ化し、アクセ スを⾼高速化した • ただ、このためにINDEXを作るのは更新コストが⼤大きくなるので、や るべきではなく、すでにあるINDEXを使えるようにフィルタ述語を利 ⽤用する ΫϥελԽ܎਺
  33. ΠϯσοΫεͷԠ༻ • select,  where句で指定されるカラムを全てインデックスに含める。 • テーブルアクセスが発⽣生しないので、⾼高速 • カバリングインデックスの効果はクラスタ化係数と選択する⾏行数によ る。 •

    ツリーの⾛走査にも数ブロックを読み込む必要があるので、選択する⾏行 数が少ない(1桁とか)と、テーブルアクセスを削減できず意味がない ΧόϦϯάΠϯσοΫε
  34. ΠϯσοΫεͷԠ༻ • リーフノードに参照先の⾏行の主キー列を含んだインデックス • ⾮非クラスタ化インデックス • セカンダリインデックスでアクセスするとインデックスのルックアッ プが2回必要になる ηΧϯμϦΠϯσοΫε

  35. ΠϯσοΫεͷԠ༻ • ⼀一時的なデータの保存先としてインデックスを指定できるものがある、 これをOracleでは索引構成表という • ほかのDBではクラスタ化インデックスという • 索引構成表はヒープテーブルをもたないBツリーインデックス(?) • SQLパフォーマンス詳解

    P122 • セカンダリインデックスはPIのキー値を取得してPIにアクセスしに⾏行 くので、⾮非効率 ࡧҾߏ੒ද
  36. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  37. ιʔτͱάϧʔϐϯά • order  by句がインデックスによる順序付けと⼀一致している場合、 DBは明⽰示的なソート処理を省略できる • where句とorder  by句のカラムをマルチカラムインデックスに 含んでいればインデックスを利⽤用したソートを利⽤用できる •

    パイプライン処理を期待してもインデックスを利⽤用せず明⽰示的 なソートを⾏行ってしまう場合、 • 明⽰示的なソートのほうがコストが低い • スキャンされるインデックスの範囲のインデックス順序がorder  by句 に対応していない • があり、2つめの理由に当てはまるか確認するためには全ての カラムを含むインデックスを作成してみる */%&9Λ࢖ͬͨ03%&3 #:
  38. ιʔτͱάϧʔϐϯά • group  byには2つのアルゴリズムがある • ハッシュアルゴリズム • ソート・グループアルゴリズム • MySQL5.6ではハッシュアルゴリズムは使わない

    • ソート・グループアルゴリズムを使うとき、インデックスのソート済 みデータを利⽤用できる ΠϯσοΫεΛ࢖ͬͨ(3061 #:
  39. ໨࣍ • テーブルとは • SQL実⾏行までの流れ • 実⾏行計画を決める要素 • キーと制約 •

    正規化 • インデックスの構造 • インデックスの⾛走査⼿手順 • WHERE句のパフォーマンス • 結合処理 • インデックスの応⽤用 • ソートとグルーピング • 部分結果
  40. ෦෼݁Ռ • DBは部分結果のみを取得することを事前に知っている場合に のみクエリを最適化できる • この条件はSQL:2008でようやく追加された⽐比較的新しい規約 • LIMIT句などにより部分結果のみを取得することがわかってい ると、パイプライン化された最初のN⾏行のみを取得して中断す るようにできる。

    ࠷ॳͷ/ߦͷΈΛऔಘ
  41. ෦෼݁Ռ • limit  N  offset  Mで、M件⽬目以降のN件を取得できる • ただしMは線形に探索するのでMが⼤大きくなると⾮非常に遅くな る。 •

    解決法としてシーク法を使う • シーク法:前の結果の最後の要素のidよりあとのN件を検索するよう にする⽅方法 • ページネーションにWindow関数を使う⽅方法もあるが、 PostgreSQLでは⾮非効率だったり、MySQLではWindow関数⾃自 体がない ࣍ϖʔδͷऔಘ