Table structure summary

• articles (≈25M rows, InnoDB assumed)

  • PK: id (bigint)
  • Important columns for queries: author_id, title (varchar), body (text), language (char(2)), status (enum), category_id (int), published_at (timestamp), updated_at
  • Search: full‑text over title/body; filtering/sorting on status, language, category_id, published_at; author feed by author_id + published_at

• comments (≈200M rows)

  • PK: id (bigint)
  • Important columns: article_id, user_id, content (text), sentiment, created_at (timestamp)
  • Search: full‑text content; moderation filters by created_at and sorts by created_at desc

• article_metrics (≈25M rows; frequent updates)

  • PK/FK: article_id
  • Important columns: views, likes, shares, updated_at
  • Query: trending within last 24h, ordered by views desc, joined to articles (status/category)

• Current indexes: PKs only

Index recommendation list

Optional, size‑saving expressions

• articles.short_body (generated): SUBSTRING(body, 1, 2000) stored; used in en_doc to cap FT index size.
• If multi‑language beyond English is needed, add analogous per‑language generated doc columns and FT indexes only when query patterns require them.

DDL examples (MySQL 8.0+)

• articles (generated columns + FT)

  • ALTER TABLE articles ADD COLUMN short_body TEXT GENERATED ALWAYS AS (SUBSTRING(body, 1, 2000)) STORED;
  • ALTER TABLE articles ADD COLUMN en_doc TEXT GENERATED ALWAYS AS (CASE WHEN status='published' AND language='en' THEN CONCAT_WS(' ', title, short_body) END) STORED;
  • CREATE FULLTEXT INDEX ft_articles_en_doc ON articles (en_doc);
  • CREATE INDEX idx_articles_pub_en_cat_pubat ON articles (status, language, category_id, published_at DESC, id);
  • CREATE INDEX idx_articles_author_pub_pubat ON articles (author_id, status, published_at DESC, id);

• comments

  • CREATE FULLTEXT INDEX ft_comments_content ON comments (content);
  • CREATE INDEX idx_comments_created_at_id ON comments (created_at DESC, id);

• article_metrics

  • CREATE INDEX idx_metrics_updated_views ON article_metrics (updated_at, views DESC, article_id);

Performance expectations

• Q1 Full‑text with filters

  • Primary access via ft_articles_en_doc. Because en_doc includes only published English rows, the FT search space is reduced; post‑filtering by category_id and published_at is applied on a much smaller candidate set.
  • idx_articles_pub_en_cat_pubat enables an efficient two‑step strategy when needed: first fetch recent IDs by filter/sort, then FT‑filter those IDs (application‑level or with a subquery). This avoids large FT scans for very common terms.
  • ORDER BY published_at desc is not satisfied by the FULLTEXT index; a filesort is expected on the candidate set, which is acceptable when candidate count is bounded (LIMIT 20).

• Q2 Trending

  • idx_metrics_updated_views prunes to last 24h by range on updated_at and provides rows in a views‑friendly order within that time window. The join to articles is via PK; remaining filters (status/category) are applied post‑join.
  • Expect optimizer to start from article_metrics using idx_metrics_updated_views, apply the time predicate, then join to articles by PK and filter status/category. If the 24h window is large, a filesort on views may still occur; the index minimizes the rows entering the sort.

• Q3 Author feed

  • idx_articles_author_pub_pubat satisfies WHERE author_id=? AND status='published' and ORDER BY published_at DESC. The index scan returns rows already in order; LIMIT with keyset pagination avoids deep offsets.

• Q4 Comment moderation

  • ft_comments_content is used to locate matching comments; created_at >= ? reduces the candidate set. ORDER BY created_at desc will be a filesort over FT matches; the time predicate limits sort volume. idx_comments_created_at_id benefits non‑FT scans and complements FT for recent‑only operations.

Index size and write cost considerations

• The sparse FT index on en_doc avoids indexing drafts/archived and non‑English rows, substantially reducing FT size relative to a global FULLTEXT(title, body).
• short_body caps FT tokenization cost on large bodies; adjust length (e.g., 1000–4000) to meet recall needs while keeping within the 1.2x index‑to‑data budget.
• article_metrics adds only one secondary index, containing narrow columns, to keep write amplification low for updates to views/likes/shares.

Implementation guidance

1. Create and validate indexes (online where possible)

