DBアタマアカデミー

第4回 クエリ評価エンジンと実行計画―“シェフおまかせ”はいつも美味しいのか(1)

この記事を読むのに必要な時間:およそ 3 分

オプティマイザとうまく付き合う

以上が,DBMSがクエリを受け取ってから実際のデータアクセスを行うまでの流れです。オプティマイザ内部の処理については,このエンジンそのものを実装するエンジニア以外には関係しないため,本稿では踏み込みません。むしろ,データベースのユーザとしては,このオプティマイザをうまく使ってやることのほうが大事です。というのも,オプティマイザは放っておけば万事よろしくやってくれるほど万能ではないからです。特に,カタログマネージャが管理する統計情報については,DBエンジニアは常に神経を使う必要があります。

適切なプランが選ばれない場合

プラン選択をオプティマイザ任せにしている場合,現実には最適なプランが選ばれないことが多々あります。代表的なパターンはいくつかありますが,中でも最も初歩的かつありがちなのが,統計情報が間違っているケースです。

実装によって差はありますが,カタログに含まれている統計情報は次のようなものです。

  • 各テーブルのレコード数
  • 各テーブルの列数と列のサイズ
  • 列値のカーディナリティ(値の個数)
  • 列値のデータ分布(どの値がいくつあるかのヒストグラム)
  • 列内のNULLの数
  • インデックス情報

これらの情報を入力として,オプティマイザはプランを作ります。問題が起こるのは,このカタログ情報がテーブルやインデックスの実体と一致しない場合です。テーブルに対してデータの挿入/更新/削除が行われたのに,カタログ情報が最新化されていないと,オプティマイザは古い情報をもとに実行計画を作ろうとします。手元にそれしか情報がないのだから当たり前です注3⁠。

たとえば極端な例ですが,テーブルを作ったばかりのレコード0件の状態でカタログ情報が保存され,その後レコードを1億件ロードしたのにカタログ情報を更新しなかった場合,オプティマイザはデータ0件を前提してプラン生成をしようとします。これでは最適なプランにはなり得ません。Garbage In,Garbage Out⁠ゴミのような入力からはゴミのような結果しか生まれない)というやつです。それでクエリが遅かったからといって,オプティマイザのせいにするのは酷です。

注3)
統計情報が一度も収集されていなかったり,失効していたりする場合,DBMSによっては,クエリ実行時に統計情報をリアルタイム収集して実行計画を作ることがあります(JIT:Just In Time処理⁠⁠。Oracleなどがこの機能を持ちます。しかし,統計情報収集はまともにやると時間のかかる処理ですので,JITで集められる情報には自ずと限度があり,あらかじめ収集した統計情報に比べれば精度は落ちます。

適切なプランが作成されるようにするには

このため,テーブルのデータが大きく更新されたら,カタログの統計情報もセットで更新することは,DBエンジニアの間では不文律のルールになっています。マニュアルで更新するだけでなく,夜間バッチに組み込む運用も多いですし,Oracleのようにデフォルト設定で定期的に統計情報更新のジョブが動くDBMSもあります。

この統計情報の更新は,対象のテーブルやインデックスのサイズと数によっては数十分~数時間を要する場合もあり,実行コストの高い面倒な作業ではありますが,DBMSが適切なプランを選択するための必要条件ですので,手を抜かずに最新化する必要があります。

データベースの鉄則 1
統計情報は必ず最新化する

代表的なDBMSの統計情報更新コマンドを一覧として掲載します表1⁠。ここに掲載するのは基本構文のみで,オプションのパラメータによって,テーブル単位ではなくスキーマ全体で取得したり,サンプリングレートを指定したり,テーブルに付与されているインデックスの統計情報もあわせて取得したりなど,さまざまな制御が可能です。詳細は各DBMSのマニュアルを参照してください。

表1 代表的なDBMSの統計情報更新コマンド

DBMS統計情報収集コマンド※1
Oracleexec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => <スキーマ名>, TABNAME =><テーブル名>);
SQL ServerUPDATE STATISTICS<テーブル名>
DB2RUNSTATS ON TABLE <スキーマ名>.<テーブル名>;
PostgreSQLANALYZE <スキーマ名>.<テーブル名>;
MySQLANALYZE TABLE <スキーマ名>.<テーブル名>;

※1 複数行になっているコマンドも実際は1行

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『SQL ゼロからはじめるデータベース操作』(翔泳社,2010)『達人に学ぶ SQL徹底指南書』(翔泳社,2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社,2007)

DBアタマアカデミー:サポートページ