MySQL B-Tree 인덱스

사람·2026년 8월 25일

MySQL

목록 보기
9/9

1. 디스크 읽기 방식


데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다. 따라서 데이터베이스의 성능 튜닝에서 디스크 I/O를 어떻게 줄이느냐가 관건일 때가 많다.

1) Solid State Drive(SSD)

  • 기계식 하드 디스크 드라이브를 대체하기 위한 전자식 저장 매체이다.

  • 기존 하드 디스크 드라이브에서 원판을 제거한 대신 플래시 메모리를 장착하고 있다.
    원판을 기계적으로 회전시킬 필요가 없어 아주 빨리 데이터를 읽고 쓸 수 있다.
    컴퓨터의 D-RAM보다는 느리지만 기계식 하드 디스크보다는 훨씬 빠르다.

  • 기존 하드 디스크 드라이브와 같은 인터페이스 (SATA나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용할 수 있다.

  • 전원이 공급되지 않아도 데이터가 삭제되지 않는다.

  • 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 거의 비슷한 성능을 보이기도 하지만, SSD의 장점은 랜덤 I/O가 기존 HDD보다 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 I/O 작업의 비중이 크지 않기 때문에 SSD의 장점은 DBMS용 스토리지에 최적이다.

2) 랜덤 I/O vs 순차 I/O

  • 순차 I/O
    디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 입출력 방식이다.
  • 랜덤 I/O
    디스크의 헤더를 움직이며 여러 차례에 걸쳐 데이터를 읽는 입출력 방식이다.

위 그림에서 순차 I/O는
3개의 페이지를 디스크에 기록하기 위해 1번의 시스템 콜을 요청했다.
즉, 디스크에 기록해야 할 위치를 찾기 위해 디스크의 헤드를 1번 움직였다.

위 그림에서 랜덤 I/O는
3개의 페이지를 디스크에 기록하기 위해 3번의 시스템 콜을 요청했다.
즉, 디스크에 기록해야 할 위치를 찾기 위해 디스크의 헤드를 3번 움직였다.

=> 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정되기 때문에, 위 그림에서 순차 I/O는 랜덤 I/O보다 3배 정도 빠르다고 볼 수 있다.

디스크 원판을 가지지 않는 SSD 역시 랜덤 I/O는 순차 I/O에 비해 전체 Throughput이 떨어진다. 그래서 SSD 드라이브의 사양에도 항상 순차 I/O와 랜덤 I/O의 성능을 구분해서 명시한다.

랜덤 I/O는 여러 번 쓰기 또는 읽기를 요청하기 때문에 순차 I/O에 비해 작업 부하가 훨씬 더 크다. 그리고 데이터베이스의 작업은 작은 데이터를 빈번히 읽고 쓰는 랜덤 I/O가 대부분이기 때문에 MySQL 서버는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능을 내장하고 있다.

일반적으로 쿼리 튜닝은 랜덤 I/O 자체를 줄여주는 것이 목적이다. 이는 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

2. 인덱스란?

DBMS에서 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다.
그래서 컬럼들의 값과 해당 레코드가 저장된 주소를 key-value 쌍으로 삼아 만들어 둔 것이 인덱스이다.

1) 인덱스의 특징

(1) 정렬된 상태 유지

인덱스가 많아지면 그 안에서 원하는 쌍을 찾는 데에 시간이 걸릴 것이기 때문에, DBMS는 인덱스를 컬럼 값 기준으로 미리 정렬해서 보관한다.

(2) 저장(INSERT, UPDATE, DELETE)은 느리지만, 검색(SELECT) 속도는 아주 빠르다.

DBMS의 인덱스는 SortedList와 같이 저장되는 컬럼의 값을 이용해 항상 정렬된 상태를 유지한다.
(데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 저장해 둔다.)
데이터가 저장될 때마다 값을 정렬해야 하므로 저장 과정이 복잡하고 느려진다.
하지만 이미 정렬이 되어 있는 덕분에 아주 빨리 원하는 값을 찾아올 수 있다.

