データベーススペシャリスト 2011年 午後1 問03

　クレジットカード会社のC社では、カード決済の検査機能を強化することになった。C社で使用しているテーブルは大規模であり、情報システム部のFさんが関係データベース管理システム (RDBMS) の SQL文の性能測定を実施することになった。 　

〔RDBMS の主な仕様〕 　RDBMS の物理構造とアクセス経路に関する主な仕様は、次のとおりである。

1．テーブルの行は、データページに格納される。 異なるテーブルの行が、同じデータページに格納されることはない。 また、1 行が異なるデータページに格納されることはない。 2．索引には、ユニーク索引と非ユニーク索引がある。 ユニーク索引のキーは1行だけを指すのに対し、非ユニーク索引のキーは1行以上の行を指す。 3．索引には、クラスタ索引と非クラスタ索引がある。 キー値の順番と、キーが指す行の物理的な並び順が一致している場合をクラスタ索引と呼び、ランダムな場合を非クラスタ索引と呼ぶ。 4．アクセス経路は、RDBMS によって表探索又は索引探索に決められる。 表探索では索引を使わずに先頭データページから全行を探索し、索引探索では WHERE 句中の述語に適した索引によって絞り込んでからデータページ中の行を読み込む。 SQL 文の実行によって得られる行を、結果行と呼ぶ。 5．索引探索に使える述語は、次のとおりである。 ここで、比較演算子は =、<、>、<=、>= のいずれかである。
　(1) 列名 比較演算子 比較値
　(2) 列名 BETWEEN 比較値 AND 比較値 　

　RDBMS のデータ入出力処理とログ出力処理に関する仕様は、次のとおりである。

1．データ入出力処理とログ出力処理は、4,096 バイトのページ単位に行われる。

2．データ入出力処理において、磁気ディスク装置(以下、ディスクという)に対してデータページをランダムに入出力する場合、SQL 処理中の CPU 処理とデータ入出力処理は並行して行われない。この場合のデータ入出力処理を同期データ入出力処理と呼び、SQL 処理時間は次の式で近似できる。

　SQL処理時間 = CPU 処理時間 + 同期データ入出力処理時間

3．データ入出力処理において、ディスクに対してデータページを順次に入出力する場合、SQL 処理中の CPU 処理とデータ入出力処理は並行して行われる。この場合のデータ入出力処理を非同期データ入出力処理と呼び、SQL 処理時間は次の式で近似できる。ここで、関数 MAX は、引数のうち、大きい方の値を返す。

　SQL処理時間 = MAX (CPU処理時間、非同期データ入出力処理時間)

4．テーブルごとに確保されたデータバッファが、変更データページによって一杯になった場合、データバッファの内容がディスクに出力される。

5．UPDATE 文によって行を変更した場合、変更前後の行全体がシステムによる回復用ログとしてログページに書き込まれ、システムに1個存在するログバッファに蓄えられる。次の事象 A〜C のいずれかが発生したとき、ログ先行出力機能によって、ログページはデータページよりも先にディスクに出力される。 　事象A トランザクションが、コミットかロールバックを行った。 　事象B ログバッファが、ログページによって一杯になった。 　事象C データバッファが、変更データページによって一杯になった。

6．同じディスクに対して、同時に入出力を行うことはない。 また、データページが格納されているディスクとログページが格納されているディスクの間で競合は起きない。

7．データバッファとログバッファの大きさは、ともに2,000ページである。 　

〔測定用テーブルとその索引の設計 〕 　測定用テーブルとして、行長が異なる2個のテーブル、“TA” と “TB” を用意した。測定用テーブルについて、その大きさを表1に、列値の範囲と分布を表2に、索引の設計を表3に示す。

〔測定用 SQL文の仕様と構文〕 測定用 SQL文について、その仕様を表4に、構文を表5に示す。

〔SELECT文の処理時間の測定〕 SELECT 文の処理時間の一部を表6に示す。 測定では、索引ページはすべてバッファヒットさせ,SELECT文の結果行は外部ファイルに出力していない。

　Fさんは、表6のSELECT文の処理時間について次のように分析した。

分析A 　SQL1AとSQL2A のデータ入出力処理は、(f) データ入出力処理である。 データ入力処理時間は、(g)秒である。 全データページを順次にデータバッファに入力するのに必要な CPU 処理時間は 10 秒であるから、全行をデータバッファからプログラム内に移動するのに必要な CPU 処理時間は、(h)秒である。SQL1A に述語 C4<:hv を指定する WHERE 句を追加して結果行を減らせば,SQL 処理時間は短くなるが、(i)行以下に減らしても(j)秒より短くはならない。 表探索によって “TA” テープルからN個の結果行を得るのに必要なSQL 処理時間 T1 は、次の実験式で表せる。 　 　T1 = MAX(10秒 + 0.1 ミリ秒行 × N行、1ミリ秒/ページ × (k) ページ) 　

分析 B 　SQL3A のデータ入出力処理は、(l)データ入出力処理である。 最大で(m) ページをランダムに入力したときのデータ入力処理時間は、10 秒である。 非クラスタ索引探索によって、"TA” テーブルからN個の結果を得るのに必要な SQL 処理時間 T2 は、次の実験式で表せる。 ただし、N は 1,000 以下とし、また、データページはバッファヒットしないものとする。 　 　T2 = 0.1ミリ秒行 × N行 + 10 ミリ秒ページ × (n)ページ 　

〔UPDATE文の処理時間の測定〕 　Fさんの上司であるG氏から、UPDATE 文の処理時間の測定に対して次のような要望があった。 　 要望① UPDATE 文を含むトランザクションを並行して実行するとデッドロックが起きる場合がある。 デッドロックを意図的に起こさせる試験を追加してほしい。 要望② テーブルの全行更新処理が予想される。 テーブルを区分に物理分割した場合を検討し、ログ出力処理時間を考慮したSQL 処理時間を推定してほしい。 　

Fさんは、まず SQL6x を測定し、クラスタ索引探索によってN行を更新する場合のSQL 処理時間 T3 を推定する実験式を考えた。 ここで、データページの1ページ中の 全行を更新するごとにログページが2ページ出力されるものとする。

　T3 = MAX(0.2ミリ秒/行 × N行、1ミリ秒/ページ × 入出力データページ数)+1ミリ秒/ページ× 出力ログページ数 　

　次に、“TA” テーブルと “TB” テーブルをそれぞれ次のように区分に物理分割し、処理時間を短縮するために区分別の UPDATE 文を並行して実行させることを考えた。 1．C3 列を区分キーとして、テーブルを四つの区分に物理分割する。 2．データ入出力処理が競合しないように、各区分を異なるディスクに配置する。 3．索引の TAX3 と TBX3 がクラスタ索引になるように、各テーブルの行を並べ替える。 4．ホスト変数に区分キーのいずれかの値を指定した四つのSQL7x を区分別に作成する。SQL7x は、それぞれ該当区分の全行を更新する。 5．UPDATE 文は、カーソルを使用し、1,000行を更新するごとにコミットする。 　

　そして、"TA” テーブルと “TB” テーブルを物理分割せずに全行更新する場合と、物理分割した上で、1 区分だけを全行更新する場合と、全区分を並行して全行更新する場合の各UPDATE 文の処理時間を、実験式を用いて推定し、表7に示した。