🔩 ねじき教室 Go と Web の教室

【課題 2】インデックスが足りない

読了目安 約3分

1 万 9 千行を読んでいたクエリと、インデックスが 1 つも無いテーブルを直します。

この章の目次

前章で見つけた visit_history の集計から手をつけます。

課題

このクエリが 1 回あたり約 1 万 9 千行を読んでいます。

SQL 
SELECT player_id, MIN(created_at) AS min_created_at
FROM visit_history
WHERE tenant_id = ? AND competition_id = ?
GROUP BY player_id

テーブルの定義はこうです。

SQL 
CREATE TABLE `visit_history` (
  `player_id` VARCHAR(255) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `competition_id` VARCHAR(255) NOT NULL,
  `created_at` BIGINT NOT NULL,
  `updated_at` BIGINT NOT NULL,
  INDEX `tenant_id_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

テナント DB 側のテーブルはこうです。

SQL 
CREATE TABLE player_score (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  player_id VARCHAR(255) NOT NULL,
  competition_id VARCHAR(255) NOT NULL,
  score BIGINT NOT NULL,
  row_num BIGINT NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);
  1. visit_history に張るべきインデックスを書いてください
  2. player_score に対して、ランキング取得のクエリが何をしているか考えてください

解答例

1. 複合インデックスにする

既存のインデックスは tenant_id だけです。 テナントで絞ったあと、その中の全行を 1 行ずつ見て competition_id を比べています。 大きなテナントほど、絞り込んだあとの行数が増えます。

WHERE の 2 つの列を両方入れます。

SQL 
ALTER TABLE visit_history
  ADD INDEX idx_vh_tenant_comp_player (tenant_id, competition_id, player_id, created_at);

player_id と created_at まで入れたのは、この 2 つが GROUP BY と MIN() で使われているからです。 インデックスだけで答えが出せるので、テーブル本体を読まずに済みます。

2. player_score にはインデックスが 1 つも無い

主キーの id しかありません。 ランキング取得はこう書かれています。

SQL 
SELECT * FROM player_score
WHERE tenant_id = ? AND competition_id = ?
ORDER BY row_num DESC

大きいテナントで 167 万行あるテーブルを、毎回すべて読んで並べ替えています。 スロークエリログには出ませんが、ここが /api/player/* の遅さの正体です。

SQL 
CREATE INDEX idx_ps_comp_row
  ON player_score (tenant_id, competition_id, row_num, player_id, score);
CREATE INDEX idx_ps_comp_player_row
  ON player_score (tenant_id, competition_id, player_id, row_num, score);

/initialize で消える

テナント DB は、初期化のたびにファイルごと差し替えられます。

シェル 
rm -f ../tenant_db/*.db
cp -r ../../initial_data/*.db ../tenant_db/

手で CREATE INDEX を打っても、ベンチマーカーが /initialize を呼んだ瞬間に消えます。 初期化の処理そのものに書き足します。

シェル 
for f in ../tenant_db/*.db; do
	sqlite3 "$f" "
		CREATE INDEX IF NOT EXISTS idx_ps_comp_row ON player_score (tenant_id, competition_id, row_num, player_id, score);
		CREATE INDEX IF NOT EXISTS idx_ps_comp_player_row ON player_score (tenant_id, competition_id, player_id, row_num, score);
	"
done

初期化スクリプトが直せるのは、そのとき存在するファイルだけです。 テナントはベンチマーカーの走行中にも増えます。 新しいテナントの DB は webapp/sql/tenant/10_schema.sql から作られるので、同じ定義をこちらにも足します。

SQL 
-- webapp/sql/tenant/10_schema.sql の末尾
CREATE INDEX idx_ps_comp_row
  ON player_score (tenant_id, competition_id, row_num, player_id, score);
CREATE INDEX idx_ps_comp_player_row
  ON player_score (tenant_id, competition_id, player_id, row_num, score);

片方だけ直すと、途中で作られたテナントだけがインデックス無しのまま残ります。

/initialize には時間制限があります。この環境では 112 個のファイルに対して 3.6 秒でした。制限は問題ごとに違うので、マニュアルで確認してください。

効果

スコア
初期実装6072
インデックスを足した10319

visit_history の集計は 1 回 44 ミリ秒から 1.8 ミリ秒になりました。 読む行数も 1 回あたり 1 万 9 千行から 2731 行に減っています。

インデックスは、コードを 1 行も変えずに効く数少ない手です。 Part 6 で見たとおり、最初に試す価値があります。