통계 정보와 힌트

2. 핵심 원리

2.1 옵티마이저가 보는 통계

옵티마이저는 표와 컬럼에 대해 아래 정보를 저장해 두고 씁니다.

이 통계로 조건별 예상 행 수를 계산합니다. 행 수가 5,000 이고 컬럼의 고유 값이 50 개이면 emp_id = 7 은 5,000 나누기 50 으로 100 행쯤이라고 봅니다. 값이 고르게 퍼져 있다는 균등 가정입니다.

균등 가정은 값이 정말 고르면 잘 맞습니다. 치우친 컬럼에서는 크게 틀립니다. 그 틀림을 줄이는 것이 히스토그램입니다.

2.2 값이 치우치면 균등 가정이 틀린다

status 가 DONE, WAIT, REFUND, CANCEL 네 값이면 균등 가정은 값 하나가 25% 라고 봅니다. 5,000 행이면 CANCEL 도 1,250 행이라고 계산합니다. 실제로 CANCEL 이 50 행뿐이라면 예상이 25 배 부풀려진 것입니다.

예상 1,250 행이면 옵티마이저는 인덱스로 1,250 번 표를 오가는 것보다 전체 읽기가 낫다고 판단할 수 있습니다. 실제로는 50 행이라 인덱스가 훨씬 빨랐을 텐데 계획이 반대로 갑니다. 반대로 DONE 은 95% 인데 25% 로 보면 인덱스를 잘못 고릅니다.

2.3 히스토그램

히스토그램은 컬럼 값의 분포를 구간이나 값별 개수로 저장한 통계입니다. 히스토그램이 있으면 옵티마이저는 status = 'CANCEL' 이 1% 라는 것을 압니다. 균등 가정 대신 실제 비율을 쓰게 됩니다.

고유 값이 적고 치우친 컬럼이 히스토그램의 주 대상입니다. 고유 값이 수만 개인 컬럼은 구간으로 뭉치므로 정밀도가 떨어집니다.

2.4 통계 수집 명령

자동 수집이 기본이라도 대량 적재 직후에는 아직 수집 전이라 통계가 낡아 있기 쉽습니다. 그래서 배치의 마지막에 통계 수집을 넣는 경우가 많습니다. 아래는 문법 검토만 한 예입니다.

-- Oracle · Tibero
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDERS');
END;
/

-- MySQL
ANALYZE TABLE orders;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

-- MSSQL
UPDATE STATISTICS orders;
UPDATE STATISTICS orders WITH FULLSCAN;

Oracle 의 ownname => USER 는 현재 접속 사용자의 스키마입니다. MSSQL 의 FULLSCAN 은 표 전체를 읽어 정확하지만 오래 걸리고, 생략하면 샘플링합니다. MySQL 의 히스토그램은 8.0 부터이고 버킷 수를 WITH n BUCKETS 로 정합니다.

2.5 통계가 낡는 순간

통계는 수집한 시점의 사진입니다. 그 뒤에 행이 크게 늘거나 값 분포가 바뀌어도 다시 수집하기 전까지는 옛 사진이 그대로 쓰입니다. 특히 아래 경우가 위험합니다.

• 배치가 한 번에 수십만 건을 넣거나 지운 직후
• 특정 값(예: 취소 상태)이 한꺼번에 몰려 들어온 직후
• 빈 표에 적재를 시작해 통계가 0 행으로 남은 경우

자동 수집은 보통 변경 비율이 일정 이상일 때 정해진 시간에 돌아갑니다. 적재가 끝나자마자 이어지는 조회는 옛 통계를 볼 수 있습니다.

2.6 바인드 변수와 치우친 분포

바인드 변수를 쓰면 값이 바뀌어도 같은 계획을 재사용합니다. 값이 고르면 이득이지만 치우친 컬럼에서는 문제가 됩니다. status = :s 에서 처음 CANCEL 로 계획을 짜면 인덱스 계획이 굳고, 이후 DONE 으로 실행해도 그 계획을 쓸 수 있습니다.

MySQL 은 이 표에서 뺐습니다. 이 레슨의 확인된 사실 범위 밖이라 필요하면 버전 문서를 확인합니다.

바인드 변수 자체는 실무 02 동적 검색(바인드 변수)에서 다뤘습니다. 여기서는 치우친 컬럼일수록 값에 따라 좋은 계획이 다르다는 점만 기억합니다.

2.7 힌트

힌트는 옵티마이저에게 이 계획을 쓰라고 지시하는 주석이나 절입니다. 통계로 고른 계획을 사람이 덮어씁니다.

MySQL 인덱스 힌트는 FROM 의 표 이름 뒤에 씁니다. MySQL 옵티마이저 힌트 /*+ ... */ 는 5.7.7 부터이고 JOIN_ORDER 같은 일부는 8.0 부터입니다. MSSQL 은 2016 부터 쿼리 저장소(Query Store)로 검증된 계획을 고정할 수도 있습니다.

핵심

통계를 먼저 고치고, 힌트는 마지막 수단으로 씁니다. 힌트는 데이터가 바뀌어도 계획을 그대로 고정하므로 지금은 맞아도 몇 달 뒤에 가장 느린 계획이 될 수 있습니다.