복합 인덱스(Composite Index)와 B-Tree 원리 분석 - 1억 개 이상의 데이터 빠르게 조회하기

 

 

 데이터가 많을 때, 조회 성능이 느려지는 이유

SQL을 실행했을 때, 데이터를 빠르게 조회하려면 인덱스를 잘 활용해야 합니다.
하지만 많은 사람들이 단순 인덱스만 생성하고, **복합 인덱스(Composite Index)**를 활용하지 않아서 쿼리 속도가 최적화되지 않는 경우가 많습니다.

오늘은 복합 인덱스가 왜 중요한지, 그리고 B-Tree 구조가 어떻게 작동하는지를 깊이 있게 다뤄보겠습니다.
실제 1억 개 이상의 데이터를 다룰 때 어떻게 최적화할 수 있는지 살펴볼까요? 

 

 

 

 

 복합 인덱스란?

 단일 인덱스(Single Index) vs 복합 인덱스(Composite Index)

1.  단일 인덱스(Single Index)

단일 인덱스는 한 개의 컬럼에만 인덱스를 생성하는 방식입니다.

CREATE INDEX idx_latitude ON location(latitude);
CREATE INDEX idx_longitude ON location(longitude);

 

위처럼 latitude와 longitude에 각각 인덱스를 만들면,
각각의 값을 조회할 때는 빠르지만 둘을 함께 조회할 때는 비효율적입니다. 😨

 

💡 문제점

1. MySQL은 latitude 인덱스를 검색한 후, 다시 longitude 인덱스를 별도로 조회해야 합니다.
2. 이 과정에서 불필요한 데이터 검색이 많아지고, 속도가 느려집니다.

 

 

2. 복합 인덱스(Composite Index)

복합 인덱스는 여러 개의 컬럼을 한 번에 묶어서 인덱스를 생성하는 방식입니다.

CREATE INDEX idx_location_lat_lng_alt_ts
ON location(latitude, longitude, altitude, `timestamp`);

 

이렇게 하면 4개의 컬럼이 하나의 인덱스로 관리되며,
한 번의 인덱스 스캔으로 빠르게 데이터를 찾을 수 있습니다!

 

복합 인덱스의 장점

• 여러 개의 조건(latitude, longitude, altitude)을 한 번에 필터링 가능
• 불필요한 인덱스 탐색을 줄여서 속도가 빠름
• 한 번의 검색으로 데이터를 가져올 수 있음

 

* B-Tree 구조란?

 MySQL 인덱스는 어떻게 동작할까?

MySQL의 InnoDB 엔진에서 사용하는 기본 인덱스 구조는 **B-Tree(Balanced Tree)**입니다.
이 B-Tree가 어떻게 동작하는지 이해하면, 왜 복합 인덱스가 빠른지 알 수 있습니다!

 B-Tree란?

B-Tree는 균형 잡힌 트리 구조로, 데이터가 정렬된 상태에서 저장됩니다.
검색, 삽입, 삭제가 O(log N) 의 시간 복잡도를 가집니다.
즉, 1억 개의 데이터라도 약 20번 이하의 탐색으로 데이터를 찾을 수 있습니다. 🤯

 

 

🔹 B-Tree의 동작 방식

아래 그림을 보면 쉽게 이해할 수 있습니다.

                      [50]
                     /    \
                [30]      [70]
               /    \    /    \
            [10]   [40] [60]   [80]

이 트리에서 "60"을 찾는 과정은 다음과 같습니다.

1. 루트 노드(50)에서 비교 → "60은 50보다 크다" → 오른쪽 이동
2. 70에서 비교 → "60은 70보다 작다" → 왼쪽 이동
3. 60을 찾음 → 검색 완료!

O(log N) 의 성능을 가지므로, 데이터가 많아도 빠르게 검색할 수 있습니다. 

 

 

 

 

 

MySQL에서 "복합 인덱스"는 자주 사용하는 SELECT문의 WHERE 절을 기준으로 설계해야 한다.
즉, **어떤 컬럼을 자주 필터링하는지(WHERE 절에서 조건으로 사용)**가 가장 중요하다.

 

저는 제가 운영하는 플렛폼에서 좌표를 고객들의 위치정보를 수집하였고 특정 지역의 시간대와 고도를 추출해야 하는 작업을 진행해야 했습니다. 하지만 1억개가 넘는데이터가 적재되어 있다보니 조회해올때 성능이 매우 떨어지는 단점이 있었습니다. 아래는 제가 주로 실행하는 쿼리입니다.

 

