스프링부트 게시판 (2)

댓글 요구사항

• 댓글 조회, 생성, 삭제 API

• 댓글 목록 조회 API

    • 계층형

        • 최대 2 depth의 대댓글

        • 무한 depth의 대댓글

    • 계층별 오래된 순 정렬

    • 페이지 번호, 무한 스크롤

• 하위 댓글이 모두 삭제되어야 상위 댓글을 삭제할 수 있다

    • 하위 댓글이 없으면, 댓글은 즉시 삭제된다

    • 하위 댓글이 있으면, 댓글은 삭제 표시만 된다

 

 

 

댓글 테이블 설계 –최대 2 depth

 

 

comment - build.gradle 수정

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'com.mysql:mysql-connector-j'
    implementation project(':common:snowflake')
}

 

 

comment - application.yml 수정

server:
  port: 9001

spring:
  application:
    name: springboot-board-article-service
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/article
    username: root
    password: root
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
    open-in-view: false
    show-sql: true
    hibernate:
      ddl-auto: none

 

 

comment  - repository, service 작성

@Repository
public interface CommentRepository extends JpaRepository<Comment, Long> {
    @Query(
            value = "select count(*) from (" +
                    "   select comment_id from comment " +
                    "   where article_id = :articleId and parent_comment_id = :parentCommentId" +
                    "   limit :limit" +
                    ") t",
            nativeQuery = true
    )
    Long countBy(
            @Param("articleId") Long articleId,
            @Param("parentCommentId") Long parentCommentId,
            @Param("limit") Long limit
    );
}

@Service
@RequiredArgsConstructor
public class CommentService {
    private final CommentRepository commentRepository;
    private final Snowflake snowflake = new Snowflake();

    @Transactional
    public CommentResponse create(CommentCreateRequest request) {
        Comment parent = findParent(request);
        Comment comment = commentRepository.save(
                Comment.create(
                        snowflake.nextId(),
                        request.getContent(),
                        parent == null ? null : parent.getParentCommentId(),
                        request.getArticleId(),
                        request.getWriterId()
                )
        );
        return CommentResponse.from(comment);
    }

    private Comment findParent(CommentCreateRequest request) {
        Long parentCommentId = request.getParentCommentId();
        if (parentCommentId == null) {
            return null;
        }
        return commentRepository.findById(parentCommentId)
                .filter(not(Comment::isDeleted))
                .filter(Comment::isRoot)
                .orElseThrow();
    }

    public CommentResponse read(Long commentId) {
        return CommentResponse.from(commentRepository.findById(commentId).orElse(null));
    }

    @Transactional
    public void delete(Long commentId) {
        commentRepository.findById(commentId)
                .filter(not(Comment::isDeleted))
                .ifPresent(comment -> {
                    if (hasChildren(comment)) {
                        comment.delete();
                    } else {
                        delete(comment);
                    }
        });
    }

    private boolean hasChildren(Comment comment) {
        return commentRepository.countBy(comment.getArticleId(), comment.getCommentId(), 2L) == 2;
    }

    private void delete(Comment comment) {
        commentRepository.delete(comment);
        if (!comment.isRoot()) {
            commentRepository.findById(comment.getParentCommentId())
                    .filter(Comment::isDeleted)
                    .filter(not(this::hasChildren))
                    .ifPresent(this::delete);
        }
    }
}

@ExtendWith(MockitoExtension.class)
class CommentServiceTest {
    @InjectMocks
    CommentService commentService;

    @Mock
    CommentRepository commentRepository;

    @Test
    @DisplayName("삭제할 댓글이 자식 있으면, 삭제 표시만 한다.")
    void deleteShouldMarkDeletedIfHashChildren() {
        // given
        Long articleId = 1L;
        Long commentId = 2L;
        Comment comment = createComment(articleId, commentId);
        given(commentRepository.findById(commentId))
                .willReturn(Optional.of(comment));
        given(commentRepository.countBy(articleId, commentId, 2L)).willReturn(2L);

        // when
        commentService.delete(commentId);

        // then
        verify(comment).delete();
    }

