API 성능 개선 - DB 인덱스 설정

1. 배경 및 목적

API 성능 개선 - SQL 튜닝 을 진행하며 각 SQL에 적합한 인덱스를 적용하기 위해 인덱스를 연구함.

연구하고 적용하는 과정을 서술했다.

2. 고민사항

2.1 싱글인덱스(uuid/doc_uid)와 복합인덱스(uuid, deleted_at) 중 어느걸 선택할까…

해당 쿼리의 속도를 줄이는게 매우 중요
쿼리가 매우 복잡하고 필요한 인덱스가 많기 때문에 복합인덱스 선택
다른 쿼리에서도 많이 사용하는 uuid와 doc_uid, deleted_at은 필수

2.2 uuid4 index가 얼마나 해비한지

랜덤 값이기 때문에, 데이터베이스에 삽입할 때 순차적이지 않아서 성능 저하가 발생할 수 있음 특히, 클러스터링 인덱스를 사용하는 경우 성능이 떨어질 수 있음.
PK의 보안 때문에 UUID를 고려한다면 UUIDv7을 사용해 보는 것도 괜찮을 것 같다. UUIDv7은 UNIX_TIMESTAMP를 ms 단위로 인코딩하여 효율적인 색인화가 가능하다고 한다.

uuid 타입별 performance 비교. 출처) https://www.borntodare.me/mysql_uuid

2.3 인덱스가 메모리 얼마나 잡아먹는지

628ms 16MB

-- 약 190139건 table에서 인덱스 생성
CREATE INDEX {index_name}
ON public.document_item_faq (
    docitmfaq_deleted_at, (timestamp)
    doc_uid, (int)
	  docitmfaq_is_indexed (bool)
);

2.4 최적의 복합인덱스 컬럼 찾는 방법

가장 아래부터 보면서 Filter를 찾는다.
Filter에 해당하는 순서대로 복합인덱스를 생성한다.
ex

인덱스 설정

CREATE INDEX {index_name}
ON public.document_item_faq (
    docitmfaq_deleted_at,
    doc_uid,
	  docitmfaq_is_indexed
);

제대로 동작하는지 로그 확인

EXPLAIN ANALYZE
{ SQL }

추가로 더 인덱스를 설정할 컬럼을 찾아보기
- order by, group by 에서 인덱스를 탈 수 있는 조건을 확인한다.

3. 인덱스 적용 사례

3.1 SQL

EXPLAIN ANALYZE
SELECT DISTINCT ON (d.docitmfaq_uuid)
    d.doc_uid AS document_uid,
    d.docitmfaq_uid AS item_uid,
    d.docitmfaq_uuid AS item_uuid
FROM public.document_item_faq d
JOIN (
    SELECT d2.docitmfaq_uuid
    FROM public.document_item_faq d2
    WHERE d2.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
      AND d2.docitmfaq_deleted_at IS NULL
    GROUP BY d2.docitmfaq_uuid
    HAVING COUNT(CASE WHEN d2.docitmfaq_is_indexed = TRUE THEN 1 ELSE NULL END) = 0
) AS unindexed ON d.docitmfaq_uuid = unindexed.docitmfaq_uuid
WHERE d.docitmfaq_deleted_at IS NULL
  AND d.doc_uid IN (393,394,414,415,416,417,418,419,496,497)
ORDER BY d.docitmfaq_uuid, d.docitmfaq_updated_at DESC;

기존 코드 sqlalchemy

metadata = MetaData()
table = Table(enum_doc_table_name, metadata, autoload_with=engine)

table1 = (
    select(table.c[document_uuid_column_name].label("document_uuid"))
    .where(
        and_(
            table.c["doc_uid"].in_(document_uids),
            table.c[document_deleted_at_column_name].is_(None),
        )
    )
    .group_by(table.c[document_uuid_column_name])
    .having(
        func.count(
            case((table.c[document_is_indexed_column_name] == True, 1))
        )
        == 0
    )
    .subquery()
)

latest_items_subq = (
    select(
        table.c["doc_uid"].label("document_uid"),
        table.c[enum_doc_uid_column].label("item_uid"),
        table.c[document_uuid_column_name].label("item_uuid"),
    )
    .distinct(table.c[document_uuid_column_name])
    .join(
        table1,
        table.c[document_uuid_column_name] == table1.c["document_uuid"],
    )
    .where(table.c[document_deleted_at_column_name].is_(None))
    .order_by(
        table.c[document_uuid_column_name],
        desc(table.c[document_updated_at_column_name]),
    )
)
return session.execute(latest_items_subq).all()

