본문 바로가기
트러블슈팅과 고민/트러블슈팅

DB의 4500만개의 데이터를 레디스로 옮기기

by 정재익 2026. 2. 28.

 

너무 바빠서 아직 글을 완성하지 못했습니다 ㅠ

제 서비스에는 친구관계와 멤버들끼리의 상호작용 점수가 있습니다. 이것들은 추천친구를 계산하는데 사용되며 레디스에서 관리되고 유사시에 RDB에서 복구할 수 있습니다.
 
친구관계는 레디스에서 Set 상호작용 점수는 ZSET으로 관리됩니다.
 
회원이 10만명이고 회원마다 친구가 300명 있고 회원마다 300명과 상호작용한 상황에서 레디스의 데이터가 전부 사라졌다고 가정하겠습니다.
 
그렇게되면 친구관계는 3000만 레코드 하지만 디비에서는 중복을 허용하지 않기에 1500만 레코드입니다.
 
그리고 상호작용 점수는 누군가의 글과 댓글을 추천하거나 글에 댓글을 달았을 때 작성자 끼리의 상호작용 점수가 추가됩니다.
이 경우 10만명 각각이 300명과 상호작용을 했다고하면 총 3000만 레코드가 됩니다.
 
종합적으로 친구관계 1500만, 글추천 1000만, 댓글추천 1000만, 댓글 1000만 레코드 총 4500만 레코드가 존재합니다.
레디스에서는 친구관계를 중복처리하지않기에 10만개의 Set에 300개의 멤버ID 친구관계에 3000만 데이터가 들어가며 상호작용점수는 10만개의 ZSET에 memberId와 score 300개 총 3000만 모두 더해서 6000만의 데이터가 들어갑니다.
 
 
친구관계 복구 로직
처음에는 각 멤버에 대한 데이터를 뽑는 것보다 어차피 모든 데이터를 스캔해야하기에 순차탐색으로 앞에서부터 1000개씩 가져와서 레디스로 보냈습니다.

public String rebuildFriendshipRedis() {
    redisFriendRestore.deleteAllFriendshipKeys();

    long afterId = 0L;
    long totalPairs = 0L;
    List<long[]> chunk;

    do {
        log.info("DB 친구관계 요청");
        chunk = friendAdminQueryRepository.getFriendshipPairsChunk(afterId, PIPELINE_BATCH_SIZE);
        log.info("DB 친구관계 조회 : {}이후 {}개 조회 완료", afterId, PIPELINE_BATCH_SIZE);
        if (!chunk.isEmpty()) {
            // chunk 원소: [id, memberId, friendId] — Redis에는 [memberId, friendId]만 전달
            List<long[]> pairs = chunk.stream()
                    .map(arr -> new long[]{arr[1], arr[2]})
                    .toList();
            log.info("레디스 친구관계 삽입 시작");
            redisFriendRestore.rebuildBatch(pairs);
            log.info("레디스 친구 관계 삽입 1000개 완료");
            afterId = chunk.getLast()[0];
            totalPairs += chunk.size();
        }
    } while (chunk.size() == PIPELINE_BATCH_SIZE);
    return String.format("친구 관계 Redis 재구축 완료. 처리된 친구 쌍: %d개", totalPairs);
}
public List<long[]> getFriendshipPairsChunk(long afterId, int size) {
    List<long[]> list = jpaQueryFactory
            .select(friendship.id, friendship.member.id, friendship.friend.id)
            .from(friendship)
            .where(friendship.id.gt(afterId))
            .orderBy(friendship.id.asc())
            .limit(size)
            .fetch()
            .stream()
            .map(t -> new long[]{
                    t.get(friendship.id),
                    t.get(friendship.member.id),
                    t.get(friendship.friend.id)
            })
            .toList();
    return list;
}

 

public void rebuildBatch(List<long[]> pairs) {
    if (pairs.isEmpty()) {
        return;
    }
    stringRedisTemplate.executePipelined((RedisCallback<Object>) connection -> {
        for (long[] pair : pairs) {
            byte[] memberKey = createFriendKey(pair[0]).getBytes(StandardCharsets.UTF_8);
            byte[] friendKey = createFriendKey(pair[1]).getBytes(StandardCharsets.UTF_8);
            byte[] memberIdBytes = String.valueOf(pair[0]).getBytes(StandardCharsets.UTF_8);
            byte[] friendIdBytes = String.valueOf(pair[1]).getBytes(StandardCharsets.UTF_8);
            connection.setCommands().sAdd(memberKey, friendIdBytes);
            connection.setCommands().sAdd(friendKey, memberIdBytes);
        }
        return null;
    });
}

 
 
