MySQL 인덱스: B-Tree, Fulltext, Spatial의 차이와 사용법 (MySQL Index Guide: B-Tree, Fulltext, Spatial)

MySQL에서 인덱스는 성능 최적화의 핵심 도구입니다. 단순히 쿼리를 빠르게 만드는 것뿐만 아니라, 데이터 구조와 접근 방식에 따라 시스템 전체 성능에 영향을 미치기도 합니다. 본 글에서는 MySQL이 제공하는 세 가지 주요 인덱스 유형인 "B-Tree", "Fulltext", "Spatial" 인덱스를 비교하고, 각각의 사용 목적, 구조적 특징, 실무 활용 팁을 정리합니다.

---

B-Tree 인덱스 (MySQL에서 B-Tree 인덱스가 기본인 이유)

MySQL에서 가장 널리 사용되는 인덱스 유형은 "B-Tree 인덱스"입니다. MyISAM, InnoDB 등 주요 스토리지 엔진에서 기본 인덱스 구조로 채택하고 있으며, 일반적인 PRIMARY KEY, UNIQUE, INDEX 제약 조건은 모두 B-Tree 구조로 생성됩니다.

B-Tree는 "Balanced Tree"의 줄임말로, 데이터의 검색, 정렬, 범위 탐색에서 뛰어난 성능을 발휘합니다.

B-Tree 구조란 무엇인가요?

B-Tree는 각 노드가 여러 개의 자식 노드를 가질 수 있는 "균형 이진 탐색 트리"입니다. 다음은 일반적인 이진 트리(Binary Tree)와의 비교입니다:

• Binary Tree는 각 노드가 최대 2개의 자식만 가짐
• B-Tree는 수십~수백 개의 자식을 가질 수 있음
• 모든 리프 노드가 동일한 깊이에 존재하여 탐색 시간이 일정함

이 구조 덕분에 대규모 데이터셋에서도 데이터를 빠르게 찾고, 범위 조건을 효율적으로 처리할 수 있습니다.

B-Tree 인덱스의 성능적 이점

1. 정렬된 구조 유지: 인덱스가 자동 정렬되므로, ORDER BY, GROUP BY의 성능 향상
2. 빠른 검색: =, <, <=, >, >=, BETWEEN 연산에서 빠른 탐색 가능
3. 범위 탐색 최적화: 특정 범위에 해당하는 데이터를 빠르게 스캔 가능
4. 복합 인덱스 최적화: 다중 컬럼 조건에서도 유용하게 사용 가능 (단, 선두 컬럼 기준 주의 필요)

실무 예제: 인덱스 생성과 사용

단일 컬럼 인덱스 생성

-- 사용자 테이블에서 이메일 검색 최적화
CREATE INDEX idx_users_email ON users (email);

복합 인덱스 생성

-- 이름과 가입일을 자주 조합해 조회하는 경우
CREATE INDEX idx_users_name_joined ON users (name, joined_at);

인덱스 조회

SHOW INDEX FROM users;

쿼리 분석 (EXPLAIN)

EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com';

• "key" 컬럼에 사용된 인덱스명이 나타나면 인덱스 사용 성공

주의사항과 실무 팁

1. NULL 주의: 인덱스 컬럼이 NULL 값을 많이 포함할 경우 인덱스 효율이 떨어질 수 있음
2. 데이터 분포도(Cardinality):
   • 데이터가 고르게 분포된 컬럼일수록 인덱스 효율이 높음
   • SHOW INDEX 결과의 "Cardinality" 항목 참고
3. 복합 인덱스의 선두 컬럼 규칙:
   • 인덱스의 첫 번째 컬럼이 조건절에 포함되지 않으면 전체 인덱스를 활용할 수 없음
4. 모든 상황에서 인덱스가 성능을 보장하는 것은 아닙니다:
   • 너무 많은 인덱스는 쓰기 성능을 떨어뜨리고 저장공간을 낭비함

실무에서의 적용 전략

• 조회 쿼리 중심으로 필요한 컬럼만 인덱싱할 것
• EXPLAIN으로 항상 실행계획을 점검할 것
• 데이터 분포와 사용 패턴을 기반으로 주기적인 인덱스 리팩토링 필요

B-Tree 인덱스는 MySQL 성능 최적화의 핵심입니다. 구조를 잘 이해하고 상황에 맞게 활용한다면, 데이터 조회 속도는 물론 전체 서비스 반응성을 획기적으로 개선할 수 있습니다.

---

