통계(Statistics)

통계(Statistics)

 

검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용하는 것이 통계이다.

 

통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어 진다.

 

Sysindex 테이블의 image형식의 statblog컬럼에 통계정보가 저장된다.

SELECT * FROM SYSINDEXES

 

 

데이터베이스 옵션의 auto create statistics와 auto update statistics는 모든 데이터베이스에 대해 자동으로 ture로 설정되며 이 옵션들이 설정되어 있으면 인덱스뿐만 아니라 인덱스가 없는 컬럼에 대해서도 필요할 경우 자동으로 통계가 만들어 지고 일정 비율(전체행의 약20%)의 데이터가 업데이트 되면 통계도 자동으로 업데이트 된다.

 

밀도(density)는 1/(인덱스 키들의 수)로 정의된다. 즉, 성별은 밀도가 0.5(남자, 여자)이지만 나의 주민등록 번호는 1/5000만 이 된다.(대한민국 인구수를 5천만으로 가정 했을 때).

이때 선택도(selectivity)는 주어진 키 값(또는 키 범위)에 해당하는 행수의 정도를 말하는데 행수가 적을수록 선택도가 높다고 한다. 그러나 전체 밀도가 낮더라도 특정 키에 대한 선택도는 낮을 수도 있다.

 

[통계 보기]

DBCC SHOW_STATISTICS문을 사용하여 통계를 확인 할 수 있다. 실습에서는 AdventureWorks의 [Person.Address]테이블을 사용 한다.

 

• 전체 코드

SELECT * FROM PERSON.ADDRESS   EXEC SP_HELPINDEX [PERSON.ADDRESS]   DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)   SELECT STATEPROVINCEID, COUNT(*) FROM PERSON.ADDRESS GROUP BY STATEPROVINCEID   DBCC SHOW_STATISTICS([PERSON.ADDRESS], IX_ADDRESS_STATEPROVINCEID)

 

[AdventureWorks] 데이터베이스의 [Person.Address] 테이블의 데이터를 확인 한다. 19614건의 데이터가 들어 있는 것을 확인 할 수 있다.

SELECT * FROM PERSON.ADDRESS

 

 

[Person.Address]의 인덱스 정보를 확인 한다.

EXEC SP_HELPINDEX [PERSON.ADDRESS]

 

 

DBCC SHOW_STATISTICS명령어를 이용하여 클러스터 인덱스 [PK_Address_AddressID]통계 정보를 확인 하자.

-- DBCC SHOW_STATISTICS(테이블명, 인덱스명) DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)

 

 

Updated : 최근 통계가 업데이트된 날짜.

Rows : 전체 행 수

Rows Sampled : 샘플링된 행 수 (행 수가 많을 경우 일부를 샘플링하여 사용)

Steps : 히스토그램의 단계 수. 고유 인덱스가 아닐 경우 단계수가 많아지며 최대 200단계 이다.

Average Key length : 컬럼 값의 평균 길이. Int형이므로 4가 표시 된다.

All density : 밀도. 밀도는 Steps의 값과 무관하며 일반적으로 (1/ 인덱스 키들의 수) 이다.

RANGE_HI_KEY : 각 단계(step)에서 샘플링된 최대 값.

RANGE_ROWS : 각 단계에서 RANGE_HI_KEY갑을 제외한 값들의 개수.

EQ_ROWS : 최대값의 개수.

DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들 중 고유한 값의 개수.

 

 

비클러스터 [IX_Address_StateProvinceID]의 통계정보를 확인 하자. 우선 해당 컬럼의 유니크 ROW가 몇 개인지 확인하자.

SELECT STATEPROVINCEID, COUNT(*) FROM PERSON.ADDRESS GROUP BY STATEPROVINCEID   DBCC SHOW_STATISTICS([PERSON.ADDRESS], IX_ADDRESS_STATEPROVINCEID)

 

STATEPROVINCEID의 유니크한 행수는 74개 이다.

 

통계 정보를 확인 하자.

 

All density의 2개의 정보가 있는 것은 키의 조합이 두 가지이기 때문이다. (인덱스가 사용될 때 StateProvinceID가 사용될 수도 있고, StateProvinceID와 AddressID키 둘다 사용할 수도 있다.)

히스토그램 단계는 StateProvinceID 키가 단독으로 사용될 때의 것이다.(복합키에 대한 히스토그램 정보는 없다.)

 

히스토그램 통계를 이용하여 임의 구간에 대한 키 값을 추정 할 수 있다.

만약 심플링된 행수가 전체 행수와 다르다면 그 비율로 다시 한번 더 계산을 해주어야 한다.

 

2012-08-13 / 강성욱 / http://sqlmvp.kr