Coding/개발 서적

[Real MySQL 8.0 V1] 08장. 인덱스

kangplay 2025. 5. 1. 16:54

8.1 디스크 읽기 방식

컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만, 하드 디스크 드라이브는 기계식 장치다. 요즘은 전자식 저장 매체인 SSD가 많이 출시되어 아주 빨리 데이터를 읽고 쓸 수 있다. (컴퓨터의 메모리보다는 여전히 느리지만)

8.1.1 DBMS에서의 SSD

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다. 

8.1.2 랜덤 I/O와 순차 I/O

랜덤 I/O와 순차 I/O는 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계를 몇 번 거치느냐에 차이가 있다. 즉, 순차 I/O가 디스크 헤더의 위치 이동 없이 더 많은 데이터를 한 번에 기록할 수 있다.

 

일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O를 순차 I/O로 바꿔서 실행하는 것이 아니라, 랜덤 I/O 자체를 줄여주는 것이 목적이다. 즉, 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 개선하는 것이다.

🔦 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 

8.2 인덱스란?

인덱스란, 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value pair)을 삼아 인덱스를 만들어 두는 것이다. 이때, 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.

8.2.1 SortedList, ArrayList 자료구조와의 비교

DBMS의 인덱스는 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장해둔다.

 

인덱스는 저장될 때마다 항상 값을 정렬해야하므로 저장하는 과정(INSERT,UPDATE,DELETE)이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있다. (SELECT)

결론적으로, DBMS에서의 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.

8.2.2 인덱스 분류

  1. 역할 기준
    - 프라이머리 키: 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스를 의미한다. 프라이머리 키는 NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
    - 세컨더리 인덱스(보조키): 프라이머리 키를 제외한 나머지 모든 인덱스를 의미한다. 유니크 인덱스는 프라이머리 키를 대체해서 사용할 수 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 그냥 세컨더리 인덱스로 분류하기도 한다.
  2. 데이터 저장 방식(알고리즘)
    - B-Tree 알고리즘: 가장 일반적으로 사용되는 인덱스 알고리즘으로서, 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다. 
    - Hash 인덱스 알고리즘: 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
  3. 데이터 중복 허용 여부
    - 유니크 인덱스: 인덱스가 유니크한 특징을 가지고 있으며, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게 상당히 중요한 문제가 된다. 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다. 
    - 유니크하지 않은 인덱스: 인덱스가 중복 허용되는 특징을 가진다.

8.3 B-Tree 인덱스

B-Tree에는 여러 가지 변형된 형태의 알고리즘이 있는데, 일반적으로 DBMS에서는 주로 B+-Tree 또는 B*-Tree가 사용된다. B-Tree의 "B"는 "Balanced"를 의미한다.

8.3.1 구조 및 특성

B-Tree는 트리 구조의 최상위에 하나의 "루트 노드"가 존재하고, 그 하위에 "자식 노드"가 붙어 있는 형태이다. 트리 구조의 가장 하위에 있는 노드를 "리프 노드"라 하고, 트리 구조에서 "루트 노드"도 아니고 "리프 노드"도 아닌 중간 노드를 "브랜치 노드"라고 한다.

 

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

Aamer 이상 ~ Ebbe 미만 → 페이지 4 Ebbe 이상 ~ Gad 미만 → 페이지 5 Gad 이상 → 페이지 6
InnoDB 테이블을 프라이머리 키를 주소처럼 사용하기 때문에 세컨더리 인덱스의 리프 노드가 논리적인 주소를 가진다고 볼 수 있다.

위 그림과 같이 인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장되어 있다.

 

많은 사람들이 데이터 파일의 레코드는 INSERT된 순서대로 저장되는 것으로 생각하지만, 그렇지 않다. 만약 레코드가 삭제되어 빈 공간이 생기면 그 다음의 INSERT는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT된 순서로 저장되는 것은 아니다.

InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장된다. 즉, 사용자가 별도의 명령이나 옵션을 선택하지 않아도 디폴트로 클러스터링 테이블이 생성된다. 즉, 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다.

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

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다. 

  1. 인덱스 키 추가
    인덱스 추가로 인해 INSERT나 UPDATE 문장이 어떤 영향을 받을지 대략적으로 계산하는 방법은, 레코드 추가 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1.5 정도로 예측하는 것이다. 테이블에 인덱스가 3개가 있다면, 5.5 정도의 비용 (1.5*3+1) 정도로 예측한다. (중요한 것은 이 비용의 대부분이 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 걸리는 시간이라는 점이다)
    InnoDB 스토리지 엔진은 INSERT 문장이 실행되면 인덱스 키 추가 작업을 지연시켜 나중에 처리하여 지능적으로 처리한다.(체인지 버퍼) 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다. 
  2. 인덱스 키 삭제
    B-Tree의 키 값이 삭제되는 경우는 상당히 간단하다. 해당 키 값이 저장된 B-Tree 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 필요한 작업이다. 
  3. 인덱스 키 변경
    인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우에는 단순히 인덱스 상의 키 값만 변경하는 것은 불가능하다. B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다. InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 이 작업 모두 체인지 버퍼를 활용해 지연 처리될 수 있다.
  4. 인덱스 키 검색
    인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 "트리 탐색"이라고 한다. (인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다.)
    B-Tree 인덱스를 이용한 검색은 인덱스를 구성하는 키 값의 뒷 부분만 검색하는 용도로는 인덱스를 사용할 수 없다. 또한, 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다. 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아니다. 따라서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다. 
✋ InnoDB 스토리지 엔진에서 인덱스
InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 

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

그렇다면 옵티마이저는 언제 인덱스를 사용할지 판단할까? B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

 

1. 인덱스 키 값의 크기
인덱스도 페이지 단위로 관리되며, 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 인덱스의 키가 16바이트라고 가정하면, 하나의 인덱스 페이지에 16*1024/(16+12) = 585개 저장할 수 있다. 최종적으로 이 경우는 자식 노드를 585개 가질 수 있는 B-Tree가 될 수 있는 것이다.

페이지의 크기는 4KB~6KB 사이의 값을 선택할 수 있지만, 기본값은 16KB이다.

그런데 인덱스 키 값이 두 배인 32바이트로 늘어났다고 가정하면, 한 페이지에 인덱스 키를 16*1024/(32+12) = 372개 저장할 수 있다. SELECT 쿼리가 레코드 500개를 읽어야한다면 전자는 인덱스 페이지 한 번으로 해결될 수 있지만, 후자는 최소 2번 이상 디스크로부터 읽어야 한다. 결국 인덱스의 키 값의 크기가 커지면 전체적인 인덱스의 크기가 커진다는 것을 의미한다. 

 

2. B-Tree 깊이

MySQL(InnoDB)에서 B-Tree를 이용한 값 검색 시 디스크 I/O는 루트 → 리프까지 트리 경로를 따라 내려가며, 해당 노드(=페이지)가 디스크에 있는 경우마다 발생합니다. 즉, 인덱스 키 값의 크기가 커지면 같은 레코드 건수라고 하더라도 B-Tree의 깊이가 깊어져서 디스크가 더 많이 필요하게 된다.

실제로는 InnoDB의 buffer pool이 캐시 역할을 하기 때문에 루트 노드와 상위 노드는 대부분 메모리에 있음 → 실제 I/O는 대부분 리프 노드에서 발생한다.

 

3. 선택도(기수성)

인덱스에서 선택도 또는 기수성은 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 인덱스 키 값 가운데 중복된 값이 많아지면 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다. 

선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다. 

예시: 선택도 개념

옵티마이저는 "인덱스를 쓰는 게 빠를까?"를 통계로 추정해서 결정한다. 

tb_test 테이블에는 country 컬럼에 단일 인덱스가 설정되어있다고 가정하고, 아래의 쿼리를 실행한다고 해보자.

SELECT * FROM tb_test WHERE country = 'korea' AND city = 'seoul';
케이스 country 유니크 개수 각 country 값당 평균 레코드 수
A 10개 전체 레코드 수(100만) / 10 = 1000개
B 1000개 전체 레코드 수(100만) / 1000 = 10개

 

