Effective SQL

社内読書会

第 7 章　メタデータの取得と分析

一般社団法人全日本ピアノ指導者協会

野口啓之

補足　メタデータとは

メタデータ

端的に言うと：

そもそものデータベースの構造データのこと

 

  • Databases
  • Tables
  • Columns
  • Index
  • Function
  • Procedure
  • Triggers
  • Events
  • Engines ... e.t.c.

http://www.atmarkit.co.jp/ait/articles/1703/01/news204.html

メタ

「高次な－」「超－」「－間の」「－を含んだ」「ーを入れた」「－の後ろの」等の意味の接頭語。ギリシア語から。

wikipedia

https://ja.wikipedia.org/wiki/%E3%83%A1%E3%82%BF

『ドラえもん』より

補足　実行プランとは

実行プラン

端的に言うと：

クエリの結果を得るために、そのクエリをどのように処理するのかを表したもの

https://blogs.msdn.microsoft.com/jpsql/2011/09/06/1/

例えば、select c1, c2 from tabA where c3=1 というクエリの結果を得るために、tabA テーブルの非クラスタ化インデックス ind_tabA を検索して行を絞り込み、その絞り込んだ行をクラスタ化インデックスから読み取り、読み取った行の中から c1, c2 の 2 列の値を取り出す、といったような、クエリ結果を得るためのプロセス

項目44　クエリアナライザを使用する

クエリアナライザとは

MySQL Query Analyzer を例に……

https://www.mysql.com/jp/products/enterprise/query.html

  • MySQL パフォーマンスの向上
    • 負荷の大きいクエリーを迅速に特定
    • クエリー・アクティビティを視覚化
    • 特定のクエリー問題を確認するためのフィルタリング
  • クエリー実行と MySQL サーバー・アクティビティとの相関関係を視覚化
  • 詳細クエリー情報へのドリルダウン
    • クエリー・アナライザ・テーブル
    • クエリー・レスポンス時間インデックス（QRTi）
    • レスポンス統計
    • EXPLAIN クエリー

※商用版のMySQL Edition よりご利用になれます。

IBM DB2

https://www.ibm.com/analytics/jp/ja/technology/db2/

  • EXPLAIN テーブルの存在が必須。
  • EXPLAIN キーワードを用いる。
  • 元々が有料なので、この部分でのサポートは無償で手厚い。
EXPLAIN PLAN FOR SELECT CustomerID, SUM(OrderTotal)
  FROM Orders
  GROUP BY CustomerID;

Microsoft Access

https://products.office.com/ja-jp/access

  • レジストリキーを書き換えなくてはならない……
  • 「残念ながら、実行プランを表示するためのツールは組み込まれていない」p.183

Microsoft SQL Server

https://www.microsoft.com/ja-jp/sql-server/

  • SQL Management Studio を活用する。
  • 一つの SELECT に対して二つの結果セットが生成されるようになる。
SET STATISTICS XML ON;

SET STATISTICS XML OFF;

MySQL

https://www.mysql.com/jp/

  • EXPLAIN キーワードを用いる。
  • DB2 と異なり事前準備は不要。
  • MySQL Workbench の Viual Explain を活用するのも良い。
EXPLAIN SELECT CustomerID, SUM(OrderTotal)
  FROM Orders
  GROUP BY CustomerID;

Oracle

https://www.oracle.com/jp/database/

  • EXPLAIN キーワードを用いる。
  • PLAN_TABLE が存在する。
  • ver. によって差異が大きいので詳しくはドキュメントを参照すること……
EXPLAIN PLAN FOR SELECT CustomerID, SUM(OrderTotal)
  FROM Orders
  GROUP BY CustomerID;

SELECT * FROM TABLE(dbms_xplan.display)

PostgreSQL

https://www.postgresql.org/

  • EXPLAIN キーワードを用いる。
  • オプション指定も可能。
    • ANALYZE, ​VERBOSE, COSTS, BUFFERS, TIMING, FORMAT
  • ​pgAdmin ツールを活用する。
EXPLAIN SELECT CustomerID, SUM(OrderTotal)
  FROM Orders
  GROUP BY CustomerID;

項目45　データベースのメタデータを取得する

もう一度メタデータ

  • 「データについてのデータ」
  • ほとんどが INFORMATION_SCHEMA から情報が取得される

INFORMATION SCHEMA

  • SQL 言語の公式規格
  • サポートされている
    • IBM DB2
    • Microsoft SQL Server
    • MySQL
    • PostgreSQL
  • サポートされていない
    • Microsoft Access
    • Oracle
  • ただしサポートされているものの中でも実装が一貫していない

INFORMATION SCHEMAの実装不一致

  • VIEW_COLUMN_USAGE
    • 存在する
      • SQL Server
      • PostgreSQL
    • 存在しない
      • MySQL
  • 「ドキュメントからもうかがえるように、 Microsoft はおそらく INFORMATION_SCHEMA を信頼していない」p.194
  • この現実を受け入れよ

テーブルとビューのリスト取得例

SELECT t.TABLE_NAME, t.TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES AS t
  WHERE t.TABLE_TYPE IN ('BASE TABLE', 'VIEW');
TABLE_NAME TABLE_TYPE
Categories BASE_TABLE
Countries BASE_TABLE
Styles BAE_TABLE
BeerStyles VIEW

制約のリスト取得例

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc;
CONSTRAINT_NAME TABLE_NAME CONSTRAINT_TYPE
Categories_PK Categories PRIMARY_KEY
Styles_PK Styles PRIMARY_KEY
Styles_FKOO Styles FOREIGN_KEY

項目46　実行プランの仕組みを理解する

前提

  • 「実行プランの解読に関するベンダーのドキュメントを読むこと」p.195

効率的なデータ検索のための設計

  • 思考実験 "図書館で本を探す時の人間行動"
    • 「コンピュータが実際に実行しなければならない手順は、人が同じタスクを実行するときと何ら変わらない」p.196
  • index の張り方による改善例
    • 目録が増えても書棚へ行かなくて済むという合理性
    • 逆に、使用されない index にも注目
  • ゾウとネズミの問題
    • 「データが均等に分散していないために、同じクエリに対して異なる最適化が必要になる」p.202
Made with Slides.com