상호작용점수 복구로직

public String rebuildInteractionScoreRedis() {
    redisFriendRestore.deleteAllInteractionKeys();

    long totalRows = 0L;

    log.info("DB 게시글 추천 조회 요청");
    BiFunction<Long, Long, List<long[]>> postLikeFetcher =
            (driveId, joinId) -> friendAdminQueryRepository.getPostLikeInteractionsChunk(driveId, joinId, PIPELINE_BATCH_SIZE);

    log.info("DB 댓글 조회 요청");
    BiFunction<Long, Long, List<long[]>> commentFetcher =
            (driveId, joinId) -> friendAdminQueryRepository.getCommentInteractionsChunk(driveId, joinId, PIPELINE_BATCH_SIZE);

    log.info("DB 댓글 추천 조회 요청");
    BiFunction<Long, Long, List<long[]>> commentLikeFetcher =
            (driveId, joinId) -> friendAdminQueryRepository.getCommentLikeInteractionsChunk(driveId, joinId, PIPELINE_BATCH_SIZE);

    totalRows += streamInteractionToRedis(postLikeFetcher);
    totalRows += streamInteractionToRedis(commentFetcher);
    totalRows += streamInteractionToRedis(commentLikeFetcher);

    return String.format("상호작용 점수 Redis 재구축 완료. 처리된 행: %d개", totalRows);
}

private long streamInteractionToRedis(BiFunction<Long, Long, List<long[]>> fetcher) {
    long afterDriveId = 0L;
    long afterJoinId = 0L;
    long count = 0L;
    List<long[]> chunk;

    do {
        chunk = fetcher.apply(afterDriveId, afterJoinId);
        if (!chunk.isEmpty()) {
            log.info("레디스 상호관계점수 삽입 시작");
            redisFriendRestore.incrementInteractionBatch(chunk);
            log.info("레디스 상호관계점수 삽입 1000개 완료");
            long[] last = chunk.getLast();
            afterDriveId = last[0];
            afterJoinId = last[1];
            count += chunk.size();
        }
    } while (chunk.size() == PIPELINE_BATCH_SIZE);

    return count;
}

 
 

public List<long[]> getPostLikeInteractionsChunk(long afterDriveId, long afterJoinId, int size) {
    List<long[]> list = jpaQueryFactory
            .select(post.id, postLike.id, postLike.member.id, post.member.id)
            .from(post)
            .join(postLike).on(postLike.post.eq(post))
            .where(
                    post.member.id.isNotNull(),
                    postLike.member.id.isNotNull(),
                    postLike.member.id.ne(post.member.id),
                    post.id.gt(afterDriveId)
                            .or(post.id.eq(afterDriveId).and(postLike.id.gt(afterJoinId)))
            )
            .orderBy(post.id.asc(), postLike.id.asc())
            .limit(size)
            .fetch()
            .stream()
            .map(t -> new long[]{
                    t.get(post.id),
                    t.get(postLike.id),
                    t.get(postLike.member.id),
                    t.get(post.member.id)
            })
            .toList();
    log.info("DB 게시글 추천 조회 : {}이후 {}개 조회 완료", afterJoinId, size);
    return list;
}

public List<long[]> getCommentInteractionsChunk(long afterDriveId, long afterJoinId, int size) {
    List<long[]> list = jpaQueryFactory
            .select(post.id, comment.id, comment.member.id, post.member.id)
            .from(post)
            .join(comment).on(comment.post.eq(post))
            .where(
                    post.member.id.isNotNull(),
                    comment.member.id.isNotNull(),
                    comment.member.id.ne(post.member.id),
                    post.id.gt(afterDriveId)
                            .or(post.id.eq(afterDriveId).and(comment.id.gt(afterJoinId)))
            )
            .orderBy(post.id.asc(), comment.id.asc())
            .limit(size)
            .fetch()
            .stream()
            .map(t -> new long[]{
                    t.get(post.id),
                    t.get(comment.id),
                    t.get(comment.member.id),
                    t.get(post.member.id)
            })
            .toList();
    log.info("DB 댓글 조회 : {}이후 {}개 조회 완료", afterJoinId, size);
    return list;
}


