thumbnail
MySQL InnoDB 인덱스 이해하기
MySQL / RDB / InnoDB
2023.08.27.

들어가며

인덱스는 DB 쿼리의 성능에서 빼놓을 수 없는 중요한 요소다.

이번 글은 일반적으로 MySQL InnoDB를 사용하는데 이해하고있어야 할 B-Tree (Balanced-Tree)기반 인덱스와 클러스터링 인덱스등을 정리한다.

이 글은 Real MySQL 8.0책과 MySQL 공식 문서 8.0를 기반으로 작성되었습니다.

1 인덱스란


🤔 인덱스란

  • 인덱스는 테이블에 대한 조회 동작의 속도를 높여주는 자료 구조이다.
    • 비유: DB 인덱스 : 데이터 = 책 색인 : 페이지 번호(책 내용)
    • 특정 책 내용을 찾기 위해 모든 페이지를 펼쳐보는 것보다, 색인을 통해 바로 찾는 것이 검색 속도가 훨씬 빠르다.
  • 인덱스는 테이블 내의 1개 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성되며, 고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다.
  • 인덱스는 데이터의 저장 성능을 희생하고 데이터의 읽기 속도를 높이는 기능이다.

💁‍♂️ 인덱스의 특성

  • 인덱스는 데이터를 저장할 때 항상 미리 정렬해야 하므로 저장하는 과정이 복잡하고 느리다.
    • CUD는 처리가 느리지만, R는 처리가 굉장히 빠르다.
  • 반면에, 인덱스 컬럼에 대해선 정렬되어있기에 아주 빨리 원하는 값을 찾아올 수 있다.

💁‍♂️ 인덱스 종류

인덱스의 종류는 관점에 따라 여러가지로 나눠볼 수 있다.

  • 인덱스의 역할 관점
    • 프라이머리 키: 해당 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스.
      • 기본적으로 NULL값을 허용하지 않으며, 중복을 허용하지 않는다.
    • 세컨더리 인덱스: 프라이머리 키를 제외한 나머지 모든 인덱스. (보조키)
      • 유니크 인덱스도 세컨더리 인덱스이나 프라이머리키와 비슷해서 대체할 수 있다고 대체키라고 불리기도한다.
  • 데이터 저장 알고리즘 관점
    • B-Tree 알고리즘 (InnoDB)
    • Hash 알고리즘 (In-Memory DB)
  • 데이터의 중복 허용 관점
    • Unique 인덱스
    • Non-Unique 인덱스

❗️ OLTP DB 작업의 70프로는 조회가 차지한다고 한다. 인덱스를 설정해줌으로써, 조회 성능을 높이기 위해 인덱스를 이용한다.

2 디스크 읽기 방식

2-1 HDD와 SSD


💁‍♂️ DB 성능 튜닝이란 디스크 I/O 횟수를 줄이기 위함이다

인덱스를 통해 디스크 I/O를 최대한 줄여야겠지만, 그럼에도 디스크 I/O를 아예 피할 순 없다.

이때 HDD보단 SSD가 디스크 I/O 성능 면에서 많은 이점을 가져다준다.


💁‍♂️ SSD가 HDD보다 랜덤 I/O 부분에서 훨씬 빠르다.

HDD는 기본적으로 디스크 원판을 기계적으로 회전시켜 순차 I/O와 비슷하게 필요한 데이터를 찾아하지만, SSD는 플래시 메모리를 사용하기에 기계적으로 회전 시킬 필요가 없다.

그래서 순차 I/O에서는 SSD와 HDD간 성능상 큰 차이는 없지만, 랜덤 I/O에선 SSD가 훨씬 빠르다고한다.

보통 DB 사용 패턴상 작은 데이터를 읽고 쓰기에 랜덤 I/O의 성능이 좋은 SSD를 사용하는 것이 좋다.

2-2 순차 I/O와 랜덤 I/O


💁‍♂️ 디스크 읽는 방식

디스크로부터 데이터를 찾아오는 방법은 순차 I/O와 랜덤 I/O 두 가지다.

출처: wikipedia
출처: wikipedia

  • 순차 I/O (sequantial)
    • 순차 I/O는 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식이다.
    • 인접한 페이지를 여러 개 읽는 다중 페이지 읽기방식으로 수행된다.
  • 랜덤 I/O (random)
    • 랜덤 I/O는 물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식이다.
    • 정해진 순서없이 이동하는 만큼 디스크의 물리적 움직임이 필요하고, 다중 페이지 읽기에 취약하다.