=> 인덱스의 추가 여부는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는지에 따라 결정해야 한다.

2) 인덱스의 구분

(1) 역할별 구분

  • Primary Key
    그 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다.
    테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 식별자라고도 부른다.
    NULL 값을 허용하지 않는다.
    중복을 허용하지 않는다.
  • Secondary Key(Secondary Index)
    PK를 제외한 나머지 모든 인덱스가 여기에 분류된다.
    • Unique Index
      유니크 인덱스는 PK와 성격이 비슷하고 PK를 대체해 사용할 수 있다고 하여 대체 키라고도 한다.
      별도로 분류하기도 하고, 세컨더리 인덱스 중 하나로 퉁쳐지기도 한다.

(2) 데이터 저장 방식(알고리즘)별 구분

  • B-Tree 알고리즘
    가장 일반적으로 사용되는 인덱스 알고리즘이다.
    컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.
    MySQL 서버에는 위치 기반 검색 지원을 위한 R-Tree 인덱스 알고리즘도 있지만 이 또한 B-Tree의 응용으로 볼 수 있다.

  • Hash 알고리즘
    컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘이다.
    매우 빠른 검색을 지원한다.
    하지만 값을 변형해서 인덱싱하므로 Prefix 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 사용할 수 없다.
    주로 메모리 기반의 데이터베이스에서 사용한다.

(3) 데이터의 중복 허용 여부에 따른 구분

  • Unique Index
    모든 인덱스가 서로 다른 값을 가진다.
    동등 조건(Equals)으로 검색 시 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.
  • Non-Unique Index
    값이 동일한 인덱스가 여러 개 존재할 수 있다.

(4) 기능별 구분

  • 전문 검색용 인덱스
  • 공간 검색용 인덱스

3. B-Tree 인덱스

1) 개요

  • 컬럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
  • 인덱싱 알고리즘 가운데 가장 먼저 도입된 알고리즘이면서도 현재까지도 가장 일반적으로(범용적으로) 사용되고 있다.
  • 여러가지 변형된 형태의 알고리즘이 존재한다.
    • 일반적으로 DBMS에서는 B+-Tree 또는 B*-Tree가 사용된다.
  • B-Tree에서 B는 Balanced를 의미한다.

2) 구조 및 특성

(1) 일반적인 구조 및 특성

  • 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장되어 있다.
  • 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

(2) InnoDB 테이블에서의 특성

  • InnoDB 테이블은 세컨더리 인덱스가 논리적 주소(PK)를 가지고 있다.
  • InnoDB 테이블에서는 PK가 ROWID의 역할을 한다.
  • 인덱스에 저장돼 있는 PK 값을 이용해 PK 인덱스를 한 번 더 검색한 후, PK 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.
    => InnoDB 테이블에서는 인덱스를 통해 레코드를 읽을 때 데이터 파일을 바로 찾아가지 못한다.

3) B-Tree 인덱스 키 추가 및 삭제

(1) 인덱스 키 추가

  1. 저장될 키 값을 이용해 B-Tree상의 적절한 삽입 위치를 검색한다.
  2. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.
    • 리프 노드가 꽉 차서 더 이상 저장할 수 없을 경우 리프 노드가 분리(Split)되어 상위 브랜치 노드까지 처리의 범위가 넓어진다.
      이러한 작업 탓에 B-Tree는 상대적으로 새로운 키를 추가하는 쓰기 작업에 비용이 많이 드는 것으로 알려졌다.
  • InnoDB 스토리지 엔진은 필요하다면 체인지 버퍼를 통해 보조 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다.
    (하지만 PK나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.)

(2) 인덱스 키 삭제

  • 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 삭제 작업이 완료된다.
    • 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 디스크 I/O가 필요하다.
  • 삭제 마킹된 인덱스 키 공간은 그대로 방치하거나 재활용할 수 있다.
  • InnoDB에서는 삭제 작업 역시 버퍼링되어 지연 처리될 수 있다.

(3) 인덱스 키 변경

  • 인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 단순히 키 값만 변경하는 것은 불가능하다.
    => 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

