데이터베이스 분포도(Database Selectivity)

DB 공부를 하다가 분포도라는 개념을 보게 되었습니다. 특정 컬럼의 데이터가 테이블에 평균적으로 분포 되어 있는 정도를 말합니다. 그리고 분포도를 구하는 산식도 접하게 되었습니다. 그러다 의문점이 생겨 그것을 풀어보고자 써본 포스팅입니다. :D

Selectivity(분포도)

DB에서 "분포도가 좋다."라 표현하는 것은 해당 컬럼의 유니크한 데이터 종류가 많다라고 이해하시면 될 것 같습니다. 산식은 1/해당 컬럼의 distinct 데이터 개수 * 100입니다. 즉, 분포도 값이 낮으면 좋다라고 표현합니다. 인덱스로 적정한 분포도는 10%~15%입니다. 한 가지 예를 들자면,

select (1/cnt)*100
from (
	select count(distinct code) cnt /* select 9 rows */
	from XXX
) a

만약 위에서 XXX 테이블의 code 컬럼 값이 9개가 나왔다고 하면 1/9*100 = 11.1%라는 분포도가 나옵니다.

하지만 해당 산식으로 낮은 값이 나왔다고 해서 반드시 인덱스로 구성하기에 좋은 것은 아닙니다. 예를 들면 아래와 같은 경우입니다. 위에 XXX 테이블에서 code 컬럼 값의 데이터 비율은 아래와 같습니다.

╋━━╋━━╋━━━━━╋━━╋  * code       : 코드 이름
┃code┃Rows┃Total Rows┃Per ┃  * Rows       : 해당 코드값을 가진 Row 수(만 단위)
╋━━╋━━╋━━━━━╋━━╋  * Total Rows : 테이블 전체 Row 수(만 단위)
┃A   ┃10  ┃1000      ┃1   ┃  * Per        : (Rows / Total Rows) * 100
┃B   ┃10  ┃1000      ┃1   ┃                 해당 코드값을 가진 Row 비율
┃C   ┃800 ┃1000      ┃80  ┃
┃D   ┃10  ┃1000      ┃1   ┃
┃E   ┃30  ┃1000      ┃3   ┃
┃F   ┃40  ┃1000      ┃4   ┃
┃G   ┃10  ┃1000      ┃1   ┃
┃H   ┃20  ┃1000      ┃2   ┃
┃I   ┃70  ┃1000      ┃7   ┃
╋━━╋━━╋━━━━━╋━━╋
┃Tot ┃1000┃-         ┃100 ┃
╋━━╋━━╋━━━━━╋━━╋
┃Avg ┃111 ┃1000      ┃11.1┃
╋━━╋━━╋━━━━━╋━━╋

위 표는 대략 1000만건이 있는 테이블이라 가정하고 만든 code 데이터 비율입니다. 예를 들어 A라는 코드 값을 가진 Row는 10만개고 전체 Row는 1000만개인거죠. 그리고 A 값을 가진 Row의 비율은 1%라는 뜻입니다.

분포도 산식의 다른 방법은 데이터별 평균 Rows / 테이블 전체 Rows * 100입니다. 위 표에서 Avg라 표시한 것이 해당 산식으로 나온 값이 되겠습니다. SQL(MariaDB)로 표현하자면 아래처럼 되겠습니다.

select ((avg(tot)/(select count(1) from XXX))*100) selectivity
from(
	select count(code) tot
	from XXX
	group by code
) a

분포도 산식에 의해 11.1%가 나왔고 이것은 인덱스로 적정한 분포도라 가정한 뒤에 code 컬럼으로 인덱스를 구성했다고 가정합니다. 만약 A나 B라는 코드값을 조회할때는 빠를지도 모릅니다. 하지만 C라는 값을 조회하는 순간 전체 Row의 80%에 해당하는 Row(800만)를 조회 해야하는 상황이 옵니다. 즉, 분포도는 괜찮지만 데이터 분포의 불균형이 심하다면 인덱스를 만들지 않는게 나을 수도 있습니다.

분포도 산식은 예외가 있을 수도 있다는 것입니다. (--;;) 다시 말해서, 지금 소개한 분포도 산식 결과는 컬럼 내의 값들이 일정하다는 가정 하에 만들어진 것입니다.

분포도가 좋다라고 표현하는 것은 분포도의 값이 낮으면서 데이터가 일정하게 분포 되어 있다라고 이해하면 좋을 것 같습니다.

Closing Remarks

DB도 깊숙히 파고들수록 뭔가 맛(?)이 있습니다. :3 잘하고 싶어요. 저도 DB는 말짱 꽝이라서 틀린 것이 있을 수 있습니다. 과감한 지적질이 필요합니다. TT