인덱스(Index)를 설정할 때 고려해야하는게 뭘까?
데이터베이스에서 인덱스는 책의 목차와 비슷한 역할을 한다. 전체 데이터를 직접 훑지 않고 필요한 데이터(레코드)가 있는 위치를 빠르게 찾을 수 있도록 도와주는 구조를 말한다.
가장 기본적인 쿼리문을 예시로 보면서 생각해보자.
SELECT *
FROM posts
WHERE title = '인덱스란';위의 쿼리에서 인덱스가 없다면 데이터베이스에서 게시글(posts) 테이블에 대해 모든 행(row)를 처음부터 하나하나 확인하는 Full Table Scan을 수행하게 된다.
반면, 제목(title) 컬럼에 인덱스가 있다면, 인덱스 트리를 탐색하여 해당 제목을 가진 행의 위치로 접근할 수 있게 되는 것이다.
현재 사용하고 있는 MySQL InnoDB 엔진에서의 인덱스 구조에 대해서 조금 더 깊게 살펴보자.
MySQL의 인덱스 구조 (B-Tree)
MySQL(InnoDB)의 인덱스는 대표적으로 B-Tree(Balanced Tree) 구조를 사용한다. 이 구조는 정렬된 상태를 유지하며 깊이가 일정하기 때문에, 데이터가 아무리 많아져도 탐색 비용이 O(log N) 이다.
B-Tree 인덱스의 예시 구조
리프 페이지에는 정렬된 키 값과 함께 PK(Primary Key) 값이 저장되며 PK 값을 이용해 실제 데이터(테이블의 레코드)를 찾아가게 되는 것이다.
이 방식은 EXPLAIN ANALYZE를 사용할 때
index lookup이라는 내용으로 나타내며, 이러한 특징을 가지는 이유는 아래의 클러스터드 인덱스 내용으로 설명한다.
인덱스를 사용했을 때의 장단점
인덱스 장점
빠른 조회 성능: 정렬된 트리(B-Tree)를 이용하여 특정 값을 빠르게 찾을 수 있다.
범위 검색에 특화:
WHERE id >= ?와 같은 범위 조건에 특히 유리하다.
인덱스 단점
쓰기 성능 저하: 빠른 조회 성능을 제공하지만, 이를 위해 INSERT, UPDATE, DELETE 작업이 발생할 때마다 B-Tree가 재정렬 작업을 수행한다.
저장 공간 증가: 인덱스는 별도의 메모리 공간에 저장되므로 추가적인 용량을 사용하게 된다.
잘못된 인덱스는 성능 문제 악화: 카디널리티가 낮은 컬럼의 경우 제대로 활용하지 못한다.
카디널리티가 낮은 컬럼의 경우 옵티마이저가 인덱스 스캔을 안하는 경우도 발생함. (e.g. 성별, YES/NO 등)
단순히 “인덱스를 사용했을 때의 조회 성능이 빨라지니까 사용해야지” 라고 생각하는 것보다 상황에 맞게 인덱스를 사용하는 것이 중요하다.
카디널리티(Cardinality)?
카디널리티란 컬럼의 값이 얼마나 다양한가를 의미한다. 우리가 흔히 사용하는 Auto Increment 도 높은 카디널리티를 보여주며 인덱스 효율이 높다.
선택도(Selectivity) 라는 것은 전체 데이터 중에서 특정 조건에 맞는 데이터 비율을 의미한다.
옵티마이저는 카디널리티를 보고 인덱스를 타는 것과 그냥 테이블 스캔을 하는 것 중 무엇이 더 효율적일지 판단하게 되는 것이다. 즉, 선택도가 높다면 인덱스 스캔을 선택할 것이고, 선택도가 낮다면 테이블 스캔을 선택하게 되는 것이다.
이렇듯 컬럼의 특성에 따라 인덱스를 설정하는 것이 중요하다.
Clustered Index vs Non-Clustered Index
MySQL을 사용하는 환경에서 클러스터드 인덱스(Clustered Index) 라는 개념에 대해 알아둬야 인덱스를 제대로 사용할 수 있다고 생각한다.
클러스터드 인덱스(Clustered Index)
InnoDB는 PK가 곧 테이블 데이터의 정렬 기준이다.
실제 데이터(row)가 PK 순서대로 저장된 구조
즉, 테이블 자체가 하나의 거대한 인덱스 구조이다.
논-클러스터드 인덱스(Non-Clustered Index)
PK가 아닌 다른 컬럼에 인덱스를 생성했을 때 사용되는 인덱스이다. (세컨더리 인덱스)
리프 노드에 해당 컬럼 값 + PK 값만 저장한다.
실제 데이터(row)는 PK를 이용해 다시 찾아간다.
이러한 구조로 인해 PK가 아닌 인덱스(세컨더리 인덱스)를 탔을 경우, 실제 데이터에 접근하기 위해 여러 번 읽기 작업이 일어나게 된다.
리프 노드에 PK를 저장하는 이유?
만약 세컨더리 인덱스의 리프 노드가 실제 데이터(레코드)의 물리적 주소를 직접 저장한다면, 클러스터링 키 값이 변경되어 레코드의 물리적 위치가 변경될 때마다 해당 테이블의 모든 세컨더리 인덱스에 저장된 물리적 주소 값을 일일이 업데이트 해야한다.
이는 매우 큰 오버헤드를 발생시키는 것이다.
반면 PK 값을 논리적으로 참조하는 방식을 사용하면, 레코드의 물리적 위치가 변경되어도 PK 값 자체는 변하지 않으므로 세컨더리 인덱스를 수정할 필요가 없다. 이를 통해 레코드 변경 시 인덱스 유지 비용을 크게 절감할 수 있다.
PK가 바뀌면 InnoDB는 실제 레코드를 다른 페이지로 재배치한다.
세컨더리 인덱스가 물리적 주소를 가지고 있다면, 그 주소가 전부 무효화된다.
따라서 모든 세컨더리 인덱스의 leaf 노드를 전부 찾아가서 주소값을 업데이트 해야한다.
인덱스가 많은 테이블일수록, 업데이트 비용이 폭발적으로 증가
특히, INSERT, UPDATE, DELETE 작업에 대한 오버헤드가 폭발적으로 증가
이러한 문제를 방지하기 위해 MySQL(InnoDB)에서는 세컨더리 인덱스의 리프 노드에 PK 값을 논리적으로 참조하는 방식을 채택한 것이다.
논-클러스터드 인덱스는 구조적인 한계로 인덱스 탐색 -> 인덱스 스캔 -> 최종 레코드 읽는 과정을 거칠 수 밖에 없다. 하지만, 커버링 인덱스(covering index)를 사용하게 된다면 마지막 과정을 생략할 수 있다.
Covering Index
SELECT 절에서 필요한 모든 컬럼이 인덱스에 이미 포함된 인덱스를 의미하며, 세컨더리 인덱스의 리프 노드에서 필요한 데이터를 모두 바로 조회할 수 있다는 것을 의미한다.
단순 세컨더리 인덱스 처리 과정
WHERE 절의 조건으로 세컨더리 인덱스 탐색
인덱스 조건에 맞는 레코드의 PK 탐색
SELECT 절 확인
SELECT 절에서 요청한 컬럼이 세컨더리 인덱스에 없다면 PK(클러스터드 인덱스)를 통해 실제 데이터에 접근하여 조회
커버링 인덱스의 경우 위의 4번 작업에서 추가 조회없이 모든 정보를 가져올 수 있어 더욱 빠른 쿼리 성능을 만들어낼 수 있는 것이다.
물론, 커버링 인덱스도 인덱스이기 때문에 오히려 전체 성능이 떨어질 수 있으며, 특정 API에 너무 종속적인 인덱스가 되지 않도록 신중하게 설계해야한다.
즉, 최적화할 가치가 높은 쿼리문에 대해 전략적으로 적용하자.
정리해보면 인덱스는 단순히 “검색을 빠르게 하는 구조” 이상의 의미를 가진다. 단순하게 “사용하느냐 마느냐” 보다는
어떻게 정렬을 수행하는지
어떤 방식으로 실제 데이터를 탐색하는지
어떤 쿼리 패턴에 최적화되어야 하는지
이러한 부분들을 잘 생각을 하면서 개발을 진행하고, 내가 의도한대로 쿼리가 발생하는지 실제 발생하는 쿼리를 기반으로 분석하고 꾸준히 최적화를 진행해야 한다.
Last updated
Was this helpful?