Docs Data

데이터베이스 성능 최적화

인덱스 최적화

DB 인덱스 튜닝은 애플리케이션 쿼리 실행 속도 향상을 위한 핵심 기법입니다. 인덱스가 제대로 최적화되지 않으면 쿼리 실행 속도가 저하되고 애플리케이션 성능에 지연이 발생할 수 있습니다.

인덱스를 사용하지 않는 케이스

다음과 같은 경우 인덱스를 사용하지 못할 수 있습니다:

인덱스 칼럼을 변경하는 조건절

-- 함수 적용
WHERE SUBSTRING(column1) = value1
WHERE CONCAT(column1, '', column2) = value2

-- 연산 적용
WHERE column1 + 10 > 100

부정형 비교

NOT IN, !=, >, < 연산자 사용 시 인덱스 사용이 제한적일 수 있음

와일드카드 LIKE문장

WHERE column1 LIKE '%2023'  -- 인덱스 미사용
WHERE column1 LIKE '2023%'  -- 인덱스 사용 가능

인덱스 칼럼의 형변환

실행 계획 분석

인덱스 설계 후 실제 실행되는 쿼리들이 인덱스를 제대로 태우는지 EXPLAIN 구문을 통해 별도로 검증해보아야 합니다. EXPLAIN은 쿼리가 어떻게 실행될지에 대한 정보를 보여주며, 인덱스 사용 여부와 조인 방식 등을 확인할 수 있습니다.

EXPLAIN 사용법

EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN 결과 분석

EXPLAIN 실행 결과에서 select_type과 type은 쿼리 최적화를 위해 중요하게 확인해야 할 핵심 지표입니다.

1. select type (쿼리 구조)

설명: 쿼리에서 사용된 SELECT 문의 종류를 나타내며, 쿼리의 구조와 복잡성을 파악하는 데 도움이 됩니다.

select_type	설명
SIMPLE	가장 일반적인 SELECT 문이며 복잡한 UNION이나 서브쿼리가 없는 경우에 해당. 단순히 테이블에서 데이터를 가져와 결과를 반환하므로 가장 빠르다.
PRIMARY	복잡한 쿼리에서의 최상위 쿼리문인 경우
SUBQUERY	SELECT 문 안에서 서브쿼리를 사용한 경우
DEPENDENT SUBQUERY	서브쿼리가 외부 쿼리로부터 값을 참조하여 실행하는 경우
DERIVED	FROM 절에서 쓰인 서브쿼리(인라인 뷰)
UNION	UNION 또는 UNION ALL을 사용한 경우
DEPENDENT UNION	UNION의 서브쿼리가 외부 쿼리로부터의 값을 참조하여 실행하는 경우
UNION RESULT	UNION의 결과로 생성된 임시 테이블에 대한 SELECT문을 실행하는 경우

2. type (접근 방식)

설명: 테이블에서 행을 조회하는 방식을 나타내며, 인덱스 사용 여부와 효율성을 판단하는 근거가 됩니다. 아래 표는 EXPLAIN join type을 최상부터 최악의 유형까지 순서대로 정리한 표입니다.

type	설명
system	테이블에 단 하나의 행만 있는 경우
const	테이블의 Unique Key 혹은 Primary Key를 상수로 조회하는 경우(한건만 존재)
eq_ref	다른 테이블과 조인 시 Primary Key를 이용(한건 조회)
ref	다른 테이블과 조회 시 인덱스에 설정된 모든 칼럼을 조회 조건으로 사용 (사용되는 키 조건이 몇 개의 행에 일치하는 경우, 최소 두 개 이상)
fulltext	풀텍스트 인덱스를 사용하여 수행
ref_or_null	ref와 동일하지만 null 값이 포함됨 MySQL의 경우 NULL에 대해서도 인덱스를 활용할 수 있고 이때 NULL은 가장 앞쪽에 정렬된다.
index_merge	단일 테이블의 여러 범위의 행을 스캔한 결과를 하나로 병합
unique_subquery	IN 절의 서브쿼리에 eq_req 적용
index_subquery	IN 절의 서브쿼리에 PK가 아닌 인덱스 적용
range	인덱스의 지정 범위 안의 행을 검색(`BETWEEN`, `LIKE`, `IN`, `<`, `>`, `<=`, `>=` 등)
index	단일 인덱스의 일부만 충족할 경우(테이블 풀 스캔보다는 유리하지만) 적용된다. 물리적인 인덱스 블록을 처음부터 끝까지 탐색한다.
ALL	테이블 풀 스캔. 테이블의 처음부터 끝까지 탐색한다. 전체 테이블 중 10~20퍼센트 정도를 조회할 때는 오히려 유리할 때도 있다.