Fulltext 인덱스 (LIKE보다 강력한 검색, Fulltext 인덱스)

단순한 문자열 포함 여부를 확인하는 LIKE 구문은 직관적이지만, 성능과 검색 정확도 측면에서 한계가 있습니다. 반면, Fulltext 인덱스는 보다 빠르고 유의미한 결과를 제공하는 텍스트 검색 도구로, 특히 게시판이나 블로그와 같이 텍스트 기반 데이터가 많은 서비스에서 유용하게 활용됩니다.

Fulltext 인덱스의 구조와 전제 조건

Fulltext 인덱스는 단어 단위로 텍스트를 분리하고, 내부적으로 역색인(Inverted Index) 구조를 사용합니다. 다만, 다음과 같은 조건을 만족해야 정상 작동합니다:

• InnoDB와 MyISAM 스토리지 엔진만 지원
  • MyISAM은 오래전부터 지원
  • InnoDB는 MySQL 5.6 이후부터 지원
• CHAR, VARCHAR, TEXT 컬럼에만 적용 가능
• 기본적으로 최소 단어 길이(ft_min_word_len) 제한 존재 (기본값: 4)

MATCH AGAINST 구문 설명

Fulltext 인덱스를 활용한 검색은 MATCH(col1, col2, ...) AGAINST ('검색어') 구문으로 수행합니다.

SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST ('database indexing');

• MATCH()에는 검색 대상 컬럼들을 나열
• AGAINST()에는 검색어를 입력

Natural Language vs Boolean 모드

Fulltext 검색에는 두 가지 모드가 있습니다:

1. Natural Language 모드 (기본값)

• 단어 빈도와 연관도 기반으로 결과 정렬
• 불용어(stopword)는 자동 제외됨

SELECT *
FROM posts
WHERE MATCH(content) AGAINST ('mysql fulltext');

2. Boolean 모드

• AND, OR, NOT, +, - 등의 논리 연산자 사용 가능
• 결과의 점수는 계산되지 않음 (정렬이 자동으로 되지 않음)

SELECT *
FROM posts
WHERE MATCH(content) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

성능 고려사항 및 설정값

1. 정렬 조건: MATCH ... AGAINST 결과는 점수 기반 정렬이 가능함 (Natural Language 모드에서만)
2. 단어 길이 제한: 기본 설정은 4자 이상 단어만 인덱싱됨 (ft_min_word_len)
3. 인덱스 크기: 텍스트 분량과 단어 수에 따라 인덱스 크기가 크게 증가할 수 있음
4. EXPLAIN 사용법:

EXPLAIN SELECT * FROM posts WHERE MATCH(title) AGAINST ('index');

• key 컬럼에 Fulltext 인덱스가 나타나면 최적화 성공

한글 검색의 제약과 대안

MySQL 기본 Fulltext 인덱스는 영어 기반의 공백 단위 토큰화를 사용하므로 한글 검색에 한계가 있습니다:

• 초성, 종성 분리 불가
• 복합어, 띄어쓰기 오류에 취약

대안: Ngram 기반 인덱싱

MySQL 5.7 이상에서는 ngram 파서를 설정하여 한글 처리 개선이 가능합니다:

CREATE TABLE posts (
  content TEXT,
  FULLTEXT INDEX ft_idx (content) WITH PARSER ngram
) ENGINE=InnoDB;

• ft_min_token_size를 1~2로 설정해 초성 단위 검색 가능

게시판 검색 실무 예제

-- 게시글 테이블에서 제목과 내용 검색
CREATE FULLTEXT INDEX ft_post_idx ON posts (title, content);

SELECT id, title
FROM posts
WHERE MATCH(title, content)
      AGAINST ('mysql index');

• 복합 컬럼 인덱스 생성 가능
• MATCH ... AGAINST 구문으로 빠르고 정교한 검색 수행

요약

• LIKE보다 강력한 텍스트 검색을 원한다면 Fulltext 인덱스를 고려해야 함
• MATCH ... AGAINST 구문은 다양한 조건과 정렬에 활용 가능
• 한글 검색은 기본 설정만으로는 한계가 있으므로, ngram 파서와 함께 사용하는 것이 바람직함

텍스트 중심의 서비스에서는 Fulltext 인덱스를 도입함으로써 검색 정확도와 응답 속도를 모두 개선할 수 있습니다.

---

Spatial 인덱스(지리 기반 검색의 핵심, Spatial Index)

