AWS 인프라 구축 ‐ (6) RDS에 데이터 삽입 - ttasjwi/board-system GitHub Wiki

문제상황

• 성능 테스트 목적으로 RDS 에 벌크로 데이터를 삽입해둬야한다.
  • 회원 : 1200만
  • 소셜연동 : 성능테스트 생략
  • 게시판 : 500
  • 게시글 카테고리 : 2000 (각 게시판별 4개)
  • 게시글 : 2400만
  • 게시글 좋아요 : 약 1440만
  • 게시글 싫어요 : 약 1440만
  • 게시글 댓글 : 2400만

---

회원 테이블 생성, 데이터 삽입

use board_db_production;

drop table users;


create table if not exists users(
    user_id       BIGINT       NOT NULL,
    email         VARCHAR(255) NOT NULL,
    password      VARCHAR(68)  NOT NULL,
    username      VARCHAR(15)  NOT NULL,
    nickname      VARCHAR(15)  NOT NULL,
    role          VARCHAR(10)  NOT NULL,
    registered_at DATETIME     NOT NULL
);

ALTER TABLE users
    ADD PRIMARY KEY (user_id),
    ADD CONSTRAINT unique_email UNIQUE (email),
    ADD CONSTRAINT unique_username UNIQUE (username),
    ADD CONSTRAINT unique_nickname UNIQUE (nickname);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\users.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (user_id, email, password, username, nickname, role, registered_at);

SELECT COUNT(*) FROM users;
SELECT * FROM users ORDER BY user_id DESC LIMIT 5;

SHOW INDEX FROM users;

---

소셜연동 테이블 생성

use board_db_production;

CREATE TABLE IF NOT EXISTS social_connections(
    social_connection_id   BIGINT       NOT NULL PRIMARY KEY,
    user_id                BIGINT       NOT NULL,
    social_service         VARCHAR(20)  NOT NULL,
    social_service_user_id VARCHAR(100) NOT NULL,
    linked_at              DATETIME     NOT NULL
);

---

게시판 테이블 생성, 삽입

use board_db_production;

CREATE TABLE IF NOT EXISTS boards(
    board_id    BIGINT       NOT NULL,
    name        VARCHAR(30)  NOT NULL,
    description VARCHAR(100) NOT NULL,
    manager_id  BIGINT       NOT NULL,
    slug        VARCHAR(30)  NOT NULL,
    created_at  DATETIME     NOT NULL
);

ALTER TABLE boards
    ADD PRIMARY KEY (board_id),
    ADD CONSTRAINT unique_name UNIQUE (name),
    ADD CONSTRAINT unique_slug UNIQUE (slug);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\boards.csv'
    INTO TABLE boards
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (board_id, name, description, manager_id, slug, created_at);

SELECT COUNT(*) FROM boards;
select * from boards order by board_id asc limit 30;

SHOW INDEX FROM boards;

---

게시글 카테고리 테이블 생성, 데이터 삽입

use board_db_production;

CREATE TABLE IF NOT EXISTS article_categories(
    article_category_id    BIGINT      NOT NULL,
    name                   VARCHAR(20) NOT NULL,
    slug                   VARCHAR(8)  NOT NULL,
    board_id               BIGINT      NOT NULL,
    allow_write            BOOLEAN     NOT NULL,
    allow_self_edit_delete BOOLEAN     NOT NULL,
    allow_comment          BOOLEAN     NOT NULL,
    allow_like             BOOLEAN     NOT NULL,
    allow_dislike          BOOLEAN     NOT NULL,
    created_at             DATETIME    NOT NULL
);

ALTER TABLE article_categories
    ADD PRIMARY KEY (article_category_id),
    ADD CONSTRAINT uq_board_id_and_name UNIQUE (board_id, name),
    ADD CONSTRAINT uq_board_id_and_slug UNIQUE (board_id, slug);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\article-categories.csv'
    INTO TABLE article_categories
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (article_category_id, name, slug, board_id, allow_write, allow_self_edit_delete, allow_comment, allow_like, allow_dislike, created_at);

select count(*) from article_categories;
select * from article_categories order by article_category_id asc limit 30;

SHOW INDEX FROM article_categories;

---

게시글 테이블 생성, 데이터 삽입

use board_db_production;

CREATE TABLE IF NOT EXISTS articles(
    article_id          BIGINT        NOT NULL,
    title               VARCHAR(50)   NOT NULL,
    content             VARCHAR(3000) NOT NULL,
    board_id            BIGINT        NOT NULL,
    article_category_id BIGINT        NOT NULL,
    writer_id           BIGINT        NOT NULL,
    writer_nickname     VARCHAR(15)   NOT NULL,
    created_at          DATETIME      NOT NULL,
    modified_at         DATETIME      NOT NULL
);

ALTER TABLE articles
    ADD PRIMARY KEY (article_id),
    ADD INDEX idx_board_id_article_id (board_id ASC, article_id DESC);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\articles.csv'
    INTO TABLE articles
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (article_id, title, content, board_id, article_category_id, writer_id, writer_nickname, created_at, modified_at);

select count(*) from articles;
select count(*) from articles where board_id=1;
select * from articles where board_id = 1 order by article_id desc limit 30;

drop table board_article_counts;

CREATE TABLE if not exists board_article_counts(
    board_id BIGINT NOT NULL PRIMARY KEY,
    article_count BIGINT NOT NULL
);

INSERT INTO board_article_counts (board_id, article_count)
SELECT board_id, COUNT(*) AS article_count
FROM articles
GROUP BY board_id;

