データベーススペシャリスト 2021年 午後1 問02

　クレジットカード会社のC社では、キャッシュレス決済の普及に伴いカード決済システムのオンライントランザクションの処理量が増えている。 情報システム部のFさんは、将来の処理量から懸念される性能低下の対策を検討することになった。 　

〔RDBMS の主な仕様〕 1．アクセス経路、区分化、再編成 　(1) アクセス経路は、RDBMS によって表探索又は索引探索に決められる。 表探索では、索引を使わずに先頭ページから順に全行を探索する。 索引探索では,WHERE 句中の述語に適した索引によって絞り込んでから表の行を読み込む。 　(2) テーブルごとに一つ又は複数の列を区分キーとし、区分キーの値に基づいて物理的な表領域に分割することを区分化という。 　(3) 区分方法にはハッシュとレンジの二つがある。 どちらも、テーブルを検索する SQL 文の WHERE 旬の述語に区分キー列を指定すると、区分キー列で特定した区分だけを探索する。 　　① ハッシュは、区分キー値を基に RDBMS が生成するハッシュ値によって一定数の区分に行を分配する方法である。 　　② レンジは、区分キー値の範囲によって区分に行を分配する方法である。 　(4) テーブル又は区分を再編成することによって、行を主キー順に物理的に並び替えることができる。 また、各ページ中に指定した空き領域を予約することができる。 　(5) INSERT 文で行を挿入するとき、RDBMS は、主キー値の並びの中で、挿入行の主キー値に近い行が格納されているページに空き領域があればそのページに、なければ表領域の最後のページに格納する。 最後のページに空き領域がなければ、新しいページを表領域の最後に追加する。

2．データ入出力とログ出力 　(1) データとログはそれぞれ別のディスクに格納される。 同じディスクに対し同時に入出力は行われないものとする。 　(2) データ入出力とログ出力は4,000バイトのページ単位に行われる。 　(3) データバッファはテーブルごとに確保される。 　(4) ページをランダムに入出力する場合、SQL 処理中の CPU 処理と入出力処理は並行して行われない。これを同期データ入出力処理と呼び、SQL 処理時間は次の式で近似できる。 　　SQL処理時間= CPU 時間 + 同期データ入出力処理時間 　(5) ページを順次に入出力する場合、SQL 処理中の CPU 処理と入出力処理は並行して行われる。これを非同期データ入出力処理と呼び、SQL 処理時間は次の式で近似できる。ここで関数 MAXは引数のうち最も大きい値を返す。 　　SQL処理時間 = MAX(CPU 時間、非同期データ入出力処理時間) 　(6) 行を挿入、更新、削除した場合、変更内容がログとして RDBMS に一つ存在するログバッファに書き込まれる。 ログバッファが一杯の場合、トランザクショインの INSERT文 UPDATE 文、DELETE 文の処理は待たされる。 　(7) ログは、データより先にログバッファからディスクに出力される。 これをログ出力処理と呼ぶ。 このとき、トランザクションのコミットはログ出力処理の完了まで待たされる。 ログ出力処理は、次のいずれかの事象を契機に行われる。 　　① ログバッファが一杯になった。 　　② トランザクションがコミット又はロールバックを行った。 　　③ あるテーブルのデータバッファが変更ページによって一杯になった。 　

〔カード決済システムの概要〕 1．テーブル 　主なテーブルのテーブル構造を図1, 将来の容量見積りを表1に示す。 各テーブルの主キーには索引が定義されており、索引キーを構成する列の順はテーブルの列の順と同じである。

2．オーソリ処理 (オンライン処理) 　オーソリ処理は、会員がカードで支払う際にカード有効期限、与信限度額を超過していないかなどを判定する処理である。 判定した結果、可ならば審査結果を'Y' に、否ならば 'N' に設定した行を “オーソリ履歴” テーブルに挿入する。オーソリ処理は最大 100 多重で処理される。 “オーソリ履歴” テーブルには直近5年分を保持する。

