search

B-Tree index의 가용성과 효율성

쿼리의 where 조건, group by, order by 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다.

hashtag
비교 조건의 종류와 효율성

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

select * from dept_emp
where dept_no='d002' and emp_no >= 10114;

dept_emp 테이블에 컬럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정하자.

  1. case A: INDEX (dept_no, emp_no)

    • 이 경우의 인덱스는 dept_no='d002' and emp_no >= 10114인 레코드를 찾고, 이후에는 dept_no가 ‘d002’가 아닐 때까지 인덱스를 쭉 읽으면 된다.

    • 조건을 만족하는 레코드가 5개라면, 이를 찾는데 5번의 비교 작업만 수행하므로 상당히 효율적인 인덱스를 이용한 것이다.

  2. case B: INDEX (emp_no, dept_no)

    • A와 달리 우선 emp_no ≥ 10144 and dept_no=’d002’ 조건에 해당하는 레코드를 찾고, 이후에 모든 레코드에 대해 dept_no=’d002’인지 비교하는 과정을 거친다.

    • 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하는 작업을 ‘필터링’이라고도 한다.

그림만 봐도 첫 번째 케이스가 더 효율적이라고 느껴진다. 🤔 그럼, 왜 case A가 더 효율적일까?

다중 컬럼 인덱스의 정렬 방식 때문이다. 뒤에 있는 키 값은 앞에 존재하는 키에 의존하는 특성이다.

위 예시처럼 작업의 범위를 결정하는 조건을 ‘작업 범위 결정 조건’이라고 하고, 비교 작업의 범위를 줄이지 못하고 거름종이 역할만 하는 조건을 ‘필터링 조건(=체크 조건)’이라고 표현한다. A의 경우 두 인덱스 컬럼이 모두 작업 범위 결정 조건에 해당하지만, B에서는 emp_no 컬럼만 작업 범위를 결정하고 dept_no 컬럼은 필터링 조건으로 사용됐다.

작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만, 필터링 조건은 많다고 해서 쿼리의 처리 성능을 높이지 못한다.

정리하자면, B-Tree index 구조에서 동등 조건(’=’)이 앞에 있으면 특정 값을 바로 찾아갈 수 있다. 하지만 범위 조건이 앞에 있으면 범위에 해당하는 모든 리프 노드를 순회하게 된다. 위 예시처럼 동등, 범위 조건이 같이 있는 경우 인덱스 컬럼 순서는 동등이 앞에 오는 것을 권장한다.

hashtag
가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음 조건에서 사용할 수 없다. 여기서 사용할 수 없다는 의미는 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미하며, 경우에 따라서 필터링(체크) 조건으로 인덱스를 사용할 수는 있다.

아래의 경우 인덱스를 사용할 수 없다. 인덱스를 사용할 수 없다는 것은 옵티마이저가 인덱스 스캔보다 테이블 풀 스캔을 선택하는 경우가 많다는 의미로도 해석할 수 있다.

  • NOT-EQUAL로 비교된 경우(’<>’, ‘NOT IN’, ‘NOT BETWEEN’, ‘IS NOT NULL’)

    • WHERE column <> ‘N’

    • WHERE column NOT IN (10, 11, 12)

    • WHERE column IS NOT NULL

  • LIKE ‘%??’ (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교되는 경우

    • WHERE column LIKE ‘%kim’

    • WHERE column LIKE ‘_kim’

    • WHERE column LIKE ‘%kim%’

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

    • WHERE SUBSTRING(column, 1, 1) = ‘X’

    • WHERE DAYOFMONTH(column) = 1

  • NOT-DETERMINSTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

    • WHERE column = determinstic_function()

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

    • WHERE char_column = 10

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

    • WHERE utf8_bin_char_column = euckr_bin_char_column

위 조건 중 일부는 SQL 쿼리 성능 최적화 방법에도 포함되는 내용이 있다.

다중 칼럼으로 만들어진 인덱스는 어떤 조건에서 사용될 수 있고, 어떤 경우에 절대 사용할 수 없다.

  • 작업 범위 결정 조건으로 인덱스 사용을 못하는 경우

    • column_1에 대한 조건이 없는 경우

    • column_1 비교 조건이 인덱스 사용 불가 조건 중 하나인 경우

  • 작업 범위 결정 조건으로 인덱스를 사용하는 경우 (i > 2 & i < n)

    • column_i 컬럼에 대해 다음 연산자 중 하나로 비교

      • 동등 비교(’=’ , ‘IN’)

      • 크다, 작다(’>’, ‘<’)

      • LIKE 좌측 일치(LIKE ‘value%’)

Last updated