db tech showcase 2015
株式会社 日立製作所 情報・通信システム社
ITプラットフォーム事業本部 サービスイノベーション統括本部 IT基盤ソリューション本部 DB部
2015/06/12
Hitachi Advanced Data Binder
実践SQLチューニング方法
はじめに
＜本日のテーマ＞
超高速データベース「Hitachi Advanced Data Binder」での
SQLチューニング方法を、情報の取得から問題点を見つけて対策 するまでの流れと、チューニング事例をご紹介いたします。
１. Hitachi Advanced Data Binderの概要
２. SQLチューニング方法の概要
３. チューニング事例
Contents
１. Hitachi Advanced Data Binderの概要
２. SQLチューニング方法の概要
３. チューニング事例
Contents
1.1 Hitachi Advanced Data Binderの概要
Hitachi Advanced Data Binder プラットフォーム
Hitachi Advanced Data Binder プラットフォーム
日立ラックサーバ 日立ストレージ
超高速データベースエンジン
□自社従来比１００倍(*3)の検索性能を誇る、超高速データベースエンジン
Hitachi Advanced Data Binderを搭載。
□可用性の高い日立のサーバと高速ストレージをセット化。 (*1) 世界のトップを目指した先端的研究を推進することで、産業、安全保障等の分野における我が国の中長期的な国際的競争力、底力の強化を図るとともに、研究開発成果の 国民および社会への確かな還元を図ることを目的として創設された国の研究開発プログラム。 最先端研究開発支援プログラム(*1)において、国立大学法人東京大学が 推進している超高速データベースエンジンの研究開発(*2)の成果を利用して 日立が製品化したリレーショナルデータベースシステム。
1.2 Hitachi Advanced Data Binderプラットフォーム
高速データアクセス基盤
Hitachi Advanced Data Binder プラットフォーム
超高速データベースエンジン Hitachi Advanced Data Binder
（ＲＤＢＭＳ） 日立サーバ 日立ストレージ ＢＩ ツール 業務 アプリケーション センサー 稼働ログ 売上 ＳＮＳ 受発注 契約 データ ソース 収集 / 加工 多種データ 高速検索 価値を創造 大量データ ＤＷＨ
Hitachi Advanced Data Binder PFはDWHの中核を支えるDBサーバです □ 大量データのローディング処理を高速化
□ 多種多様なデータ結合処理(JOIN)を高速化
ＪＤＢＣ/ＯＤＢＣ/ＣＬＩ （ＳＱＬインタフェース）
1.2 Hitachi Advanced Data Binderの高速化技術
サーバ、ストレージの能力を最大限に使いきるソフトウェア技術。 ＤＢ検索(ＳＱＬ)処理を並列実行単位(I/O単位)に自動分割し高多重で実行。 タスク割当 検索処理 Ｉ/O完了待ち ディスクI/O サーバ ストレージ 【従来方式】 ：順序実行方式 【新方式】：非順序型実行原理(*2) 検索処理(μs) 同期I/O処理(ms) 【従来方式でのストレージアクセストレース】 【新方式でのストレージアクセストレース】 処理時間を大幅短縮 東京大学との超高速データベースエンジンの共同研究開発成果の製品化。 自社従来比約１００倍(*1)のデータ検索性能。 顧客情報 注文情報 明細履歴情報 検索処理(μs) 同期I/O処理(ms) サーバ ストレージ1.2 Hitachi Advanced Data Binderの高速化技術
非順序実行原理では、発行したＩ/Ｏを待たずに、次々にレコード処理を行うた め、並列度を高めやすい。レコード処理順序に依存しない集合演算や結合処 理が得意。
１. Hitachi Advanced Data Binderの概要
２. SQLチューニング方法の概要
３. チューニング事例
Contents
2.1 SQLチューニングの前に
画面のレスポンスが遅い・・・、こんな時にどうしますか？ データベース ＢＩサーバ 利用者： 画面のレスポンスが遅いなあ インデクスが効いていない？ 検索量が多すぎる？ etc・・・ DB管理者2.1 SQLチューニングの前に
問題箇所の切り分け データベース ＢＩサーバ 利用者： 画面のレスポンスが遅いなあ インデクスが効いていない？ 検索量が多すぎる？ etc・・・ DB管理者まずは、
端末・ＢＩサーバ・ＤＢサーバのどこで処理時間が
かかっているかを切り分けます
⇒ ＢＩサーバ、ＤＢサーバのログからSQL発行時刻、処理時間、 要求元へのリタン時刻等をもとに時間のかかっている箇所 を調査します ⇒ ＤＢサーバで処理時間がかかっていることを確認してから チューニングに着手しますSQLチューニングの基本的な流れ
2.2 SQLチューニングの流れ
対象SQLのアクセスパス(※)取得と アクセスパス観点の問題点の調査 チューニング対象SQLの特定 対策案の検討と検証 対象SQLの統計情報の取得と 統計情報観点の問題点の調査 要件クリア？ Ｙ Ｎ ※SQLの実行計画・実行プランをタイムスタンプ AP_name SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数
2015/06/01 06:35:12 adbsql 1 SELECT 266,948 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0
2015/06/01 06:35:12 adbsql 1 SELECT ADBUIDX01 ADBUIDX01BUF 120,202 100 0 0
2015/06/01 06:35:25 adbsql 2 SELECT 112,899 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0
2015/06/01 06:35:25 adbsql 2 SELECT ADBUTBL01 ADBUTBL01BUF 75 100 0 0
2015/06/01 06:37:55 adbsql 3 SELECT 23,822,936 1 ADBDIC ##ADBOTHER#0000004096 16 100 0 0
2015/06/01 06:37:55 adbsql 3 SELECT ADBUIDX01 ADBUIDX01BUF 14,760,202 100 0 0
2015/06/01 06:37:55 adbsql 3 SELECT ADBUTBL01 ADBUTBL01BUF 14,520,000 100 0 0
チューニング対象SQLの特定 ⇒SQL処理時間を調査し、画面レスポンスとSQL処理時間を比較して、 レスポンスに影響しているSQLを特定します
2.2 SQLチューニングの流れ
＜SQL処理時間の取得方法＞ HADBの統計解析コマンド（adbstat）でSQL文の統計情報を取得します HADBサーバ データベース adbstat -c sql -m '開始時刻','終了時刻' > log_adbstat_sql.csv DB管理者 ・・・ ・・・ ・・・ ・・・ ・・・ ・・・ ・・・対象SQLのアクセスパスの取得と調査 ⇒対象SQLのアクセスパスを取得して、適切なインデクスが使われているか といった、アクセスパス観点の問題点を調査します
2.2 SQLチューニングの流れ
＜ポイント＞ 以下のような点を調査します ・適切なインデクスが使用されているか HADBサーバ データベース adbsql -u ユーザID -p パスワード < SQL文テキスト.txt > log_adbsql.txt DB管理者 ＜アクセスパスの取得方法＞SQL実行コマンド(adbsql)のサブコマンド「#set opt report on type=all」で、 対象SQLのアクセスパスを取得します
#set opt report on type=all; select count(*) from T1 where C6='01'; SQL文テキスト.txt <<Tree View>> 1 QUERY : 1 2 SELECT STATEMENT 3 |-KEY SCAN(USER01.T1) 4 +-GROUPING <<Detail >> QUERY : 1 3 KEY SCAN(USER01.T1) INDEX NAME : T1_IDX03 INDEX TYPE : B-TREE
対象SQLの統計情報の取得と調査 ⇒対象SQLの統計情報を取得して、バッファへのアクセス要求回数や I/O回数といった、統計情報観点の問題点を調査します
2.2 SQLチューニングの流れ
＜ポイント＞ ＜統計情報の取得方法（SQL処理時間の取得と同じ）＞ HADBの統計解析コマンド（adbstat）でSQL文の統計情報を取得しますタイムスタンプ AP_name SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数
2015/06/01 06:40:29 adbsql 4 SELECT 266,948 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0
2015/06/01 06:40:29 adbsql 4 SELECT ADBUIDX01 ADBUIDX01BUF 120,202 100 0 0
2015/06/01 06:41:07 adbsql 5 SELECT 112,899 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0
2015/06/01 06:41:07 adbsql 5 SELECT ADBUTBL01 ADBUTBL01BUF 75 100 0 0
2015/06/01 06:42:31 adbsql 6 SELECT 23,822,936 1 ADBDIC ##ADBOTHER#0000004096 16 100 0 0
2015/06/01 06:42:31 adbsql 6 SELECT ADBUIDX01 ADBUIDX01BUF 14,760,202 100 0 0
2015/06/01 06:42:31 adbsql 6 SELECT ADBUTBL01 ADBUTBL01BUF 14,520,000 100 0 0
HADBサーバ データベース adbstat -c sql -m '開始時刻','終了時刻' > log_adbstat_sql.csv DB管理者 ・・・ ・・・ ・・・
対策案の検討と検証 ⇒見つけた問題点の対策案を検討し、効果を検証します
2.2 SQLチューニングの流れ
＜対策案の検討＞ 問題点によって対策方法は様々ですが、例えば以下のような方法があります。 ■ パラメタ設定の変更 ・バッファ面数の割当の変更・拡張 ・１ＳＱＬを処理する多重度の拡張 ■ 定義の変更 ・インデクスの構成列の追加、並び順の変更 ・インデクスの追加 ■ SQL文の書換え ・ジョインする順番の変更、ジョイン方式の変更 ・副問合せの書換え（ジョイン化） ・グループ化処理のタイミングの変更 対策したSQLを実行して、再度統計情報を取得・変更前と比較して 対策の効果を検証します。１. Hitachi Advanced Data Binderの概要
２. SQLチューニング方法の概要
３. チューニング事例
Contents
3.1 事例1 －グループ化処理のタイミングー
4月分の売上集計するSQLで、名称を付加するためにマスタ表をジョインして いるが、処理時間がかかっている。改善するポイントがありますか？ １対１ジョインのはずなのに ずいぶん時間がかかるなあ？ select U.大分類 , U.商品コード , SUM(U.金額) , max(S.商品名)from 売上TBL U LEFT JOIN 商品TBL S on U.大分類=S.大分類
and U.商品コード=S.商品コード
where U.日付 between '2014/04/01' and '2014/04/30' and U.大分類 in ('01', '02', '03', '04)
3.1 事例1 －グループ化処理のタイミングー
＜ポイント１＞
検索の対象行数がどのくらいあるか、ざっくりと求めて、統計情報の DBアクセス量（バッファ要求回数）と比べてみましょう。
SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 0
1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0
2 SELECT 7,355,184 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
2 SELECT ADBUIDX01 ADBUIDX01BUF 4,241,679 100 0 0
2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0
４月分の売上データは410万件あります。 それに対して、統計情報のインデクス要求回数は1645万回。約４倍です。 →ネストジョインの内側である商品TBLを検索する際、インデクス段数が ３段として、売上データ１件当たり商品TBLのインデクスを３回参照。 売上ﾃﾞｰﾀ００１ 売上ﾃﾞｰﾀ００２ 売上ﾃﾞｰﾀ００３ ・・・ 売上TBL 商品００１ 商品００２ 410万件＋410万件×3段≒1600万回 売上TBL 商品TBL 商品INDEX (３段） ・・・
3.1 事例1 －グループ化処理のタイミングー
＜改善策＞
集計前の売上データには商品コードが重複するので、集計後に商品TBLを ジョインするように変更します（グループ化処理を先に実施）
SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 0
1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0
2 SELECT 7,355,184 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
2 SELECT ADBUIDX01 ADBUIDX01BUF 4,241,679 100 0 0
2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0
本改善でインデクスへの要求回数が1645万回→424万回に削減できました。
select U.大分類, U.商品コード, U.金額, S.商品名 from （select 大分類, 商品コード, SUM(金額)
from 売上TBL
where 日付 between '2014/04/01' and '2014/04/30' and 大分類 in ('01', '02', '03', '04')
group by 大分類, 商品コード ) U
left join 商品TBL S
on U.大分類=S.大分類 and U.商品コード=S.商品コード
書換えたSQL の統計情報
・・・
SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 0
1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0
2 SELECT 8,823,041 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
2 SELECT ADBUIDX01 ADBUIDX01BUF 4,332,134 100 0 0
2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0
3.2 事例2 －ジョインの順序ー
＜ポイント２＞ 事例1の改善策として、ジョイン順序を変更する方法もあります。 事例1は売上TBLを起点にしていましたが、商品TBLの方が件数が少ない ため、商品TBLを起点としたジョインに変更します。 本改善でインデクスへの要求回数が1645万回→433万回に削減できました。select U.大分類, U.商品コード, U.金額, S.商品名
from 商品TBL S
INNER JOIN 売上TBL U
on U.大分類=S.大分類 and U.商品コード=S.商品コード where U.日付 between '2014/04/01' and '2014/04/30' and S.大分類 in ('01', '02', '03', '04')
group by U.大分類, U.商品コード
書換えたSQL の統計情報
・・・
3.3 事例3 －ジョイン方式の変更ー
商品ＴＢＬ 売上ﾃﾞｰﾀ００１ 売上ﾃﾞｰﾀ００２ 売上ﾃﾞｰﾀ００３ 売上ﾃﾞｰﾀ００４ 売上ﾃﾞｰﾀ００５ ・・・ 商品００１ 商品００２ 商品００３ 商品００４ 商品００５ ・・・ 売上ＴＢＬ 適切なインデクスを使っているけど ジョインがなんだか遅いなあ？ 商品TBLと売上TBLの突き合わせをしたいが、両方とも件数が多くて 処理時間がかかってしまう。改善するポイントがありますか？3.3 事例3 －ジョイン方式の変更ー
内側表・外側表の件数に 応じて結合回数が増加 ＜ネストジョイン方式＞ 商品ＴＢＬ 売上ﾃﾞｰﾀ００１ 売上ﾃﾞｰﾀ００２ 売上ﾃﾞｰﾀ００３ 売上ﾃﾞｰﾀ００４ 売上ﾃﾞｰﾀ００５ ・・・ 商品００１ 商品００２ 商品００３ 商品００４ 商品００５ ・・・ 売上ＴＢＬ ＜ハッシュジョイン方式＞ ハッシュテーブル 商品TBLを１回ｽｷｬﾝ してﾊｯｼｭﾃｰﾌﾞﾙに 登録 売上TBLを１回ｽｷｬﾝ してﾊｯｼｭﾃｰﾌﾞﾙで突き 合わせ 商品ＴＢＬ 売上ﾃﾞｰﾀ００１ 売上ﾃﾞｰﾀ００２ 売上ﾃﾞｰﾀ００３ 売上ﾃﾞｰﾀ００４ 売上ﾃﾞｰﾀ００５ ・・・ 商品００１ 商品００２ 商品００３ 商品００４ 商品００５ ・・・ 売上ＴＢＬ ＜ポイント３＞ 大量データを対象とする場合、内側表・外側表の件数に応じて、繰り返し 処理の回数が増えるネストジョイン方式よりも、両表を１回ずつスキャン するハッシュジョイン方式が優位となる場合があります。3.4 事例4 －演算を含むIN(副問合せ)の書換えー
select 大分類 , 商品コード , SUM(金額) from 売上TBL
where 日付 between '2014/04/01' and '2014/04/30' and 大分類 || 商品コード in (select 大分類 || 商品コード from 商品TBL where メーカーコード＝'000456' ) group by 大分類 IN(副問合せ)を使うと なんか遅い気がするなあ？ あるメーカーの商品の4月1日分の売上集計をしたいが、IN副問合せを使うと 処理時間がかかってしまう。改善ポイントはありますか？
＜ポイント４＞
演算を含むIN(副問合せ)はインデクスで評価できずに、思わぬ処理時間 がかかってしまうことがあります。
SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数 1 SELECT 58,134,960 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
1 SELECT ADBWRK ADBWRK 54,657,604 100 0 0
1 SELECT ADBUIDX01 ADBUIDX01BUF 137,394 100 0 0
1 SELECT ADBUTBL01 ADBUTBL01BUF 137,185 100 1 0
2 SELECT 1,417,099 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
2 SELECT ADBUIDX01 ADBUIDX01BUF 548,355 100 0 0
2 SELECT ADBUTBL01 ADBUTBL01BUF 273,974 100 0 0
演算を含むIN(副問合せ)は、副問合せの結果を作業表に格納して、 主問合せの１件ごとに、作業表と突き合わせて評価します。 →副問合せ結果（あるメーカの商品数）は４００件あり、4/1の売上データは 136000件あります。 →400件×136000件＝5400万回の突き合わせが行われます。 統計情報からも、作業表のバッファに5465万回の要求回数をだしており、 この突き合わせに時間がかかっていることがわかります。
3.4 事例4 －演算を含むIN(副問合せ)の書換えー
・・・＜改善策＞ 演算を含むIN(副問合せ)は、外への参照を使ったEXISTS述語で書き換えると 効率的に検索できるケースが多いです。 select 大分類 , 商品コード , SUM(金額) from 売上TBL U where 日付 ＝ '2014/04/01' and EXISTS( select * from 商品TBL where メーカーコード＝'000456'
and 大分類=U.大分類 and 商品コード=U.商品コード )
group by 大分類 , 商品コード
SQL# SQL_type SQL時間[μ秒] ﾌｪｯﾁ行数 DBエリア名 バッファ名 要求回数 ﾊﾞｯﾌｧﾋｯﾄ率 read回数 write回数 1 SELECT 58,134,960 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
1 SELECT ADBWRK ADBWRK 54,657,604 100 0 0
1 SELECT ADBUIDX01 ADBUIDX01BUF 137,394 100 0 0
1 SELECT ADBUTBL01 ADBUTBL01BUF 137,185 100 1 0
2 SELECT 1,417,099 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0
2 SELECT ADBUIDX01 ADBUIDX01BUF 548,355 100 0 0
2 SELECT ADBUTBL01 ADBUTBL01BUF 273,974 100 0 0
書換えたSQL の統計情報 本改善で、作業表(ADBWRK)へのアクセスそのものがなくなり、5467万回
3.4 事例4 －演算を含むIN(副問合せ)の書換えー
・・・ ・・・3.5 事例5 －テーブルスキャンの活用ー
インデクスはちゃんと 使っているんだけどなあ？
B-Treeインデクスはちゃんと使っていて、絞り込みも期待できるはずだけど なんとなく遅い気がします。改善ポイントはありますか？
3.5 事例5 －テーブルスキャンの活用ー
データ部 B-treeインデクス 売上TBL B-treeインデクス で絞り込み ランダムI/O ＳＱＬ検索 データ部 売上TBL ＳＱＬ検索 ＜インデクス経由の検索＞ ＜テーブルスキャン＞ ＜ポイント５＞ ビッグデータの場合、B-Treeインデクスを適切に使用して、条件も絞り込める （母体全体に対する比率として）場合でも、件数そのものが膨大なため、 インデクス経由のランダムI/Oよりも、テーブルスキャンが優位な場合が あります。 ヒント句でテーブル スキャン指定１. Hitachi Advanced Data Binderの概要
２. SQLチューニング方法の概要
３. チューニング事例
Contents
4．おわりに
１．超高速データベースエンジンとは
Hitachi Advanced Data Binderプラットフォームと高速化の技術について 概要をご説明しました。 ２．SQLチューニング方法の概要 SQLチューニング方法を、問題のSQLの特定から問題点の調査、対策案の 効果の検証までをご説明しました。 ３．チューニング事例 実際に現場で適用した際のチューニング事例をいくつかご紹介 統計情報の結果も併せて、定量的に効果を検証しました。
株式会社 日立製作所 情報・通信システム社
ITプラットフォーム事業本部 サービスイノベーション統括本部 IT基盤ソリューション本部 DB部
Hitachi Advanced Data Binder 実践SQLチューニング方法
2015/06/12
山口 健一
