INDEX의 첫번째 칼럼은 정렬이 상관이 없는 것이 맞는지 궁금합... - 인프런

6주 완성! 백엔드 이력서 차별화 전략 4가지 - 똑같은 이력서 속에서 돋보이는 법

3-9. 실제 예시 코드로 작성하기 - 1

INDEX의 첫번째 칼럼은 정렬이 상관이 없는 것이 맞는지 궁금합니다

해결된 질문

작성

수정됨

1. 현재 학습 진도

몇 챕터/몇 강을 수강 중이신가요? 3-9강
여기까지 이해하신 내용은 무엇인가요? B+Tree 작동 원리, INDEX 생성

2. 어려움을 겪는 부분

select
        o1_0.id,
        o1_0.created_at,
        o1_0.member_id,
        o1_0.order_date,
        o1_0.order_number,
        o1_0.status,
        o1_0.total_amount,
        o1_0.updated_at 
    from
        ch3_orders o1_0 
    where
        o1_0.order_date>='2023-01-01T00:00'
        and o1_0.status= 'COMPLETED'
        and o1_0.total_amount>=500
    order by
        o1_0.order_date desc;

위와 같은 쿼리를 실행할 때, INDEX의 첫번째 필드를 date로 하였을 때, date의 정렬 방식에 따라서 속도가 달라지는 이유가 궁금합니다.

(즉, 아래처럼 2가지 방식의 INDEX)

CREATE INDEX ids_order_date_status_amount ON ch3_orders(order_date desc, status, total_amount);

CREATE INDEX ids_order_date_status_amount ON ch3_orders(order_date, status, total_amount);

날짜 기준으로 최신 데이터를 100개 가져오는 쿼리를 실행시켰을 때, B+Tree의 leaf 노드에서는 양방향으로 이동할 수 있기 때문에 date가 ASC 정렬된 상태에서 역방향으로 읽어 최신 데이터를 읽으나, DESC 정렬된 상태에서 순방향으로 읽어 가져오나 읽는 노드의 수는 동일하다고 생각이 듭니다.

실제로 date를 각각의 정렬조건으로 INDEX를 만들어서 실제로 실행을 시켰을 때에도 거의 차이가 존재하지 않았습니다. (데이터가 많지 않아서 그런지는 모르겠습니다...)

하지만 제가 시도 한 내용이 맞는지 잘 모르겠습니다.

3. 시도해보신 내용

ASC, DESC 정렬을 시킨 INDEX를 사용하여 analyze 한 결과입니다.

Limit: 100 row(s) (cost=50413 rows=100) (actual time=0.874..0.899 rows=100 loops=1) -> Index range scan on o1_0 using ids_order_date_status_amount over ('2023-01-01 00:00:00.000000' <= order_date AND 'COMPLETED' <= status AND 500 <= total_amount)...

