u-ar’s blog

研究や技術について。もろもろ。

トップ > 読書メモ > 【読書メモ2】SQL実践入門―高速でわかりやすいクエリの書き方

【読書メモ2】SQL実践入門―高速でわかりやすいクエリの書き方

読書メモ データベース

ミック, 『SQL実践入門―高速でわかりやすいクエリの書き方』, 技術評論社　を読む。1に続きDB PRESSシリーズ。

概要

基本的には、SQLの内部構造、特に実行計画を観察したうえでトレードオフを考慮した最適なクエリの書き方を考えるという内容。ただし、データモデル設計の段階(戦略)がクエリを書く段階(戦術)より大事だということは再三注意喚起している。 単一プロセスのパフォーマンス向上にフォーカスしており、同時実行制御や並列化については触れられていない。

学び

標語

  • 「文単位から式単位へのパラダイムシフト」
    • プログラミングに親しむ人が陥りやすい文単位の思考で組み立てたSQL文は、スキャンや結合の回数が冗長になりやすい。1つの式で全てを記述する宣言的な思考を持てるとSQL的には有用
  • 「SQLは意図的にループを追放したので、そこのところで文句を付けられても困る」
    • 全てのユーザに使いやすくすることを目指した結果、ループを廃しブラックボックス化したのがSQLとのこと。ただし内部的には結局ループしている(そもそも機械語がそういう風にしか動かない、宣言的マイクロアーキテクチャなんてありえるのか？)。ループするような処理を行いたいとき、DBMSの内部実装に委ねて表面では宣言的に記述するのも、カーソルやらアプリケーション側やらでループするのもいちおうの長短を持っているが、パフォーマンスチューニングの余地などを考えると基本は宣言的記述を頼るべき
  • 「結合はSQLの性能問題の火薬庫」
    • もともと結合の計算コスト高いんじゃないのと思っていたが、別にDBMSが魔法を使っているわけもなく実際高かった。基本はO(N2)のオーダーなわけだし。なのでできるだけ使う回数を減らしたクエリを書きたい。
  • 「一度はSQLが追放した手続き型が、ウィンドウ関数という形で大々的に復活を遂げた」
    • ウィンドウ関数は行に順序をつける点で関係モデルに反しているが、その利用を通して記法を簡潔にしつつ結合の回数を減らすこともできるという実利を持つ
  • 「エンジニアの本当の仕事は、戦略の失敗を挽回する戦術を探すことではなく、正しい戦略を、トレードオフを考慮しながら選択すること」
    • 変更できないER図から目的を達するための煩雑なSQL文をひねり出すよりも、データモデルのレベルから先を見据えた設計を行うのが理想

知識

  • DBMSがメモリに保持するバッファにはデータキャッシュとログバッファの2種類がある
    • データキャッシュはデータの一部を保持するメモリ領域
    • ログバッファはINSERTやUPDATE等に伴う更新情報を溜める領域で、コミット時点でディスクに反映される
    • 基本的にはデータキャッシュの方がはるかに大きく、参照と更新では参照に比重が置かれているSQLの構想時点での思想が現れている。更新が重要なOLTPならデフォルト設定をいじってログバッファを拡張する選択肢もある
  • オプティマイザはカタログマネージャが収集するデータベースのメタ情報・統計情報を参照して実行計画を決定する
    • 各テーブルのレコード数
    • 各テーブルの列数と列のサイズ
    • 列値のカーディナリティ
    • 列値のヒストグラム
    • 列内にあるNULLの数
    • インデックス情報
  • GROUP BY 句はカットと集約という2つの操作を同時に行うものであり、そのカットに相当する機能のみを備えるのがウィンドウ関数
    • RANKやROW_NUMBERなど。PARTITION BY句に指定したキーでカットを行う
  • 結合を行うアルゴリズムには3つある。使われる頻度の多い順で、Nested LoopとHashとSort Merge(とクロス結合)。
    • クロス結合=直積。直積を取った後結合条件に合う行だけ取り出すのが最も単純だが、効率が悪いのでまず使われない
    • Nested Loop
      • 駆動表1行ごとに、結合キーが一致する行を探して内部表をループする。
      • 通常二重ループだが、・駆動表が比較的小さい・内部表の結合キーにインデックスがある　の2条件を満たしていると特に高速に動作する。ただし、内部表インデックスのヒット件数が多い場合は結局ループ回数が多くなる
    • Hash
      • 小さい方のテーブルの結合キーをハッシュに変換する。大きい方のテーブル1行ごとにハッシュ値をテーブルと比較して結合する。等値結合にのみ利用可能で、ハッシュ表を作るためメモリを多く消費する
      • Nested Loopsで適切な駆動表や内部表のインデックスが存在しない場合に相対的に有利になる
    • Sort Merge
      • 両方の表を結合キーでソートして比較する。
      • メモリ消費はHashよりも多いが、不等号による結合にも利用でき、片方のテーブルスキャンが終わった時点で結合が終わりになる

技術

  • 集計における条件分岐をUNIONを用いて書きたくなるが、これは条件の数だけテーブルフルスキャンを要する効率の悪い書き方

    • 代わりにSELECT句内でCASE句を使った場合分けを行えば、フルスキャンは1回で済む

  • 結合はアルゴリズムが複数あるために実行計画変動も起きやすい。これを防止するには結合を回避することが重要になる

    • ヒント句により実行計画を固定することも突発的な変動への対応方法にあるが、コストベースの制御を行うDBMSの手を離れてユーザ側で制御してしまうことは、その時点で最適な実行計画が後にそうでなくなっていくリスクを伴う。やるなら性能テストと一緒に。

  • サブクエリの問題点

    • サブクエリの計算コストが上乗せされる
    • データのIOコストがかかる
    • 最適化を受けられない(サブクエリにはテーブルと違ってメタ情報が乗らず、オプティマイザが解析できない)
      • 問題の分割には便利だが、性能リスクは常に考慮する必要がある
        • ウィンドウ関数による代替が可能なことがある
        • ただし、結合対象のレコード数をあらかじめ絞り込むことでサブクエリでもパフォーマンスを改善できる場合がある

総括

『理論から学ぶデータベース実践入門』より詳細で説得的。話題はより狭く、SQLの実行計画に基づくパフォーマンスの改善やインデックスの活用に終始しているが、それだけに内容が充実している。

本書で示唆されていたできるだけ結合を減らすSQLの書き方は、少々直感に反する部分があり、身に着けるためには実践が必要と感じる。サンプルコードを使いつつ自分で書いて覚えたい。