    private Comment createComment(Long articleId, Long commentId) {
        Comment comment = mock(Comment.class);
        given(comment.getArticleId()).willReturn(articleId);
        given(comment.getCommentId()).willReturn(commentId);
        return comment;
    }

    private Comment createCommentWithParent(Long articleId, Long commentId, Long parentId) {
        Comment comment = createComment(articleId, commentId);
        given(comment.getParentCommentId()).willReturn(parentId);
        return comment;
    }

    @Test
    @DisplayName("하위 댓글이 삭제되고 삭제되지 않은 부모면 하위 댓글만 삭제한다.")
    void deleteShouldDeleteChildOnlyIfNotDeletedParent() {
        // given
        Long articleId = 1L;
        Long commentId = 2L;
        Long parentId = 1L;

        Comment comment = createCommentWithParent(articleId, commentId, parentId);
        given(comment.isRoot()).willReturn(false);

        Comment parentComment = mock(Comment.class);
         given(parentComment.isDeleted()).willReturn(false);

        given(commentRepository.findById(commentId))
                .willReturn(Optional.of(comment));
        given(commentRepository.countBy(articleId, commentId, 2L)).willReturn(1L);

        given(commentRepository.findById(parentId)).willReturn(Optional.of(parentComment));

        // when
        commentService.delete(commentId);

        // then
        verify(commentRepository).delete(comment);
        verify(commentRepository, never()).delete(parentComment);
    }

    @Test
    @DisplayName("하위 댓글이 삭제되고 삭제된 부모면, 재귀적으로 모두 삭제한다.")
    void deleteShouldDeleteAllRecursivelyDeleteParent() {
        // given
        Long articleId = 1L;
        Long commentId = 2L;
        Long parentId = 1L;

        Comment comment = createCommentWithParent(articleId, commentId, parentId);
        given(comment.isRoot()).willReturn(false);

        Comment parentComment = createComment(articleId, parentId);
        given(parentComment.isRoot()).willReturn(true);
        given(parentComment.isDeleted()).willReturn(true);

        given(commentRepository.findById(commentId))
                .willReturn(Optional.of(comment));
        given(commentRepository.countBy(articleId, commentId, 2L)).willReturn(1L);

        given(commentRepository.findById(parentId)).willReturn(Optional.of(parentComment));
        given(commentRepository.countBy(articleId, parentId, 2L)).willReturn(1L);

        // when
        commentService.delete(commentId);

        // then
        verify(commentRepository).delete(comment);
        verify(commentRepository).delete(parentComment);
    }
}

 

 

data init

@SpringBootTest
public class DataInitializer {
    @PersistenceContext
    EntityManager em;

    @Autowired
    TransactionTemplate transactionTemplate;

    Snowflake snowflake = new Snowflake();
    CountDownLatch latch = new CountDownLatch(EXECUTE_COUNT);

    static final int BULK_INSERT_SIZE = 2000;
    static final int EXECUTE_COUNT = 6000;

    @Test
    void initialize() throws InterruptedException {
        ExecutorService executorService = Executors.newFixedThreadPool(10);
        for (int i = 0; i < EXECUTE_COUNT; i++) {
            executorService.submit(() -> {
                insert();
                latch.countDown();
                System.out.println("latch.getCount() = " + latch.getCount());
            });
        }
        latch.await();
        executorService.shutdown();
    }

    void insert() {
        transactionTemplate.executeWithoutResult(status -> {
            Comment prev = null;
            for (int i = 0; i < BULK_INSERT_SIZE; i++) {
                Comment comment = Comment.create(
                        snowflake.nextId(),
                        "content",
                        i % 2 == 0 ? null : prev.getCommentId(),
                        1L,
                        1L
                );
                prev = comment;
                em.persist(comment);
            }
        });
    }
}

# application.yml
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
    open-in-view: false
    show-sql: false 
    hibernate:
      ddl-auto: none

게시판 데이터 삽입할 때는 귀찮아서 변경 안 했었는데 이번에는 "show-sql: false"로 변경하고 돌렸다 

 

