[MySQL] 인덱스를 알아보자 (2)

B-Tree 인덱스를 통한 데이터 읽기

1. 인덱스 레인지 스캔

   • 가장 대표적인 인덱스의 스캔 방식
   • 찾아야 할 인덱스의 범위가 정해졌을 때 사용되는 방식
   • 루트 노드 부터 브랜치 노드를 거쳐 리프 노드까지 들어가 시작하는 지점을 정하고 그 다음에는 리프 노드의 레코드만 순서대로 읽는다.
   • 인덱스의 리프 노드에서 검색 조건에 일치하는 노드는 데이터에서 레코드를 읽어오는 과정이 필요해진다. 조건이 3개 일치한 경우 해당 데이터의 레코드를 읽기 위해 3번의 I/O 가 필요해진다.
   • 어떤 쿼리를 사용하느냐에 따라 갈리지만 인덱스를 차례대로 읽은 후 해당 키와 주소를 이용해 가져온 페이지에서 최종 레코드를 읽어오는 작업이 필요 없을 수가 있는데 이를 커버링 인덱스 라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 I/O 가 상당히 줄어든다.

2. 인덱스 풀 스캔

   • 인덱스의 처음부터 끝까지 읽는 방식
   • 쿼리 조건절에 사용된 컬럼이 인덱스의 1번째 컬럼이 아닌 경우 사용, 인덱스는 (A, B, C)의 순으로 만들어져 있지만 조건절은 B 혹은 C를 사용하는 경우
   • 처음부터 끝까지 풀 스캔 하여 인덱스를 사용하는 방식 중에서는 효율적인 방식이 아니지만 테이블 풀 스캔보다는 효율적이다.

3. 루스 인덱스 스캔

   • 레인지 스캔과 비슷하게 동작하지만 필요하지 않은 인덱스 키 값은 무시(skip) 하고 넘어가는 형태로 동작함
   • 일반적으로 GROUP BY 혹은 MAX, MIN 등의 함수를 최적화를 하는 경우에 사용된다.
   • 아래 쿼리에서 dept_emp 테이블은 dept_no와 emp_no 로 인덱스가 걸려있다. 루스 인덱스 스캔을 사용하면 옵티마이저가 WHERE 절에 있는 범위만 만족하면 되는 것을 알고 있어서 만족하지 않는 부분은 무시하고 다음 레코드로 이동한다.

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no

4. 인덱스 스킵 스캔

• gunpla 테이블은 아래의 인덱스를 가지는 테이블이다.

ALTER TABLE gunpla ADD INDEX idx_grade_created_at (grade, created_at)

• 위에 정의된 인덱스를 사용하기 위해서는 grade 컬럼에 대한 조건이 필수다.

#인덱스 안탐
SELECT grade, created_at FROM gunpla WHERE created_at >= '2019-01-01'

#인덱스 탐
SELECT grade, created_at FROM gunpla WHERE grade = 'MG' AND created_at >= '2019-01-01' 

• 위 쿼리문 처럼 두 가지의 비교 조건을 가진 2번째 쿼리는 인덱스를 사용하지만 1번째 쿼리는 grade에 대한 비교조건이 들어 있지 않아서 인덱스를 효율 좋게 사용할 수 없었는데 이런 경우 created_at 을 첫 번째로 하는 인덱스를 만들어줘야 했다고 한다.

• MySQL 8버전부터는 옵티마이저가 grade 컬럼을 건너뛰어서 created_at 컬럼만으로도 인덱스를 탈 수 있게 해주는 인덱스 스킵 스캔(Index Skip Scan) 이 등장했다.

• 인덱스 스킵 기능을 사용하지 않으면 인덱스를 타긴 하나 인덱스 풀 스캔이 발생해서 그다지 효율적으로 검색할 수 없다.