(4) 인덱스 키 검색

  • B-Tree의 루트 노드부터 브랜치 노드를 거쳐 최종 리프 노드까지 이동하며 트리 탐색을 수행한다.

  • SELECT뿐만 아니라, UPDATE나 DELETE를 할 때도 일단 먼저 레코드를 검색한다.

  • 100% 일치 또는 값의 일부분(Left-most part)만 일치하는 경우에 사용할 수 있다.

    • 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.

    • Left-most part
      '복합 인덱스에서 앞쪽 컬럼부터'라는 뜻.

      INDEX (A, B, C)

      복합 인덱스가 위와 같이 있다고 가정하면,

      검색이 가능한 경우는 다음과 같다.

      WHERE A = 10
WHERE A = 10 AND B = 5
WHERE A = 10 AND B = 5 AND C = 3

      정렬의 시작점인 A를 알고 있기 때문에 B-Tree에서 인덱스 범위를 정확히 좁힐 수 있다.

      검색이 불가능한 경우는 다음과 같다.

      WHERE B = 5
WHERE B = 5 AND C = 3
WHERE A > 10 AND B = 5 // A > 10 범위 안에는 B=1, B=3, B=5 등등이 흩어져 있으니 B=5인 애들을 한 번에 못 찾음...

      위 경우들은 A를 확실히 모르는 상태에서, B는 여기 저기 흩어져 있기 때문에 트리를 타고 내려갈 기준이 없다.

  • 인덱스의 키 값에 변형이 가해진 후 비교되는 경우(함수나 연산을 수행한 결과로 정렬, 검색하는 경우 등)에는 B-Tree의 검색 기능을 사용할 수 없다.

  • InnoDB에서 지원하는 레코드 잠금이나 넥스트 키 락은 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다.

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

(1) 인덱스 키 값의 크기

  • 인덱스는 페이지 단위로 관리된다.
  • 일반적으로 DBMS의 B-Tree는 자식노드의 개수가 가변적인 구조다.
    인덱스의 페이지 크기와 키 값의 크기에 따라 B-Tree가 가질 수 있는 자식 노드의 최대 개수가 결정된다.
    • InnoDB의 페이지 크기 디폴트 값은 16KB이다.
      • 인덱스의 키 값의 크기가 16바이트이고 자식 노드의 주소 영역이 12바이트라면,
        하나의 인덱스 페이지에 16 * 1024 / (16 + 12) = 582개의 키를 저장할 수 있으며, 자식 노드를 585개 가질 수 있다는 뜻이다.
      • 인덱스의 키 값의 크기가 2배인 32바이트로 늘어났다면,
        하나의 인덱스 페이지에 16 * 1024 / (32 + 12) = 372개의 키를 저장할 수 있으며, 자식 노드를 372개 가질 수 있다는 뜻이다.
  • 인덱스의 키 값의 크기가 커진다.
    == 하나의 인덱스 페이지에 저장할 수 있는 키의 개수가 줄어든다.
    == B-Tree가 가질 수 있는 자식 노드의 개수가 줄어든다.
    == B-Tree의 깊이가 깊어진다.
    == 트리 탐색 시 타고 내려가야 하는 횟수가 늘어난다.
    == 디스크로부터 읽어야 하는 횟수가 늘어난다.
    == 느려진다.
  • 인덱스를 캐싱해두는 InnoDB의 버퍼 풀의 크기는 제한적이다.
    따라서 인덱스의 키 값의 크기가 커질수록 메모리에 캐시해둘 수 있는 레코드의 수가 줄어들어 메모리의 효율이 떨어진다.

(2) 선택도(Selectivity, 또는 기수성(Cardinality))

  • 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.
  • 인덱스 키 값 가운데 중복된 값이 많아질수록 선택도와 기수성이 낮아진다.
    -> 검색 대상이 많아져 그만큼 검색 속도가 느려진다.
    • 하지만 선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