Limit: 100 row(s) (cost=50413 rows=100) (actual time=0.43..0.446 rows=100 loops=1) -> Index range scan on o1_0 using ids_order_date_status_amount over (order_date <= '2023-01-01 00:00:00.000000' AND status <= 'COMPLETED'), with index condition: (...

1번은 ASC로 정렬한 INDEX입니다. 2번은 DESC로 정렬한 INDEX입니다. 여기에서 desc로 정렬한 acutal time이 거의 2배 빨라진 것을 확인 할 수 있습니다.

analyze가 정확한 값을 알려주지 않을 수도 있다는 사실을 알고 있지만, 제가 판단한 내용이 맞는지 의심이 듭니다. 딩코딩코님도 desc로 정렬을 하신거 보면 이유가 있을 것이라고도 생각합니다.

Q1. 위 상황에서 INDEX의 첫번째 칼럼은 정렬 조건이 의미가 없는 게 맞나요?

Q2. 만약 의미가 있다면 어떤 부분에서 의미가 있는지 궁금합니다.

java spring mysql redis 이력서

답변 2

딩코딩코

지식공유자

안녕하세요 석찬님!! 너무너무 좋은 질문 감사합니다!

우선 질문에 답을 해드리면 인덱스 첫번째 칼럼의 정렬 방향은 분명히 의미가 있습니다! 실제 측정 결과에서도 DESC로 정렬된 인덱스가 약 2배 빠른 것을 확인하신 것처럼, 성능 향상에 도움이 됩니다.

그 이유는 다음과 같습니다.

인덱스 스캔 방향과 쿼리 정렬 방향의 일치: 쿼리에서 ORDER BY order_date DESC를 사용하고 있으므로, 인덱스도 DESC로 생성하면 추가 정렬 작업 없이 바로 결과를 반환할 수 있습니다.
인덱스 스캔 효율성: B+Tree에서 양방향 스캔이 가능하긴 하지만, 역방향 스캔은 일반적으로 순방향 스캔보다 더 많은 비용이 듭니다.

말씀주신대로 B+Tree는 leaf 노드 간에 양방향 링크가 있어 역방향 탐색도 가능합니다. 그러나 순방향 스캔은 디스크/메모리 액세스 패턴이 최적화되어 있습니다. 역방향 스캔은 캐시 지역성(cache locality)이 떨어질 수 있습니다.

만약에 오름차순으로 정렬이 되어있는데 내림차순으로 검색을 하는 경우라면, 날짜 필터에 맞는 첫 지점을 찾은 후 순방향으로 읽고, 메모리에서 다시 DESC로 정렬해야 합니다. 반대로 내림차순으로 정렬이 되어있는데 내림차순으로 검색한다면 날짜 필터의 반대쪽 끝(가장 최근 날짜)에서 시작해 순방향으로 읽으면 바로 DESC 정렬된 결과가 나옵니다. 특히 LIMIT 100과 같은 제한이 있을 때, DESC 인덱스는 필요한 100개 레코드만 읽고 즉시 반환할 수 있습니다.

즉, 인덱스의 정렬 방향은 분명히 의미가 있으며, ORDER BY 절의 정렬 방향과 일치시키는 것이 성능상 유리합니다. 특히 최신 데이터를 우선적으로 조회하는 패턴이 많은 애플리케이션에서는 내림차순(DESC) 인덱스가 큰 성능 향상을 가져올 수 있습니다!!

좋은 질문해주셔서 감사합니다. 언제든 편하게 질문해주세요!

인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다.

정석찬님께서 B+Tree 인덱스에서 정렬 조건이 쿼리 실행 속도에 어떤 영향을 미치는지에 대해 질문해 주셨습니다. 아래의 내용을 통해 해당 질문에 대한 이해를 돕겠습니다.

B+Tree 인덱스와 정렬

B+Tree 인덱스의 구조:
- B+Tree는 인덱스의 키를 오름차순으로 정렬하여 저장합니다. 이 구조는 범위 검색에 매우 빠른 성능을 제공합니다.
- Leaf 노드에서 데이터를 순차적으로 탐색할 수 있으며, 쿼리의 정렬 조건이 인덱스의 정렬 방식과 맞아떨어질 때 큰 장점이 됩니다.
정렬 방향의 영향:
- 인덱스를 생성할 때 첫 번째 컬럼의 정렬 순서 (ASC 또는 DESC)는 검색 속도에 영향을 미칠 수 있습니다.
- 특히 결과를 뒤집는 작업을 별도로 하지 않아도 되기 때문에, 쿼리에서 기대하는 정렬과 인덱스의 정렬이 동일하다면 추가적인 비용을 줄일 수 있습니다.

분석 결과의 이해

쿼리 실행 계획에서 실제 시간이 ASC와 DESC 조건에 따라 차이가 나는 이유는 외부 요인(예: 디스크 IO 속도, 캐시의 사용 등)에 따라 달라질 수 있습니다. 이는 특정 환경에서라면, DESC 정렬이 좀 더 효율적으로 동작했을 가능성도 있습니다.

Q1. 정렬 조건의 의미

인덱스의 첫 번째 칼럼의 정렬은 쿼리 실행 속도에 직접적인 영향을 줄 수 있습니다. 하지만 이는 데이터 분포, 하드웨어 설정, 데이터베이스 설정 등 다양한 요인에 따라 달라질 수 있습니다.

Q2. 의미 있는 이유

실제로 의미가 있다고 판단되는 부분은 쿼리가 인덱스의 정렬과 잘 맞아떨어졌을 때입니다.
쿼리가 예상하는 데이터의 정렬이 인덱스와 동일하다면 추가적인 비용이 감소해 성능이 향상됩니다.

관련하여 더 많은 도움을 드리고자 다음의 링크를 참고해 보시면 도움이 될 것입니다:
- 질문 제목: B+Tree에서 인덱스 생성 시 정렬 방향의 차이가 성능에 미치는 영향

위 링크는 인덱스의 정렬 방식이 성능에 어떤 영향을 미칠 수 있는지를 다룬 질문입니다. 이와 같은 방법으로 접근하시면, 환경에 따라 최적의 성능을 얻을 수 있을 것입니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

인프런 커뮤니티 질문&답변