• 생긴지 얼마 안돼서 몇 가지 제약조건이 있는데, 조건절에 없는 인덱스의 유니크한 값이 적어야 하고 쿼리가 인덱스에 존재하는 컬럼만으로 끝나야 한다. 그래서 인덱스 스킵 스캔은 인덱스의 첫 번째 컬럼의 유니크 한 값이 적을 때에만 적용이 가능한 최적화 방법이다.

SELECT * FROM gunpla WHERE grade = 'MG' AND created_at >= '2019-01-01' 

• 위와 같이 쿼리를 사용하는 경우 인덱스에 포함된 컬럼 뿐만 아니라 다른 컬럼도 포함이 되어야 쿼리문이 종료가 되기 때문에 테이블 풀 스캔으로 이어진다.

다중 컬럼 인덱스

• 2개 이상의 컬럼으로 구성된 인덱스
• 데이터 레코드 건수가 적은 경우 브랜치 노드가 없는 경우도 있다.
• 정렬은 앞의 인덱스 컬럼에 의존한다. 1번 컬럼에 2번 컬럼이 종속되는 방식
• 따라서 다중 컬럼 인덱스에서는 컬럼의 순서가 가장 중요하다.

B-Tree 인덱스의 정렬

• MySQL 8버전 부터 정렬 순서를 혼합한 인덱스를 작성할 수 있음

  CREATE INDEX idx_name_price ON gunpla (name ASC, price DESC)

인덱스의 스캔 방향

• 인덱스 생성 시점에 오름차순 혹은 내림차순으로 정렬이 결정된다.

• 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라서 오름차순 혹은 내림차순으로 정렬하는 효과를 얻을 수 있다.

• 오름차순으로 만들어진 인덱스를 역방향으로 읽으면 내림차순이 되는 것이다. 반대도 마찬가지 name의 인덱스가 오름차순으로 되어 있다고 가정하면

  SELECT * FROM gunpla WHERE name >= 'RX-78-3' ORDER BY name ASC LIMIT 6
  
  SELECT * FROM gunpla ORDER BY name DESC LIMIT 8

  첫 번째 쿼리는 name에 정의된 인덱스를 통해 G-3건담을 찾은 후 정방향으로 인덱스를 읽으면서 5개의 레코드만 가져오면 된다. 두 번째 쿼리는 인덱스를 역방향으로 읽으면서 8개만 가져오면 된다.

• MIN(), MAX() 와 같은 함수 최적화가 필요한 경우에도 옵티마이저가 자동으로 읽기 방향을 전환하여 실행 계획을 만들어낸다.

내림차순 인덱스

• 복합 인덱스에서는 각각의 컬럼이 내림차순, 오름차순이 혼합되어 있는 경우 내림차순 인덱스로만 사용할 수 있다.

• 더미로 7239680건 정도를 집어넣은 테이블로 테이블을 풀 스캔하면서 정렬만 수행하는 쿼리를 실행해보면

  SELECT * FROM test ORDER BY id ASC LIMIT 7239679 1
  #3.31 sec
  SELECT * FROM test ORDER BY id DESC LIMIT 7239679 1
  #4.52 sec

  첫 번째 쿼리보다 두 번째 쿼리가 시간이 좀 더 걸린다는 것을 알 수 있는데 InnoDB 에서 정방향 스캔보다 역방향 스캔이 느릴 수 밖에 없는 이유가 2가지 있다. 첫 번째는 페이지의 잠금이 정방향 스캔에 적합한 구조로 되어있고 두 번째는 페이지 내 인덱스들의 레코드가 단방향으로만 연결이 되어 있다.

• 일반적으로 인덱스를 내림차순 하는 쿼리가 별로 없다면 내림차순 인덱스를 고려할 필요는 없을 것 같다.

• 게시판 같은 경우 가장 최신글이 먼저 올라와야 하니까 내림차순 인덱스를 고려하는 것이 좋을 것 같다.

• 자주 사용되는 정렬의 형태로 인덱스를 만들어놓으면 쿼리의 병목현상 완화에 도움이 될 것 같다.