(3) 읽어야 하는 레코드의 건수

  • 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.
    따라서 인덱스를 이용한 읽기의 손익 분기점이 얼마인지 판단할 필요가 있다.
  • 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.
    • 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20%~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필터링하는 방식으로 처리하는 것이 더 효율적이다.

5) MySQL이 B-Tree 인덱스를 이용하는 방법

(1) 인덱스 레인지 스캔

  • 인덱스의 접근 방법 중 가장 대표적인 접근 방식이다.
  • 뒤에 설명될 나머지 두 가지 접근 방식보다 빠르다.
  • 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

위 쿼리를 실행했을 때 인덱스 레인지 스캔은 다음의 이미지와 같이 이루어진다.

  1. 인덱스 탐색(Index Seek)
    루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가서 필요한 레코드의 시작 지점을 찾는다.
  2. 인덱스 스캔(Index Scan)
    일단 시작 위치를 찾으면 그때부터는 리프노드의 레코드만 순서대로 읽는다.(== 스캔한다.)
    만약 스캔 중 리프노드의 끝까지 읽었다면, 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다.
  3. 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.

  • MySQL은 인덱스 탐색과 인덱스 스캔이 얼마나 수행됐는지를 확인할 수 있는 상태 값을 제공한다.
SHOW STATUS LIKE 'Handler_%';

위 예시는 실제 인덱스만을 읽는 경우를 보여주는데, B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야 하는 경우도 많다.

  • 레코드 한 건 한 건을 가져올 때마다 랜덤 I/O가 한 번씩 일어난다.
    위 그림처럼 3건의 레코드가 검색 조건에 일치했다고 가정하면, 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 3번 필요한 것이다.
    => 이처럼 인덱스를 통해 실제 데이터 파일의 데이터를 읽는 작업은 비용이 많이 든다.
  • 인덱스 자체의 정렬 특성 때문에, 어떤 방식으로 스캔을 하든 해당 인덱스를 구성하는 컬럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져오게 된다.

쿼리가 필요로 하는 데이터에 따라 이렇게 실제 데이터 파일의 레코드를 읽어 오는 작업이 필요할 수도, 필요하지 않을 수도 있다.
실제 데이터 파일의 레코드를 읽어오지 않는 인덱스를 커버링 인덱스라고 한다.
커버링 인덱스로 처리되는 쿼리는 랜덤 읽기가 상당히 줄어들고 성능이 그만큼 빨라진다.

(2) 인덱스 풀 스캔

  • 인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식이다.
  • 인덱스 레인지 스캔보다 빠르지는 않지만, 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 효율적이다.
  • 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.
    인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
    • 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 사용된다.
      (인덱스는 (A, B, C) 컬럼 순서로 만들어져 있지만 쿼리의 조건절은 B 컬럼이나 C 컬럼으로 검색하는 경우.)

동작 방식

  1. 먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한다.
  2. 인덱스의 리프 노드를 연결하는 Linked List를 따라서 처음부터 끝까지 스캔한다.

(3) 루스(Loose) 인덱스 스캔

  • 앞에서 소개된 인덱스 레인지 스캔과 인덱스 풀 스캔은 타이트(Tight) 인덱스 스캔으로 분류한다.
  • 인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다.
  • 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.

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

위 쿼리에서 사용된 dept_emp 테이블은 dept_no와 emp_no라는 두 개의 컬럼으로 인덱스가 생성되어 있다.
이 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 되어 있으므로, dept_no의 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다.
즉, 인덱스에서 WHERE 조건을 만족하는 범위 전체를 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건을 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

(4) 인덱스 스킵 스캔

다음 명령을 사용해 인덱스 스킵 스캔을 활성화할 수 있다.

SET optimizer_switch='skip_scan=on';

다음과 같이 인덱스를 생성했다고 해보자.

ALTER TABLE employees
    ADD INDEX ix_gender_birthdate (gender, birth_date);

위 인덱스를 사용하려면 WHERE 조건절에 gender 컬럼에 대한 비교 조건이 필수다.

-- // 인덱스를 사용하지 못하는 쿼리
SELECT * FROM empolyees WHERE birh_date>='1965-02-01';

