[MySQL] 인덱스

나는 DB와 MySQL에 대해 한 번이라도 깊게 공부하지 않았고, 인덱스가 조회 성능을 향상시킨다는 정도로만 알고 있었다.

 

이번에 프로젝트에 검색 기능을 도입했는데, 아직은 느리다는 체감을 하지는 못했으나, 데이터가 쌓일수록 느려질 수 있겠다는 생각이 들어 인덱스를 찍먹해봤고, 그 내용을 공유하고자 한다.

 

이 글을 읽으면 좋은 대상은 다음과 같다.

• 인덱스가 무엇인지 궁금하지만, 이름만 들어봤지 개념이나 사용법은 잘 모르는 사람
• MySQL에서 인덱스가 어떻게 저장되고, 그 구조가 탐색 속도를 어떻게 높여주는지 알고 싶은 사람, MySQL의 인덱스 종류가 궁금한 사람

✏️ 인덱스

인덱스는 데이터를 빠르게 찾기 위한 도구이다. 어떤 데이터를 찾기 위해 테이블 전체를 뒤지지 않아도 되게끔, 목차를 제공하는 것이다.

 

예를 들어 우리가 어떤 책을 읽을 때, 목차가 없다면 원하는 내용을 확인하기 위해 책을 처음부터 끝까지 뒤져야 하지만, 목차가 제공되면 타고 들어가서 원하는 내용을 금방 찾을 수 있다.

그럼 MySQL은 어떻게 목차를 제공할까?

✏️ MySQL의 인덱스

(아래 내용은 모두 MySQL InnoDB를 기준으로 설명한다.)

 

MySQL에서 일반적인 인덱스는 B+Tree 구조로 저장된다.

 

B-Tree와 B+Tree는 모두 균형 트리(Balanced Tree)라서 높이가 O(logN)이고, 탐색 시간도 O(logN)으로 전체 데이터를 전부 스캔하는 것보다 훨씬 빠르다.

 

B-Tree와 B+Tree의 차이점은 중간 노드에 데이터가 존재하는지 여부이다. B-Tree는 중간 노드에도 데이터가 존재하고, B+Tree는 리프 노드에만 데이터가 존재한다.

B+Tree는 리프 노드끼리는 연결되어있기에, 범위 탐색을 효율적으로 할 수 있다.

 

예를 들어, 위의 그림에서 Key가 3~7 사이인 데이터를 찾는다고 하면, 3을 찾고 오른쪽으로 이동하면서 7까지만 확인하면 된다. 전체 테이블을 스캔하면서 3보다 크고 7보다 작은지 확인하는 방식보다 훨씬 효율적이다.

✔️ 클러스터드 인덱스(Clustered Index)

처음에는 직접 추가를 해줘야지만 인덱스가 생긴다고 생각했다. 그런데 알고보니 테이블의 모든 데이터가 PK를 기준으로 정렬된 B+Tree 구조로 저장된다고 한다. 그리고 이 PK 인덱스를 클러스터드 인덱스라고 한다.

 

클러스터드 인덱스의 리프 노드에는 해당 행의 실제 데이터가 함께 저장되므로, 곧바로 데이터에 접근할 수 있다.

 

InnoDB에서 테이블이 저장된다는 것은, PK를 기준으로 한 B+Tree(클러스터드 인덱스)가 만들어진다는 뜻이라고 할 수 있다.

✔️ 보조 인덱스

반면 특정 컬럼에 대해 직접 추가한 보조 인덱스의 경우, 리프 노드에는 해당 행의 PK값만 들어있다. 따라서 PK를 기반으로 클러스터드 인덱스를 탐색해야 원하는 실제 데이터를 얻을 수 있다.

 

 

✏️ 인덱스는 언제 사용할까?

특정 컬럼에 대해 보조 인덱스를 추가해서 쿼리 실행 속도를 개선할 수 있다.

 

특정 컬럼에 대해 탐색이 필요한 경우, 즉 보조인덱스를 추가하면 유리할 수 있는 경우는 아래와 같다.

• WHERE 절에서 자주 사용되는 컬럼
  • 검색 기능(like keyword%)에 사용되는 컬럼
• JOIN 조건에 자주 사용되는 컬럼
• ORDER BY / GROUP BY에 자주 사용되는 컬럼
• 범위 검색에 사용되는 컬럼

자주 사용되더라도, 값 종류가 적은 컬럼(gender 등), 테이블 크기가 작을 때는 인덱스를 사용하지 않는 것이 빠를 수 있다.

✏️ 실제 성능 비교

인덱스를 이용하면 실제로 속도가 얼마나 빨라지는지 직접 쿼리를 실행시키면서 확인해보자.

 

참고로 더미 데이터 10만개를 기준으로 테스트했고, 쿼리를 총 6번 실행시키고 첫 번째를 제외한 5번의 평균 시간을 기준으로 비교했다.

select *
from content
where title like '부산%';

index 설정 전 - 5.82ms

 

이제 탐색의 기준이 되는 content 테이블의 title 컬럼에 인덱스를 추가하고, 똑같은 쿼리를 실행시켜보자.

CREATE INDEX idx_content_title ON content(title);

index 설정 후 - 3.62ms

 

약 1.6배 빨라진 것을 확인할 수 있었다.

 

어떤 인덱스가 사용됐는지는 쿼리 앞에 EXPLAIN 키워드를 붙이고 실행시키면 알 수 있다.

types이 range -> 인덱스 범위 탐색

 

각 type의 의미는 아래와 같다.

 