지도 기반 서비스, 위치 기반 추천, 주변 매장 검색 등 다양한 실무 환경에서 지리 정보를 효율적으로 다루는 기술이 필요합니다. MySQL의 Spatial 인덱스는 이러한 위치 기반 데이터의 탐색 성능을 크게 향상시키는 도구로, 공간 데이터를 정밀하게 처리할 수 있도록 설계되었습니다.

지원되는 공간 데이터 타입

MySQL의 공간 인덱스는 GEOMETRY 계열의 컬럼을 대상으로 적용되며, 주요 데이터 타입은 다음과 같습니다:

• POINT: 위경도 등 좌표 1개
• LINESTRING: 선 또는 경로 정보
• POLYGON: 다각형 영역 정보

InnoDB 기준으로는 POINT 타입에만 Spatial 인덱스를 적용할 수 있으며, MyISAM은 다양한 타입을 보다 유연하게 지원합니다.

제약사항 및 사용 조건

1. InnoDB vs MyISAM:
   • InnoDB는 MySQL 5.7부터 Spatial 인덱스를 지원 (단, POINT만 인덱싱 가능)
   • MyISAM은 다양한 GEOMETRY 타입에 대해 인덱스 생성 가능
2. GEOMETRY 컬럼 필수: Spatial 인덱스를 적용하려면 반드시 GEOMETRY 계열 데이터 타입이어야 함
3. NULL 불허 조건: InnoDB의 Spatial 인덱스는 NULL 값 허용 안 됨 (NOT NULL 설정 필요)

주요 공간 연산 함수 활용법

MBRContains() – 최소 경계 사각형(Minimum Bounding Rectangle) 포함 여부

SELECT *
FROM stores
WHERE MBRContains(
  ST_GeomFromText('POLYGON((...))'), location
);

ST_Within() – 한 도형이 다른 도형 내부에 있는지 판단

SELECT *
FROM stores
WHERE ST_Within(location, ST_GeomFromText('POLYGON((...))'));

ST_Distance() – 두 지점 간 거리 계산

SELECT id, name,
       ST_Distance(location, ST_GeomFromText('POINT(127.0 37.5)')) AS distance
FROM stores
ORDER BY distance ASC;

Spatial 인덱스의 내부 구조 개념

MySQL은 Spatial 인덱스를 구현할 때 R-Tree 방식을 사용합니다 (MyISAM 기준). R-Tree는 다차원 데이터를 트리 구조로 정리하여, 범위 기반의 공간 탐색에 최적화된 구조입니다.

InnoDB에서는 일반 인덱스와 유사한 방식으로 GIST 유사 구조를 활용하나, 명확한 세부 구현은 공개되어 있지 않습니다.

SRID(Spatial Reference System Identifier)의 중요성

공간 데이터를 처리할 때 좌표계(SRID)를 일치시켜야 정확한 결과를 얻을 수 있습니다.

• 기본 SRID는 0 (Cartesian 평면 좌표계)
• 위경도 기반 검색 시 SRID 4326 (WGS 84) 사용 권장
• 함수 사용 시 SRID가 다르면 에러 발생 가능

SELECT ST_Within(
  ST_GeomFromText('POINT(127.0 37.5)', 4326),
  ST_GeomFromText('POLYGON((...))', 4326)
);

실무 예제: 반경 내 매장 검색

-- 위치 컬럼 정의 및 Spatial 인덱스 생성
CREATE TABLE stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  location POINT NOT NULL,
  SPATIAL INDEX(location)
) ENGINE=InnoDB;

-- 반경 5km 이내 매장 검색 예시 (위경도 기반)
SELECT id, name
FROM stores
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(127.0 37.5)', 4326)) <= 5000;

※ ST_Distance_Sphere()는 거리 계산에 실제 지구 곡률을 반영함 (MySQL 5.7 이상)

요약 및 활용 전략

• 위치 기반 기능이 필요한 서비스에서는 Spatial 인덱스를 적극 고려
• POINT 컬럼, SRID 설정, NOT NULL 조건 등 구조적 제약을 먼저 확인
• 복잡한 지오메트리 연산은 기본 제공 함수(ST_Within, ST_Distance 등)를 적극 활용

위치 기반 서비스에 Spatial 인덱스를 적용하면, 데이터 처리의 정밀성과 속도 모두를 효과적으로 개선할 수 있습니다.

---

인덱스 선택 전략 (성능을 좌우하는 효율적인 설계 방법)