-- // 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';

그러므로 위 두 쿼리 중 두 번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만, 첫 번째 쿼리는 인덱스를 사용할 수 없었다.
하지만 MySQL 8.0 버전부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스캡 스캔 최적화 기능이 도입되었다.
(루스 인덱스 스캔은 GROUP BY 처리에만 적용할 수 있었지만 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어졌다.)

동작 과정


1. 선행 인덱스 컬럼에 대해 가능한 값을 모두 구한다. 위 예시에서 gender 컬럼은 'M'와 'F'만을 가진다.
2. 내부적으로 여러 개의 AND 조건을 처리하는 쿼리를 실행하는 것과 비슷한 형태의 최적화를 실행한다.

SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date<='1965-02-01';
SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date<='1965-02-01';

단점

  • WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 한다.
    • 유니크한 값의 개수가 매우 많다면 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 여러 번 필요하기 때문에 쿼리의 처리 성능이 오히려 더 느려질 수도 있다.
  • 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 한다.(-> 커버링 인덱스)
    • 커버링 인덱스가 아닌 경우 풀 테이블 스캔으로 실행 계획을 수립한다.

6) 다중 컬럼(Multi-Column) 인덱스

  • 두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복합 컬럼 인덱스)라고 한다.
    • 두 개 이상의 컬럼이 연결되었다고 하여 Concatenated Index라고도 한다.
  • 인덱스의 N+1번째 컬럼은 항상 N번째 컬럼에 의존해서 정렬된다.
    • 즉, N번째 컬럼의 값이 동일한 경우에만 N+1번째 컬럼 값을 기준으로 정렬된다.
      -> 다중 컬럼 인덱스에서는 인덱스 내 각 컬럼의 위치(순서)를 결정하는 것이 상당히 중요한 문제이다.

7) B-Tree 인덱스의 정렬 및 스캔 방향

(1) 인덱스의 정렬

  • 일반적인 상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 컬럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다.
  • MySQL 8.0 버전부터는 다음과 같이 정렬 순서를 혼합한 인덱스를 생성할 수 있게 되었다.
    // team_name은 오름차순 정렬, user_score는 내림차순 정렬
CREATE INDEX ix_teamname_userscore ON empolyees (team_name ASC, user_score DESC);

(2) 인덱스의 스캔 방향

  • 오름차순 인덱스(Ascending index)
    : 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
  • 내림차순 인덱스(Descending index)
    : 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
  • 인덱스 정순 스캔(Forward index scan)
    : 인덱스 키의 크고 작음에 관계 없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
    (-> 오름차순 인덱스를 오름차순으로 스캔하거나, 내림차순 인덱스를 내림차순으로 스캔)
  • 인덱스 역순 스캔(Backword index scan)
    : 인덱스 키의 크고 작음에 관계 없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔
    (-> 오름차순 인덱스를 내림차순으로 스캔하거나, 내림차순 인덱스를 오름차순으로 스캔)
  • 인덱스의 생성 시점에 오름차순 또는 내림차순으로 정렬로 결정되지만, 정렬되어 있는 인덱스는 역순으로 읽는 것도 가능하다.
    (오름차순 정렬된 인덱스를 내림차순으로 읽거나, 내림차순 정렬된 인덱스를 오름차순으로 읽거나.)
    • MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.
      ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우에도 마찬가지이다.
  • InnoDB의 인덱스 역순 스캔은 정순 스캔에 비해 느리다. 다음의 두 가지 이유 때문이다.
    • 페이지 락이 인덱스 정순 스캔에 적합한 구조이다.
    • 페이지 내에서 인덱스 레코드가 단방향으로만 연결되어 있다.

7) B-Tree 인덱스의 가용성과 효율성

(1) 인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값이 기준해서(Left-most) 오른쪽 값이 정렬되어 있다는 것이다. 따라서 정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 정렬 기반 인덱스인 B-Tree에서는 인덱스의 효과를 얻을 수 없다.

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

