MySQL을 통해 SQL 파헤치기 -3- 인덱스

1.0 Overview

MySQL 8.0 이후로, InnoDB가 관여하는 부분에서 성능에서 critical 한 부분은 대부분 개선된 걸로 알고 있다. 그런데, 그건 대부분 시스템 관리 부분, 혹은 하드웨어상에서 비효율적으로 작동되는 부분을 개선했을 뿐이지, 개발자의 쿼리문을 자동으로 최적화해주는 옵티마이저의 경우는, 쿼리의 종류에 따라, 최적화를 효과적으로 처리할 수도 있고, 아닐 수도 있다.

특히 인덱스의 경우, 종류에 따라 개발자가 어떻게 구성하느냐에 따라, 효율적인 검색 성능을 나타낼 수도 있고 아닐수도 있다. 즉 쿼리 튜닝에 있어서 인덱스는 절대 빼놓을 수 없는 부분이라 할 수 있다.

1.1 디스크 읽기 방식

1.1.1 HDD와 SSD

• HDD는 다른 하드웨어와는 달리 기계식 디스크 장치이다.
• SSD는 플래시 메모리를 장착하여, 전자식으로 동작하는 디스크 장치이다.
• SSD의 순차IO 성능은 HDD와 큰 차이는 없지만, 랜덤 IO의 경우 HDD보다 훨씬 더 뛰어나다.
• 메모리와 HDD의 처리속도 차이느 10만 배 이상.
• 메모리와 SSD의 처리속도 차이는 1000배 정도.
• SSD는 비싸다.

1.1.2 순차IO와 랜덤 IO

• 순차 IO는 페이지를 최대한 쌓아서, 시스템 콜 요청.
• 랜덤 IO는 페이지 처리마다, 시스템 콜 요청.
• 인덱스 레인지 스캔은 주로 랜덤 IO를 사용.
• 풀 테이블 스캔은 주로 순차 IO를 사용.

1.2 인덱스란?

• 인덱스란 B-Tree 알고리즘 기반으로 정렬된 SortedList의 형태로 레코드 주소를 보관하는 영역을 말한다.
• DML의 INSERT, UPDATE, DELETE 성능을 희생하고, SELECT를 개선시키는 의도로 많이 활용된다.
• InnoDB에서 PK를 담은 인덱스를 클러스터링 인덱스 혹은 클러스터링 키라 고도하며, 유니크 인덱스의 제약이 걸려있다.
• PK를 제외한 나머지 인덱스를 세컨더리 인덱스로 분류한다.
  • 대체 키라 고도 부른다.
• 인덱스를 저장하는 알고리즘은 크게 B-tree, Hash 인덱스 알고리즘이 있다.
  • Hash 인덱스 알고리즘의 경우 메모리 기반 데이터베이스에서 많이 사용한다.
• 보통 인덱스는 기능별로 전문 검색용 인덱스나 공간 검색용 인덱스로 분류해볼 수 있다.

1.3 B-Tree

• 가장 기본적이고 오래된 인덱싱 알고리즘이다.
• B-Tree의 B는 'Balanced'이다.(이진 탐색 트리와 헷갈리면 안 된다.)
• B-Tree알고리즘을 통해 인덱스의 정렬된 상태를 유지하기 때문에, 전문 검색과 같은 특수한 조건이 아닌 이상, 대부분의 인덱스는 이 방식의 정렬 알고리즘에 적합하다.

1.3.1 InnoDB의 B-tree 인덱스 구조

• 인덱스의 ROWID 역할은 pk가 담당한다.
• 즉, InnoDB 테이블은 pk를 주소처럼 사용하여, 논리적인 주소를 가진다고 본다.
• 세컨더리 인덱스 검색에서 반드시, pk를 가진 B-Tree를 다시 한번 검색해야 한다.

1.3.2 B-Tree 인덱스 추가

• 인덱스의 리프 노드가 가득 찬 경우, split이 일어나 추가 비용이 발생한다.
• 인덱스 추가로 인해 INSERT, UPDATE 문에 끼치는 영향을 계산하기 위해 확인해야 할 요소는 다음과 같다.
  • 테이블 칼럼 수
  • 칼럼의 크기
  • 인덱스 칼럼의 특성
  • 인덱스 추가로 인한 write DML 영향 = (인덱스 키 추가 작업비용 : 1.5) * (추가 이전에 있던 인덱스의 개수) + (레코드 추가 갯수)
