테스트 데이터 삽입 ‐ (5) 게시글 데이터 삽입 - ttasjwi/board-system GitHub Wiki

게시글 데이터

  • 게시판에 벌크 게시글을 추가한다.
  • 서비스에서 가장 많이 사용되는 기능이 게시글 조회이니만큼, 테스트 데이터는 2400만건정도로 잡는다.

테이블 생성

CREATE TABLE IF NOT EXISTS articles(
    article_id          BIGINT        NOT NULL PRIMARY KEY,
    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
);

csv 파일 생성

package com.ttasjwi.board.system.app.article.data

import org.junit.jupiter.api.DisplayName
import org.junit.jupiter.api.Test
import java.io.File
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*

class ArticleDataInitializer {

    @Test
    @DisplayName("게시글 벌크 삽입 csv 파일 생성")
    fun initialize() {
        val csvFile = File("articles.csv")
        val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")

        val totalArticleCount = 24_000_000L
        val random = Random()

        csvFile.bufferedWriter().use { writer ->

            for (articleId in 1L..totalArticleCount) {
                val paddedId = articleId.toString().padStart(8, '0')
                val title = "title$paddedId"
                val content = "content$paddedId"
                val (boardId, articleCategoryId) = getRandomBoardArticleCategoryId(random)
                val (writerId, writerNickname) = getRandomWriter(random)
                val baseTime = LocalDateTime.of(2025, 6, 10, 0, 0)
                val createdAt = baseTime.plusSeconds(articleId).format(formatter)
                val modifiedAt = createdAt
                writer.write("$articleId,$title,$content,$boardId,$articleCategoryId,$writerId,$writerNickname,$createdAt,$modifiedAt\n")

                if (articleId % 100000 == 0L) {
                    println("Written $articleId lines...")
                }
            }
        }
    }

    private fun getRandomBoardArticleCategoryId(random: Random): Pair<Long, Long> {
        val otherBoards = (2..500L).toList()  // 499개 게시판

        // 50% 의 확률로 게시판 1번에 게시글 작성, 나머지 50% 확률을 499개 게시판이 분할
        val boardId = if (random.nextDouble() < 0.5) {
            1L
        } else {
            otherBoards[random.nextInt(otherBoards.size)]
        }

        val articleCategoryId = 4 * (boardId - 1) + random.nextLong(3L) + 1 // (일반, 질문, 정보)

        return Pair(boardId, articleCategoryId)
    }

    private fun getRandomWriter(random: Random): Pair<Long, String> {
        val userId = random.nextLong(12_000_000L) + 1L
        val userNickname = userId.toString().padStart(8, '0')
        return Pair(userId, userNickname)
    }
}
  • 총 2400만개의 게시글 작성
  • 랜덤: 게시판 1번에 게시글이 작성될 확률은 50%로 설정, 나머지 499개의 게시판은 전체 확률의 나머지 50%를 균등하게 분배
    • 데이터 쏠림 현상 방지: 특정 게시판에만 데이터가 집중되는 것을 막고, 보다 현실적인 데이터 분포를 시뮬레이션하기 위함
    • 게시판 1번 페이지네이션/조회 성능 테스트 목적: 1번 게시판에 한해서 게시글 페이징 조회를 해보려고 함.
    • 페이징 조회를 하려는 게시판은 1번 게시판인데, 그렇다고 1번 게시판 글만 삽입하면 질의 과정이 의도와 벗어나는 경우도 있을 수 있음.

데이터 삽입

docker cp data/articles.csv mysql:/tmp/articles.csv
LOAD DATA INFILE '/tmp/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);

데이터 조회

mysql> select count(*) from articles;
+----------+
| count(*) |
+----------+
| 24000000 |
+----------+
1 row in set (5.59 sec)

mysql> select count(*) from articles where board_id=1;
+----------+
| count(*) |
+----------+
| 12001499 |
+----------+
1 row in set (8.20 sec)