💁‍♂️ 보통 랜덤 I/O의 비용이 더 크다

DB 성능 튜닝의 목적은 디스크 I/O의 횟수를 줄이는 것이다. (정확히는 디스크 I/O 시스템 콜 횟수)

인접한 3개의 데이터를 가져온다고 가정했을 때, 순차 I/O는 디스크 I/O가 1번, 랜덤 I/O는 디스크 I/O를 3번해야하므로 랜덤 I/O가 시스템 콜 횟수가 더 많이 필요하다.

게다가 순차 I/O는 한번만 헤더를 이동하여 필요한 데이터 3개를 가져오는 반면, 랜덤 I/O는 헤더를 3번 이동시켜 데이터 3개를 가져와야하기때문에 디스크 성능에도 좋지 않다.

플래시 메모리인 SSD의 경우에도 랜덤 I/O는 여전히 순차 I/O보다 처리량이 떨어진다.


💁‍♂️ 무조건 순차 I/O가 좋은게 아니다

이렇게보면 순차 I/O만 사용하면 될 것 같지만, 원하는 데이터가 적은 개수라면 순차 I/O로 전체 데이터를 순회하며 찾는 것보단,

인덱스를 통해 필요한 데이터의 위치를 찾고 적은 횟수의 랜덤 I/O로 데이터를 조회하는게 훨씬 빠르다.

즉, 실제 서비스에서의 쿼리는 대부분 랜덤 I/O가 주로 사용되며, 쿼리를 튜닝한다는 것은 랜덤 I/O 횟수 자체를 줄이는 것이 목적이다.

여기서 랜덤 I/O의 횟수를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 말한다.


2-3 인덱스 레인지 스캔과 테이플 풀 스캔


💁‍♂️ DB에서 데이터를 찾는 방법

DB내에서 데이터를 찾는 가장 많이 사용되는 방법은 테이블 풀 스캔과 인덱스 레인지 스캔이다.

  • Table Full Scan
    • 순차 I/O 방식와 MultiBlock I/O방식으로 디스크를 읽어 한 블록에 속한 모든 레코드를 한번에 읽어들이는 방법.
  • Index Range Scan
    • 랜덤 I/O와 Single Block I/O로 레코드 하나를 읽기 위해 매번 I/O를 통해 필요한 레코드를 읽는 방법.

실제로 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔의 경우에만 순차 I/O를 사용한다.


💁‍♂️ Index Range Scan이 무조건 좋은 건 아니다

인덱스를 탄다면 인덱스 레인지 스캔을 사용하게되고 이는 랜덤 I/O를 사용한다는 의미다.

다만 데이터가 일정량을 넘으면 인덱스 레인지 스캔의 경우 매 인덱스마다 랜덤 I/O로 데이터를 가져와야 함으로 다량의 디스크 I/O가 발생하게 된다.

이땐 읽어야하는 데이터 수 자체가 많기에 순차 I/O가 랜덤 I/O보다 훨씬 빨리 데이터를 디스크로부터 가져올 수 있기때문이다.

정리하면 큰 테이블에서 소량 데이터를 검색할 때는 당연히 인덱스 레인지 스캔 (랜덤 I/O) 이 유용하다. 다만, 큰 테이블에서 다량 데이터를 검색할 때는 손익분기점에 따라 테이블 풀 스캔 (순차 I/O)가 유용하다.

어떤 스캔 방식을 사용할지는 MySQL 옵티마이저가 알아서 계산하여 쿼리시 실행한다.

3 B-Tree 인덱스

B-Tree (Balanced-Tree) 알고리즘은 DB의 인덱싱 알고리즘으로 가장 일반적으로 사용된다.

MySQL의 InnoDB도 기본적으로 B-Tree 인덱스를 사용하며, 인덱스뿐만아니라 실제 디스크에 데이터를 저장할 때도 B-Tree 알고리즘 형태로 저장한다. (클러스터링 인덱스)

3-1 구조와 특성


💁‍♂️ B-Tree 인덱스 구조

B-Tree 인덱스를 제대로 사용하려면 기본적인 구조를 이해하는 것이 중요하다.

B-Tree는 트리 구조의 최상위에 루트 노드가 존재하고, 그 하위에 자식 노드가 붙어 있는 형태다.

가장 하단 노드를 리프 노드라하고, 중간을 브랜치 노드라고 부른다.

