データベースの設計，実装に関する次の記述を読んで，設問1〜3に答えよ。
太陽光発電設備，空調設備などの住宅設備メーカであるJ社は，家庭の電気の使用状況を可視化するサービスの提供に向け，節電支援システムの構築を行っている。
〔業務の概要〕
（1）J社は，太陽光発電機器，配電盤，スマートメータ，空調機器などの機器との通信機能をもつコントローラを提供している。ユーザは，住居にコントローラを設置して，節電支援システムに住居及びコントローラの情報を登録する。
（2）コントローラは，J社とネットワーク接続し，住居内の機器から収集した買電，発電，使用電力，機器のログなどの情報をJ社に送信する。J社では，情報をデータベースに蓄積，加工してユーザに節電支援情報を提供する。その一つとして使用電力量表示画面の例を図1に示す。図1では，使用電力量とその供給元の内訳（買電，発電など）が示される。
（3）節電支援情報には，日射量を基に算出された標準発電量が含まれる。全国の800観測点で計測された日射量データを外部から取得する。
（4）自治体，他の企業からの依頼を受けて，蓄積したデータを統計用に集計し，個人情報を含まないアドホック分析用のデータを抽出して提供する。
図1 使用電力量表示画面の例
〔節電支援システムのテーブル構造〕
設計済みのテーブル構造を図2に，主な列とその意味制約を表1に示す。
図2 節電支援システムのテーブル構造（一部省略）
表1 主な列とその意味制約
〔節電支援システムの処理〕
節電支援情報の提供，アドホック分析の処理の例を，表2に示す。
表2 処理の例
〔RDBMSの仕様〕
1.テーブル索引
（1）RDBMSとストレージ間の入出力単位をページという。
（2）同じページに異なるテーブルのデータが格納されることはない。
（3）NOT NULL制約を指定しない列には，1バイトのフラグが付加される。
（4）列の主なデータ型は，表3のとおりである。
（5）索引にはユニーク索引と非ユニーク索引がある。
（6）DMLのアクセスパスは，RDBMSによって索引探索又は表探索が選択される。索引探索が選択されるためには，WHERE旬又はON句のANDだけで結ばれた一つ以上の等値比較の述語の対象列が，索引キーの全体又は先頭から連続した一つ以上の列に一致していなければならない。
（7）索引探索では，ページの読込みはバッファを介して行われ，バッファの置換えはLRU方式で行われる。同じページ長のテーブルは，一つのバッファを共有する。バッファごとにバッファサイズを設定する。
（8）索引のキー値の順番と，キー値が指す行の物理的な並び順が一致している割合（以下，クラスタ率という）が高いほど，隣接するキー値が指す行が同じページに格納されている割合が高い。
表3 列の主なデータ型
1.ウィンドウ関数
RDBMSがサポートする主なウィンドウ関数を，表4に示す。
表4 RDBMSがサポートする主なウィンドウ関数
3.クラスタ構成のサポート
（1）シェアードナッシング方式のクラスタ構成をサポートする。クラスタは複数のノードで構成され，各ノードには専用のディスク装置をもつ。
（2）ノードへのデータの配置方法には複製，分散の二つがあり，テーブルごとにどちらかを指定する。複製は，各ノードにテーブルの全行を配置する方法である。分散は，一つ又は複数の列を分散キーとし，その値に基づいてRDBMS内部で生成するハッシュ値によって，各ノードにデータを配置する方法である。
（3）データベースへの要求は，いずれか一つのノードで受け付ける。要求を受け付けたノードは，要求を解析し，自ノードに配置されているデータへの処理は自ノードで処理を行う。自ノードに配置されていないデータへの処理は，当該データが配置されている他ノードに処理を依頼し，結果を受け取る。
〔システムの構成〕
（1）複数のAPサーバと複数のDBサーバから成る。ユーザは，APサーバを介して操作を行う。APサーバはいずれか一つのDBサーバにアクセスする。
（2）データベースは，シェアードナッシングのクラスタ構成とし，20ノードを配置する。各ノードには同じ仕様のDBサーバを配置する。クラスタ構成によるDBサーバ全体のTPSは，ノード数に比例することを確認している。
（3）機器ログ，買電，発電，使用電力の各テーブルは，配置方法を分散にし，住居番号を分散キーに指定する。それ以外のテーブルの配置方法は複製にする。
1.テーブルの行数，所要量見積り
次の前提で主なテーブルの行数，所要量を見積もり表を作成した。
（1）日射量，機器ログ，買電，発電，使用電力は，2年前の1月1日から現在まで，最大3年分のデータを保存する。1年を360日として行数を見積もる。
（2）どのテーブルもページ長を2，000バイト，空き領域率を10%とする。
（3）見積ページ数は，“見積行数+ページ当たり平均行数”の小数部を切り上げる。ページ当たり平均行数は，“ページ長×（1-空き領域率）+平均行長”の小数部を切り捨てる。
（4）所要量は，“見積ページ数×ページ長”で算出する。
表5 主なテーブルの見積行数・所要量
2.テーブル定義表の作成
次の方針に基づいてテーブル定義表を作成した。その一部を表6〜8に示す。
（1）データ型欄には，適切なデータ型，適切な長さ，精度，位取りを記入する。
（2）NOT NULL欄には，NOT NULL制約がある場合だけYを記入する。
（3）格納長欄には，RDBMSの仕様に従って，格納長を記入する。
（4）索引の種類には，P（主キーの索引），U（ユニーク索引），NU（非ユニーク索引）のいずれかを記入し，各構成列欄には，構成列の定義順に1からの連番を記入する。該当する索引がなければどちらも空欄にする。
（5）主キー及び外部キーには，索引を定義する。
表6 “買電”テーブルのテーブル定義表
表7 “発電”テーブルのテーブル定義表
表8 “使用電力”テーブルのテーブル定義表
3.テーブル構造の検討
表2の処理1では，ストレージからの読込みに時間が掛かるおそれがあるので，次の前提で読込みページ数の予測を行い，必要であれば対策を講じる。
（1）前提
・図1の使用電力量表示画面で，年月日の区分を'月'，対応する年月を'2019年4月'，比較対象を‘前年の同じ月’として照会を行う。
・“買電"，"発電”，“使用電力”の各テーブルには，住居番号当たりの行数は均等で，どのページにも最大行数分の行が格納されているものとする。
・アクセスパスは，どのテーブルも索引探索が選択されるものとする。索引のバッファヒット率は，100%とする。
（2）予測
・表9に使用電力量表示画面における読込みページ数の予測をまとめた。
・探索行数は，選択条件に一致する行を求めるために読み込む行数である。
・最小読込みページ数は，クラスタ率が最も高い場合の読込みページ数で，“ a ÷ b ”の小数部を切り上げた値に等しい。
・最大読込みページ数は，クラスタ率が最も低い場合の読込みページ数で， c に一致する。
・クラスタ率50%の読込みページ数を平均読込みページ数といい，“（最小読込みページ数+最大読込みページ数）×50%”の小数部を切り上げる。
表9 使用電力量表示画面における読込みページ数の予測（未完成）
（3）対策
表9の結果から照会の応答時間が長すぎると判断した。そこで，次の対策を行うことで，平均読込みページ数を100以下にすることにした。
・照会対象となる電力量を集計した一つ又は複数のテーブルを追加する。
・追加したテーブルには，前日までの集計行を夜間バッチ処理で追加する。
・当日の電力量は“買電”，“発電”，“使用電力”の各テーブルから求め，それ以外の電力量は追加したテーブルから求める。
〔問合せの検討〕
1.表2の処理3の問合せ
処理3の問合せ内容を表10に整理し，問合せに用いるSQL文を図3に作成した。問合せ内容は，次の要領で記入し，内容のない欄は“ー”にする。
（1）行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合は，行に固有の名前（以下，問合せ名という）を付ける。
（2）列名又は演算には，テーブルから射影する列名又は演算（MAX関数，AVG関数など）によって求まる項目を“項目名=[演算の概要]”の形式で記述する。
（3）テーブル名又は問合せ名には，参照するテーブル名又は問合せ名を記入する。
（4）選択又は結合の内容には，テーブル名又は問合せ名ごとの選択条件，結合の具体的な方法と結合条件を記入する。
表10 処理3の問合せ内容
図3 処理3の問合せに用いるSQL文（未完成）
2.表2の処理4の問合せ
処理3と同様に，処理4の問合せ内容を表11に整理し，問合せに用いるSQL文を図4に作成した。
表11 処理4の問合せ内容（未完成）
図4 処理4の問合せに用いるSQL文（未完成）
〔性能テストの実施〕
ピーク時の処理性能を見積もった上で，性能テストを実施して検証する。
1.性能テストの方針
（1）DBサーバ2台による2ノードのクラスタ構成のテスト環境を使用する。各DBサーバの仕様は本番のDBサーバと同じにする。また，ページ長が2,000バイトのバッファには，480,000ページ分のサイズを設定する。
（2）ピーク時の5分間に，表2中の複数の処理が，それぞれ複数同時にDBサーバ上で実行される状況を模してテストを行い，応答時間を計測して見積りと比較する。処理は，それぞれピーク時の想定に基づきDBサーバごとに50〜100の多重度で実行する。
（3）表5の見積行数を基に，テスト環境の特性を踏まえて，適切な行数，適切な列値，参照整合性を備えたテストデータを作成する。
2.応答時間の見積り
次の前提に基づいて，トランザクション当たりの応答時間見積りを表12にまとめた。
（1）ページ当たりのバッファへのアクセス時間は，平均0.1ミリ秒である。
（2）ページ当たりのストレージへのアクセス時間は，平均8ミリ秒である。
（3）各DBサーバは，本テストにおける最大の同時並行処理数を上回る並行処理能力を備えている。
（4）DBサーバ上の実行待ち時間，ネットワーク待ち時間，CPU待ち時間は考慮しない。
表12 トランザクション当たりの応答時間見積り
3.テストデータの作成
主なテーブルのテストデータの作成要領を表13に示す。
表13 主なテーブルのテストデータの作成要領
4.性能テストの結果
性能テストの実行結果を図5に示す。図5の処理の実行時間帯の列は，10秒間を表し，網掛け部分は，その処理が1回以上実行された時間帯を表す。応答時間の実測値を表12の見積りと比較したところ，次の差異があった。
差異1：処理5は，どの時間帯でもバッファヒット率が90%を超え，応答時間は見積りの約50分の1だった。
差異2：処理3は，図5中の1及び2の時間帯で応答時間が見積りよりも長かった。
図5 性能テストの実行結果
設問1 〔データベースの物理設計〕について，（1），（2）に答えよ。
（1）表2の処理2では，比較対象住居を索引内で絞り込むようにしたい。そのために，“住居”テーブルに，主キー，外部キー以外の索引を一つ定義する。索引を構成する列名を定義順に答えよ。
解答・解説
解答例
世帯区分，地域コード
解説
ー
（2）“3.テーブル構造の検討”について，次の①〜③に答えよ。
①予測について，本文中の a 〜 c に入れる適切な字句を，本文中の用語を用いて答えよ。また，表9中の d 〜 f に入れる適切な数値を答えよ。
②対策について，追加するテーブルのテーブル構造を答えよ。解答に当たっては，巻頭の表記ルールに従うこと。
なお，"列1番，列2番，列3番，列4番”のように，一定の規則で連続する列名は，“列1番，...，列4番”のように間を省略してもよい。また，買電，発電，使用電力を区別する列を用いる場合は，列名を“電力区分”とし，データ型をCHAR(1)とせよ。
③②のテーブルを使用して使用電力量照会を行う場合の読込みページ数予測を行い，2のテーブルの最小読込みページ数，最大読込みページ数を答えよ。
解答・解説
解答例
①a：探索行数
b：ページ当たり平均行数
c：探索行数
d：540,000
e：6,000
f：540,000
②日別計（住居番号，年月日，電力区分，電力量）
③最小読込みページ数：1
最大読込みページ数：120
解説
ー
設問2 〔問合せの検討〕について，（1），（2）に答えよ。
（1）図3中の g 〜 k に入れる適切な字句を答えよ。
解答・解説
解答例
g：CROSS
h：距離
i：AVG(標準発電量) AS 平均標準発電量
j：GROUP BY 住居番号, 年月日, 時
k：SUM(発電量) AS 時間発電量
解説
ー
（2）表11中の l 〜 o ，図4中の p に入れる適切な字句を答えよ。
解答・解説
解答例
l：①の結果と住居を住居番号で内結合
m：平均使用電力量=[地域コード，階級番号ごとの合計使用電力量の平均]
n：W3 の全行を選択
o：①の結果と地域を地域コードで内結合
p：PARTITION BY 地域コード ORDER BY 合計使用電力量 DESC
解説
ー
設問3 〔性能テストの実施〕について（1），（2）に答えよ。
（1）“3.テストデータの作成”について，次の①，②に答えよ。
①“住居”テーブルの行数は見積行数の10分の1の行数となっている。この行数で本番環境の性能が推測できる理由を50字以内で述べよ。
②表13の下線部について，地域コードに値を設定する上で留意すべき事項を具体的に50字以内で述べよ。
解答・解説
解答例
①分散するテーブルは住居番号が分散キーであり，ノード当たりの行数と DB サーバの仕様が同じだから
②・地域当たりの住居数が実データに近い比率で分散するように値を設定す る。
・1 地域当たりの住居数が 100 となるように値を設定する。
解説
ー
（2）“4.性能テストの結果”の差異1，差異2について，最も可能性が高いと考えられる差異の発生原因を，それぞれ具体的に50字以内で述べよ。
解答・解説
解答例
差異 1：“機器ログ”テーブルは，住居番号，年月日順に行を追加したこと で，クラスタ率が高くなったこと
差異 2：処理 2 の実行によって，同時に実行される処理 3 が参照するページが バッファから追い出されたこと
解説
ー
IPA公開情報
出題趣旨
家電，センサ，太陽光発電機器などの機器をネットワークに接続して制御し，機器の稼働データを収集，蓄積したデータを集計，加工して情報提供を行うサービスが増えている。こうした分刻みに大量発生するデータを扱うシステムでは，特に性能に配慮した設計，テストが求められる。
本問は，ホームエネルギーマネジメントシステムと連携して収集，蓄積したデータを利用した情報提供サービスを例として，物理設計，データ操作，性能チューニングを行う能力を問うものである。具体的には，①論理データモデルを理解し，物理データモデルとして設計する能力，②適切なインデックスを設計し，評価する能力，③テストを計画し実施する能力，④RDBMSにおける処理性能の基礎数値を取得し，性能の妥当性を評 価する能力を評価する。
採点講評
問 1 では，ホームエネルギーマネジメントシステムの情報提供サービスを題材に，データベースの設計及び実装について出題した。全体として正答率は低かった。
設問 1(1)では，処理内容に合わせた最適な索引の設計を求めたが，索引構成列の定義順を考慮しない解答が 散見された。(2)は，索引探索における入出力ページ数に着目した性能の試算及び改善策について出題したが，正答率は低かった。行の物理的な並び順と索引のキー順の一致度合いによって性能差が出ることを考慮した性 能設計を行うように心掛けてほしい。
設問 2 は，クロス結合の結果行の集計，ウィンドウ関数を用いた問合せについて出題した。全体として正答率は高かったが，ウィンドウ関数の構文を正しく理解していない解答が散見された。ビッグデータの活用に際して，こうした問合せの設計は特に必要とされることであり，是非理解を深めてもらいたい。
設問 3 は，性能テストについて出題した。全体として正答率は低かった。(1)②では，性能テストの実施を考慮した解答を期待していたが，システム機能の観点での解答が散見された。(2)の差異 1 では，バッファヒット率に言及した解答が多かった。データの投入順序によって性能が変わることを是非知っておいてもらいたい。 (2)の差異 2 では，処理が並走している点だけに言及した解答が目立った。処理の特性，RDBMS の仕組みを考慮した上で解答してほしい。
