<div data-type="callout" data-emoji="👀" data-background-color="rgba(235, 236, 237, 0.3)" style="background-color: rgba(235, 236, 237, 0.3);">SQL 성능 최적화, 이번 기회에 제대로 공부하고 싶다면!SQL 성능 최적화, 데이터 모델링 뿐만 아니라 LTR 분석 등 다양한 분석 프레임워크까지 배울 수 있는 <a href="https://datarian.io/bootcamp/sql-master" target="_blank">SQL 마스터반</a> 커리큘럼을 살펴보세요. 쿠팡, 리디, 잡플래닛 출신 데이터 분석가들이 실무에 꼭 필요한 내용만 골라 담았어요.</div>인덱스는 쿼리에서 반환할 행을 찾는 기능을 외에, 데이터를 정렬하는 기능을 제공하기도 합니다. 인덱스를 통해 별도의 정렬 연산 없이도 <code>ORDER BY</code>가 동작한 것처럼 쿼리 결과를 만들 수 있습니다. 현재 PostgreSQL에서 지원하는 인덱스 타입 중 B-Tree만 정렬된 출력을 생성할 수 있으며, 다른 인덱스 타입은 구현 의존적인 순서로 반환합니다. (*역자주: 쉽게 말하면 B-Tree 인덱스 이외에는 행의 순서가 제멋대로 출력된다는 뜻입니다.)플래너는 <code>ORDER BY</code>가 들어간 쿼리의 실행 계획을 세울 때 두 가지 옵션을 고려합니다. <ol><li>한가지는 <code>ORDER BY</code>에 명시된 정렬 조건과 일치하는 인덱스를 스캔하는 것이고</li><li>다른 하나는 테이블을 순서대로 스캔하고 따로 정렬 연산을 하는 것입니다.</li></ol>테이블의 많은 부분을 스캔해야 하는 쿼리의 경우, 두 번째 방법이 빠를 수 있습니다. 순서대로 스캔하는 것이 인덱스를 사용하는 것보다 더 적은 디스크 I/O를 발생시키기 때문입니다. 인덱스는 몇 개의 행만 가져와야 할 때 더 유용합니다. <code>ORDER BY</code>와 <code>LIMIT</code>를 함께 사용하는 특수한 경우에는 첫 번째 방법이 더 효율적입니다. 첫 번째 방법은 상위 <code>n</code>개의 행을 찾기 위해 전체 데이터를 정렬해야 하지만, <code>ORDER BY</code> 조건과 매칭되는 인덱스를 사용하면 상위 <code>n</code> 행을 바로 뽑아낼 수 있기 때문입니다.기본적으로 B-Tree 인덱스는 항목을 오름차순으로 저장하며 null을 마지막에 둡니다. 즉, <code>x</code> 열에 있는 인덱스를 정방향으로 스캔하면 <code>ORDER BY x</code>(더 자세히 말하면 <code>ORDER BY x ASC NULLS LAST</code>)를 만족하는 출력이 생성됩니다. 인덱스를 역방향으로 스캔하여 <code>ORDER BY x DESC</code>를 만족하는 출력을 생성할 수도 있습니다(더 상세하게는 <code>ORDER BY x DESC NULLS FIRST</code>, <code>NULLS FIRST</code>가 <code>ORDER BY DESC</code>의 기본값입니다).아래 예시처럼, 인덱스를 만들 때 <code>ASC</code>, <code>DESC</code>, <code>NULLS FIRST</code> 및/또는 <code>NULLS LAST</code> 옵션을 포함하여 B-Tree 인덱스의 순서를 조정할 수도 있습니다.<pre><code class="language-sql">CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);</code></pre><code>NULLS FIRST</code> 옵션과 함께 오름차순으로 저장된 인덱스는 스캔하는 방향에 따라 <code>ORDER BY x ASC NULLS FIRST</code> 또는 <code>ORDER BY x DESC NULLS LAST</code>를 만족할 수도 있습니다.두 가지 옵션과 역방향 스캔 가능성을 함께 사용하면 <code>ORDER BY</code>의 모든 변형을 포괄할 수 있는데 왜 네 가지 옵션을 모두 제공해야 하는지 궁금할 수 있습니다. 이 방식이 멀티 컬럼 인덱스에서는 유용할 수 있습니다. <code>(x, y)</code>에 대한 두 개의 열 인덱스를 예로 들어보면, 앞으로 스캔하는 경우 <code>ORDER BY x, y</code>를 충족할 수 있고 뒤로 스캔하는 경우 <code>ORDER BY x DESC, y DESC</code>를 충족할 수 있습니다. 그러나 애플리케이션에서 <code>ORDER BY x ASC, y DESC</code>를 자주 사용해야 할 수도 있습니다. 일반 인덱스에서는 이러한 순서를 얻을 수 있는 방법이 없지만, 인덱스가 <code>(x ASC, y DESC)</code> 또는 <code>(x DESC, y ASC)</code>로 정의되어 있으면 가능합니다.물론 기본 정렬 순서가 아닌 인덱스는 상당히 특수한 기능이지만 특정 쿼리에 대해 엄청난 속도 향상을 가져올 수 있습니다. 이러한 인덱스를 유지할 가치가 있는지 여부는 특별한 정렬 순서가 필요한 쿼리를 얼마나 자주 사용하는지에 달려있습니다.<div data-type="callout" data-emoji="✍🏻">Editor 선미’s comment이 글의 원문은 PostgreSQL 공식문서 “<a href="https://www.postgresql.org/docs/16/indexes-ordering.html" target="_blank">Chapter 11. Indexes - 11.4. Indexes and </a><a href="https://www.postgresql.org/docs/16/indexes-ordering.html" target="_blank"><code>ORDER BY</code></a>”입니다. PostgreSQL 버전 16을 기준으로 작성되었습니다.데이터의 정렬에도 도움이 된다니, 인덱스는 참 여러모로 유용한 기능이지요. B-Tree 알고리즘이 트리에 키를 정렬된 상태로 저장하기 때문입니다. 아래에 예시로 B-Tree를 첨부했습니다. 왼쪽 가지에 연결된 자식 노드의 값보다 키의 값이 크고, 오른쪽 가지에 연결된 자식 노드의 값보다는 키가 작지요. 각 노드에 여러 키가 들어가는 경우에도 키가 정렬되어 있고요. B+Tree는 잎 노드에 모든 키가 다 들어있기 때문에 좀 더 정렬된 값을 쉽게 파악할 수 있습니다.<figure data-type="image-block" data-src="https://attachments.datarian.io/production-d5f8e171-1932-4279-9104-4ff9834ca501-TLF0wtT7LvhLlUJApyrY06D2b5vMEQ5e0cXkODjz7ug.png" data-align="center" data-width="432" style="width: 432px; max-width: 100%"><img src="https://attachments.datarian.io/production-d5f8e171-1932-4279-9104-4ff9834ca501-TLF0wtT7LvhLlUJApyrY06D2b5vMEQ5e0cXkODjz7ug.png" width="432" /></figure>정렬은 다른 타입의 인덱스에는 해당하지 않는 기능입니다. 예를 들어, Hash 알고리즘만 보더라도 데이터를 정렬에는 도움이 안됩니다. 심지어 Hash 인덱스는 <code>=</code> 이외의 다른 연산자(<code>&gt;</code>, <code>&lt;</code> 등)를 지원하지도 않죠. B-Tree 인덱스가 가장 범용적으로 사용되는데에는 다 이유가 있는 것 같습니다.이 번역 작업이 여러분이 인덱스에 대해 공부하는데 도움이 되었으면 좋겠습니다. PostgreSQL 공식 문서가 인덱스에 대해 잘 쓰여져 있어 이전부터 번역을 꼭 해오고 싶었는데요. 흔쾌히 허가해주신 PostgreSQL CoC(Code of Conduct)팀 감사합니다. 이해를 돕기 위해 역자의 의역이 섞여있으니 만약 번역과 원문 간에 차이가 있는 경우 원문을 우선적으로 생각해주세요. 번역 오류는 <a href="mailto:contact@datarian.io" target="_blank">contact@datarian.io</a>로 제보해주세요.</div>

