データパーティションとは
データパーティションとは、データの格納場所を分割することができる機能です。
1つのテーブルを日付の範囲などで、複数に行分割することができます。
パーティション化することのメリットとは
行を分割することによって、アクセスを迅速かつ効率的に行うことができます。
例えば、過去10年間分の膨大なデータを保持しているテーブルがあったとします。
実際によくアクセスするのは直近2年くらい、といった場合に、年で分割しておくと、アクセスを必要なデータのみにあらかじめ絞る事が出来るので、処理を効率化することができます。
実態は分割されていても理論的には1つのテーブルに見えているので、クエリを書く側はパーティション化されている事を意識することなく使用することができます。
パーティション化していない場合
パーティション化していれば、効率よくデータにアクセスできる
実際にパーティションを設定してみる
パーティション化するためには、事前に以下の設定が必要です。
- ファイルグループの作成
- ファイルの作成
- パーティション関数の作成
- パーティション構成の作成
今回は、以下のような売上のデータを、OrderDateの日付で年ごとに分割したいと思います。
SQL Server Management Studio で操作していきます。
ファイルグループの作成
まずはファイルグループを作成します。
[partitionTest]というDBに設定をしていきます。
今回は日付で分割し、2017年以前、2018年、2019年、2020年、2021年以降という5つのファイルグループを作成します。
--2017年以前
ALTER DATABASE [partitionTest]
ADD FILEGROUP [FG2017izen]
--2018年
ALTER DATABASE [partitionTest]
ADD FILEGROUP [FG2018]
--2019年
ALTER DATABASE [partitionTest]
ADD FILEGROUP [FG2019]
--2020年
ALTER DATABASE [partitionTest]
ADD FILEGROUP [FG2020]
--2021年以降
ALTER DATABASE [partitionTest]
ADD FILEGROUP [FG2021ikou]
ファイルグループが作成されたか確認する方法
オブジェクトエクスプローラーのDB名の上で右クリック→「プロパティ」を開きます。
「ファイルグループ」を選択すると、先ほど作成したファイルグループが表示されます。
ファイルの作成
次に、ファイルを作成します。
サイズやパスなどは適宜変更してください。
--2017年以前
ALTER DATABASE [partitionTest]
ADD FILE (
NAME = N'partitionTest_FG2017izen'
,FILENAME = N'パス\partitionTest_FG2017izen.ndf'
,SIZE = 5120KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024KB
)
TO FILEGROUP [FG2017izen] --先ほど作成したファイルグループを指定する
--2018年
ALTER DATABASE [partitionTest]
ADD FILE (
NAME = N'partitionTest_FG2018'
,FILENAME = N'パス\partitionTest_FG2018.ndf'
,SIZE = 5120KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024KB
)
TO FILEGROUP [FG2018] --先ほど作成したファイルグループを指定する
--2019年
ALTER DATABASE [partitionTest]
ADD FILE (
NAME = N'partitionTest_FG2019'
,FILENAME = N'パス\partitionTest_FG2019.ndf'
,SIZE = 5120KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024KB
)
TO FILEGROUP [FG2019] --先ほど作成したファイルグループを指定する
--2020年
ALTER DATABASE [partitionTest]
ADD FILE (
NAME = N'partitionTest_FG2020'
,FILENAME = N'パス\partitionTest_FG2020.ndf'
,SIZE = 5120KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024KB
)
TO FILEGROUP [FG2020] --先ほど作成したファイルグループを指定する
--2021年以降
ALTER DATABASE [partitionTest]
ADD FILE (
NAME = N'partitionTest_FG2021ikou'
,FILENAME = N'パス\partitionTest_FG2021ikou.ndf'
,SIZE = 5120KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024KB
)
TO FILEGROUP [FG2021ikou] --先ほど作成したファイルグループを指定する
ファイルが作成されたか確認する方法
先ほどと同様、「プロパティ」から「File」を選択すると、先ほど作成したファイルが表示されます。
上記で指定したフォルダをみると、ちゃんとファイルが作成されています。
パーティション関数の作成
次はデータをファイルグループに振り分けるための関数を作成します。
今回は日付で分割する「funcPartitionDate」という関数を作成して、2017年以前、2018年、2019年、2020年、2021年以降と分けていきます。
CREATE PARTITION FUNCTION funcPartitionDate(datetime) AS RANGE RIGHT FOR VALUES (
'2018/01/01'
,'2019/01/01'
,'2020/01/01'
,'2021/01/01'
)
RANGE句の指定により、分割する値の範囲が異なります。
RANGE RIGHTを指定した場合
| パーティション | 値（RANGE RIGHTを指定した場合） |
|---|---|
| 1 | column < 2018/01/01 |
| 2 | column >= 2018/01/01 AND column < 2019/01/01 |
| 3 | column >= 2019/01/01 AND column < 2020/01/01 |
| 4 | column >= 2020/01/01 AND column < 2021/01/01 |
| 5 | column >= 2021/01/01 |
RANGE LEFTを指定した場合
| パーティション | 値（RANGE LEFTを指定した場合） |
|---|---|
| 1 | column <= 2018/01/01 |
| 2 | column > 2018/01/01 AND column <= 2019/01/01 |
| 3 | column > 2019/01/01 AND column <= 2020/01/01 |
| 4 | column > 2020/01/01 AND column <= 2021/01/01 |
| 5 | column > 2021/01/01 |
パーティション関数が作成されたか確認する方法
オブジェクトエクスプローラーを最新の状態に更新します。
DB名→「ストレージ」→「パーティション関数」を選択すると、先ほど作成した関数が表示されます。
パーティション構成の作成
パーティション構成を作成します。
パーティション関数で指定されたパーティションに対し、ファイルグループを割り当てます。
複数のパーティションを同じファイルグループを指定することも可能です。
CREATE PARTITION SCHEME scmPartitionDate AS PARTITION funcPartitionDate TO (
FG2017izen -- ～20171231
,FG2018 --20180101～20181231
,FG2019 --20190101～20191231
,FG2020 --20200101～20201231
,FG2021ikou --20210101～
)
パーティション構成が作成されたか確認する方法
先程と同様、オブジェクトエクスプローラーを最新の状態に更新します。
DB名→「ストレージ」→「パーティション構成」を選択すると、先ほど作成した構成が表示されます。
テーブルの作成
パーティションの準備が出来たので、実際にテーブルを作成します。
テーブルを作成する時は、利用するパーティション構成と、分割したい列を指定します。
CREATE TABLE [Sales](
[SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
,[RevisionNumber] [tinyint] NOT NULL
,[OrderDate] [datetime] NOT NULL
,[Status] [tinyint] NOT NULL
,[CustomerID] [int] NOT NULL
,[SubTotal] [money] NOT NULL
,[ModifiedDate] [datetime] NOT NULL
) ON [scmPartitionDate]([OrderDate]) --パーティション構成と分割列を指定する
これでパーティションの構築が完了しました。
このテーブルにデータを登録すると、パーティション関数で指定した値の範囲にしたがって、それぞれのファイルグループへデータが保存されます。
今回はテストデータを約10万件ほど登録しました。
実際に実行してパフォーマンスを調べてみる
パーティション化したテーブルとしていないテーブルとで比較してみます。
テーブルの内容は、パーティションありもなしも同じです。
パーティションありのテーブル：[Sales]
パーティションなしのテーブル：[Sales_NP]
テーブルの年ごとのレコード数はこんなかんじです。
実行プランを表示させる
パフォーマンスを調べるために、実行プランの設定を行います。
「クエリ」→「実際の実行プランを含める」をクリックすると、クエリ実行時に使用される実際のクエリ実行プランが表示されます。
全レコードをSELECT
まず、全レコードを表示してみます。
SELECT * FROM [Sales]
「実行プラン」タブを開いて、「Table Scan」をクリックします。
「Table Scan」をクリックした状態で、「プロパティウィンドウ」の「実際にアクセスされたパーティション」を確認します。
全レコードを表示させているので、1～5のすべてのパーティションにアクセスしています。
[Sales_NP]テーブルはパーティションの設定をしていないので、「実際にアクセスされたパーティション」の項目自体がありません。
日付を指定してSELECT
次に、条件で日付を指定して表示してみます。
SELECT * FROM [Sales]
WHERE [OrderDate] >= '2019/01/01' And [OrderDate] <= '2019/12/31'
条件を2019年に絞ったので、アクセスしたパーティションが「3」のみになっています。
パーティションありとなしで同じ条件で実行した際の実行プランを比較すると、
パーティションありの方が若干ですがコストが低くなっています。
今回はテストデータの件数がそこまで多くないのであまり差が出ていませんが、大規模なテーブルであればより差が出るとおもいます。
パーティションあり
パーティションなし
比較
| 項目名 | パーティションあり | パーティションなし |
|---|---|---|
| クエリコスト（バッチ相対） | 24% | 76% |
| CUPの推定コスト | 0.0364397 | 0.114913 |
| I/Oの推定コスト | 0.132979 | 0.414315 |
| 実際の経過CUP時間（ミリ秒） | 23 | 40 |
パーティションの恩恵を受けられないクエリ
折角パーティションの設定をしても、クエリの書き方によってはうまくパフォーマンスが上がらない場合があります。
例を紹介します。
--LIKE文を使用している
SELECT * FROM [Sales]
WHERE [OrderDate] like '%2019%'
--分割列に対して関数を使用している
SELECT * FROM [Sales]
WHERE format([OrderDate],'yyyy') = '2019'
上記のパターンは2019年で絞っているつもりでも、1～5のすべてのパーティションにアクセスしてしまいます。
パフォーマンス向上のためには、クエリの書き方にも注意が必要です。
どのデータがどのパーティションに属しているか確認する
$PARTITION (Transact-SQL) 関数を使用すると、行が属しているパーティション番号を得ることができます。 指定したパーティション関数に対して、パーティション分割列の値のセットがマップされるパーティション番号を取得できます。
※20210309追記 実際に格納されているパーティションの番号を返すわけではなく、関数でセットされるであろうパーティション番号を返してくれるようです。
--パーティション番号を表示する
SELECT
$partition.[funcPartitionDate]([OrderDate]) AS [パーティション番号]
,*
FROM
[Sales]
実行結果
おわりに
インデックスと同様、パーティションも奥が深いです。
設定だけしてやった気分にならずに、実行プランを確認しながらパフォーマンス向上に取り組みたいとおもいます。
参考になったサイト
データパーティショニングで巨大DBも楽々管理 (1/3)：SQL Server 2005を使いこなそう（11） - ＠IT
パーティション テーブルとパーティション インデックス - SQL Server | Microsoft Docs