[CS] 데이터베이스 설계 및 성능 최적화(feat. ChatGPT)

처누 2024. 12. 23. 18:21

ChatGPT와 함께하는 면접 준비

질문 3: 데이터베이스 설계 및 성능 최적화

1. 인덱스 설계

지역(지역명), 카테고리, 평점 필터를 모두 지원해야 할 때, 인덱스를 어떻게 설계하시겠습니까?
복합 인덱스와 단일 인덱스를 비교하여 장단점을 설명해 주세요.

내 답변

1-1. 평점 필터, 카테고리, 지역 순으로 복합 인덱스를 설계할 것입니다. 평점자체가 크기가 적고 카티날리티가 높기 때문에 앞서 말씀드린 순서대로 인덱스를 설계하는 것이 최선이라고 생각합니다.

1-2. 단일 인덱스는 하나의 컬럼에 대해서만 생성된 인덱스이고, 복합 인덱스는 두 개 이상의 컬럼에 대해서 생성된 인덱스입니다. 단일 인덱스는 복합 인덱스에 비해 단순한 검색 조건에 적합하고, 인덱스를 유지하고 관리하기가 쉽다는 장점이 있지만 여러 조건이 결합된 쿼리에서는 비효율적입니다. 복합 인덱스는 단일 인덱스에 비해 복합 조건 검색에서 효율적이고, 순차적으로 정렬된 인덱스를 사용하므로 범위 검색에서 유리합니다. 하지만 인덱스가 클수록 관리와 유지 비용이 증가하고, 컬럼 순서에 따라 인덱스 사용 여부가 결정된다는 단점이 있습니다.

정리

인덱스(Index)란?

- 데이터베이스 테이블에서 특정컬럼의 값을 빠르게 찾을 수 있도록(검색 속도 향상) 별도로 관리하는 데이터 구조

- 데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.(ex.책의 색인)

장점

- 빠른 검색 : 데이터를 빠르게 찾을 수 있어 쿼리 성능이 향상됨.

- 정렬된 데이터 : 인덱스가 정렬되어 있어 ORDER BY와 같은 연산이 효율적임.

- 중복 방지 : UNIQUE 제약 조건을 통해 데이터 무결성 유지

단점

- 저장 공간 증가 : 인덱스를 별도로 저장해야 하므로 추가적인 디스크 공간이 필요함.

- 쓰기 성능 저하 : DML(INSERT, UPDATE, DELETE) 시 인덱스를 갱신해야 하므로 성능에 영향을 미침.

-> UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 한다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 됨.

- 잘못된 인덱스 설계 : 불필요하거나 과도한 인덱스는 성능을 악화시킬 수 있음.

Hash Table(Hash Index)

- Hash Table은 (Key, Value)로 데이터를 저장하는 자료 구조.

- 특정 키에 대해 해시값을 계산해 데이터를 찾음.

- 동일 값 검색에 최적화되어 빠른 데이터 검색이 필요할 때 유용함.

- 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않음.

B-Tree

- 자식 2개 만을 갖는 이진 트리를 확장하여 N개의 자식을 가질 수 있도록 고안된 것.

- 좌우 자식 간의 균형이 맞지 않을 경우에는 매우 비효율적이라, 항상 균형을 맞춘다는 의미에서 균형 트리라고 불림.

- B-Tree는 최상위에 단 하나의 노드 만이 존재하는데, 이를 루트 노드(Root Node)라고 한다. 그리고 중간 노드를 브랜치 노드(Branch Node), 최하위 노드를 리프 노드(Leaf Node)라고 한다.

- B-Tree 인덱스는 정렬된 상태로 데이터 유지, 검색, 삽입, 삭제에서 일정한 시간 복잡도 제공.

B+Tree

- DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조.