3.2 인덱스 적용

CREATE INDEX {index_name}
ON public.document_item_faq (
    docitmfaq_deleted_at,
    doc_uid,
	  docitmfaq_is_indexed
);

3.3 결론

항목	Planning Time (ms)	Execution Time (ms)	Scan 방식
인덱스 적용 전	0.616	103.073	Parallel Seq Scan
인덱스 적용 후	0.249	72.571	Index Only Scan
개선 효과	약 59.6% 감소	약 29.6% 성능 향상	디스크 접근 최소화

인덱스 적용을 통해 디스크 I/O를 최소화하고 쿼리 실행 속도를 약 30% 개선함.

특히, Index Only Scan이 사용되면서 한 번의 loop로 더 많은 row를 처리하는 최적화가 진행됨.

실제 쿼리 결과 사진 (인덱스 적용 전)

실제 쿼리 결과 사진 (인덱스 적용 후)

4. 인덱스 주의사항

4.1 인덱스 설계 주의사항

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.
- ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
- WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 조건절에 의해 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
- ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
- GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
- ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우
이미 대용량 데이터가 입력되어 있다면, 업무시간에 클러스터형 인덱스를 생성하는건 매우 부하를 주므로 자제하자
테이블 생성 시 Primary Key(PK)를 지정하면, 그 칼럼은 자동으로 Clustered Index가 만들어진다.
→ 만약 Clustered Index를 따로 지정하고 싶으면, 설정을 통해 테이블내에서 원하는 컬럼으로 Clustered Index로 생성할 수 있다.
테이블의 PK에는 기본 클러스터 인덱스가 주어지지만 이를 다른 테이블에서 FK로 사용한다해서 인덱스를 타지는 않는다.
성능 비교를 할 때엔 무조건 버퍼 캐시를 지우고 테스트 하자

4.2 싱글인덱스 설계 주의사항

user 테이블의 (싱글 인덱스 name)과 (싱글 인덱스 job)일 때

쿼리 조건	인덱스 사용 여부
WHERE name = 'ji'	✅ 완전 사용 (name 사용)
WHERE job = 'engineer'	✅ 완전 사용 (job 사용)
WHERE name = 'ji' AND job='engineer'	✅ 사용함 (한 쿼리에서는 더 효율 높은 인덱스 하나만 사용)

4.3 복합인덱스 설계 주의사항

복합인덱스 구성시 선택성이 좋은 컬럼을 앞으로 구성하는 것이 일반적으로 좋다.
- High Cardinality First : 가장 유니크한 컬럼 먼저
등치조건 (=)을 사용하는 컬럼이 범위조건(>, <)을 사용하는 컬럼보다 앞으로 두는 것이 일반적으로 좋다
- Follow Logical Order : 정렬,필터링의 순서를 유지하라
복합 인덱스는 왼쪽부터 순차적으로 적용됨.
- user 테이블의 복합 인덱스 (name, job)일 때
  
  쿼리 조건 인덱스 사용 여부
  
  WHERE name = 'ji' ✅ 사용함
  
  WHERE job = 'engineer' ❌ 사용 못함
  
  WHERE name = 'ji' AND job='engineer' ✅ 완전 사용
WHERE col1 = ? AND col2 = ? AND col3 BETWEEN ? AND ? AND col4 = ? AND col5 = ?
- 모든 인덱스를 참조하게 하고 싶다면 col1, col2, col4, col5, col3 순으로 생성해야 한다.
다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야한다.

5. 인덱스 종류

5.1 클러스터 인덱스 (Clustered Index)

출처) https://gwang920.github.io/database/clusterednonclustered/

