DB 선택도와 카디널리티 (+옵티마이저 통계 정보)

프로젝트를 진행하던 중, 속도가 느린 쿼리들을 실행계획을 분석하여 추출한 경험이 있다.

실행계획에서 중요한 것은 올바른 인덱스를 타고 있는지, Cost가 비정상적으로 높은지 등을 확인했다.

 

해당 작업을 진행하면서  Cost는 어떤 방식으로 계산되는지 궁금하여 찾아본 것을 여기에 정리하고자 한다.

옵티마이저의 통계 정보의 경우 각각을 외우기보다는 실행계획에서의 Cost가 어떠한 값을 기반으로 계산된 것인지 참고하는 정도로 러프하게 생각해야 할 것 같다.

 

선택도와 카디널리티

우선, 옵티마이저가 처음으로 계산하는 카디널리티가 무엇인지 선택도와 함께 알아보자.

 

1.  선택도 (Selectivity)

데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표이다.
선택도는 낮을수록 인덱스 설정에 좋은 컬럼이다.
선택도 = (컬럼의 특정 값의 레코드 수 / 테이블의 총 레코드 수) * 100

선택도는 5~10%가 적당하다.

 

+) 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식은 선택도 = 1 / Distinct Value 개수 = 1 / num_distinct

 

2. 카디널리티 (Cardinality)

카디널리티는 컬럼에 사용되는 값의 다양성 정도를 의미한다. 값의 중복이 적을수록 카디널리티는 높다고 볼 수 있다.
즉, 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼이다.

카디널리티 = 선택도*전체레코드수 
✅ start_at 컬럼은 '2023-08-01 08:00:00' 과 같은 DATETIME값으로 저장되고 중복되는 경우가 적으므로 카디널리티가 높다.

 

 

옵티마이저의 통계정보 활용

옵티마이저는 선택도 계산을 시작으로 카디널리티를 계산한다. 또한, 그만큼의 데이터를 액세스하는데 드는 비용을 계산해서 테이블 액세스 방식, 조인 순서, 조인 방식 등을 결정하게 된다.

이를 위해서, 통계정보를 수집하는 주기와 샘플링 비율 등을 잘 결정해야 한다.

 

통계정보의 종류는 오브젝트 통계(테이블, 인덱스, 컬럼(히스토그램 포함))와 시스템 통계가 있다.

 

 

각각이 어떤 정보를 포함하는지 러프하게 이해하는 정도로만 알아보자.

 

오브젝트 통계 항목

 

1. 테이블 통계 항목

주요한 테이블 통계항목으로는

1. 테이블에 저장된 총 레코드 수
2. 테이블 블록 수
3. 레코드 당 평균 Bytes
4. 샘플링한 레코드 수
5. 통계정보 수집일시

 

2. 인덱스 통계 항목

주요한 인덱스 통계항목으로는

1. 인덱스 Root에서 Leaf 블록에 도달하기 직전까지 읽게되는 블록의 수
2. 인덱스 리프 블록 총 개수
3. 인덱스에 저장된 레코드 개수
4. 인덱스 키값의 조합으로 만들어지는 값의 종류 개수
5. 인덱스 키 값을 모두 "=" 조건으로 조회할 때 읽게 될 리프 블록 개수
6. 인덱스 키 값을 모두 "=" 조건으로 조회할 때 읽게 될 테이블 블록 개수
7. 인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도.

 

3. 컬럼 통계 항목

주요한 컬럼 통계항목으로는

1. 컬럼 값의 종류 개수
2. "=" 조건으로 검색할 때의 선택도를 미리 구해 놓은 값
3. 컬럼 평균 길이
4. 최소 값
5. 최대 값
6. 값이 NULL인 레코드 수

등치 조건에 대한 선택도를 구할때는 Distinct Value를 통계정보 수집 과정에서 정확하게 구하는 것이 중요해진다.

하지만, 데이터 분포가 균일하지 않은 컬럼에 대해서는 히스토그램 데이터를 추가적으로 활용한다.
컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해놓은 데이터를 히스토그램 데이터를 사용하게 된다.

 

시스템 통계 항목

 

시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이다.

주요 항목으로는

1. CPU 속도
2. 평균 Single Block I/O 속도
3. 평균 Multi Block I/O 속도
4. 평균 Multi Block I/O 개수
5. I/O 서브시스템의 최대 처리량
6. 병렬 Slave의 평균적인 처리량

 

 

 

이런식으로 오브젝트 통계와 시스템 통계를 종합하여 Cost를 계산하게 된다.

위에서 말했듯이 각각을 전부 외우는 것은 힘들고 실행계획에서의 Cost가 어떠한 것을 반영한 비용인지 정도로 이해하면 될 것 같다.