SELECT latitude, longitude, `timestamp`, altitude
FROM location
WHERE latitude BETWEEN 37.460705 AND 37.514499
AND longitude BETWEEN 126.680818 AND 126.759986
AND altitude IS NOT NULL;

이 쿼리를 기반으로 복합 인덱스를 어떻게 설계해야 최적의 성능이 나오는지 분석해보았습니다.

 

 복합 인덱스와 B-Tree의 결합

복합 인덱스를 생성하면 B-Tree가 여러 컬럼을 한 번에 정렬합니다.
예를 들어, (latitude, longitude, altitude, timestamp) 복합 인덱스를 만들면 내부적으로 이렇게 정렬됩니다.

 

B-Tree 구조 (복합 인덱스)
-------------------------------------------------
| latitude | longitude | altitude | timestamp |
-------------------------------------------------
|   37.460 |   126.681 |    10.2  | 1700000000 |
|   37.461 |   126.682 |    15.4  | 1700000001 |
|   37.462 |   126.683 |    20.1  | 1700000002 |
...

 

이렇게 정렬된 상태로 저장되기 때문에, 쿼리를 실행하면 B-Tree가 빠르게 검색할 수 있습니다!
- 첫 번째 컬럼(latitude)로 먼저 필터링
- 그 안에서 두 번째 컬럼(longitude)으로 필터링
- 그 안에서 altitude를 확인 후, timestamp 가져오기

 

 

 복합 인덱스의 최적화된 순서

 "인덱스 컬럼의 순서를 잘 정해야 한다"는 의미

MySQL은 왼쪽에서 오른쪽 순서대로 검색합니다.
즉, WHERE 절에서 자주 필터링하는 컬럼을 앞쪽에 배치해야 최적화됩니다.

올바른 복합 인덱스 순서:

CREATE INDEX idx_location_lat_lng_alt_ts
ON location(latitude, longitude, altitude, `timestamp`);

 

이 순서로 인덱스를 만들면, MySQL이 데이터를 다음과 같이 찾습니다.

1. latitude 조건에 맞는 데이터를 찾음 (1억 개 → 1000만 개)
2. longitude 조건을 추가하여 검색 (1000만 개 → 100만 개)
3. altitude IS NOT NULL로 필터링 (100만 개 → 50만 개)
4. 최종적으로 timestamp 값을 가져옴 (50만 개 → 결과 반환)

 이렇게 하면 MySQL이 불필요한 검색을 줄이고, 최적의 속도로 데이터를 찾을 수 있습니다! 

 

 

 

 

SQL로 인덱스 확인하기

 MySQL에서 특정 테이블의 인덱스 조회

SHOW INDEX FROM 테이블명;

 실행하면 해당 테이블에 설정된 PK, 일반 인덱스, Unique Index, FK 인덱스 등 모든 인덱스 정보가 나온다.

 

 1. 현재 location 테이블의 인덱스 목록

 

 

 2. PRIMARY 키 분석 (PK)

idx_location_lat_lng_alt_ts	1	latitude	A	48677474		BTREE

 

• PRIMARY 키는 테이블의 기본 키(PK)
• 컬럼: id (첫 번째 필드)
• Cardinality(카디널리티): 194,709,898
  • 이건 **"고유한 값의 개수"**를 의미해.
  • 즉, id가 거의 2억 개의 고유한 값을 가지고 있다는 뜻.
  • PK는 유니크한 값이므로, 카디널리티는 항상 높아야 한다.
• Index_type(BTREE): PK는 자동으로 B-Tree 인덱스를 생성함.
• Non_unique = 0 (고유 인덱스)
  • PRIMARY KEY는 항상 유일해야 하므로, Non_unique = 0
  • 즉, 중복 불가, NULL 불가 🚀

결론:

• PK(id)는 기본적으로 자동 인덱싱되며, 데이터 검색 속도를 최적화한다.
• PK는 클러스터형(Clustered) 인덱스로 관리됨.
• PK 인덱스를 사용한 조회는 매우 빠름.

 

 

 

 복합 인덱스 상세 분석

 latitude (첫 번째 컬럼)

idx_location_lat_lng_alt_ts	1	latitude	A	48677474		BTREE

• 복합 인덱스의 첫 번째 컬럼이 latitude
• 카디널리티: 48,677,474 (고유한 값이 많음)
• 즉, 검색을 latitude부터 시작하면 인덱스를 효율적으로 사용할 수 있음!

---

 longitude (두 번째 컬럼)

idx_location_lat_lng_alt_ts	2	longitude	A	194709898		BTREE