• ALL: 풀 테이블 스캔 (인덱스 사용 안 함).
• index: 풀 인덱스 스캔 (데이터 대신 인덱스 전체를 스캔).
• range: 인덱스 범위 탐색 (예: BETWEEN, <, >, LIKE 'abc%').
• ref: 인덱스로 동일 값 탐색, 여러 row 반환 가능 (예: col = ? 형태, FK 조인).
• eq_ref: 인덱스로 동일 값 탐색, 단 하나의 row만 반환 (예: PK나 UNIQUE 조인).
• const / system: 쿼리 최적화 시 상수처럼 취급되는 경우 (PK로 한 건만 찾을 때).
• NULL: 테이블 접근 자체가 불필요할 때 (예: SELECT 1).

 

✏️ LIKE %keyword%

아래의 쿼리는 인덱스가 적용될까?

select *
from content
where title like '%부산%';

 

EXPLAIN 키워드를 붙여서 확인해보면, type이 all로 뜨고, 인덱스가 존재함에도 Full Table Scan이 이뤄졌음을 확인할 수 있다.

type이 ALL

 

인덱스를 활용하지 못한 이유는 정렬된 B+Tree 구조를 활용할 수 없기 때문이다. 

 

B+Tree 인덱스는 앞글자부터 차례대로 비교해야 탐색이 가능한데, '%부산%'처럼 앞부분이 고정되지 않은 패턴은 이를 활용할 수 없다.
따라서 결국 Full Table Scan이 발생하게 된다.

 

→ 이런 경우에는 Fulltext Index를 사용하는 것이 적합하다.

✏️ Fulltext Index

fulltext index는 긴 문자열(CHAR, VARCHAR, TEXT)을 대상으로 단어 단위로 쪼개서 검색할 수 있는 인덱스다. 일반 B+Tree 인덱스는 문자열 맨 앞에서부터만 검색 가능(LIKE '부산%')하지만, fulltext Index는 문자열 중간에 있는 단어까지 검색할 수 있다.

 

fulltext index의 데이터 key가 설정되는 기준은 아래와 같다.

• 공백 단위로 단어 분리 (기본 설정)
• 또는 n글자 단위(ngram)로 분리

이렇게 쪼갠 단어들을 역색인(Inverted Index) 구조로 저장한다. 몇 행에 어떤 단어가 나왔는지 확인하는게 아니라, 어떤 단어가 나온 행이 몇 행인지 확인할 수 있다.

• "부산" → pk 1, pk 9, pk 11
  • "부산"이라는 단어가 포함된 컨텐츠들의 pk가 1, 9, 11
• "브이로그" -> pk 1, pk 4
  • "브이로그"이라는 단어가 포함된 컨텐츠들의 pk가 1, 4

 

 

아래 쿼리를 통해 특정 컬럼에 대한 fulltext index를 추가할 수 있다.

ALTER TABLE content ADD FULLTEXT INDEX idx_title (title);

이렇게 만든 fulltext index를 이용해 탐색을 하고 싶은 경우, MATCH ... AGAINST를 사용해야 한다.

SELECT *
FROM content
WHERE MATCH(title) AGAINST('부산');

✏️ 인덱스 단점

탐색 속도가 빨라지니까 인덱스를 무조건 적용하는게 좋을까?

 

인덱스를 사용하면 탐색 성능은 개선되지만, 그만큼 인덱스를 저장하기 위한 추가 디스크 공간이 필요하고, INSERT, UPDATE, DELETE 같은 쓰기 연산 시마다 인덱스도 함께 갱신해야 하므로 비용이 늘어난다.

 

따라서 자주 사용되고 값의 분포가 다양(카디널리티가 높음)한 컬럼에 인덱스를 적용하는 것이 효과적이다.

 

예를 들어 성별 컬럼처럼 값 종류가 극히 적은 경우에는, 설령 정렬이나 범위 탐색을 하더라도 풀 테이블 스캔에 비해 연산량이 크게 줄지 않을 것이다.

 

그런데 이렇게 말하면 너무 추상적이고 기준을 정하기가 어렵다. MySQL 공식 문서( 8.4 Optimization and Indexes )에서도 “빠른 쿼리 성능을 얻으려면 불필요하지 않고 최적의 인덱스 집합을 찾는 균형이 필요하다”라고만 설명하고 있다. 그래서 내가 내린 결론은, 인덱스 적용 기준은 직접 경험을 통해 데이터와 쿼리 패턴을 관찰하며 찾아가는 수밖에 없다는 것이다.

✏️ 그래서 인덱스 언제 적용하면 좋을까?

인덱스의 장, 단점을 고려해봤을 때, 인덱스를 추가하기 전에 아래의 질문을 던질 것 같다.

 

1. 카디널리티가 높은가

2. 조건으로 자주 사용되는가

3. UPDATE가 자주 일어나는가

 

근데 사실 이마저도 구체적이지 않은 추상적인 기준이고, 정말 성능 개선과 저하 사이에서 저울질하는 상황을 맞닥뜨려봐야 나만의 기준을 세울 수 있을 것 같다. (감을 잘 못 잡겠어서, ai에게 물어봤을 때 "쿼리 결과가 전체 테이블의 일부(10~15% 미만)만 반환된다면 인덱스 효율적이다"라는 답변을 얻을 수 있긴 했다.)

 

그런데 애초에 인덱스 효율성이 좋지 않으면 MySQL 옵티마이저가 알아서 풀테이블 스캔을 한다고 한다. 그래서 나는 이번 프로젝트에서 인덱스 추가해주고 EXPLAIN을 통해 인덱스 활용 하는지 확인해보고, 활용한다면 적용하는 전략을 적용해볼 것 같다.