|
|
 |
|
| Alexander Kuznetsov |
| 米国イリノイ州シカゴ |
|
|
|
| |
|
はじめに
時として、データベースの物理構造を少し変えるだけで照会のパフォーマンスが大幅に改善されることがあります。DB2® Universal
DatabaseTMには、索引の他にマテリアライズ照会表（V7.2以前の「サマリー表」 ）が用意されており、多くの場合、索引より効率の点で優れています。マテリアライズ照会表（以下MQT）とは基本的に、照会の結果に基づいて定義される表のことです。この記事では、MQTによって、索引だけを使用する場合よりも効果的にパフォーマンス向上を実現できるいくつかの例について説明します。
利点: 計算の繰り返しの回避
MQTを用いると、SUMなどの計算を照会のたびに繰り返す必要がなくなります。ここに数年間分の顧客からの注文が格納されたCUSTOMER_ORDERという表があると仮定します。この表には、行幅が平均で400バイトのレコードが100万件以上入っています。ここで、次に示す例のように、2001年分の注文に関して複数の照会を実行しなければなりませんが、必要なのは表内の3列だけとします。
| |
select SUM(AMOUNT), trans_dt
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
または
select SUM(AMOUNT), status
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by status
|
適切な索引がある場合は、これらの照会を索引スキャンとして実行します。リスト1は実行プランの抜粋ですが、これを見ると索引スキャンによる照会実行の見積りコストが152455であることがわかります。
リスト1. CUSTOMER_ORDER表に対する照会実行のコスト
| |
Listing 1. Cost of running query on
CUSTOMER_ORDER table
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select SUM(AMOUNT), trans_dt
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
Estimated Cost = 152455
Estimated Cardinality = 378
(some lines are omitted here)
Subsection #2:
Access Table Name = DB2INST2.CUSTOMER_ORDER ID = 2,591
| #Columns = 1
| Index Scan: Name = DB2INST2.CUST_ORD_TRANS_DT ID = 4
| | Index Columns:
| | | 1: TRANS_DT (Ascending)
(some lines are omitted here)
End of section
|
それでは、総計の計算を含めて必要な列と行が格納されたMQTを作成してみましょう。
| |
CREATE TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2001 AS
(SELECT SUM(AMOUNT) AS TOTAL_SUM,
TRANS_DT,
STATUS
FROM DB2INST2.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN '1/1/2001' AND '12/31/2001'
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
|
DATA INITIALLY DEFERRED文節は、データがCREATE TABLEステートメントの一部として表に挿入されないことを意味します。挿入したい場合は、REFRESH
TABLEステートメントを使用して表にデータを読み込む必要があります。REFRESH DEFERRED文節は、REFRESH TABLEステートメントを発行した時点でのスナップショットとしての照会の結果のみが表内のデータに反映されるということを意味します。MQT作成の詳細については、SQL
Referenceをご覧ください。
作成したMQTにデータを読み込む準備ができたら、次のステートメントを発行します。
| |
REFRESH TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2001;
|
これで、照会はMQTに対して実行することでかなり高速になります。これは、MQTのサイズが非常に小さく、しかも行が短い（基本表の行が400バイトであるのに対してわずか45バイト）ことによります。リスト2に示す、dynexplnによって生成された実行プランの抜粋を見ると、見積りコストが先のプランの152455に対して101と、パフォーマンスが大幅に向上していることがわかります。
リスト2. MQTに対する照会実行によるコスト削減
| |
Listing 2. Running a query on MQT
reduces cost
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select sum(total_sum), trans_dt
from db2inst2.summary_customer_order_2001
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
Estimated Cost = 101
Estimated Cardinality = 25
lines are omitted here
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_CUSTOMER_ORDER_2001 ID = 2,44
| #Columns = 2
| Relation Scan
(lines are omitted here)
| | Sortheap Allocation Parameters:
| | | #Rows = 21
| | | Row Width = 45
| | Piped
(lines are omitted here)
|
ただし、リフレッシュの結果、CUSTOMER_ORDERの2001年分のデータが更新された場合、MQTもリフレッシュする必要がある点に注意してください。
利点: 大量のリソースを必要とするスキャンの回避
次に、2002年分の合計を頻繁に最新の状態にする必要があると仮定します。レポートは、2002年1月3日の時点ではすぐに出力されますが、2002年分のデータ量が増加するにつれて、5月ともなれば出力に時間がかかるようになります。まず、先ほどと同様に、CUSTOMER_ORDER表に対する索引スキャンとして照会を実行します。
MQTがどの程度パフォーマンス向上に役立つのかを検討したいところですが、データが常に更新されており、しかも必要なのは最新のデータであるため、REFRESH
DEFERREDを使用することはできません。なぜなら、基本表が次に更新されたときにMQTと基本表の同期が失われることになるからです。REFRESH DEFERREDの代わりに、REFRESH
IMMEDIATEおよびENABLE QUERY OPTIMIZATIONオプションを指定してMQTを作成してみましょう。
| |
CREATE TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 AS(
SELECT
TRANS_DT,
STATUS,
COUNT(*) AS COUNT_ALL,
SUM(AMOUNT) AS SUM_AMOUNT,
COUNT(AMOUNT) AS COUNT_AMOUNT
FROM DB2INST2.CUSTOMER_ORDER
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE
ENABLE QUERY OPTIMIZATION;
|
REFRESH IMMEDIATEは、REFRESH TABLEステートメントによってMQTにデータを読み込んだ後は常にMQTの内容が最新の状態になるということを意味します。
重要: オプティマイザーがMQTの使用を自動的に選択できるようにするには、ENABLE QUERY OPTIMIZATIONを有効にする必要があります（これがデフォルトです）。
その他の構文に関する注意: ビジネスの目的上、SUM(AMOUNT)にしか関心がない場合も、全選択にはCOUNT(*)とCOUNT(AMOUNT)を含める必要があります。理由は簡単です。特定日のすべてのレコードを基本表から削除する場合を考えてみましょう。
| |
DELETE FROM DB2INST2.CUSTOMER_ORDER WHERE TRANS_DT = ‘1/1/2002’;
|
この場合、DB2はある特定日のすべてのレコードが削除されたことを検出し、MQT内の対応するすべてのレコードを削除する必要がありますが、COUNTフィールドがあれば、表スキャンまたは索引スキャンを行う必要なく直ちにその操作を実行できます。なお、COUNT(AMOUNT)が必要になるのは、AMOUNT列がヌル可能な場合だけです。
次に、MQTにデータを読み込み、統計をリフレッシュしてみましょう。
| |
REFRESH TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002;
RUNSTATS ON TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 WITH DISTRIBUTION;
|
照会パフォーマンスがどの程度向上するかを見てみましょう（見積りコスト = 392）。リスト3は、照会実行プランの抜粋です。
リスト3. オプティマイザーによるMQT使用の選択
| |
Listing 3. Optimizer chooses to use
MQT
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select SUM(AMOUNT), trans_dt
from db2inst2.customer_order
where trans_dt >= '1/1/2002'
group by trans_dt
Estimated Cost = 392
Estimated Cardinality = 268
(lines are omitted here)
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 ID = 2,46
| #Columns = 2
| Relation Scan
|
この照会では、サマリー表ではなく、CUSTOMER_ORDER表を指定しましたが、オプティマイザーによってMQTの使用が自動的に選択されています。
CUSTOMER_ORDER表を変更すると、トランザクションが終了するまで、SUMMARY_CUSTOMER_ORDER_2002に対して排他表ロックが保持される可能性があります。これは、集約関数とREFRESH
IMMEDIATEオプションの両方が指定されたMQTにのみ発生します。したがって、ロック競合を少なくするには、CUSTOMER_ORDERの関連フィールドを変更するトランザクション（すべての挿入および削除を含む）をごく短時間で済ませる必要があります。この問題は、REFRESH
DEFERREDオプションが指定されたMQTや複製MQT（次のセクションで説明します）には当てはまりません。
利点: 複製MQTの使用によるブロードキャストの回避
CUSTOMER_DATAという大きな表が区画環境内にあると仮定します。このCUSTOMER_DATA表は子表と連結されます。区分化キーは、システム生成の整数CUSTOMER_IDです。また、CUSTOMER_DATA表はZIP_CODEという他の表への参照を含んでいます。CUSTOMER_DATA表とZIP_CODE表は連結されていませんが、これら2つの表は頻繁に結合されます。それでは、リスト4に示すアクセス・プランを見てみましょう。
リスト4. ZIP_CODEとの結合によるノード間ブロードキャストの発生
| |
Listing 4. Joins with ZIP_CODE cause
cross-node broadcasts
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select c.*, z.zip, z.state_name, z.country_name
from db2inst2.customer_address c join db2inst2.zip_code z on
c.zip_cd = z.zip_cd
Estimated Cost = 100975
Estimated Cardinality = 255819
Coordinator Subsection:
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 0, 1
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1
Access Table Queue ID = q1 #Columns = 38
Return Data to Application
| #Columns = 38
Subsection #1:
Access Table Queue ID = q2 #Columns = 4
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Nested Loop Join
| Access Table Name = DB2INST2.CUSTOMER_ADDRESS ID = 2,591
| | #Columns = 35
| | Index Scan: Name = DB2INST2.CU_ZIP_CD ID = 2
| | | Index Columns:
| | | | 1: ZIP_CD (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: Eligible 162
| | | Index Prefetch: Eligible 162
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Insert Into Asynchronous Table Queue ID = q1
| | | Broadcast to Coordinator Node
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q1
Subsection #2:
Access Table Name = DB2INST2.ZIP_CODE ID = 2,590
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 4
| | #Sort Key Columns = 1
| | | Key 1: ZIP_CD (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 4479
| | | Row Width = 36
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Insert Into Asynchronous Table Queue ID = q2
| | Broadcast to All Nodes of Subsection 1
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q2
End of section
|
ZIP_CODE表は、あまり更新されませんが（新しい郵便番号というのはそれほどないため）、頻繁に結合のターゲットとなります。したがって、結合を生じさせる照会を発行するたびに、すべてのノードにZIP_CODE表をブロードキャストする必要があります。
こうした状況こそ、複製MQTを使用するのに適していると言えます。単一区画ノードグループ内で作成された表に基づくMQTであっても、ノードグループ内のすべてのデータベース区画にわたる複製によって、頻繁にアクセスされるデータのコロケーションを可能にしたい場合です。複製MQTを作成するには、REPLICATEDキーワードを指定してCREATE
TABLEステートメントを呼び出します。
| |
CREATE TABLE DB2INST2.SUMMARY_ZIP_CODE AS (SELECT * FROM DB2INST2.ZIP_CODE)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION REPLICATED;
|
この定義では、集約はできません。ZIP_CODE表は、ZIP_CDに固有索引を持っています。表にデータを読み込み、それに対して索引を作成し、統計を更新してみましょう。
| |
REFRESH TABLE DB2INST2.SUMMARY_ZIP_CODE;
CREATE INDEX AAA_TTT ON DB2INST2.SUMMARY_ZIP_CODE(ZIP_CD);
RUNSTATS ON TABLE DB2INST2.SUMMARY_ZIP_CODE WITH DISTRIBUTION AND DETAILED INDEXES ALL;
|
この場合、オプティマイザーによって複製表の使用が自動的に選択され、照会を実行するたびにすべてのノードにZIP_CODE表をブロードキャストする必要がなくなります。
リスト5. 複製ZIP_CODE表の使用による一部のノード間ブロードキャストの回避
| |
Listing 5. By using replicated
ZIP_CODE table, some cross-node broadcasts are avoided.
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select c.*, z.zip, z.state_name, z.country_name
from db2inst2.customer_address c join db2inst2.zip_code z on
c.zip_cd = z.zip_cd
Estimated Cost = 101171
Estimated Cardinality = 255819
Coordinator Subsection:
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1
Access Table Queue ID = q1 #Columns = 38
Return Data to Application
| #Columns = 38
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_ZIP_CODE ID = 2,47
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 4
| | #Sort Key Columns = 1
| | | Key 1: ZIP_CD (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 8958
| | | Row Width = 36
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = DB2INST2.CUSTOMER_ADDRESS ID = 2,591
| | #Columns = 35
| | Index Scan: Name = DB2INST2.CU_ZIP_CD ID = 2
| | | Index Columns:
| | | | 1: ZIP_CD (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: Eligible 162
| | | Index Prefetch: Eligible 162
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Insert Into Asynchronous Table Queue ID = q1
| | | Broadcast to Coordinator Node
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q1
End of section
|
この例では、複製MQTを使用すると見積りコストは若干高くなりますが（100975に対して101171）、これは1台のコンピューター上の、この操作以外はアイドル状態の2区画のシステムで実行しているからです。しかし、この場合の複製MQTを使用するパフォーマンス上の利点は、各ノードがそれぞれ別のコンピューター上に置かれ、それらのコンピューター間のネットワークが混んでいる場合に明らかになります。
したがって、複製MQTの使用によってパフォーマンス上の利点が得られるのは、次のような表からデータを複製する場合であると言えます。
- 頻繁に結合される
- めったに更新されない
- あまり大きくない（ただし、1回の複製のコストがコロケーションのパフォーマンス上の利点によって相殺できるような場合には、大きな表でもめったに更新されないものなら複製を検討してもよいでしょう）
なお、REFRESH IMMEDIATE表で説明したロックの問題は、複製MQTでは発生しません。
REFRESH IMMEDIATEとREFRESH DEFERRED
REFRESH IMMEDIATE MQTは、索引と同様に照会のパフォーマンスに影響します。REFRESH IMMEDIATEには次のような特徴があります。
- 関連する選択ステートメントのパフォーマンスを高速化する
- 妥当な場合には常にオプティマイザーによって自動的に選択される
- 挿入、更新、および削除ステートメントのパフォーマンスを低下させる可能性がある
- 直接更新できない
- かなりのディスク・スペースを占有する可能性がある
- その基本表の更新中に排他ロックが保持される可能性がある
それでは、リスト6に示すINSERTステートメントのEXPLAIN出力から、更新のパフォーマンスに対する影響を検証してみましょう（MQTは未作成）。
リスト6. ZIP_CODE基本表に対するINSERT
| |
Listing 6. INSERT on base ZIP_CODE
table
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
insert into db2inst2.zip_code(zip_cd, zip, state_cd, state_name,
country_name) values (60606, '60606', 'IL', 'Illinois',
'United States')
Estimated Cost = 25
Estimated Cardinality = 1
(lines omitted here)
|
次に、REFRESH IMMEDIATEオプションを指定してMQTを追加した場合のEXPLAIN出力をリスト7に示します。
リスト7. REFRESH IMMEDIATEが指定されたMQTに対するINSERTによるパフォーマンス・コスト増加の可能性
| |
Listing 7. INSERTs on MQTs with
REFRESH IMMEDIATE can add performance cost
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
insert into db2inst2.zip_code(zip_cd, zip, state_cd, state_name,
country_name) values (60606, '60606', 'IL', 'Illinois',
'United States')
Estimated Cost = 50
Estimated Cardinality = 1
(lines omitted here)
|
この場合は、REFRESH IMMEDIATE MQTが存在すると、レコード挿入の見積りコストが倍増します。それに対して、REFRESH DEFERRED
MQTの場合には、挿入、更新、削除のいずれのステートメントのパフォーマンスも低下しません。
REFRESH IMMEDIATE MQTは、頻繁に実行され、最新のデータが重要である照会を最適化する場合に限って使用するにとどめるべきです。また、MQTによっては即時リフレッシュに適さないものもあります。正確な規則については、SQL
Referenceをご覧ください。
オプティマイザーに決定を任せる
オプティマイザーは、次の条件に基づいて、基本表の代わりに、REFRESH IMMEDIATEオプションが指定されたMQTの使用を選択することができます。
- 基本表、MQT、およびその索引に関する最新の統計
- CURRENT QUERY OPTIMIZATIONの設定値
REFRESH DEFERREDオプションが指定されたMQTの使用をオプティマイザーが選択できるのは、CURRENT REFRESH AGEが「ANY」に設定されている場合です。CURRENT
QUERY OPTIMIZATIONおよびCURRENT REFRESH AGE設定の詳細については、SQL
Referenceをご覧ください。
基本表とサマリー表のどちらを使用するかをオプティマイザーに選択させる場合、オプティマイザーにMQTを与え、適切な索引を作成し、統計を最新の状態に維持してください。場合によっては、オプティマイザーがMQTの使用を選択しないこともあります。
また、REFRESH DEFERREDとREFRESH IMMEDIATEのいずれの場合も、CURRENT REFRESH AGEおよびCURRENT
QUERY OPTIMIZATIONの設定値に関係なく、SELECTステートメントのWHERE文節にMQTを直接指定することが可能です。
まとめ
以上見てきたように、MQTは適切に使用すれば、さまざまな状況で非常に役立ちます。ここで示した例をご覧になって、MQTの使用による照会パフォーマンスの向上がおわかりいただけたかと思います。MQTは非常に便利ですが、追加のディスク・スペースが必要になるという欠点もあります。REFRESH
DEFERREDオプションが指定されたMQTは、基本表に対する挿入、更新、および削除のパフォーマンスに影響しませんが、REFRESH IMMEDIATEオプションが指定されたMQTはパフォーマンスに影響します。
著者について
 |
Alexander Kuznetsovは、ソフトウェア設計、開発、データベース管理の分野に14年の経験があり、現在はDB2 UDB EEEによるマルチテラバイト・クラスター・データベースの設計に取り組んでいます。彼は、ワールド・ワイド認定のDB2アドバンスト・エキスパート（DB2
for Clusters）、DB2エキスパート（管理）、およびDB2エキスパート（開発）の資格を持っています。連絡先はcomp.databases.ibm-db2ニュースグループです。 |
 原文はこちら
|
