DBについて

[tiwanari]

DBについて、準備や本番の環境の改善できそうなところ・困っているところなどなんでもコメント

[vintersnow]

DB初期化

/home/isucon/isucari/webapp/sql/init.sh

[vintersnow]

01_schema.sql

DROP TABLE IF EXISTS `configs`;
CREATE TABLE configs (
    `name` VARCHAR(191) NOT NULL PRIMARY KEY,
    `val` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `account_name` varchar(128) NOT NULL UNIQUE,
  `hashed_password` varbinary(191) NOT NULL,
  `address` varchar(191) NOT NULL,
  `num_sell_items` int unsigned NOT NULL DEFAULT 0,
  `last_bump` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `seller_id` bigint NOT NULL,
  `buyer_id` bigint NOT NULL DEFAULT 0,
  `status` enum('on_sale', 'trading', 'sold_out', 'stop', 'cancel') NOT NULL,
  `name` varchar(191) NOT NULL,
  `price` int unsigned NOT NULL,
  `description` text NOT NULL,
  `image_name` varchar(191) NOT NULL,
  `category_id` int unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category_id (`category_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `transaction_evidences`;
CREATE TABLE `transaction_evidences` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `seller_id` bigint NOT NULL,
  `buyer_id` bigint NOT NULL,
  `status` enum('wait_shipping', 'wait_done', 'done') NOT NULL,
  `item_id` bigint NOT NULL UNIQUE,
  `item_name` varchar(191) NOT NULL,
  `item_price` int unsigned NOT NULL,
  `item_description` text NOT NULL,
  `item_category_id` int unsigned NOT NULL,
  `item_root_category_id` int unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `shippings`;
CREATE TABLE `shippings` (
  `transaction_evidence_id` bigint NOT NULL PRIMARY KEY,
  `status` enum('initial', 'wait_pickup', 'shipping', 'done') NOT NULL,
  `item_name` varchar(191) NOT NULL,
  `item_id` bigint NOT NULL,
  `reserve_id` varchar(191) NOT NULL,
  `reserve_time` bigint NOT NULL,
  `to_address` varchar(191) NOT NULL,
  `to_name` varchar(191) NOT NULL,
  `from_address` varchar(191) NOT NULL,
  `from_name` varchar(191) NOT NULL,
  `img_binary` mediumblob NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `parent_id` int unsigned NOT NULL,
  `category_name` varchar(191) NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

[vintersnow]

初回クエリ分析

• SELECT categoriesが死ぬほど呼ばれている (222359) そんなに変わらないだろうし、キャッシュ出来そう @tiwanari

• select items の条件が複雑。どうすれば最適なのか？

  SELECT * FROM `items` WHERE `status` IN ('on_sale','sold_out') AND category_id IN (61, 62, 63, 64, 65, 66) AND (`created_at` < '2019-08-12 15:50:09'  OR (`created_at` <= '2019-08-12 15:50:09' AND `id` < 49805)) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

[tiwanari]

datetime の範囲指定のメモ [MYSQL] datetimeカラムのインデックスの使われ方をテストしてみた │ M0DE

クエリが悪いと使われないこともあるよう

[tiwanari]

db サーバを立てた 47.74.38.176 (global) 172.24.242.195 (private)

[tiwanari]

Host isucon9-db
 User root
 Hostname 47.74.38.176
 IdentityFile ~/.ssh/isucon-nyan.pem

[tiwanari]

users => account_name transaction_evidences => item_id, status(?) shippings => transaction_evidence_id (primary key だったのでOK)

items はもう貼ってくれた

[vintersnow]

CREATE TABLE items ( id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, seller_id bigint NOT NULL, buyer_id bigint NOT NULL DEFAULT 0, status enum('on_sale', 'trading', 'sold_out', 'stop', 'cancel') NOT NULL, name varchar(191) NOT NULL, price int unsigned NOT NULL, description text NOT NULL, image_name varchar(191) NOT NULL, category_id int unsigned NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_category_id (category_id, created_at), INDEX idx_seller_id (seller_id, created_at), INDEX idx_buyer_id (buyer_id, created_at) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;