트리의 균형을 자동으로 맞추는 균형 트리 자료구조이다보니 자료를 정렬된 상태로 보관하고, 빠른 조회를 제공하는 자료구조이다. (O(logN))

B-Tree에 대한 자세한 내용은 B-Tree 이해하기글을 참고.


💁‍♂️ B-Tree 인덱스의 키 값은 모두 정렬되지만, 디스크내 데이터 파일은 임의의 순서로 저장된다.

위 그림에서 알 수 있듯이, B-Tree 인덱스는 균형된 트리 구조로 키 값을 기준으로 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장된다.

즉, 디스크내에선 INSERT된 순서대로 저장되지않는다.

인덱스는 테이블 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 데이터를 찾아야한다. 이를 위해 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가진다.


💁‍♂️ 더 정확히는 B+Tree를 사용한다

실제 InnoDB의 경우는 B-Tree의 변형된 알고리즘인 B+Tree를 사용한다.

B+Tree는 B-Tree에 더해 리프 노드들끼리 ‘연결성’이 추가된 자료구조다.

즉, 리프 노트간의 연결되어있으므로, 특정 리드 노드의 위치를 알게되면 브랜치 노드로향하지않고 오른쪽 방향으로해서 다른 리드 노드를 바로 탐색할 수 있어 범위 검색 측면에서 유리하다.

B-Tree나 B+Tree나 모두 Balanced Tree라는 점은 동일해서인지, 공식문서에선 B-Tree라고만 표기하고있다. (다만 몇몇 공식 블로그에선 B+Tree라고 명시하고있기도하다.)


💁‍♂️ InnoDB는 데이터 파일도 인덱스 형태로 저장한다 - 클러스터링 인덱스

대부분의 RDBMS는 데이터 파일에서 레코드를 특정 기준으로 정렬하지않고 임의의로 순서로 저장한다.

다만 InnoDB는 레코드를 클러스터되어 디스크에 저장되므로 기본적으로 아래와 같이 PK 순서로 정렬되어 저장된다.

이를 클러스터링 인덱스라 부르며, 이런경우 인덱스의 키의 값으로 데이터 파일의 ROWID가 아닌 해당 ROW의 PK값이 인덱스 값이 된다.

클러스터링을 사용하지않는 MyISAM의 경우 세컨더리 인덱스가 물리적인 주소를 가지는 반면, InnoDB는 프라이머리 키 (PK)를 주소처럼 사용한다.

이로인해 InnoDB의 경우 인덱스틀 통해 레코드를 읽을 때 데이터 파일내 레코드에 바로 찾아가지 못한다. 대신 프라이머리 키 (PK) 인덱스를 한번 더 검색한 후, PK 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.

InnoDB의 경우 PK값을 기준으로 디스크내 인덱스 검색을 한번 더 해야하기때문에, InnoDB에서 PK 설정은 굉장히 중요하다. 자칫 성능이 안좋아질 수 있기 때문.

3-2 B-Tree 인덱스 키 추가/삭제/변경/조회


💁‍♂️ 인덱스 키 추가

B-Tree내 데이터 삽입과 동일하다.

  1. 요소 삽입에 적절한 Leaf 노드를 검색 및 선택. (하향식)
  2. 선택한 Leaf 노드에 여유가 있다면 삽입. 없다면 분할 및 병합. (상향식)

이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업에 비용이 비교적 많이 든다.

이로인해 InnoDB는 체인지 버퍼등을 사용해 인덱스 키 추가 작업을 지연시켜 나중에 처리하기도한다.

데이터 삽입이 1의 비용이라면 인덱스 하나당 대략 1.5의 비용이 든다고한다.


💁‍♂️ 인덱스 키 삭제

삭제처리는 삭제하고자하는 B-Tree내 리프 노드를 찾아서 그냥 삭제 마크만하면 된다.

삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다.

물론 이또한 InnoDB의 경우는 버퍼링되어 지연 처리될 수도 있다고한다.


💁‍♂️ 인덱스 키 변경

B-Tree 특성상 위치 자체가 키 값 기준이기에, 단순히 키 값만을 수정하는 것은 불가능하다.

그리하여 B-Tree 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다. - 중요

결국 인덱스 키 값을 변경하는 작업은 기존 인덱스 키 값을 삭제한 후 새로운 인덱스 키 값을 추가하는 작업을 처리되며, InnoDB의 경우 이에 대한 처리는 모두 체인지 버퍼를 통해 지연처리 될 수 있다.

3-3 B-Tree 인덱스 성능에 영향을 미치는 요소

