동블

그래프 뷰

02. Optimizer와 Statistics 상세

9 min read

이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.

  • Optimizer는 무엇을 근거로 실행 계획을 선택하는가?
  • 통계가 오래되면 왜 실행 계획이 나빠지는가?
  • 데이터 분포가 치우친 서비스에서 어떤 문제가 생기는가?

개요

Optimizer는 SQL의 실행 방법을 선택하는 DB의 핵심 구성 요소다. 어떤 인덱스를 쓸지, 어떤 테이블을 먼저 읽을지, 어떤 조인 방식을 사용할지, 정렬을 어떻게 처리할지 결정한다.

Optimizer는 실제 미래를 아는 것이 아니다. 통계와 비용 모델을 바탕으로 “이 방법이 싸 보인다”고 판단한다. 그래서 통계가 틀리거나 데이터 분포가 치우치면 좋은 SQL과 인덱스가 있어도 나쁜 계획이 나올 수 있다.

원리

DB는 테이블의 행 수, 컬럼 값 분포, NULL 비율, 고유 값 개수, 인덱스 상태 같은 통계를 유지한다. Optimizer는 이 통계를 사용해 조건이 얼마나 많은 행을 걸러낼지 추정한다.

SELECT id, status, created_at
FROM orders
WHERE status = 'FAILED'
ORDER BY created_at DESC
LIMIT 20;

만약 FAILED가 전체 주문의 0.1%라면 status 조건은 매우 선택적이다. 반대로 PAID가 90%라면 status = 'PAID'는 많은 행을 읽는다. 같은 컬럼 조건이라도 값에 따라 계획이 달라질 수 있다.

통계가 틀릴 때

통계가 오래되면 Optimizer는 실제 데이터와 다른 판단을 한다.

예를 들어 신규 서비스에서 orders가 1만 건일 때 통계가 잡혔고, 며칠 뒤 1천만 건이 되었는데 통계가 갱신되지 않았다면 DB는 여전히 작은 테이블처럼 계획을 세울 수 있다.

PostgreSQL에서는 autovacuum과 analyze가 통계 갱신에 관여한다.

ANALYZE orders;

MySQL에서도 InnoDB 통계와 histogram 같은 기능이 계획 선택에 영향을 준다.

운영에서 개발자가 임의로 통계 명령을 실행하기보다, 인프라/DBA와 함께 통계 갱신 정책과 영향 범위를 확인하는 편이 좋다.

데이터 분포의 함정

데이터가 균등하다는 가정은 자주 틀린다.

  • 대부분의 주문은 PAID이고 일부만 FAILED다.
  • 게시글 대부분은 특정 인기 게시판에 몰린다.
  • 한 명의 대형 고객이 전체 데이터의 절반을 가진다.
  • 이벤트 기간에 특정 날짜 데이터가 폭증한다.

다음 쿼리는 회원마다 비용이 다를 수 있다.

SELECT id, created_at
FROM orders
WHERE member_id = ?
ORDER BY created_at DESC
LIMIT 20;

일반 회원은 주문 10건이지만, 법인 회원은 주문 100만 건일 수 있다. 평균 행 수만 보고 계획을 세우면 특정 고객 API만 느려지는 일이 생긴다.

파라미터와 계획

Prepared statement나 ORM 환경에서는 파라미터 값에 따라 최적 계획이 달라질 수 있다. 어떤 값은 인덱스가 좋고, 어떤 값은 전체 스캔이 나을 수 있다.

SELECT id
FROM orders
WHERE status = ?;

status = 'FAILED'와 status = 'PAID'는 같은 SQL 모양이지만 선택도가 다르다. 이 차이를 무시하면 특정 파라미터에서만 느린 API가 생긴다.

백엔드 장애 대응에서는 “이 API가 느리다”보다 “어떤 파라미터에서 느리다”를 확인해야 한다. 특정 회원, 특정 게시판, 특정 날짜 범위가 문제일 수 있다.

PostgreSQL에서는 pg_stats로 컬럼 통계의 단서를 볼 수 있다.

SELECT attname,
       n_distinct,
       most_common_vals,
       most_common_freqs,
       histogram_bounds
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders'
  AND attname IN ('status', 'member_id', 'created_at');

여러 컬럼이 서로 강하게 연관되어 있으면 단일 컬럼 통계만으로 추정이 빗나갈 수 있다. PostgreSQL의 extended statistics나 MySQL histogram 같은 기능은 이런 추정을 개선하는 데 도움이 될 수 있지만, 생성과 유지 정책은 DBA와 함께 정해야 한다.

실전 팁

  • 느린 쿼리는 대표 바인딩 값 여러 개로 실행 계획을 본다.
  • 예상 rows와 실제 rows 차이가 크면 통계와 데이터 분포를 의심한다.
  • 대형 고객, 인기 게시판, 이벤트 날짜처럼 치우친 데이터 케이스를 테스트 데이터에 넣는다.
  • 배포 직후 느려졌다면 코드 변경뿐 아니라 데이터 증가와 통계 갱신 상태도 본다.
  • 인프라/DBA와 대화할 때 “평균”과 “상위 1% 케이스”를 구분해서 전달한다.
  • 특정 파라미터만 느리면 해당 값의 데이터량, 분포, 실행 계획을 따로 캡처한다.
  • rows estimate와 actual rows 차이가 크면 통계 갱신, histogram, extended statistics, 조건식 형태를 함께 검토한다.
  • 대형 테넌트나 인기 게시판은 테스트 데이터에도 의도적으로 넣어 skew를 재현한다.

위험 신호!

  • 모든 회원이 비슷한 데이터량을 가진다고 가정한다.
  • 개발 테스트 데이터가 균등 분포라 운영 치우침을 재현하지 못한다.
  • 실행 계획의 estimated rows와 actual rows 차이가 큰데 넘어간다.
  • 통계 갱신과 autovacuum/analyze 상태를 아무도 모른다.
  • 특정 파라미터에서만 느린 문제를 전체 API 문제로 뭉뚱그린다.
  • 여러 컬럼이 연관된 조건인데 단일 컬럼 선택도만 보고 인덱스를 설계한다.
  • 장애 원인이 특정 대형 고객인데 평균 응답 시간만 보고 놓친다.

확인 질문

  • Optimizer가 실행 계획을 선택할 때 사용하는 핵심 근거는 무엇인가?
    • 테이블과 인덱스 통계, 데이터 분포 추정, 비용 모델, 조건 선택도 등을 사용한다.
  • 통계가 오래되면 왜 문제가 되는가?
    • Optimizer가 실제 행 수와 분포를 잘못 추정해 비효율적인 인덱스나 조인 순서를 선택할 수 있기 때문이다.
  • 운영 장애에서 파라미터별 실행 계획을 봐야 하는 이유는 무엇인가?
    • 같은 SQL이라도 특정 값의 데이터량과 선택도에 따라 비용과 계획이 크게 달라질 수 있기 때문이다.
  • estimated rows와 actual rows 차이가 클 때 의심해야 할 것은 무엇인가?
    • 통계 오래됨, 데이터 분포 치우침, 컬럼 간 상관관계, 파라미터 선택도 차이, 조건식이 통계를 활용하기 어려운 형태인지 확인해야 한다.

참고 문서

관련 문서