책너두 (Real MySQL 8.0 1권) 22일차 (~246p)

요약

• 다중 칼럼 인덱스에 대해 이해하게 됨.
  • 각 칼럼의 위치가 중요하다.
• B-Tree 인덱스의 정렬과 스캔 방향에 따른 동작 원리를 이해하게 됨.
  • 정렬의 경우, 오름차순 내림차순 상관없이, 레코드의 값을 거꾸로 읽으면됨. (옵티마이저의 실행계획에 따름)
    • 인덱스의 스캔방향을 거꾸로 할 수 있음.
    • 정렬 순서가 혼합된 인덱스를 만들 수도 있음.
  • 스캔 방향과 달리, 내림 차순 인덱스로 인덱스 자체를 내림차순으로 만들어낼 수 있음.
  • InnoDB 에서는 역순 스캔의 성능이 느린 이유가 있음.
    • 따라서, 사용하는 쿼리에 예상되는 레코드의 수, 정렬 순서를 고려하여, 내림차순 인덱스를 설정하여 더 적절하게 쿼리를 처리할 수 있음.

메모

다중 칼럼(Multi-column) 인덱스

• 실제 서비스용 데이터베이스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용됨.
  • 2개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(= 복합 칼럼 인덱스) 라고 함.
  • 2개 이상의 칼럼이 연결됐다고해서 “Concatenated Index” 라고도 함.

• 위 그림은 2개 이상의 칼럼을 포함하는 다중 칼럼 인덱스의 구조를 보여줌.
• 위 그림에서는 편의상 루트 노드를 생략함.
  • 실제로는 데이터 레코드 건수가 작은 경우, 브랜치 노드가 없는 경우도 있을 수 있음.
  • 하지만 루트 노드와 리프 노드는 항상 존재함.
• 위 그림은 다중 칼럼 인덱스 일때 각 인덱스를 구성하는 칼럼의 값이 어떻게 정렬되어 저장되는지 보여줌.
  • 중요한 것은 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬되어 있다는 것임.
  • 즉, 첫 번째 칼럼이 똑같은 레코드에서만 두 번째 칼럼의 정렬이 의미가 있다.
• 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요함.
  • 따라서 이를 신중히 결정해야 함.

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

• 인덱스 생성 시, 설정한 정렬 규칙에 따라 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장됨.
  • 하지만, 어떤 인덱스가 오름차순으로 생성됐다고 해서, 그 인덱스를 오름차순으로만 읽을 수 있다는 뜻은 아님.
  • 사실, 그 인덱스를 거꾸로 읽으면 내림차순으로 정렬된 인덱스로도 사용될 수 있음.
    • 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정됨.

인덱스의 정렬

• 상용 DBMS에서 인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 or 내림차순으로 설정할 수 있음.
• 5.7 버전까지는 칼럼 단위로 정렬 순서를 혼합(ASC와 DESC 혼합)해서 인덱스를 생성할 수 없었음.
  • 이 문제를 해결하기 위해 숫자 칼럼의 경우 -1을 곱한 값을 저장하는 우회 방법을 사용했었음.
  • 8.0 버전부터는 아래와 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됨.

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

인덱스 스캔 방향

• first_name 칼럼에 대한 인덱스가 포함된 employees 테이블에 대해 다음 쿼리의 실행 과정을 살펴본다.

mysql> SELECT *
             FROM employees
             ORDER BY first_name DESC
             LIMIT 1;

• MySQL은 이 쿼리를 실행하기 위해, 인덱스를 처음부터 오름차순으로 끝까지 읽어서 first_name이 가장 큰값 하나를 가져오는 것이 아님.
  • 인덱스는 항상 오름차순으로만 정렬되어 있지만, 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고 있음.
  • 따라서, 위의 쿼리는 인덱스를 역순으로 접근해서 첫 번째 레코드만 읽으면 됨.

• 위 그림은 인덱스를 정순으로 읽는 경우와 역순으로 읽는 경우를 보여줌.
• 쿼리의 ORDER BY 처리나 MIN(), MAX() 함수 등의 최적화가 필요한 경우도 MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 냄.

