InterSystems Symposia 2014

Caché SQL 最新情報
インターシステムズジャパン株式会社
セールスエンジニア（テクニカル・コンサルタント）
堀⽥ 稔

アジェンダ
• Caché SQL概要• インデックスの種類• SQL統計情報とクエリ・プラン• チューニングとヒント• ビットマップインデックスの圧縮• インデックスの追加・再構築の新機能

1

1

InterSystems Symposia 2014

Caché SQL概要

2

ダイレクト・アクセス vs SQLアクセス• ダイレクト・アクセス– データの取得⽅法をプログラムで記述する(How)。
– プログラムのロジックを改善させることで、パフォーマンスを向上させる。
– プログラマによる柔軟なコントロール。
– インデックスの⼀貫性、その他データモデルの⼀貫性を維持するのはプログラマの責任。

• SQLアクセス– どんなデータを取得したいかを、SQLで記述する(What)。
– オプティマイザが、より良いパフォーマンスの実⾏プランを計算・推測し、コードを⽣成。
– チューニングは、基本的に、オプティマイザに情報を与えることで⾏う。
– インデックスや、その他制約の⼀貫性は、システムが⾃動的に維持。

32

InterSystems Symposia 2014

Caché SQL 概念図

Caché SQL 実⾏の仕組み
Caché Object Script
SQL⽂ 埋め込み
SQL

クラス
クエリ

ダイナミック
クエリ

xDBC
クライアント

クエリプラン

コンパイル
OBJコードインデックス

xDBC

キャッシュド・クエリ

辞書

アクセス