옵티마이저는 country='korea'일 때 몇 개의 row가 나올지 통계로 추정하는데, 그 유일값이 적을수록(=선택도가 낮을수록) 너무 많은 row가 걸릴 것으로 판단하게 된다. 

  • 케이스 A에서는 country='korea' 인 row가 평균적으로 1000건 있다고 추정한다. 그럼 인덱스로 찾은 다음, 1000번 테이블 row lookup해야 하고, 결국 인덱스를 써도 느리다고 판단하여, 테이블 full scan을 사용할 수도 있다. 
    • A 케이스에서 city='seoul' 조건까지 합치면 1건일 수 있지만, 옵티마이저는 city에 대한 인덱스 정보가 없기 때문에, city='seoul' 조건은 적중률 예측에 못 쓴다.
  • 케이스 B에서는 country='korea'인 row가 평균 10건 있다고 추정한다. 그럼 인덱스로 좁은 범위 조회하여, row lookup도 적다.  따라서, 전체 테이블에서 찾는 것보다 인덱스를 사용하는 것이 훨씬 효율적이라고 판단한다. 

4. 읽어야하는 레코드의 건수

테이블에 레코드가 100만 건 저장되어있는데, 그 중에서 50만 건(선택도에 기반해 옵티마이저가 예측한 row) 을 읽어야하는 쿼리가 있다고 가정해보자. 이 작업은 전체 테이블을 모두 읽어서 필요 없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만 건만 읽어오는 것이 효율적일지 판단해야 한다.

 

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배정도 비용이 더 많이 드는 작업인 것으로 에측한다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 방식으로 처리하는 것이 효율적이다. 

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

MySQL이 인덱스를 이용하는 대표적인 방법 세 가지를 살펴보자.

 

1. 인덱스 레인지 스캔 

인덱스 레인지 스캔(Index Range Scan)은 인덱스를 활용해 범위 형태의 데이터를 조회한 경우를 의미한다. 범위 형태란 BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE를 활용한 데이터 조회를 뜻한다.

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

이 방식은 인덱스를 활용하기 때문에 효율적인 방식이다. 하지만 인덱스를 사용하더라도 데이터를 조회하는 범위가 클 경우 성능 저하의 원인이 되기도 한다.

위 그림처럼 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다. 그때부터 리프 노드의 레코드만 순서대로 읽으면 된다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아 다시 스캔한다.

또한, 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서. 레코드를 읽어오는 과정이 필요하다. 이때 리프 노드에 저장된 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다. 그래서 인덱스를 통해 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다. 그리고 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리방식이 된다.

📈 커버링 인덱스
쿼리가 필요로 하는 데이터에 따라 레코드를 읽어오는 과정이 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 즉, SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스이다. 실행 계획을 실행시키면 Extra에 Using index;라고 찍힌다.

 

MySQL 서버에서는 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는 과정(인덱스 탐색)과 필요한 만큼 인덱스를 차례대로 쭉 읽는 과정(인덱스 스캔) 작업이 얼마나 수행됐는지를 확인할 수 있게 다음 명령어를 제공한다.

SHOW STATUS LIKE 'HANDLER_%'
  • Handler_read_key: 인덱스 탐색 과정이 실행된 횟수 → 인덱스를 정확히 사용한 조회가 많다는 의미
  • Handler_read_next, Handler_read_prev: 인덱스 스캔이 실행된 횟수-> 테이블 풀스캔이 많다는 의미 → 인덱스 미사용
  • Handler_read_first, Handler_read_last: 인덱스의 첫번 째와 마지막 레코드를 읽은 횟수(MIN(), MAX() , 범위 탐색의 경우)
쿼리 유형  증가하는 Handler 항목
WHERE name = 'Aamer' Handler_read_key
WHERE name BETWEEN 'Aamer' AND 'Gad' Handler_read_first, Handler_read_next
ORDER BY name LIMIT 10 Handler_read_first, Handler_read_next
SELECT * FROM table (인덱스 미사용) Handler_read_rnd_next (랜덤 풀스캔)

 

2. 인덱스 풀 스캔 

인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 인덱스의 테이블은 실제 테이블보다 크기가 작기 때문에, 풀 테이블 스캔(Full Table Scan)보다 효율적이다. 하지만 인덱스 테이블 전체를 읽어야 하기 때문에 아주 효율적이라고 볼 수는 없다.

 