public List<long[]> getCommentLikeInteractionsChunk(long afterDriveId, long afterJoinId, int size) {
    List<long[]> list = jpaQueryFactory
            .select(comment.id, commentLike.id, commentLike.member.id, comment.member.id)
            .from(comment)
            .join(commentLike).on(commentLike.comment.eq(comment))
            .where(
                    comment.member.id.isNotNull(),
                    commentLike.member.id.isNotNull(),
                    commentLike.member.id.ne(comment.member.id),
                    comment.id.gt(afterDriveId)
                            .or(comment.id.eq(afterDriveId).and(commentLike.id.gt(afterJoinId)))
            )
            .orderBy(comment.id.asc(), commentLike.id.asc())
            .limit(size)
            .fetch()
            .stream()
            .map(t -> new long[]{
                    t.get(comment.id),
                    t.get(commentLike.id),
                    t.get(commentLike.member.id),
                    t.get(comment.member.id)
            })
            .toList();
    log.info("DB 댓글 추천 조회 : {}이후 {}개 조회 완료", afterJoinId, size);
    return list;
}
public void incrementInteractionBatch(List<long[]> batch) {
    if (batch.isEmpty()) {
        return;
    }
    stringRedisTemplate.executePipelined((RedisCallback<Object>) connection -> {
        for (long[] row : batch) {
            connection.zSetCommands().zIncrBy(
                    createInteractionKey(row[2]).getBytes(StandardCharsets.UTF_8),
                    INTERACTION_SCORE_DEFAULT,
                    String.valueOf(row[3]).getBytes(StandardCharsets.UTF_8));
        }
        return null;
    });
}

 
 
 
 
 
결과
친구관계 삽입에는 20분 9초가 소요되었습니다.

▶ 결과  : 친구 관계 Redis 재구축 완료. 처리된 친구 쌍: 15000000개
▶ 소요  : 1209261ms
▶ Redis : friend:* 키 100,000 개 생성

 

2026-02-28 18:52:38.861 - DB 친구관계 요청
2026-02-28 18:52:38.864 - DB 친구관계 조회 : 22434327이후 1000개 조회 완료
2026-02-28 18:52:38.864 - 레디스 친구관계 삽입 시작
2026-02-28 18:52:38.951 - 레디스 친구 관계 삽입 1000개 완료
2026-02-28 18:52:38.951 - DB 친구관계 요청
2026-02-28 18:52:38.953 - DB 친구관계 조회 : 22435327이후 1000개 조회 완료

시간을 보면 DB는 상당히 빠르지만 레디스에 넣는것이 꽤 오래걸리고 있습니다. 그리고 이것이 순차로 이루어진다는 단점이 있습니다.
 
더 큰 문제는 상호작용 점수입니다.

2026-02-28 18:59:14.608 - DB 게시글 추천 조회 : 16334652이후 1000개 조회 완료
2026-02-28 18:59:14.608 - 레디스 상호관계점수 삽입 시작
2026-02-28 18:59:14.684 - 레디스 상호관계점수 삽입 1000개 완료
2026-02-28 19:02:03.756 - DB 게시글 추천 조회 : 16335652이후 1000개 조회 완료
2026-02-28 19:02:03.756 - 레디스 상호관계점수 삽입 시작
2026-02-28 19:02:03.827 - 레디스 상호관계점수 삽입 1000개 완료
2026-02-28 19:04:52.628 - DB 게시글 추천 조회 : 16336652이후 1000개 조회 완료
2026-02-28 19:04:52.628 - 레디스 상호관계점수 삽입 시작
2026-02-28 19:04:52.702 - 레디스 상호관계점수 삽입 1000개 완료
2026-02-28 19:07:53.646 - DB 게시글 추천 조회 : 16337652이후 1000개 조회 완료
2026-02-28 19:07:53.646 - 레디스 상호관계점수 삽입 시작
2026-02-28 19:07:53.717 - 레디스 상호관계점수 삽입 1000개 완료
2026-02-28 19:10:52.589 - DB 게시글 추천 조회 : 16338652이후 1000개 조회 완료
2026-02-28 19:10:52.589 - 레디스 상호관계점수 삽입 시작
2026-02-28 19:10:52.656 - 레디스 상호관계점수 삽입 1000개 완료

