スキーマ変更時のベスト・プラクティス　その１

  • Article

マイクロソフトの植田です。

今回はテーブル・スキーマの変更に関する話題をご紹介したいと思います。

https://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx

注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。

本ドキュメントは以下の方を対象としております。

l 開発者、テストエンジニア、データベース・アドミニストレータ

l データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方

スキーマ変更の制御（その１）

質問：SQL Server 2005においてスキーマ変更を行う際のベスト・プラクティスはなんでしょうか。最も早く、簡単な方法は何でしょうか？

回答：ビジネス要件は時間とともに変化していきます、その変化に合わせてスキーマ変更の要求が発生することは珍しくありません。SQL Server 2005はスキーマ変更（例えば、列や制約の追加/削除、列のデータ型やプロパティの変更）をサポートしていますが、本ブログでは大きなテーブルを操作する際に考慮しなければならないパフォーマンスと並行性の問題にフォーカスします。そしてパフォーマンスと並行性を最大限に活用するベスト・プラクティスについては次の機会（その２:https://blogs.msdn.com/sqljapan/archive/2007/08/20/4471435.aspx）で取り扱います。

では、現在ではかなり一般的になってきた、10億行規模のテーブルを想定して話を進めます。可用性の観点から、スキーマ変更はできるだけ早く、出来るだけ簡単な方法で行う必要があります。

いくつかのスキーマ変更ではバックグラウンドでの読み出し、書き込みが発生します。これらの処理はスキーマ変更の時間を引き延ばし、かつ、ロックを確保することによって（読み込み時には共有ロック、書き込み時には排他ロック）、並行性を低下させる恐れがあります。例えば、チェック制約を追加する際は、制約を施行するためにテーブル・スキャンを行います。NOT NULL制約、列のデータ型やサイズの変更を含むスキーマ変更はバックグラウンドでのアップデートを実行します。これらの暗黙的な読み出しや書き込みはプロファイラーによるステートメント・レベルのトラッキング、および、「SET STATISTICS IO ON」を使うことによって調べることができます。

これらの処理をプロファイラーで追跡するためには、以下の項目を選択してください。

1. TSQL イベント：SQL:StmtStarting、SQL:StmtCompleterd

2. ストアドプロシージャイベント：SP:StmtStarting、SP:StmtCompleted

列の追加 ― alter table MyTable add NewColumn……

＃

列のプロパティ

列につけられている

制約

プロファイラーによるモニタ　(StmtStarting &StmtCompleted)

パフォーマンス、および、同時実行に対する影響

1

NULL

N/A

alter table MyTable

add MySSN numeric(9,0) NULL

最速。

最高のパフォーマンス、および、並行性。

メタデータの変更のみ。

2

NULL

DEFAULT

alter table MyTable

add MyDate2 datetime NULL

default getdate()

最速。

最高のパフォーマンス、および、並行性。

メタデータの変更のみ。

3

NULL

CHECK

alter table MyTable

add MyPct3 numeric(4,1) NULL constraint MyChk3

check (MyPct3 between 0 and 100)

統計を採取し、その後チェック制約施行のため、テーブル全体を読み込み。

NOT NULLプロパティ設定時よりも高速、しかしチェック制約による読み込みが発生。

大きなテーブルでは、チェック制約のための読み込み処理が書き込みをブロックする可能性がある。

4

NULL

CHECK, DEFAULT

alter table MyTable

add MyInt4 int NULL default 0

constraint myChk4

check (MyInt4 between 0 and 100)

統計を採取し、その後チェック制約施行のため、テーブル全体の読み込みを行う。

NOT NULLプロパティ設定時よりも高速、しかしチェック制約による読み込みが発生。

大きなテーブルでは、チェック制約のための読み込み処理が書き込みをブロックする可能性がある。

5

NULL

DEFAULT

WITH VALUES

alter table MyTable

add MyInt5 int NULL default 0

with values

テーブル全体をデフォルトの値で更新

UPDATE [Northwind].[dbo].[MyTable]

SET [MyInt5] = DEFAULT