Full Index Scan은 커버링 인덱스일 때 가장 효율적이다. 하지만 정렬된 순서 유지(ORDER 조건), 또는 범위 탐색을 위한 진입 등의 이유로 커버링 인덱스가 아니어도 사용될 수 있다. 다만 그럴 경우 테이블을 추가로 조회하므로 성능에 유의해야 한다.

 

3. 루스 인덱스 스캔

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다. 

(dept_no, emp_no) 조합으로 정렬이 되어있음. 따라서 dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다.

루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다. 

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

 

4. 인덱스 스킵 스캔

MySQL 8.0 버전부터는 옵티마이저가 멀티 인덱스 칼럼에서, 첫번째 인덱스 칼럼을 건너뛰어서 두번째 인덱스 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.

type 칼럼의 값이 "range"라는 것은, 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다.

gender 칼럼은 성별을 구별하는 칼럼으로 'M'과 'F'만 가지는 ENUM 타입의 칼럼이다. 그래서 gender 칼럼에 대해 가능한 값 2개를 구한 다음, 옵티마이저는 내부적으로 아래 2개의 쿼리를 실행하는 것과 비슷한 형태의 최적화를 실행하게 된다.

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

이는 다음과 같은 단점을 가지고 있다.

  • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 한다.
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 한다.(커버링 인덱스)

8.3.5 다중 컬럼(Multi-column) 인덱스

지금까지 살펴본 인덱스는 모두 1개의 칼럼만 포함된 인덱스였다. 하지만 실제 서비스용 데이터베이스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용된다.

인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬되어 있다

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

인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 게획에 따라 결정된다. 

SELECT * FROM employees ORDER By first_name DESC LIMIT 1;

위의 쿼리는 인덱스를 역순으로 접근해 첫 번째 레코드만 읽으면 된다. 쿼리의 ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우에도 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

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

쿼리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 

 

1. 비교 조건의 종류와 효율성

SELECT * FROM dept_emp WHERE dept_no='d002'AND emp_no >= 10114;

이 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정하자.

  • 케이스 A: Index(dept_no, emp_no)
  • 케이스 B: Index(emp_no, dept_no)

왼쪽은, dept_no='d002' AND emp_no>10144 인 레코드를 찾고, 그 이후에는 dept_no가 'd002'가 아닐 때까지 인덱스를 쭉 읽기만 하면 된다. 오른쪽은, emp_no>=10144 AND dept_no='d002'인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no가 'd002'인지 비교하는 과정을 거쳐야 한다.

케이스 A 인덱스에서 2번째 칼럼인 emp_no는 비교 작업의 범위를 좁히는 데 도움을 준다. 하지만 케이스 B 인덱스에서 2번째 칼럼인 dept_no는 비교 작업의 범위를 좁히는데 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용됐다.

 

이처럼 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만, 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못한다. 오히려 쿼리 실행을 더 느리게 만들 때가 많다. 

 

2. 인덱스의 가용성

SELECT * FROM dept_emp WHERE emp_no>=10144;

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수 없다. 조건절에 주어진 상숫값에는 왼쪽 부분이 고정되지 않았기 때문이다. 

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

인덱스의 선행 컬럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다. 다중 칼럼으로 구성된 인덱스이므로 dept_no 칼럼에 대해 먼저 정렬한 후, 다시 emp_no 칼럼값으로 정렬돼 있기 때문이다. 

 

3. 가용성과 효율성 판단

기본적으로 다음과 같은 조건에서는 B-Tree 인덱스를 사용할 수 없다. (작업 범위 결정 조건으로 사용할 수 없다.)

  • NOT-EQUAL로 비교된 경우
    • <>, NOT IN, NOT BETWEEN, IS NOT NULL
  • LIKE %??로 비교된 경우
  • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
    • SUBSTRING(column,1,1), DAYOFMONTH(column)
  • 인덱스의 칼럼 타입을 변환해야 비교가 가능한 경우
일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다. 따라서 다음과 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.
.. WHERE column IS NULL ..​

 