로그 시점을 이상하게 적어놔서 DB요청 시점을 볼 수는 없지만 레디스에 삽입이 완료되고 다시 DB에서 가져오는데 3분이 걸리는 것을 볼 수 있습니다.
1000개에 3분라고 계산하면 3000만개는 62일이 걸립니다.
 
이것을 최적화 해보겠습니다.

[Hibernate] 
    select
        p1_0.post_id,
        pl1_0.post_like_id,
        pl1_0.member_id,
        p1_0.member_id 
    from
        post p1_0 
    join
        post_like pl1_0 
            on pl1_0.post_id=p1_0.post_id 
    where
        p1_0.member_id is not null 
        and pl1_0.member_id is not null 
        and pl1_0.member_id<>p1_0.member_id 
        and (
            p1_0.post_id>? 
            or p1_0.post_id=? 
            and pl1_0.post_like_id>?
        ) 
    order by
        p1_0.post_id,
        pl1_0.post_like_id 
    limit
        ?

이것은 글의 추천을 가져오는 쿼리입니다.
 

-> Limit: 1000 row(s)  (actual time=179631..179631 rows=1000 loops=1)
    -> Sort: p1_0.post_id, pl1_0.post_like_id, limit input to 1000 row(s) per chunk  (actual time=179631..179631 rows=1000 loops=1)
        -> Stream results  (cost=2.29e+6 rows=1.16e+6) (actual time=73.7..178316 rows=10e+6 loops=1)
            -> Nested loop inner join  (cost=2.29e+6 rows=1.16e+6) (actual time=73.7..176930 rows=10e+6 loops=1)
                -> Filter: ((p1_0.member_id is not null) and ((p1_0.post_id > 0) or (p1_0.post_id = 0)))  (cost=10022 rows=24819) (actual time=0.528..457 rows=100000 loops=1)
                    -> Covering index range scan on p1_0 using FK7ky67sgi7k0ayf22652f7763r over (NULL < member_id)  (cost=10022 rows=49638) (actual time=0.525..434 rows=100000 loops=1)
                -> Filter: ((pl1_0.member_id is not null) and (pl1_0.member_id <> p1_0.member_id))  (cost=82.4 rows=46.9) (actual time=1.65..1.76 rows=100 loops=100000)
                    -> Index lookup on pl1_0 using FKj7iy0k7n3d0vkh8o7ibjna884 (post_id=p1_0.post_id), with index condition: ((p1_0.post_id > 0) or ((p1_0.post_id = 0) and (pl1_0.post_like_id > 0)))  (cost=82.4 rows=93.7) (actual time=1.65..1.75 rows=100 loops=100000)

실행계획 입니다.
 
 
친구관계 삽입 리팩토링 20분 -> 39초

2026-03-02 00:09:46.336 - friendship Redis 재구축 시작
2026-03-02 00:09:47.136 - 친구 관계 생산자 : memberId 3811 이후 1000명 처리 완료
2026-03-02 00:09:47.193 - 컨슈머: 33명 Redis 파이프라인 삽입 완료
2026-03-02 00:09:47.501 - 컨슈머: 1000명 Redis 파이프라인 삽입 완료
2026-03-02 00:09:48.134 - 친구 관계 생산자 : memberId 2811 이후 1000명 처리 완료
2026-03-02 00:10:24.971 - 컨슈머: 98121명 Redis 파이프라인 삽입 완료
2026-03-02 00:10:25.087 - 컨슈머: 99000명 Redis 파이프라인 삽입 완료
2026-03-02 00:10:25.349 - 친구 관계 생산자 : memberId 98712 이후 1000명 처리 완료
2026-03-02 00:10:25.350 - 친구관계 프로듀서: POISON_PILL 삽입, 종료
2026-03-02 00:10:25.351 - 컨슈머: 99001명 Redis 파이프라인 삽입 완료
2026-03-02 00:10:25.458 - 컨슈머: 100000명 Redis 파이프라인 삽입 완료
2026-03-02 00:10:25.458 - 컨슈머: 종료. 총 100000명 처리

 
 
상호작용 점수 삽입 리팩토링 65일 -> 6분 15초

