db2023 台別データ読み取りパフォーマンス調査（Issue #2293）

目的

wp_db2023（DatabaseTableConstants::DAILY_DATA）の読み取りパフォーマンスを調査し、インデックス見直し・DAY 型変換・MySQL パーティショニング・年別テーブル分割の各案を比較する。本ドキュメントは 調査/設計 の成果物であり、スキーマ変更の実装は Issue #2135（kishu_id バックフィル）完了後を推奨する。

調査環境

項目	値
環境	Local（`slotkouryaku.local`）
DB	MySQL（Local run: `5pYRuxwSB`）
テーブル	`wp_db2023`
調査日	2026-06-12

現状スキーマ（ローカル DB）

sql

CREATE TABLE `wp_db2023` (
  `DAY` date NOT NULL,
  `kishu` varchar(255) NOT NULL,
  `dainum` varchar(255) NOT NULL,
  `kaiten` int(11) NOT NULL,
  `samai` int(11) NOT NULL,
  `BB` int(11) NOT NULL,
  `RB` int(11) NOT NULL,
  `hall` varchar(255) NOT NULL,
  `kishu_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`DAY`,`dainum`,`hall`) USING BTREE,
  KEY `idx_kishu_id` (`kishu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ローカルと Issue 記載の差異

項目	Issue #2293 記載（本番想定）	ローカル実測
`DAY` 型	`varchar(10)`、`Y/n/j` 形式	`date` 型、`YYYY-MM-DD` 格納
日付リテラル	`'2025/1/10'` 等	`'2026-03-16'` 等（MySQL が date に解釈）

注意: 本番環境では Issue 記載どおり varchar の可能性がある。実装前に本番で SHOW CREATE TABLE wp_db2023 を再確認すること。DBML（custom-tables.dbml）は date 型を正としている。

テーブル規模（ローカル）

指標	値
総行数	1,439,926
データサイズ	約 151 MB
インデックスサイズ	約 64 MB
合計	約 215 MB
日付範囲	2022-07-29 〜 2026-03-16
1 日あたり行数（直近）	約 519〜911 行/日

用途別の読み取りパターン

用途	読み取り範囲	主要メソッド	SQL パターン
日別記事・台別ランキング	直近 3 日（`DAYS_BACK_FOR_RANKING_DATA = 2`）	`select_daily_data_per_unit_by_ymd`	`WHERE DAY IN (3日)` → PHP で hall フィルタ
期間差枚サマリー（SC-010）	最大 366 日	`select_daily_data_group_by_day_and_hall_and_kishu_by_ymd`	`WHERE DAY IN (最大366日)` → PHP で hall・kishu フィルタ
期間機種別差枚ランキング（SC-016）	最大 366 日	同上	`WHERE DAY IN (最大366日)` → PHP で hall フィルタ

機種別ランキングは db_daily_article_kishu_single_day_summary を優先参照（Issue #2076）。台別 raw（db2023）の直接参照は上記 3 用途が中心。

kishu_id バックフィル状況（Issue #2135）

sql

SELECT COUNT(*) AS total,
       SUM(kishu_id IS NULL) AS null_count,
       SUM(kishu_id IS NOT NULL) AS filled_count
FROM wp_db2023;

環境	total	null_count	filled_count
ローカル（2026-06-12）	1,439,926	1,439,926	0

ローカルでは kishu_id が全行 NULL。現行 SQL は INNER JOIN wp_db_kishu_master ON t.kishu_id = m.id を前提とするため、JOIN 付きクエリは 0 件 を返す。本番のバックフィル完了状況は別途確認が必要。

依存関係: 大規模 ALTER（パーティション追加・型変更）と kishu_id バックフィルが重複すると全行スキャンが二重化する。調査は先行可、実装は #2135 完了後を推奨。

EXPLAIN 結果

1. 台別ランキング（直近 3 日）— `select_daily_data_per_unit`

対象日: 2026-03-14, 2026-03-15, 2026-03-16（実データ 1,949 行）

kishu_master JOIN あり（現行アプリ SQL）

id  select_type  table  type   key       rows     Extra
1   SIMPLE       m      index  uk_name   1        Using index; Using temporary; Using filesort
1   SIMPLE       t      range  PRIMARY   3        Using where; Using join buffer (Block Nested Loop)

kishu_id が NULL のため JOIN が成立せず、推定 rows が実態と乖離。

JOIN なし（テーブル単体の読み取り性能）

id  select_type  table  type   key       rows     Extra
1   SIMPLE       t      range  PRIMARY   1949     Using where; Using filesort

実行時間（profiling）: 約 0.012 秒（1,949 行取得）

2. 期間集計（SC-010 / SC-016）— `select_daily_data_group_by_day_and_hall_and_kishu`

3 日 GROUP BY（JOIN なし）

type=range, key=PRIMARY, rows=1949, Extra=Using where; Using temporary

年跨ぎ 121 日（2024-12-01 〜 2025-03-31）

type=range, key=PRIMARY, rows=108053（推定）, 実際の取得行数=108,053
Extra=Using where; Using temporary

最大 365 日（2025-03-17 〜 2026-03-16）

type=range, key=PRIMARY, rows=55480（推定）, 実際の取得行数=303,586
Extra=Using where; Using temporary

3. DAY + hall クエリ — `DailyDataRepository`

WHERE DAY = '2026-03-16' AND hall = 'アイランド秋葉原':

type=ref, key=PRIMARY, rows=911, Extra=Using where

PRIMARY KEY (DAY, dainum, hall) の先頭列 DAY で絞り込み後、hall は 3 列目のためインデックス上では完全カバーされない。

コード影響範囲調査

`DAY` varchar → date 変換の影響

レイヤ	ファイル	影響
インポート	`DailyDataImportService`	`DateUtil::normalize_date_for_import()` で `YYYY/M/D` 文字列を生成し INSERT
リポジトリ	`DailyDataPerUnitRepository`	入力日付を `DateFormatEnum::Y_N_J_SLASH`（`Y/n/j`）に変換して `WHERE DAY IN (...)`
リポジトリ	`DailyDataRepository`	`WHERE DAY = %s AND hall = %s`（`Y/n/j` または DB 依存の文字列）
ユーティリティ	`DateUtil::normalize_date_for_import`	`YYYYMMDD` → `YYYY/M/D`、`YYYY/M/D` はそのまま
SQL	`insert_or_update_batch.sql` 他	`DAY` を `%s` でバインド。date 型 DB では MySQL が暗黙変換
Entity	`DailyDataPerUnitEntity`	`DAY` を文字列として保持（DB からの読み取り値をそのまま利用）

変換時の作業:

本番スキーマ確認（varchar か date か）
既存データの Y/n/j → YYYY-MM-DD 正規化マイグレーション
インポート・リポジトリの日付フォーマット統一（Y-m-d または date 型バインド）
PHPUnit・結合テストの更新（RankingKishuDataTest 等で Y/n/j 前提のテストあり）

`(DAY, hall)` 複合インデックスの効果

クエリパターン	SQL に hall 条件	`(DAY, hall)` の効果
`select_daily_data_per_unit`（台別ランキング）	なし（PHP フィルタ）	低 — 3 日分全ホール取得のため
`select_daily_data_group_by_*`（SC-010/016）	なし（PHP フィルタ）	低 — 最大 366 日×全ホール取得
`DailyDataRepository::get_daily_data_by_kishu`	あり（`DAY = ? AND hall = ?`）	中 — PRIMARY の `DAY` 先頭で部分利用、専用索引で hall 絞り込み改善の余地
`select_count_by_day_and_title`	オプション（`hall IN (...)`）	中（hall 指定時のみ）
`delete_by_date_and_hall` / `batch_delete_by_date_hall_pairs`	あり	中〜高

結論: (DAY, hall) インデックス単体では SC-010/SC-016 のボトルネック（全ホール DB 転送）は解消しない。リポジトリ/SQL に hall 条件を追加するアプリ変更とセットで検討する価値がある。

Period 系サービスの全ホール取得（確認済み）

PeriodKishuSamaiRankingService::get_ranking() — select_daily_data_group_by_day_and_hall_and_kishu_by_ymd($ymd_list) で全ホール取得後、if (!in_array($dto->hall, $halls, true)) で PHP フィルタ
PeriodSamaiSummaryService::get_summary() — 同上取得後、array_filter で hall・kishu フィルタ
DailyArticleResultService — select_daily_data_per_unit_by_ymd で 3 日分全ホール取得後、PHP で hall フィルタ

改善案の比較

案	概要	年跨ぎ対応	アプリ変更	運用コスト	ローカル調査所見
A: インデックス追加	`(DAY, hall)` 複合インデックス	そのまま	不要（ただし効果限定）	低	SC-010/016 には効果薄。`DailyDataRepository` 系には有効
A': SQL に hall 条件追加	リポジトリに hall パラメータを追加	そのまま	必要（Interface・Service・SQL）	低	転送量削減に最も効果的（1 ホール指定時）
B: MySQL パーティショニング	年別 RANGE パーティション	MySQL が自動横断	不要	低〜中	`DAY` が date 型なら `PARTITION BY RANGE (YEAR(DAY))` が適用可能。varchar 時は式パーティションが必要
C: 年別テーブル分割	`db_daily_data_{year}`	リポジトリで UNION	必要（ルーティング）	高（毎年作業）	年跨ぎクエリの UNION コスト・アプリ複雑化が大きい

案 B のパーティション設計（`DAY` が date 型の場合）

sql

ALTER TABLE wp_db2023
PARTITION BY RANGE (YEAR(`DAY`)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

WHERE DAY IN (...) は該当パーティションのみスキャン
年跨ぎ期間（例: 2024-12 〜 2025-03）は複数パーティション横断だが、アプリ変更なし
大テーブル ALTER は pt-online-schema-change 等のオンライン DDL を検討

案 B の制約（`DAY` が varchar の場合）

式ベースパーティションが必要:

sql

PARTITION BY RANGE (YEAR(STR_TO_DATE(`DAY`, '%Y/%c/%e')))

パーティション pruning が式に依存し、プランナの挙動が複雑化
DAY を date 型へ正規化してからパーティション適用が望ましい

推奨実装順序

現時点の推奨: 案 B（MySQL パーティショニング） を中長期の主軸とし、短期では 案 A'（SQL hall 条件追加） で SC-010/SC-016 の DB 転送量を削減する。案 C（年別テーブル分割）は運用コスト・アプリ変更量の観点から優先度を下げる。

DoD 計測基準との対照

docs/performance/README.md の目標値:

指標	目標（2 ホール）	本調査での観測
Query Count	15 以下	本調査対象外（別途 `run-performance-test.sh` で計測）
Execution Time	0.05 秒以下	3 日 `select_daily_data_per_unit`（JOIN なし）: 約 0.012 秒 ✓
Memory Usage	2 MB 以下	本調査対象外
EXPLAIN rows 改善	改善していること	366 日 GROUP BY: 推定 55,480 / 実 303,586 行 — パーティション+hall 条件で削減見込み

次のアクション（実装 Issue 用）

本番: SHOW CREATE TABLE wp_db2023 と kishu_id NULL 件数を確認
#2135 完了後: DailyDataPerUnitRepository に hall オプション引数を追加する設計 Issue を起票
#2135 完了後: DAY 型統一 + RANGE パーティション追加のマイグレーション Issue を起票（pt-online-schema-change 手順含む）
計測: 変更前後で scripts/run-performance-test.sh および SC-010/016 の代表クエリで EXPLAIN・実行時間を比較

再現用 SQL（ローカル）

bash

# Local MySQL ソケット例（環境によりパスが異なる）
MYSQL_SOCK="/Users/a/Library/Application Support/Local/run/<site-id>/mysql/mysqld.sock"

# スキーマ・行数
mysql -uroot -proot -S "$MYSQL_SOCK" local -e "SHOW CREATE TABLE wp_db2023;"
mysql -uroot -proot -S "$MYSQL_SOCK" local -e "SELECT COUNT(*) FROM wp_db2023;"

# 3 日クエリ EXPLAIN
mysql -uroot -proot -S "$MYSQL_SOCK" local -e "
EXPLAIN SELECT t.\`DAY\`, t.kishu, t.dainum, t.kaiten, t.samai, t.BB, t.RB, t.hall
FROM wp_db2023 AS t
WHERE t.\`DAY\` IN ('2026-03-14','2026-03-15','2026-03-16')
ORDER BY t.dainum ASC, t.\`DAY\` ASC;
"

kishu_id バックフィル完了後は、JOIN 付きクエリ（select_daily_data_per_unit.sql 相当）でも EXPLAIN を再取得すること。

admin-ajax

endpoints

画面設計書

同一管理画面リクエスト

db2023 台別データ読み取りパフォーマンス調査（Issue #2293）

目的

調査環境

現状スキーマ（ローカル DB）

ローカルと Issue 記載の差異

テーブル規模（ローカル）

用途別の読み取りパターン

kishu_id バックフィル状況（Issue #2135）

EXPLAIN 結果

1. 台別ランキング（直近 3 日）— `select_daily_data_per_unit`

kishu_master JOIN あり（現行アプリ SQL）

JOIN なし（テーブル単体の読み取り性能）

2. 期間集計（SC-010 / SC-016）— `select_daily_data_group_by_day_and_hall_and_kishu`

3 日 GROUP BY（JOIN なし）

年跨ぎ 121 日（2024-12-01 〜 2025-03-31）

最大 365 日（2025-03-17 〜 2026-03-16）

3. DAY + hall クエリ — `DailyDataRepository`

コード影響範囲調査

`DAY` varchar → date 変換の影響

`(DAY, hall)` 複合インデックスの効果

Period 系サービスの全ホール取得（確認済み）

改善案の比較

案 B のパーティション設計（`DAY` が date 型の場合）

案 B の制約（`DAY` が varchar の場合）

推奨実装順序

DoD 計測基準との対照

次のアクション（実装 Issue 用）

再現用 SQL（ローカル）

参照

endpoints

db2023 台別データ 読み取りパフォーマンス調査（Issue #2293） ​

目的 ​

調査環境 ​

現状スキーマ（ローカル DB） ​

ローカルと Issue 記載の差異 ​

テーブル規模（ローカル） ​

用途別の読み取りパターン ​

kishu_id バックフィル状況（Issue #2135） ​

EXPLAIN 結果 ​

1. 台別ランキング（直近 3 日）— select_daily_data_per_unit ​

kishu_master JOIN あり（現行アプリ SQL） ​

JOIN なし（テーブル単体の読み取り性能） ​

2. 期間集計（SC-010 / SC-016）— select_daily_data_group_by_day_and_hall_and_kishu ​

3 日 GROUP BY（JOIN なし） ​

年跨ぎ 121 日（2024-12-01 〜 2025-03-31） ​

最大 365 日（2025-03-17 〜 2026-03-16） ​

3. DAY + hall クエリ — DailyDataRepository ​

コード影響範囲調査 ​

DAY varchar → date 変換の影響 ​

(DAY, hall) 複合インデックスの効果 ​

Period 系サービスの全ホール取得（確認済み） ​

改善案の比較 ​

案 B のパーティション設計（DAY が date 型の場合） ​

案 B の制約（DAY が varchar の場合） ​

推奨実装順序 ​

DoD 計測基準との対照 ​

次のアクション（実装 Issue 用） ​

再現用 SQL（ローカル） ​

参照 ​

db2023 台別データ読み取りパフォーマンス調査（Issue #2293）

目的

調査環境

現状スキーマ（ローカル DB）

ローカルと Issue 記載の差異

テーブル規模（ローカル）

用途別の読み取りパターン

kishu_id バックフィル状況（Issue #2135）

EXPLAIN 結果

1. 台別ランキング（直近 3 日）— `select_daily_data_per_unit`

kishu_master JOIN あり（現行アプリ SQL）

JOIN なし（テーブル単体の読み取り性能）

2. 期間集計（SC-010 / SC-016）— `select_daily_data_group_by_day_and_hall_and_kishu`

3 日 GROUP BY（JOIN なし）

年跨ぎ 121 日（2024-12-01 〜 2025-03-31）

最大 365 日（2025-03-17 〜 2026-03-16）

3. DAY + hall クエリ — `DailyDataRepository`

コード影響範囲調査

`DAY` varchar → date 変換の影響

`(DAY, hall)` 複合インデックスの効果

Period 系サービスの全ホール取得（確認済み）

改善案の比較

案 B のパーティション設計（`DAY` が date 型の場合）

案 B の制約（`DAY` が varchar の場合）

推奨実装順序

DoD 計測基準との対照

次のアクション（実装 Issue 用）

再現用 SQL（ローカル）

参照