LoginSignup
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@devtoona(toona)

DBパフォーマンスチューニング系のタスクでよくやることまとめ

Posted at

背景

DBパフォーマンスチューニング系のタスクを振られたときに、進め方を忘れがちなので備忘録にまとめてみた。

1. DBバージョンの確認

まずは、対象となるDBのバージョンを確認する。
これはチューニングの方向性に直結するため、必須ステップ。

-- MySQLの場合
SELECT VERSION();

📌 チェックポイント

  • MySQL 5.6 / 5.7 / 8.0 によって機能差がある
  • 特定の機能が利用可能か事前に把握する

2. 現状のパフォーマンス確認

速度を確認し、どの処理がネックになっているかを洗い出す。

  • Laravelなら Clockwork や Laravel Debugbar
  • CLIツールなら SHOW PROFILE, pt-query-digest

3. メモリ使用量の確認

速度だけでなく、該当処理のメモリ使用量にも注目。

dockerの場合は、以下をよく使う。

docker stats mysql-container-name

あとは、ピークメモリのログをスクリプトに埋め込む。


$memory = memory_get_peak_usage(true);
$memoryMB = round($memory / 1024 / 1024, 2);

\Log::info("🔍 Peak memory usage: {$memoryMB} MB");

4. ネックになっているクエリの特定

  • Laravelで DB::listen() を使ったログ出力
  • slow query log を有効化してスロークエリを洗い出す
  • Debuggerでリクエストを送信して確認するのもよくやる

5. クエリのEXPLAINで実行計画を見る

SQLクライアントで EXPLAIN を付けて、どのようにインデックスが使われているかを確認。

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

確認項目

  • type が ALL になっていないか
  • key 、possible_keysにインデックス名が出ているか
  • rows が極端に多くないか

注意点

クエリの EXPLAIN を使って実行計画を確認する際、type が ALL になっていたからといって、必ずしも「インデックスがない」とは限りません。
MySQLが「インデックスより全スキャンの方が早い」と判断した場合も、ALL になることがあります。
そのため、possible_keys（使用可能なインデックス）と key（実際に使われたインデックス）の両方を確認するのが重要です。

6. インデックスの最適化

インデックスが効いていない場合、適切なインデックスを作成する。

-- 単一カラムインデックス
CREATE INDEX idx_email ON users(email);

-- 複合インデックス
CREATE INDEX idx_status_email ON users(status, email);

✅ MySQL 8.0以降では「関数インデックス」も使える！

たとえば DATE(created_at) をWHERE句でよく使う場合、こうすることでインデックスが効く：

CREATE INDEX idx_date_created_at ON orders((DATE(created_at)));
  • 通常は関数が使われたカラムではインデックスが効かない
  • MySQL 8.0以降は「式インデックス（Functional Index）」が利用可能！

⚠️ 注意点

  • WHERE句やORDER BYの順序に注意
  • インデックス過剰になるとINSERT/UPDATEが遅くなるのでバランスが大事

7. ストレージのキャッシュ確認（EBS）

storage_path() などを使ってEBS上にファイルを出力する場合、ディスクI/Oがボトルネックになることがある。
特に、ローカルでは再現しないけど開発や本番環境では再現する見たいなケースの場合、確認しておいたほうがいい。

df -h
iostat -xz 1

キャッシュが効いていない、もしくはEBS帯域制限がかかっている可能性も検討。

8. chunkById を使った逐次処理

一括取得ではなく、主キー単位で分割してメモリ使用量を抑える。

SELECT * FROM users WHERE id > 0 ORDER BY id ASC LIMIT 1000;
-- 次回は前回の最大IDを控えておいて
SELECT * FROM users WHERE id > 1000 ORDER BY id ASC LIMIT 1000;
  • 主キーの昇順を活かし、OFFSET を使わないことで高速かつ安定
  • メモリ消費を最小限に抑えて大量データを処理可能

Laravelでは、上記のような逐次処理を簡潔に記述できる chunkById メソッドが用意されています。

DB::table('users')->orderBy('id')->chunkById(1000, function ($users) {
    foreach ($users as $user) {
        // 個別処理
    }
});

9. 非正規データを使った柔軟な処理

場合によっては正規化を優先せず、非正規データ（JSON形式など）をそのまま保存・活用することも有効。

✅ 例：ユーザ情報をJOINせず、JSONで埋め込む

