[B15] HiRDBのSQL実行プランはどのように決定しているのか？by Masaaki Narita

HiRDBのSQL実行プランはどのようにHiRDBのSQL実行プランはどのように決定しているのか？

株式会社日立製作所情報・通信システム社

2014/06/18

株式会社製作所情報通信テ社ITプラットフォーム事業本部開発統括本部ソフトウェア開発本部 DB設計部

成田正亮

© Hitachi, Ltd. 2014. All rights reserved.

成田正亮

本日のテーマ

ＨｉＲＤＢ初期バージョンから進化し続けているＳＱＬ実行プラン/ＳＱＬ実行時ている、ＳＱＬ実行プラン/ＳＱＬ実行時動作の根幹部分を紹介致します。

© Hitachi, Ltd. 2014. All rights reserved. 1

目次目次

１. ＨｉＲＤＢとは？２ＳＱＬ実行プラン２. ＳＱＬ実行プラン３. フロータブルサーバ４. プロセス間のＤＢデータ転送５. ＬＩＭＩＴ処理


目次目次



1.1 ＨｉＲＤＢについて

「止めない」設計思想を貫く「止めない」設計思想を貫く高信頼ノンストップデータベース高信頼ノンストップデータベース高信頼ノンストップデタベス高信頼ノンストップデタベス

社会基盤を支えるために日立が自社開発にこだわり続ける純国産ＲＤＢＭＳ日立が自社開発にこだわり続ける純国産ＲＤＢＭＳ

ハイアールディービー

Highly Scalable Relational DataBase

今まで培った信頼性をベースにクラウド時代を支える「ワンランク上の」

データベースを目指します


デタベスを目指します。

1.2 ＨｉＲＤＢの歴史

オープンシステムで進化’14

超高速データベースエンジン

進化

■クラウド時代を支える高性能高信頼デタベスへ

メインフレーム(ＭＦ)での発展

XDM/RD V12’10/1 ’10/4

Hitachi Advanced Ｄata Binder技術の継承

’03

HiRDB V8’03/8

■ ビジネスの急速な変化に対応する柔軟性を兼ね備えた、情報統合基盤と高信頼ノンストップデータベースへ

XDM/RD V11’05/1 ’06/6

高性能・高信頼データベースへ

HiRDB V6 ■ネットビジネス（24×7運用）に応える長時間連続運転の更なる強化

■ ノンストップビジネスに応える耐障害性と可用性を追及

’01

XDM/RD V9’02/9

HiRDB V7XDM/RD V10

XDM/RD

技術の継承

HiRDB V2～5 ■ミッションクリティカル向け機能･性能強化■デジタルコンテンツの拡張・Universal Server リリース

に応える長時間連続運転の更なる強化

’94

’95～’99

PDMADMXDM/SD

RDB1 HiRDB V1 ■オープンシステム向けミッションクリティカル向けデータベース・ＳｈａｒｅｄＮｏｔｈｉｎｇでのパラレルサーバリリース

94

© Hitachi, Ltd. 2014. All rights reserved. 5１９７０１９９０２０００２０１０

PDM時代

目次目次



2.1 ＳＱＬ最適化とは？

インデクスを使用すれば必ずコストが良いのか？

コスト

インデクススキャンのコスト遷移

インデクスを使用しない方がコストが小さい場合も！

コスト

テーブルスキャンのコスト遷移

インデクスを使用した方がコストが小さい場

合はこの範囲！

ヒット率100%0

条件にもよるが１０数％が境目

合はこの範囲！

100%0

テーブルスキャンを選択するケース

インデクススキャンを選択するケース選択するケス


2.2 ＳＱＬ実行プラン決定方法～ルールベース～

ルールベース

インデクスの定義情報，SQLの指定内容に基づいて，プロダクションルール従って， SQLの各部分ごとにSQL実行プダクションルル従って， SQLの各部分ごとにSQL実行プランを選択していくことによってSQL全体を最適化。

インデクス定義CREATE UNIQUE INDEX インデクス1 ON 表1(列1,列2)

SQLSQLSELECT 列1,列2,列3 FROM 表1 WHERE 列1=値 and … ORDER BY 列1,列2

