もっと詳しく

巨大データなデータをSQL Server（又はAzure SQL Database）で扱う場合、パーティション化（パーティショニング）するという方法が思いつきます。

しかし、マイクロソフトのサイトをググってもイマイチ分かり難くありませんか？

その他にもいくつかのサイトでパーティションの作成方法は解説されているものの、コードばかりが掲載されていて考え方とか全体像が掴みにくくありませんか？

私もそうでした。

そこで、当サイトでは、そんな方の為に図を使って全体をまず理解し、次に具体的なコードを紹介するというステップで、SQL Serverのパーティション化を分かりやすく解説しようと思います。

パーティション化（パーティショニング）とは

一応、パーティション化について解説しておきますので、知っている方は読み飛ばしてください。

パーティション化とは、１つの巨大な物理テーブルを複数の小さなテーブルに分割することで、１テーブル当たりのデータ件数を少なくし、検索や更新のパフォーマンスを改善する方法です。

パーティション化に際しては、分割後のテーブル数（パーティション数）と、各パーティションに振り分けるルール（パーティション関数）を用意する必要はありますが、使う際は従来通り、１つの物理テーブルに対する検索や更新と同じ感覚で利用できます。

SQL Serverの場合、15000個のパーティションに分割することが可能ですが、分割数が多いとオーバーヘッドが大きくなるため、通常は1000以下が推奨されています。

SQL Serverにおけるパーティションの構造

SQL Server の場合、パーティションの構造は次の様になります。

名前 役割
テーブル データを保持するテーブルです。
Create Tableにパーティションスキーマを指定することで、
そのテーブルがパーティション化されます。
パーティション関数 検索や更新が行われる際、分割されたテーブルのアクセス先を
指定するための関数です。
Partition パーティションの構成を保持するためのスキーマ（パーティションスキーマ）
のことで、FileGroupと紐づける必要があります。
FileGroup 複数の物理ファイル(File)を束ねるグループであり、１つのグループには
１つ以上の物理ファイルが紐づけられます。
File データを保持するための物理ファイル（データベースファイル）です。

パーティション毎に１つのファイルグループを紐づける

この方法が一般的だと思いますが、１つのパーティションに１つのファイルグループを紐づけて使います。

ファイルグループ単位でバックアップを取得したい場合は、この方法を使います。

ただ、個別のバックアップ／リストアという作業は管理が煩雑になるので、簡単に運用したい場合はこの方法は適しません。

全てのパーティションを１つファイルグループを紐づける

複数のパーティションを１つのファイルグループに紐づける方法で、ファイルグループ単位でのバックアップが必要ない場合、こちらの方が管理が楽になります。

ちなみに、SQL Database では、「PRIMARY」という名前のファイルグループしかサポートされないため、全てのパーティションはこの「PRIMARY」というファイルグループに紐づける必要があるため、必然的にこちらの方法になります。

パーティション化の手順

パーティション化する手順は、次の様になります。

  1. ファイルグループの作成
  2. ファイルグループにファイルを登録
  3. パーティション関数の作成
  4. パーティションスキーマとファイルグループの紐づけ
  5. パーティションスキーマを指定してテーブルを作成

以上の手順を図で表すと、次の様になります。

ここでのポイントとしては、テーブル作成時（CREATETABLE）にパーティションスキーマ名とカラムを指定するのですが、ここで指定できるカラムはプライマリーキーに含まれている必要があります。

パーティション毎に１つのファイルグループを紐づける

下記は、データベース名 “miming” に “BIG_DATA_TABLE” という名前でパーティション化されたテーブルを作成する例です。

パーティションは3分割（2021-01-01 未満、2021-01-01～2022-01-01 、2022-01-01以上）に分割し、それぞれに対して “FileGroup01″,”FileGroup02″,”FileGroup03” というファイルグループを紐づけています。

紐づけ方法は、パーティション関数のRANGE RIGHTFORVALUESに列挙した閾値に対応させるようにCREATE PARTITION SCHEME の TO～に記述するだけです。

但し、パーティションスキーマには、パーティション関数で列挙した閾値の数よりも１つ余分にファイルグループを指定する必要があるという点には注意が必要です。

USE [mining]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/***** パーティショングループの作成（今回は３個） *****/
ALTER DATABASE [mining]
ADD FILEGROUP [FileGroup01]

ALTER DATABASE [mining]
ADD FILEGROUP [FileGroup02]

ALTER DATABASE [mining]
ADD FILEGROUP [FileGroup03]

/***** パーティショングループに物理ファイルを追加 *****/
ALTER DATABASE [mining]
ADD FILE (
	 NAME = N'mining_FileGroup01_001'
	,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mining_FileGroup01_001.ndf'
	,SIZE = 5120KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024KB
)
TO FILEGROUP [FileGroup01]

ALTER DATABASE [mining]
ADD FILE (
	 NAME = N'mining_FileGroup02_001'
	,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mining_FileGroup02_001.ndf'
	,SIZE = 5120KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024KB
)
TO FILEGROUP [FileGroup02]

ALTER DATABASE [mining]
ADD FILE (
	 NAME = N'mining_FileGroup03_001'
	,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mining_FileGroup03_001.ndf'
	,SIZE = 5120KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024KB
)
TO FILEGROUP [FileGroup03]


