8.인덱스 - B-Tree Index의 가용성과 효율성

이번 포스트에서는 B-Tree Index의 가용성과 효율성에 대해 알아보도록 하겠습니다.

 

비교 조건의 종류와 효율성

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용되는 조건이 동등 비교("=", "IN")인지 또는 범위 조건(">", ">=", "<", "<=")인지에 따라 각 인덱스 컬럼의 활용 형태와 효율이 달라집니다.

 

mysql> SELECT * FROM dept_no
	WHERE dept_no = 'd002' AND emp_no >= 10114;

위의 쿼리에서 dept_no와 emp_no를 두 가지 다중 컬럼 인덱스를 만들고 두 인덱스 끼리 어떤 차이가 있는지 알아보도록 하겠습니다.

케이스 A : INDEX(dept_no, emp_no), 케이스 B : INDEX(emp_no, dept_no)

 

케이스 A는 dept_no = 'd002'  AND emp_no >= 10114의 레코드를 찾고 인덱스를 쭉 읽으면 되지만

케이스 B는 emp_no >= 10114의 레코드를 찾고 찾은 모든 레코드에서 레코드가 dept_no = 'd002'인지 비교하는 과정을 거쳐야 합니다. (이 부분은 제가 이해한게 확실하지 않고 헷갈리는 내용이 있어서 스터디시간에 스터디원들과 함께 이야기 해보고 추후에 더 정확한 내용으로 업데이트 하겠습니다.)

케이스 A(왼쪽) 케이스 B(오른쪽)

케이스 A처럼 모든 인덱스가 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 하고

케이스 B는 emp_no 컬럼은 작업 범위 결정 조건이고 dept_no 컬럼은 작업의 범위를 줄이지 못하고 단순히 필터링만 하는 조건을 필터링 조건 또는 체크 조건이라고 합니다.

 

B-Tree Index 특성상 작업 범위 결정 조건으로 사용할 수 없는 경우는 다음과 같습니다.

1. NOT_EQUAL로 비교된 경우 ("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
2. LIKE '%??' (앞부분이 아닌 뒷부분부터 일치하는 것을 찾을 때)형태로 문자열 패턴이 비교되는 경우
3. 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교되는 경우
4. NOT_DETERMINISTIC 속성의 스토어로 함수가 비교조건에 사용된 경우
5. 데이터 타입이 서로 다른비교를 할 경우 (인덱스 컬럼의 타입이 변환해야 비교가 가능한 경우)

INDEX(column_1, column_2, column_3, ......, column_n)

위와 같이 인덱스가 만들어진 경우 어떤 경우에 작업 범위 결정 조건으로 사용이 가능하고 불가능 한지 알아보도록 하겠습니다.

 

작업 범위 결정 조건으로 사용하지 못 하는 경우

• column_1 컬럼에 대한 조건이 없는 경우
• column_1 컬럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우

작업 범위 결정 조건으로 사용할 수 있는 경우

• column_1 ~ column(i - 1) 컬럼까지 동등 비교 조건("=", "IN")을 사용하는 경우
• column_i 컬럼에 동등 비교 조건, 크다 작다 형태(">", "<"), LIKE 좌측 일치 패턴(LIKE '규난%')을 사용하는 경우

이것 이외에 작업 범위 결정 조건으로 인덱스를 사용할 수 있는 방법은 많지만 대표적으로 이 정도만 기억하고 있어도 효율적인 쿼리를 작성할 수 있습니다.