인덱스 Index - (5)

1. 다중 컬럼 (Multi-column) 인덱스

두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복합 칼럼 인덱스)라고 한다. 

그림(1) 다중 칼럼 인덱스

그림(1)에서는 편의상 루트 노드는 생략했으나 실제로 데이터 레코드 건수가 작은 경우에는 브랜치 노드가 없는 경우도 있을 수 있다. 하지만 루트 노드와 리프 노드는 항상 존재한다. 다중 칼럼 인덱스에서 중요한 점은 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬돼 있다는 것이다. 즉 두번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 값일 때 의미가 있다는 것이다.

 

2. 인덱스의 정렬

MySQL 8.0 버전부터는 아래와 같이 정렬 순서를 혼합한 인덱스를 생성할 수 있다. MySQL 5.7에서는 상위 버전과의 호환성을 위해 문법상으로만 제공을 하였고 실제로는 오름차순 정렬만으로 인덱스가 생성됐다.

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

 

2-1 인덱스 스캔 방향

first_name 컬럼에 대한 인덱스가 포함된 employees 테이블에 대해 다음 쿼리를 실행하는 과정을 한번 살펴보자. 

SELECT first_name
  FROM employees
 ORDER BY first_name DESC
 LIMIT 1;

인덱스는 오름차순으로 정렬돼 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고 있다. 그래서 위의 쿼리는 인덱스를 역순으로 접근해 첫 번째 레코드만 읽으면 된다. 

 

-- SELECT (1) 정순
SELECT first_name 
  FROM employees
 WHERE first_name >= 'Anneke'
 ORDER BY first_name ASC LIMIT 4;
 
 -- SELECT (2) 역순
SELECT first_name 
  FROM employees 
 ORDER BY first_name DESC LIMIT 6;

그림(2) 인덱스의 오른차순(ASC)과 내림차순(DESC) 읽기

그림(2)는 인덱스를 정순으로 읽는 경우와 역순으로 읽는 경우를 보여준다. 즉, 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. 쿼리의 ORDER BY 처리나 MIN(), MAX() 함수 등의 최적화가 필요한 경우에도 MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만든다.

 

2-2 내림차순 인덱스

CREATE TABLE t1 (
    tid INT PRIMARY KEY AUTO_INCREMENT
  , name varchar(64) 
);

위 쿼리로 생성된 테이블에 12,345,678 건의 레코드가 있다고 가정하자. PRIMARY KEY인 tid 컬럼은 오름차순으로 정렬된 인덱스가 생성된다.

SELECT * FROM t1 ORDER BY tid ASC LIMIT 12345677, 1; -- SELECT (3)
1 row in set (4.15 sec)

SELECT * FROM t1 ORDER BY tid DESC LIMIT 12345677, 1; -- SELECT (4)
1 row in set (5.35 sec)

WHERE 절이 없는 LIMIT OFFSET 쿼리는 테이블의 모든 레코드를 스캔해야 한다. SELECT (3)은 가장 낮은 값의 tid를 가진 레코드 하나를 가져오고, SELECT (4) 가장 높은 값의 tid를 가진 레코드 하나를 가져온다. 두 쿼리 모두 하나의 레코드를 가져오지만 1.2초 정도의 차이를 보여준다. 정순으로 정렬된 인덱스에서 역순 정렬 쿼리를 수행하면 약 30%의 성능 저하를 볼 수 있다. 성능저하가 있더라도 일반적으로 ORDER BY ... DESC 하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없다. 

다음 쿼리를 한번 살펴보자.

SELECT *
  FROM tab
 WHERE userid = 10
 ORDER BY score DESC
 LIMIT 10;

이 쿼리의 경우 오름차순 인덱스 (userid ASC, score ASC), 내림차순 인덱스 (userid DESC, score DESC) 모두 적절한 선택이 될 수 있다. 하지만 위 쿼리가 많은 레코드를 조회하면서 빈번하게 실행된다면 오름차순 인덱스보다 내림차순 인덱스가 더 효율적이라고 볼 수 있다.