데이터베이스 성능을 좌우하는 핵심 요소 중 하나는 인덱스입니다. 하지만 모든 컬럼에 인덱스를 만드는 것이 정답은 아닙니다. 오히려 잘못 설계된 인덱스는 성능을 저하시킬 수 있습니다.

"잘 설계된 인덱스"란, 자주 조회되는 쿼리를 분석하여 최소한의 인덱스로 최대한의 성능을 끌어내는 구조를 의미합니다.

단일 인덱스 vs 복합 인덱스

단일 인덱스의 장점과 한계

• 단일 컬럼 인덱스는 구조가 단순하고 생성 비용이 낮습니다.
• 그러나 여러 조건이 조합된 쿼리에는 적합하지 않습니다.

복합 인덱스와 선두 조건

• 복합 인덱스는 최대 16개 컬럼까지 지정 가능
• 인덱스가 생성된 순서대로 조건을 만족해야 성능을 발휘함

예를 들어 다음과 같이 인덱스를 생성한 경우:

CREATE INDEX idx_user_search ON users (age, gender, region);

다음 쿼리는 인덱스를 활용할 수 있습니다:

SELECT * FROM users WHERE age = 30 AND gender = 'M';

그러나 다음 쿼리는 인덱스가 제대로 사용되지 않습니다:

SELECT * FROM users WHERE gender = 'M' AND region = '서울';

WHERE + ORDER BY + LIMIT

• 인덱스는 WHERE 조건뿐 아니라 ORDER BY, LIMIT과 함께 작동할 때 특히 강력합니다.

SELECT * FROM posts
WHERE category = 'news'
ORDER BY created_at DESC
LIMIT 10;

이 경우 category, created_at DESC 순서의 복합 인덱스를 생성하면 매우 효과적입니다.

선택도(Selectivity)의 이해

인덱스는 선택도가 높은 컬럼에 적용할수록 성능 향상이 큽니다.

선택도란?

• 전체 행 중 인덱스로 필터링 가능한 비율
• 값이 고르게 분포된 컬럼일수록 선택도가 높음

SHOW INDEX와 cardinality 확인

SHOW INDEX FROM users;

• 결과의 "Cardinality"가 높을수록 해당 컬럼이 인덱스로 적합함

예를 들어,

• 성별 컬럼(gender)은 값이 'M', 'F'만 존재하므로 선택도가 낮음
• 이메일 주소(email)은 거의 모두가 고유하므로 선택도가 매우 높음

자주 조회되는 쿼리 기반 설계

인덱스 설계의 출발점은 쿼리 패턴 분석입니다.

EXPLAIN으로 쿼리 실행 계획 분석

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

• "key" 컬럼에 인덱스명이 나타나야 인덱스가 사용된 것임을 의미함

실무 예시: 검색창, 필터 조건, 페이지네이션

-- 검색 조건이 자주 사용되는 게시판
SELECT *
FROM posts
WHERE title LIKE 'mysql%'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

이 경우, created_at과 함께 title의 Fulltext 인덱스를 혼합 고려할 수 있습니다.

인덱스가 오히려 성능을 떨어뜨리는 경우

• 사용되지 않는 인덱스는 쓰기 성능을 저하시킴
• 쿼리 조건과 일치하지 않는 인덱스는 오히려 옵티마이저를 혼란시킬 수 있음

인덱스 오남용 방지 가이드

INSERT / UPDATE 성능 저하

• 인덱스가 많을수록 데이터 삽입/갱신 시 추가 작업 발생

디스크 공간 증가

• 인덱스는 별도의 저장 공간을 차지함

커버링 인덱스가 항상 빠른 건 아님

• 일부 쿼리에서 인덱스만으로 데이터를 반환할 수 있는 경우 커버링 인덱스를 활용
• 하지만 SELECT *처럼 많은 컬럼을 조회하는 경우엔 큰 효과 없음

인덱스 실무 예제 정리

인덱스 생성

CREATE INDEX idx_email ON users (email);

인덱스 확인

SHOW INDEX FROM users;

쿼리 실행 계획 확인

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

인덱스 삭제

DROP INDEX idx_email ON users;

---

참고 자료

• MySQL 공식 문서
• Baron Schwartz 외, "High Performance MySQL", O'Reilly
• https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

MySQL 인덱스는 단순한 설정 항목이 아니라 데이터 구조와 쿼리 성능을 좌우하는 핵심 기술입니다. 상황에 따라 적절한 인덱스를 선택하고, 실시간 성능을 모니터링하며 지속적으로 개선하는 것이 가장 효과적인 운영 전략입니다.