[MySQL] B-Tree 인덱스 -4

이전 글에 이어서 MySQL의 B-Tree 인덱스에 대해 알아보겠습니다.

[MySQL] B-Tree 인덱스 - 1

[MySQL] B-Tree 인덱스 - 2

[MySQL] B-Tree 인덱스 - 3

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

쿼리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지를 다룹니다. 그래야 쿼리 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있습니다.

비교 조건의 종류와 효율성

비교 조건(=, <)에 따라 인덱스 칼럼의 활용 형태와 효울이 달라집니다. 다음 예제를 살펴봅시다.

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

이 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정합니다.

 

A) INDEX (dept_no, emp_no)

B) INDEX (emp_no, dept_no)

 

A의 경우 "dept_no='d002' AND emp_no >= 10114"인 첫 번째 레코드를 찾고, 이후에는 dept_no가 'd002'가 아닐때까지 인덱스를 읽으면 됩니다. 조건의 맞는 레코드가 5개라면 5개의 인덱스만 읽습니다.

 

B의 경우 "emp_no>=10114 AND dept_no='d002'"인 레코드를 찾고 이후 모든 레코드에 대해 dept_no가 'd002'인지 비교하며 필터링하는 과정이 필요합니다.

 

따라서 다중 칼럼 인덱스의 정렬 방식은 쿼리의 조건에 따라 적절히 활용될 수 있지만 작업범위를 좁히지 못하는 경우 아무런 도움이 되지 않습니다.

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬돼 있다는 것입니다. 예를 들어 AAA, AAB, ABA 처럼 정렬됩니다. 이는 다중 칼럼 인덱스에서도 적용됩니다. 다음 두 케이스를 살펴봅시다.

 

// INDEX(first_name)
mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 사용할 수 없습니다. 그 이유는 first_name 칼럼에 대한 인덱스가 왼쪽부터 한 글자씩 비교하여 정렬돼 있기 때문입니다. 조건절의 '%mer'는 왼쪽 부분이 고정되지 않았기 때문에 인덱스를 사용할 수 없습니다.

 

// INDEX(dept_no, emp_no)
mysql> SELECT * FROM dept_no WHERE emp_no >= 10144;

인덱스는 dept_no, emp_no 순서로 정렬됩니다. 따라서 dept_no 조건 없이 emp_no 조건으로만 검색을 하면 dept_no 에 대해 검색 범위를 좁힐 수 없기 때문에 인덱스를 효율적으로 사용할 수 없습니다.

 

인덱스 가용성에 대해 WHERE 조건절에 대해서만 언급했지만 GROUP BY 절이나 ORDER BY 절에도 똑같이 적용됩니다.

가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없습니다. 다시말해 다음 조건에서는 작업 범위를 좁힐 수 없습니다.

 

1. NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)

2. 앞부분이 아닌 뒷부분 일치 형태로 문자열 패턴이 비교된 경우(LIKE "&?")

3. 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우

    ... WHERE SUBSTRING(column, 1, 1) = 'X'

4. NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용되는 경우(RAND(), UUID())

5. 데이터 타입이 서로 다른(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)

6. 문자열 데이터 타입의 콜레이션이 다른 경우

 

다른 일반적인 DBMS는 NULL 값이 인덱스에 저장되지 않습니다. 하지만 MySQL에서는 NULL 값도 인덱스에 저장됩니다. 따라서 다음과 같은 WHERE 조건도 작업 범위 결정 조건으로 사용될 수 있습니다.

mysql> ... WHERE column IS NULL;

 

다중 칼럼의 경우 어떤 경우에 인덱스를 사용할 수 없는지 알아봅시다. 다음과 같은 인덱스가 있다고 가정했을 때

INDEX ix_test (c_1, c_2, c_3, ..., c_n)

• 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
  • c_1 칼럼에 대한 조건이 없는 경우
  • c_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
• 작업 범위 결정 조건으로 인덱스를 사용하는 경우
  • c_1 ~ c_(i - 1) 칼럼까지 동등 비교 형태로 비교
  • c_i 칼럼에 대해 다음 연산자 중 하나로 비교
    • 동등 비교
    • 크다 작다 형태
    • LIKE로 좌측 일치 패턴

위 두 조건을 만족하는 쿼리는 c_1 부터 c_(i - 1) 칼럼을 작업 범위 결정 조건으로 사용하고 c_i 칼럼의 조건을 체크 조건으로 사용합니다.