2026-03-02 00:16:58.770 [main] INFO  j.b.s.m.p.FriendRedisRebuildPerformanceTest [trace=none user=anonymous ip=] - ║  interaction-score Redis 재구축 시작  ║
2026-03-02 00:16:58.770 [main] INFO  j.b.s.m.p.FriendRedisRebuildPerformanceTest [trace=none user=anonymous ip=] - ╚══════════════════════════════════════╝
2026-03-02 00:16:59.414 [scheduling-1] INFO  j.b.d.p.scheduler.RealTimePostScheduler [trace=none user=anonymous ip=] - 실시간 점수 감쇠 스케줄러 - [RealTimePostScheduler.syncRedisToCaffeine] 시작
2026-03-02 00:16:59.418 [scheduling-1] INFO  j.b.d.p.scheduler.RealTimePostScheduler [trace=none user=anonymous ip=] - [RealTimePostScheduler.syncRedisToCaffeine] 완료 | 실행시간: 4ms
2026-03-02 00:17:03.349 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 3311 이후 500명 처리 완료
2026-03-02 00:23:10.465 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99000명 Redis 파이프라인 삽입 완료
2026-03-02 00:23:10.895 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 98212 이후 500명 처리 완료
2026-03-02 00:23:10.895 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 프로듀서 : 종료
2026-03-02 00:23:10.955 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99226명 Redis 파이프라인 삽입 완료
2026-03-02 00:23:11.035 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99500명 Redis 파이프라인 삽입 완료
2026-03-02 00:23:12.034 [interaction-producer-2] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 98712 이후 500명 처리 완료
2026-03-02 00:23:12.035 [interaction-producer-2] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 프로듀서 : 종료
2026-03-02 00:23:12.035 [interaction-producer-2] INFO  j.b.d.friend.service.FriendAdminService [trace=none user=anonymous ip=] - 상호작용 프로듀서 3개 완료, POISON_PILL 삽입
2026-03-02 00:23:12.050 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99540명 Redis 파이프라인 삽입 완료
2026-03-02 00:23:12.181 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 100000명 Redis 파이프라인 삽입 완료
2026-03-02 00:23:12.181 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 종료. 총 100000명 처리

 
발생쿼리 추가리팩토링 필요 
 

[Hibernate] 
    select
        c1_0.member_id,
        p1_0.member_id,
        count(c1_0.comment_id) 
    from
        comment c1_0 
    join
        post p1_0 
            on p1_0.post_id=c1_0.post_id 
    where
        c1_0.member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
        and p1_0.member_id is not null 
        and c1_0.member_id<>p1_0.member_id 
    group by
        c1_0.member_id,
        p1_0.member_id
[Hibernate] 
    select
        cl1_0.member_id,
        c1_0.member_id,
        count(cl1_0.comment_like_id) 
    from
        comment_like cl1_0 
    join
        comment c1_0 
            on c1_0.comment_id=cl1_0.comment_id 
    where
        cl1_0.member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
        and c1_0.member_id is not null 
        and cl1_0.member_id<>c1_0.member_id 
    group by
        cl1_0.member_id,
        c1_0.member_id
[Hibernate] 
    select
        pl1_0.member_id,
        p1_0.member_id,
        count(pl1_0.post_like_id) 
    from
        post_like pl1_0 
    join
        post p1_0 
            on p1_0.post_id=pl1_0.post_id 
    where
        pl1_0.member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
        and p1_0.member_id is not null 
        and pl1_0.member_id<>p1_0.member_id 
    group by
        pl1_0.member_id,
        p1_0.member_id

 
실행계획 
 
댓글 상호작용

-> Table scan on <temporary>  (actual time=298..303 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=298..298 rows=50000 loops=1)
        -> Nested loop inner join  (cost=109354 rows=26250) (actual time=20..252 rows=50000 loops=1)
            -> Filter: (c1_0.post_id is not null)  (cost=55109 rows=52500) (actual time=20..149 rows=50000 loops=1)
                -> Index range scan on c1_0 using FKqm52p1v3o13hy268he0wcngr5 over (member_id = 1) OR (member_id = 2) OR (498 more), with index condition: (c1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500))  (cost=55109 rows=52500) (actual time=20..146 rows=50000 loops=1)
            -> Filter: ((p1_0.member_id is not null) and (c1_0.member_id <> p1_0.member_id))  (cost=0.933 rows=0.5) (actual time=0.00183..0.00191 rows=1 loops=50000)
                -> Single-row index lookup on p1_0 using PRIMARY (post_id=c1_0.post_id)  (cost=0.933 rows=1) (actual time=0.00168..0.0017 rows=1 loops=50000)

 
