SQLにおける大規模テーブルの最適化方法

Posted at 2025-04-08

前書き

大規模テーブルの最適化は、昔からよく議論されてきたテーマですが、ビジネスの規模が拡大するにつれて、誰しもが一度は「はまる」ものです。

多くの開発者のデータベースは、初期段階ではパフォーマンスが良好で、クエリもスムーズに動作します。しかし、テーブル内のデータ量が数千万件規模になると、パフォーマンス問題が次々と表面化してきます。たとえば、クエリの遅延、書き込みの詰まり、ページネーションの遅延、さらには時折サーバーダウンすることさえあります。

このとき、「データベースがダメなのでは？」「もっと強力なハードウェアにアップグレードすべきでは？」と考える人もいるかもしれません。

しかし、実際には多くの場合、根本的な原因は最適化が不十分なことにあります。

今回は、問題の本質から出発し、大規模テーブルにおけるよくあるパフォーマンスのボトルネックを一つひとつ分析し、それに対してどのように段階的に最適化を進めていくかを解説します。少しでも参考になれば幸いです。

1 なぜ大規模テーブルは遅くなるのか？

最適化を行う前に、まずは大規模テーブルにおけるパフォーマンス問題の根本原因を明らかにしましょう。データ量が増えると、なぜデータベースは遅くなるのでしょうか？

1.1 ディスク IO のボトルネック

大規模テーブルのデータはディスクに保存されています。データベースによるクエリ処理は通常、データブロックの読み込みを伴います。

データ量が多くなると、1 回のクエリで複数のディスクブロックから大量のデータを読み込む必要が出てきます。このとき、ディスクの読み書き速度がクエリ性能の直接的な制約となります。

例：

たとえば、orders という注文テーブルに 5000 万件のデータがあり、あるユーザーの直近 10 件の注文を取得したい場合：

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;

インデックスが存在しない場合、データベースはテーブル全体をスキャンしてソートする必要があり、パフォーマンスは著しく低下します。

1.2 インデックスの欠如・インデックスの無効化

クエリでインデックスが使用されないと、データベースは全表スキャン（Full Table Scan）を行うことになります。つまり、テーブル内のすべての行を 1 行ずつ読み込む必要があります。

このような操作は、データ量が数千万件を超えると非常にリソースを消費し、性能が急激に悪化します。

例：

次のような条件でクエリを書くと：

SELECT * FROM orders WHERE DATE(order_time) = '2025-01-01';

ここで DATE() 関数を使用しているため、データベースはすべてのレコードの order_time フィールドに対して関数計算を行う必要があり、結果としてインデックスが無効化されます。

1.3 ページネーション性能の低下

ページネーション（ページ区切りクエリ）は、大規模テーブルで非常に一般的なユースケースですが、深いページ（例：100 ページ目以降）を取得する場合、性能問題が発生します。

たとえ 10 件しか取得しなくても、データベースはその前にあるすべてのレコードを読み込む必要があります。

例：

1000 ページ目の 10 件を取得するクエリ：

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

この SQL は、まず最初に 9990 件のデータを取得し、それらを破棄してから、後ろの 10 件を返すという動作になります。

ページ数が増えるほど、クエリのパフォーマンスはどんどん悪化します。

1.4 ロックの競合

高トラフィック環境では、複数のスレッドが同じテーブルに対して同時に挿入・削除・更新・検索などの操作を行うことになり、行ロックやテーブルロックの競合が発生し、これが性能低下を引き起こします。

2 性能最適化の全体的な考え方

性能最適化の本質は、不必要な IO、計算、ロック競合を減らすことであり、目的はデータベースに「無駄な作業」をさせないことです。

最適化の全体的なアプローチは以下のポイントに集約できます：

テーブル設計を適切に行う：不要なフィールドを避け、分割できるデータは分割する。
インデックスを効率的に使う：適切なインデックス構造を設計し、インデックスの無効化を防ぐ。
SQL を最適化する：クエリ条件を正確に書き、全表スキャンを極力避ける。
データの分割：水平分割・垂直分割により、1 テーブルあたりのデータ量を削減する。
キャッシュと非同期処理の導入：データベースへの直接的な負荷を軽減する。

このあと、それぞれの方法について詳しく説明していきます。

3 テーブル設計の最適化

テーブル構造は、データベース性能最適化の基盤です。設計が不適切なテーブルは、後々のクエリ処理やストレージ性能に悪影響を及ぼします。