"show-sql: true"인 상태라면 모든 SQL 쿼리가 콘솔에 출력되며 콘솔 버퍼가 가득차고 intelliJ가 모든 로그를 메모리에 저장하기 때문에 RAM 부족 경고가 뜨며 종료됐던거였다 ..........................!!

노트북 문제인가 했는데 .....

 

 

인덱스 생성

create index idx_article_id_parent_comment_id_comment_id on comment ( article_id asc, parent_comment_id asc, comment_id asc );

 

 

N번 페이지에서 M개의 댓글 조회

select * from ( select comment_id from comment where article_id = {article_id} order by parent_comment_id asc, comment_id asc limit {limit} offset {offset} ) t left join comment on t.comment_id = comment.comment_id;

• comment_id : 서브쿼리로 커버링 인덱스 뽑아내서 쿼리 실행 

 

 

댓글 개수 조회

select count(*) from ( select comment_id from comment where article_id = {article_id} limit {limit} ) t;

• comment_id : 서브쿼리로 커버링 인덱스 뽑아내서 쿼리 실행 

 

 

무한 스크롤 댓글 조회

- 1번 페이지
select * from comment where article_id = {article_id} order by parent_comment_id asc, comment_id asc limit {limit};

- 2번 페이지 이상 (기준점 = last_parent_comment_id, last_comment_id)
select * from comment where article_id = {article_id} and ( parent_comment_id > {last_parent_comment_id} or (parent_comment_id = {last_parent_comment_id} and comment_id > {last_comment_id}) ) order by parent_comment_id asc, comment_id asc limit {limit};

 

 

@Repository
public interface CommentRepository extends JpaRepository<Comment, Long> {
    @Query(
            value = "select comment.comment_id, comment.content, comment.parent_comment_id, comment.article_id, " +
                    "comment.writer_id, comment.deleted, comment.created_at " +
                    "from ( " +
                    "   select comment_id from comment where article_id = :articleId " +
                    "   order by parent_comment_id asc, comment_id asc " +
                    "   limit :limit offset :offset " +
                    ") t left join comment on t.comment_id = comment.comment_id",
            nativeQuery = true
    )
    List<Comment> findAll(
        @Param("articleId") Long articleId,
        @Param("offset") Long offset,
        @Param("limit") Long limit
    );

    @Query(
            value = "select count(*) from (" +
                    "   select comment_id from comment where article_id = :articleId limit :limit" +
                    ") t",
            nativeQuery = true
    )
    Long count(
            @Param("articleId") Long articleId,
            @Param("limit") Long limit
    );

    @Query(
            value = "select comment.comment_id, comment.content, comment.parent_comment_id, comment.article_id, " +
                    "comment.writer_id, comment.deleted, comment.created_at " +
                    "from comment " +
                    "where article_id = :articleId " +
                    "order by parent_comment_id asc, comment_id asc " +
                    "limit :limit",
            nativeQuery = true
    )
    List<Comment> findAllInfiniteScroll(
            @Param("articleId") Long articleId,
            @Param("limit") Long limit
    );

    @Query(
            value = "select comment.comment_id, comment.content, comment.parent_comment_id, comment.article_id, " +
                    "comment.writer_id, comment.deleted, comment.created_at " +
                    "from comment " +
                    "where article_id = :articleI and (" +
                    "    parent_comment_id > :lastParentCommentId or " +
                    "   (parent_comment_id = :lastParentCommentId and comment_id > : lastCommentId)" +
                    ") " +
                    "order by parent_comment_id asc, comment_id asc " +
                    "limit :limit",
            nativeQuery = true
    )
    List<Comment> findAllInfiniteScroll(
            @Param("articleId") Long articleId,
            @Param("lastParentCommentId") Long lastParentCommentId,
            @Param("lastCommentId") Long lastCommentId,
            @Param("limit") Long limit
    );
}

 

 

 

댓글 무한 depth