B-Tree 인덱스는 인덱스를 구성하는 컬럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능에 영향을 준다.

3-3-1 인덱스 키 값의 크기

💁‍♂️ InnoDB 디스크에 데이터를 저장하는 기본 단위는 페이지다.

운영체제에서의 페이지와 유사한 것으로 보인다. 혹은 동일.

페이지는 InnoDB내 실제 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. (버퍼 풀의 버퍼링 기본 단위이기도하다)


💁‍♂️ B-Tree 자식 노드 개수는 인덱스 페이지 크기와 키 값 크기에 따라 결정된다.

일반적으로 B-Tree의 자식 노드 개수는 가변적이다. 개수는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.

이때 페이지 크기는 innodb_page_size를 통해 설정할 수 있으며, 기본값으론 16KB를 사용한다.

한 페이지가 16KB라 가정하고 키와 값을 16바이트, 12바이르 구성한다면 아래와 같다.

그럼 16 * 1024 / (16 + 12) = 585개의 키를 저장할 수 있다. 즉, 자식 노드를 585개 가질 수 있는 B-Tree가 되는 것.

이때 만약 키 값이 커져서 두 배인 32바이트라고 가정한다면 16 * 1024 / (32 + 12) = 372개가 된다.

만약 SELECT 쿼리에 500개를 읽어야 한다면 전자는 한번에 읽을 수 있지만, 후자는 최소 2번 이상 디스크로부터 읽어야한다.

정리하면 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나게되고, 그만큼 느려지게된다.

인덱스 크기는 가능한 작게하는게 이득

3-3-2 B-Tree 깊이

💁‍♂️ 인덱스 키 값의 크기가 커질수록 깊이는 깊어져 성능이 떨어진다.

B-Tree의 깊이는 중요하지만 직접 제어할 방법은 없다.

B-Tree의 깊이가 3이라고 가정하고 인덱스의 키값을 비교하면 아래와 같다.

  • 인덱스 키 값 16byte -> 585 * 585 * 585 = 2억
  • 인덱스 키 값 32byte -> 372 * 372 * 372 = 5천만

당연히 5천만 이상의 데이터를 담아야한다면 16byte는 그대로 깊이가 3이고, 32byte의 경우 깊이가 4가 될 것이다.

B-Tree의 깊이는 깊을수록 검색할 때 몇 번이나 랜덤하게 디스크를 더 읽어야하므로 16byte가 효율적이라고 볼 수 있다.

3-3-3 카디널러티 (기수성)

선택도 (Selectivity) = 기수성 (Cardinality)

💁‍♂️ 카디널러티는 유니한 값의 수를 말한다

카디널러티는 모든 인덱스 키 값 가운데 유니크한 값의 개수를 의미한다.

전체 인덱스 100개중, 유니크한 값이 10개면 카디널리티는 10이다.


💁‍♂️ 카디널러티는 높을 수록 효율적이다

인덱스는 카디널리티가 높을 수록 검색 대상이 줄어들기 때문에 조회할 때 더 효율적이다

예를 들어 1만건의 데이터가 있고 아래 쿼리를 실행한다고 가정해본다.

SELECT *
    FROM tb_test
    WHERE country = 'KOREA' AND city = 'SEOUL';

위 쿼리를 실행할 때 인덱스의 케이스별로 어떤 결과를 나타나는지 살펴보면 아래와 같다.

  • 케이스 A: country 컬럼의 유니크한 값이 10개인 경우
    • 유니크한 값이 10개이므로 10개 국가가 존재한다는 것이며, 각 국가별 10000/10 = 1000개의 도시가 존재한다는 의미다.
    • 즉, SEOUL 하나 찾기위해 999건의 불필요한 데이터를 읽어 비교해야한다.
  • 케이스 B: country 컬럼의 유니크한 값이 1000개인 경우
    • 유니크한 값이 1000개이므로 1000개 국가가 존재한다는 것이며, 각 국가별 10000/1000 = 10개의 도시가 존재한다는 의미다.
    • 즉, SEOUL 하나 찾기위해 9건의 데이터를 읽어 비교하면 된다.

위 예시에서 알 수 있듯이, 카디널리티가 높을수록 쿼리가 효율적이게된다.

3-3-4 읽어야 하는 레코드 건수

인덱스를 이용한 읽기의 손익 분기점이 얼마인지에 따라 인덱스를 통한 여러 차례의 랜덤 I/O가 발생 할 수 있다.

즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블 모두를 순차 I/O로 읽어서 필요한 레코드만 필터링한다.

3-4 B-Tree 인덱스를 통한 데이터 조회 (인덱스 스캔)

InnoDB내에서 인덱스를 활용한 데이터 조회는 어떤게 있는지 살펴본다.

3-4-1 인덱스 레인지 스캔


💁‍♂️ 인덱스 레인지 스캔은 가장 대표적인 인덱스 사용 조회 방식이며, 아래와 같이 동작한다.

  1. 인덱스 탐색 - 루트 노드를 시작으로, 브랜치 노드를 거쳐 리프 노드의 시작점을 찾는다. 그리고 찾은 위치부터 리프 노드의 레코드만을 순서대로 읽는다.
    • 스캔하다가 리드 노드의 끝까지 읽으면 리프 노드 간의 링크를통해 다음 리프 노드를 찾아서 다시 스캔한다.
  2. 데이터 액세스 - 인덱스로 탐색한 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O를 통해 가져온다.
    • 물론 InnoDB의 경우 PK 기준으로 정렬하여 Disk에 저장하고있으며, 매번 PK의 클러스터링 인덱스를 통해 데이터를 액세스한다.

💁‍♂️ 랜덤 I/O의 손익분기점에 따라 순차 I/O가 효율적일 수도 있다.

인덱스 레인지 스캔은 인덱스 검색후 랜덤 I/O를 통해 데이터를 가져오기에, 인덱스를 통해 읽어야 할 데이터가 전체 데이터의 25%정도가 넘으면 MySQL 옵티마이저는 순차 I/O로 데이터를 가져와서 필터링한다.


💁‍♂️ 필요한 데이터만 SELECT하는게 좋다 - 커버링 인덱스

만약 인덱스 컬럼만을 조회하는거라면 굳이 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 I/O의 횟수를 줄여 성능을 높일 수 있다.

이를 커버링 인덱스라고 부르며, 가능한 인덱스에 존재하는 데이터만을 통해 조회하는게 빠르다.

3-4-2 인덱스 풀 스캔

💁‍♂️ 말그대로 인덱스의 처음부터 끝까지 모두 읽는 방식이다

  1. 인덱스 풀 스캔 - 인덱스의 처음부터 끝까지 모두 읽는다.
  2. 데이터 액세스 - 인덱스로 찾은 레코드를 랜덤 I/O를 통해 가져온다.

💁‍♂️ 쿼리가 인덱스에 명시된 컬럼만으로 조회할 수 있을때 사용된다.

일반적으로 인덱스의 크기는 테이블 크기보다 작기때문에 직접 테이블을 처음부터 읽는 테이플 풀 스캔보단 인덱스만 읽는 것이 효율적이다.

대표적으로 인덱스는 (A, B, C)로 걸었는데 필요한 데이터가 (B, C)인 커버링 인덱스인 경우 사용된다.

즉, 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 이 방식이 사용된다.

테이블 풀 스캔보단 인덱스 풀 스캔이 적은 디스크 I/O로 쿼리를 처리할 수 있어 더 효율적이다.

3-4-3 루스 인덱스 스캔

인덱스 스킵 스캔이라고도 불리며, 말 그대로 인덱스를 듬성듬성 읽는 방식이다.

주로 GROUP BY 또는 집합 함수 가운데 MAX(), MIN() 사용시 최적화하기위해 사용된다. (MYSQL 8.0부터 사용가능)

예시

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

위 예시에서 인덱스가 이미 (dept_no, emp_no)로 되어있어 이미 정렬까지 된 상태라면, 인덱스내 범위 전체를 스캔할 필요없이 찾은 dept_no 기준 emp_no의 MIN, MAX 값을 찾고 바로 다음 dept_no로 스킵한다.

3-5 다중 칼럼 인덱스 (복합 인덱스)


💁‍♂️ 다중 컬럼 인덱스란

  • 두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스라고 하며, 2개 이상의 컬럼이 연결됐다고해서 Concatenated Index라고도 불리운다.
    • 실제 서비스에선 2개 이상의 컬럼을 포함하는 인덱스가 훨씬 더 많이 사용된다.

💁‍♂️ 다중 컬럼 인덱스는 왼쪽 컬럼 값을 기준으로 오른쪽 컬럼 값이 정렬된다

다중 컬럼 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬된다. 즉, 두번 째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드인 경우에만 의미가 있다.