3.1 フィールド型の簡素化

フィールドの型は、ストレージサイズとクエリ性能に直接影響します。

INT で済むものに BIGINT を使わない。
VARCHAR(100)で十分な場合は TEXT を使わない。
時間フィールドには TIMESTAMP または DATETIME を使い、CHAR や VARCHAR で保存しない。

例：

-- 非推奨
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    order_status VARCHAR(255),
    remarks TEXT
);

-- 最適化後
CREATE TABLE orders (
    id BIGINT,
    user_id INT UNSIGNED,
    order_status TINYINT,  -- ステータスは列挙型で表現
    remarks VARCHAR(500)   -- 最大長を制限
);

このようにすることで、ストレージ容量を節約でき、クエリ性能も向上します。

3.2 テーブルの分割：垂直分割と水平分割

垂直分割

フィールドが多すぎるテーブルで、頻繁に参照されないフィールドがある場合、ビジネスロジックに基づいてテーブルを複数の小さなテーブルに分割します。

例：注文テーブルを orders_basic と orders_details に分割する。

-- 基本情報テーブル
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id INT UNSIGNED,
    order_time TIMESTAMP
);

-- 詳細情報テーブル
CREATE TABLE orders_details (
    id BIGINT PRIMARY KEY,
    remarks VARCHAR(500),
    shipping_address VARCHAR(255)
);

水平分割

1 つのテーブルにデータが多すぎる場合、一定のルールに従って複数のテーブルに分割します。

例：user_id に基づいて注文テーブルを水平分割する。

orders_0  -- user_id % 2 = 0 の注文を格納
orders_1  -- user_id % 2 = 1 の注文を格納

分割後は 1 テーブルあたりのデータ量が大幅に減り、クエリ性能が明らかに向上します。

4 インデックス最適化

インデックスは、データベース性能最適化における「最強の武器」です。しかし、多くの人はインデックスの使い方をよく理解しておらず、逆にパフォーマンスを悪化させてしまうこともあります。

4.1 適切なインデックスの作成

頻繁に検索に使われるフィールドに対しては、インデックスを作成する必要があります。例えば、主キー、外部キー、WHERE 句の条件として使われるフィールドなどです。

例：

CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);

上記の複合インデックスは、user_id および order_time の両方のクエリを高速化できます。

4.2 インデックスの無効化を避ける

■ インデックスフィールドに関数や演算を使わない

誤り：

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

最適化：

SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'
  AND order_time < '2023-01-02 00:00:00';

■ 暗黙的な型変換に注意する

誤り：

SELECT * FROM orders WHERE user_id = '123';

最適化：

SELECT * FROM orders WHERE user_id = 123;

5 SQL 最適化

5.1 取得するフィールドを絞る

必要なフィールドだけをクエリするようにし、SELECT * は避けましょう。

-- 非推奨
SELECT * FROM orders WHERE user_id = 123;

-- 最適化
SELECT id, order_time FROM orders WHERE user_id = 123;

5.2 ページネーションの最適化

深いページを取得する際は、「遅延カーソル」方式を使って、不要なスキャンを避けましょう。

-- 深いページネーション（性能が悪い）
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

-- 最適化：カーソル方式
SELECT * FROM orders WHERE order_time < '2023-01-01 12:00:00'
  ORDER BY order_time DESC LIMIT 10;

6 データベースの分割

6.1 水平分割によるデータベース・テーブルの分散

単一のテーブルを分割してもパフォーマンスが改善されない場合、データベース自体を複数に分け、テーブルもそれぞれのデータベースに分散させることで負荷を軽減することができます。

よく使われる分割ルールは以下の通りです：

ユーザー ID によるハッシュ分割（例：user_id % N）
日時によるパーティショニング（例：月ごと・年ごと）

7 キャッシュと非同期処理

7.1 ホットデータのキャッシュに Redis を使用する

アクセス頻度が高いデータ（ホットデータ）を Redis などのインメモリキャッシュに保存することで、データベースへのクエリ回数を減らし、負荷を大幅に下げることができます。

例：

// キャッシュからデータを取得
String result = redis.get("orders:user:123");
if (result == null) {
    result = database.query("SELECT * FROM orders WHERE user_id = 123");
    redis.set("orders:user:123", result, 3600); // キャッシュを1時間保持
}