AWS CloudWatch Logsと連携するMCPをロール（プロファイル）ごとに切り替えて使ってみた #Python – Qiita

# メディアサイトデータベースの上位3つのスロークエリ分析

過去1週間のCloudWatchログから、STG環境を除外して分析した結果、PRD環境で最も実行時間が長いスロークエリの上位3つは以下の通りです。

## 1. 統計データ挿入クエリ

**クエリパターン:**

    INSERT INTO content_statistics (date, content_id, page, page_views, sessions)
    SELECT StatsTemp.date AS `StatsTemp__date`, 
           StatsTemp.content_id AS `StatsTemp__content_id`, 
           StatsTemp.page AS `StatsTemp__page`, 
           sum(page_views) AS `page_views`, 
           sum(sessions) AS `sessions` 
    FROM content_statistics_temp StatsTemp 
    WHERE (is_enable = 1 AND content_id  0) 
    GROUP BY date, content_id, page;

**特徴:**
- 実行時間: 平均13〜16秒
- 検査行数: 40,000〜51,000行
- 発生頻度: 毎日19:00頃に定期実行
- GROUP BY句を使用した集計処理と大量データの挿入を一括で実行
- 毎日同じ時間に実行されるバッチ処理

**対策:**
1. インデックス最適化: `content_statistics_temp`テーブルの`date`、`content_id`、`page`列に複合インデックスを作成
2. バッチ処理の分割: 大量データを小さなバッチに分けて挿入（例：日付ごとに分割実行）
3. パーティショニング: `date`列でテーブルをパーティショニングし、挿入パフォーマンスを向上
4. 実行時間の調整: アクセスが少ない時間帯に実行するようスケジュール変更
5. メンテナンス対策: 処理前後でテーブル統計情報の更新やVACUUM処理の実行

## 2. 統計データ削除クエリ

**クエリパターン:**

    DELETE FROM content_statistics WHERE (date = '2025-XX-XX');

**特徴:**
- 実行時間: 平均11〜16秒
- 検査行数: 17,000〜20,000行
- 発生頻度: 毎日00:00と14:30頃に定期実行
- 単一日付のデータを一括削除
- 毎回異なる日付だが同一パターンのクエリ

**対策:**
1. インデックス最適化: `content_statistics`テーブルの`date`列に専用インデックス作成
2. クエリ書き換え: 等値比較（`date="YYYY-MM-DD"`）を使用
3. パーティショニング: `date`列でテーブルをパーティショニングし、`DROP PARTITION`による高速削除
4. バッチ処理: 一度に削除する行数を制限したバッチ処理に変更
5. アーカイブ戦略: 削除の代わりにアーカイブテーブルへの移動を検討

## 3. メールアドレス取得クエリ

**クエリパターン:**

    SELECT user.id AS `id`, user.email AS `email` 
    FROM ((SELECT User.id AS `id`, User.email AS `email` 
           FROM user User 
           INNER JOIN user_subscription UserSubscription 
           ON UserSubscription.user_id = (User.id) 
           WHERE (User.is_enabled = 1 AND UserSubscription.is_enabled = 1 AND UserSubscription.type=1))
          UNION ALL 
          (SELECT LegacyUsers.id AS `id`, LegacyUsers.email AS `email` 
           FROM legacy_users LegacyUsers 
           WHERE (LegacyUsers.role_id = 400 AND LegacyUsers.banned = 0 AND LegacyUsers.is_migration = 0 
                  AND LegacyUsers.subscription_status = 1))) AS user;

**特徴:**
- 実行時間: 平均11〜13秒
- 検査行数: 約260,000行
- 発生頻度: 不定期だが主に深夜〜早朝に実行
- UNIONを使った2つのテーブルからのデータ結合と大量データの取得
- ニュースレター購読者のメールアドレス抽出用途と推測

**対策:**
1. インデックス最適化:
   - `user`テーブルの`is_enabled`列にインデックス
   - `user_subscription`テーブルの`user_id`、`is_enabled`、`type`列に複合インデックス
   - `legacy_users`テーブルのWHERE句条件に複合インデックス

2. マテリアライズドビュー: 頻繁に利用される場合は、結果をマテリアライズドビューとして保存
3. クエリ最適化: サブクエリ内でLIMITを使用してメモリ消費を制限
4. テーブル統合: 可能であれば、`user`テーブルと`legacy_users`テーブルの統合を検討
5. データ分析: 実際の用途に基づいて、必要な列のみを選択するよう最適化

これらの対策を実装することで、メディアサイトDBクラスターのパフォーマンスは大幅に向上し、スロークエリによるリソース消費が削減されるでしょう。これらの変更を本番環境に適用する前に、テスト環境でのパフォーマンス検証を強くお勧めします。