サービスを止めずにテーブルの列定義を変更する

本記事は、Silbird Advent Calendar 2017 22日目の記事です。

はじめに

サービスの運用が長くなるとテーブルの列定義変更が必要になるケースが発生することがあります。
例えば、数値型の項目をint型で設計してテーブルを作成していたが、サービスの運用を続けた結果、累計の値がintの上限値を超えてしまった場合などが該当すると思います。

SQL Serverを例に、サービスを止めずにデータ量があるトランザクションテーブルの列定義を変更する方法をご紹介しようと思います。

SQL Serverの数値型の範囲

Microsoft の技術情報に拠れば、代表的な数値型の取りうる値は以下の通りです。
累計項目をint型でテーブルを作成した場合、21億を超えるとデータを格納できないリスクが発生します。(update文がエラーになる)

データ型	範囲
bigint	-2^63 (-9,223,372,036,854,775,808) ～ 2^63-1 (9,223,372,036,854,775,807)
int	-2^31 (-2,147,483,648) ～ 2^31-1 (2,147,483,647)
smallint	-2^15 (-32,768) ～ 2^15-1 (32,767)

int、bigint、smallint、および tinyint (Transact-SQL)

テーブル例

以下のようなゲームにおけるユーザーのイベントポイントを管理するテーブルを使用して説明します。
列「event_point」はゲームのイベントをプレイして獲得できる累計ポイントです。このポイントがインフレしていくと、intの上限に近づき、対応に迫られる場合があります。

user_battle_point.sql

CREATE TABLE [dbo].[user_battle_event](
	[event_id] [int] NOT NULL,
	[user_id]  [int] NOT NULL,
	[event_point] [int] NOT NULL,
	[addtime] [datetime] NOT NULL,
	[updatetime] [datetime] NOT NULL,
 CONSTRAINT [PK_user_battle_event] PRIMARY KEY CLUSTERED 
(
	[event_id] ASC,
	[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

対応方法1 テーブル定義の変更

対応方法の1つ目としては、テーブルの列定義を変更し、データ型を int→bigint へ変更する方法です。

列の型変更

SQL Server Management Studio より、テーブル名を右クリック - [構造] からデータ型を変更することができます。
しかし、GUIからの列定義変更は、テーブルに含まれるデータ量が多い場合はタイムアウトエラーになることがあります。
以下のように、DDLを用意して実行することを推奨します。

int_bigint_convert.sql

alter table user_battle_event alter column event_point bigtint not null

型変更にかかる時間

AWS上の RDS for SQL Server (db.r3.2xlarge)でDDLを実行して列定義をint→bigintへ変更した際にかかった時間です。
サービス停止中/稼働中か、稼働中の場合はサーバーにどのくらい負荷がかかっているかに依存すると思いますが、参考にしていただければと思います。

レコード数	実行時間
100万件	2秒
5000万件	3分
9000万件	10分15秒

サービス稼働中に実行する制約

できることならサービスを停止させずにテーブル定義を変更したいと思いますが、テーブル定義変更中はアプリからのクエリ要求がタイムアウトとなるリスクがあり、以下のようなケースに限定されると思います。

• 実行時間が数秒(約100万件）未満のケース
• サービスが該当のテーブルにクエリを発行していない時間帯

基本的にテーブルの列定義を変更する場合は、メンテナンスに入れるなど該当のテーブルにクエリが発行されない状態にする必要があります。

対応方法2 別テーブルに移行

テーブルの列定義を変更したいが、どうしてもサービスを止めたくないケースもあると思います。
その場合は、定義を変更した別のテーブルを作成し、そちらにデータを移行して対応する方法もあります。

具体的には以下の手順となります。

• event_point列を bigint にした user_battle_point_2 というテーブルを作成する
• アプリケーションに変更を加え、user_battle_point_2 にデータがあればそちらの値を、データがない場合は user_battle_point のデータを user_battle_point_2 へinsertする
• データの更新は user_battle_point_2 のみ行う

アプリケーションを変更する必要がありますが、新規にデータを作成し、アクセスのあったユーザーのデータを随時新テーブルへ移行している方法です。
この方法であれば、サービスを止める必要はありませんし、データベースエンジンに依存せずに移行することができます。

ポイントを取得するメソッド、更新するメソッドを例に処理イメージをC#で記載します。

public int GetUserPoint(int eventId, int userId)
{
    var adapter2 = new UserBattlePoint2Adapter();    // SQLを実行してくれるクラス

    var userBattlePoint2 = adapter2.ExecuteSQL($"select * from user_battle_point_2 where event_id = {eventId} and user_id = {userId}");

    // 新テーブルにレコードがある場合は、そちらを使用
    if (userBattlePoint2 != null)
        return userBattlePoint2.event_point;

    // 新テーブルにレコードがない場合は、旧テーブルから移行
    var adapter = UserBattlePointAdapter();

    var userBattlePoint2 = adapter2.ExecuteSQL($"select * from user_battle_point_ where event_id = {eventId} and user_id = {userId}");

    adapter.ExecuteSQL($@"insert into user_battle_point_2 
                            (event_id, user_id, event_point, addtime, updttime) 
                            values 
                            ({userBattlePoint2.event_id}, {userBattlePoint2.user_id}, {userBattlePoint2.event_point}, {DateTime.Now}, {DateTime.Now})");

    return userBattlePoint2.event_point;
}

public void UpdateUserPoint(int eventId, int userId, long incrementPoint)
{
    var adapter2 = new UserBattlePoint2Adapter();    // SQLを実行してくれるクラス

    var userBattlePoint2 = adapter2.ExecuteSQL($"select * from user_battle_point_2 where event_id = {eventId} and user_id = {userId}");

    // 新テーブルにレコードがある場合は、Update
    if (userBattlePoint2 != null)
    {
        adapter2.ExecuteSQL($@"update user_battle_point_2 
                                set event_point = event_point + {incrementPoint}, updatetime = {DateTime.Now} 
                                where event_id = {eventId} and user_id = {userId}");
        return;
    }

    // 新テーブルにレコードがない場合は、旧テーブルから移行
    var adapter = UserBattlePointAdapter();

    var userBattlePoint2 = adapter2.ExecuteSQL($"select * from user_battle_point_ where event_id = {eventId} and user_id = {userId}");

    adapter.ExecuteSQL($@"insert into user_battle_point_2 
                            (event_id, user_id, event_point, addtime, updttime) 
                            values 
                            ({userBattlePoint2.event_id}, {userBattlePoint2.user_id}, {userBattlePoint2.event_point + incrementPoint}, {DateTime.Now}, {DateTime.Now})");
}

まとめ

• サービス稼働中のままテーブルの列定義を行えるのは数秒で処理が完了するケース、基本はサービスを止めて行う
• どうしてもサービスを止められない場合は、新テーブルを作成し、アプリケーションを改修して新テーブルに移行する方法もある