또한 많은 쿼리가 인덱스의 앞쪽 혹은 뒤쪽에만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 된다.

 

3. B-Tree 인덱스의 비교 조건의 종류와 효율성

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 칼럼에 사용된 조건이 비교 연산자(=)인지 아니면 관계 연산자(>, <, >=, <=)에 따라 각 인덱스 칼럼의 활용 형태가 달라지며 효율 또한 달라진다. 다음 인덱스와 쿼리를 살펴보자.

• CASE (A): INDEX( dept_no, emp_no )
• CASE (B): INDEX( emp_no, dept_no )

-- SELECT (3)
SELECT *
  FROM dept_emp
 WHERE dept_no = 'd002' 
   AND emp_no >= 100114;

그림 (3) 인덱스의 컬럼 순서로 인한 쿼리 실행의 차이

CASE (A)는 " dept_no = 'd002' AND emp_no >= 10144 " 조건에 맞는 레코드를 찾고 그 이후에는 dept_no의 값이 'd002'가 아닐 때까지 순차적으로 스캔을 하면 된다. 이 경우에는 읽은 모든 레코드가 사용자가 요청한 결과이다. 즉, 조건을 만족하는 레코드가 5건이라고 할 때, 딱 5건의 비교 작업만 수행하였기 때문에 상당히 효율적으로 인덱스를 이용하였다.
CASE (B)는 " emp_no >= 10144 AND dept_no = 'd002' " 조건에 맞는 레코드를 찾고 그 이후에는 dept_no의 값이 'd002'가 맞는지 비교하는 과정을 거쳐야 한다. 비교를 통해 레코드를 제외하는 과정을 '필터링'이라고 한다. 
그림 (3)을 살펴보면 CASE (A), (B) 모두 레코드 5건을 가져온다. 하지만 CASE (B)는 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친 후 2건은 제외를 하였다. 

CASE (A)에서 emp_no는 비교 작업의 범위를 좁히는데 도움을 주었지만 CASE (B)에서 dept_no는 범위를 좁히는데 아무런 도움이 되지 않았고 조건에 맞는지 검사하는 용도로만 사용됐다. 인덱스를 사용하거나 생성할 때는 쿼리 조건에 따라 비교 범위를 최대한 줄여줄 수 있게 하는 것이 좋다.

 

4. B-Tree 인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬된다는 것이다. 여기서 왼쪽 값이란 하나의 컬럼인 경우와 다중 컬럼인 경우 모두 같다. 

• CASE (C): employees 테이블의 INDEX( first_name )
• CASE (D): dept_emp 테이블의 INDEX( dept_no, emp_no )

그림 (4) 왼쪽 값 기준으로 인덱스 정렬

인덱스를 사용할 때 왼쪽 값이 없다면 인덱스 레인지 스캔 방식을 이용할 수 없게 된다. 다음 쿼리를 한번 살펴보자.

-- SELECT (4)
SELECT *
  FROM employees
 WHERE first_name LIKE '%mer';
 

-- SELECT (5)
SELECT *
  FROM dept_emp
 WHERE emp_no >= 10144;

SELECT (4)에서 우측값으로 레코드를 찾는 조건이 사용되었기 때문에 INDEX( first_name ) 를 사용할 수 없기에 테이블 풀 스캔으로 레코드를 찾게 된다. 
SELECT (5)에서 좌측값인 dept_no값이 조건에 없기 때문에 INDEX( dept_no, emp_no ) 를 사용할 수 없기에 테이블 풀 스캔으로 레코드를 찾게 된다.

-- SELECT (6)
SELECT *
  FROM employees
 WHERE first_name LIKE 'Abd%';
 

-- SELECT (7)
SELECT *
  FROM dept_emp
 WHERE dept_no >= 'd002';
 
 
 -- SELECT (8)
SELECT *
  FROM dept_emp
 WHERE dept_no >= 'd002' 
   AND emp_no >= 100114;

SELECT(3), (6~8) 은 인덱스를 사용하기에 효율적으로 레코드를 찾을 수 있다.