• Use ALGORITHM=INPLACE/INSTANT when supported to avoid long blocking:
  • Adding generated columns: ALTER TABLE ... ADD COLUMN ... STORED, ALGORITHM=INSTANT;
  • Creating BTREE indexes: CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE;
  • FULLTEXT index build is online in InnoDB; schedule during off‑peak.
• After creation, run ANALYZE TABLE on each affected table to refresh statistics.

2. Query adjustments (optional, but beneficial)

• Q1: Switch to MATCH(en_doc) AGAINST (? IN BOOLEAN MODE). This leverages the sparse FT index and avoids scanning non‑published/non‑English rows.
• Q1 two‑step pattern for very common terms:
  • Step 1: SELECT id FROM articles FORCE INDEX (idx_articles_pub_en_cat_pubat) WHERE status='published' AND language='en' AND category_id IN (?,?) AND published_at BETWEEN ? AND ? ORDER BY published_at DESC LIMIT 2000;
  • Step 2: SELECT id, title, published_at FROM articles WHERE id IN (list_from_step1) AND MATCH(en_doc) AGAINST (? IN BOOLEAN MODE) ORDER BY published_at DESC LIMIT 20;
• Q2: Allow optimizer to start from article_metrics. If needed, hint: SELECT /*+ SET_VAR(sort_buffer_size=...) */ ... FROM article_metrics m FORCE INDEX (idx_metrics_updated_views) JOIN articles a USING (article_id) WHERE ... ORDER BY m.views DESC LIMIT 50;

3. Full‑text configuration (MySQL)

• Review and, if needed, tune:
  • innodb_ft_min_token_size (default 3) and ft_min_word_len (default 4 for MyISAM; InnoDB uses innodb_ft_min_token_size). Align with language needs to avoid excessive index growth.
  • Stopword list: ensure domain‑specific terms are not inadvertently dropped; use a custom stopword list if necessary.
• For multi‑language beyond English, consider per‑language generated columns and FT indexes only when that language is queried frequently.

4. Monitoring and validation

• Use EXPLAIN ANALYZE to verify index usage paths for the four top queries.
• Track:
  • Handler_read* and Innodb_rows_read to detect table/FT scans.
  • Sort_merge_passes and sort_rows for Q1/Q4 filesorts.
  • Index sizes via information_schema.TABLES and STATISTICS; keep total index size within the <1.2x data limit. If exceeding, reduce short_body length and/or narrow FT coverage.
  • QPS/latency P95 via Performance Schema events_statements_summary_by_digest per query pattern.

5. Maintenance

• Rebuild frequency:
  • BTREE: no periodic rebuild needed; monitor fragmentation (DATA_FREE) and only OPTIMIZE TABLE when bloat > 20% and measurable performance impact is observed.
  • FULLTEXT: no routine rebuilds; re‑ANALYZE after large data shifts.
• Statistics:
  • ANALYZE TABLE articles, comments, article_metrics weekly or after large data churn to keep histograms current (if enabled).
• Fragmentation:
  • article_metrics is update‑heavy; consider periodic OPTIMIZE TABLE during maintenance if secondary index leaf fragmentation grows and affects read latency.

6. Rollout plan

• Stage 1 (non‑intrusive): Add generated columns (articles.short_body, articles.en_doc).
• Stage 2: Create ft_articles_en_doc; validate Q1 with EXPLAIN and controlled canary traffic.
• Stage 3: Create BTREE indexes on articles and article_metrics; validate Q2/Q3 plans.
• Stage 4: Create ft_comments_content and idx_comments_created_at_id; validate Q4 on a recent‑time window.
• Stage 5: Adjust queries (Q1 to MATCH(en_doc)), deploy two‑step retrieval for high‑fanout categories/terms if needed.
• Stage 6: Monitor, then refine short_body length and FT tokenization settings to meet the size budget.

Notes on redundancy and constraints

• Avoid additional indexes on likes/shares individually; they increase write cost without serving listed queries.
• Do not duplicate (status, category_id, published_at) permutations beyond the two recommended composites; adding both (status, language, category_id, published_at) and (status, category_id, language, published_at) is redundant.
• No filtered indexes are created beyond what is represented via generated columns (industry‑standard workaround in MySQL).

表構造摘要

• システム種別：高並行OLTP＋近実時レポート／分析。読み取りは直近7–30日が中心。書き込みピーク≈80k行/分。
• 規模：
  • risk_events ≈5億行（1.1億/日増）
  • devices ≈5千万行
  • ip_reputation ≈300万行
  • user_flags ≈数百万行
