本稿はデータベースソフトウェア「SQL Anywhere」およびデータベース全般に関する英語ドキュメントを翻訳する形で提供しています。図など、部分的に英語のままになっていますが、製品のSQL Anywhere自体は完全に日本語化されていますのでご安心ください。
SQL Anywhereはバージョン8.0.2以来、クラスタードインデックスをサポートしてきました。SQL Anywhereにおいて、クラスタードインデックスと非クラスタードインデックスの間に物理的な違いはありません。また、参照整合性制約のメンテナンスのために暗黙的に作成されるインデックスなども含め、すべてのインデックスはクラスタ化することができます。さらに、ALTER INDEX文を使うことで、クラスタ化の属性を追加または削除できます。ただ、このクラスタ化はあくまで検索の''手がかり''であり、クエリがORDER BY句を含む場合はやはりソート処理が必要です。
インデックスがクラスタ化されている場合（ちなみに宣言できるクラスタードインデックスの数は1つのテーブルにつき1つまでです）、ローが最初に挿入される際、データベースサーバは、テーブルページ内のローの物理的並び順を、クラスタードインデックスにおけるインデックスエントリの並び順にできるだけ一致させようとします。このようなクラスタードインデックスの利点は言うまでもなく、サーバがレンジスキャンの際にクラスタ化されたこの性質を活用できることです。クエリ処理時にクラスタードインデックスによる検索を行うと、読み込まなければならないテーブルページの数を最小限に抑えることができます。
クラスタ化に関する統計情報
SQL Anywhereバージョン10からは、インデックスがCLUSTEREDとして宣言されているかどうかに関係なく、データベース内の各インデックスのクラスタ化に関する統計がサーバによって管理されるようになりました。こうした統計情報はSYS.SYSPHYSIDXシステムビューで見ることができ、また次のようなクエリを使って検索できます。
1 SELECT tbl.creator, tbl.table_name, ix.table_id, ix.index_name, 2 COALESCE((IF tbl.clustered_index_id = ix.index_id THEN 'Y' 3 ELSE 'N' ENDIF), 'N') AS CLUSTERED, 4 pix.depth, pix.seq_transitions, 5 pix.rand_transitions, pix.rand_distance, 6 pix.key_value_count, pix.leaf_page_count 7 FROM SYS.SYSIDX ix JOIN SYS.SYSPHYSIDX pix ON (ix.table_id = pix.table_id 8 AND ix.phys_index_id = pix.phys_index_id ) 9 JOIN SYS.SYSTAB tbl ON (tbl.table_id = ix.table_id) 10 WHERE tbl.creator = 1 AND pix.depth > 1 11 ORDER BY tbl.table_name;
SYS.SYSPHYSIDXシステムビューが表示する統計情報は、データベースの最新チェックポイントの時点のものです。
上記のクエリに見られる値のうち、depth、key_value_count、leaf_page_countの3つを理解するのは簡単です。depthはインデックスのレベル数を、key_value_countは個別のキー値の数を、そしてleaf_page_countはインデックス内のリーフページの数を表しています。その他の値、すなわちseq_transitions、rand_transitions、そしてrand_distanceは次のように定義されます。
あるインデックスの中に、辞書式順序で隣接した2つのインデックスエントリがあるとします。これに対応する2つのベーステーブルローが同じテーブルページにある場合、この2つのインデックスエントリはゼロトランジション（zero transition）であると呼ばれます。つまり、遷移の必要がないという意味です。一方、2つのベーステーブルローが隣接するテーブルページに現れる場合はシーケンシャルトランジション（sequential transition）が必要になります（上記クエリのseq_transitions）。もっと離れたテーブルページに現れる場合はランダムトランジション（random transition）が必要になります（上記クエリのrand_transitions）。また、このようにローを隔てるテーブルページの数はランダムディスタンス（random distance）と呼ばれます（上記クエリのrand_distance）。
こうした統計情報に従って、クエリオプティマイザは各インデックスのクラスタ化の状態（クラスタ化の宣言がされているかどうかは不問）を評価し、コスト見積もりを修正できます。次の例を見てください。
画面の4、5、6行目のインデックスは、約5,000,000のローを含む同じベーステーブルに対するものです。まず4行目の、4,956,540の個別のキー値を含むインデックスに注目してください。このインデックスは、クラスタ化の宣言はされていませんが、かなりクラスタ化された状態にあります。このことは、シーケンシャルトランジションが41,747であるのに対し、ランダムトランジションがたった5,134しかないことから判断できます（ゼロトランジションの数は不明）。このような統計情報に基づいて、クエリオプティマイザはクエリ処理実行時のクラスタ化プロパティを仮定し、このインデックスを利用してデータにアクセスする際に読み込まれるテーブルページの数を最小限に抑えます。
逆に今度は、6行目のクラスタ化が宣言されているインデックスを見てみましょう。このインデックスは厄介です。というのも、シーケンシャルトランジションが6,123しかないのに、ランダムトランジションが4,806,149もあります。つまりこのインデックスは、真のクラスタ状態からは程遠いものです。ランダムディスタンスの値をキー値の総数で割って平均をとると、隣接するインデックスエントリの各ペアは2ページ離れたベーステーブルのローを指し示すことになります。このような余分な取得コストも、オプティマイザがこのインデックスを利用したアクセスプランのコストを算定する際に考慮に入れられます。したがって、データベース管理者は、REORGANIZE INDEX文、もしくはALTER INDEX REBUILD文を使ってこのインデックスを再編成すべきかもしれません。
5行目のインデックスでは、シーケンシャルトランジションとランダムトランジションの数がほぼ同じで、クラスタ化されたローとクラスタ化されていないローが同程度の割合で混在していることがわかります。このような場合でも、インデックスの再編成をすることは、6行目のインデックスほどの効果は期待できないとはいえ、有用だと考えられます。
インデックスの密度とスキュー
前述のインデックスに関する統計情報は、インデックスのメンテナンス作業の実行中に算出されるので、SYS.SYSPHYSIDXシステムビュー上ですぐに利用できます。SQL Anywhereサーバは、この他にも3つの統計値を取得できますが、これらの値の測定にはインデックスとテーブルの両方、またはいずれか一方のスキャンを必要とします。
まず1つ目は、dbinfo -uの出力、つまり情報ユーティリティによって取得される統計情報です。dbinfoコマンドラインで-uのオプションを指定すると、データベースに含まれるすべてのページの使用状況に関する統計情報が算出されます。これにより、データベース管理者はデータベース内のページ断片化がどの程度進行しているか、見当をつけることができます。
2つ目と3つ目の統計値とは、インデックスの密度とスキューです。これらはsa_index_density()システムプロシージャによって返される値です。このプロシージャを呼び出すと、指定したインデックス全体のスキャンが行われ、インデックスの密度とスキューの値を含む結果セットが返されます。
インデックスの''密度''は、各インデックスページで使用中のスペースの割合を、0から1の間の小数で表したものです。この数値は、データベースページのサイズ、キーのサイズとそのばらつき、さらに達成可能なキーの圧縮率など、さまざまな要因に左右されるのですが、B+ツリーインデックスにおいては0.60から0.80のインデックス密度が典型的といえます。
一方、''スキュー''は、インデックスのバランスの度合いを表す尺度です。B+ツリーインデックスを利用した実装の多くがそうであるように、SQL Anywhereにおいても、削除操作後にインデックスが自動的に再均衡化されることはありません。そのため、激しく「攪拌」されたテーブルでは、インデックスの一部が比較的低密度であるのにその他のページでは100%に近い高密度である、ということもあり得るのです。sa_index_density()が返すスキュー測定値は、ページごとのインデックスエントリ数の自然対数を求め、その標準偏差を表した値です。定義上、スキューの値が1.0より小さくなることはありません。
次の例を見てください。
この例では、パラメータを指定せずにsa_index_density()を呼び出したので、データベース内のすべてのインデックスに関する密度とスキューの統計値が返されています。
259行目のインデックスは、前掲の画面の6行目にあった、クラスタ化宣言されたインデックスに対応しています。このインデックスの密度は0.54です。つまり、各インデックスページのおよそ50%が使用されているということで、これは平均を下回っています。ただプラス面として、約1.27というスキューの値は悪くありません。100の自然対数が4.605で、500の自然対数が6.21であることを考えれば、標準偏差1.27というのはそれほど不合理な数値ではありません。
これに対し、260行目のインデックスは、前掲の画面の5行目にあったのと同じ、クラスタ化されていないインデックスを表しています。このインデックスは、5,000,000ローの中で、ユニークなキー値がたった24しかありません。ここでも先程のケースと同様に、インデックスの密度は0.54と低いのですが、それよりも重大なのは2.105というスキュー値で、これはインデックスが偏っていることを示し、再編成の必要性を示唆しています。
