Search

INDEX 개념잡기

본 글은, 오프라인 강의인 StartUP Tuning For PostgreSQL을 책으로 펼치기 위해 정리 중인 글입니다.

INDEX란?

인덱스(Index)는 테이블에서 원하는 데이터를 빠르게 찾을 수 있도록 미리 만들어 놓은 별도의 객체(Object)다.
인덱스는 SQL 성능을 위해 가장 기본적이면서도 강력한 무기다. 잘 만들어진 인덱스는 SQL 성능에 도움이 되지만, 잘 못 만들어진 인덱스는 SQL 성능을 끌어내릴 수도 있다. 최적의 인덱스를 만들려면 아래와 같은 능력이 필요하다.
•
인덱스의 물리적인 구조를 이해
•
만들어진 인덱스가 어떻게 사용될지 예측할 수 있는 능력
•
조인의 내부적인 처리 알고리즘의 이해
•
실전에서 인덱스를 만들어보고 적용해 본 경험
•
복잡한 SQL을 분해해서 이해할 수 있는 능력
◦
이 능력은 많은 사람들이 가장 어려워 하는 능력이다.
◦
이 능력을 갖추기 위해서는 길고 복잡한 SQL을 계속해서 작성해 볼 필요가 있다.
인덱스를 완벽히 이해하기 위해서는 이 책 외에도 다른 책들도 같이 살펴보며 공부할 필요가 있다. 개인적으로 조시형님의 ‘친절한 SQL 튜닝’을 추천한다. 이 책에서 인덱스를 공부하는 이유는, 인덱스를 효율적으로 사용할 수 있는 SQL을 만들기 위해서다. 인덱스를 만들기 위해 인덱스를 공부하는 것이 아니다.
인덱스가 SQL 성능에 진짜 도움이 되는가를 먼저 알아보자. 인덱스의 개념을 잡는데 도움이 될 것이다.

어느 Operation이 문제인가?

아래 SQL을 실행해 실행계획을 살펴보자.
EXPLAIN (ANALYZE,COSTS OFF) SELECT t1.mbr_id ,count(*) cnt FROM tr_ord_big t1 WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD') AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD') GROUP BY t1.mbr_id;
SQL
복사
위 SQL의 실행계획을 그림으로 살펴보면 다음과 같다.
실행계획의 가장 아래에 표시된 Execution Time이 17813.719 ms다. 무려 17.8초가 걸린 SQL이다. 어째서 17초나 걸렸는지 실행계획을 통해 찾아보자. 실행계획의 처리 흐름은 기본적으로 자식(child)에서 부모쪽으로 처리된다. 앞에서도 이야기했지만, 이러한 처리 흐름이 자식이 완료된 후에 부모가 처리된다는 것을 의미하지는 않는다. 오퍼레이션에 따라 자식이 완료된 후에 부모가 처리되기도 하며, 자식과 부모를 교대로 오가면서 처리되기도 한다. 어쨋든, 기본적인 처리 흐름은 자식에서 부모쪽으로 진행된다. 그러므로, 위 실행계획은 (A)→(B)→(C) 의 흐름으로 오퍼레이션이 처리된다. 오퍼레이션의 순서에 따라 actual 수치를 정리해보면 다음과 같다.
•
(A) Seq Scan on tr_ord_big t1
◦
(actual time=6.359..17802.762 rows=6696 loops=1)
◦
(A) 오퍼레이션의 수행시간 = 17802.762 - 6.359 = 17813.719 ms
•
(B) Sort
◦
(actual time=17811.243..17811.592 rows=6696 loops=1)
◦
(B) 오퍼레이션의 수행시간 = 17811.592 - 17811.243 = 0.349 ms
•
(C) GroupAggregate
◦
(actual time=17811.254..17813.471 rows=5808 loops=1)
◦
(C) 오퍼레이션의 수행시간 = 17813.471 - 17811.254 = 2.217 ms
실행계획에서 가장 먼저 처리된 (A) 오퍼레이션의 수행시간이 17813.719ms다. 전체 처리 시간인 17.8초만큼 (A) 오퍼레이션이 차지하고 있다. 다시 말해, 이 SQL의 성능 대부분은 (A) 오퍼레이션에서 발생하고 있다. 문제의 부분을 찾았으니, 이제 문제를 해결하면 SQL은 빨라 질 수 있다. 여기서는 문제점을 찾기 위해, 가장 먼저 처리되는 (A) 오퍼레이션을 먼저 살펴봤다 .SQL 튜닝을 위해 이처럼 가장 먼저 처리되는 오퍼레이션을 살펴봐야 하는 것은 아니다. 실행계획이 길고 복잡하면, 가장 늦게 처리되는 부모부터 찾아 내려가는 것이 효율적인 경우도 있다.

