SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

パーティショニング実施時にパーティションキーの有無による検索効率の違い

leave a comment

パーティショニングをしているときにパーティションキーを WHERE に含めている場合の検索効率の違いについてまとめてみたいと思います。

■パーティションキーの有無による検索効率の違い


以下のようなテーブルを作成しています。

CREATE TABLE [dbo].[PersonalInfo](
    [ID] [int] NOT NULL,
    [姓] [varchar](10) NULL,
    [名] [varchar](10) NULL,
    [姓(カタカナ)] [varchar](20) NULL,
    [名(カタカナ)] [varchar](20) NULL,
    [姓(ローマ字)] [varchar](40) NULL,
    [名(ローマ字)] [varchar](40) NULL,
    [性別] [varchar](2) NULL,
    [電話番号] [varchar](13) NULL,
    [FAX] [varchar](13) NULL,
    [携帯電話] [varchar](13) NULL,
    [メールアドレス] [varchar](50) NULL,
    [郵便番号] [varchar](7) NULL,
    [住所] [varchar](200) NULL,
    [住所(カタカナ)] [varchar](350) NULL,
    [住所(ローマ字)] [varchar](350) NULL,
    [生年月日] [datetime] NULL,
    [年齢] [smallint] NULL,
    [出身地] [varchar](20) NULL,
    [血液型] [varchar](2) NULL,
    [Hash] [int] NOT NULL
)

 

ID がキー項目 (クラスター化インデックス) で、Hash をパーティションで分割するキーとして設定をしています。

このテーブルに対して 2 種類のクエリを実行してみます。

SELECT * FROM PersonalInfo WHERE ID = 32218
SELECT * FROM PersonalInfo WHERE ID = 32218 AND Hash = 4

ひとつはクラスター化インデックスのみを、もう一つはクラスター化インデックスとパーティションキーを含めています。

クラスター化インデックスを条件に指定していますのでどちらもピンポイントでデータを取得できます。
image

これらのクエリですがパーティションキーの指定有無で読み取るページ数が変わってきます。
最初のクエリでは以下のページ数が読み取られています。
image

パーティションキーを含めた場合はこのようになります。
image

パーティションキーを含めない場合は 12 ページ読み取りが行われていますが、パーティションキーを含めた場合 3 ページの読み取りとなっています。

この違いですが、検索時にツリー構造で検索するかパーティションから検索するかの違いになります。

パーティションキーを含めていない場合、どのパーティションからデータを取得すればよいかの判断ができないため、B-Tree のルートから検索をする必要があります。
そのため、アクセスされたパーティションには複数のパーティションが含まれています。
image

パーティションキーを含めた場合、特定のパーティションのみを検索すればよいという判断ができます。
この場合は、ルートからデータを検索しなくても特定のパーティションからツリーを検索すればよいことになります。
image

パーティションキーの有無により対象のデータにたどり着くまでにアクセスする必要のある中間ページの数が変わってくるので、読み取り数に差が出てきます。

検索にパーティションキーを含められる場合は含めておくと多少レスポンスに差が出てくるかと。

Written by masayuki.ozawa

4月 10th, 2012 at 3:16 pm

Posted in SQL Server

Tagged with

Leave a Reply

*