다중 칼럼으로 만들어진 인덱스는 다음의 조건에서 사용된다.

  • column_1 ~ column_(i-1) 칼럼까지 동등 비교 형태('=' 또는 'IN')로 비교
  • column_i 칼럼에 대해 다음 연산자 중 하나로 비교
    • 동등 비교('=' 또는 'IN')
    • 크다 작다 형태('>' 또는 '<')
    • LIKE로 좌측 일치 패턴

8.4 전문 검색 인덱스

문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다. 

 

문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스라고 하는데, 전문 검색 인덱스는 일반화된 기능의 명칭이지 전문 검색 알고리즘의 이름을 지칭하는 것은 아니다.

8.4.1 인덱스 알고리즘

전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다. 전문 검색 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다. 

🔹 형태소란?
형태소란, 의미를 가지는 가장 작은 언어 단위이다.
- 예시: "먹었습니다" → 먹 + 었 + 습 + 니다
    - 먹: 어근 (뜻이 있는 핵심 단어)
    - 었, 습, 니다: 문법적 기능을 하는 어미(접사)

 

1. 어근 분석 알고리즘

어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다. MySQL 서버에서는 오픈소스 형태소 분석  라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.

하지만 Mecab이 제대로 작동하려면 단어 사전이 필요하며, 문장을 해체해서 각 단어의 품사를 식별할 수 있는 문장의 구조 인식이 필요하다. 이를 위해서는 실제 언어의 샘플을 이용해 언어를 학습하는 과정이 필요한데, 이 과정은 상당히 시간이 필요한 작업이다. MeCab을 MySQL에 적용하는 방법은 어렵지 않지만 한글에 맞게 완성도를 갖추는 작업은 많은 시간과 노력이 필필요하다. 

 

2. n-gram 분석 알고리즘

전문적인 검색 엔진을 고려하는 것이 아니고, 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘인 n-gram 분석 알고리즘을 사용하면 된다. n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. n은 인덱싱할 키워드의 최소 글자 수를 의미하는데, 일반적으로 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-gram 방식이 많이 사용된다.

To be or not to be. That is the question

각 단어는 띄어쓰기(공백)과 마침표(.)를 기준으로 10개의 단어로 구분되고, 2글자씩 중첩해서 토큰으로 분리된다. 

이렇게 생성된 토큰들에 대해 불용어를 걸러낸 다음, 최종 인덱스 엔트리를 저장한다.

8.4.2 전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 다음 두 가지 조건을 갖춰야 한다.

  • 쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ...)을 사용
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

테이블의 doc_body 칼럼에 전문 검색 인덱스 생성

전문 검색 인덱스를 사용하려면 다음 예제와 같이 작성해야하며, 전문 검색 인덱스를 구성하는 컬럼들은 MATCH 절의 괄호 안에 모두 명시돼야 한다.

8.5 함수 기반 인덱스

일반적인 인덱스는 칼럼의 값 일부(칼럼의 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반의 인덱스를 활용하면 된다. 

 

My SQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음과 같이 두 가지로 구분할 수 있다.

  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스
MySQL 서버의 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

8.5.1 가상 칼럼을 이용한 인덱스

다음과 같이 사용자의 정보를 저장하는 테이블이 있다고 가정해보자.

그런데 first_name과 last_name을 합쳐서 검색해야 하는 요건이 생겼다면, 이전 버전의 MySQL 서버에서는 full_name이라는 칼럼을 추가하고 모든 레코드에 대해 full_name을 업데이트하는 작업을 거쳐야지만, MySQL 8.0 버전부터는 가상 컬럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다.

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

8.5.2 함수를 이용한 인덱스

MySQL 8.0 버전부터는 다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다. 

8.6 멀티 밸류 인덱스

멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다. 일반적인 RDBMS를 기준으로 생각하면 이러한 인덱스는 정규화에 위배되는 형태다. 하지만 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하고, MySQL 8.0 버전으로 업그레이드되면서 JSON 관리 기능은 MongoDB에 비해서도 부족함이 없는 상태가 됐다.

mysql> CREATE TABLE user (
	user_id BIGINT AUTO_INCREMENT PRIMARY KEY, 
	first_name VARCHAR(10), 
	last_name VARCHAR (10),
	credit_info JSON, 
	INDEX mx_creditscores ( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)) )
	);