Seq Scan

앞에서 실행한 SQL의 대부분 실행 시간이 (A) 오퍼레이션에서 발생한다는 사실을 실행계획을 통해 알 수 있었다. (A)에는 Seq Scan이란 오퍼레이션이 적혀져 있다. Seq Scan을 이해해야 해당 오퍼레이션의 성능 문제를 해결할 수 있다. Seq Scan은 쉽게 말해 TABLE FULL SCAN이다. 필요한 데이터를 찾기 위해 테이블의 모든 데이터를 처음부터 끝까지 스캔하는 작업이다. 그림으로 표현해보면 다음과 같다.
실행계획에서 (A) 오퍼레이션의 Seq Scan 내용을 다시 살펴보면 다음과 같다.
•
Seq Scan on tr_ord_big t1 (actual time=6.359..17802.762 rows=6696 loops=1)
◦
Filter: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND …생략…
◦
Rows Removed by Filter: 26285076
실행계획에는 Seq Scan과 관련된 다양한 정보가 포함되어 있다. 가장 먼저, Seq Scan 바로 뒤에 대상이 명시되어 있다. Seq Scan 바로 뒤에 ‘on tr_ord_big t1’이라고 되어 있는 것을 알 수 있다. tr_ord_big 테이블에 대해 Seq Scan이 작동했음을 뜻한다. 뒤이어, actual 수치가 나온다. actual의 정보는 아래와 같다.
•
time = 6.359..17802.762
◦
해당 오퍼레이션의 수행시간 정보다. 6.359 ms에 시작해 17802.762 ms에 완료
◦
17802.762 - 6.359 = 17813.719 ms(약 17.8초)가 걸렸다.
•
rows = 6696
◦
해당 오퍼레이션을 거쳐 추출된 레코드 건수다.
•
loops = 1
◦
해당 오퍼레이션이 반복 실행된 횟수다.
지금까지의 내용을 종합하면 아래와 같이 해석할 수 있다.
•
tr_ord_big 테이블에 Seq Scan이 1번 수행되었고 6,696 건의 레코드를 찾는데, 17.8초 정도가 걸렸다.
Seq Scan 오퍼레이션 바로 아랫 줄에는 Filter 정보가 포함되어 있다. Filter 정보는 테이블을 전체 스캔하면서 데이터를 찾아내는데 사용한 조건이다. 지면상 Filter 정보가 일부 생략되었지만, SQL의 WHERE 조건인 ord_dtm에 대한 조건(2022년 1월 3일 이상, 2022년 1월 4일 미만)을 Filter로 처리하고 있다. 끝으로 Seq Scan에는 Rows Removed by Filter란 정보도 포함되어 있다. 이 정보는 Seq Scan 과정에서 Filter 조건을 충족하지 못해 버려진 레코드 건수를 뜻한다. 26,285,076 이란 매우 많은 건수가 버려졌다. 바꿔말하면, 약 2천 6백만건의 데이터에 불필요하게 접근한 것이다. SQL에 따라서 Filter 정보나 Rows Removed by Filter는 생략될 수도 있다.
지금까지의 Seq Scan의 오퍼레이션을 모두 종합해서 해석하면 다음과 같다.
•
tr_ord_big 테이블에 Seq Scan이 1번 수행되었고 6,696 건의 레코드를 찾는데, 17.8초 정도가 걸렸다. 6,696 건의 레코드를 찾기 위해서 ord_dtm 조건이 사용되었으며, 이 과정에서 약 2천 6백만건의 데이터가 버려졌다.

Seq Scan 각오해. Index가 간다.

