댓글 목록 조회 - 튜플 비교 시 쿼리 성능 저하

안녕하세요, 먼저 소중한 강의 만들어주셔서 너무 감사드립니다! 🙏🏻

댓글 목록 조회 쿼리에서 궁금한 점이 있어 질문드립니다.

( 댓글 최대 2 depth - 목록 API 설계 7:19 부분 )

질문

"튜플 비교 (a, b) > (x, y)를 사용하면 인덱스 풀 스캔이 발생하여 성능이 매우 떨어지는데, 명시적 조건 a > x OR (a = x AND b > y) 으로 분리하면 인덱스 레인지 스캔이 발생하여 쿼리 성능이 매우 빨라지는데 왜 그런 것일지 모르겠습니다.."

질문 상세

• 테스트 환경: comment 에 약 8백만건의 테스트 데이터 삽입

• mysql base image: mysql:8.0.38

• comment table ddl

-- auto-generated definition
create table comment
(
    comment_id        bigint        not null
        primary key,
    content           varchar(3000) not null,
    article_id        bigint        not null,
    parent_comment_id bigint        not null,
    writer_id         bigint        not null,
    is_deleted        tinyint(1)    not null,
    created_at        datetime      not null
);

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

문제가 되는 테스트 케이스 (1: slow, 2: fast)

case 1. tuple comparision (slow)

explain analyze
select comment.comment_id,
       comment.parent_comment_id,
       comment.content,
       comment.article_id,
       comment.writer_id,
       comment.content,
       comment.is_deleted,
       comment.created_at
from comment
where article_id = 1
  and (parent_comment_id, comment_id) > (142539921307124354, 142539921307124350)
order by parent_comment_id, comment_id
limit 30;

--
-> Limit: 30 row(s)  (cost=542979 rows=30) (actual time=8620..8620 rows=30 loops=1)
    -> Filter: ((`comment`.comment_id,`comment`.parent_comment_id) > (142539921307124354,142539921307124350))  (cost=542979 rows=4.01e+6) (actual time=8620..8620 rows=30 loops=1)
        -> Index lookup on comment using idx_article_id_parent_comment_id_comment_id (article_id=1)  (cost=542979 rows=4.01e+6) (actual time=1.83..8251 rows=8e+6 loops=1)

case 2. fast

explain analyze
select comment.comment_id,
       comment.parent_comment_id,
       comment.content,
       comment.article_id,
       comment.writer_id,
       comment.content,
       comment.is_deleted,
       comment.created_at
from comment
where article_id = 1
  and (
    parent_comment_id > 142539921307124354
        or
    (parent_comment_id = 142539921307124354 and comment_id > 142539921307124350)
    )
order by parent_comment_id, comment_id
limit 30;

--
-> Limit: 30 row(s)  (cost=416 rows=30) (actual time=0.252..0.727 rows=30 loops=1)
    -> Index range scan on comment using idx_article_id_parent_comment_id_comment_id over (article_id = 1 AND parent_comment_id = 142539921307124354 AND 142539921307124350 < comment_id) OR (article_id = 1 AND 142539921307124354 < parent_comment_id), with index condition: ((`comment`.article_id = 1) and ((`comment`.parent_comment_id > 142539921307124354) or ((`comment`.parent_comment_id = 142539921307124354) and (`comment`.comment_id > 142539921307124350))))  (cost=416 rows=358) (actual time=0.232..0.705 rows=30 loops=1)

튜플 비교를 사용한 1번 쿼리에서는 index full scan 이 발생하여 ( 8백만개의 row 를 모두 스캔 ) 8초의 안좋은 쿼리 성능이 나타난 것으로 판단했습니다.

반면 튜플 비교를 명시적 조건으로 분리한 2번 쿼리에서는,

• (a > X OR (a = X AND b > Y)))

index range scan 을 통해 0.7초 이하의 빠른 쿼리 성능이 나타난 것 같아요.

요약

• 튜플 비교 (a, b) > (x, y)를 사용한 1번 쿼리에서 MySQL 옵티마이저는 왜 풀 인덱스 스캔을 선택하는 것인지,

  튜플 비교가 인덱스 레인지 스캔으로 최적화되지 않는 이유가 무엇인지 원인을 찾고 있는데 잘 모르겠네요.. 힌트를 받을 수 있을까요?

새해복 많이 받으세요!