規則Aに従ってプランA-1を選択

規則Bに従ってプランB-3を選択

規則Cに従ってプランC-5を選択


2.3 ＳＱＬ実行プラン決定方法～コストベース～

コストベース

幾つかのアクセス手順の候補を生成し，SQL実行プランの各候補ごとにコスト（処理負荷）を計算・比較することによて採用するSQL実行プランを決定よって，採用するSQL実行プランを決定

アクセス手順1インデクスAを使用して・・・

リソースA負荷：1234リソースB負荷：2345

アクセス手順2インデクスB,Cを使用して


比較

アクセス手順3インデクスを使用せずに・・・



2.4 ＨｉＲＤＢのＳＱＬ実行プラン決定方法

ＨｉＲＤＢが採用している方式は？

Ｖ１(1994年)からコストベース方式を採用！

ルールベース（既定値）＋リアルコストベースルルス（既定値）＋リアルコストス

ハイブリッド方式！


2.5 コスト計算に使用する値

コスト計算に使用している主項目は？

・表の情報・ＣＰＵ単価・ロック処理単価・インデクスの情報・Ｉ／Ｏ単価・サーバ数・列の情報・通信単価・ヒット率など・列の情報・通信単価・ヒット率など

インデクス表/列

インデクスA～D 表A列A 列B

イデク E H 表B列C 列D

インデクスE～H 表B


2.6 コスト計算に使用する値～表の情報～

表の情報で使用している値は？

・行数・格納ページ数など

ペジ

物理構造データベースの入出力

表

表A

ページ

ページA

ペジB

スの入出力最小単位

行数

ページC

ページB

ページ数

ページD


2.7 コスト計算に使用する値～インデクスの情報～

インデクスの情報で使用している値は？

・異なるキー値の個数・インデクスページ数・インデクスの段数・インデクスの段数・行データのシーケンシャル度など

重複がインデクス

インデクスA

b b d d

インデクスキー値

異なるキー値の個数重複が少ないとインデクスの効果が高い！

インデクスの段数a b b c c c d d e

行データの

ページ

ページA 行デタのシーケンシャル度

行データd d e

a b b c c c

ジA

ページB ページ数


2.8 コスト計算に使用する値～列の情報① ～

列の情報で使用している値は？

・異なる値の個数・重複値の個数の最小数・重複値の個数の最大数・重複値の個数の最大数・ナル値の個数次ページに続く

列A 列B 列C 列D・・・・・・・・・ 1・・・・・・・・・ 2

重複値の個数の最小数

異なる値の・・・・・・・・・ 1・・・・・・・・・ 3・・・・・・・・・ 2・・・・・・・・・ 4

重複値の個数の最大数

異なる値の個数

・・・・・・・・・ 4・・・・・・・・・ 2・・・・・・・・・ 2・・・・・・・・・ NULL ナル値の個数


・・・・・・・・・ NULL

2.9 コスト計算に使用する値～列の情報② ～

列の情報で使用している値は？

・値の分布情報など

区間内の値の個数

区間内の異なる

データ件数を基に、最大分割数nで区間分割を行い、区間内の値の個数/異なる値の個数を分布情報化

異なる値の個数

区件数

列値の最小値列値の

最大値

区間

件数

値

最大値

区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

・・・


間１

間２

間３

間４

間５

間６

間n-5

間n-4

間n-3

間n-2

間n-1

間n

2.10 コスト計算に使用する値～区間分布情報～

重複データが多い場合は？

隣合う区間の全キー値が一つの場合、一区間に纏める

・分布情報容量圧縮探索範囲削減

全て同一の値一区間に纏める

・探索範囲削減

件数件数件数件数

異なる値の個数は全て1

異なる値の個数は1のまま

値区間

区間

区間

区間

区間

区間

値区間

区間

区間

区間


間１

間２

間３

間４

間５

間６

16

間１

間２

間３

間４

2.11 コスト計算に使用する値～ヒット率単一条件時～

分布情報をどのように使用するのか？

各区間内のインデクスキー値数、及び重複数を基に、SQLに指定した条件のヒット率を計算(仮定)。