위 쿼리는 first_name 컬럼에 인덱스가 생성되어 있더라도 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수 없다.
조건절에 주어진 상숫값의 왼쪽 부분이 고정되지 않았기 때문이다.

SELECT * FROM dept_emp WHERE emp_no>=10144;

인덱스가 (dept_no, emp_no) 순서대로 생성되어 있다면, 위 쿼리는 선행 컬럼인 dept_no 조건 없이 emp_no 값으로만 검색하고 있기에 인덱스를 효율적으로 사용할 수 없다.
다중 컬럼으로 구성된 인덱스이므로 dept_no에 대해 먼저 정렬한 후, 다시 emp_no로 정렬되어 있기 때문이다.

(2) 가용성과 효율성 판단

기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다.

  • NOT-EQUAL로 비교된 경우("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
  • LIKE '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
  • 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교하는 경우
  • NOT-DETERMINISTC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
  • 문자열 데이터 타입의 콜레이션이 다른 경우)

4. 함수 기반 인덱스

컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때는 함수 기반의 인덱스를 활용하면 된다. MySQL 8.0부터 함수 기반 인덱스를 지원하기 시작했다.

1) 가상 컬럼을 이용한 인덱스

다음과 같은 테이블이 있다고 해보자.

 CREATE TABLE user (
 	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id)
);

first_name과 last_name을 합쳐서 검색해야 하는 요건이 생겼다면, 다음과 같이 가상 컬럼을 추가하고 그 가상 컬럼에 인덱스를 생성할 수 있다.

ALTER TABLE user
	ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ', last_name)) VIRTUAL,
	ADD INDEX ix_fullname (full_name);

가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

2) 함수를 이용한 인덱스

MySQL 8.0 버전부터는 다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 되었다.
함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고 계산된 결괏값의 검색을 빠르게 만들어준다.

 CREATE TABLE user (
 	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id),
    INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
);

함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다. 함수 생성 시 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 다르다면 옵티마이저가 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.

EXPLAIN SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name)='Matt Lee';

6. 클러스터링 인덱스

1) 개요

PK 값이 비슷한 레코드끼리 묶어서 저장하는 컷을 클러스터링 인덱스라고 표현한다.
PK 값에 의해 레코드의 저장 위치가 결정되므로 테이블 레코드의 저장 방식이라고 볼 수 있다.

InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 일반적으로 PK 기반 검색이 매우 빠르며, 대신 레코드의 저장이나 PK의 변경이 상대적으로 느리다.


클러스터링 인덱스의 리프 노드에는 모든 컬럼이 같이 저장되어 있다.

(1) PK가 없는 InnoDB 테이블

PK가 없는 경우 InnoDB 스토리지 엔진이 다음 우선순위대로 PK를 대체할 컬럼을 선택해 클러스터링 테이블을 구성한다.
1. PK가 존재하면 기본적으로 PK를 클러스터링 키로 선택
2. NOT NULL 옵션의 UNIQUE INDEX 중 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터링 키로 선택

이렇게 자동으로 추가되는 PK는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다. 이는 아무 의미 없는 값으로 클러스터링되는 것이기 때문에 어떠한 혜택이 없다. 가능하면 PK를 명시적으로 생성하자.

2) 장단점

(1) 장점

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

(2) 단점

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

7. 유니크 인덱스

MySQL에서는 인덱스 없이 유니크 제약만 설정할 수 없다.
유니크 인덱스에서 NULL 값 저장이 가능하며, NULL은 2개 이상 저장될 수 있다.

1) 유니크 인덱스 VS 일반 세컨더리 인덱스

(1) 인덱스 읽기

유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느릴 뿐, 인덱스 자체의 특성 때문에 느린 것은 아니다.
읽어야 할 레코드의 건수가 같다는 전제 하에서는 성능상의 차이가 미미하다.

(2) 인덱스 쓰기

유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하기 때문에 일반 세컨더리 인덱스보다 느리다.

profile
사람
알고리즘 블로그 아닙니다.
이전 포스트

실행 계획

0개의 댓글