目次
はじめに
Webアプリケーションでは、データベースに対する処理は不可欠です。ユーザー情報や商品データなど、多くの情報はデータベースに格納され、アプリケーションはこれらを適切に処理することで様々な機能を実現します。
|
機能例 |
データベース処理の内容例 |
|
ユーザー登録 |
入力された情報をデータベースに保存する |
|
商品一覧表示 |
データベースから商品情報を取得して画面に表示する |
|
注文履歴の表示 |
ユーザーIDに紐づいた注文情報をデータベースから取得して表示する |
このように、データベース処理はWebアプリケーションの根幹を支える重要な要素と言えるでしょう。そして、アプリケーションが扱うデータ量が増えるにつれて、データベース処理の効率化はパフォーマンスに大きく影響を与えるようになります。
特に、大量のデータを扱う場合、処理速度がアプリケーション全体のパフォーマンスに大きく影響します。
そのため、Webアプリケーション開発においては、データベース処理の効率化、特に処理速度の改善が重要な課題となります。
処理速度改善の必要性
前述したとおり、Webアプリケーションにおいて、データベース処理はパフォーマンスに大きく影響します。特に、大量データを扱う場合、処理速度が遅いと、以下のような問題が発生する可能性があります。
-
ユーザー体験の低下： ウェブページの表示速度が遅くなり、ユーザー満足度が低下する。
-
サーバー負荷の増大： データベース処理に時間がかかり、サーバーリソースを圧迫する。
|
問題点 |
詳細 |
|
ユーザー体験の低下 |
ページ表示の遅延により、ユーザーがストレスを感じ、離脱につながる可能性があります。 |
|
サーバー負荷の増大 |
処理が完了するまでリソースが占有され、他の処理に影響が及ぶ可能性があります。 |
これらの問題を回避し、快適なユーザー体験と安定したサービス提供を実現するためには、データベース処理の速度改善が不可欠です。
大量データを扱う場合、探索・挿入・更新の観点でパフォーマンスへの影響を考慮する必要がありますが、本記事では、更新の観点を取り上げます。
大量データ挿入の観点では、いわゆるバルクインサートという対策が効果的であることはよく知られています。バルクインサートの更新処理バージョンとして、バルクアップデートという手法について紹介します。
本記事で紹介するバルクアップデートでは、CTEを活用した手法を使います。バルクアップデートについてLaravelでの利用事例も交えて解説していきます。
なお、DBはPostgreSQLを利用します。
CTE (Common Table Expression)とは？
本記事で取り上げるバルクアップデートでは、CTE (Common Table Expression) を使用します。
CTEは、SQLクエリの中で一時的に名前付きの仮想テーブルを定義できる機能です。複雑なクエリを簡潔に記述できるため、可読性と再利用性の向上が見込めます。
CTEの基本的な構文と使い方
CTE（Common Table Expression）は、SQLの中で一時的に名前付きの結果セットを定義できる機能です。複雑なクエリをシンプルに記述できるため、可読性が向上します。基本的な構文は以下の通りです。
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
|
項目 |
説明 |
|
|
CTE句の開始を示します。 |
|
|
CTEに任意の名前をつけます。 |
|
|
CTEを定義することを示します。 |
|
|
CTEの定義を囲みます。 |
|
|
CTE内で実行するSELECT文を記述します。 |
|
|
通常のSELECT文と同様にテーブルや条件を指定します。 |
CTEを使うメリット（可読性、再利用性）
CTEを使うことで、複雑なクエリをシンプルに表現できるため、コードの可読性が向上します。可読性が高いコードは、開発者間での共有やメンテナンスが容易になるというメリットがあります。
また、CTEはクエリ内で何度でも参照できるため、一度定義した内容を再利用できます。クエリの重複を減らすことで、コードの冗長さを解消し、保守性を高めることができます。
|
メリット |
説明 |
|
可読性の向上 |
複雑なクエリをシンプルに表現できるため、コードが読みやすくなる |
|
再利用性 |
一度定義したCTEはクエリ内で何度でも参照できるため、コードの重複を減らせる |
これらのメリットにより、CTEはLaravel開発におけるデータベース処理を効率化し、より保守性の高いコードを実現するための有効な手段と言えるでしょう。
LaravelでCTEを活用したバルクアップデート
通常、Laravelでは、UPDATE文を繰り返し実行する方法でデータベースの更新処理を行います。更新対象の件数が一定程度であれば、Webアプリケーションへのパフォーマンスに影響を与えることはほとんど無いといえます。
しかし、一度の更新処理で扱う件数が大量になってくると話が変わってきます。とくに、処理速度という面でパフォーマンスへの影響が無視できないものとなってきます。
そこで、Laravelでのデータベース更新処理にPostgreSQLのCTEを組み合わせることでバルクアップデートを実現し、パフォーマンスへの影響について改善を図ります。
具体的な手順は以下の通りです。
-
更新対象のデータをCTEで事前に定義します。
-
CTEを用いて、UPDATE文で結合条件を指定し、一括でデータを更新します。
|
項目 |
説明 |
|
CTE |
更新対象のデータセットを一時的に定義 |
|
UPDATE文 |
CTEと結合条件を用いて、一括更新を実行 |
この方法を用いることで、従来の複数回のUPDATE文実行に比べて、データベースへのアクセス回数が減り、処理速度の向上が見込めます。
具体的なコード例と解説
LaravelでCTEを活用したバルクアップデートを行うには、クエリビルダやEloquentではなく、生のSQLクエリの実行となります。
例えば、usersテーブルのstatusを、active_usersというCTEで取得したユーザーIDとupdated_atカラムで一括更新する場合、以下のように記述します。
DB::statement('
WITH active_users AS (
SELECT id
FROM users
WHERE status = ?
)
UPDATE users
SET status = ?,
updated_at = ?
WHERE EXISTS (
SELECT 1
FROM active_users
WHERE active_users.id = users.id
)
', ['active', 'inactive', now()]);
上記のコードでは、まずactive_usersというCTEでstatusがactiveのユーザーIDを取得します。次に、UPDATE文でusersテーブルのstatusをinactiveに更新しますが、更新対象となるのはactive_usersのIDが存在するユーザーのみです。
このようにCTEを活用することで、複数回のクエリ実行を避け、効率的にバルクアップデートを実現できます。
個別に更新内容を定義してCTEを用意する場合
PostgreSQLでは、WITH句を使ってCTEを定義し、それをUPDATE文の対象として使用することでバルクアップデートを実現できます。
-- CTEを使って更新内容のデータセットを作成
WITH update_data (
id,
column01,
column02,
column03,
column04,
column05,
updated_at
)
AS
(
VALUES
(1, 'data11', 'data12', 'data13', 'data14', 'data15', '2025/01/01 00:00:00'),
(2, 'data21', 'data22', 'data23', 'data24', 'data25', '2025/01/01 00:00:00'),
(3, 'data31', 'data32', 'data33', 'data34', 'data35', '2025/01/01 00:00:00')
)
-- CTEで作成したデータセットで更新
UPDATE users
SET
column01 = update_data.column01,
column02 = update_data.column02,
column03 = update_data.column03,
column04 = update_data.column04,
column05 = update_data.column05,
updated_at = update_data.updated_at
FROM
update_data
WHERE users.id = update_data.id;
処理速度比較：従来の方法 vs バルクアップデート(CTE)
Laravelでの更新処理を通常VerとバルクアップデートVerで作成し、処理速度を比較してみます。
通常Verのコード
public function updateNormal(array $data)
{
DB::beginTransaction();
try {
foreach ($data as $d) {
$info = [
'column01' => $d->column01,
'column02' => $d->column02,
'column03' => $d->column03,
'column04' => $d->column04,
'column05' => $d->column05,
'updated_at' => $d->updated_at,
];
DB::table('sample_tbl')
->where('id', $d->id)
->update($info);
}
DB::commit();
} catch (\Exception $e) {
DB::rollback();
}
}
バルクアップデートVerのコード
public function updateBulk(array $data)
{
// チャンク 500
$chunks = array_chunk($data, 500);
DB::beginTransaction();
try {
foreach ($chunks as $chunk) {
$this->executeBulk($chunk);
}
DB::commit();
} catch (\Exception $e) {
DB::rollback();
}
}
private function executeBulk(array $data)
{
$values = '';
$length = count($data);
foreach ($data as $i => $d) {
$updated_at = $d->updated_at->format('Y-m-d H:i:s.u');
$values .= "(
$d->id,
'{$d->column01}',
'{$d->column02}',
'{$d->column03}',
'{$d->column04}',
'{$d->column05}',
TO_TIMESTAMP('{$updated_at}', 'YYYY/MM/DD HH24:MI:SS')
)";
if ($i !== array_key_last($data)) {
$values .= ",";
}
}
$sql =
"WITH
-- 更新対象項目の定義
update_data (
id,
column01,
column02,
column03,
column04,
column05,
updated_at
)
AS
(
VALUES
-- 更新内容をセット
{$values}
)
UPDATE sample_tbl
SET
column01 = update_data.column01,
column02 = update_data.column02,
column03 = update_data.column03,
column04 = update_data.column04,
column05 = update_data.column05,
updated_at = update_data.updated_at
FROM
update_data
WHERE sample_tbl.id = update_data.id
";
$updateCount = DB::affectingStatement($sql);
// 更新結果件数と更新対象件数が一致しない場合は例外スロー
if ($updateCount !== $length) {
throw new Exception('Update failed');
}
}
従来のUPDATE文を複数回実行する方法と、CTEを使ったバルクアップデートを比較すると、その処理速度に大きな改善が見られました。
|
実行方法 |
処理 時間 (1回目) |
処理 時間 (2回目) |
処理 時間 (3回目) |
処理 時間 (4回目) |
処理 時間 (平均) |
|
従来のUPDATE文を15,000回実行 |
27.27秒 |
32.81秒 |
27.46秒 |
29.62秒 |
29.29秒 |
|
15,000件の対象を 500件ごとにバルクアップデートで実行 |
1.01秒 |
1.17秒 |
1.01秒 |
1.09秒 |
1.07秒 |
上記は15,000件のデータを更新する処理を行った場合のベンチマーク結果です。CTEを使ったバルクアップデートでは、従来の方法と比較して約27倍の高速化を実現しました。
従来の方法では、更新対象のデータごとにUPDATE文を発行するため、データベースとの通信回数が増加し、処理が遅くなっていました。一方、CTEを使ったバルクアップデートでは、1回のクエリで複数のデータをまとめて更新できるため、データベースとの通信回数を大幅に削減でき、処理速度が大幅に向上したと思われます。
まとめ
Laravelで通常どおりの更新処理を実装すると、大量データを扱う際の処理時間が無視できないものとなっていました。今回のCTEを活用したバルクアップデートは、Webアプリケーションのパフォーマンス向上に大きく貢献します。
|
処理方法 |
処理時間 |
|
従来のUPDATE文を15,000回実行 |
29.29秒 |
|
15,000件の対象を 500件ごとにバルクアップデートで実行 |
1.07秒 |
今回のベンチマーク結果から、CTEの利用は、特に大量データの更新処理において有用な施策となりうると考えます。従来の複数回のUPDATE文実行と比較して、データベースとの通信回数を削減することができるため、処理時間を大幅に短縮できるからです。
バルクアップデートは、バルクインサートと比べて聞き馴染みのない手法かもしれませんが、CTEの使い方がわかってしまえばそれほど難しい仕組みではないと気づくことができるのではないでしょうか。
フレームワークを使用しているからといってフレームワークでできることに固執せず、データベース側の領域にも手を出し、Webアプリケーションのパフォーマンス改善に対して総合的なアプローチができるようになることが大切ですね。