통계 정보와 코스트 모델

옵티마이저(optimizer)는 통계를 보고 실행 방법마다 비용을 계산해 가장 싼 방법을 고른다. 계산 재료는 행 수, 카디널리티(cardinality), 히스토그램(histogram)이고, 이 재료로 비용을 매기는 규칙인 코스트 모델(cost model)이 계산기다.

테이블·인덱스 통계 정보

• 행 수 추정: 테이블에 행이 몇 건인지의 추정값이다. 테이블 전체를 읽는 풀 테이블 스캔(full table scan) 비용을 계산하는 기준이 된다.
• 카디널리티: 인덱스 칼럼에 든 서로 다른 값의 개수다. 높을수록 한 값에 해당하는 행이 적어 선택도(selectivity)가 좋고, 인덱스로 찾는 쪽이 유리하다.
• 페이지(page) 수: 행 데이터를 담은 클러스터링 인덱스(clustered index)와 보조 인덱스인 세컨더리 인덱스(secondary index)가 각각 몇 페이지를 차지하는지, 즉 크기다. 디스크를 얼마나 읽을지, 곧 I/O(Input/Output, 입출력) 비용을 어림하는 데 쓰인다.

InnoDB 통계는 모든 페이지를 세지 않고 일부 페이지만 표본으로 읽어(sampling) 계산하므로 어림값이다. 그래서 대량 적재·삭제 뒤에는 ANALYZE TABLE로 통계를 다시 계산해 갱신한다. 통계를 디스크에 저장해 두는 영속 통계(persistent statistics, innodb_stats_persistent)가 기본이라 서버를 재시작해도 통계가 유지된다.

히스토그램

히스토그램은 "값이 어떻게 분포했나"의 기록이다. 칼럼 값이 어느 구간에 얼마나 몰려 있는지 나눠 센다. 인덱스가 없는 칼럼은 인덱스 통계만으로 분포를 알기 어려운데, 히스토그램은 이런 칼럼에 대한 동등·범위 조건의 선택도 추정을 보완한다.

ANALYZE TABLE t UPDATE HISTOGRAM ON status;   -- 생성·갱신
ANALYZE TABLE t DROP HISTOGRAM ON status;     -- 삭제

값이 고르게 퍼진 균등 분포면 카디널리티로 충분하다. 하지만 상태값의 99%가 'done'인 것처럼 치우친 분포에서는 히스토그램이 있어야 옵티마이저가 범위 비용을 제대로 본다. 만든 히스토그램 정보는 INFORMATION_SCHEMA.COLUMN_STATISTICS에서 확인한다.

코스트 모델

코스트 모델은 실행 방법의 비용을 I/O 비용(페이지 읽기)과 CPU 비용(행 검사·비교)을 더해 어림한다. 페이지 하나 읽기, 행 하나 비교하기 같은 기본 작업마다 매긴 단가를 코스트 상수(cost constant)라 한다. 상수는 mysql.engine_cost·server_cost 테이블에서 확인·조정한다.

SELECT * FROM mysql.engine_cost;  -- io_block_read_cost 등

코스트 상수는 환경에 맞게 조정할 수 있지만 잘못 바꾸면 전체 쿼리 계획에 영향을 준다. 실제 하드웨어 측정과, 바꾼 뒤 다른 쿼리가 나빠지지 않았는지 확인하는 회귀 검증(regression test) 없이는 손대지 않는다. 먼저 통계·히스토그램·인덱스·쿼리를 확인한다.

면접 포인트

• "통계가 왜 중요해요?" → 비용 계산의 재료. 틀리면 계획이 틀어진다.
• "히스토그램이 뭔가요?" → 값 분포 기록. 치우친 분포의 범위 비용을 정확히.
• "ANALYZE는 언제?" → 대량 변경 후, 계획이 이상할 때 첫 단추.