- 모든 노드에 데이터(Value)를 저장했던 B-Tree와 달리 리프 노드(데이터 노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지 노드(인덱스 노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.

- 리프 노드들은 LinkedList로 연결되어 있음.(InnoDB에서는 같은 레벨의 노드들끼리는 Double Linked List로 연결되어 있고, 자식 노드들은 Single Linked List로 연결되어 있음.)

- 모든 리프 노드가 동일한 깊이를 가지므로 균형을 유지하고, 삽입 및 삭제 시 트리의 균형을 유지하기 위한 재구조화가 수행됨.

인덱스를 사용하는 경우

- WHERE 조건문에 자주 사용되는 컬럼

- ORDER BY 또는 GROUP BY에 사용되는 컬럼

- JOIN 조건에서 자주 사용되는 컬럼

- DISTINCT 검색을 수행해야 하는 경우

인덱스 사용에 영향을 주는 요소

1. PK의 크기

인덱스가 PK가 아닌 실제 레코드의 주소를 갖게 할 수도 있지만, PK가 변경될 때 레코드의 주소가 변경되고 모든 인덱스에 저장된 레코드 주소를 변경해야 한다. 이러한 오버헤드를 피하기 위해 인덱스는 레코드의 주소가 아닌 PK를 저장하고 있다. 따라서 PK 값이 클수록 인덱스에 좋지 않음. PK가 클수록 한 페이지에 담을 수 있는 인덱스 정보도 줄어들고, 메모리도 비효율적으로 사용됨. 또한 트리의 깊이도 지나치게 깊어지면 읽어야 하는 페이지가 많아져서 성능에 좋지 않음.

2. 인덱스의 컬럼 순서

다중 컬럼 인덱스에서 항상 다음 컬럼이 이전 컬럼에 의존하여 정렬됨.

3. 카티날리티(Cardinality)

특정 컬럼에 존재하는 데이터의 고유성

카디날리티가 높을수록 중복도가 낮아지며 유니크한 값이 많다는 의미.

카디날리티가 낮더라도 정렬 또는 그루핑 등에 효율적으로 사용될 수 있음.

4. 인덱스의 정렬 및 스캔 방향

인덱스 역순 스캔은 인덱스 정순 스캔보다 느림.

페이지 잠금이 인덱스 정순 스캔에 적합한 구조.

페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조.

단일 인덱스

하나의 컬럼에 대해서만 생성된 인덱스
한 컬럼만 기준으로 데이터 검색을 최적화함.
특정 컬럼에서 자주 사용하는 조건문에 적합함.

장점

단순한 검색 조건에 적합.
인덱스를 유지하고 관리하기 쉬움.

단점

여러 조건이 결합된 쿼리에서는 비효율적

복합 인덱스

두 개 이상의 컬럼을 조합하여 생성된 인덱스
여러 컬럼의 조합에 대해 데이터 검색을 최적화함.
컬럼의 순서가 중요함 -> 인덱스가 (A, B)로 생성되었다면, A를 먼저 조건으로 사용해야 함.

장점

복합 조건 검색에서 매우 효과적.
순차적으로 정렬된 인덱스를 사용하므로 범위 검색에도 유리함.

단점

컬럼 순서에 따라 인덱스 사용 여부가 결정되므로 설계에 신중해야 함.
인덱스가 클수록 관리와 유지 비용이 증가함.

인덱스 선택 기준

단일 인덱스가 적합한 경우

- 단일 컬럼에 대한 검색이나 필터링이 자주 사용되는 경우

복합 인덱스가 적합한 경우

- 두 개 이상의 컬럼을 조합한 조건이 자주 사용되는 경우.

- 특정 컬럼에 대한 범위 검색과 다른 컬럼의 정확한 매칭 조건이 있는 경우.

ChatGPT의 꼬리 질문

1. 평점, 카테고리, 지역의 사용 빈도나 쿼리 패턴이 바뀐다면, 복합 인덱스를 어떻게 재구성하시겠습니까?

2. "평점 자체가 크기가 적다"고 하셨는데, 구체적으로 이 의미를 설명해 주실 수 있을까요?

2. 쿼리 최적화

여행지 데이터를 검색할 때, 정렬 및 필터링 조건이 많습니다.
예를 들어, "서울 지역의 박물관 카테고리 중 평점이 4점 이상인 장소를 평점순으로 정렬"이라는 요청이 들어왔습니다.
이 쿼리를 최적화하기 위해 어떤 방법을 사용하실 것인지 설명해 주세요.

내 답변

평점, 카테고리 지역 순, 평점을 내림차순으로 하여 복합 인덱스를 설계할 것입니다. 이렇게 인덱스를 설계한다면 정순 스캔으로 인해 평점이 높은 순부터 검색하므로 최적화가 될 것이라고 생각합니다.

ChatGPT의 꼬리 질문

1. 실행 계획에서 어떤 항목을 확인해 성능을 최적화할지 설명해 주세요.

2. 만약 평점과 카테고리의 조건이 빠지거나, 조건 순서가 변경되었다면 이 인덱스는 여전히 최적화된 상태일까요?

그렇지 않다면, 이를 어떻게 보완하실 수 있을까요?

3. 캐싱 활용

이 서비스에서 캐싱을 적용한다고 가정했을 때, 어떤 데이터와 요청에 캐싱을 적용하는 것이 효과적일지 설명해 주세요.
또한 캐시 무효화(Cache Invalidation)를 어떤 방식으로 처리할지 알려주세요.

내 답변

3-1. 지역 데이터에 대해 캐싱을 적용하는 것이 효과적이라고 생각합니다. 카테고리 및 평점에 비해 비교적 변화가 적고, 호출이 잦은 데이터이기 때문입니다.

3-2. 죄송합니다. 생각나지 않습니다.

정리

캐시 무효화

캐싱된 데이터가 더 이상 최신 상태가 아닐 때 이를 제거하거나 갱신하는 과정.

캐시 무효화의 주요 방식

Write-Through

- 데이터베이스에 쓰기 작업이 발생할 때 동시에 캐시에 업데이트.

- 캐시와 데이터베이스가 항상 동기화 상태를 유지.

- 읽기가 매우 빈번하며, 쓰기 작업이 적은 경우 적합함.

장점

- 캐시 데이터가 항상 최신 상태를 보장

- 읽기 성능 최적화

단점

- 쓰기 작업 시 지연이 발생(캐시와 DB 모두 업데이트)

Write-Around

- 쓰기 작업 시 데이터베이스만 업데이트하고 캐시는 건드리지 않음.

- 데이터가 캐시에 필요해질 때만 다시 로드.

- 쓰기 작업이 빈번하지만, 읽기 패턴이 특정 데이터에 집중되지 않는 경우 적합함.

장점

- 쓰기 작업 성능이 향상

단점

- 쓰기 후 즉시 읽을 경우, 캐시 적중률이 낮아질 수 있음.

Write-Back

- 데이터를 캐시에 먼저 기록하고, 일정 주기나 조건을 만족할 때 데이터베이스를 업데이트.

- 쓰기 작업이 캐시에만 반영되므로 지연이 적음.

- 쓰기 작업이 빈번하고, 약간의 지연 데이터 일관성을 허용할 수 있는 경우.

장점

- 쓰기 성능이 매우 빠름.

단점

- 캐시 손실 시 데이터가 유실될 위험.

- 데이터 일관성을 보장하기 어렵고 복잡한 설계가 필요.

캐시 무효화 전략

명시적 무효화(Explicit Invalidation)

- 애플리케이션 코드에서 명시적으로 캐시를 무효화하거나 삭제

//Redis내에서 delete로 삭제
redisTemplate.delete("key");

시간 기반 무효화(Time-to-Live, TTL)

- 캐시 항목에 유효 기간(TTL, Time-to-Live)을 설정.

- TTL이 만료되면 캐시가 자동으로 무효화.

//Redis에 등록 시 TTL 설정
redisTemplate.opsForValue().set("key", value, TTL);

LRU(Least Recently Used) 정책 기반 무효화

- 사용 빈도가 낮은 데이터부터 자동으로 무효화.

- 캐시 공간이 가득 찼을 때 활용

//Redis내 설정
CONFIG SET maxmemory-policy allkeys-lru

이벤트 기반 무효화

- 데이터베이스에서 데이터가 변경되면 관련 캐시를 무효화.

- 데이터 변경 이벤트를 트리거로 캐시 삭제.

지연 무효화(Lazy Invalidation)

- 데이터가 요청될 때 캐시를 확인하고, 최신 데이터가 아니면 무효화 후 새로 저장.

수정된 답변

3-2. TTL 기반으로 한 자동 무효화 방식으로 일정 시간이 지난 데이터는 삭제되도록 설계할 것입니다.

ChatGPT의 꼬리 질문

1.캐시 무효화(Cache Invalidation)에 대한 전략으로 다음 중 하나를 선택한다면, 어떤 것을 적용하시겠습니까?

TTL(Time to Live)을 사용해 일정 시간 후 캐시를 갱신.
데이터 변경 시 직접 캐시를 갱신하는 Write-through 전략.
LRU(Least Recently Used) 정책을 사용해 오래된 캐시부터 삭제.
각 전략의 장단점도 간단히 설명해 주세요.

2. Redis 같은 캐시 서버를 사용할 때, "Cold Start 문제"(처음 요청 시 캐시가 비어 있는 상황)를 어떻게 해결할 수 있을지 말씀해 주세요.