지난 게시글에서는 B-tree와 B+tree가 어떤 원리인지를 배웠고, 키를 정렬된 순서로 보관한다는 것도 배웠다.
https://wanna-developer02.tistory.com/219
[DB] 쿼리 튜닝은 무엇인가 (2) - 인덱스 (B-tree와 B+tree)
지난 게시글에서는 쿼리 튜닝이 무엇인지에 대해 다루었고, 이번 게시글에서는 인덱스에 대해 이어서 정리하려고 한다. 지난 게시글은 아래 링크를 참고하면 된다.https://wanna-developer02.tistory.com/2
wanna-developer02.tistory.com
이번 게시글에서는 칼럼 여러개를 묶어 하나의 인덱스로 만들면 어떻게 될지 알아보려 한다. 목차는 다음과 같다.
- 복합 인덱스란
- 칼럼 순서를 정하는 방법
1. 복합 인덱스란?
지난 게시글에서는 하나의 칼럼으로 인덱스를 만드는 걸 배웠는데, 여러 칼럼으로도 인덱스를 만들 수 있다. 예를 들면 (user_id, created_at) 으로 인덱스를 만들면, B+tree는 userId로 먼저 정렬 후 createdAt으로 정렬을 한다. 이렇게 여러 칼럼으로 하나의 인덱스를 만든 것을 복합인덱스라고 한다.
위에서 예시를 들었듯이 칼럼의 순서가 중요하다. 첫번째 칼럼으로 먼저 정렬한 후, 두번째 칼럼을 정렬한다. 즉, 첫번쨰 칼럼이 같은 그룹 안에서만 두번쨰 칼럼이 정렬되는 것이다.
(user_id, created_at) 로 복합인덱스를 만든 경우, 아래 쿼리들에는 잘 쓰인다.
WHERE user_id = 123 -- 1순위 컬럼으로 검색
WHERE user_id = 123 AND created_at > '2024' -- 1순위 + 2순위
하지만 아래 쿼리에서는 제대로 쓸 수 없다. 이 경우, 결국 인덱스 전체나 테이블을 훑어야 한다.
WHERE created_at > '2024' -- 2순위 컬럼 단독
따라서 복합 인덱스는, 가장 왼쪽 칼럼부터 연속된 접두사로 조건이 주어진 경우에만 작동한다. 이를 왼쪽 접두사 규칙이라고 한다. (a,b,c) 인덱스라면 (a,b). (a,b,c), (a) 로는 쓸 수 있지만 (b), (b,c) 이렇게는 쓰이지 않게 된다.
이러한 규칙으로 인해 (a,b) 인덱스와 (b,a) 인덱스는 완전 다른 인덱스가 된다. 그럼 복합 인덱스는 칼럼 순서를 어떻게 정하는 게 좋을까?
2. 칼럼 순서를 정하는 방법
1순위 기준은 쿼리패턴이다. 어떤 칼럼 조합으로 자주 검색하는지 확인한 후, 단독이나 접두사로 자주 등장하는 칼럼을 앞에 둔다. 또, 등호 조건(=) 칼럼을 앞에, 범위 조건 (<, >, BETWEEN) 칼럼을 뒤에 둔다. 예를 들어 확인해보자.
WHERE user_id = 123 AND created_at > '2024-01-01'
이 경우, (user_id, created_at) 인덱스를 쓰면 user_id = 123으로 연속된 한 블록이 잡히고, 그 블록 안은 created_at으로 정렬돼 있으니 > '2024-01-01' 범위를 깔끔하게 이어서 읽을 수 있다.
그런데 만약 인덱스가 (created_at, user_id) 라면, created_at에 범위 조건이 먼저 걸리는 순간, 그 범위 안에서는 user_id가 더 이상 한 덩어리로 모여 있지 않다 (범위 안의 여러 날짜마다 user_id가 제각각 섞여 있다). 그래서 범위 조건이 한 번 걸리면 그 뒤 컬럼들은 인덱스로 좁히는 능력을 잃게 된다.
이렇게 간단하게 인덱스에 대한 정리가 끝났다. 원리를 공부했으니, 필요한 상황에 맞게 최소한의 인덱스를 활용할 수 있도록 프로젝트를 하면서 고민해보아야겠다!