보조 인덱스

보조 인덱스란 기본 키가 아닌 다른 컬럼에 생성한 인덱스를 의미한다.

• PK로 생성한 인덱스를 클러스터 인덱스라고 한다.
• 나머지 인덱스(UNIQUE, 일반 인덱스 등)는 보조 인덱스라고 한다.

보조 인덱스: 구조

MySQL 에서 사용하는 InnoDB 스토리지 엔진을 기준으로 보조 인덱스의 leaf 노드에 해당 row의 PK 값을 저장한다.
이는 보조 인덱스를 따라가서 먼저 PK값을 찾고, 그것을 기반으로 실제 레코드(모든 row)를 클러스터 인덱스에서 다시 찾는다.
이 과정을 인덱스 루킹 혹은 Double Read라고 한다.

보조 인덱스: 접근 과정

-- 테이블 생성
CREATE TABLE users
(
    id    INT PRIMARY KEY,
    email VARCHAR(30),
    name  VARCHAR(5),
    INDEX idx_email (email)
);

-- 조회 쿼리
SELECT *
FROM users
WHERE email = 'rlarlgus0206@naver.com';

idx_email 보조 인덱스에서 rlarlgus0206@naver.com을 찾아 해당하는 PK(id)를 얻어오고, 그 PK를 가지고 클러스터 인덱스를 다시 조회해서 전체 row를 가져옵니다.

• 장점: email을 인덱스로 빠르게 탐색
• 단점: 두 번 인덱스를 접근한다(보조 인덱스, 클러스터 인덱스)

보조 인덱스: 특징

• 저장 구조: 보조 인덱스에는 해당 컬럼 값 + PK 값이 저장된다.
• 검색 방식: 보조 인덱스에서 PK를 찾고, PK로 클러스터 인덱스에 접근한다.(Double Read)
• 성능: 단일 인덱스만 사용하는 것보다 느릴 수 있다.
• 커버링 인덱스: SELECT 절에 보조 인덱스에 있는 컬럼만 있으면 double read 없이 조회 가능하다.
• 중복 인덱스 피하기: 보조 인덱스가 많을 수록 쓰기 성능이 저하되고, 관리 비용이 증가한다.

클러스터 인덱스

InnoDB 스토리지 엔진에서 테이블의 데이터 자체가 인덱스 구조(B-Tree)에 저장되는 방식의 인덱스이다. 기본 키에 의해 테이블이 정렬되고 저장되도록 만든다.

• InnoDB에서 테이블은 PK 기반의 B+ 트리 구조로 저장된다.
• 이 PK 인덱스를 클러스터 인덱스라 부른다.
• 테이블의 데이터 자체가 클러스터의 인덱스에 저장된다
  • row = 클러스터 인덱스의 leaf 노드

클러스터 인덱스: 특징

• 정렬 기준: 테이블은 PK 기준으로 정렬되어 저장된다.
• leaf 노드: 실제 row 전체 데이터가 들어가있다.
• 갯수: InnoDB 테이블 당 하나만 존재한다(PK는 하나)
• 보조 인덱스와 비교: 보조 인덱스는 leaf에 PK만 저장한다.

클러스터 인덱스: 조회가 삐른 이유

SELECT *
FROM users
WHERE id = 5;

id가 PK일 경우 클러스터에서 인덱스에서 바로 row에 접근한다. 중간 단계 없이 한 번에 접근하기 때문에 매우 빠르다.

클러스터 인덱스: 만약 클러스터 인덱스가 없다면?

MySQL의 InnoDB는 아래와 같은 순서로 클러스터 인덱스를 결정한다.

1. PK가 있는가? - 있으면 그것을 클러스터 인덱스로 만든다
2. PK가 없다 - UNIQUE NOT NULL 제약 조건이 있는 첫 번째 컬럼을 클러스터 인덱스로 결정한다.
3. 아무것도 없다 - InnoDB가 숨겨진 6바이트 Row ID를 내부적으로 생성해서 클러스터 인덱스로 사용한다.

따라서 성능과 명확성 확보를 위해 PK는 반드시 명시하는 것이 좋다.

클러스터 인덱스: 주의할 점

• 클러스터 인덱스 기준으로만 정렬되기 때문에 ORDER BY 에서 다른 인덱스가 없다면 정렬 비용이 증가한다.
• PK 컬럼 값은 되도록 짧고 불변한 값이 좋다. 긴 문자열이나 자주 바뀌는 값은 성능 저하를 유발한다.

클러스터 인덱스: PK값을 포함한 인덱스는 클러스터 인덱스일까?

PK 값을 포함한 복합 인덱스가 무조건 클러스터 인덱스가 되는 것은 아니다. 커버링 인덱스 구성, ORDER BY 최적화 등의 이유로 인덱스에 PK를 포함시키는 경우가 있다. 이때 인덱스에 PK가 들어간다고 해서 클러스터 인덱스가 되는 것은 아니다.