인덱스의 통계적 상관관계

인덱스와 통계적 상관관계

 

개요

인덱스와 카디널리티와 선택도에 대한 관계를 알아보고자 테스트를 하고자 한다.

 

요인(factor)

- 선택도 (X1)

- 카디널리티 (X2)

- 검색 범위 (X3)

- 인덱스 개수 (X4)

 

테스트 가설

n  Y(수행시간) = aX1 + bX2 + cX3 + dX4

단 계수에 대한 가중치나 차등을 둘 것이 아니면 1로 생각한다.

예를 들면 아래 규칙에 따라 가중치를 계산한다면 

1.     선택도에 따라

A.     1 / 0.5 / 0.3 / 0.1 / 0.01

2.     카디널리티에 따라

A.     10000 / 100000 / 1000000

3.     검색 범위에 따라

A.     90% / 70% / 50% / 30% / 10% / 1%

4.     인덱스 개수

A.     1개, 2개, 3개, 4개

경우에 따라 결과에 영향을 미치는 차이가 있다는 설계를 하지 않으면 계수에 대한 영향은 각각 같다

모든 경우를 테스트 하는 것은 아니고 카디널리티에 대해서 구분하고자 한다.

 

테스트 Case

1.     카디널리티를 낮은 -> 높은 순서로 인덱스를 구성

2.     카디널리티를 높은 -> 낮은 순서로 인덱스를 구성

테이블 형태

전체 Row는 약 1700만건으로 생성하고 각각의 카디널리티를 구해 본다

인덱스를 2가지 형태로 생성

첫 번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은 순에서 높은 순 (중복도가 높은 순에서 낮은순으로) 으로, 두번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은 순에서 낮은 순 (중복도가 낮은 순에서 높은순으로) 으로 생성

select SQL_NO_CACHE *               -- 쿼리 Cache를 사용하지 않음

from salaries

use index (IDX_SALARIES_INCREASE)  -- 옵티마이저에서 인덱스 사용을 변경하지 않도록 강제로 지정

where from_date = '1998-03-30'

and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')

and is_bonus = true;

 

select SQL_NO_CACHE *

from salaries

use index (IDX_SALARIES_DECREASE)

where from_date = '1998-03-30'

and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')

and is_bonus = true;

 

테스트 방법

2가지 쿼리에 대해 각각 10회씩 진행

 

테스트 결과

	IDX_SALARIES_INCREASE	IDX_SALARIES_DECREASE
1	110ms	46.9ms
2	89.5ms	24.6ms
3	95.4ms	38.1ms
4	85.6ms	29.3ms
5	83.6ms	29.3ms
6	85.2ms	38.2ms
7	59.4ms	26.1ms
8	64.2ms	29.4ms
9	93.7ms	25.7ms
10	102ms	35.4ms
평균	86.86ms	32.3ms

 

결과 요약

성능 수치로 봤을 때 카디널리티가 높은 순에서 낮은 순으로 구성하는 게 성능이 더 뛰어나다