MySQL의 인덱스

인덱스 개요

인덱스란, 책 내용의 메타정보를 가지고있는 목차와도 같은 자료구조입니다.

데이터 획득을 위해 특정 조건으로 쿼리할 때, 특정 조건이 인덱스에 포함된다면 자료구조에 따라 O(LogX)의 시간에 원하는 데이터를 얻을 수있습니다.

위 그림은 MySQL의 기본 스토리지 엔진의 인덱스의 간략한 구조입니다.

InnoDB B-Tree 인덱스 특징

• 인덱스 레코드는 인덱스 키 순서로 정렬되어있습니다. 위의 예시에서는 ( 학번, 이름 ) 으로 기수정렬 되어있습니다.
  • 이를 기반으로 대소비교를 통해 인덱스 트리를 순회하며 첫 번째 인덱스 컬럼 값이 일치하는 리프노드까지 도달합니다.
  • 리프노드는 Doubly Linked List로 구현되어있어, Index Key 순서 상 다음페이지를 접근할 수 있습니다. 이는 Index Range Scan, Skip Scan을 할 수 있도록 합니다 (뒤에서 설명)
  • 인덱스 변경은 인덱스 삭제 후 추가하는 작업으로 처리됩니다. (정렬된 상태이므로)
• B-Tree 특성상 삽입/삭제 시 페이지 내 레코드 최대/최소 개수에 영향을 받아 분리/병합을 수행해야 합니다.
  • 인덱스는 상당한 탐색성능 향상을 가져오지만, 삽입/삭제시에는 성능 오버헤드가 생기는 이유입니다.
  • 그러나 MySQL에서는 체인지 버퍼에서 메모리상에서만 삽입/삭제 마스킹을 하고, 테이블 균형 작업을 지연하는 방법을 사용해 성능 오버헤드를 줄입니다. (공식문서: Unique Index, PK Insert는 중복체크를 위해 즉시 반영하도록 동작합니다.)

MySQL의 기본 스토리지 엔진인 InooDB인덱스는 B-Tree자료구조로 이뤄져있으며, 스토리지 엔진에따라 Hash알고리즘으로 구현된 인덱스도 있습니다.

Workbench 또는 CLI에서 인덱스를 조회하는 쿼리를 통해 인덱스의 존재를 확인할 수 있습니다. 인덱스의 주요 정보입니다.

인덱스를 이용한 레코드 읽기

실제 쿼리를 통해 데이터를 조회할 때는 다양한 쿼리를 사용해 데이터를 조회합니다. 쿼리 옵티마이저는 최대한 디스크 I/O를 줄이는 방향으로 인덱스 사용을 최적화해 쿼리 실행기에 넘겨줍니다.

인덱스가 실행되는 방법은 아래와 같습니다.

• Index seek: 인덱스 조건을 만족하는 시작부분을 찾아 리프노드까지 내려간다.
• Insex scan: 범위 탐색의 경우 시행되며, 리프노드에서 범위에 포함된 페이지를 읽는다.
• Fetch record: 모든 컬럼이 포함된 실제 레코드 페이지를 불러온다.

쿼리가 필요로하는 칼럼이 모두 Index Record에 포함되면 굳이 3번 Fetch Record를 통해 실제 데이터 페이지를 불러오지 않아도 됩니다. 이것을 이용해 인덱스가 존재하는 컬럼만으로 동작하는 쿼리를 ‘커버링 인덱스’를 사용한다’ 라고 합니다.

만약, (성별,이름)이 인덱스이고, 쿼리가 아래와 같다면, 커버링 인덱스를 통해 인덱스 레코드만으로 쿼리 데이터를 반환할 수 있습니다.

SELECT 성별,이름 FROM MEMBER WHERE 이름='이명규';

위 그림처럼, 쿼리 앞에 EXPLAIN키워드를 붙여주면 인덱스가 어떻게 처리되는지에 대한 정보를 확인할 수 있습니다.

