RealMySQL - 인덱스 스캔 방향

1. 인덱스 스캔 방향은 무조건 오름차순으로 이루어지는가?

- No, 인덱스는 항상 오름차순으로 정렬돼 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있다. 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고있다.

 

 

2. 인덱스 스캔

- 내림차순 인덱스 : 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스

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

 

- 오름차순 인덱스 : 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스

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

 

3. 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없는 이유

- 페이지 잠금이 인덱스 정순 스캔에 적합한 구조

- 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

 

• 정순 스캔 시:
  1. 페이지 내에서 첫 번째 레코드를 찾습니다.
  2. Next Record 포인터를 따라 순차적으로 다음 레코드를 읽습니다. 매우 빠르고 효율적입니다.
  3. 페이지의 끝에 도달하면, 페이지의 Next Page 포인터를 따라 다음 페이지로 이동합니다.
• 역순 스캔 시:
  1. 페이지 내에서 마지막 레코드를 찾습니다.
  2. 이전 레코드를 가리키는 포인터가 없기 때문에, 이전 레코드를 찾기 위해 페이지 내의 레코드 리스트를 비효율적으로 탐색하거나 추가적인 연산이 필요합니다.
  3. 이 과정이 모든 레코드마다 반복되므로 CPU 작업량이 증가하고 성능이 저하됩니다.
  4. 페이지의 시작에 도달하면, 페이지의 Prev Page 포인터를 따라 이전 페이지로 이동합니다.

4. ORDER BY ... DESC가 매우 빈번하고 성능에 민감한 쿼리라면?

- 내림차순 인덱스(CREATE INDEX ... (col DESC))를 생성하는 것이 근본적인 해결책이다.

내림차순 인덱스를 만들면, DESC 쿼리도 가장 효율적인 '정순 스캔'으로 처리할 수 있다.