NULL & Default (#2)の場合よりも低速。

「WITH VALUE」句によって存在するテーブルの全ての行が更新されるため。

大きなテーブルでは、このバッチ更新処理は並行性の問題を引き起こす恐れがある。

6

NULL

CHECK, DEFAULT

WITH VALUES

alter table MyTable

add MyInt6 int NULL default 0

with values

constraint myChk6

check (MyInt6 between 0 and 100)

テーブル全体をデフォルトの値で更新し、統計を採取、その後チェック制約施行のため、テーブル全体の読み込みを行う。

UPDATE [Northwind].[dbo].[MyTable]

SET [MyInt6] = DEFAULT

最も遅い。「WITH VALUE」句によって存在するテーブルの全ての行が更新され、かつ、チェック制約のための読み込みが行われるため。

デフォルトの値によるバッチ更新処理、および、チェック制約のための読み込み処理が発生するため、大きなテーブルでは顕著な並行性の問題が発生する可能性がある。

7

NOT NULL

DEFAULT

alter table MyTable

add MyDate7 datetime NOT NULL

constraint myDft7

default getdate()

NOT NULL制約によりテーブル全体のデフォルト値による更新処理が要求される。

UPDATE [Northwind].[dbo].[MyTable]

SET [MyDate7] = DEFAULT

NULL & Default (#2)の場合よりも低速。「NOT NULL」制約が、デフォルトの値による更新を要求するため。

大きなテーブルでは、このバッチ更新処理が排他ロックを握るため、並行性の問題を引き起こす可能性がある。

8

NOT NULL

CHECK, DEFAULT

alter table MyTable

add MyInt8 int

NOT NULL

constraint myDft8

default 0

constraint myChk8

check (MyInt8 between 0 and 100)

テーブル全体をデフォルトの値で更新し、統計を採取、その後チェック制約施行のため、テーブル全体の読み込みを行う。

UPDATE [Northwind].[dbo].[MyTable]

SET [MyDate7] = DEFAULT

最も遅い。デフォルトの値による更新処理、および、チェック制約のための読み込み処理のため。このデフォルトの値によるバッチ更新処理、および、チェック制約のための読み込み処理により、大きなテーブルでは顕著な並行性の問題が発生する恐れがある。

大きなテーブルに新しい列を追加する最も速い方法は（DEFAULT制約のありなしに因らず）NULLプロパティを使ったやり方です（上記の例の#1、#2参照）。 #2の例ではDEFAULT制約がありますが、MyDate列の値は全ての行でNULLになります。

大きなテーブルにチェック制約を含めると、チェック制約を施行するためにテーブル全体の読み込み処理が発生します（上記の例の#3、#4、#6、#8参照）。 #3、#4の例ではそれぞれ新規に追加したMyPct3列とMyInt4列にNULLの値が含まれますが、NULL値はチェック制約で不正とならないことに注意してください。NULLはただ単にその値が分からないということを意味します。しかし、もしその値を分かっているものであればチェック制約は強制的にその値をチェックの条件に適応させます。

#5から#8の例ではバッチ更新処理が発生します。 もしNULLおよびDEFAULTプロパティに加えてWITH VALUES句が指定されていれば（#5、#6参照）、全ての行がデフォルトの値で更新されます。NOT NULLとDEFAULTの組み合わせによりバッチ更新処理が発生します。バッチ更新処理は排他ロックを保持し、並行性を低下させるので、これらのケースは理想的なパフォーマンス、および、可用性の目標には到達できません。

書き込み処理が発生するケースにチェック制約を加えた場合、#6：NULLプロパティのケース、#8：NOT NULLプロパティのケースともにチェック制約のための読み込み処理が発生し、パフォーマンス、および、可用性が最も低くなります。

列のデータ型、デフォルト制約、チェック制約、NULLプロパティの変更

alter table alter columnステートメントを使って列のデータ型やデータ長（numeric型の場合はスケール、および、精度が含まれる）を変更することができます。これらのケースでは変更に付随して行われるバッチ更新処理によって、パフォーマンス、および、並行性が影響を受けます。もし、NULLプロパティがNOT NULLに変更されると、バッチ更新処理が発生します。例外はNOT NULLプロパティがNULLに変更される時です。これはメタデータのみの高速な処理で行われます。

列プロパティの変更、データ型、および、データ長、列制約の追加　－　alter table MyTable alter column

変更

プロファイラーによる観察

プロパティ　変更

NOT NULLからNULL

alter table MyTable

alter column MyDate7 datetime NULL

高速

メタデータのみ変更

プロパティ　変更

NULLからNOT NULL

alter table MyTable

alter column MyDate7 datetime NOT NULL

バッチ更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MyDate7] = [MyDate7]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

デフォルトプロパティ追加

ALTER TABLE MyTable

ADD CONSTRAINT MyDft

DEFAULT 0 FOR MySSN

高速

メタデータのみ変更

デフォルトプロパティ削除

ALTER TABLE MyTable

DROP CONSTRAINT MyDft

高速

メタデータのみ変更

チェック制約追加

ALTER TABLE MyTable

ADD CONSTRAINT MyDtChk

Check (MyDate2 <= getdate())

統計を採取し、その後チェック制約施行のため、テーブル全体の読み込みを行う。

大きなテーブルでは、変更に引き続き起こるチェック制約のための読み込み処理により並行性の問題が発生する恐れがある。

チェック制約削除

ALTER TABLE MyTable

DROP CONSTRAINT MyDtChk

高速

メタデータのみ変更

データ長の　拡張

NUMERIC(9) から(11)

ALTER TABLE MyTable

ALTER COLUMN MySSN numeric(11,0)

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

データ型の　変更

NUMERICからINT

ALTER TABLE MyTable

ALTER COLUMN MySSN int

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

データ型の　変更

INTからVarchar(9)

ALTER TABLE MyTable

ALTER COLUMN MySSN varchar(9)

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

データ長の　拡張

Varchar(9) からVarchar(15)

ALTER TABLE MyTable

ALTER COLUMN MySSN varchar(15)

高速

メタデータのみ変更

データ長の　縮小

Varchar(15)からVarchar(10)

ALTER TABLE MyTable

ALTER COLUMN MySSN varchar(15)

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

データ型の　変更

Varchar(15) からChar(15)

ALTER TABLE MyTable

ALTER COLUMN MySSN char(15)

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

データ長の　拡張

Char(15) からchar(20)

ALTER TABLE MyTable

ALTER COLUMN MySSN char(20)

更新処理実行

UPDATE [Northwind].[dbo].[MyTable] SET [MySSN] = [MySSN]

大きなテーブルでは、変更に引き続き起こるバッチ更新処理により並行性の問題が発生する恐れがある。

列の削除

列の削除はスキーマのみの操作になります。この場合は、削除される列からオブジェクトを再利用スペースに移動させない限り、並行性の問題は発生しません。

ユニーク、および、プライマリーキー制約

ユニーク、および、プライマリーキー制約の追加は読み込み処理、ソート処理、書き込み処理を含みます。既定では、ユニーク制約は非クラスタ化インデックスを付加し、プライマリーキー制約はクラスタ化インデックスを付加します。 すでにユニーク制約（非クラスタ化インデックス）を持っている大きなテーブルに対してプライマリーキー制約（そしてデフォルトのクラスタ化インデックス）を追加する場合、パフォーマンス、および、並行性の問題が発生することは明白です。これはプライマリーキー（クラスタ化インデックス）を追加することによって非クラスタ化インデックスの再構築が行われ、RID（列ID）がプライマリーキーに置き換えられるためです。

Alter table, create/alter indexはユニーク、および、プライマリーキー制約/インデックスを制御するためのONLINEオプションを持っています。可能であれば、最高のパフォーマンス、および、最も高い並行性を実現するために、インデックス操作の際はONLINEオプションを使うべきです。もしくは、メンテナンスの時間を別途設けてこれらの操作を行うのが賢明でしょう。

結論、および、ベスト・プラクティス

SQL Server 2005の動作を理解することにより、効果的にスキーマの変更を制御することができます。このナレッジによって、スキーマの変更に付随して起こる望ましくない処理、例えば意図しない読み込み処理、書き込み処理など、を回避することができ、高い並行性、および、可用性を実現する、最も速く、最も簡単な方法を利用することができます。

以下の場合、最も速くスキーマ変更を行うことができます：

1. NULLプロパティを設定して列を追加

2. NULL、および、デフォルトプロパティを設定して列を追加

3. NOT NULLプロパティをNULLに変更

4. デフォルト制約を追加

5. チェック、または、デフォルト制約を削除

以下の場合は変更に付随して読み込み、または、書き込み処理が発生するため、スキーマ変更の処理は最も遅くなります。

1. チェック制約はチェックを行うためにテーブルの全ての行の読み込みが発生

2. NULLプロパティの列にデフォルト制約と「WITH VALUES」句の組み合わせを適応するとバッチ更新処理が発生

3. NOT NULL制約を（デフォルト制約と共に）追加するとバッチ更新処理が発生

4. NULLプロパティをNOT NULLに変更するとバッチ更新処理が発生

5. データ型、または、データ長を変更するとバッチ更新処理が発生。唯一の例外はvarchar型の長さを拡張した場合。

結論として、パフォーマンス、および、並行性を最も高くするための、大規模なテーブルにおけるスキーマ変更のベストプラクティスはNULLプロパティで列を追加することからスタートします。もし、NULL値を許容できないのであれば、そのアプリケーションはNOT NULLで列を追加しなければならないでしょう。「スキーマ変更の制御（パート2）」では、NULLプロパティで追加された新しい列のバッチ更新処理を分離することによって、パフォーマンスと並行性を最大限に高めること方法を紹介します。そのバッチ更新処理は、パフォーマンス、および、並行性を高くするために、複数の部分的な更新処理に置き換えられます。これは並行性を損なわずに列のデータ型やデータ長を変更するためのテクニックです。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。