• 보통 인덱스 추가 비용을 체인지 버퍼를 활용해 지연 처리하여, 최적화가 가능하지만, pk와 같은 유니크 인덱스의 경우 중복 체크로 인해 즉시 추가/삭제가 발생한다.(즉시 랜덤 IO)

1.3.3 인덱스 키 삭제

• B-Tree의 리프 노드를 찾아서 삭제 마크를 한다.(즉 조회를 한 다음에 삭제한다.)
• 삭제 마킹된 공간은 방치 및 재활용이 가능하다.
• 추가와 마찬가지로 디스크 IO가 필요한 영역이다.
  • InnoDB의 경우 8.0 이상의 환경에서 체인지 버퍼를 통한, 지연 삭제 처리가 가능하다.

1.3.4 인덱스 키 변경

• 인덱스의 키는 인덱스의 리프 노드의 위치를 결정하는 요소이기에 단순히 인덱스 상의 키 값을 변경하는 것은 불가능하다.
• 즉, 변경하고자 하는 인덱스를 삭제하고 다시 추가하는 과정을 거친다.
• InnoDB의 경우, 체인지 버퍼를 통한 지연 처리가 가능하다.

1.3.5 B-Tree 인덱스 사용에 영향을 미치는 요소

• 인덱스 키 값의 크기
  • InnoDB 스토리지 엔진에서 디스크에 데이터를 저장하는 기본 단위를 Page 또는 Block이라 한다.
  • B-Tree와 같은 가변적인 구조에서 인덱스의 키 값이 커지면, 메모리에 캐시 할 수 있는 레코드의 개수가 줄어든다.
• B-Tree 깊이
  • 깊어질수록 랜덤 하게 디스크를 읽는 비율이 증가한다.
  • 근데 이 경우 웬만한 대용량 데이터베이스라도 5 depth를 넘지 않을 정도로 영향은 매우 적다.
• 선택도(기수성)
  • 유니크한 값의 수
  • 인덱스는 선택도가 높을수록 검색 대상이 줄어들며, 검색 속도 역시 빨라진다는 점을 알아두면 될 것 같다.
• 읽어야 하는 레코드의 건수
  • 일반적으로 인덱스를 통한 테이블 레코드 검색은 바로 테이블 레코드를 검색하는 것보다 높은 비용이 든다.
  • 따라서, 검색하는 레코드의 건수에 따라, 인덱스 레인지 스캔이나 인덱스 풀 스캔을 해야 할지, 혹은 풀 테이블 스캔을 해야할지 결정을 해야 한다.
    • 효율적인 선택을 하기 위해선, 항상 검색하는 방식에 따라 실질적인 레코드 검색 건 수의 손익분기점을 분석해야 한다.

1.3.6 인덱스 레인지 스캔

• 검색하는 인덱스의 수와 상관없이 모두 '인덱스 레인지 스캔'이라 한다.
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
• 어떤 방식으로 검색하든, 인덱스를 구성하는 칼럼의 정렬 상태에 맞게 레코드를 가져온다.(역순 혹은 정순)
• 인덱스 레인지 스캔의 경우 레코드 검색 한건당 랜덤 IO가 발생한다.
• 인덱스 레인지 스캔의 과정은 다음과 같다.
  1. 조건에 따른 인덱스 탐색(index seek)을 통해 만족하는 조건이 있는 값의 위치 조회
  2. 해당 위치를 조회하여 필요한 만큼 인덱스를 차례대로 읽는다. (여기까지의 과정을 인덱스 스캔이라 하기도 한다.)
  3. 2번에서 조회한 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

1.3.7 인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식.
• 인덱스 풀 스캔의 경우, 테이블 풀 스캔보다 더 적은 디스크 IO로 처리 가능하다.

1.3.8 루스 인덱스 스캔

• 인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
• GROUP BY 또는 MAX(), MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
• 사용조건이 까다롭다.

1.4 클러스터링 인덱스

