Y

ISUCON12予選の復習をしました 8

この記事は何か?

ISUCON12予選の復習記録です。

第8回はISUCON12 予選の解説 (Node.jsでSQLiteのまま10万点行く方法)の「8 tenantDB player_scoreにINDEXをはる」を試します。

目次

1.「8 tenantDB player_scoreにINDEXをはる」を試す

ISUCON12予選は2つのデータソースがあります。AdminDBとTenantDBです。TenantDBのテーブル構造は以下です。

/home/isucon/webapp/sql/tenant/vi 10_schema.sql

DROP TABLE IF EXISTS competition;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS player_score;

CREATE TABLE competition (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  finished_at BIGINT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE player (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  display_name TEXT NOT NULL,
  is_disqualified BOOLEAN NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

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
);

上記のとおりインデックスがありません。最も大きなテナントの件数を確認します。

/home/isucon/webapp/tenant_db/1.db

sqlite> select count(*) from competition;
22
sqlite> select count(*) from player;
5001
sqlite> select count(*) from player_score;
1675597

player_scoreにインデックスをはると効果がありそうです。TenantDBに対するSELECT文を全部見てみます。

// player_score, tenant_id, competition_id, player_id
if err := tenantDB.SelectContext(
ctx,
&scoredPlayerIDs,
"SELECT DISTINCT(player_id) FROM player_score WHERE tenant_id = ? AND competition_id = ?",

// competition, PK
if err := tenantDB.SelectContext(
ctx,
&cs,
"SELECT * FROM competition WHERE tenant_id=?",

// player, tenant_id, created_at
if err := tenantDB.SelectContext(
ctx,
&pls,
"SELECT * FROM player WHERE tenant_id=? ORDER BY created_at DESC",

// player, tenant_id
if err := tenantDB.SelectContext(
ctx,
&playerIDs,
"SELECT DISTINCT(id) FROM player WHERE tenant_id = ?",

// competition, tenant_id, created_at
if err := tenantDB.SelectContext(
ctx,
&cs,
"SELECT * FROM competition WHERE tenant_id=? ORDER BY created_at DESC",

// competition, tenant_id, created_at
if err := tenantDB.SelectContext(
ctx,
&cs,
"SELECT * FROM competition WHERE tenant_id = ? ORDER BY created_at ASC",

// ..? (JOINの時の適切なインデックスとは?)
if err := tenantDB.SelectContext(
ctx,
&pss,
"SELECT player_score.*, player.display_name FROM player_score JOIN player ON player.id = player.score_id WHERE player_score.tenant_id = ? AND competition_id = ? ORDER BY row_num DESC",

// competition, tenant_id, created_at
if err := tenantDB.SelectContext(
ctx,
&cs,
"SELECT * FROM competition WHERE tenant_id=? ORDER BY created_at DESC",

// competition, created_at
if err := tenantDB.SelectContext(
ctx,
&cs,
"SELECT * FROM competition ORDER BY created_at DESC",

上記のとおり、解説にあるとおり以下のインデックスが有効そうです。ただし「player_id」を含める発想はありませんでした。

CREATE INDEX idx_score ON player_score (tenant_id, competition_id, player_id);

また、初期データに対してもインデックスを作成します。

cd /home/isucon/webapp/tenant_db
for db in *.db; do echo "CREATE INDEX idx_score ON player_score (tenant_id, competition_id, player_id);" | sqlite3 $db; done
$ git diff
@@ -30,3 +30,5 @@ CREATE TABLE player_score (
   created_at BIGINT NOT NULL,
   updated_at BIGINT NOT NULL
 );
+
+CREATE INDEX idx_score ON player_score (tenant_id, competition_id, player_id);
09:21:44.540379 SCORE: 9868 (+11747 -1879(16%))
22:42:51.168658 SCORE: 15122 (+16087 -965(6%))

解説にあるとおりスコアが1.5倍になりました。

感想

必要なインデックスの洗い出しや、for db in *.db.. のコマンドを時間かけずにできるようにする必要がありそうです。

予選突破スコアは22,000点あたりなので、近づいてきました。

参考

1 ISUCON12 予選の解説 (Node.jsでSQLiteのまま10万点行く方法)

2 ISUCON12 予選当日マニュアル