(mariadb/mysql) 결합 인덱스

인덱스를 분류할때 저장 방식에따라 클러스터 인덱스와 넌클러스터 인덱스로 나눌 수 있습니다.

그리고 사용한 컬럼 갯수에 따라 단일 인덱스와 결합 인덱스로 나눌 수 있습니다.

 

이번에 살펴보고자 하는 내용은 결합인덱스의 활용입니다.

 

where 절에서의 인덱스 활용을 먼저 살펴보겠습니다.

Short-circuit evaluation 이라고 들어보신적 있으신가요? 프로그래밍 언어에서 대부분 지원하는 기능으로 판정식에서 뒤에 조건을 판단하지 않고도 결과를 도출 할 수 있다면 연산을 생략하는 것 을 뜻합니다.

 

( a == b && c == d ) 라는 조건이 존재할때 a == b 와 c == d 두가지의 조건중에 한개라도 false 가 된다면 결과는 false 가 됩니다.

 

마찬가지로 ( a == b || c == d)라는 조건이 존재한다면 a == b 혹은 c == d 중에 한가지가 true가 된다면 결과는 true가 됩니다.

 

이때 ( a == b && c == d ) 에서 a == b가 false라면 뒤에 c == d를 판단하지 않고 false로 판정하는 것이 Short-circuit evaluation 입니다.

 

sql의 where절 조건 역시 Short-circuit evaluation이 적용됩니다.

where col1 = '1' and col2 is null 이란 조건에서 col1 이 '1'이 아니라면 뒤의 col2 is null을 확인하지 않고 false가 되는것 입니다. 그런데, 여기서는 한가지 예외 상황이 있습니다. 인덱스를 활용한 조건이 있다면 그것을 최우선적으로 판단한다는 것 입니다. 여기까지 살펴보고 다시 where절에서 결합 인덱스 활용을 알아보겠습니다.

 

col1~col10 열개의 컬럼을가진 테이블 A가 존재합니다.

테이블 A는 col1, col2, col3 순으로 세개의 컬럼을 사용한 결합인덱스가 존재합니다.

이때 경우에 따라 인덱스를 활용할 수 있는 조건과 그렇지 않은 조건을 확인해보면 다음과 같습니다.

 

1. where col1 = '1' and col2 = '2' and col3 = '3' (O)

2. where col1 = '1' and col3 = '3' and col2 = '3' (O)

3. where col1 = '1' and col2 > '2' and col3 = '3' (X)

4. where col3 = '3' and col2 = '2' or col1 = '1' (X)

 

1번과 2번 예제는 세개의 컬럼을 동등 비교하는 것 이기에 인덱스를 정상적으로 사용할 수 있습니다.

3번은 인덱스 순서상 중간에서 범위 탐색이 필요하기에 뒤에 col3 = '3' 계산시 인덱스 활용이 불가하고 각 행 하나하나 비교가 수행되어야 합니다.

4번은 or 연산 사용 되었기에 앞과 뒷 부분에서 비교연산이 각각 수행되어야 함으로 인덱스 활용이 불가능합니다.

그리고 where절에서는 결합인덱스의 순서와 상관없이 인덱스 사용이 가능합니다. 그렇기에 Short-circuit evaluation이 적용될수 없으며 예외적으로 인덱스가 사용되는 조건이 가장먼저 계산될 수 밖에 없습니다.

 

 

다음은 group by 와 order by 에서의 결합인덱스 활용입니다.

group by 그리고 order by에서도 인덱스를 활용할 수 있습니다. 단 where절과는 다르게 group by 혹은 order by 에서는 항상 결합인덱스의 순서가 지켜져야 합니다.

group by col2, col1, col3 은 인덱스를 사용할 수 없고 group by col1, col2는 인덱스를 사용할 수 있습니다.

또한 col1부터 순차적으로 나열되어야 하며 결합인덱스 순서상 뒷 컬럼들은 없어도 무방합니다.

 

인덱스가 저장될 모습을 생각해보면 당연한 이야기 입니다. col1기준으로 정렬된 상태에서 col2기준으로 정렬되고 그 상태에서 col3기준으로 정렬될 테니 col2, col1 순으로 인덱스를 사용할 수는 없는 것 입니다.

 

단 여기서 예외가 존재하는데, group by 혹은 order by 에서 col2, col3을 나열한뒤 where절에서 col1을 사용한다면 이는 인덱스를 활용할 수 있습니다.

흔히들 착각하는 내용이 where절은 where절 대로 인덱스를 사용하고 order by 나 group by는 그것대로 인덱스를 사용한다고 생각 할 수 있는데 실제로는 같이 묶어서 한번의 인덱스 사용으로 질의를 수행할 수 있습니다.

 

 

데이터가 많으면 많을수록 꼭 필요한 인덱스.

만드는 것 만큼이나 정말 내가 작성한 질의가 효율적으로 인덱스를 사용하고 있는지 자세히 살펴봐야 할 것 같습니다.