• 무한 depth에서는 상하위 댓글이 재귀적으로 무한할 수 있으므로 정렬 순을 나타내기 위해 모든 상위 댓글의 정보가 필요할 수 있다 
• 댓글 구조를 트리로 본다면 root node부터 각 node에 도달하기 위한 경로로 이해할 수 있다 
• 경로에 의해 정렬 순서가 명확하기 때문에 경로 정보로 인덱스를 생성하면 페이징을 처리할 수 있다
• 각 depth 만큼 컬럼을 만들어서, 각 depth별 댓글 ID를 관리해줄 수있지만 depth는 무한할 수 있어야 하기 때문에 테이블 구조가 복잡해질 수 있다 
• 문자열 컬럼 1개로 경로 정보를 만든다
  • 각 depth에서의 순서를 문자열로 나타내고 문자열을 순서대로 결합하여 경로를 나타낸다 
  • Path Enumeration(경로 열거) 방식을 취할 수 있다 

 

 

• 각 경로는 상위 댓글의 경로를 상속하여 각 댓글마다 독립적이고 순차적인(문자열 순서) 경로가 생성된다 
• 각 자릿수 하나는 0-9, A-Z, a-z 62개의 문자를 사용하여 (문자열 순서 0-9 < A-Z < a-z) 표현할 수 있는 경로의 범위가 각 경로 별로 62^5 = 916,132,832개로 제한된다 
• “00000” 부터 “zzzzz”까지 증가하는 문자열의 덧셈
  • 62진수 문자열을 10진수 숫자로 바꿔서 +1 한 다음 숫자를 대응하는 문자 열로 다시 바꿔준다

 

 

데이터베이스 collation

• 문자열을 정렬하고 비교하는 방법을 정의하는 설정
  • 대소문자 구분, 악센트 구분, 언어별 정렬 순서 등을 포함
• utf8mb4_0900_ai_ci
  • utf8mb4 = 각 문자 최대 4바이트 utf8 지원
  • 0900 = 정렬 방식 버전
  • ai = 악센트 비구분
  • ci = 대소문자 비구분

 

 

무한 depth 댓글 테이블 설계

create table comment_v2 (
    comment_id bigint not null primary key,
    content varchar(3000) not null,
    article_id bigint not null,
    writer_id bigint not null,
    path varchar(25) character set utf8mb4 collate utf8mb4_bin not null,
    deleted bool not null,
    created_at datetime not null
);

• 댓글 경로를 나타내기 위한 path 컬럼 추가 
  • path 컬럼 utf8mb4_bin 설정
• parent_id 삭제
• depth를 더 늘려야 한다면 VARCHAR 크기 조절하면 됨 

 

 

create unique index idx_article_id_path on comment_v2( article_id asc, path asc );

• path에 인덱스 생성해 정렬 데이터 관리
  • 페이징에도 사용 가능
• path는 독립적인 경로를 갖기 때문에 uniqe index로 생성
  • 애플리케이션에서 동시성 문제 방지 

 

 

• comment_v2 테이블 path 컬럼에 collation 설정 적용 확인

 

 

• idx_article_id_path 인덱스 사용
• 인덱스가 데이터를 포함하기 때문에 Extras=Using index를 통해 커버링 인덱스로 동작
• Backward index scan : 인덱스를 역순으로 스캔하는 것 
  • 인덱스 트리 leaf node 간에 연결된 양방향 포인터 활용

 

 

public interface CommentV2Repository extends JpaRepository<CommentV2, Long> {
    @Query("select c from CommentV2 c where c.commentPath.path = :path")
    Optional<CommentV2> findByPath(@Param("path") String path);

    @Query(
            value = "select path from comment_v2 " +
                    "where article_id = :articleId and path > :pathPrefix and path like :pathPrefix " +
                    "order by path desc limit 1",
            nativeQuery = true
    )
    Optional<String> findDescendantsTopPath(
            @Param("articleId") Long articleId,
            @Param("pathPrefix") String pathPrefix
    );