3．利用明細抽出処理 (バッチ処理) 　請求書作成に必要な1か月分の利用明細の記録を “オーソリ履歴” テーブルから抽出しファイルに出力する。 　

〔参照処理の性能見積り〕 　将来の処理時間が懸念される利用明細抽出処理の SQL文を、図2に示す。

　Fさんは、利用明細抽出処理の処理時間を、次のように見積もった。 1．このSQL文での表の結合方法を調べたところ、“オーソリ履歴” テーブルを外側、“加盟店” テーブルを内側とする入れ子ループ法だった。 “オーソリ履歴” テーブルのアクセス経路は表探索だったので、(a)ページを非同期に読み込む。 2．ディスク転送速度を100M バイト/秒と仮定すれば、(a)ページを非同期に読み込むデータ入出力処理時間は、(b)秒である。 3．カード数を 1,000万枚、カード・月当たり平均オーソリ回数を80 回、審査結果が全て可であると仮定すると、“オーソリ履歴” テーブルの結果行数は、(c)行である。これに掛かる CPU 時間は、96,000 秒である。 4．この結合では、外側の表の結果行ごとに “加盟店” テーブルの主キー索引を索引探索し、“加盟店” テーブルを1行、ランダムに合計(d) 回読み込む。 5．索引はバッファヒット率 100%、テーブルはバッファヒット率 0%と仮定すれば、“加盟店” テーブルを合計で (e)ページを同期的に読み込むことになる。 　同期読込みにページ当たり1ミリ秒掛かると仮定すれば、同期データ入出力処理時間は(f)秒である。 6．内側の表の索引探索と結合に掛かる CPU 時間は、1 結果行当たり 0.01 ミリ秒掛かると仮定すれば、(g)秒である。 7．外側の表の CPU 時間は 96,000 秒、内側の表の CPU 時間は(g)秒、内側の表の同期データ入出力処理時間は(f)秒なので、SQL 文の処理時間を(h)秒と見積もった。 　

　処理時間が長くなることが分かったので時間短縮のため、次の2案を検討した。 案1：“加盟店” テーブルのデータバッファを増やしバッファヒット率 100%にする。 案2： “オーソリ履歴” テーブルの利用日列をキーとする副次索引を追加する。 　

〔“オーソリ履歴” テーブルの区分化〕 　Fさんは、上司であるG氏から、次の課題の解決策の検討を依頼された。

課題1：月末近くに起きるオーソリ処理の INSERT文の性能低下を改善すること 課題2：将来懸念される利用明細抽出処理の処理時間を短縮すること 課題3：月初に行う “オーソリ履歴” テーブル再編成の処理時間を短縮すること 　

　Fさんは、課題を解決するために、“オーソリ履歴” テーブルを区分化することにし、区分キーについて表2に示す3案を評価した。 いずれの案も 60 区分に行を均等に分配する前提であり、図2のSQL文を基に区分化に対応したSQL文を作成した。作成したSQL文のWHERE句を図3に示す。 利用明細抽出処理及び再編成について、アクセスする総ページ数が最小になるようにジョブを設計した。 このときジョブは、必要に応じて並列実行させる。

〔更新処理の多重化〕 　“オーソリ履歴” テーブルの請求済フラグと請求日を更新する処理も同様に、将来の処理時間が懸念された。 更新処理の SQL 文を図4に示す。 更新処理はバッチ処理であり、カーソルを使用して1,000行を更新するごとにコミットする。

　Fさんは、次のように、区分化と併せて、更新処理を多重化することにした。 1．“オーソリ履歴” テーブルについて、カード番号、利用日の順の組で区分キーとし、レンジによって区分化する。 2．区分ごとのジョブで更新処理を多重化する。 3．更新処理を多重化しても競合しないように、各区分を異なるディスクに配置し、データバッファを十分に確保する。