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点あたりなので、近づいてきました。