TIL

8.3 B-Tree 인덱스 정렬과 효율성

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

인덱스 생성 시 정렬 규칙에 따라 오름차순 또는 내림차순으로 정렬되어 저장된다. 오름차순으로 생성되어도 내림차순으로 읽을 수도 있고 이는 옵티마이저의 실행 계획에 따라 결정된다.

인덱스의 정렬

• MySQL 8.0 버전부터 아래와 같이 정렬 순서를 혼합한 인덱스를 생성할 수 있게 되었다.

CREATE INDEX ix_teamname_userscore ON employyes (team_name ASC, user_score DESC);

인덱스 스캔 방향

• 인덱스가 어떤 칼럼에 대해 오름차순으로 정렬되어 있어도 옵티마이저는 쿼리에 따라 최댓값부터 거꾸러 읽어 내림차순으로 값을 가져올 수 있다.
• 옵티마이저는 ORDER BY나 MIN(), MAX() 함수 등의 최적화가 필요한 경우 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

-- // first_name ASC로 인덱스가 설정되어 있을 때
-- // 아래와 같은 쿼리를 실행하면 인덱스를 역순으로 접근해 첫 번째 레코드만 읽게 된다.
SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;

내림차순 인덱스

• 옵티마이저의 실행 계획에 따라 인덱스 정렬에 상관 없이 읽기 방향을 결정할 수 있다는 것을 알았다.
• 하지만 InnoDB 스토리지 엔진에서 역순 스캔은 정순 스캔보다 느리다.
  • 1천 2백여만 건을 스캔하는데 역순 스캔하는 쿼리가 정순 스캔보다 28.9% 시간이 더 걸린다.
• InnoDB에서 역순 스캔이 정순 스캔보다 느린 이유
  • 페이지 잠금이 인덱스 정순 스캔에 더 적합한 구조
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조
• 인덱스 정렬 방향과 역순으로 정렬하여 많은 레코드를 조회하는 쿼리가 빈번하게 실행된다면 내림차순 인덱스가 더 효율적이라고 볼 수 있다.
• 많은 쿼리가 인덱스의 앞쪽 또는 뒤쪽만 집중적으로 읽어서 특정 페이지 잠금 병목이 될 것이라 예상된다면 쿼리에서 자주 사용되는 정렬로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 될 것이다.

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

비교 조건의 종류와 효율성

• 다중 칼럼 인덱스에서 동등 비교(’=’)인지 아니면 크다(’>’), 작다(’<’)의 조건인지에 따라 인덱스 활용 형태와 효율이 달라진다.
• 아래 쿼리와 두 인덱스를 통해 알아보자.

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

• 케이스 A: INDEX (dept_no, emp_no)
  • dept_no로 정렬하고 같은 dept_no에 대해서 emp_no로 정렬한 형태
  • dept_no='d002' AND emp_no>=10114인 레코드를 찾고 dept_no가 ‘d002’가 아닐 때까지 읽으면 된다.
  • 조건에 맞는 레코드가 5건이라고 하면 5번의 비교 작업만 수행한 것
• 케이스 B: INDEX (emp_no, dept_no)
  • emp_no로 정렬하고 같은 emp_no에 대해서 dept_no로 정렬한 형태
  • emp_no≥10114 AND dept_no=’d002’인 레코드를 찾고 그 이후 모든 레코드에 대해 dept_no가 d002인지 비교해야 한다.
  • 실제 결과인 5건보다 더 많은 레코드를 보며 dept_no=’d002’인지 배교해야 한다.
• 케이스 B의 dept_no=’d002’처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 선택하는 작업을 ‘필터링’이라고 한다.
• 필터링 작업은 많을수록 쿼리 처리 성능을 떨어뜨릴 때가 많다.

공식적인 명칭은 아니지만 케이스 A의 dept_no='d002' AND emp_no>=10114와 케이스 B의 emp_no≥10114 같은 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라고 한다. 작업 범위 결정 조건이 많을수록 쿼리 성능을 높일 수 있다.

인덱스의 가용성과 효율성 판단

• 기본적으로 B-Tree 인덱스 특성상 다음 조건에서는 사용할 수 없다.
  • 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미하며 경우에 따라 필터링 조건으로는 인덱스를 사용할 수는 있다.
    1. NOT-EQUAL로 비교된 경우 (<>, NOT IN, NOT BETWEEN, IS NOT NULL)
    2. LIKE ‘%??’(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
    3. 스토어드 함수나 다른 연산자로 인ㄷ게스 칼럼이 변경된 후 비교된 경우 1. …WHERE SUBSTRING(column, 1, 1) = ‘X’ 2. …WHERE DAYOFMONTH(column) = 1
    4. NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건으로 사용된 경우 1. …WHERE column = deterministic_function()
    5. 데이터 타입이 서로 다른 비교(인덱스 칼럼 타입을 변환해야 비교가 가능한 경우) 1. …WHERE char_column = 10
    6. 문자열 데이터 타입의 콜레이션이 다른 경우 1. …WHERE utf8_bin_char_column=euckr_bin_char_column

MySQL에서는 NULL도 인덱스에 저장될 수 있다. 때문에 IS NULL 조건은 작업 범위 결정 조건으로 사용할 수 있다.

• 다중 칼럼 인덱스도 살펴보자.
  • INDEX ix_test (col_1, col_2, col_3, … , col_n)
    1. 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우 1. col_1 칼럼에 대한 조건이 없거나 인덱스 사용 불가 조건 중 하나인 경우
    2. 작업 범위 결정 조건으로 인덱스를 사용하는 경우 1. col_1 ~ col_(i - 1) 칼럼까지 동등 비교 형태(’=’ or ‘IN’)로 비교
    3. i는 2보다 크고 n보다 작은 임의의 값 2. col_i 칼럼에 대해 다음 연사자 중 하나로 비교
    4. 동등 비교(’=’ or ‘IN’)
    5. 크다 작다 형태
    6. LIKE로 좌측 일치 패턴 3. a, b 조건을 모두 만족하는 쿼리는 col_1 ~ col_i까지는 작업 범위 결정 조건으로 사용되고 그 뒤 col_n까지의 조건은 체크 조건으로 사용된다.