만약 컬럼 4개인 다중 인덱스가 생성된다면 세 번째 컬럼은 두 번째 컬럼에 의존해서 정렬되고 네 번째 컬럼은 다시 세 번째 컬럼에 의존해서 정렬된다.

즉, 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 매우 중요하다.


💁‍♂️ 컬럼의 순서뿐만 아니라 쿼리 조건이 동등, 범위에 따라 컬럼의 효율이 달라진다. - 중요

컬럼의 앞 순서일수록 카디널리티가 높아야 쿼리의 성능이 좋다.

즉, 인덱스내 비교 연산 자체를 줄이기위해선 가능한 앞에서 좁은 작업 범위를 결정해줘야 쿼리의 효율이 좋게된다


💁‍♂️ 인덱스의 가용성

B-Tree는 기본적으로 왼쪽 값에 기준해서 오른쪽 값이 정렬된다.

그러므로 왼쪽 부분이 없으면 인덱스를 타는 것 조차 안된다. 그러므로 컬럼이 하나라도 아래 쿼리들은 인덱스 풀 스캔이나 테이블 풀 스캔이 발생하게된다.

SELECT * FROM employees WHERE first_name LIKE '%mer';

3-6 B-Tree 인덱스의 정렬 및 스캔 방향

인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이나 내림차순으로 저장된다.

하지만 오름차순으로 인덱스가 생성됐어도 오름차순/내림차순 모두 인덱스로 정렬하여 조회할 수 있다.


💁‍♂️ MySQL 8.0부터 인덱스내 컬럼의 정렬 순서를 다르게 설정할 수 있다.

MySQL 5.7까지는 컬럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없었다.

하지만 8.0부터는 아래와 같이 정렬 순서를 혼합한 인덱스 생성이 가능해졌다.

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

💁‍♂️ 인덱스내 정렬 설정에 따른 성능 차이가 있을 수 있다

하나의 인덱스를 정순으로 읽느냐(ASC) 또는 역순으로 읽느냐(DESC)에 따라 쿼리 성능이 발생할 수 있다.

그 이유는 InnoDB에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결 고리 (Double linked list)를 통해 전지 하느냐 후진 하느냐에 차이만 있지만,

실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느리다고한다.

그 이유는 페이지 감금이 인덱스 정순 스캔에 적합한 구조이기 때문이라고한다.

그러므로 만약 조회를 DESC로 해야한다면 DESC로 인덱스를 생성하고, ASC로 한다면 ASC로하는 것이 좋다.

다만 이는 소량의 레코드에 드물게 실행되는 경우라면 굳이 고려안해도 된다.

4 클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로 사용되는데, 여기서의 클러스터링도 비슷한 의미도 사용된다.

즉, 테이블의 레코드를 비슷한 것들끼리 묶어서 저장하는 형태로 구현하며, 보통 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안하였다고 한다.


4-1 클러스터링 인덱스


💁‍♂️ PK 값에 따라 레코드의 물리적인 저장 위치가 결정된다.

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다.

중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 또한 PK 값이 변경되면 그 레코드의 물리적인 저장 위치가 바뀌어야한다는 의미이기도하다.

PK 값으로 클러스터링된 테이블은 PK 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 결정해야한다.


💁‍♂️ 클러스터링 인덱스는 인덱스 알고리즘보단 테이블 레코드의 저장 방식이라고 볼 수 있다.

InnoDB는 항상 클러스터링 인덱스 방식으로 레코드의 물리적인 저장 위치를 정한다.

그러다보니 PK 기반의 검색이 매우 빠르긴하지만, 레코드의 저장이나 PK의 변경이 상대적으로 느리다.

보통 테이블의 레코드가 PK 값으로 정렬되어 저장된 경우만 “클러스터링 인덱스” 또는 “클러스터링 테이블”이라고한다.


실제로 위와 같이 클러스터링 인덱스의 리프 노드에 레코드의 모든 컬럼이 같이 저장돼 있는 구조이다.

즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리된다.


💁‍♂️ PK 설정이 없는 경우 다음 우선순위대로 PK 키를 대체할 컬럼을 선택한다.

  1. PK가 있으면 기본적으로 PK를 클러스터링 키로 선택한다.
  2. NOT NULL 옵션의 유니크 인덱스중에서 첫 번째 인덱스를 클러스터링 키로 선택한다.
  3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택한다.

3번의 경우, 실제로 PK가 존재하지만 실질적으로 접근이 불가하여 아무런 혜택이 되지 못한다.