SQL例）SELECT 列1,列2,列3 FROM 表1 WHERE 列1=‘HiRDB’

デタ‘HiRDB’が存在する区間 ‘HiRDB’のデタ件数は ‘HiRDB’のヒト率は

件数

データ‘HiRDB’が存在する区間・値の個数：１００・異なる値の個数：２０

全データ件数

‘HiRDB’のデータ件数は100/20=5件と仮定

‘HiRDB’のヒット率は5/10000=0.05%

件数全データ件数１００００

100

ヒット率が低いとインデクスを使用

値区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

区間

・・・インデクスを使用、ヒット率が高いとインデクス未使用


値間１

間２

間３

間４

間５

間６

間95

間96

間97

間98

間99

間100

17

2.12 コスト計算に使用する値～ヒット率複数条件時～

条件を複数指定している場合は？

条件式が互いに独立と仮定して、個別にヒット率を計算

SQL例）SELECT 列1 列2 列3 FROM 表1 WHERE 列1 ‘HiRDB’ AND 列2 ‘HITACHI’

‘HiRDB’& ‘HITACHI’では

SELECT 列1,列2,列3 FROM 表1 WHERE 列1=‘HiRDB’ AND 列2=‘HITACHI’

件数

100

列1の区間分布情報 ‘HiRDB’のヒット率が0.05%HITACHI では

0.0005%

値区区区区区区区区区区区区

・・・

100

値区間１

区間２

区間３

区間４

区間５

区間６

区間95

区間96

区間97

区間98

区間99

区間100

件数列２の区間分布情報 ‘HITACHI’のヒット率が1%

値

・・・

100

定義しているインデクスの中で、一番ヒット率が低いインデクスを採用

定義しているインデクスの中で、一番ヒット率が低いインデクスを採用

列1、列2を含んで定義しているインデクスの中から、ヒット率が一番

デ


値区間１

区間２

区間３

区間４

区間５

区間６

区間95

区間96

区間97

区間98

区間99

区間100

低いインデクスを採用低いインデクスを採用低いインデクスを採用

2.13 コスト計算に使用する値～区間分布情報～

ＨｉＲＤＢが最も精度を高められるケースは？

区間分割数が「100」の場合！⇒ヒット率精度誤差0.5%以内を保証

精度誤差が発生するケース：全データが1000件(‘a’が1件 ’b’が999件)

分布上は1データあたりの件数は

データ数は990件

種類数は( a が1件、 b が999件)⇒’a’のヒット率は1/1000=0.1%

あたりの件数はデータ/重複数=10/2=5件

種類数は’b’1件

区間分布情報を使うと・・・⇒’a’のヒット率は5件/1000=0.5%

データ数は１0件

値区区

データ数は１0件種類数は’a’’b’2件


間１

間２

19

2.14 算出しているコスト～種類～

算出しているコストの種類は？

・ＣＰＵコスト・Ｉ／ＯにかかるＣＰＵコストＩ／Ｏストなど・Ｉ／Ｏコストなど

サーバマシンCPUコスト I/Oコスト

DB

I/OにかかるCPUコスト


2.15 算出しているコスト～計算式～

計算式は？

インデクススキャンコストの計算式概要は下記

ＣＰＵコストＣＰＵコスト(インデクスヒット率,行数,プロセス数,インデクス段数などから算出)

＋Ｉ／ＯにかかるＣＰＵコスト（インデクスヒット率,行数,プロセス数,クラスタ率などから算出）Ｉ／Ｏト＋Ｉ／Ｏコスト（ランダムI/O時間(インデクス段数などから算出)＋ブロックI/O時間(インデクスページ数などから算出)）


2.16 ＳＱＬの世界の最適化項目

ＳＱＬの世界の最適化対象項目

・表、インデクス、列・・・・データ格納ページ数、インデクスキー格納ページ数・・・・異なる値のデータ個数、分布情報・・・

ＳＱＬの世界以外ＳＱＬの世界以外（マシン構成）の

最適化対象項目は？最適化対象項目は？


2.17 ＨｉＲＤＢシステムの構成①

ＨｉＲＤＢの構成は？

サーバマシン

