「SQL実践入門」を読んだ

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

目的、モチベーション

• パフォーマンス改善する際に考える材料として、実行計画周りの原理を理解したい

全体の感想

実行計画周りは思ってたより記載されていなかったです。
WINDOW関数などについても言及されており、効率的なSQLを学びたい初心者と中級者の間ぐらいの方にはオススメです。

メモ

効率的なSQLの書き方もたくさん記載されていましたが、あくまでも原理を理解することが今回は目的だったため、割愛してます。

第1章　DBMSのアーキテクチャ──この世にただ飯はあるか

1.1　DBMSのアーキテクチャ概要

Database Management Systemsより

• クエリ評価エンジン
  • SQLを解釈して実行計画を行う
• バッファマネージャ
  • メモリ領域の使い方を管理する
• ディスク容量マネージャ
  • 永続的にデータを保存できるように管理し、読み込みや書き込みを制御する
• トランザクションマネージャとロックマネージャ
• リカバリマネージャ
  • バックアップやリカバリの制御

1.2　DBMSとバッファ

I/Oアクセスを避けて、いかにメモリ上で処理を行うかが、パフォーマンス改善においてはキーになる。

1.3　DBMSと実行計画

データへのアクセス方法はどう決まるのか

1. パーサ（parser）
   • SQL文を要素に分解してDBMSが処理しやすい形式に変換
2. オプティマイザ（optimizer）
   • インデックス、データの分散や偏り度合い、DBMSの内部パラメータなどの条件から実行計画を作成して、それらのコストを計算して、実行計画を決定する
3. カタログマネージャ（catalog manager）
   • テーブルやインデックスの統計情報をオプティマイザに提供する
   • 遅延が発生すると適切な計画が練れない一方で更新コストも高いため、適切な設定が重要
4. プラン評価（plan evaluation）

第3章　SQLにおける条件分岐──文から式へ

3.1　UNIONを使った冗長な表現

UNIONを使うのは、条件分岐という手続き型の発想から脱していないことに原因があることが多い。
SELECT中のCASEで分岐したほうがテーブルのスキャン回数が減るのでパフォーマンスがよい。

3.3　それでもUNIONが必要なのです

CASEによってINDEXスキャンができなくなるときなどは、UNIONを使ったほうがパフォーマンスが良いケースがある。

第6章　結合──結合を制する者はSQLを制す

6.2　結合のアルゴリズムとパフォーマンス

Nested Loops
駆動表とするテーブルを1行ずるループしながら、もう一方の内部表となるテーブルをスキャンして結合条件に合致するものを検索する方法。
アクセスされる行数は、レコード数の積になる。

内部表の結合キーの列にインデックスが存在すると、ループがスキップできるようになりパフォーマンスが改善される。
このとき、内部表が大きい方がスキップされる行数が多くなり、よりパフォーマンスが良くなることが期待できる。

Hash
小さいテーブルからメモリ上でハッシュテーブルをつくり、もう一つの大きいテーブルをスキャンしてハッシュ値が存在するか調べる。
メモリ内に収まらないとTEMP落ちが発生してパフォーマンスが悪化するので注意。

Sort Merge
対象テーブルをソートして、一致する結合キーがあれば結果に含める。
そのため、片方のテーブルのみハッシュテーブルをつくるHashよりもメモリを消費する。 INDEXなどでソートしなくて良い場合には時間とリソースを節約できるため有効だが、基本的には上述の他のアルゴリズムが優先となる。

6.3　結合が遅いなと感じたら

ケース別の最適な結合アルゴリズム

種類	メリット	デメリット
Nested Loops	「小さな駆動表」+「内部表のインデックス」の条件下で高速 メモリやディスクの消費が少なくOLTPに適している 非等値結合でも使用できる	大規模テーブル同士の結合に不向き 内部表のインデックスが使えなかったり、内部表の選択率が高いと低速
Hash	大規模テーブル同士の結合に適している	メモリ消費量が多くOLTPに不向き メモリ不足の場合はTEMP落ちが発生する 等値結合のみで使用可能
Sort Merge	大規模テーブル同士の結合に適している 非等値結合でも使用できる	メモリ消費量が多くOLTPに不向き メモリ不足の場合はTEMP落ちが発生する データがソート済みでなければ効率的でない

そもそも実行計画の制御は可能なのか？
MySQLは結合アルゴリズムがNested Loops系しかない。

揺れるよ揺れる，実行計画は揺れるよ
データ量が変更されることで実行計画が変化され、急にパフォーマンスが悪化することがある。
WINDOW関数や非正規化などで、結合をなるべく避ける。

第10章　インデックスを使いこなす──秀才の弱点

10.4　インデックスが使用できない場合どう対処するか

インデックスオンリースキャンによる対処
通常ならテーブルのフルスキャンが発生するようなケースにおいても、選択した列を網羅するINDEXを貼ることで、テーブルスキャンを避けることができる手法。

CREATE INDEX IdAndName ON users (id, name);
SELECT id, name from users;

次のアクション

思ったより深ぼれなかったので、SQLパフォーマンス詳解あたりを読んでみようと思います。

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)