타입 설명은 아래와 같습니다.

Full Index Scan

Full Scan은 인덱스 리프 노드를 전부 탐색함을 의미합니다. 인덱스 칼럼은 (A,B,C)순서이지만, 쿼리 조건절은 B,C컬럼으로만 이뤄져있는 경우에 발생합니다. (이는 추후에 개선됩니다. Skip, Loose Index Scan 참고)

인덱스 레코드는 인덱스로 지정한 컬럼만으로 구성되기에, 일반적으로 테이블 레코드보다 사이즈가 작아 디스크에서 디스크 랜덤 I/O를 통해 페이지를 불러오는 횟수가 적습니다.

또한, 조건절에 있는 비교를 위해 테이블 레코드를 모두 조회하는 것을 Full Table Scan이라고 하는데, 이는 더 많은 페이지 Load가 필요하므로, Full Index Scan보다 훨씬 더 느립니다.

→ 조건절에 인덱스가 아닌 컬럼이 있다: (Full 또는 Range)Table Scan

→ 조건절은 인덱스 컬럼으로 구성되어있지만, 인덱스 컬럼 외 가져와야 할 데이터가 있다: Index Scan

→ 조건절도 인덱스 컬럼에, 가져와야 할 컬럼도 인덱스 내에 있다: Covering Index

Index Range Scan

Range Scan의 경우, 쿼리에서 첫 번째 인덱스를 통해 범위가 특정되어 리프노드 인덱스 페이지들 중, 일부 페이지만 탐색 및 로드하는것을 의미합니다.

앞서 설명한, Index Seek, Index Scan, Fetch Record과정을 통해 데이터를 가져오지만, Index Scan과정에서 특정 범위의 인덱스 페이지만 가져오는 차이가 있습니다.

전체 데이터의 20~25%정도의 데이터를 Index Scan할 때 Index Range Scan보다 Full Table Scan의 효율이 더 좋다고 합니다.

[막간 실험] 책에서 위의 경우에 Full Table Scan이 성능이 더 좋다는게 살짝 이해가 안되어서, 아래의 “인덱스 성능에 영향을 주는 요인”에서그 이유를 고민해보겠습니다.

Index Loose Scan ( MySQL 5.7 ~ )

Index Loose Scan은 Group By에서사용하는 정렬과 관련된 Max,Min 집계함수의 최적화에 사용됩니다.

앞서 Full Index Scan에서 조건절에 두 번째 인덱스를 통해 쿼리할 때 Full Index Scan을 한다고 설명드렸는데요, 아래의 쿼리에 대해 생각해보겠습니다.

인덱스가 (성별, 학번, 이름)으로 구성되었다고 가정합니다.

//사실 성별은 카디널리티가 낮아 좋은 키는 아닙니다. 단지 예시를 위해..

SELECT MIN(학번) FROM STUDENT GROUP BY 성별

Index Loose Scan으로 동작하는 쿼리는 ‘남자’의 첫 번째 리프 페이지로 도착해 최댓값을 읽고, 나머지 ‘남자’인 인덱스 레코드는 읽지 않고 ‘여자’인덱스 페이지로 이동해 최댓값을 읽고 종료합니다.

Index Skip Scan (MySQL 8.0)

Index Skip Scan은 MySQL8.0부터 도입된 기능입니다. Loose Scan이 Group By절만 최적화 했다면, Skip Scan에서는 첫 번째 인덱스 컬럼에서 두 번째 인덱스 컬럼과 매칭되는 레코드를 탐색하고, 이외의 레코드를 스킵합니다.

이를 통해 조건절에서 인덱스 첫 번째 조건절을 누락하고 쿼리하더라도 Full Index Scan보다 빠르게 데이터를 가져올 수 있습니다.

다시 비슷한 예시를 들어보겠습니다. 인덱스는 (성별, 학번, 이름)으로 구성되어 있습니다.

SELECT * FROM STUDENT WHERE 학번 > 2017000;