추가 분석 포인트

key
- 실제로 사용된 인덱스
- NULL이면 인덱스가 사용되지 않았음을 의미
rows
- SQL문이 접근한 모든 데이터 행수를 예측해서 나타내는 정보
Extra
- 쿼리 실행 시 추가적인 작업이 어떻게 수행되는지 보여주는 중요한 지표입니다. 성능 최적화 시 주로 Using temporary와 Using filesort를 확인합니다.

Extra 명	설명 및 최적화 방안
Using index	커버링 인덱스가 사용되어 테이블 접근 없이 인덱스만으로 쿼리가 처리됩니다. 가장 이상적인 경우로, 인덱스가 효율적으로 설계되었다는 의미입니다.
Using where Using index condition	WHERE 절 필터링이 적용되었습니다. 인덱스를 효과적으로 사용할 수 있도록 WHERE 절의 조건을 최적화하거나, 불필요한 조건을 제거하는 것이 좋습니다.
Using temporary	쿼리 실행 중 임시 테이블이 사용됩니다. GROUP BY, ORDER BY, DISTINCT 등에서 주로 발생하며, 성능 저하의 원인이 될 수 있습니다. 쿼리를 재작성하거나 인덱스를 추가하여 임시 테이블의 필요성을 줄일 수 있습니다.
Using filesort	정렬 작업을 위해 추가적인 파일 정렬이 수행되었습니다. 적절한 인덱스가 없다면 메모리나 디스크를 사용하여 정렬하므로 성능에 영향을 줄 수 있습니다. 정렬이 필요한 경우 인덱스를 올바르게 사용하거나 애플리케이션에서 최적화하는 등의 방법을 고려해야 합니다.
Using join buffer	조인 작업 시 조인 버퍼가 사용되었습니다. 조인할 테이블이 많거나 인덱스가 적절하지 않을 때 발생합니다. 성능을 개선하기 위해 조인 버퍼 크기를 조정하거나, 인덱스를 사용하여 조인 작업을 최적화해야 할 수 있습니다.

성능 개선 팁

인덱스 설계 및 최적화
- 자주 조회되는 컬럼에 인덱스 추가 (카디널리티가 높은 컬럼 우선)
- 복합 인덱스는 등치 조건(=WHERE col=val) → 범위 조건(>, <, BETWEEN) → 정렬(ORDER BY) 순으로 컬럼 정렬
- WHERE, ORDER BY, GROUP BY 절에 사용되는 컬럼 우선 인덱싱
- type이 ALL인 쿼리는 인덱스 추가 검토
- Using filesort나 Using temporary가 보이면 쿼리나 인덱스 구조 개선 필요
쿼리 구조 개선
- SELECT * 대신 필요한 컬럼만 조회
- LIMIT으로 결과 집합 제한
- DERIVED가 자주 보인다면 조인으로 대체 검토
- 서브쿼리 대신 조인 사용 고려
- OR 대신 IN 또는 UNION ALL 사용 고려
모니터링
- EXPLAIN ANALYZE로 실제 실행 계획 확인
- 정기적으로 ANALYZE TABLE 실행으로 통계 정보 최신화
- 슬로우 쿼리 로그 분석을 통한 최적화 대상 쿼리 식별