쿼리의 효율적인 실행에 대해 고민하고 있다면 지나칠 수 없는 토픽이 바로 인덱스입니다

인덱스 개요

대부분의 데이터베이스 인덱스는 B-Tree 또는 개선된 버전인 B+Tree로 구현이 되어있습니다.

인덱스 타입: B-Tree, Hash

멀티 컬럼 인덱스는 자주 사용하지 않는 것이 좋습니다. 대부분의 상황에서 단일 컬럼에 대한 인덱스로 충분합니다.

멀티 컬럼 인덱스

인덱스는 쿼리에서 반환할 행을 찾는 기능을 외에 데이터를 정렬하는 기능을 제공하기도 합니다.

인덱스와 ORDER BY

멀티 컬럼 인덱스와 인덱스 조합 기능을 잘 구분하여 이해해주세요!

인덱스 조합 기능 (Combining Multiple Indexes)

고유성 제약이 걸려있는 컬럼과 기본 키의 경우, 생성 시 자동으로 B-Tree 인덱스가 만들어집니다.

고유 인덱스 (Unique Indexes)

함수 연산의 결과로도 인덱스를 정의할 수 있습니다.

연산 결과에 인덱스 걸기 (Indexes on Expressions)

매우 드문 상황에 처해있지 않은 이상 일반 일덱스를 사용하고 쿼리 플래너를 믿읍시다. 그러려고 DBMS를 사용하는 것이니까요.

부분 인덱스 (Partial Indexes)

EXPLAIN 명령을 사용하면 플래너가 어떤 쿼리 계획을 생성하는지 확인할 수 있습니다.

인덱스와 ORDER BY

내 쿼리가 왜 느린지 궁금했던 적 있다면?

Contents

데이터 분석 소식을
매월 받아보세요

🐘 PostgreSQL 공식 문서 번역 모아보기

함께 읽어보면 좋은 글

SQL 코딩테스트 준비하기: 준비 방법과 난이도

ERD 어떻게 읽어야 하나요?

데이터 분석 프로젝트를 위한 3가지 도구 활용법

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요

인덱스와 ORDER BY

내 쿼리가 왜 느린지 궁금했던 적 있다면?

Contents

데이터 분석 소식을 매월 받아보세요

🐘 PostgreSQL 공식 문서 번역 모아보기

함께 읽어보면 좋은 글

SQL 코딩테스트 준비하기: 준비 방법과 난이도

ERD 어떻게 읽어야 하나요?

데이터 분석 프로젝트를 위한 3가지 도구 활용법

데이터 분석 소식을 매월 받아보세요데이터 분석 소식을 받아보세요

데이터 분석 소식을
매월 받아보세요

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요