내림차순 인덱스

• MySQL 서버에서 아래 두 쿼리는 실제로 내림차순인지 오름차순인지와 관계없이 인덱스를 읽는 순서만 변경해서 해결할 수 있다는 걸 알았다.

mysql> SELECT * FROM ORDER BY first_name ASC  LIMIT 10;
mysql> SELECT * FROM ORDER BY first_name DESC LIMIT 10;

• 아래와 같이 2개 이상의 칼럼으로 구성된 복합 인덱스에서, 각 칼럼이 내림차순과 오름차순이 혼합된 경우에는 MySQL 8.0의 내림차순 인덱스 로만 해결될 수 있음.

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

• 오름차순 인덱스 (Ascending index) : 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스임.
• 내림차순 인덱스 (Descending index) : 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스임.
• 인덱스 정순 스캔(Forward index scan) : 인덱스의 키가 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔함.
• 인덱스 역순 스캔(Backward index scan) : 인덱스의 키가 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 오른쪽으로 스캔함.
• ex) 약 1천만 건 정도의 레코드가 들어있는 테스트용 테이블을 생성했다고 가정하자.

mysql> CREATE TABLE t1 (
                 tid INT NOT NULL AUTO_INCREMENT,
                 ...
                 PRIMARY KEY(tid)
             ) ENGINE=InnoDB;

// 데이터 천만건 입력

mysql> SELECT COUNT(*) FROM t1;
// -> 12619776

mysql> SELECT * FROM t1 ORDER BY tid ASC LIMIT 12619776, 1
1 row in set (4.15 sec)

mysql> SELECT * FROM t1 ORDER BY tid DESC LIMIT 12619776, 1
1 row in set (5.35 sec)

• 위 예시에서, 테이블을 풀 스캔하면서 정렬만 수행하는 쿼리를 아래에서 실행했음.
  • 이 두 쿼리는 테이블의 프라이머리 키를 정순 또는 역순으로 스캔하면서 마지막 레코드 1건만 반환하기를 기대함.
  • 하지만 LIMIT … OFFSET … 부분의 쿼리로 인해 실제 MySQL 서버는 테이블의 모든 레코드를 스캔해야 함.
• 1천 2백여만 건을 스캔하는데, 역순 정렬이 1.2초 정도 더 걸림.
  • 역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 더 시간이 걸리는 것임.
    • MySQL 서버의 InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결 고리를 통해 전지하느냐 후진하느냐의 차이만 있음.
    • 하지만 실제로 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없는 2가지 이유가 있음.
      • 페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조이다.
      • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조임. (아래 그림에서, InnoDB 페이지 내부에서 레코드들이 단방향으로만 링크를 가진 구조임.)

위 그림에서 InnoDB 페이지 내부의 레코드들이 정렬 순서대로 저장되어 있는 것처럼 표시되어 있지만, 실제로 InnoDB 페이지는 힙(Heap) 처럼 사용되므로 물리적으로 저장이 순서대로 배치되지는 않음.

• 만약 first_name 칼럼을 역순으로 정렬하는 요건만 있다면 아래 2개 인덱스 중에서 어떤 것을 선택하는것이 좋을까?

mysql> SELECT * FROM ORDER BY first_name DESC;

mysql> CREATE INDEX ix_firstname_asc  ON employees (first_name ASC  ); // 오름차순 인덱스
mysql> CREATE INDEX ix_firstname_desc ON employees (first_name DESC `); // 내림차순 인덱스

• 일반적으로 인덱스를 ORDER BY … DESC 하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없음.
• 하지만, 위 쿼리를 많은 레코드를 조회하면서 빈번히 발생하면 오름차순 인덱스보다는 내림차순 인덱스가 더 효율적임.
• 만약, 쿼리가 인덱스의 앞쪽 혹은 뒤쪽만 집중적으로 읽어서 특정 페이지 잠금이 병목으로 될 것이 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 될 것임.