댓글 추천 상호작용

-> Table scan on <temporary>  (actual time=1058..1062 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=1058..1058 rows=50000 loops=1)
        -> Nested loop inner join  (cost=106984 rows=25250) (actual time=45.3..1005 rows=50000 loops=1)
            -> Filter: (cl1_0.comment_id is not null)  (cost=54395 rows=50500) (actual time=45.3..628 rows=50000 loops=1)
                -> Index range scan on cl1_0 using FKl5wrmp8eoy5uegdo3473jqqi over (member_id = 1) OR (member_id = 2) OR (498 more), with index condition: (cl1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500))  (cost=54395 rows=50500) (actual time=45.3..624 rows=50000 loops=1)
            -> Filter: ((c1_0.member_id is not null) and (cl1_0.member_id <> c1_0.member_id))  (cost=0.941 rows=0.5) (actual time=0.00731..0.00739 rows=1 loops=50000)
                -> Single-row index lookup on c1_0 using PRIMARY (comment_id=cl1_0.comment_id)  (cost=0.941 rows=1) (actual time=0.00712..0.00715 rows=1 loops=50000)

 
글 추천 상호작용

-> Table scan on <temporary>  (actual time=194..198 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=194..194 rows=50000 loops=1)
        -> Nested loop inner join  (cost=68265 rows=27500) (actual time=0.594..147 rows=50000 loops=1)
            -> Filter: ((pl1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500)) and (pl1_0.post_id is not null))  (cost=11172 rows=55000) (actual time=0.562..49.1 rows=50000 loops=1)
                -> Covering index range scan on pl1_0 using uk_postlike_member_post over (member_id = 1) OR (member_id = 2) OR (498 more)  (cost=11172 rows=55000) (actual time=0.556..41.7 rows=50000 loops=1)
            -> Filter: ((p1_0.member_id is not null) and (pl1_0.member_id <> p1_0.member_id))  (cost=0.938 rows=0.5) (actual time=0.00175..0.00184 rows=1 loops=50000)
                -> Single-row index lookup on p1_0 using PRIMARY (post_id=pl1_0.post_id)  (cost=0.938 rows=1) (actual time=0.00159..0.00162 rows=1 loops=50000)

 
상호작용 점수 리팩토링
 
실행계획 
 
댓글 상호작용

-> Table scan on <temporary>  (actual time=221..225 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=221..221 rows=50000 loops=1)
        -> Nested loop inner join  (cost=65111 rows=25000) (actual time=1.42..152 rows=50000 loops=1)
            -> Filter: ((c1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500)) and (c1_0.post_id is not null))  (cost=10159 rows=50000) (actual time=0.912..67 rows=50000 loops=1)
                -> Covering index range scan on c1_0 using idx_comment_member_post over (member_id = 1) OR (member_id = 2) OR (498 more)  (cost=10159 rows=50000) (actual time=0.907..58.9 rows=50000 loops=1)
            -> Filter: ((p1_0.member_id is not null) and (c1_0.member_id <> p1_0.member_id))  (cost=0.999 rows=0.5) (actual time=0.00146..0.00154 rows=1 loops=50000)
                -> Single-row index lookup on p1_0 using PRIMARY (post_id=c1_0.post_id)  (cost=0.999 rows=1) (actual time=0.00128..0.00131 rows=1 loops=50000)

 
 
댓글 추천 상호작용