학번은 인덱스 테이블의 두 번째 칼럼이기에 Index Skip Scan이 없다면, Full Index Scan을 통해 데이터를 찾아야됩니다.

Index Skip Scan에서는 아래와 같이 동작합니다.

• **성별이 ‘남자’**인 레코드에서 학번 > 2017000 를 수행 후 ‘여자’ 레코드가 있는 페이지까지 SKIP
• **성별이 ‘여자’**인 레코드에서 학번 > 2017000 를 수행 후, SKIP과정을 통해 다음 레코드가 없음을 확인하고 종료

이를 통해 로드해야 하는 인덱스 페이지 수가 줄어듭니다.

Q. 브랜치노드로의 참조가 없는데, 어떻게 SKIP이 가능할까..?

인덱스 성능에 영향을 주는 요인

공통적으로 고려해야할 부분

• 인덱스 키 크기(인덱스 트리 깊이)
  • DB가 데이터를 처리하는 단위인 ‘페이지’는 설정에 따라 4~16KB입니다. (기본값 16KB)
  • 대략적으로 계산했을 때, 키 크기가 16Byte라면 자식 노드 주소(12Byte)까지 합해 인덱스 테이블 레코드 크기는 28Byte 라고 하자.
  • 하나의 페이지에는 585개의 레코드가 들어갈 수 있으며, Depth가 3인 트리에서는 585**3 = 대략 2억개의 레코드를 저장할 수 있지만, 키 값이 커질수록 저장할 수 있는 레코드 수는 훨씬 적어집니다.
• 카디널리티(Cardinality) : 선택도 라고도 하며 어떤 컬럼에 대한 카디널리티는, 전체 레코드에 대해 해당컬럼의 유니크한 값 개수입니다.
  • 만약 인덱스 컬럼을 성별로 저장해놓는다면, 카디널리티는 2입니다.
  • WHERE 성별=’남자’ AND NAME=’이명규’; 로 쿼리한다면 성별=’남자’인 인덱스 레코드들 을 전부 탐색합니다. (’이름’은 UNIQUE가 아니므로)

쿼리나 서버의 특성에 따라 고려해보아야할 것들

위의 요소 외에도, 인덱스 컬럼을 지정할 때는 굉장히 다양한 요인을 생각해 설계해야합니다.

• 수행빈도,중요도
  • ****인덱스는 처리시간과 저장공간을 등가교환하기때문에, 자주 수행되지 않거나 빠르게 실행되지 않는 쿼리때문에 인덱스를 만드는것은 좋지 않습니다.
• 인덱스 컬럼이 DML을 통해 데이터가 자주 변경되는지?
  • 앞서 설명했듯, 인덱스 컬럼의 수정은 삭제+생성으로 처리됩니다. 이 작업이 반복되면 트리 균형작업으로 인한 성능 저하가 발생합니다.
• 데이터량: 탐색할 데이터가 많지 않으면, 굳이 인덱스를 사용할 필요가 없습니다.
• 저장공간: 인덱스 페이지를 저장할 충분한 공간이 있어야 합니다. 일반적으로 전체 데이터의 10 ~ 20%

인덱스 개념을 아는것과 인덱스를 설계할 줄 아는것은 완전 다른 내용같습니다.

위의 주요 요소 외에도 SQL 패턴별로 다양한 인덱스 설계가 나올 수 있으니 실제 업무에서 사용할때는 꼭 따로 공부해봐야겠습니다!

클러스러 인덱스

앞서 설명한 내용은 다 논클러스터드 인덱스입니다.

더 공부해보면 좋을것들

• 인덱스 설계
• 통계 기반 인덱스 사용
• 비용기반 옵티마이저

Reference

MySQL 공식문서 https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

백은빈, 이성욱 저 Real MySQL 8.0

Inpa님 블로그 https://inpa.tistory.com/entry/MYSQL-📚-인덱스index-핵심-설계-사용-문법-💯-총정리