데이터베이스가 느려지면 대부분의 개발자들은 데이터베이스를 탓합니다. 인덱스(Index)를 더 추가하거나, 데이터베이스를 교체하거나, 쿼리(Query)를 다시 작성하곤 합니다. 하지만 2,800개 이상의 아티클을 인덱싱하고 수백만 건의 페이지 뷰를 처리하는 프로덕션 지식 베이스(Knowledge Base) 전반의 쿼리 성능을 최적화하면서 저는 한 가지를 배웠습니다. 대부분의 느린 데이터베이스는 쿼리 문제가 아니라, 인덱스 전략(Index Strategy)의 문제입니다. 실제로 중요한 것은 다음과 같습니다.

당신에게 없는 인덱스가 가장 느린 쿼리를 만듭니다
가장 흔한 성능 저하 원인은 인덱스가 없는 것이 아닙니다. 바로 복합 인덱스(Composite Index)가 없는 것입니다. 당신에게 category_id, status, published_at 컬럼이 있는 articles 테이블이 있다고 가정해 봅시다. 당신은 각 컬럼을 개별적으로 인덱싱했습니다. PostgreSQL(또는 MySQL)은 테이블 스캔(Table Scan)당 하나의 인덱스만 사용할 수 있습니다. 데이터베이스는 가장 선택도(Selective)가 높은 인덱스 하나를 선택하고, 나머지에 대해서는 전체 스캔(Full Scan)을 수행합니다.

해결책: CREATE INDEX idx_articles_category_status_published ON articles ( category_id , status , published_at DESC );

하나의 복합 인덱스가 세 개의 단일 컬럼 인덱스를 대체하며, 필터링된 쿼리에 대해 10~100배 더 빠를 수 있습니다. 경험 법칙(Rule of thumb): 필터링 조건(WHERE)에 사용하는 컬럼들을 선택도가 가장 높은 것부터 낮은 순서대로 인덱싱하세요. ORDER BY 컬럼은 마지막에 추가합니다.

당신이 가진 인덱스가 오히려 방해가 될 수 있습니다
모든 인덱스는 읽기(Read) 속도를 높이지만 쓰기(Write) 속도는 늦춥니다. 모든 INSERT, UPDATE, 또는 DELETE 작업은 해당 테이블의 모든 인덱스를 업데이트해야 합니다. 저희 지식 베이스의 경우, 14개의 인덱스가 있는 테이블이 있었습니다. 쓰기 작업이 필요 이상으로 4배 더 오래 걸렸습니다. 쿼리 패턴을 감사(Audit)한 결과, 쿼리 플래너(Query Planner)가 전혀 사용하지 않는 7개의 인덱스를 제거했습니다.

사용되지 않는 인덱스 확인하기:
-- PostgreSQL
SELECT schemaname , relname , indexrelname , idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY idx_scan ASC ;

만약 프로덕션 환경에서 스캔 횟수가 0인 인덱스가 있다면, 그것은 아무런 이득 없이 매 쓰기 작업마다 비용을 발생시키고 있는 것입니다.

커버링 인덱스(Covering Index): 비밀 병기
커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼을 포함합니다. 데이터베이스는 테이블에 전혀 접근하지 않고, 인덱스만으로 모든 것을 가져옵니다.

-- 쿼리
SELECT title , slug , created_at FROM articles WHERE status = 'published' ;

-- 커버링 인덱스 (Covering index)
CREATE INDEX idx_articles_covering ON articles ( status ) INCLUDE ( title , slug , created_at );

우리의 기사 목록과 같이 읽기 작업이 많은 (read-heavy) 페이지의 경우, 커버링 인덱스 (covering indexes)를 통해 50ms의 쿼리를 1ms 미만의 조회 (lookups)로 전환했습니다. 데이터베이스는 메모리 내의 B-tree에서 전체 응답을 제공합니다.

눈에 잘 띄지 않는 곳에 숨어 있는 N+1 문제
당신은 쿼리를 최적화했습니다. 인덱스도 완벽합니다. 그런데도 여전히 느립니다. 그렇다면 애플리케이션 계층 (application layer)을 확인하십시오. "데이터베이스가 느리다"는 불만의 가장 흔한 원인은 사실 N+1 쿼리 (N+1 queries)입니다. 즉, 연관된 데이터를 한 번에 하나씩 행 단위로 가져오는 것입니다.

나쁜 예: N+1

articles = db.query(" SELECT * FROM articles WHERE status = 'published' ")
for article in articles :
author = db.query(" SELECT * FROM users WHERE id = ? ", article.author_id)

좋은 예: JOIN을 사용한 단일 쿼리

results = db.query("""
SELECT a.*, u.name as author_name
FROM articles a
JOIN users u ON a.author_id = u.id
WHERE a.status = 'published'
""")

ORM의 즉시 로딩 (eager loading) 기능을 사용하십시오. 개발 단계에서는 모든 쿼리를 로그로 남기십시오. 만약 서로 다른 ID를 가진 동일한 쿼리 패턴이 반복되는 것을 본다면, 그것이 바로 N+1 문제입니다.

최적화를 멈춰야 할 때
조기 최적화 (Premature optimization)는 시간을 낭비합니다. 하지만 측정되지 않은 최적화는 더 많은 시간을 낭비합니다.

인덱스를 추가하기 전에:

• 느린 쿼리에 대해 EXPLAIN ANALYZE를 실행하십시오.
• 대규모 테이블에서 순차 스캔 (sequential scan)이 수행되고 있는지 확인하십시오.
• 쿼리 패턴이 흔한 것인지 확인하십시오 (일회성 관리자 보고서가 아닌지).
• 인덱스가 쓰기 성능 (write performance)에 미치는 영향을 테스트하십시오.

인덱스를 추가한 후에는:

• 다시 EXPLAIN ANALYZE를 실행하여 실제로 인덱스가 사용되는지 확인하십시오.
• 해당 테이블의 쓰기 지연 시간 (write latency)을 모니터링하십시오.
• 인덱스 크기를 확인하십시오. 비대해진 인덱스는 도움이 되기보다 오히려 해가 될 수 있습니다.

진정한 해답
빠른 데이터베이스는 최신 기술에 관한 것이 아닙니다.

그것은 다음과 같은 것들에 관한 것입니다:

• 쿼리 패턴 (Query patterns) 파악하기 — 모든 것을 기록하고, 자주 사용되는 것(hot)을 최적화하기
• 복합 인덱스 (Composite indexes)를 전략적으로 사용하기 — 하나의 좋은 복합 인덱스가 다섯 개의 단일 인덱스보다 낫습니다
• 불필요한 요소 제거하기 — 사용되지 않는 인덱스는 소리 없이 성능을 갉아먹습니다
• 애플리케이션 계층에서 N+1 문제 해결하기 — 어떤 데이터베이스도 잘못된 쿼리 패턴을 구할 수는 없습니다
• 전후 비교 측정하기 — EXPLAIN ANALYZE는 당신의 가장 친한 친구입니다

데이터베이스 성능, 아키텍처 패턴, 그리고 수백만 건의 페이지 뷰로 확장하며 배운 인프라 교훈에 대해 더 깊이 알고 싶다면, 저의 지식 베이스(codcompass.com)에 모두 기록해 두었습니다. 여러분이 겪은 가장 고통스러운 데이터베이스 성능 사례는 무엇인가요? 아래에 남겨주세요 — 저는 무엇이든 겪어보았습니다.