-> Table scan on <temporary>  (actual time=305..309 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=305..305 rows=50000 loops=1)
        -> Nested loop inner join  (cost=60145 rows=24750) (actual time=0.39..248 rows=50000 loops=1)
            -> Filter: ((cl1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500)) and (cl1_0.comment_id is not null))  (cost=10058 rows=49500) (actual time=0.357..39.7 rows=50000 loops=1)
                -> Covering index range scan on cl1_0 using uk_comment_like_member_comment over (member_id = 1) OR (member_id = 2) OR (498 more)  (cost=10058 rows=49500) (actual time=0.351..32.4 rows=50000 loops=1)
            -> Filter: ((c1_0.member_id is not null) and (cl1_0.member_id <> c1_0.member_id))  (cost=0.912 rows=0.5) (actual time=0.00394..0.00402 rows=1 loops=50000)
                -> Single-row index lookup on c1_0 using PRIMARY (comment_id=cl1_0.comment_id)  (cost=0.912 rows=1) (actual time=0.00377..0.0038 rows=1 loops=50000)

 
글 추천 상호작용

-> Table scan on <temporary>  (actual time=252..256 rows=50000 loops=1)
    -> Aggregate using temporary table  (actual time=252..252 rows=50000 loops=1)
        -> Nested loop inner join  (cost=40914 rows=25000) (actual time=0.0783..202 rows=50000 loops=1)
            -> Filter: ((pl1_0.member_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500)) and (pl1_0.post_id is not null))  (cost=10156 rows=50000) (actual time=0.0572..33 rows=50000 loops=1)
                -> Covering index range scan on pl1_0 using uk_postlike_member_post over (member_id = 1) OR (member_id = 2) OR (498 more)  (cost=10156 rows=50000) (actual time=0.0541..25.4 rows=50000 loops=1)
            -> Filter: ((p1_0.member_id is not null) and (pl1_0.member_id <> p1_0.member_id))  (cost=0.515 rows=0.5) (actual time=0.00317..0.00325 rows=1 loops=50000)
                -> Single-row index lookup on p1_0 using PRIMARY (post_id=pl1_0.post_id)  (cost=0.515 rows=1) (actual time=0.003..0.00303 rows=1 loops=50000)

 
2분 33초

2026-03-02 14:16:16.311 [interaction-producer-5] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 2811 이후 500명 처리 완료
2026-03-02 14:16:16.315 [interaction-producer-3] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 2311 이후 500명 처리 완료
2026-03-02 14:16:16.334 [interaction-producer-2] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 3311 이후 500명 처리 완료
2026-03-02 14:16:16.346 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 34명 Redis 파이프라인 삽입 완료
2026-03-02 14:16:16.388 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 3811 이후 500명 처리 완료
2026-03-02 14:18:47.905 [interaction-producer-2] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 프로듀서 : 종료
2026-03-02 14:18:47.922 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 98563명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:48.048 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99000명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:48.253 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 98212 이후 500명 처리 완료
2026-03-02 14:18:48.254 [interaction-producer-1] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 프로듀서 : 종료
2026-03-02 14:18:48.268 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99043명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:48.398 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99500명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:49.113 [interaction-producer-4] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 생산자 : memberId 98712 이후 500명 처리 완료
2026-03-02 14:18:49.113 [interaction-producer-4] INFO  j.b.d.friend.async.FriendRebuildProducer [trace=none user=anonymous ip=] - 상호작용 프로듀서 : 종료
2026-03-02 14:18:49.113 [interaction-producer-4] INFO  j.b.d.friend.service.FriendAdminService [trace=none user=anonymous ip=] - 상호작용 프로듀서 5개 완료, POISON_PILL 삽입
2026-03-02 14:18:49.132 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 99561명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:49.255 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 100000명 Redis 파이프라인 삽입 완료
2026-03-02 14:18:49.255 [interaction-consumer-1] INFO  j.b.d.friend.async.FriendRebuildConsumer [trace=none user=anonymous ip=] - 컨슈머(상호작용): 종료. 총 100000명 처리

 
도합 3분 12초

'트러블슈팅과 고민 > 트러블슈팅' 카테고리의 다른 글

Single Flight 운영 중 스레드 풀 포화 비율 50% 식별, Soft TTL 전환으로 5배 개선  (0) 2026.04.25
레디스 파이프라인으로 인한 Netty 태스크 큐 병목 식별 및 해결 후 Spring Data Redis 기여  (0) 2026.03.09
게시글 목록 조회 API 응답시간 200ms 이내 TPS 12.3배 개선  (0) 2026.02.03
레디스 장애 시 서킷브레이커와 로컬 캐시를 활용한 실시간 인기글 API 개선  (0) 2026.01.30
게시판 조회 쿼리 개선  (1) 2025.12.31

관련글

티스토리툴바