    @Query(
            value = "select comment_v2.comment_id, comment_v2.content, comment_v2.path, comment_v2.article_id, " +
                    "comment_v2.writer_id, comment_v2.deleted, comment_v2.created_at " +
                    "from (" +
                    "    select comment_id from comment_v2 where article_id = :articleId " +
                    "    order by path asc " +
                    "    limit :limit offset :offset " +
                    ") t left join comment_v2 on t.comment_id = comment_v2.comment_id",
            nativeQuery = true
    )
    List<CommentV2> findAll(
            @Param("articleId") Long articleId,
            @Param("offset") Long offset,
            @Param("limit") Long limit
    );

    @Query(
            value = "select count(*) from (" +
                    "    select comment_id from comment_v2 where article_id = :articleId limit :limit " +
                    ") t",
            nativeQuery = true
    )
    Long count(@Param("articleId") Long articleId, @Param("limit") Long limit);

    @Query(
            value = "select comment_v2.comment_id, comment_v2.content, comment_v2.path, comment_v2.article_id, " +
                    "comment_v2.writer_id, comment_v2.deleted, comment_v2.created_at " +
                    "from comment_v2 " +
                    "where article_id = :articleId " +
                    "order by path asc " +
                    "limit :limit",
            nativeQuery = true
    )
    List<CommentV2> findAllInfiniteScroll(@Param("articleId") Long articleId, @Param("limit") Long limit);

    @Query(
            value = "select comment_v2.comment_id, comment_v2.content, comment_v2.path, comment_v2.article_id, " +
                    "comment_v2.writer_id, comment_v2.deleted, comment_v2.created_at " +
                    "from comment_v2 " +
                    "where article_id = :articleId and path > :lastPath " +
                    "order by path asc " +
                    "limit :limit",
            nativeQuery = true
    )
    List<CommentV2> findAllInfiniteScroll(
            @Param("articleId") Long articleId,
            @Param("lastPath") String lastPath,
            @Param("limit") Long limit);
}

@Service
@RequiredArgsConstructor
public class CommentV2Service {
    private final Snowflake snowflake = new Snowflake();
    private final CommentV2Repository commentRepository;

    @Transactional
    public CommentResponse create(CommentCreateRequestV2 request) {
        CommentV2 parent = findParent(request);
        CommentPath parentCommentPath = parent == null ? CommentPath.create("") : parent.getCommentPath();
        CommentV2 comment = commentRepository.save(
                CommentV2.create(
                        snowflake.nextId(),
                        request.getContent(),
                        request.getArticleId(),
                        request.getWriterId(),
                        parentCommentPath.createChildCommentPath(
                                commentRepository.findDescendantsTopPath(request.getArticleId(), parentCommentPath.getPath())
                                        .orElse(null)
                        )
                )
        );
        return CommentResponse.from(comment);
    }

    private CommentV2 findParent(CommentCreateRequestV2 request) {
        String parentPath = request.getParentPath();
        if (parentPath == null) {
            return null;
        }
        return commentRepository.findByPath(parentPath)
                .filter(not(CommentV2::isDeleted))
                .orElseThrow();
    }

    public CommentResponse read(Long commentId) {
        return CommentResponse.from(
                commentRepository.findById(commentId).orElseThrow()
        );
    }

    @Transactional
    public void delete(Long commentId) {
        commentRepository.findById(commentId)
                .filter(not(CommentV2::isDeleted))
                .ifPresent(comment -> {
                    if (hasChildren(comment)) {
                        comment.delete();
                    } else {
                        delete(comment);
                    }
                });
    }

    private boolean hasChildren(CommentV2 comment) {
        return commentRepository.findDescendantsTopPath(
                comment.getArticleId(),
                comment.getCommentPath().getPath()
        ).isPresent();
    }

    private void delete(CommentV2 comment) {
        commentRepository.delete(comment);
        if (!comment.isRoot()) {
            commentRepository.findByPath(comment.getCommentPath().getParentPath())
                    .filter(CommentV2::isDeleted)
                    .filter(not(this::hasChildren))
                    .ifPresent(this::delete);
        }
    }

    public CommentPageResponse readAll(Long articleId, Long page, Long pageSize) {
        return CommentPageResponse.of(
                commentRepository.findAll(articleId, (page - 1) * pageSize, pageSize).stream()
                        .map(CommentResponse::from)
                        .toList(),
                commentRepository.count(articleId, PageLimitCalculator.calculatePageLimit(page, pageSize, 10L))
        );
    }