그럼으로 InnoDB에서 클러스터링 인덱스는 테이블 단 하나만 가질 수 있는 엄청난 혜택이므로 가능한다면 PK를 명시적으로 생성하는 것이 좋다.


4-2 세컨더리 엔덱스에 미치는 영향 - 중요

PK가 세컨더리 인덱스에 어떤 영향을 미치는지 살펴본다.


💁‍♂️ InnoDB의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 PK값을 저장한다.

MyISAMdㅣ나 MEMORY 테이블 같이 클러스터링되지 않은 테이블은 INSERT 될 때 처음 저장된 공산에서 절대 이동하지 않는다.

그리고 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디 (ROWID) 역할을 한다. 그리고 인덱스가 이 ROWID를 값으로 저장하여 접근한다.

반면 InnoDB의 경우 인덱스의 값으로 PK의 값이 저장된다. 이로인해 PK 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야한다.

이런 오버헤드를 제거하기 위해 InnoDB의 모든 세컨더리 인덱스는 해당 레코드가 저장된 구조가 아닌 PK 값을 저장하도록 구현돼있다.

다시말해 PK의 값을 수정하게된다면 디스크내 저장되는 위치가 달라지기에, 해당 PK 값을 가리키는 인덱스도 모두 같이 수정해줘야한다. 이로인해 UPDATE에 대한 비용이 비교적 크다.

when the value of a Primary Key (PK) in an InnoDB table is changed, all Secondary Indexes that refer to that Primary Key will also need to be updated

by chat-GPT
by chat-GPT


💁‍♂️ 가급적 PK의 값은 수정하지 않는 것이 좋다. - 중요

위와 같이 PK의 값이 변경됨에 따라 해당 PK를 가르키고있는 인덱스도 모두 수정해줘야하기때문에 InnoDB내 PK 수정을 가급적 피하는게 좋다.

즉, 절대적으로 필요하지 않는 한 PK 키 값을 수정하지 않는 것이 좋습니다.

대신 Auto Increment 값이나 UUID와 같은 대체 키처럼 안정적이고 수정이 필요하지 않는 기본 키를 사용하는 것이 좋다.


4-3 클러스터링 인덱스의 장단점


💁‍♂️ 장점

  • PK로 검색할 때 처리 성능이 매우 빠르다.
  • 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음. (커버링 인덱스)

💁‍♂️ 단점

  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
  • 세컨더리 인덱스를 통해 검색할 때 PK로 다시 한번 검색해야 하므로 처리 성능이 느려진다
  • INSERT할 때 PK에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느린다.
  • PK를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느리다.

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


💁‍♂️ 클러스터링 인덱스 키의 크기

앞서 말했듯이 모든 세컨더리 인덱스는 PK 키 값을 포함한다. 그래서 PK의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

이때 세컨더리 인덱스의 개수가 많을 수록 그 크기는 더더욱 커지게 된다. 아래는 세컨더리 인덱스 5개가 있다는 가정하에 PK 크기에 따른 인덱스의 크기다.

  • PK 크기 - 10바이트
    • 레코드당 증가하는 인덱스 크기: 10바이트 * 5 = 50바이트
    • 100만 건 레코드 저장 시 증가하는 인덱스 크기: 50 * 1,000,000 = 47MB.
  • PK 크기 - 50바이트
    • 레코드당 증가하는 인덱스 크기: 50바이트 * 5 = 250바이트
    • 100만 건 레코드 저장 시 증가하는 인덱스 크기: 250 * 1,000,000 = 238MB.

이렇게 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야한다.


💁‍♂️ PK는 Auto-Increment보다는 업무적인 컬럼으로 생성 (하는 경우)

PK 값에 의해 레코드의 위치가 결정된다. 즉, PK로 검색하는 경우 클러스터링 되지 않는 테이블에 비해 매우 빠르게 조회할 수 있다.

그러므로 설령 그 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 PK로 설정하는 것도 좋은 방법이다.


💁‍♂️ PK는 반드시 명시할 것

가능하면 Auto-Increment 컬럼을 이용해서라도 PK를 생성하는 것이 좋다.

DB에의해서 자동으로 생성되는 PK 값은 사용자가 활용할 수 없기 때문이다.


💁‍♂️ Auto-Increment 컬럼을 인조 식별자로 사용할 경우

여러 개의 컬럼이 복합으로 PK가 만들어지는 경우 PK의 크기가 길어질 때가 가끔 있다.

하지만 PK의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 PK키를 사용하는 것이 좋다. - 중요

