🗂️ DB Index란?
인덱스는 테이블에서 데이터를 더욱 빠르게 검색(조회)하기 위해서 사용하는 자료 구조이다.
Index가 필요한 이유
데이터베이스에서 원하는 값을 찾는 상황이라고 가정해 보자.
인덱스가 없는 상황에서는, Full Table Scan 을 해야 한다.
🔍 Full Table Scan
Full Table Scan을 하는 경우는 아래와 같다.
1. 테이블의 모든 row를 처음부터 끝까지 탐색하는 경우
SELECT * FROM users WHERE age = 20;2. 인덱스를 타지 않는 연산을 사용하는 경우
SELECT * FROM users WHERE YEAR(birthdate) = 2000;- birthdate에 인덱스가 있더라도, YEAR() 함수로 감싸면 인덱스를 무시하고 전체를 탐색한다.
SELECT * FROM users WHERE name LIKE '%sangho%';- LIKE 문 앞에
%가 붙는 경우 인덱스를 사용할 수 없다. - 반면,
sangho%와 같이 뒤에 붙는 경우에서는 사용이 가능하다. - 이는 인덱스의 자료구조가
B-Tree이기 때문인데, 해당 내용은 아래에서 자세히 설명할 예정이다.
3. 조건에 해당하는 데이터가 너무 많은 경우
SELECT * FROM users WHERE age > 5;- 다시 말해, 인덱스가 있더라도 굳이 인덱스를 탈 필요가 없다고 판단하는 경우이다.
- 테이블에 1살부터 100살까지의 데이터가 총 100개 있다고 가정해 보자. 위 쿼리문을 실행하면 95개의 데이터가 조회될 것이다.
- 95% 해당하는 데이터를 인덱스를 타는 것은 비효율적이라고 판단이 되어 타지 않는다.
🧑🏻💻 즉, 인덱스가 항상 더 효율적이라고 말할 수는 없다. 이에 대해서는 이후 목차에서 정리할 예정이다.
- 그런데 여기서 판단은 누가, 언제 하는 것일까?
DB 옵티마이저(Query Optimizer)
- DB 옵티마이저는 SQL 실행 전에 여러 실행 계획 후보들을 만든 후, 그 안에서 가장 cost가 적은 계획을 택하여 실행한다.
- 실행 계획 후보들의 cost를 계산할 때는 아래의 요소들을 고려한다.
| 고려 항목 | 설명 |
|---|---|
| 통계 정보 (Statistics) | - 테이블 전체 row 수 - 컬럼별 데이터 분포 - NULL/중복 여부 - 인덱스 selectivity 등 |
| 인덱스 유무 | - 인덱스 존재 여부 - 단일 인덱스 vs 복합 인덱스 - Covering Index 여부 |
| 조건절 형태 | - WHERE 조건에서 사용하는 연산자 종류 (LIKE, BETWEEN, =, <, != 등) |
| 조인 방식 | - Nested Loop Join - Hash Join - Merge Join 등 다양한 조인 전략 비교 |
| 필요한 작업 | - ORDER BY, GROUP BY, 집계 함수 등 부가 연산 여부 - 서브쿼리 포함 여부 |
| 디스크 접근량 | - 전체 테이블 vs 인덱스 탐색 시 I/O 비용 차이 - Random Access vs Sequential Access |
여기서 통계 정보 - 인덱스 selectivity는 무엇일까?
Index Selectivity
- 이는 인덱스의 효율성을 판단하는 핵심 개념으로, 특정 조건이 전체 row 중 얼마나 적은 row를 선택하는지를 나타내는 수치이다.
| 조건 | 전체 row 수 | 조건에 해당하는 row 수 | selectivity | 효율성 |
|---|---|---|---|---|
WHERE gender = 'M' | 1,000,000 | 500,000 | 0.5 | ❌ 낮은 효율 |
WHERE email = 'abc@example.com' | 1,000,000 | 1 | 0.000001 | ✅ 매우 효율적 |
- 위와 같이 selectivity 수치가 낮을수록 더욱 높은 선택도를 가진다고 말할 수 있다.
- 주로 고유값이 많은 컬럼(ex. id, email 등)일수록 높은 선택도를 가지며, 이는 카디널리티와 유사한 특성을 가진다.(카디널리티가 더 상위 개념)
Full Table Scan의 단점
결과적으로 단점은 아래와 같다.
1. Full Table Scan은 모든 데이터를 무조건 읽는다
- 조건에 맞는 row가 10건만 있어도, 인덱스가 없으면 100만 row 전체를 읽어야 한다.
- 반면 인덱스가 있으면, 딱 10건만 찾아서 바로 접근 가능하다.
2. 디스크 I/O 비용 차이가 크다
- 인덱스 탐색은 필요한 블록만 접근 (랜덤 액세스, 적은 양)
- Full Scan은 모든 블록 순차 접근 (시퀀셜, 많고 무거움)
🧱 Index의 자료구조
그렇다면 Index는 어떻게 효율적으로 탐색할 수 있게 해주는 것일까? 이는 자료구조를 통해 살펴볼 수 있다.
| 인덱스 종류 | 사용되는 자료구조 | 주요 용도 / 특징 | 사용 가능 엔진 |
|---|---|---|---|
| PRIMARY / INDEX / UNIQUE | B+Tree | 일반 조회, 범위 검색, 정렬 등 다용도 | InnoDB (기본) |
| FULLTEXT | Inverted Index (역색인) | 자연어 기반 텍스트 검색 (MATCH ...) | InnoDB |
| SPATIAL | R-Tree | 공간 좌표 기반 검색 (GIS 등) | InnoDB |
| HASH | Hash Table | 빠른 동등 비교 (=) 검색 전용 | MEMORY |
위와 같이 MySQL Index는 4종류의 자료구조를 가지고 있는데, 이번 글에서는 B+Tree에 대해서만 다루고자 한다.
B+Tree
B+Tree는 B-Tree의 확장 개념으로, 트리의 높이를 최대한 낮게 유지하며 탐색의 성능을 극대화한 자료구조이다.
B-Tree & B+Tree가 무엇인지, 그리고 차이점은 무엇이 있는지는 해당 글에 정리해 두었다.
🔀 Index 동작 과정
지금까지 인덱스가 왜 필요한지, 그리고 어떠한 자료구조로 구성되어 있는지까지 알아보았다.
하지만 실질적으로 어떠한 동작 과정으로 원하는 값을 빠르게 조회할 수 있는지는 아직 감이 잘 오지 않는다. 지금부터는 이에 대해 정리하려고 한다.
위 이미지를 통해서 설명하도록 하겠다. 이미지 출처
- 개발자가 특정 컬럼에 대해서 인덱스를 생성한다. 현재는
company_id이다. - 해당 컬럼의 값들을 기반으로 B+Tree 자료구조의 인덱스가 생성된다. 이 인덱스는 디스크 상 별도의 저장 구조로 유지된다.
- 사용자가
company_id = 18인 데이터를 조회하는 쿼리를 보낸다. - DB는 먼저 인덱스(B+Tree)를 탐색하여 18이라는 키 값을 가진 리프 노드의
포인터 목록을 찾아낸다. - 이 포인터는 실제 테이블의 row 위치를 가리키며, DB는 해당 위치로 직접 접근하여 row 데이터를 읽는다.
여기서 아래의 궁금증들이 생길 수 있다.
1. 인덱스는 어디에 저장되는가?
MySQL InnoDB 기준으로, 인덱스는 데이터(.ibd) 파일 내부에 함께 저장된다.
테이블을 만들게 되면, 보통 ~.ibd라는 파일이 함께 생기게 되는데, 인덱스 구조(B+Tree)는 내부 페이지 단위 구조에 함께 저장이 된다. (페이지 단위에 대해서는 추후 따로 다루어 보려고 한다)
때문에 나중에 인덱스를 생성하게 된다면 .ibd 파일의 크기가 증가하게 되는 것이다.
2. 인덱스는 저장 공간을 얼마나 차지하는가?
저장 공간을 얼마나 차지하는지에 대해서는 정확히 공식처럼 계산하기는 어렵다. 아래와 같은 요소들에 의해서 변동성이 크기 때문이다.
- 인덱스 키의 데이터 타입
- 인덱싱 대상 row 수 (인덱스는 row 수만큼 리프 노드를 가진다)
- 인덱스 타입
- InnoDB 설정 (페이지 크기 등)
실제로 확인해 보고 싶다면 아래 명령어를 통해서 확인이 가능하다.
SHOW TABLE STATUS LIKE {테이블명};여기서 Index_length 컬럼이, 인덱스가 차지하는 디스크 용량(byte)이 된다.
3. 인덱스를 생성하면 테이블에 컬럼이 하나 추가되는가?
그렇지 않다.
테이블 스키마에 컬럼이 추가되는 것이 아니라, 데이터베이스 엔진이 시스템적으로 관리하는 메타 데이터로 남기 때문이다.
🚨 Index의 한계
지금까지는 인덱스의 좋은 점들에 대해서만 나열했다. 그렇다면 모든 컬럼에 대해서 인덱스를 생성한다면 조회 속도가 매우 빨라지지 않을까?
하지만 실제로는 여러 한계점들도 존재한다.
1. 추가적인 저장 공간 사용
위에서 언급했던 것처럼 인덱스를 생성하면 .ibd 파일 내부에 별도의 인덱스 구조가 저장된다. 이는 여러 조건에 따라 달라질 수 있으나, 보통 전체 테이블 크기의 5~25% 수준을 차지하게 된다고 한다.
또한 인덱스를 여러 개 생성하여 복합 인덱스가 되는 경우에는 차지하는 정도가 빠르게 증가하게 된다.
2. 관리 비용
인덱스는 조회 성능을 극대화하는 데 특화되어 있다. 반면 쓰기 연산(INSERT, UPDATE, DELETE)에 대해서는 오히려 성능 저하의 원인이 될 수 있다.
이는 쓰기 연산이 발생할 때마다, 해당 인덱스에도 동일한 변경을 반영해야 하기 때문이다.
즉, 데이터가 삽입되거나 삭제될 때, 인덱스에 저장된 B+Tree 구조 역시 함께 갱신되어야 하며, 이는 추가 비용을 발생시킨다.
따라서 조회 성능을 극대화하되, 쓰기 성능과의 Trade-Off를 고려한 인덱스 설계가 중요하다.
✅ Index 생성 전략
마지막으로 효율적인 인덱스 생성 전략에 대해 작성하며 마무리하려고 한다.
1. WHERE, JOIN, ORDER BY에 자주 사용되는 컬럼
해당 컬럼이 조건절, 정렬, 조인 기준으로 자주 사용된다면 인덱스를 고려하면 좋다. 특히 대용량 테이블에서의 조건 검색에 효과적이다.
SELECT * FROM users WHERE email = 'abc@example.com';2. 데이터의 선택도(Selectivity) 가 높은 컬럼
즉, 고유값이 많은(=카디널리티가 높은) 컬럼일수록 인덱스 효율이 높다. (email, user_id 등..)
반면, gender, is_active(T or F 이기에) 같은 컬럼은 selectivity가 낮기에 거의 효과가 없다.
3. FK(외래 키)로 자주 조회되는 컬럼
PK(Primary Key)는 기본적으로 인덱스가 자동 생성된다.
하지만 FK는 인덱스를 자동 생성하지 않기 때문에, FK 컬럼에 대한 WHERE 조회나 JOIN이 빈번하다면 수동으로 인덱스를 걸어야 성능 향상을 기대할 수 있다.
4. UPDATE/DELETE가 많다면 최소화 고려
쓰기 연산이 많고, row가 자주 바뀌는 컬럼에는 인덱스 생성을 자제하는 것이 좋다.
예를 들어 상태에 따라 값이 자주 바뀌는 status 컬럼 등은 불필요한 인덱스 갱신 부담이 크다.