DB::table('activity_logs')->insert([
    'user_id' => $user->id,
    'user_info' => json_encode([
        'name' => $user->name,
        'email' => $user->email,
        'plan' => $user->plan,
    ]),
    'action' => 'updated_profile',
    'created_at' => now(),
]);

メリット：

  • JOIN不要でパフォーマンスが安定する
  • 当時の状態を履歴として保持できる（変更による影響を受けない）

デメリット：

  • ストレージ使用量が増える
  • 一貫性が担保されない可能性あり

使い所：

  • 履歴・ログ・非同期処理系で「当時の状態」を残したいとき
  • JOINによる複雑化・性能低下を避けたいとき

10. サブクエリや一時テーブルの活用

逐次処理が困難な場合は、サブクエリで事前に集約・フィルタを行い、JOINで結合。

SELECT a.user_id, a.total, b.last_login
FROM (
  SELECT user_id, SUM(price) AS total
  FROM charges
  GROUP BY user_id
) a
LEFT JOIN (
  SELECT user_id, MAX(logged_in_at) AS last_login
  FROM logins
  GROUP BY user_id
) b ON a.user_id = b.user_id;

11. 設計から見直す（集計パフォーマンスを改善する構造）

実際の現場では、毎回 API 内で SUM() を使って合計値を算出したり、JOIN + GROUP BY を多用して仮想テーブル的に処理しているパターンもよく見かけます。

✅ 問題点：

  • アクセスごとに重い集計クエリが発生
  • JOINが複雑化し、結果的にINDEXが効きづらくなる
  • 集計に伴うI/O負荷が無視できないほどに成長する

✅ 対策：集計済みの summary テーブルを設計段階で導入する

例）user_calorie_summary テーブルを設けておく

CREATE TABLE user_calorie_summary (
    user_id BIGINT PRIMARY KEY,
    total_calories BIGINT,
    total_fat DOUBLE,
    updated_at TIMESTAMP
);
  • cron や job で定期的に更新
  • API はこのテーブルから SELECT するだけ

12. APIでの重処理をJobに逃す設計

重い処理を API リクエストの中で直接やってしまうと、レスポンスタイムが悪化しやすく、UXも劣化します。

✅ よくあるアンチパターン：

  • API呼び出し時に DB から全データ取得して
  • その場で CSV を生成し
  • その場でレスポンスとして返却しようとする

✅ ベストプラクティス：

  • Step1：Job に処理を逃がす
    • dispatch(new GenerateCsvExport($params));
  • Step2：Job内で DB処理＋CSV生成まで完了させ、S3などに保存
  • Step3：APIでは「CSVダウンロードリンク」を返すだけ

✅ メリット：

  • 処理時間が非同期化され UX 向上
  • タイムアウトやメモリ不足を回避しやすい
  • ジョブ失敗時のリトライやエラー監視がしやすくなる
// コントローラ
GenerateCsvExportJob::dispatch($filters);

// ジョブ
public function handle()
{
    $data = $this->getDataFromDB();
    $csv = $this->generateCsv($data);
    Storage::put('exports/result.csv', $csv);
}

13. 同時接続時のみ発生するかどうかの切り分け

パフォーマンス問題が「特定の時間帯やアクセス集中時にのみ発生する」場合は、同時接続数の増加によるリソース枯渇の可能性が高いです。

特に以下を確認しましょう：

  • SHOW STATUS LIKE 'Threads_connected'; で同時接続数のピークを監視
  • sort_buffer_size や tmp_table_size が高すぎないか（接続数 × 各バッファサイズが物理RAMを超えていないか）
  • アプリ側（LaravelやGoなど）で接続プールの上限設定が適切か
  • 接続数が多い場合、読み取り処理はリードレプリカに逃すなど設計的な工夫も重要です

同時接続に起因する問題は一時的な再現が難しいため、ログとステータス変数を用いた観測と、設計段階からのリソース管理がカギになります。

まとめ

見える化・数値化 → 原因特定 → 対策実行 の流れを忘れずに
クエリ最適化だけでなく、I/O・インフラ・メモリ視点も重要だと感じてます。
Laravelなら debugbar, chunkById, EXPLAIN をフル活用するとよいでしょう。

また、csv生成処理などは、設計レベルで集計済みテーブルを活用すると圧倒的に速くなるので、APIでやるべき処理とJobでやるべき処理は明確に分けることも検討すべきです。

パフォーマンスチューニングとは「設計・構造・実装」すべてを見直すことだという認識で今後もお仕事に取り組んでいきます！

4
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?