■ HiRDB/シングルサーバ ■ HiRDB/パラレルサーバ(Shared-Nothing)

特定のデータは特定のサーバで参照

DBサーバマシン

クライアント

定参

DB

DB

クライアントクライアント

DB

DBクライアントクライアント

• 1台のサーバマシン上で動作• 構築が容易、運用もシンプル• 1台のサーバマシン上で動作• 構築が容易、運用もシンプル

• 複数台のサーバマシン上で動作• 並列処理が可能• 複数台のサーバマシン上で動作• 並列処理が可能


• スケールアップも可能• スケールアップも可能 • スケールアップとスケールアウトが可能• スケールアップとスケールアウトが可能

2.18 ＨｉＲＤＢシステムの構成②

ＤＢデータの処理方法は？

サーバマシン

■ HiRDB/パラレルサーバ(Shared-Nothing)

ケース1：1つのサバのデタのみ

使用していないこのマシンを有効活用すると

DB

1つのサーバのデータのみ参照するケース

用すると・・・

DB

クライアント

ケース1：複数のサーバのデータをケース２：複数のサーバのデータを

必要なデータのみ転送すると・・・

DBクライアント

複数のサバのデタを参照するケース複数のサバのデタを参照するケース(結合検索/ソート処理等)

マシン構成の世界の

検索したデータをいずれかのサバに転送し結合処理を行う

マシン構成の世界の最適化対象項目・結合/ソート処理サーバ(フロータブルサーバ)の決定

結合処理


サーバに転送し結合処理を行う (フタブルサ )の決定・サーバ間のデータ転送方法の決定

目次目次



3.1 ＨｉＲＤＢシステムの構成(再掲)


サーバマシン




クライアント

定参

DB

DB







3.2 データ取出サーバでのＳＱＬ処理

データ取出サーバでＳＱＬ処理を行う場合

サーバマシンデータ取出ソート処理

DB

データ取出サーバでI/Oおよびソート処理を行っている最中

処理なしサーバ

クライアント

る最中に、処理を行わないサーバが存在するケースあり処理なしサーバクライアント

ソート処理

処理なしサバ

クライアント

DB データ取出


3.3 フロータブルサーバ

フロータブルサーバとは？

負荷が高い結合処理、

サーバマシンデータ取出

負荷が高い結合処理、ソート処理用に割り当てるサーバ

DB

結合処理

クライアント

ソート処理

データ配置によらず

クライアント

結合処理負荷が低いサーバに

処理を任せる！負荷が低いサーバに

処理を任せる！デタ配置によらずサーバ間の負荷を均等にできる

クライアント

DB データ取出


3.4 割り当て方式の種類～専用サーバ～

専用サーバ割り当て方式


データ取出用以外のDB デタ取出用以外のサーバを候補に割り当て

クライアント

フロータブルサーバ

クライアント

フロータブルサーバデータ取り出し量が多い場合にサーバ間の

クライアント

DB データ取出

多い場合にサバ間の負荷分散効果大


3.5 割り当て方式の種類～全サーバ～

全サーバ割り当て方式


フロータブルサーバ兼務

DB

全サーバを候補に割り当て

クライアント


クライアント


ソート/結合処理の並列処理効果大

クライアント

DB

処理効果大

データ取出


フロータブルサーバ兼務

3.6 フロータブルサーバの決定方法

フロータブルサーバの決定方法は？

・台数決定方法⇒データ取出サーバ/フロータブルサーバ間での負荷分散・並列実行度を考慮し、フロータブルサーバでの処理時間が、データ取出サーバでのスキャン時間の４倍未満となるように決定なるように決定・対象のサーバ決定方法⇒候補サーバリストの中から、負荷を考慮して最適な候補サバリストの中から、負荷を考慮して最適なサーバを決定


目次目次



4.1 ＨｉＲＤＢシステムの構成(再掲)


サーバマシン




クライアント

定参

DB

DB







4.2 クライアント～サーバマシン間のデータ転送①

結合検索の場合のデータの流れは？


データ転送SQL例）SELECT 表A.顧客名,表B.顧客名FROM 表A,表B WHERE 表A.製品コード=表B.製品コード