Seq Scan은 테이블 전체를 스캔하므로, 찾고자 하는 데이터가 소량인 경우에는 비효율이 존재할 수 밖에 없다. 앞에서 살펴봤듯이, 6,696 건의 레코드를 찾는 과정에서 약 2천 6백만건의 레코드를 읽고 버렸다는 것은 매우 비효율적인 상황이다. 6천건 정도의 소량 데이터를 찾고자 한다면 인덱스를 활용하는 것이 좋다. Seq Scan에서 사용한 필터 조건 컬럼은 tr_ord_big 테이블의 ord_dtm이다. 이 컬럼에 인덱스를 생성하면, 해당 SQL은 저절로 ord_dtm에 대한 인덱스를 이용해 데이터를 찾아낼 가능성이 높다. 아래와 같은 SQL로 인덱스를 만들어보자.
CREATE INDEX tr_ord_big_x01 ON tr_ord_big(ord_dtm);
SQL
복사
인덱스를 만드는 시간은 데이터 양, 데이터베이스가 설치된 서버나 컴퓨터의 성능에 따라 다를 수 있다. 필자 환경에서는 약 10초 정도가 걸렸다. 인덱스가 생성되었다면 앞에서 실행했던 SQL을 다시 실행해보자.
EXPLAIN (ANALYZE,COSTS OFF) SELECT t1.mbr_id ,count(*) cnt FROM tr_ord_big t1 WHERE t1.ord_dtm >= TO_DATE('20220103','YYYYMMDD') AND t1.ord_dtm < TO_DATE('20220104','YYYYMMDD') GROUP BY t1.mbr_id;
SQL
복사
인덱스 생성 후에 실행계획을 살펴보면 다음과 같다. 각자 환경에 따라 필자와는 다른 실행계획이 나올 수도 있다. 기존 실행계획에서 성능의 많은 부분을 차지하던, Seq Scan이 사라지고 Bitmap Index Scan 오퍼레이션이 출력되고 있다.
기본적으로 Bitmap Index Scan은 자신의 부모 오페레이션인 Bitmap Heap Scan과 묶음으로 같이 실행되는 오퍼레이션이다. Bitmap Index Scan을 먼저 살펴보자.
PostgreSQL의 Bitmap Index Scan PostgreSQL의 Bitmap Index Scan은 B-Tree 인덱스를 활용한 데이터 검색 방식 중 하나이며, 이름이 비슷한 오라클의 Bitmap Index와는 전혀 다른 개념입니다.
•
Bitmap Index Scan on tr_ord_big_x01 (actual time=0.250..0.250 rows=6696 loops=1)
◦
Index Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND …
Bitmap Index Scan 바로 뒤에는 방금 우리가 생성한 tr_ord_big_x01이라는 인덱스 명칭이 표시되어 있다. 해당 인덱스를 사용하는 Bitmap Index Scan이 동작한 것이다. 그 뒤에는 actual 정보가 포함되어 있다. 해당 작업의 시작 시간이 0.250 ms, 종료 시간도 0.250 ms다. 0.000초 만에 해당 오퍼레이션이 처리된 것이다. rows를 통해 6,696 건의 레코드를 찾아냈으며, loops를 통해 해당 오퍼레이션이 한 번만 실행된 것을 알 수 있다. Index Cond(Condition의 줄임말)에는 Bitmap Index Scan 과정에서 데이터를 검색하기 위해 사용한 조건이 표시되어 있다. SQL의 WHERE 절에 해당하는 ord_dtm에 대한 조건이 사용된 것을 알 수 있다.
Bitmap Index Scan과 함께 수행되는 Bitmap Heap Scan에는 다음과 같은 정보가 표시되어 있다.
•
Bitmap Heap Scan on tr_ord_big t1 (actual time=0.283..5.306 rows=6696 loops=1)
◦
Recheck Cond: ((ord_dtm >= to_date('20220103'::text, 'YYYYMMDD'::text)) AND …
◦
Heap Blocks: exact=337
Bitmap Heap Scan이 어떤 오퍼레이션인지는 뒤에서 설명한다. 여기서 살펴볼 것은 단지, actual 수치다. Bitmap Heap Scan의 actual 수치가 0.283 ms에서 5.306 ms로서 약 0.005초만에 완료된 것을 알 수 있다. 새로 만든 인덱스인 tr_ord_big_x01을 Bitmap Index Scan & Bitmap Heap Scan 방식으로 처리해 0.005초만에 필요한 데이터를 찾아낸 것이다.
실행계획의 마지막 줄에는 SQL의 전체 실행 시간이 표시되어 있다. 전체 실행시간이 7.872 ms(약 0.007초)인 것을 알 수 있다. 약 17초가 걸리던 SQL이, 인덱스를 추가함으로 인해 0.007초 개선된 것이다. 정확히는 Seq Scan 방식으로 6,696 건의 데이터를 찾으면서 오래 걸렸던 SQL이, 인덱스를 활용해 6,696 건의 데이터를 찾으면서 성능이 좋아진 것이다. 이처럼 인덱스는 특정 조건의 데이터를 빠르게 찾아내는데 도움이 된다. 하지만, 벌써부터 자신이 운영하는 데이터베이스에 인덱스를 만들기 시작하면 곤란하다. 상황에 따라서 인덱스는 성능을 더 나쁘게 만들기도 한다. 이 책을 완전히 다 읽기 전까지는 인덱스 작업을 미루기 바란다.

INDEX 개념잡기

인덱스는 대체 어떤 구조이길래 데이터를 빠르게 찾을 수 있는 것일까? 원리는 간단하다. 데이터를 정렬하고 정렬된 데이터 중간 중간에 라벨링을 하는 것이다.
아래 그림과 같이, 스타벅스(Starbucks), 테슬라(Tesla), 우버(Uber)등 다양한 고객사를 가진 회사가 있다. 고객사 관련 서류가 쌓이다 보니, 정리가 필요해 서류함을 하나 샀다고 가정해보자. 신입사원에게 서류를 정리하라고 했더니, 특별한 기준없이 되는데로 서류함에 서류를 집어 넣었다. 이처럼 무작위로 서류를 정리하면 찾을 때가 문제다. 스타벅스(Starbucks) 서류를 찾는다고 가정해보자. 해당 서류는 어디에 있는지 알 수 없다. 서류함의 서랍을 순차적으로 하나씩 열어볼수 밖에 없다. 운 좋으면 첫 번째 서랍에서, 운 나쁘면 마지막 서랍에서 서류를 발견할 것이다. 다른 서류도 마찬가지다. 어느 서류를 찾든지 서랍을 모두 열어봐야 한다.
어느 서류를 찾든지, 일단 서랍을 열어봐야 하고 운 좋으면 처음 서랍에서, 운 나쁘면 마지막 서랍에서 서류를 발견할 수 있는 것은 매우 비효율적이다. 이를 효율적으로 처리하기 위해 다음과 같이 서류를 다시 정리했다.
•
서류함의 서류를 모두 꺼낸다.
•
서류를 A부터 Z 까지 알파벳 순서로 정리한다.
•
가장 위에 서랍부터 알파벳 순서에 따라 서류를 세 개씩 집어넣는다.
•
그리고 서랍에는 어떤 알파벳으로 시작하는 서류가 있는지 라벨링을 한다.
위와 같이 서류를 정리하면 아래와 같은 그림이 된다.
이제, 필요한 서류를 찾을 때 불필요하게 여러번 서랍을 열 필요가 없다. 스타벅스(Starbucks) 서류가 필요하면, 바로 세 번째 서랍을 열면된다. 스타벅스의 첫 번째 알파벳인 S는 3번 서랍에 라벨링된 N 보다는 크고, 4번 서랍의 T 보다는 작기 때문이다. 만약에 우버(Uber) 서류를 찾는다면, 바로 네 번째 서랍을 열면된다.
바로 이것이 인덱스의 원리다. 데이터를 정렬하고, 라벨링을 한다. 이후에는 라벨을 보고 필요한 데이터를 빠르게 찾아낼 수 있다. 이와 같은 인덱스의 원리는 우리 주변에 매우 많다. 대표적으로 두꺼운 책 앞 부분에 있는 책의 목차도 인덱스의 원리와 같다.
인덱스는 이해를 쉽게 하기 위해, 여러 책에서 삼각형 모양으로 간략하게 표현한다. 위의 그림을 아래와 같이 옆으로 돌려보면, 인덱스를 왜 삼각형으로 표현했는지 이해할 수 있다.
준비한 내용은 여기까지입니다.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서