Real MySQL 8.0 8장 - 인덱스 정렬 및 스캔의 방향

인덱스 생성시 기본적으론, 오름차순(ASC) 정렬로 인덱스가 생성된다.

mysql 5.7 버전 이후의 버전에서 인덱스 생성 시, 인덱스를 구성하는 컬럼의 정렬 순서를 정 할 수 있다.

5.7 버전에서는 인덱스 생성시 아래 와 같이 ASC, DESC 키워드로 정렬 순서를 지정해 주어도, 실제로 내부적으로 오름차순(ASC)으로만 정렬된다.

CREAT INDEX ix_teamname ON employees (team_name DESC)
// 5.7 버전에서는 DESC로 생성해도 실제로는 ASC 로 정렬 됨.

8.0 버전 부터 복합 인덱스 생성시 각 컬럼마다 별개로 정렬 순서를 정할 수 있게 되었다.

CREAT INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC)

 

 

인덱스 정렬 방향과 스캔 방향의 관계

만약 어떤 인덱스가 오름차순으로 정렬되어있다면, 오름차순으로만 읽을 수 있는것은 아니다. 그 인덱스를 거꾸로 부터 읽으면 내림차순으로 정렬된 인덱스로도 사용될수 있기 때문이다.

인덱스를 어떤방향으로 읽을지는 옵티마이저가 실시간으로 만들어내는 실행계획에 따라 달라진다.

 

 

인덱스 정순 스캔 VS 인덱스 역순 스캔

먼저 인덱스 정순 스캔과 역순 스캔을 비교하기 전에 용어에 대한 정리를 하면

오름차순 인덱스 : 작인 값의 인덱스 키가 B-Tree 왼쪽 노드에 위치

내림차순 인덱스 : 큰인 값의 인덱스 키가 B-Tree 왼쪽 노드에 위치

인덱스 정순 스캔 : 인덱스 키의 크고 작음에 관계없이 인덱스 리프노드의 왼쪽 페이지 부터 오른쪽을 스캔

인덱스 정순 스캔 : 인덱스 키의 크고 작음에 관계없이 인덱스 리프노드의 오른쪽 페이지 부터 왼쪽을 스캔

 

만약 오름차순(ASC) 인덱스로 생성된 컬럼을 활용해서 Order by 절에서 오름차순(ASC)으로 읽는 다면 정순스캔 (DESC)로 읽는다면 역순 스캔이 된다. 반대로 내림차순(DESC) 인덱스로 생성된 컬럼(MySQL 8.0 부터 지원)을 활용해서 Order by 절에서 오름차순(DESC)으로 읽는 다면 정순스캔 (ASC)로 읽는다면 역순 스캔이 된다.

 

즉 인덱스 생성시 정한 정렬 방향과 일치하는 방향대로 읽는 경우를 정순 스캔, 인덱스 생성시 정한 정렬 방향과 반대로 읽는 경우를 역순 스캔이라고 생각 할 수 있다.

 

 

인덱스 정순 스캔 과 인덱스 역순 스캔 성능 차이

// 322597개 row가 있는 테이블에서 테스트, 오름차순(ASC) 인덱스 생성 된 BeginDate 컬럼 사용

// 인덱스 정순 스캔(ASC로 생성된 인덱스를 ASC로 읽음)
select * from `test_sales` ORDER BY BeginDate ASC LIMIT 322596,1; // 233ms

// 인덱스 역순 스캔(ASC로 생성된 인덱스를 DESC로 읽음)
select * from `test_sales` ORDER BY BeginDate DESC LIMIT 322596,1; // 391ms

인덱스 역순 스캔의 경우 시간이 더 걸린것을 확인 할 수 있다. 보통 28.9% 정도 더 걸리는데 그 이유는 InnoDB 자체 내부에서 페이지 잠금 구조나, 페이지 내부에서 레코드들의 연결 구조가 정순 스캔에 더 적합한 구조로 되어있기때문이다.