mysql> INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores": [360, 353, 351]}*);

멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

  • MEMBER OF()
  • JSON_CONTAINS()
  • JSON_OVERLAPS()

이 예제에서는 MEMBER OF() 연산자를 사용했지만 나머지 두 연산자도 모두 멀티 밸류 인덱스를 활용해 실행 게획이 만들어진다.

8.7 클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 프라이머리 인덱스는 클러스터링 인덱스라고도 하는데, 테이블의 실제 레코드를 저장하고 있는 B+Tree이다. 즉, 이 인덱스 자체가 "테이블"의 본체이므로 클러스터링 테이블이라고도 부른다.

 

세컨더리 인덱스 (보조 인덱스)는 프라이머리 키 값을 포함하는 별도의 B+Tree로, 실제 데이터는 없고 오직 키와 프라이머리 키 값만 존재한다. 프라이머리 인덱스를 거쳐야만 실제 레코드 조회 가능하다.

클러스터링 인덱스 구조를 보면 클러스터링 테이블의 구조 자체는 일반 B-Tree와 비슷하지만, 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼있음을 알 수 있다. 

8.7.1 클러스터링 인덱스 선택

프라이머리 키가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성될까? 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다.

  1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
  2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
  3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
    -> 이렇게 자동으로 추가된 프라이머리 키는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다.

InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하자.

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

InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까? 레코드 위치는 페이지 split, insert, compact 등으로 자주 변할 수 있다. 반면 프라이머리 키는 보통 바뀌지 않도록 설계하기 때문에 더 안정적인 참조 수단이다. 따라서, 이런 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있다.

 

그렇다면 세컨더리 인덱스를 활용해 데이터를 조회하는 경우, 어떤 과정을 거칠까? 먼저, 세컨더리 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다. 조금 복잡하게 처리되지만, 프라이머리 키는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정하지 않아도 된다.

 

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

대부분 클러스터링의 장점은 빠른 읽기(SELECT)이며, 단점은 느린 쓰기(INSERT, UPDATE, DELETE) 라는 것을 알 수 있다. 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 읽기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

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

  • 프라이머리 키 크기
    • 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 
  • 프라이머리 키는 반드시 명시할 것
    • 가능하면 AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다. 
    • 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키를 설정하는 것이 성능 향상에 도움이 된다. (INSERT 순차적 → 쓰기 성능 매우 좋음)

8.8 유니크 인덱스

유니크 인덱스는 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다. 

8.8.1 일반 세컨더리 인덱스와의 비교 

  • 인덱스 읽기
    • 많은 사람들이 유니크 인덱스는 1건만 읽으면 되지만 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야하므로 니크 인덱스가 빠르다고 생각한다. 하지만 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없다고 볼 수 있다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것이다.
    • 일반 SELECT와 다르게 MVCC를 무시하고 "현재 인덱스 상태"를 직접 확인한다. (읽기 잠금(Shared Lock))
      • 트랜잭션 A가 email = 'abc@example.com'을 INSERT 중 (아직 커밋 안됨)
      • 트랜잭션 B도 동일한 email을 INSERT 시도하면
      • → B는 A가 커밋하지 않았더라도 충돌로 인해 실패하거나 대기
  • 인덱스 쓰기
    • 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다. 
    • 쓰기를 할 때는 쓰기 잠금(Exclusive Lock)을 사용하고, 인덱스의 저장이나 변경 작업에 중복 체크를 해야하므로 버퍼링하지 못한다. 

8.9 외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다. 외래키가 제거되지 않은 상태에서 자동으로 생성된 인덱스를 삭제할 수 없다. 

  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
    • 자식 테이블의 외래키 칼럼 변경은 부모 테이블의 확인이 필요한데, 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다린다.
    • 자식 테이블의 레코드에 쓰기 잠금이 있는 경우, 해당 자식 테이블의 외래키 참조(부모 테이블)의 레코드도 변경이 불가하다. 
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다. 
    • 자식 테이블의 외래키 칼럼 변경은 부모 테이블의 확인이 필요한데, 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있지 않으면 바로 변경 가능하다.