• ストレージエンジン前提：InnoDB（MySQL 8.0想定）
• 既存インデックス：risk_events(id PK)、devices(device_id PK)
• 主なクエリパターン：
  • 等値＋期間フィルタ＋created_at降順＋LIMIT（Q1, Q2）
  • 期間＋イベント種別＋OR条件＋join(ip)＋降順＋LIMIT（Q3）
  • 期間＋GROUP BY device_id＋count(distinct user_id)（Q4）
• 制約：インデックス総容量≦テーブル容量、書込劣化≦3%、分割（created_atベース）と分割別索引（物理的局所化）を志向

索引推薦一覧

以下は重複と冗長を避け、典型クエリに直結する最小構成です（risk_eventsのみ）。BTREE前提。DESCは8.0でサポートされていますが、ASCでも最適化は成立します。

補足：

• Q3のために「flagged」生成列を追加（virtual）。これによりOR条件を等値に変換し、単一インデックスで最適化。
• ip_reputationはPK(ip)がクラスタ化（InnoDB）されているため追加インデックス不要。

性能予期

• Q1/Q2：
  • インデックス範囲走査＋順序満足（filesort回避）。LIMITにより上位N件のみの読み出し。
  • 直近分割（created_at）によりパーティションプリューニングが働き、探索範囲が短縮。
• Q3：
  • flaggedによりORが等値化され、(event_type, flagged, created_at)の複合キーを使用した順序付き範囲走査が可能。
  • joinはe.ip→ip_reputation.PKでネストループ。ip_reputationは小規模で主キー参照コスト低。
• Q4：
  • 分割（created_at日次）で当日パーティションのみを走査。必要に応じてオプションインデックスを追加し、範囲→グループ化の負荷を低減。
• インデックス容量と書込コスト：
  • 上記3本（オプション除く）で総容量はテーブル容量の上限（≦1.0倍）を目標に収まる構成。特にdevice_idの長さによりidx_re_device_createdが支配的。導入後に容量監視必須。
  • 書込劣化は生成列（単純論理式）＋3本のセカンダリで抑制。ピーク時負荷は監視のうえ必要ならQ4用インデックス導入を見合わせ。

実施提案（DDL・展開・ロールバック・監視）

1. 生成列とインデックス作成（オンライン）