세컨더리 인덱스도 필요하고 PK 키의 크기도 길다면 Auto-Increment 컬럼을 추가하고, 이를 PK로 설정하면 된다. - 중요

이렇게 PK를 대체하기 위해 인위적으로 추가된 PK를 인조 식별자하고 부른다.


5 유니크 인덱스


💁‍♂️ 유니크는 인덱스라기보단 제약 조건에 가깝다

말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하며, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

UK에 NULL로 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다. 이때 PK의 경우는 NULL이 될 수 없다.


💁‍♂️ 유니크 인덱스와 일반 세컨더리 인덱스의 차이 - 읽기 관점

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 인덱스의 구조상 아무런 차이가 없다.

인덱스의 읽기 관점에서 보면, 유니크와 유니크하지않는 일반 세컨더리의 성능 차이는 거의 없다. 그저 비교하는 CPU 성능이 필요한데 이는 크게 영향을 주지 않는다.

대신 유니크하지 않는 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 하는 레코드가 많아 디스크 I/O가 더 잦아 느리다. - 중요

결국 중요한 것은 디스크 I/O가 몇번 발생하느냐인데, 유니크 인덱스는 보통 한번이기에 빠를 수 있다는 것.


💁‍♂️ 유니크 인덱스와 일반 세컨더리 인덱스의 차이 - 쓰기 관점

유니크 인덱스는 INSERT시 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 추가된다.

그래서 보통 유니크 인덱스가 일반 세컨더리 인덱스보다 쓰기부분에서 더 느리다.

그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.

또한 일반 세컨더리 인덱스는 체인지 버퍼로 조금 더 효율적으로 쓰기를 실행하는 반면 유니크 인덱스는 체인지 버퍼를 타지 못한다.

그러므로 일반 세컨더리 인덱스보다는 변경 작업이 더 느리다.


💁‍♂️ 유니크 인덱스 사용 시 주의사항

  • 중복 생성
    • 하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성하는 경우가 가끔 있는데, MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할이므로 중복해서 인덱스를 생성할 필요가 없다.
    • 즉, 유니크 인덱스도 일반 세컨더리 인덱스와 같은 역할을 동일하게 수행한다.
  • PK와 유니크 인덱스를 동일하게 생성하는 것은 피해야한다.
    • 똑같은 컬럼에 대해 PK와 유니크 인덱스를 동일하게 생성하는 경우가 있는데, 이는 불필요한 쓰기 작업의 지연만 초래한다. 중복이므로 주의하는 것이 좋다.
  • 유일성이 꼭 보장돼야 하는 컬럼에 대해서만 유니크 인덱스를 생성하자.

6 외래키


💁‍♂️ 외래키 제약이 설정되면 자동으로 연관된 테이블의 컬럼에 인덱스까지 생성된다.

InnoDB에서 외래키를 생성하면, 외래키 제약이 설정된 테이블의 컬럼에 인덱스까지 생성된다.

그리고 외래키가 제거되지 않는 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.


💁‍♂️ InnoDB 외리캐 두 가지 특징

  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

💁‍♂️ 외래키는 꼭 필요한 경우에만 사용하는 것이 좋다. 가급적 사용 안하는 것을 추천한다.

외래키를 사용하면 정합성, 무결성 유지 편익이 존재하지만 다음과 같은 이유로 개인적으로 사용하는 것을 추천하지 않는다.

  • 확장성, 유연성이 좋지 않다.
    • 설계 수정을 진행하거나 마이그레이션, 샤딩등을 할 때 방해요소가 된다.
    • 관계가 명확하다면 문제가 없겠지만, 외래키가 복잡하게 얽힌 경우엔 수동으로 데이터 적재나 변경 관리 작업 실수를유발한다.
  • 성능 저하
    • 외래키도 어쨋듯 인덱스가 생성되는 것이므로, UPDATE/DELETE시 외래키로 성능 저하가 발생한다.
  • 운영 대응할 때 비효율적이다
    • 실무에선 수작업으로 데이터를 다루는 경우가 빈번하다. 이때 테이블의 관계상 데이터 생성 순서가 맞지 않으면 에러가 발생한다. 정말 번거롭다.
    • 그리고 만약 FK cascade라고 걸려 있으면 원치 않는 데이터가 삭제되는 대참사가 일어날 수 있다.

위와 같은 이유로 무결성 유지는 DB가 아닌 애플리케이션 단에서 Validate를 최대한 꼼꼼하게 잡아주는 것이 좋다.


© mark-kim.blog Built with Gatsby