はじめに
皆さん、こんにちは。
データベースのパフォーマンスは、サービスの成長とともに重要な課題となります。小規模なうちは問題なく動作していたクエリが、データ量の増加に伴って急激に遅くなることは珍しくありません。
そこで本日は、数百万件のレコードを扱うプロジェクトにおいて、クエリをどのように最適化すべきかについて、基本的なポイントをいくつか共有したいと思います。
基本的な内容ではありますが、皆さんの開発の参考になれば嬉しいです。
本記事が皆様のお役に立てれば幸いです。
0. 基本的なミスを避ける
大規模なシステムを構築する前に、まず基本的な設計原則を押さえておくことが重要です。
0.1 正しいテーブル設計
データベース設計は、システムのパフォーマンスに大きな影響を与えます。
❌ 悪い例：正規化されていない設計
-- 注文テーブル（非正規化）
CREATE TABLE orders (
id INT PRIMARY KEY,
user_name VARCHAR(100), -- ユーザー名を直接保存
user_email VARCHAR(100), -- メールアドレスを直接保存
user_address VARCHAR(255), -- 住所を直接保存
product_name VARCHAR(100), -- 商品名を直接保存
product_price DECIMAL(10,2), -- 商品価格を直接保存
order_date DATETIME
);
問題点：
- データの重複が発生し、ストレージを無駄に消費する
- ユーザー情報や商品情報の更新時に、全ての関連レコードを更新する必要がある
- データの整合性を保つことが困難になる
✅ 良い例：正規化された設計
-- ユーザーテーブル
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
address VARCHAR(255)
);
-- 商品テーブル
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);
-- 注文テーブル（正規化済み）
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
product_id INT,
order_date DATETIME,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
メリット：
- データの重複を排除できる
- 更新時の整合性を保ちやすい
- ストレージ効率が向上する
0.2 N+1問題を避ける
N+1問題は、パフォーマンス低下の最も一般的な原因の一つです。
❌ 悪い例：N+1クエリ
// Node.js + MySQL の例
async function getOrdersWithUsers() {
// 1回目のクエリ：全ての注文を取得
const orders = await db.query('SELECT * FROM orders LIMIT 100');
// N回のクエリ：各注文のユーザー情報を個別に取得
for (const order of orders) {
const user = await db.query(
'SELECT * FROM users WHERE id = ?',
[order.user_id]
);
order.user = user[0];
}
return orders;
}
// 合計: 1 + 100 = 101回のクエリが実行される！
問題点：
- 100件の注文に対して101回のクエリが発生
- データベースへの接続オーバーヘッドが増大
- レスポンス時間が大幅に悪化
✅ 良い例：JOINを使用
// JOINを使用して1回のクエリで取得
async function getOrdersWithUsers() {
const query = `
SELECT
orders.*,
users.name AS user_name,
users.email AS user_email
FROM orders
INNER JOIN users ON orders.user_id = users.id
LIMIT 100
`;
return await db.query(query);
}
// 合計: 1回のクエリで完了！
✅ 良い例：IN句を使用（バッチ取得）
// IN句を使用してバッチ取得
async function getOrdersWithUsers() {
// 1回目：注文を取得
const orders = await db.query('SELECT * FROM orders LIMIT 100');
// ユーザーIDを抽出
const userIds = [...new Set(orders.map(o => o.user_id))];
// 2回目：関連するユーザーを一括取得
const users = await db.query(
'SELECT * FROM users WHERE id IN (?)',
[userIds]
);
// メモリ上でマッピング
const userMap = new Map(users.map(u => [u.id, u]));
orders.forEach(order => {
order.user = userMap.get(order.user_id);
});
return orders;
}
// 合計: 2回のクエリで完了！
| 方法 | クエリ数 | 適用場面 |
|---|---|---|
| N+1（悪い例） | N+1回 | 使用すべきではない |
| JOIN | 1回 | シンプルな関連取得 |
| IN句（バッチ） | 2回 | 複雑な処理が必要な場合 |
1. ページネーションの最適化
1.1 なぜLIMIT OFFSETは大量データに向かないのか
多くの開発者が最初に学ぶページネーション方法は LIMIT OFFSET ですが、大規模データでは深刻なパフォーマンス問題を引き起こします。
LIMIT OFFSETの仕組み
-- 100万件目から10件を取得
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000000;
データベースの内部動作：
1. ORDER BY に従って全データをソート
2. 先頭から1,000,000件を読み込む（スキップするため）
3. 1,000,001件目から10件を返却
4. 最初の1,000,000件は破棄される
→ つまり、1,000,010件のデータを読み込んで、10件だけ返す！
❌ 悪い例：LIMIT OFFSET
-- ページ1（高速）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;
-- 実行時間: 約5ms
-- ページ100（やや遅い）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 990;
-- 実行時間: 約50ms
-- ページ100,000（非常に遅い）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 999990;
-- 実行時間: 約5,000ms以上！
パフォーマンス比較グラフ（概念図）：
実行時間
│
5s │ ×
│ ×
│ ×
│ ×
│ ×
│ ×
50ms│ ×
└──────────────────────────────────────
1 10K 50K 100K 500K 1M ページ番号
1.2 大量データのページネーション解決策
✅ 解決策1：カーソルベースページネーション（Cursor-based Pagination）
最後に取得したレコードのIDを基準にして次のページを取得します。
-- 最初のページ
SELECT * FROM users
ORDER BY id
LIMIT 10;
-- 次のページ（last_id = 前ページの最後のID）
SELECT * FROM users
WHERE id > :last_id
ORDER BY id
LIMIT 10;
// Node.js実装例
async function getUsersWithCursor(cursor = null, limit = 10) {
let query = 'SELECT * FROM users';
const params = [];
if (cursor) {
query += ' WHERE id > ?';
params.push(cursor);
}
query += ' ORDER BY id LIMIT ?';
params.push(limit);
const users = await db.query(query, params);
return {
data: users,
nextCursor: users.length > 0 ? users[users.length - 1].id : null
};
}
メリット：
- 一定の実行時間を維持できる（O(1)）
- インデックスを効率的に活用できる
- 大量データでも高速に動作する
デメリット：
- 特定のページに直接ジャンプできない
- 前のページに戻ることが難しい
✅ 解決策2：Keyset Pagination（複合条件）
複数のカラムでソートする場合の解決策です。
-- created_at と id の複合条件
SELECT * FROM users
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 10;
-- または、より互換性の高い書き方
SELECT * FROM users
WHERE created_at > :last_created_at
OR (created_at = :last_created_at AND id > :last_id)
ORDER BY created_at, id
LIMIT 10;
✅ 解決策3：Deferred Join（遅延結合）
IDだけを先に取得し、その後で詳細データを取得します。
-- ❌ 遅い：全カラムを含むOFFSETクエリ
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 100000;
-- ✅ 速い：Deferred Join
SELECT u.*
FROM users u
INNER JOIN (
SELECT id FROM users
ORDER BY id
LIMIT 10 OFFSET 100000
) AS tmp ON u.id = tmp.id;
なぜ速いのか：
- サブクエリではIDのみを扱うため、インデックスだけで処理できる
- 実際のデータ取得は10件分のみ
解決策の比較
| 方法 | 速度 | 直接ジャンプ | 実装難易度 | 適用場面 |
|---|---|---|---|---|
| LIMIT OFFSET | △〜× | ✅ | 簡単 | 小規模データ |
| カーソルベース | ✅ | ❌ | 普通 | 無限スクロール、API |
| Keyset | ✅ | ❌ | やや難 | 複合ソート |
| Deferred Join | ○ | ✅ | 普通 | 既存システム改善 |
2. インデックスの最適化
インデックスは、データベースパフォーマンスを向上させる最も効果的な手段の一つです。
2.1 インデックスの種類
1. B-Tree インデックス（最も一般的）
-- 単一カラムインデックス
CREATE INDEX idx_users_email ON users(email);
-- 使用例
SELECT * FROM users WHERE email = 'test@example.com';
-- → インデックスが使用される ✅
特徴：
- 等価比較（=）、範囲検索（<, >, BETWEEN）に効果的
- ORDER BY、GROUP BY の高速化にも有効
- MySQLのデフォルトインデックスタイプ
2. 複合インデックス（Composite Index）
-- 複合インデックス
CREATE INDEX idx_users_status_created
ON users(status, created_at);
-- ✅ 効果あり：左端のカラムから使用
SELECT * FROM users WHERE status = 'active';
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
-- ❌ 効果なし：左端のカラムを飛ばす
SELECT * FROM users WHERE created_at > '2024-01-01';
重要：左端原則（Leftmost Prefix Rule）
インデックス: (A, B, C)
✅ 使用可能な条件:
- WHERE A = ?
- WHERE A = ? AND B = ?
- WHERE A = ? AND B = ? AND C = ?
❌ 使用不可能な条件:
- WHERE B = ?
- WHERE C = ?
- WHERE B = ? AND C = ?
3. カバリングインデックス（Covering Index）
クエリに必要な全てのカラムがインデックスに含まれている場合、テーブルへのアクセスが不要になります。
-- インデックス作成
CREATE INDEX idx_covering
ON orders(user_id, status, created_at);
-- ✅ カバリングインデックス（テーブルアクセス不要）
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 100;
-- ❌ 通常のインデックス（テーブルアクセス必要）
SELECT *
FROM orders
WHERE user_id = 100;
4. 全文検索インデックス（Full-Text Index）
-- 全文検索インデックス
CREATE FULLTEXT INDEX idx_fulltext_content
ON articles(title, content);
-- 使用例
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('データベース 最適化');
5. ユニークインデックス（Unique Index）
-- ユニークインデックス
CREATE UNIQUE INDEX idx_unique_email
ON users(email);
-- 重複を防ぐ + 検索の高速化
2.2 大規模データのインデックス最適化
ポイント1：選択性（Selectivity）を考慮する
-- ❌ 悪い例：選択性が低いカラム
CREATE INDEX idx_gender ON users(gender);
-- gender は 'M', 'F', 'Other' の3値程度
-- → インデックスの効果が薄い
-- ✅ 良い例：選択性が高いカラム
CREATE INDEX idx_email ON users(email);
-- email はほぼユニーク
-- → インデックスが効果的
選択性の計算：
-- 選択性 = ユニーク値の数 / 全レコード数
SELECT
COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
COUNT(DISTINCT email) / COUNT(*) AS email_selectivity
FROM users;
-- 結果例：
-- gender_selectivity: 0.000003 (低い)
-- email_selectivity: 0.999999 (高い)
ポイント2：複合インデックスのカラム順序
-- シナリオ：status = 'active' かつ created_at で範囲検索
-- ❌ 悪い順序
CREATE INDEX idx_bad ON orders(created_at, status);
-- ✅ 良い順序（等価条件 → 範囲条件）
CREATE INDEX idx_good ON orders(status, created_at);
原則：
- 等価条件（=）のカラムを先に
- 範囲条件（<, >, BETWEEN）のカラムを後に
- 選択性が高いカラムを先に
ポイント3：不要なインデックスを削除する
-- 使用されていないインデックスを確認（MySQL）
SELECT
table_name,
index_name,
stat_value AS rows_read
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_diff_pfx01'
ORDER BY rows_read;
-- 重複インデックスを確認
-- (A) と (A, B) がある場合、(A) は不要な可能性
2.3 インデックス最適化のベストプラクティス
-- EXPLAINで実行計画を確認
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN出力例：
| 項目 | 値 |
|---|---|
| id | 1 |
| select_type | SIMPLE |
| table | users |
| type | ref ✅ |
| possible_keys | idx_email |
| key | idx_email |
| rows | 1 ✅ |
| Extra | NULL |
EXPLAINの重要な項目：
| 項目 | 良い値 | 悪い値 |
|---|---|---|
| type | const, eq_ref, ref | ALL, index |
| rows | 小さい値 | 大きい値 |
| Extra | Using index | Using filesort, Using temporary |
3. データベースの分割戦略
データ量が増大すると、単一のデータベースでは限界が来ます。ここでは、3つの主要な分割戦略をご紹介します。
3.1 PARTITION（パーティショニング）
概念
一つのテーブルを論理的に複数の部分に分割する技術です。データベースサーバーは同一のままです。
┌─────────────────────────────────────┐
│ orders テーブル │
├───────────┬───────────┬─────────────┤
│ 2024年1月 │ 2024年2月 │ 2024年3月 │
│ partition │ partition │ partition │
└───────────┴───────────┴─────────────┘
↓
単一サーバー上に存在
実装方法
-- RANGE パーティション（範囲による分割）
CREATE TABLE orders (
id INT NOT NULL,
user_id INT,
amount DECIMAL(10,2),
created_at DATE NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- LIST パーティション（リストによる分割）
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(100),
region VARCHAR(20) NOT NULL,
PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
PARTITION p_asia VALUES IN ('JP', 'KR', 'CN'),
PARTITION p_europe VALUES IN ('UK', 'DE', 'FR'),
PARTITION p_america VALUES IN ('US', 'CA', 'BR')
);
-- HASH パーティション（ハッシュによる分割）
CREATE TABLE sessions (
id INT NOT NULL,
user_id INT NOT NULL,
data TEXT,
PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id)
PARTITIONS 8;
メリット・デメリット
| メリット | デメリット |
|---|---|
| パーティションプルーニングにより検索が高速化 | パーティションキーの選択が重要 |
| 古いデータの削除が高速（DROP PARTITION） | PRIMARY KEYにパーティションキーを含める必要がある |
| 実装が比較的容易 | パーティション数の上限がある |
| 運用の透明性（アプリ変更不要） | パーティション間の結合は遅い |
適用場面
- 時系列データ（ログ、注文履歴など）
- 地域別データ
- 古いデータの定期的な削除が必要な場合
3.2 SHARDING（シャーディング）
概念
データを複数の独立したデータベースサーバーに分散させる技術です。
┌─────────────┐
│ Router │
└──────┬──────┘
┌───────────────┼───────────────┐
↓ ↓ ↓
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Shard 1 │ │ Shard 2 │ │ Shard 3 │
│ user_id │ │ user_id │ │ user_id │
│ 1-1000 │ │1001-2000 │ │2001-3000 │
└──────────┘ └──────────┘ └──────────┘
Server A Server B Server C
実装方法
// シャーディングルーター（Node.js例）
class ShardRouter {
constructor() {
this.shards = [
{ id: 0, host: 'shard0.example.com', range: [0, 999999] },
{ id: 1, host: 'shard1.example.com', range: [1000000, 1999999] },
{ id: 2, host: 'shard2.example.com', range: [2000000, 2999999] },
];
}
// Range-based sharding
getShardByUserId(userId) {
for (const shard of this.shards) {
if (userId >= shard.range[0] && userId <= shard.range[1]) {
return shard;
}
}
throw new Error('Shard not found');
}
// Hash-based sharding（より均等な分散）
getShardByHash(userId) {
const shardIndex = userId % this.shards.length;
return this.shards[shardIndex];
}
async query(userId, sql, params) {
const shard = this.getShardByHash(userId);
const connection = await getConnection(shard.host);
return connection.query(sql, params);
}
}
// 使用例
const router = new ShardRouter();
const user = await router.query(12345,
'SELECT * FROM users WHERE id = ?',
[12345]
);
メリット・デメリット
| メリット | デメリット |
|---|---|
| 水平スケーリングが可能 | 実装が複雑 |
| 各サーバーの負荷を分散できる | クロスシャードクエリが困難 |
| 障害の影響範囲を限定できる | トランザクション管理が複雑 |
| 理論上は無限にスケール可能 | リシャーディングが大変 |
適用場面
- 数億件以上の大規模データ
- 地理的に分散したユーザーベース
- 高い書き込みスループットが必要な場合
3.3 REPLICATION（レプリケーション）
概念
同じデータを複数のサーバーにコピーする技術です。主に読み取り性能の向上と可用性の確保に使用されます。
┌───────────────────┐
│ Master (書き込み) │
└─────────┬─────────┘
│ 複製
┌─────────┼─────────┐
↓ ↓ ↓
┌─────────┐ ┌─────────┐ ┌─────────┐
│ Replica │ │ Replica │ │ Replica │
│ 1 │ │ 2 │ │ 3 │
│ (読取) | │ (読取) │ │ (読取) │
└─────────┘ └─────────┘ └─────────┘
実装方法
// Read/Write分離の実装例（Node.js）
class DatabasePool {
constructor() {
// マスター（書き込み用）
this.master = mysql.createPool({
host: 'master.example.com',
user: 'app',
password: 'password',
database: 'myapp'
});
// レプリカ（読み取り用）
this.replicas = [
mysql.createPool({ host: 'replica1.example.com', /* ... */ }),
mysql.createPool({ host: 'replica2.example.com', /* ... */ }),
mysql.createPool({ host: 'replica3.example.com', /* ... */ }),
];
this.currentReplica = 0;
}
// ラウンドロビンでレプリカを選択
getReadConnection() {
const replica = this.replicas[this.currentReplica];
this.currentReplica = (this.currentReplica + 1) % this.replicas.length;
return replica;
}
// 読み取りクエリ
async read(sql, params) {
const pool = this.getReadConnection();
return pool.query(sql, params);
}
// 書き込みクエリ
async write(sql, params) {
return this.master.query(sql, params);
}
}
// 使用例
const db = new DatabasePool();
// 読み取り → レプリカへ
const users = await db.read('SELECT * FROM users WHERE status = ?', ['active']);
// 書き込み → マスターへ
await db.write('INSERT INTO users (name, email) VALUES (?, ?)', ['Taro', 'taro@example.com']);
メリット・デメリット
| メリット | デメリット |
|---|---|
| 読み取り性能が向上 | レプリケーション遅延が発生 |
| 高可用性（フェイルオーバー） | 書き込み性能は向上しない |
| バックアップの取得が容易 | データの整合性に注意が必要 |
| 実装が比較的容易 | ストレージコストが増加 |
適用場面
- 読み取りが多いワークロード
- 高可用性が必要なシステム
- 地理的に分散した読み取りが必要な場合
3.4 三つの戦略の比較
| 特徴 | Partition | Sharding | Replication |
|---|---|---|---|
| 目的 | クエリ最適化、管理性向上 | 水平スケーリング | 読み取り性能、可用性 |
| サーバー数 | 1台 | 複数台 | 複数台 |
| データ分散 | 論理的 | 物理的 | コピー |
| 書き込み性能 | 変わらない | 向上 | 変わらない |
| 読み取り性能 | 向上 | 向上 | 大幅に向上 |
| 実装難易度 | 低 | 高 | 中 |
| 運用コスト | 低 | 高 | 中 |
組み合わせの例
実際の大規模システムでは、これらを組み合わせて使用することが多いです。
┌────────────────────────────────────────────────┐
│ クライアント │
└────────────────────────┬───────────────────────┘
↓
┌────────────────────────────────────────────────┐
│ シャーディングルーター │
└──────┬─────────────────────────────────┬───────┘
↓ ↓
┌──────────────────┐ ┌──────────────────┐
│ Shard 1 │ │ Shard 2 │
│ ┌──────────┐ │ │ ┌──────────┐ │
│ │ Master │ │ │ │ Master │ │
│ └────┬─────┘ │ │ └────┬─────┘ │
│ │ │ │ │ │
│ ┌────┴─────┐ │ │ ┌────┴─────┐ │
│ │ Replicas │ │ │ │ Replicas │ │
│ └──────────┘ │ │ └──────────┘ │
│ │ │ │
│ (Partitioned) │ │ (Partitioned) │
└──────────────────┘ └──────────────────┘
まとめ
本記事では、大規模データベースの最適化について、以下のポイントをご紹介いたしました。
最適化のステップ
-
基本を固める
- 適切なテーブル設計（正規化）
- N+1問題の回避
-
クエリを最適化する
- LIMIT OFFSETの代わりにカーソルベースページネーション
- 適切なインデックスの設計
-
スケールアウト
- Partition：管理性向上、クエリ最適化
- Sharding：水平スケーリング
- Replication：読み取り性能、可用性
最後に
データベースの最適化は、一度行えば終わりというものではありません。サービスの成長に合わせて継続的に見直していく必要があります。
また、最適化を行う前に、まず現状のボトルネックを正確に把握することが重要です。EXPLAINやスロークエリログを活用して、問題のあるクエリを特定してから改善に取り組むことをお勧めいたします。
本記事が皆様の開発のお役に立てれば幸いです。