slow index

인덱스 조회가 예상만큼 빠르게 작동하지 않는 경우가 존재합니다.

인덱스를 정상적으로 사용하더라도 단순한 쿼리가 느려지는 진짜 원인은 무엇일까요? 디스크 I/O 메커니즘과 클러스터링 팩터 관점에서의 구체적인 분석은 인덱스를 사용해도 쿼리가 느려지는 원인 분석 글에서 자세히 살펴보실 수 있습니다.

커넥션 사이즈 조절

시스템의 요청 작업 증가 또는 slow query 발생은 커넥션을 얻기 위한 대기 시간 증가로 이어질 수 있다. 따라서 쿼리 튜닝과 함께 성능 테스트를 반복하면서 커넥션의 지정한 적정한 사이즈를 확보해야 한다. 초당 요청 쿼리 수를 산정하고 애플리케이션의 최대 피크 시간을 감안한 사이즈를 설정한다. 이는 성능 테스트를 통해서 가늠할 수 있다.

커넥션 수를 설정할 때는 시스템의 부하를 고려하여 최적의 값을 찾는 것이 중요하다. 모니터링과 성능 테스트를 통해 실제 사용 사이즈에 맞게, 즉 초당 처리할 수 있는 쿼리의 최대 수를 바탕으로 조정하는 것이 좋다.

중복 쿼리 제거

애플리케이션 성능에서 가장 간단하면서 가장 큰 효과를 볼 수 있는 방법은 DB접근 횟수를 줄이고 인텍스 Hit을 통해 응답 시간을 개선하는 것이다. 결국 DB 쿼리 동작이 병목을 일으키는 가장 큰 요인이므로, 해결책은 DB 액세스 횟수 자체를 줄이는 것이다.

중복 쿼리 제거는 필수적인 튜닝이며 이와 함께 캐싱을 도입한다.
읽어온 쿼리 바탕으로 로직 레벨에서 처리할 수 있는 것들은 최대한 로직으로 풀어내야 한다.
- 조회해야 할 대상 테이블이 많아 Join구문이 복잡하고 depth가 깊다면, 애플리케이션에서 이 조인 관계를 풀어주고 따로 호출해야 한다. 그 후 각 테이블을 호출하여 건수를 좁히는 방식으로 실행 속도를 개선할 수 있다. (조인된 테이블 간 Primary Key를 통해 먼저 가져와야 할 데이터를 애플리케이션에서 구하고, 다시 그 키를 통해 Foreign Key나 필터링 조건으로 두 번째 타겟을 구해와서 머지 혹은 필터 등을 수행하여 결과물을 리턴하는 형태이다.)

조인 관계를 폴어내는 것은 MSA 설계 시 레거시 테이블을 분리하는 전략으로 유용하게 사용되는 기법 중 하나이므로 반드시 기억해두어야 한다.

캐싱

데이터 사이즈는 크지만 잘 변하지 않는 경우나 코드성 테이블은 캐싱 솔루션을 도입하여 성능 향상을 꾀할 수 있다. 가장 널리 사용되는 방법은 별도로 Redis를 구축하여 사용하거나 Ehcache나 Memcached와 같은 인-메모리 캐싱을 통해 API 콜이나 DB의 데이터 검색 횟수를 줄이는 방식이 있다. Ehcache는 멤캐시드에 비해 고급기능이 많고, Memcached는 단순하고 가벼워 분산 환경에서 더 많이 쓰인다.

Redis 사용시 장단점
- 장점: DB접근보다 빠르고 애플리케이션과 분리하여 구축하기에 서버 측의 부담이 덜하다.
- 단점: 별도의 장비로 구축이 필요하고 커넥션을 맺는 과정의 네트워크 비용도 고려해야 하며 조회 프로그램이나 모니터링 툴을 추가하는 등 운영 비용이 추가로 발생한다.

