복합 인덱스란?

복합 인덱스는 하나의 인덱스에서 여러 컬럼을 조합하여 더 복잡한 조건을 빠르게 처리해주는 인덱스이다.

CREATE INDEX idx_name_age ON users (name, age);

위 인덱스에서 name을 기준으로 정렬되고, name이 같을 경우 age순으로 정렬된다.

복합 인덱스를 쓰는 이유

복합 인덱스는 특정 조건에서 단일 인덱스보다 훨씬 효율적이기 때문이다.

-- 단일 인덱스 2개
CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_age ON users (age);

-- 복합 인덱스 1개
CREATE INDEX idx_name_age ON users (name, age);

-- 사용할 쿼리
SELECT *
FROM users
WHERE name = 'Kim'
  AND age = 30;

• 단일 인덱스 2개: idx_name, idx_age중 하나만 사용하거나 index merge방식을 사용하며 이는 상대적으로 느리다.
• 복합 인덱스: name, age를 모두 포함하므로 단일 인덱스 2개보다 훨씬 빠르게 처리 가능하다.

복합 인덱스는 왼쪽부터 매칭된다: Left-most Prefix Rule

복합 인덱스는 반드시 왼쪽부터 순서대로 조건이 매칭되어야 인덱스를 효율적으로 사용할 수 있다. 예제의 복합 인덱스가 (name, age, city) 컬럼을 가진다.

WHERE 조건	인덱스 사용 가능 여부	설명
WHERE name = 'Kim'	O	첫 번째 컬럼 사용
WHERE name = 'Kim' AND age = 20	O	앞의 두 개의 컬럼 사용
WHERE age = 20	X	첫 번째 컬럼이 누락되어 인덱스를 탐색할 수 없다
WHERE name = 'Kim' AND city = 'Suwon'	O	name까지는 사용되지만 city는 건너뛰어서 사용이 불가능하다

복합 인덱스가 정렬 순서에 미치는 영향

복합 인덱스는 ORDER BY에도 최적화 효과가 있다.

-- 아래 쿼리는 인덱스를 그대로 활용 가능 → 정렬 생략
SELECT *
FROM users
ORDER BY name, age;

-- 아래 쿼리는 인덱스와 정렬 순서가 달라 → filesort 발생
SELECT *
FROM users
ORDER BY age, name;

예제

테이블 생성

-- 테이블 생성
CREATE TABLE users
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age  INT,
    city VARCHAR(100)
);

-- 자료 입력
INSERT INTO users (name, age, city)
VALUES ('Park', 25, 'Seoul'),
       ('Lee', 30, 'Busan'),
       ('Lee', 30, 'Incheon'),
       ('Kim', 25, 'Suwon'),
       ('Kim', 30, 'Busan'),
       ('Kim', 35, 'Daegu');

-- 인덱스 생성
CREATE INDEX idx_name_age ON users (name, age);

name만 사용하는 경우

EXPLAIN
SELECT *
FROM users
FORCE INDEX (idx_name_age)
WHERE name = 'Kim';

위 EXPLAIN 쿼리를 날리면 아래와 같은 결과가 나온다.

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'users', NULL, 'ref', 'idx_name_age', 'idx_name_age', '403', 'const', '3', '100.00', NULL

각 항목이 뜻하는 것은 아래와 같다.

• key: 사용된 인덱스 이름
• type: 인덱스를 통해 특정 값 탐색
• rows: 예측 결과에서 몇 개의 row를 탐색할지

name + age 을 사용하는 경우

EXPLAIN
SELECT *
FROM users
FORCE INDEX (idx_name_age)
WHERE name = 'Kim'
  AND age = 30;

위와 같은 쿼리를 날리면 아래와 같은 결과가 나온다.

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, users, NULL, ref, idx_name_age, idx_name_age, 408, const.const, 1, 100.00, NULL

• key_len 항목이 더 길어졌는데 이는 name + age를 정밀하게 탐색했다는 것을 의미한다.
• rows = 1 이라는 것은 한 건만 탐색함을 말한다.

정렬 활용

EXPLAIN
SELECT *
FROM users
FORCE INDEX (idx_name_age)
WHERE name = 'Kim'
ORDER BY age;

• 이 쿼리는 name으로 필터링하고 age로 정렬한다.
• idx_name_age는 name -> age순서로 되어있기 때문에 정렬도 인덱스로 처리된다.

Extra컬럼에 "Using index; Using where" 또는 "Using where"만 나오고 "Using filesort"가 없다면 정렬을 인덱스로 처리한다는 의미이다.

• 실제 실행 시간까지 특정하려면 EXPLAIN ANALYZE를 사용하면 된다.

복합 인덱스 설계 전략

1. 자주 사용하는 WHERE 조건부터 왼쪽에 배치한다

왼쪽부터 사용되기 때문에, 가장 자주 사용하는 조건을 가장 앞 컬럼으로 지정해야 한다.

-- 인덱스
CREATE INDEX idx_user_status_date ON orders (user_id, status, created_at);

-- 쿼리문
SELECT *
FROM orders
WHERE user_id = 1
  AND status = 'COMPLETE'
  AND created_at BETWEEN ? AND ?

가장 자주 사용되는 컬럼이 user_id라면 user_id를 맨 앞에 위치시켜야 한다.

2. 선택도가 높은 컬럼을 앞에 배치한다

아래와 같은 컬럼을 예시로 들어보자.

• gender: M 또는 F
• user_id: 너무 많다

위 예시에서 gender는 선택도가 낮고 user_id는 선택도가 높다. 선택도가 높은 컬럼을 앞쪽에 두면 필터링 범위를 줄여 읽는 행 수를 크게 줄일 수 있다.

3. 정렬, 범위 조건 까지 고려해서 순서 잡기

인덱스는 WHERE 뿐만 아니라 ORDER BY, GROUP BY, BETWEEN 에도 영향을 준다. 범위 조건이 들어가는 컬럼은 되도록 뒤쪽에 배치해야 전체 인덱스를 더 활용할 수 있다.

SELECT *
FROM posts
WHERE user_id = 123
  AND created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY created_at DESC;
-- 이렇게 마지막에 범위가 들어가는 쿼리문을 사용한다면

CREATE INDEX idx_user_date ON posts (user_id, created_at);
-- created_at을 제일 마지막에 두어야 한다

4. 복합 인덱스는 WHERE, JOIN, ORDER BY 모두 고려해서 설계해야한다

SELECT o.*
FROM orders o
         JOIN users u ON o.user_id = u.id
WHERE o.status = 'COMPLETED'
ORDER BY o.created_at DESC;

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

• user_id: JOIN
• status: WHERE
• create_at: ORDER BY
  이처럼 복합 인덱스 하나로 JOIN + WHERE + ORDER BY를 모두 처리할 수 있게 설계하면 좋다.

5. 중복 인덱스 및 과잉 인덱스 경계하기

• INDEX(a, b)가 있으면 INDEX(a)는 불필요하다. 이는 중복되는 인덱스이다.
• 너무 많은 인덱스는 쓰기 성능 저하 등 단점이 있다.