• 클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용된다.
• MySQL에서는 InnoDB 스토리지 엔진에만 지원된다.
• pk를 기준으로 정렬된 상태의 인덱스를 유지한다.
• 클러스터링 인덱스, 클러스터링 테이블, 클러스터링 키 모두 거의 같은 의미로 사용된다.
• pk가 없는 InnoDB 테이블의 경우, 먼저 NOT NULL 제약이 있는 유니크 칼럼을 찾고 그래도 없으면, 내부적으로 보이지 않은 칼럼을 만들어 클러스터링 키로 선택한다.
  • 따라서, pk를 지정하지 않는 건 결과적으로는 아무런 혜택을 주지 않는다.
  • 항상 명시적으로 pk를 지정하자

1.4.1 세컨더리 인덱스에 미치는 영향

• 세컨더리 키의 변경으로 인한 오버헤드를 줄이기 위해, 모든 세컨더리 인덱스는 해당 레코드의 저장된 주소가 아닌 pk 값을 저장하도록 구현되어있다.
• 즉 세컨더리 인덱스는 클러스터링 인덱스의 장점을 공유하며, 결과적으로는 성능 저하에 영향을 많이 끼치지 않는다.

1.4.2 클러스터링 인덱스의 장점과 단점

• 장점
  • pk 검색 처리 성능이 빠름.
  • 모든 세컨더리 인덱스는 클러스터링 키를 가지고 있기 때문에, 인덱스만으로 쿼리를 처리하는 '커버링 인덱스'에 유리하다.
• 단점
  • 클러스터링 키의 크기가 너무 클 경우, 전체적인 인덱스 크기가 커진다.
  • 세컨더리 인덱스를 통해 검색할 때 pk로 다시 검색해야 하므로, 처리 성능이 느리다.
  • INSERT 시 pk에 의해 레코드 저장 위치 결정되기 때문에 처리 성능이 느리다.
  • pk를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하여 처리 성능이 느려진다.

1.4.3 클러스터링 테이블 사용 시 주의사항

• 클러스터링 인덱스 키의 크기
  • 너무 클 경우, 메모리에 여유 공간이 줄어든다.
• pk는 꼭 명시할 것
  • 그렇지 않으면, 내부 AUTO_INCREMENT 일련번호 칼럼을 추가한다.
  • 그렇게 때문에, 클러스터링 키를 형성할 때 발생하는 불이익을 얻으면서, 클러스터링 키 조회를 통한 조회 성능 확보를 포기하는 상황이 발생한다.
• AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우
  • 세컨더리 인덱스가 별로 없다면 InnoDB의 기본 AUTO-INCREMENT에 의존해도 된다.
  • 반대의 경우 성능 향상을 위해, 인위적으로 AUTO-INCREMENT pk 식별자를 사용하는 것이 좋다.

1.5 유니크 인덱스

• 인덱스라기보단, 제약에 가깝다.

1.5.1 인덱스 읽기

• 세컨더리 인덱스에서 발생하는 추가 조회 기능은 cpu에서 발생하기 때문에, 유니크 인덱스 조회라고 사실 그렇게 크게 성능이 증가하지는 않는다.
• 즉 유니크 인덱스틑 인덱스의 성격을 나타내는 것일 뿐, 큰 의미는 없다.

1.5.2 인덱스 쓰기

• 유니크 인덱스의 쓰기의 경우, 유니크 인덱스의 제약을 검증하는 과정을 가지기 때문에 세컨더리 인덱스의 쓰기보다 느리다.
• 이 경우 데드락을 방지하기 위해 체인지 버퍼를 사용하면 좋지만, 유니크 인덱스의 경우 작업 자체를 버퍼링 하지 못한다.

1.5.3 유니크 인덱스 사용 시 주의 사항

• 이미 pk로 유니크 인덱스를 처리해놓았다면, 다른 칼럼에 유니크 인덱스를 처리할 필요가 없다.
• 유일성을 꼭 보장해야 하지 않는다면, pk 이외의 유니크 인덱스 사용은 자제하자.

1.6 외래 키

• InnoDB 스토리지 엔진에서만 생성할 수 있는 키.
• 외래 키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.
• InnoDB의 외래 키의 경우 다음과 같은 특징을 가졌다.
  • 테이블의 변경(쓰기 락)이 일어날 경우에만 락 경합(락 대기)이 발생한다.
  • 외래 키와 연관되지 않은 칼럼의 변경은 최대한 락 경합(락 대기)을 발생시키지 않는다.
• 외래 키의 락은 연관 테이블까지 전이(CASACADE)된다.