DB

表A顧客名製品コード製品動画(BLOB型データ）

A 100 データA

表A 顧客名,製品コード顧客名,製品コード

クライアント

結果返却

表A顧客名

A 100 デタA

A 1000 データB

A 500 データC

表B

データ取出

結果返却

顧客名,製品コード

結合処理顧客名製品コード製品動画(BLOB型データ）

B 300 データD

B 10 データE

B 500 データC

表

クライアント

DB

表B各表のデータを処理対象のサーバマシンへ



データ転送

処対象サ漏れなく転送

4.3 クライアント～サーバマシン間のデータ転送②

もし、転送データの制御を行わなかったら？


データ転送SQL例）SELECT 表A.顧客名,表B.顧客名FROM 表A,表B WHERE 表A.製品コード=表B.製品コード

DB

表A顧客名製品コード製品動画(BLOB型データ）

A 100 データA

表A 顧客名,製品コード顧客名,製品コード

クライアント

結果返却

表AA 100 デタA

A 1000 データB

A 500 データC

表B

顧客名返却しない

返却する

データ取出

結果返却結合処理顧客名製品コード製品動画(BLOB型データ）

B 300 データD

B 10 データE

B 500 データC

表


返却しない

返却する

クライアント

DB

表B黄色部分では結合検索で致しないためクライアントには


返却する


データ転送一致しないため、クライアントには返却不要である顧客名データも転送

4.4 クライアント～サーバマシン間のデータ転送③

もし、転送データの制御を行わなかったら？


データ転送SQL例）SELECT 表A.製品動画,表B.製品動画FROM 表A,表B WHERE 表A.製品コード=表B.製品コード

DB

表A

製品動画,製品コード

顧客名製品コード製品動画(BLOB型データ）

A 100 データA

表A製品動画,製品コード

クライアント

結果返却

表A製品動画

A 100 デタA

A 1000 データB

A 500 データC

表B

返却しない

返却する

データ取出

結果返却結合処理顧客名製品コード製品動画(BLOB型データ）

B 300 データD

B 10 データE

B 500 データC

表

返却しない

返却する製品動画,製品コード

クライアント

DB

表BBLOB型等のサイズが大きいデタでは

返却する

製品動画,製品コード


データ転送サイズが大きいデータでは、通信量が膨大になる

4.5 クライアント～サーバマシン間のデータ転送④

ＨｉＲＤＢではどのように制御しているか？



DB

表AROWID 顧客名製品コード製品動画(BLOB型データ）

XXXXX1 A 100 データA

表A ROWID,製品コード ROWID,製品コード

データを一意に決定できる情報

クライアント

表AXXXXX1 A 100 データA

XXXXX2 A 1000 データB

XXXXX3 A 500 データC

表B

データ取出

結合処理表

ROWID,製品コード

ROWID 顧客名製品コード製品動画(BLOB型データ）

XXXXX1 B 300 データD

XXXXX2 B 10 データE

XXXXX3 B 500 データC

クライアント

DB

表BROWID,製品コード

XXXXX3 B 500 デタC

結合に必要なデータとROWIDのみを転送


データ転送ROWIDのみを転送

4.6 クライアント～サーバマシン間のデータ転送⑤


サーバマシンデータ取得


DB

表A

ROWIDROWID



表A

クライアント

表AXXXXX1 A 100 データA



表B

データ取得ROWID

結合処理表





クライアント

DB

表BROWID

結合処理後でヒットしたROWIDを転送し、必要な



データ転送

を転送し、必要なデータを取得

4.7 クライアント～サーバマシン間のデータ転送⑥




DB

表A製品動画

製品動画ROWID 顧客名製品コード製品動画(BLOB型データ）


表A

クライアント

表A製品動画

結果返却




表B

データ取出製品動画

結果返却表





クライアント

DB

表B製品動画


ROWIDから返却対象列を取得しクライアントへ返却


データ転送取得し、クライアントへ返却

4.8 クライアント～サーバマシン間のデータ転送⑦




DB

表A製品動画

製品動画HiRDBでは、列データのサイズとSQL処理内容を基に下

クライアント

表A製品動画

結果返却

ズとSQL処理内容を基に、下記から最適な方式を決定して制御している・最初から列デタを取得して転送

データ取出製品動画

結果返却・最初から列データを取得して転送・最後にROWIDから取得して転送

クライアント

DB

表B製品動画


データ転送

目次目次



5.1 ＬＩＭＩＴ処理とは？

ＬＩＭＩＴ処理とは？

SQLの検索結果のうち，先頭からn行だけを受け取る

顧客名 … … … … … …

検索対象表

ＬＩＭＩＴ指定なしの検索結果の場合

ＬＩＭＩＴ指定ありの

A

A

A

Ａ

顧客名売上 …

A 100

A 10

検索結果の場合検索結果の場合

先頭n行だけの検索結果

顧客名売上 …

A 100

A 10

・・・・・・・・・

Ａ

B

A 1000

Ａ 300

・・・・・・・・・

Ａ 0

検索結果 A 1000

B

B

顧客名’A’のデータを


顧客名デタを抽出する場合

5.2 ソート処理指定あり時のＬＩＭＩＴ処理①

ソート処理と組み合わせた場合には？

もし、ヒットした全データソート後に，先頭からn行だけを受け取ると制御だと・・・

顧客名 … … … … … …

不要なデータまでソート検索対象表



顧客名売上 …

A 0

A 10

A

A

A

Ａ

検索結果の場合検索結果の場合顧客名売上 …

A 0

A 10先頭n行だけの

検索結果 A 100

Ａ 300

・・・・・・・・・

Ａ 1000

・・・・・・・・・

Ａ

B

A 100検索結果

B

B

売上でソート


5.3 ソート処理指定あり時のＬＩＭＩＴ処理②


数件だけ枠を持っていて、範囲外であれば読み捨てる。範囲内であれば既存の最大値を捨ててソート

顧客名 … … … … … …

検索対象表上限～下限のデータのみソート



顧客名売上 …

A 0

A 10

A

A

A

Ａ

検索結果の場合検索結果の場合顧客名売上 …

A 0

A 10先頭n行だけの

検索結果 A 100・・・・・・・・・

Ａ

B

A 100検索結果

対象の範囲に収まらない B

B

対象の範囲に収まらないデータは読み捨て

(ex.売上データが300)


5.4 ソート処理指定あり時のＬＩＭＩＴ処理③


メモリ上でソートするケースと、作業表(※)上でソートするケースを制御

※ソート対象のデータサイズが作業表バッファサイズ未満であればメモリ上で処理

顧客名 … … … … … …

検索対象表

顧客名売上 …

メモリ上でソート

内部閾値に基づきA

A

A

Ａ

A 0

A 100

Ａ 300

・・・・・・・・・

Ａ 10000

内部閾値に基づき、メモリ使用量を考慮して、ソート処理領域を変更

サイズ<xxx

・・・・・・・・・

Ａ

B

Ａ 10000

顧客名売上 …

作業表上でソート

取得する先頭行が多くも B

B

顧客名売上

A 0

A 100

Ａ 300

・・・・・・・・・

サイズ≧xxx取得する先頭n行が多くても少なくても、同時に実行している全SQLで見ると効率がよい方法を選択


Ａ 100000

最後に

エンドユーザから見ると意識することが少ないSQLエンドユザから見ると、意識することが少ないSQL実行プラン生成。SQL実行時間遅いとエンドユーザは気になってしまうので、意識されないようにますます早く、技術者の方からは、早いけどどうなっているんだ？と気にしてもらえるようにします！もらえるようにします！

ご清聴ありがとうございました。


END

HiRDBのSQL実行プランはどのようにHiRDBのSQL実行プランはどのように決定しているのか？

/ /

株式会社日立製作所情報・通信システム社ITプラットフォーム事業本部開発統括本部ソフトウア開発本部 DB設計部

2014/06/18

ソフトウェア開発本部 DB設計部

成田正亮


[B15] HiRDBのSQL実行プランはどのように決定しているのか？by Masaaki Narita

Technology

Transcript of [B15] HiRDBのSQL実行プランはどのように決定しているのか？by Masaaki Narita