• longitude는 latitude에 이어 두 번째 컬럼
• 카디널리티: 194,709,898
• 즉, latitude로 먼저 필터링한 후, 그 안에서 longitude를 추가로 검색하는 방식!

---

 altitude (세 번째 컬럼)

idx_location_lat_lng_alt_ts	3	altitude	A	194709898		BTREE

• altitude는 세 번째 컬럼
• 카디널리티: 194,709,898
• NULL 값을 허용 (YES 표기됨)
  • 즉, altitude IS NOT NULL 조건을 사용하면 인덱스를 더 잘 활용할 수도 있음.

---

 timestamp (네 번째 컬럼)

idx_location_lat_lng_alt_ts	4	timestamp	A	194709898		BTREE

• timestamp는 복합 인덱스의 마지막 컬럼
• 카디널리티: 194,709,898 (고유값이 많음)
• 이 컬럼은 WHERE 절에서 직접 필터링하는 게 아니라 SELECT 대상이라면 인덱스를 활용하는 데 영향을 미치지 않음.

 

 

 

 

 

Covering Index(커버링 인덱스)를 활용한 성능 최적화

 

 

MySQL에서 복합 인덱스를 만들 때 가장 중요한 원칙은
왼쪽부터 차례대로 WHERE 조건에 사용된 컬럼이 있어야 인덱스가 최대로 활용됨

예를 들어, 우리가 주로 실행하는 쿼리는:

 

SELECT latitude, longitude, `timestamp`, altitude
FROM location
WHERE latitude BETWEEN 37.460705 AND 37.514499
AND longitude BETWEEN 126.680818 AND 126.759986
AND altitude IS NOT NULL;

 WHERE 절에는 latitude, longitude, altitude가 들어가고,
 timestamp는 SELECT 대상일 뿐 WHERE 조건에는 없음.

 즉, latitude → longitude → altitude 순서로 검색 필터링이 이루어지는데,
그럼에도 불구하고 timestamp를 인덱스에 추가한 이유가 뭘까? 🤔

 

* Covering Index(커버링 인덱스)란?

쿼리를 실행할 때, 테이블을 조회하지 않고 인덱스에서 바로 데이터를 가져올 수 있도록 하는 기법!

즉, SELECT 대상 컬럼까지 인덱스에 포함하면, 인덱스만으로 결과를 가져올 수 있어서 성능이 훨씬 빨라짐!

---

 

 

timestamp가 커버링 인덱스를 구성하는 이유

만약 복합 인덱스를 latitude, longitude, altitude까지만 만들었다면?

*** 문제 발생

 WHERE 조건에 맞는 데이터를 찾기 위해 idx_location_lat_lng_alt_ts 인덱스를 탐색 (OK)
 하지만 timestamp가 인덱스에 없으므로,
→ MySQL은 다시 테이블을 조회(Full Table Access)해야 함 
→ 즉, "인덱스만으로 데이터를 가져오지 못하고" 다시 원본 테이블을 읽어야 하므로 속도가 느려짐

---

 4. timestamp를 추가하면 MySQL이 더 빠르게 동작하는 이유

 

CREATE INDEX idx_location_lat_lng_alt_ts
ON location(latitude, longitude, altitude, timestamp);

 

이렇게 만들면?

 WHERE 조건을 만족하는 행을 인덱스에서 찾음 (latitude → longitude → altitude)
그리고 timestamp도 인덱스에 포함되어 있으므로, MySQL이 테이블을 다시 조회하지 않고, 인덱스에서 직접 결과를 반환할 수 있음! 

즉, MySQL이 Covering Index를 사용하면 "Using index"로 표시됨

 

-- 현재 DB의 location테이블의 전체 인덱스 조회
SHOW INDEX FROM location;

 

 

Covering Index가 적용된 예제

만약 EXPLAIN을 실행했을 때,

EXPLAIN SELECT latitude, longitude, `timestamp`, altitude
FROM location
WHERE latitude BETWEEN 37.460705 AND 37.514499
AND longitude BETWEEN 126.680818 AND 126.759986
AND altitude IS NOT NULL;

 

결과에서 Extra 컬럼에 Using index 가 보인다면?

"Covering Index가 적용되었다"는 뜻이고, 쿼리 성능이 최적화된 상태이다.

 

 

결론

 

- 1억 개 이상의 데이터를 빠르게 조회하려면 복합 인덱스를 활용해야 합니다.
- B-Tree 구조를 이해하면, MySQL이 어떻게 데이터를 찾는지 알 수 있습니다.
- WHERE 절에서 자주 필터링하는 컬럼을 앞쪽에 배치해야 성능이 최적화됩니다.