    public List<CommentResponse> readAllInfiniteScroll(Long articleId, String lastPath, Long pageSize) {
        List<CommentV2> comments = lastPath == null ?
                commentRepository.findAllInfiniteScroll(articleId, pageSize) :
                commentRepository.findAllInfiniteScroll(articleId, lastPath, pageSize);

        return comments.stream()
                .map(CommentResponse::from)
                .toList();
    }
}

public class CommentApiV2Test {
    RestClient restClient = RestClient.create("http://localhost:9001");

    @Test
    void create() {
        CommentResponse response1 = createComment(new CommentCreateRequestV2(1L, "my comment1", null, 1L));
        CommentResponse response2 = createComment(new CommentCreateRequestV2(1L, "my comment2", response1.getPath(), 1L));
        CommentResponse response3 = createComment(new CommentCreateRequestV2(1L, "my comment3", response2.getPath(), 1L));

        System.out.println("commentId=%s".formatted(response1.getCommentId()));
        System.out.println("\tcommentId=%s".formatted(response2.getCommentId()));
        System.out.println("\t\tcommentId=%s".formatted(response3.getCommentId()));
        // commentId=252238814154854400
        //	 commentId=252238814641393664
        //		commentId=252238814784000000

        System.out.println("commentPath=%s".formatted(response1.getPath()));
        System.out.println("\tcommentPath=%s".formatted(response2.getPath()));
        System.out.println("\t\tcommentPath=%s".formatted(response3.getPath()));
        // commentPath=00000
        //	 commentPath=0000000000
        //		commentPath=000000000000000
    }

    CommentResponse createComment(CommentCreateRequestV2 request) {
        return restClient.post()
                .uri("/v2/comments")
                .body(request)
                .retrieve()
                .body(CommentResponse.class);
    }

    @Test
    void read() {
        CommentResponse response = restClient.get()
                .uri("/v2/comments/{commentId}", 252238814154854400L)
                .retrieve()
                .body(CommentResponse.class);
        System.out.println("response = " + response);
        // response = CommentResponse(commentId=252238814154854400, content=my comment1, parentCommentId=null, path=00000, articleId=1, writerId=1, deleted=false, createdAt=2025-11-27T10:07)
    }

    @Test
    void delete() {
        restClient.delete()
                .uri("/v2/comments/{commentId}", 252238814784000000L)
                .retrieve();
    }

    @AllArgsConstructor
    @Getter
    public static class CommentCreateRequestV2 {
        private Long articleId;
        private String content;
        private String parentPath;
        private Long writerId;
    }

    @Test
    void readAll() {
        CommentPageResponse response = restClient.get()
                .uri("/v2/comments?articleId=1&pageSize=10&page=1")
                .retrieve()
                .body(CommentPageResponse.class);
        System.out.println("response.getCommentCount() = " + response.getCommentCount());
        for (CommentResponse comment : response.getComments()) {
            System.out.println("comment.getCommentId() = " + comment.getCommentId());
        }
    }

    @Test
    void readAllInfiniteScroll() {
        List<CommentResponse> response1 = restClient.get()
                .uri("/v2/comments/infinite-scroll?articleId=1&pageSize=5")
                .retrieve()
                .body(new ParameterizedTypeReference<List<CommentResponse>>() {});
        System.out.println("first Page");
        for (CommentResponse response : response1) {
            System.out.println("response.getCommentId() = " + response.getCommentId());
        }

        String lastPath = response1.getLast().getPath();

        List<CommentResponse> response2 = restClient.get()
                .uri("/v2/comments/infinite-scroll?articleId=1&pageSize=5&lastPath=%s".formatted(lastPath))
                .retrieve()
                .body(new ParameterizedTypeReference<List<CommentResponse>>() {});
        System.out.println("second Page");
        for (CommentResponse response : response2) {
            System.out.println("response.getCommentId() = " + response.getCommentId());
        }
    }
}