search

kno3a87 / isucon13

Apache License 2.0
0 stars 0 forks source link

データベースのスキーマ🍔 #1

Open euglena1215 opened 11 months ago

kno3a87 commented 11 months ago 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isudns             |
| isupipe            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show tables;
+----------------------------+
| Tables_in_isupipe          |
+----------------------------+
| icons                      |
| livecomment_reports        |
| livecomments               |
| livestream_tags            |
| livestream_viewers_history |
| livestreams                |
| ng_words                   |
| reactions                  |
| reservation_slots          |
| tags                       |
| themes                     |
| users                      |
+----------------------------+
12 rows in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table icons;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| icons | CREATE TABLE `icons` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `image` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from icons;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table livecomment_reports;
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                                                                                                                                                                                     |
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| livecomment_reports | CREATE TABLE `livecomment_reports` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `livestream_id` bigint NOT NULL,
  `livecomment_id` bigint NOT NULL,
  `created_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from livecomment_reports;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table livecomments;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                          |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| livecomments | CREATE TABLE `livecomments` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `livestream_id` bigint NOT NULL,
  `comment` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `tip` bigint NOT NULL DEFAULT '0',
  `created_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from livecomments;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.01 sec)
kno3a87 commented 11 months ago 
mysql> show create table livestream_tags;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| livestream_tags | CREATE TABLE `livestream_tags` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `livestream_id` bigint NOT NULL,
  `tag_id` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10967 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from livestream_tags;
+----------+
| count(*) |
+----------+
|    10966 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table livestream_viewers_history;
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                                                                                                                                                                                                                        |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| livestream_viewers_history | CREATE TABLE `livestream_viewers_history` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `livestream_id` bigint NOT NULL,
  `created_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from livestream_viewers_history;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
kno3a87 commented 11 months ago 
mysql> show create table livestreams;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| livestreams | CREATE TABLE `livestreams` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `description` text COLLATE utf8mb4_bin NOT NULL,
  `playlist_url` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `thumbnail_url` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `start_at` bigint NOT NULL,
  `end_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7496 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from livestreams;
+----------+
| count(*) |
+----------+
|     7495 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table ng_words;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                               |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ng_words | CREATE TABLE `ng_words` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `livestream_id` bigint NOT NULL,
  `word` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `created_at` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ng_words_word` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=14338 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from ng_words;
+----------+
| count(*) |
+----------+
|    14337 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table reactions;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                     |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reactions | CREATE TABLE `reactions` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `livestream_id` bigint NOT NULL,
  `emoji_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `created_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from reactions;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table reservation_slots;
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                       |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reservation_slots | CREATE TABLE `reservation_slots` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `slot` bigint NOT NULL,
  `start_at` bigint NOT NULL,
  `end_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8760 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from reservation_slots;
+----------+
| count(*) |
+----------+
|     8759 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 

mysql> show create table tags;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tags  | CREATE TABLE `tags` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_tag_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from tags;
+----------+
| count(*) |
+----------+
|      103 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table themes;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                        |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| themes | CREATE TABLE `themes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `dark_mode` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select count(*) from themes;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
kno3a87 commented 11 months ago 
mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `description` text COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
euglena1215 commented 11 months ago 
mysql> ALTER TABLE icons ADD COLUMN icon_hash VARCHAR(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

icons テーブルに icon_hash カラムを追加

kno3a87 commented 11 months ago

index貼ったやつ

ALTER TABLE livestream_tags ADD INDEX idx_livestream_id (livestream_id);
ALTER TABLE icons ADD INDEX idx_user_id (user_id);
kno3a87 commented 11 months ago

index 貼ったやつ

ALTER TABLE livestreams ADD INDEX idx_user_id (user_id);
kno3a87 commented 11 months ago

ref: https://github.com/kno3a87/isucon13/issues/3#issuecomment-1826235155 index

ALTER TABLE themes ADD INDEX idx_user_id (user_id);
kno3a87 commented 11 months ago

server 3 だけ追加

ALTER TABLE users ADD COLUMN total_tip BIGINT DEFAULT 0;
euglena1215 commented 11 months ago

2だけ追加 → 全部に追加

ALTER TABLE ng_words ADD INDEX idx_user_id_livestream_id (user_id, livestream_id);
kno3a87 commented 11 months ago

3 だけ

ALTER TABLE users ADD COLUMN total_reaction BIGINT DEFAULT 0;
euglena1215 commented 11 months ago 
erDiagram
    USERS ||--o{ ICONS : has
    USERS ||--o{ LIVECOMMENT_REPORTS : reports
    USERS ||--o{ LIVECOMMENTS : comments
    USERS ||--o{ LIVESTREAMS : streams
    USERS ||--o{ NG_WORDS : sets
    USERS ||--o{ REACTIONS : reacts
    USERS ||--o{ THEMES : prefers
    LIVESTREAMS ||--o{ LIVECOMMENT_REPORTS : reported_for
    LIVESTREAMS ||--o{ LIVECOMMENTS : includes
    LIVESTREAMS ||--o{ LIVESTREAM_TAGS : tagged_with
    LIVESTREAMS ||--o{ LIVESTREAM_VIEWERS_HISTORY : has_viewers
    LIVESTREAMS ||--o{ NG_WORDS : bans_words
    LIVESTREAMS ||--o{ REACTIONS : receives
    TAGS ||--o{ LIVESTREAM_TAGS : tags

    USERS {
        bigint id PK
        varchar name
        varchar display_name
        varchar password
        text description
    }

    ICONS {
        bigint id PK
        bigint user_id FK
        longblob image
    }

    LIVECOMMENT_REPORTS {
        bigint id PK
        bigint user_id FK
        bigint livestream_id FK
        bigint livecomment_id FK
        bigint created_at
    }

    LIVECOMMENTS {
        bigint id PK
        bigint user_id FK
        bigint livestream_id FK
        varchar comment
        bigint tip
        bigint created_at
    }

    LIVESTREAM_TAGS {
        bigint id PK
        bigint livestream_id FK
        bigint tag_id FK
    }

    LIVESTREAM_VIEWERS_HISTORY {
        bigint id PK
        bigint user_id FK
        bigint livestream_id FK
        bigint created_at
    }

    LIVESTREAMS {
        bigint id PK
        bigint user_id FK
        varchar title
        text description
        varchar playlist_url
        varchar thumbnail_url
        bigint start_at
        bigint end_at
    }

    NG_WORDS {
        bigint id PK
        bigint user_id FK
        bigint livestream_id FK
        varchar word
        bigint created_at
    }

    REACTIONS {
        bigint id PK
        bigint user_id FK
        bigint livestream_id FK
        varchar emoji_name
        bigint created_at
    }

    RESERVATION_SLOTS {
        bigint id PK
        bigint slot
        bigint start_at
        bigint end_at
    }

    TAGS {
        bigint id PK
        varchar name
    }

    THEMES {
        bigint id PK
        bigint user_id FK
        tinyint dark_mode
    }

https://chat.openai.com/share/2d45bf7c-c67d-4db5-a074-f9fe771f25dc

すげ〜〜〜