Clustered Index는 트리로 저장되어, Root 페이지와 Leaf 페이지로 구성된다. 또한 Root페이지는 Leaf 페이지의 주소로 구성하고, Leaf페이지는 실제 데이터 페이지로 구성된다. 따로 추가적인 저장소에 인덱스 페이지를 만들지 않는다. (루프 페이지에서 인덱스 페이지 없이, 바로 리프 페이지(=데이터 페이지)와 연결됨)
한 테이블당 Row별로 한개만 존재 (주로 PK)
Clustered Index는 물리적으로 정렬되어 있어 검색 속도가 Non-Clustered Index 보다 더 빠르다. 하지만 데이터의 입력. 수정, 삭제는 느리다.
- Balancing - Tree구조이기 때문에 기본적으로 모두 같은 크기의 페이지를 유지한다. 그래서 새로운 데이터가 추가되면 새로운 데이터가 추가 될 테이블의 기존 데이터 절반이 새로운 페이지로 이동한 후에 새로운 행이 추가된다.
- 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지하는데, 이때 물리적으로 행을 재배열하며 페이지 분할이 일어날 수 있어 오래 걸릴 수 있다.
비순차적인 값이 삽입되면 데이터가 재배치되거나 페이지 분할이 발생할 수 있음. 특히 UUIDv4처럼 랜덤한 값이 기본 키로 사용될 때, 이러한 성능 저하가 심화될 수 있음 반대로 기본 키 값이 순차적으로 증가할 경우(예: AUTO_INCREMENT), 데이터가 물리적으로 연속된 위치에 저장되므로 디스크 I/O가 효율적임

5.2 비클러스터 인덱스 (Non Clustered Index)

위는 인덱스 페이지, 아래 박스는 데이터 페이지. 출처) https://gwang920.github.io/database/clusterednonclustered/

Root 페이지는 별도의 장소에 생성된 leaf 페이지 (=인덱스 페이지)를 거쳐 데이터 페이지와 연결된다. 인덱스 페이지(leaf페이지)는 키값과 데이터가 위치하는 포인터(RID)로 구성된다.
인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지한다.
클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠르다.
- 클러스터드 인덱스보다 페이지 분할이 적게 일어나고 인덱스 페이지를 정렬하는데 빠름
인덱스 조회 시 거쳐야 하는 단계가 많아 데이터 접근 속도가 클러스터 인덱스보다 상대적으로 느리다.
- 검색하고자하는 데이터의 키 값을 루트 페이지에서 비교하여 리프 페이지 번호를 찾고, 리프 페이지에서 RID 정보로 실제 데이터의 위치로 이동

6. 인덱스 타기 좋은 컬럼

✅ FK
✅ where 절에 많이 들어가는 컬럼
✅ select, join, union, distinct 과 order by, group by에 많이 들어가는 컬럼
❌ 대규모(25%이상)를 조회하면 쓰면 안됨 (특정 소수만 조회할 때)
❌ long character string은 피해라
❌ update가 잦은 컬럼은 피해라

7. Scan 종류

7.1 Sequential scan (순차탐색)

인덱스가 존재하지 않는 컬럼을 대상으로 전체탐색

데이터가 많을수록 오래 걸림

7.2 Index scan

인덱스 사용하긴 하는데 인덱스가 없는 컬럼까지 가져오거나, 조건의 일부만 인덱스를 탈 때

7.3 Index Only scan

인덱스만을 사용해 해결가능 할 때

Index에서 바로 값을 가져올 수 있을 때, 인덱스가 걸려있는 컬럼만 조회할 때

7.4 Bitmap Index scan

일반적으로 인덱스를 사용해 여러 행을 반환할 때

Sequential SCAN과 Index SCAN의 사이이며, 인덱스가 걸린 컬럼에서 여러 개 행을 반환할 때 I/O 과정을 줄이기 위해 사용

내부 로직

하나의 블록 안에는 여러 개의 레코드(행)이 들어가는데, 이 때 하나의 블록 안에 존재하는 여러개의 레코드를 찾아야한다면

Index SCAN을 사용하면 해당 블록에 3번 접근
한 블록 안에 어떤 레코드들이 있는지 미리 알고 있다면 1번만 접근하면 됨. (I/O 과정 ↓)

따라서 각 블록 안에 어떤 레코드가 있는지 Bitmap으로 만드는 과정이 Bitmap index scan이고 이후 Bitmap Heap scan에서 이걸 활용

7.5 Bitmap Heap scan

Bitmap을 스캔하면서 조건에 맞는 레코드를 가져옴

참고 )

https://growth-coder.tistory.com/329#Bitmap Index SCAN-1-5

배지의 개발 블로그