파티셔닝

테이블 파티셔닝은 하나의 테이블을 여러 개의 작은 단위로 분할하여 테이블 제약 조건에 따라 저장, 보관하는 방식을 말한다. 물리적으로 여러 개의 테이블을 생성하는 것이 아닌 논리적인 측면에서 테이블 분할 설계를 의미한다. 다만 실제 데이터 파일들은 여러 개의 물리 파일로 분리되어 보관될 것이다. 이는 성능 및 관리 효율을 향상하고 쿼리 조건을 기반으로 데이터를 분리하면 슬로우 쿼리를 해소하는 데 도움이 될 것이다.(예를 들어 고객의 구매 이력을 연도별로 조회)

설계 시 데이터 사이즈가 시간에 비례하여 늘어나거나 데이터가 큰 테이블에서 select, insert, update가 빈번하게 일어나서 인덱스 역시 영향을 받을 거라고 예상된다면 파티셔닝을 통해 인덱스를 분할하는 효과를 기대할 수 있다.

보통은 기간이나 값의 범위 같은 것으로 분할하거나 특정 칼럼의 값을 해싱하여 저장할 파티션을 선택하는 방식으로 진행한다. 파티셔닝 전략은 수직 분할과 수평 분할로 구분지을 수 있다.

수직 분할은
- 테이블 칼럼을 기준으로 나누는 기법이고 자주 사용하는 칼럼 등을 분리시켜 성능을 향상한다.
수평 분할은
- 데이터 개수를 기준으로 나누고 이를 통해 인덱스 성능을 끌어올리는 것을 기대한다. (단 테이블 간의 비용 증가나 애플리키에이션의 복잡도 증가가 유발되기에 어떤 전략을 선택할지 잘 분석해야 한다.)

CREATE TABLE app_logs (
    log_id NUMBER GENERATED ALWAYS AS IDENTITY,
    log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
    user_id VARCHAR2(50),
    action_type VARCHAR2(50),
    ip_address VARCHAR2(50),
    details CLOB,
    status_code NUMBER
)
PARTITION BY RANGE (TRUNC(log_timestamp, 'MM')) (
    PARTITION logs_202301 VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')),
    PARTITION logs_202302 VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD')),
    PARTITION logs_202303 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION logs_202304 VALUES LESS THAN (TO_DATE('2023-05-01', 'YYYY-MM-DD')),
    PARTITION logs_maxvalue VALUES LESS THAN (MAXVALUE)
);

샤딩(Sharding)

J커브를 그리며 급성장 하는 회사에서 회원의 폭발적인 증가와 빈번하게 트렌젝션이 일어나는 주문/반품/환불 데이터나 주문에 비례해서 쌓이는 배송 정보, 구매내역 등은 데이터 사이즈만큼이나 지속적으로 select, insert, update가 발생하여 시간이 지날수록 슬로우 현상이나 장애를 유발할 수 있다. 인덱스와 파티셔닝으로 해결할 수 없는 조회 속도 저하, DB 밀림 등의 문제가 발생할 경우 DB 증설이나 샤딩을 고려해야 한다.

샤딩이란?

샤딩은 데이터를 분할하여 저장하는 방식이다. 동일 DB에서 테이블을 논리적으로 분리하는 게 파티셔닝이라면 샤딩은 다른 물리 DB에 분산하여 저장하는 것이다. 큰 사이즈의 테이블을 여러 개의 DB로 분할함으로써 각각의 데이터베이스에서 처리할 수 있는 양의 효율화를 추구하고 성능 및 확장성의 향상을 목표로 한다.

샤딩의 종류

모듈러 샤딩
- 데이터베이스를 균등하게 분할하기 위해 키의 모듈러 연산을 통해 결정하는 방식
레인지 샤딩
- 키의 범위로 데이터베이스를 분할하는 방식

참고 자료

Tomcat 튜닝

DocumentDB 개요