mysql> select * from articles where board_id = 1 order by article_id desc limit 30;
+------------+---------------+-----------------+----------+---------------------+-----------+-----------------+---------------------+---------------------+
| article_id | title         | content         | board_id | article_category_id | writer_id | writer_nickname | created_at          | modified_at         |
+------------+---------------+-----------------+----------+---------------------+-----------+-----------------+---------------------+---------------------+
|   24000000 | title24000000 | content24000000 |        1 |                   1 |    114936 | user00114936    | 2026-03-14 18:40:00 | 2026-03-14 18:40:00 |
|   23999999 | title23999999 | content23999999 |        1 |                   2 |   6081206 | user06081206    | 2026-03-14 18:39:59 | 2026-03-14 18:39:59 |
|   23999998 | title23999998 | content23999998 |        1 |                   1 |   9132070 | user09132070    | 2026-03-14 18:39:58 | 2026-03-14 18:39:58 |
|   23999995 | title23999995 | content23999995 |        1 |                   2 |    598219 | user00598219    | 2026-03-14 18:39:55 | 2026-03-14 18:39:55 |
|   23999994 | title23999994 | content23999994 |        1 |                   2 |   8374508 | user08374508    | 2026-03-14 18:39:54 | 2026-03-14 18:39:54 |
|   23999989 | title23999989 | content23999989 |        1 |                   3 |   2637930 | user02637930    | 2026-03-14 18:39:49 | 2026-03-14 18:39:49 |
|   23999986 | title23999986 | content23999986 |        1 |                   3 |  10344527 | user10344527    | 2026-03-14 18:39:46 | 2026-03-14 18:39:46 |
|   23999985 | title23999985 | content23999985 |        1 |                   1 |    851571 | user00851571    | 2026-03-14 18:39:45 | 2026-03-14 18:39:45 |
|   23999983 | title23999983 | content23999983 |        1 |                   2 |   3129142 | user03129142    | 2026-03-14 18:39:43 | 2026-03-14 18:39:43 |
|   23999982 | title23999982 | content23999982 |        1 |                   2 |   3620071 | user03620071    | 2026-03-14 18:39:42 | 2026-03-14 18:39:42 |
|   23999981 | title23999981 | content23999981 |        1 |                   1 |   5627599 | user05627599    | 2026-03-14 18:39:41 | 2026-03-14 18:39:41 |
|   23999978 | title23999978 | content23999978 |        1 |                   1 |   7107127 | user07107127    | 2026-03-14 18:39:38 | 2026-03-14 18:39:38 |
|   23999977 | title23999977 | content23999977 |        1 |                   1 |   4071222 | user04071222    | 2026-03-14 18:39:37 | 2026-03-14 18:39:37 |
|   23999971 | title23999971 | content23999971 |        1 |                   2 |   3510705 | user03510705    | 2026-03-14 18:39:31 | 2026-03-14 18:39:31 |
|   23999970 | title23999970 | content23999970 |        1 |                   3 |   9836419 | user09836419    | 2026-03-14 18:39:30 | 2026-03-14 18:39:30 |
|   23999967 | title23999967 | content23999967 |        1 |                   2 |   7730696 | user07730696    | 2026-03-14 18:39:27 | 2026-03-14 18:39:27 |
|   23999963 | title23999963 | content23999963 |        1 |                   1 |   8062791 | user08062791    | 2026-03-14 18:39:23 | 2026-03-14 18:39:23 |
|   23999960 | title23999960 | content23999960 |        1 |                   2 |   2130261 | user02130261    | 2026-03-14 18:39:20 | 2026-03-14 18:39:20 |
|   23999954 | title23999954 | content23999954 |        1 |                   1 |   2664214 | user02664214    | 2026-03-14 18:39:14 | 2026-03-14 18:39:14 |
|   23999953 | title23999953 | content23999953 |        1 |                   3 |   8128313 | user08128313    | 2026-03-14 18:39:13 | 2026-03-14 18:39:13 |
|   23999951 | title23999951 | content23999951 |        1 |                   2 |    146463 | user00146463    | 2026-03-14 18:39:11 | 2026-03-14 18:39:11 |
|   23999950 | title23999950 | content23999950 |        1 |                   2 |   1519817 | user01519817    | 2026-03-14 18:39:10 | 2026-03-14 18:39:10 |
|   23999948 | title23999948 | content23999948 |        1 |                   2 |  10274830 | user10274830    | 2026-03-14 18:39:08 | 2026-03-14 18:39:08 |
|   23999944 | title23999944 | content23999944 |        1 |                   1 |   6568790 | user06568790    | 2026-03-14 18:39:04 | 2026-03-14 18:39:04 |
|   23999943 | title23999943 | content23999943 |        1 |                   2 |   1776794 | user01776794    | 2026-03-14 18:39:03 | 2026-03-14 18:39:03 |
|   23999940 | title23999940 | content23999940 |        1 |                   1 |   6221817 | user06221817    | 2026-03-14 18:39:00 | 2026-03-14 18:39:00 |
|   23999935 | title23999935 | content23999935 |        1 |                   1 |   7590757 | user07590757    | 2026-03-14 18:38:55 | 2026-03-14 18:38:55 |
|   23999934 | title23999934 | content23999934 |        1 |                   1 |  10101027 | user10101027    | 2026-03-14 18:38:54 | 2026-03-14 18:38:54 |
|   23999933 | title23999933 | content23999933 |        1 |                   1 |   9070568 | user09070568    | 2026-03-14 18:38:53 | 2026-03-14 18:38:53 |
|   23999930 | title23999930 | content23999930 |        1 |                   2 |   2711451 | user02711451    | 2026-03-14 18:38:50 | 2026-03-14 18:38:50 |
+------------+---------------+-----------------+----------+---------------------+-----------+-----------------+---------------------+---------------------+
30 rows in set (0.00 sec)
⚠️ **GitHub.com Fallback** ⚠️