グローバル変数(データ、インデックス）
Caché
システム

^MyPackage.TableD(1) = $LB(“”, “abc”, 1)…
^MyPackage.TableI(“Idx”, “abc”, 1) = “”4インデックスの種類

53

InterSystems Symposia 2014

インデックス

インデックスの種類• 通常のインデックス• ビットマップインデックス• ビットスライスインデックス• 複合インデックス（複数フィールド）6

通常のインデックス
インデックス名•ユニークキーなど、どんなフィールドにも使⽤可能•

その他のフィールドの値（インデックス対象となっているフィールド以外のフィールドの値）を、保持することが可能•

パフォーマンスは安定している値

ID

その他フィールドの値（オプション）74InterSystems Symposia 2014

ビットマップインデックス
001000100……………10011 (64000ビット)

8ビットマップインデックス• コンパクト（1ノードに64000⾏分の情報）• フィールドのデータの値が、概ね10000種類以下であれば最も良い選択肢 （Uniqueインデックスでは使⽤できない）• 複雑な条件の組み合わせを、⾼速なビット演算に置き換えられる• データ更新のパフォーマンスに対するインパクトが⼩さい• DELETE, INSERTを繰り返すテーブルでは、パフォーマンスが劣化するケースがある– 改善の⽅法に付いては後述• IDKeyが整数でないテーブルでは使⽤できない
95

InterSystems Symposia 2014

複合インデックス

複数フィールドに対するインデックス

State,GenderSSN

特定の条件のクエリが繰り返される場合、⾮常に有効

10

SQL統計情報とクエリ・プラン

116InterSystems Symposia 2014

SQL実⾏時統計％SYS.PTools.SQLStats• SQLを含むメソッドやルーチンをコンパイルしたOBJや、キャッシュド・クエリに、SELECT⽂の実⾏時間に関する統計情報を収集するコードを⽣成する。
• コードの⽣成や、収集する統計の詳細レベルを選択できる。
• 時間のかかっているクエリ、グローバル変数の参照が多いクエリを特定できる。
• 以下の数値を集計: Global Refs, Commands Executed, Time, RowCount, クエリの実⾏
回数.
• %SYS_PTools.SQLStatView で、すべての数値を確認できる。

12

クエリパフォーマンス統計の収集• 管理ポータル： [システム]→[SQL実⾏時統計情報]• $SYSTEM.SQL.SetSQLStats() もしくは $SYSTEM.SQL.SetSQLStatsJob()

13

7

InterSystems Symposia 2014

クエリパフォーマンス統計の収集

キャッシュド・クエリに収集⽤のコードが埋め込まれる

変数 i%%CursorDatad(5)に、統計情報収集設定の値がセットされている

14

クエリパフォーマンス統計の収集

統計情報の確認158

InterSystems Symposia 2014

インデックス使⽤統計

効率の悪いインデックスや必要なインデックスを探す16クエリ・プラン

Stateのビットマップで絞る
マスタマップをループしながらGenderをチェック
17

9InterSystems Symposia 2014

クエリ・プランビットマップインデックスの追加後StateビットマップとGenderビットマップのANDで絞る

18

クエリ・プラン

統計情報による⽐較

19

10

InterSystems Symposia 2014

クエリ・プラン複合インデックスとの⽐較
Index StateGenderIdx On (State, Gender);

20

（参考）

より選択性の⾼い場合はビットマップが有効

Stateビットマップ & Genderビットマップ

State 通常のインデックス

21

11InterSystems Symposia 2014

チューニングとヒント

22

クエリのチューニング

TuneTableユーティリティ• TuneTableは、テーブルの実データからテーブルのサイズや、データの特性を記録し、クエリ・プラン⽣成の元となるパラメータを計算する。
• $SYSTEM.SQL.TuneTable() コマンド、もしくは、管理ポータルから実⾏• 実データが存在する状態で実⾏• TuneTableの結果をエクスポートし、他の環境にインポートすることも可能• パラメータ– EXTENTSIZE：テーブルに含まれるデータの⾏数– SELECTIVITY：フィールドの値を指定することで選択される⾏の割合（性別なら50％など..）– BLOCKCOUNT: 各マップ(Master, Index1, …)のブロック数

23

12

InterSystems Symposia 2014

クエリのチューニング

「外れ値」のSELECTIVITY

あるフィールドで、⼤きく偏りのある値を検出

右の例では、State=ʻMAʼが全体の33%
その他の値のSELECTIVITY=1.37%

24

ヒント• JOINの順序• インデックスの選択• サブクエリ• UNION / OR

• 本来は、SQLで書かなくても、Caché Object Scriptで…

2513

InterSystems Symposia 2014（参考）サブクエリ
SELECT g.city, t.month, AVG(s.profit) FROM geog g, sales s, time t
WHEREs.gid = g.gid AND s.tid = t.tid AND
s.pid NOT EXISTS (SELECT p.pid FROM products pWHERE s.pid = p.pid AND p.color = 'Pink')
GROUP BY g.city, t.month;• サブクエリが、salesテーブルに含まれる p.pidの数だけ繰り返される可能性

26

ヒント• %ALLINDEX – JOIN順の最初のテーブルに対して、適⽤可能なすべてのインデックスを使⽤する。WHERE句に%NOINDEXを指定して、明⽰的に例外を指定可能。
•• %NOFLATTEN – サブクエリのFROM句に指定して、サブクエリを”メインクエリ”の条件に変換することを抑⽌する。
•• %NOSVSO – – サブクエリのFROM句に指定して、“Set-Valued SubqueryOptimizationを抑⽌する。SVSO: [NOT] EXIST または[NOT] IN サブクエリの条件から、⼀時インデックスを作成して、”メインクエリ”の条件指定に変換する。
• %NOUNIONOROPT – OR ⇔ サブクエリでのUNION 変換による最適化を抑⽌する。
•• %NOTOPOPT – TOP … ORDER BYにおいて、最初の⾏への時間を優先する最適化を抑⽌し、全体の結果セット取得時間を優先する。
2714

InterSystems Symposia 2014

ヒント
• %FIRSTTABLE – 指定したテーブルを最初にJOINする。
• %FULL – すべてのJOIN順を計算し、最適なプランを算出する (コンパイル時間が⻑くなる)。ストアドプロシージャ内のクエリなどで有効な場合がある。
•• %IGNOREINDEX – 指定されたインデックスを使わない。
•• %INORDER – FROM句に指定されたテーブルの順序でJOINする。
• %STARTTABLE – FROM句の最初のテーブルからJOINする。
• %NOINDEX –指定したWHERE句に対して、インデックスを使⽤しない。
28

ビットマップインデックスの圧縮

2915

InterSystems Symposia 2014ビットマップインデックスが遅くなる理由• グローバル変数には、64000⾏分のビットが含まれる。
• 約8KBを占める。
• 1⾏削除すると、1のビットを0に変更する。
• すべてのビットが0になれば、その「チャンク」は必要ない。
• 検索実⾏時、このようなすべてが0の「チャンク」からも、1のビットを探す。
• このような0の「チャンク」は、ビットマップのすべて値について積み重なる。

ビットマップインデックス
001000100……………10011 (64000ビット)

31

16InterSystems Symposia 2014

ビットマップインデックス

0ばかりのチャンク

000000000……………000000 (64000ビット)

000000000……………000000 (64000ビット)

32

ビットマップ圧縮ユーティリティ%SYS.Maint.Bitmap• Cache 2014.2の新機能• 稼働中のシステムでも動作• ビットマップを圧縮もしくは、不要なチャンクを削除• メソッド:– d ##class(%SYS.Maint.Bitmap).Namespace("Samples",1,1,"2014-01-17 09:00:00")– d ##class(%SYS.Maint.Bitmap).OneClass("BitMap.Test",1,1)

17

InterSystems Symposia 2014

ビットマップ圧縮の例
SAMPLES>d ##class(GS.Compact).Populate(1000000)
Global - GS.CompactI
1.

Blocks - 34

Bytes - 250,901

DELETE FROM GS.Compact WHERE ID < 750000
Global - GS.CompactI

Blocks - 46

Bytes - 344,747

SAMPLES>d ##class(%SYS.Maint.Bitmap).OneClass("GS.Compact",1,1)
Class: GS.Compact Start Time: 2014-03-13 14:45:29
Global: ^GS.CompactI("$Compact") was compressed: 93.87 %Global: ^GS.CompactI("GenderIdx") was compressed: 70.48 %Compression time in seconds: 0
Global - GS.CompactI

Blocks - 12

Bytes - 80,066

インデックス追加・再構築の新機能

35

18

InterSystems Symposia 2014

“ホット インデキシング”

稼働中のシステムにインデックスを追加する• 稼働中システムにインデックスを追加するのはいくつかの問題がある– クエリが有効でなくなる– インデックスの再構築には時間がかかる– ダウンタイムの必要• ”ホット インデキシング”はこれらの問題を解決する– インデックス追加時でも、クエリは有効– インデックスの構築を⾼速に– ユーザはシステムを継続的に使⽤できる• 2つの新技術– マップの選択可・不可の設定– ⾼速なインデックス構築

マップの選択可・不可を設定する$SYSTEM.SQL.SetMapSelectability()• コンパイラに、指定インデックスを無視させる• 間違った検索結果を⼼配することなく、構築中のインデックスを保持できる• INSERT, UPDATE, DELETEで、インデックスは最新状態に保たれる

19

InterSystems Symposia 2014

インデックスを⾼速に構築する新しいAPI• 新しいクラス: %Library.IndexBuilder– 新機能を使うために継承する• パラメータ: INDEXBUILDERFILTER– クラスに追加して、新しいインデックス名を指定する• API:– Do ##class(Package.Class).%ConstructIndicesParallel()

インデックスを⾼速に構築する%ConstructIndicesParallel• インデックスの構築に、複数のジョブを利⽤する。
• インデックスの構築対象となるIDの範囲を指定できる。
– 新しい値はインデックスする必要がない• ジャーナルをOFFにすることにより、インデックス構築のスピードを向上させることができる。

20

InterSystems Symposia 2014

稼働中システムへの新しいインデックスの追加

2014.1の場合

1. クラスのスーパークラスに%Library.IndexBuilderを追加
2. INDEXBUILDERFILTERパラメータをクラスに追加
3. インデックスを追加
4. このマップのselectabilityを0に–

w $SYSTEM.SQL.SetMapSelectability(クラス名,インデックス名,0)5. コンパイル
6. インデックスを構築
7. このマップのselectabilityを1に– W $SYSTEM.SQL.SetMapSelectability(クラス名,インデックス名,1)

8. 再コンパイル• この⼿順は%Dictionary.ClassDefinitionを使って、プログラム的に実⾏可能“ホット インデキシング”の将来

2014.2では

1. %BuildIndices()が、%IndexBuilderの機能を受け継ぐ
2. %IndexBuilderクラスの継承や、INDEXBUILDERFILTERパラメータの追加は必要な
くなる21InterSystems Symposia 2014

その他の最新機能 (2014.1)• Unified trigger– SQLトリガー(Insert/Update/Delete)が、Caché Objectアクセス経由でも実⾏される。
– %On~ メソッドは引き続きサポートされる。

• INSERT or UPDATE– INSERT OR UPDATE⽂は、レコードが存在しない場合はINSERT、存在する場合はUPDATEと同様の動作を⾏う。
– レコードの存在チェックは、Uniqueキーによる判断される。

その他の最新機能 (2014.1)• Push Subquery Conditions into UNION Legs
SELECT Name,Age FROM(SELECT Name,Age FROM Sample.Person
UNION SELECT Name,Age FROM Sample.Employee)
WHERE Age IN (SELECT TOP 5 Age FROM Sample.Employee WHERE Age>55 ORDER BY Age)

•4322

InterSystems Symposia 2014

パラレル クエリ– %PARALLEL
SELECT SUM(amountofsale) FROM %PARALLEL holefoods.salestransaction
Query Plan (Relative cost = 3560400)
Call module B.
Output the row.
module B
Read master map HoleFoods.SalesTransaction.IDKEY, looping on ID. For each row:
Read master map HoleFoods.SalesTransaction.IDKEY, looping on ID.
For each row:
Send query to a separate process, piping results to temp-file A:
SELECT sum(T1.AmountOfSale) FROM %NOPARALLEL HoleFoods.SalesTransaction T1
WHERE T1.ID<=?
Send query to a separate process, piping results to temp-file A:
SELECT sum(T1.AmountOfSale) FROM %NOPARALLEL HoleFoods.SalesTransaction T1
WHERE T1.ID>? AND T1.ID<=?
Send query to a separate process, piping results to temp-file A:
SELECT sum(T1.AmountOfSale) FROM %NOPARALLEL HoleFoods.SalesTransaction T1
WHERE T1.ID>? AND T1.ID<=?
Send query to a separate process, piping results to temp-file A:
SELECT sum(T1.AmountOfSale) FROM %NOPARALLEL HoleFoods.SalesTransaction T1
WHERE T1.ID>?
Read temp-file A, looping on a counter.
For each row:
Accumulate the sum([value]).
44

Q&A

45

23

InterSystems Symposia 2014

Caché SQL 最新情報24