• 目的：Q3最適化（OR排除）
• DDL（MySQL 8.0, InnoDB）
  • ALTER TABLE risk_events ADD COLUMN flagged TINYINT(1) GENERATED ALWAYS AS ((decision IN ('review','reject')) OR (risk_score >= 80)) VIRTUAL, ALGORITHM=INPLACE, LOCK=NONE;
  • CREATE INDEX idx_re_eventflag_created ON risk_events (event_type, flagged, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
• ロールバック：DROP INDEX idx_re_eventflag_created; ALTER TABLE risk_events DROP COLUMN flagged;

2. Q1/Q2向け複合インデックス作成（オンライン）

• DDL：
  • CREATE INDEX idx_re_user_created ON risk_events (user_id, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
  • CREATE INDEX idx_re_device_created ON risk_events (device_id, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
• ロールバック：DROP INDEX idx_re_user_created; DROP INDEX idx_re_device_created;

3. 時間分割（RANGE COLUMNS(created_at)日次）

• 方針：直近60–90日を日次分割。パーティションプリューニング＋物理的局所化で範囲走査を短縮。InnoDBのセカンダリインデックスは各分割に物理分散（論理定義はグローバル）。
• 新テーブルを用いた移行（大規模のため推奨）
  • 作成（例：直近90日＋MAXVALUE）
    • CREATE TABLE risk_events_v2 LIKE risk_events;
    • ALTER TABLE risk_events_v2 PARTITION BY RANGE COLUMNS (created_at)( PARTITION p20250101 VALUES LESS THAN ('2025-01-02'), ... PARTITION p_max VALUES LESS THAN (MAXVALUE) );
    • 既存インデックス相当をrisk_events_v2へ作成（上記3本）。
  • データ移行：gh-ost または pt-online-schema-change を利用しオンラインコピー（書込継続）。
    例：pt-online-schema-change --alter "PARTITION BY RANGE COLUMNS (created_at) ..." --execute
  • スイッチ：トランザクションメンテナンス短時間の停止ウィンドウでRENAME TABLEで切替。
  • ロールバック：旧risk_eventsを保持し、RENAME TABLEで戻す。
• 運用（分割の自動追加/削除）
  • 1日1回、新規分割追加＋保持期間超の分割DROP。
    例：
    • ALTER TABLE risk_events_v2 ADD PARTITION (PARTITION p20250302 VALUES LESS THAN ('2025-03-03'));
    • ALTER TABLE risk_events_v2 DROP PARTITION p20250101;
  • 直近30–60日を「ホット」、以降はアーカイブテーブルへ移送可（分析専用）。

4. Q4オプションインデックス（必要時のみ）

• DDL：
  • CREATE INDEX idx_re_created_device_user ON risk_events (created_at, device_id, user_id) ALGORITHM=INPLACE, LOCK=NONE;
• 導入条件：EXPLAINで当日分割へのプリューニングが効くが行走査量が多くSLOやバッチ時間を阻害する場合。
• ロールバック：DROP INDEX idx_re_created_device_user;

5. クエリの微調整

• Q3：flagged導入後の推奨クエリ（論理同一）
  • SELECT e.id, e.user_id, e.ip, r.risk_level FROM risk_events e JOIN ip_reputation r ON r.ip = e.ip WHERE e.created_at >= ? AND e.event_type IN ('login','payment') AND e.flagged = 1 ORDER BY e.created_at DESC LIMIT 1000;
• Q4：分割前提で当日分のみを明示（日次境界の取り扱いを明確化）。

6. 監視指標（導入前後の比較必須）

• 実行計画／効用
  • EXPLAIN: type=range/const、key=推奨インデックス名、rows推定、Extraに「Using index」「Using filesort」の有無。
  • パーティションプリューニング確認（EXPLAIN PARTITIONS）。
• 実行時統計
  • performance_schema: statement/event長、Rows_examined、Sort_merge_passes。
  • sys.schema_index_statistics／schema_table_statistics: 使用頻度、ヒット率。
  • InnoDB: buffer pool hit率、redo/flush量、page splits（information_schema.innodb_index_stats）。
• 書込影響
  • トランザクションレイテンシ、Insert QPS、InnoDB行ロック/ページラッチ待ち。
• 容量
  • information_schema.tables／statisticsで各インデックスサイズ、総容量がテーブル容量≦1.0倍に収まっているか。

7. メンテナンス方針

• 統計更新：ANALYZE TABLE risk_events（日次〜週次、分割追加/削除後）。
• 断片化監視：innodb_index_statsのleaf_pagesとsizeを比較、顕著な肥大時のみOPTIMIZE TABLE（メンテ時間確保）。
• 未使用索引の検出：sys.schema_unused_indexesで90日無使用なら削除検討（必ずクエリ監査と併用）。

DDLまとめ（抜粋）

• 生成列＋Q3用索引：
  • ALTER TABLE risk_events ADD COLUMN flagged TINYINT(1) GENERATED ALWAYS AS ((decision IN ('review','reject')) OR (risk_score >= 80)) VIRTUAL, ALGORITHM=INPLACE, LOCK=NONE;
  • CREATE INDEX idx_re_eventflag_created ON risk_events (event_type, flagged, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
• Q1/Q2用索引：
  • CREATE INDEX idx_re_user_created ON risk_events (user_id, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
  • CREATE INDEX idx_re_device_created ON risk_events (device_id, created_at DESC) ALGORITHM=INPLACE, LOCK=NONE;
• （必要時）Q4用索引：
  • CREATE INDEX idx_re_created_device_user ON risk_events (created_at, device_id, user_id) ALGORITHM=INPLACE, LOCK=NONE;
• 分割（新テーブルに適用）：
  • ALTER TABLE risk_events_v2 PARTITION BY RANGE COLUMNS (created_at)( PARTITION p20250301 VALUES LESS THAN ('2025-03-02'), ..., PARTITION p_max VALUES LESS THAN (MAXVALUE) );

注意事項

• device_idはVARCHAR(64)で索引サイズが大きいため、容量監視必須。将来的に実運用データが16進IDである場合、BINARY(16)化（正規化）や生成列によるハッシュ短縮などでインデックスフットプリントを削減可能（別途計画）。
• 分割の導入は大規模再構築を伴うため、オンライン移行ツール（gh-ost/pt-online-schema-change）利用とバックアップ／リハーサル必須。
• 本提案は提示クエリの実行パターンに基づく最小構成。追加インデックスはEXPLAIN/モニタリングで有効性確認後に限定的に導入してください。