SELECT * from board_article_counts where board_id = 1;

ALTER TABLE articles
    DROP INDEX idx_board_id_article_id;

SHOW INDEX FROM articles;
SHOW INDEX FROM board_article_counts;

• 게시글, 게시판 게시글수

---

게시글 좋아요/싫어요 테이블 생성, 데이터 삽입

use board_db_production;

CREATE TABLE IF NOT EXISTS article_likes(
    article_like_id BIGINT   NOT NULL,
    article_id      BIGINT   NOT NULL,
    user_id         BIGINT   NOT NULL,
    created_at      DATETIME NOT NULL
);

ALTER TABLE article_likes
    ADD PRIMARY KEY (article_like_id),
    ADD CONSTRAINT uq_article_id_and_user_id UNIQUE (article_id, user_id);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\article-likes.csv'
    INTO TABLE article_likes
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (article_like_id, article_id, user_id, created_at);

SHOW INDEX FROM article_likes;

select count(*) from article_likes;
select * from article_likes where article_id = 6;

CREATE TABLE IF NOT EXISTS article_like_counts(
    article_id BIGINT NOT NULL PRIMARY KEY,
    like_count BIGINT NOT NULL
);

INSERT INTO article_like_counts (article_id, like_count)
SELECT article_id, COUNT(*) AS like_count
FROM article_likes
GROUP BY article_id;

select * from article_like_counts limit 10 offset 10;
select * from article_like_counts where article_id = 6;

SHOW INDEX FROM article_like_counts;

CREATE TABLE IF NOT EXISTS article_dislikes(
    article_dislike_id BIGINT   NOT NULL,
    article_id         BIGINT   NOT NULL,
    user_id            BIGINT   NOT NULL,
    created_at         DATETIME NOT NULL
);

ALTER TABLE article_dislikes
    ADD PRIMARY KEY (article_dislike_id),
    ADD CONSTRAINT uq_article_id_and_user_id UNIQUE (article_id, user_id);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\article-dislikes.csv'
    INTO TABLE article_dislikes
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (article_dislike_id, article_id, user_id, created_at);

WITH cte AS (
    SELECT ad.article_id
    FROM article_dislikes ad
             JOIN articles a ON a.article_id = ad.article_id
    WHERE a.article_category_id % 4 = 3
)
DELETE FROM article_dislikes
WHERE article_id IN (SELECT article_id FROM cte);

SHOW INDEX FROM article_dislikes;

select count(*) from article_dislikes;

select ad.article_id, ad.user_id, a.article_category_id
from article_dislikes ad
         join articles a on a.article_id = ad.article_id
where ad.article_id = 11;

CREATE TABLE IF NOT EXISTS article_dislike_counts(
    article_id BIGINT NOT NULL PRIMARY KEY,
    dislike_count BIGINT NOT NULL
);

INSERT INTO article_dislike_counts (article_id, dislike_count)
SELECT article_id, COUNT(*) AS dislike_count
FROM article_dislikes
GROUP BY article_id;

select * from article_dislike_counts limit 10 offset 10;

select * from article_dislike_counts where article_id = 11;

SHOW INDEX FROM article_dislike_counts;

---

게시글 댓글 테이블 생성, 데이터 삽입

use board_db_production;

CREATE TABLE IF NOT EXISTS article_comments(
    article_comment_id             BIGINT        NOT NULL PRIMARY KEY,
    content                        VARCHAR(3000) NOT NULL,
    article_id                     BIGINT        NOT NULL,
    root_parent_comment_id         BIGINT        NOT NULL,
    writer_id                      BIGINT        NOT NULL,
    writer_nickname                VARCHAR(15)   NOT NULL,
    parent_comment_writer_id       BIGINT,
    parent_comment_writer_nickname VARCHAR(15),
    delete_status                  VARCHAR(20)   NOT NULL,
    created_at                     DATETIME      NOT NULL,
    modified_at                    DATETIME      NOT NULL
);

ALTER TABLE article_comments
    ADD INDEX idx_article_id_root_parent_comment_id_article_comment_id (article_id ASC, root_parent_comment_id ASC, article_comment_id ASC);

LOAD DATA LOCAL INFILE 'C:\\Users\\ttasjwi\\data\\article-comments.csv'
    INTO TABLE article_comments
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (article_comment_id, content, article_id, root_parent_comment_id, writer_id, writer_nickname, @parent_comment_writer_id, @parent_comment_writer_nickname, delete_status, created_at, modified_at)
    SET
        parent_comment_writer_id = NULLIF(@parent_comment_writer_id, ''),
        parent_comment_writer_nickname = NULLIF(@parent_comment_writer_nickname, '');

select count(*) from article_comments;
select count(*) from article_comments where article_id=24000000;
select * from article_comments where article_id = 24000000 order by root_parent_comment_id asc, article_comment_id asc limit 30 offset 2500000;

CREATE TABLE IF NOT EXISTS article_comment_counts(
    article_id    BIGINT NOT NULL PRIMARY KEY,
    comment_count BIGINT NOT NULL
);

INSERT INTO article_comment_counts (article_id, comment_count)
SELECT article_id, COUNT(*) AS comment_count
FROM article_comments
GROUP BY article_id;

select * from article_comment_counts where article_id = 24000000;

ALTER TABLE article_comments
    DROP INDEX idx_article_id_root_parent_comment_id_article_comment_id;

SHOW INDEX FROM article_comments;
SHOW INDEX FROM article_comment_counts;

• 댓글, 게시글별 댓글수