/***** パーティション関数の作成 *****/
CREATE PARTITION FUNCTION SalesResultsPartitionFunc (datetime)  
    AS RANGE RIGHT FOR VALUES (
	'2021-01-01','2022-01-01'
	) ;  
GO  

/***** パーティションスキーマの作成 *****/
CREATE PARTITION SCHEME [SalesResultsPartitionScheme]
    AS PARTITION [SalesResultsPartitionFunc]  
    TO (
	[FileGroup01],[FileGroup02],[FileGroup03]
    ) ;  
GO  

/***** テーブルの作成 *****/
CREATE TABLE [dbo].[BIG_DATA_TABLE](
	[SHOP_ID] [numeric](9, 0) NOT NULL,
	[PRODUCT_ID] [numeric](9, 0) NOT NULL,
	[SALE_DATETIME] [datetime] NOT NULL,
	[SALES_AMOUNT] [numeric](5, 0) NOT NULL,
	[COMMENT] [varchar](20)
PRIMARY KEY CLUSTERED 
(
	[SHOP_ID] ASC,
	[PRODUCT_ID] ASC,
	[SALE_DATETIME] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON SalesResultsPartitionScheme ("SALE_DATETIME")
GO

全てのパーティションを１つファイルグループを紐づける

下記は、PRIMARYというデフォルトで用意されているファイルグループに対して、全てのパーティションを紐づけるサンプルです。

パーティション作成関数に記述する閾値は、’2020-09-01’～’2022-02-01′ までを1か月ごとに分割しています。

全てのパーティションを１つのファイルグループ（今回はPRIMARY）に紐づける場合、パーティションスキーマには ALL TO (‘PRIMARY’)　と書くだけで済むので、非常に楽になります。

USE [mining]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/***** パーティション関数の作成 *****/
CREATE PARTITION FUNCTION SalesResultsPartitionFunc (datetime)  
    AS RANGE RIGHT FOR VALUES (
      '2020-09-01','2020-10-01','2020-11-01','2020-12-01','2021-01-01','2021-02-01',
      '2021-03-01','2021-04-01','2021-05-01','2021-06-01','2021-07-01','2021-08-01',
      '2021-09-01','2021-10-01','2021-11-01','2021-12-01','2022-01-01','2022-02-01'
    ) ;  
GO  

/***** パーティションスキーマの作成 *****/
CREATE PARTITION SCHEME SalesResultsPartitionScheme  
    AS PARTITION SalesResultsPartitionFunc 
    ALL TO ('PRIMARY') ;  
GO  

/***** テーブルの作成 *****/
CREATE TABLE [BIG_DATA_TABLE](
	[SHOP_ID] [numeric](9, 0) NOT NULL,
	[PRODUCT_ID] [numeric](9, 0) NOT NULL,
	[SALE_DATETIME] [datetime] NOT NULL,
	[SALES_AMOUNT] [numeric](5, 0) NOT NULL,
	[COMMENT] [varchar](20)
PRIMARY KEY CLUSTERED 
(
	[SHOP_ID] ASC,
	[PRODUCT_ID] ASC,
	[SALE_DATETIME] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON SalesResultsPartitionScheme ("SALE_DATETIME")
GO

パーティション関数で指定する閾値の範囲について

パーティション関数を作成する際の CREATE PARTITION FUNCTION ～ AS RANGE ～ FOR VALUES において、RANGE RIGHT と記述するか、RANGE LEFTと記述するかで、閾値を含む／含まないが変わってきます。

CREATE PARTITION FUNCTION SalesResultsPartitionFunc (datetime)  
    AS RANGE RIGHT FOR VALUES (
	'2021-01-01','2022-01-01'
	) ;

ちなみに、良く使われるのは RANGE RIGHT で、後述する SQL Server Management Studio (SSMS)を使ったパーティション化でも、初期値は RANGE RIGHT となっています。

SSMSによる既存テーブルのパーティショニング化

SQL Server Management Studio (SSMS)　を使うことで、既存のテーブルをパーティション化することが可能です。

新規でパーティション化したテーブルを作成する場合は、まず空のテーブルを作成してから、以下の操作を実行して下さい。

SSMSのダウンロードは こちらのサイトから可能です。

SQL Server Management Studio (SSMS) のダウンロード – SQL Server Management Studio (SSMS)
最新バージョンの SQL Server Management Studio (SSMS) をダウンロードします。
docs.microsoft.com

まず、SSMSを起動後、オブジェクトエクスプローラーからパーティション化したいテーブルを選択します。

次に、右クリック⇒ストレージ⇒パーティションの作成の順に選択し、「パーティションの作成ウィザード」を表示し、「次へ」をクリックします。

あとは、画面の指示に従って操作していくだけです。

最後に、下記のウィンドウが表示されるので、すぐに実行するを選択します。

ここで、スクリプトの作成を選択すると、一連の処理のコマンドを表示することが可能なので、中身を確認することも可能です。

まとめ

今回は、SQL Server (又は Azure SQL Database）でテーブルをパーティション化する方法について図を使って解説しました。

SQL Serverのパーティション化については、他のサイトでも取り扱われていますが、いきなり細かいコマンドが紹介されていることがほとんどで、全体像が分からない状態での理解は難しいとお思います。

少なくとも私はそうでした。

この記事で、全体像をつかんでいただければ、他のサイトの内容が理解し易くなると思いますので、仕事で使う必要が生じた方は、是非この記事を参考にしてください。