인덱스란?
인덱스는 데이터베이스에서 데이터를 조회할 때 결과를 빠르게 추출하도록 도와주는 하나의 '데이터베이스 개체'입니다. 마치 사전의 '찾아보기'와 같은 역할을 한다고 생각하시면 됩니다. 결국, 인덱스에 따른 결과 값에 차이는 없고 단지 조회 성능을 개선하는 용도로 사용됩니다.
실무에서는 현실적으로 인덱스 없이 DB 운영이 불가능합니다. 하지만 인덱스가 무조건 있다고 해서 좋은 것은 아니며, 추가적인 DB 공간이 필요하고 데이터 변경 작업시 오히려 성능 저하를 불러일으킬 수 있기 때문에 꼭 필요한 컬럼에 인덱스를 생성해야합니다.
인덱스 특징
- 장점
- 적절한 인덱스를 생성하고 사용하면 조회 성능을 획기적으로 개선할 수 있음
[검색 속도 향상 & 시스템 성능 향상]
- 적절한 인덱스를 생성하고 사용하면 조회 성능을 획기적으로 개선할 수 있음
- 단점
- 인덱스는 대략 테이블 크기의 10% 공간이 추가로 필요 (인덱스 페이지 때문)
- SELECT 가 아닌 데이터의 변경 작업 (INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능에 악영향 (페이지 정렬 작업 때문)
인덱스 종류
인덱스의 종류는 크게 클러스터형 인덱스와 보조 인덱스로 나뉩니다. 각 인덱스의 특징은 다음과 같습니다.
클러스터형 인덱스 (Clustered Index)
- 테이블당 1개만 존재할 수 있음
- 기본 키로 지정된 컬럼은 자동으로 클러스터링 인덱스가 생성됨
- 실제 저장된 데이터와 같은 무리의 페이지 구조를 가짐
- 클러스터링 인덱스를 기준으로 데이터가 자동 정렬됨
- 기본 키를 변경하면 클러스터링 인덱스가 변경되기 때문에 변경된 기본 키를 기준으로 자동 정렬
보조 인덱스 (Secondary Index / non- Clustered Index)
- 한 테이블에 여러개 설정 가능
- UNIQUE 키워드로 고유 컬럼 지정시 자동으로 보조 인덱스가 생성됨
- 실제 저장된 데이터와 다른 무리의 별도의 페이지 구조를 가짐
- 클러스터링 인덱스와 달리 데이터를 정렬하지 않음
- CREATE INDEX 문으로 직접 보조 인덱스 생성 가능
인덱스 동작 원리
클러스터링 인덱스와 보조 인덱스 모두 내부적으로 균형 트리(B-tree)로 만들어집니다. 균형 트리 구조에서 데이터가 저장되는 공간을 '노드' 라고 합니다. 노드는 위치에 따라 루프 노드, 중간 노드, 리프 노드로 구성됩니다. MySQL에서는 이러한 노드들을 '페이지' 라고 부릅니다.
이와 같은 균형 트리 (B-tree)는 데이터를 검색할 때 아주 뛰어난 성능을 발휘합니다. 이와 같은 구조에서 데이터 검색 시 루트 페이지에서 검색할 데이터가 몇 번 페이지에 있는지 탐색하고 해당 페이지를 확인하여 데이터를 추출하게 됩니다. 이 과정에서 몇 개의 페이지를 읽었는지에 따라 효율성을 판단할 수 있습니다.
MySQL에서는 페이지를 나누어 데이터를 저장합니다. 페이지는 MySQL 기준 최소 16KB의 크기를 가지며 페이지 데이터 공간이 추가적으로 필요할 경우 페이지 분할 작업을 통해 페이지를 생성하고 각 페이지에 데이터를 분할하는 작업을 거칩니다.
페이지 분할
인덱스를 구성하면 데이터 변경 작업 (INSERT, UPDATE, DELETE)시 성능이 나빠질 수 있습니다. 특히 INSERT에 영향이 큰데, 그 이유는 페이지 분할 작업이 발생하기 때문입니다.
페이지 분할이란 데이터를 페이지 단위로 저장하는 기본 구조에서 추가적인 페이지가 필요할 때 새로운 페이지를 준비하여 데이터를 나누는 작업을 말합니다. 페이지 분할 작업이 자주 일어나면 데이터베이스 성능에 큰 영향을 미칩니다.
클러스터형 인덱스 페이지 구조
클러스터형 인덱스 페이지는 다음과 같이 루트 - (중간) - 리프 페이지로 이루어져 있습니다. 클러스터형 인덱스가 걸린 기본 키를 기준으로 실제 데이터가 정렬됩니다. 클러스터형 인덱스는 리프 노드가 곧 데이터 페이지(실제 데이터가 저장된 위치) 입니다. 클러스터형 인덱스를 통해 검색시 먼저 루트 노드에서 탐색할 페이지를 찾고 해당 페이지에서 검색할 데이터를 찾게 됨으로써 검색 시간을 줄이게 됩니다.
보조 인덱스 페이지 구조
보조 인덱스 페이지는 실제 데이터를 정렬하지 않습니다. 따라서, 보조 인덱스를 설정해도 데이터 페이지에는 아무런 영향이 없고 별도의 장소에 인덱스 페이지를 생성합니다. 별도로 생성된 인덱스 페이지는 인덱스가 걸린 컬럼 값에 따라 정렬되어 별도의 페이지를 생성합니다.
리프 페이지는 실제 데이터가 저장된 위치를 가리킵니다. 클러스터링 인덱스와 같이 사용하게 될 경우 클러스터링 인덱스로 인해 정렬된 데이터 페이지를 가리키게 됩니다.
인덱스 사용법
인덱스 또한 데이터베이스 개체입니다. 따라서 CREATE, DELETE 문으로 인덱스를 생성 및 제거할 수 있습니다.
- 자동으로 생성되는 인덱스
- 클러스터형 인덱스
- PK로 지정 (우선순위) / UNIQUE + NOT NULL 제약 조건
- 보조 인덱스
- UNIQUE 제약 조건
인덱스 생성
인덱스는 다음의 SQL 문을 통해 생성할 수 있습니다.
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열 이름) [ASC | DESC]- UNIQUE 옵션은 중복이 안되는 고유 인덱스를 생성하는 것인데 생략하면 중복이 허용됩니다.
- UNIQUE 옵션으로 인덱스를 생성하려면 인덱스를 설정하려는 컬럼 값에 중복이 있으면 안됩니다
(이후 삽입시에도 마찬가지)
- UNIQUE 옵션으로 인덱스를 생성하려면 인덱스를 설정하려는 컬럼 값에 중복이 있으면 안됩니다
- ASC 또는 DESC 로 인덱스의 정렬 방향을 변경 가능합니다.
- DESC 로 만드는 경우는 거의 없습니다.
인덱스 제거
인덱스는 다음의 SQL 문을 통해 제거할 수 있습니다.
DROP INDEX 인덱스_이름 ON 테이블_이름- DROP INDEX 문으로 간단하게 제거할 수 있습니다.
- 기본 키, 고유 키(UNIQUE)로 자동 생성된 인덱스는 DROP INDEX로 제거할 수 없습니다.
- ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거 가능
인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 더 좋습니다.
또한 인덱스가 많이 생성되어있는 테이블의 경우 사용하지 않는 인덱스는 과감히 제거해주는 것이 좋습니다.
(인덱스는 메모리를 사용하는 개체이기 때문)
예제
MySQL workbench 를 통해 인덱스를 생성하고 결과를 확인해보겠습니다.
인덱스 생성
-- 단순 보조 인덱스 생성
CREATE INDEX idx_member_number
ON member (mem_number);
-- 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_name
ON member (mem_name);
-- 테이블 구분 분석/처리를 통해 인덱스 적용
ANALYZE TABLE member;member 테이블의 addr 컬럼에는 단순 보조 인덱스를 설정했고 mem_name 컬럼에는 고유 보조 인덱스를 설정했습니다. 이후, ANALYZE TABLE 을 통해 인덱스를 적용해주어야 합니다.
SHOW INDEX FROM member;SHOW INDEX 구문을 통해 테이블에 적용된 INDEX를 확인할 수 있습니다.
현재 인덱스는 다음과 같이 클러스터형 인덱스 1개, 보조 인덱스 2개입니다.
인덱스를 사용한 조회
SELECT mem_id, mem_name, addr
FROM member
WHERE mem_name = '에이핑크';위 SQL문은 WHERE 절에서 인덱스가 걸린 컬럼을 조건으로 합니다. 따라서 인덱스를 사용하여 데이터를 조회합니다. (Single Row)
SELECT * FROM member
WHERE mem_number >= 5;위와 같이 범위 검색에 대해서도 인덱스를 사용하여 데이터를 조회합니다. (Index Range Scan)
인덱스가 사용되지 않는 경우
인덱스를 설정했더라도 검색시 무조건 인덱스를 사용하는 것은 아닙니다. 몇가지 예시를 통해 알아보겠습니다.
인덱스 조건 누락
SELECT * FROM member;
인덱스가 설정된 컬럼을 조건으로 걸지 않았을 경우, 테이블에 인덱스가 존재하더라도 위와 같이 Full Table Scan이 발생합니다. (전체 테이블 조회)
전체 테이블 검색이 더 빠른 경우
SELECT * FROM member
WHERE mem_number >= 1;
member 테이블에 저장된 모든 number값은 1 이상입니다. 따라서 전체 데이터가 조회됩니다.
이와 같이 인덱스가 설정된 컬럼을 조건에 걸었더라도 인덱스를 사용하여 데이터를 추출하는 것 보다 전체 테이블을 탐색하는 것이 더 빠른 경우가 있습니다. MySQL은 인덱스를 사용할지, 전체 테이블을 탐색할지 판단하고 해당 방식을 사용하여 데이터를 조회합니다.
인덱스가 사용된 컬럼에 연산을 가할 경우
-- 인덱스 사용 X
SELECT mem_name, mem_number
FROM member
WHERE LENGTH(addr + '1') >= 5;
-- 인덱스 사용 O
SELECT mem_name, mem_number
FROM member
WHERE LENGTH(addr) >= 5 - 1;인덱스는 가공된 데이터를 저장하고 있지 않기 때문에 컬럼에 연산이 가해지면 인덱스를 사용한 데이터 조회가 불가능해집니다. 따라서, 컬럼에 직접적인 연산을 가하지 않는 방식으로 사용해야합니다.
인덱스 제거
다음의 SQL문을 통해 설정된 인덱스를 제거할 수 있습니다.
DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_mem_number ON member;
클러스터형 인덱스는 ALTER TABLE 문으로만 제거 할 수 있습니다.
ALTER TABLE member
DROP PRIMARY KEY;
인덱스 간단 성능 비교
인덱스에 따른 성능 차이를 확인하기 위해 약 15000 건의 데이터에 대하여 비교 해보겠습니다.
인덱스 X
먼저 인덱스 없이 데이터를 조회해보겠습니다.
SELECT * FROM rental
WHERE rental_date ='2005-05-24 22:53:30';
Full Table Scan 을 통해 데이터가 조회되었으며 조회 비용은 약 1502.14입니다.
인덱스 O
다음의 SQL로 예제 테이블에서 cadinality가 높은 rental_date 컬럼에 idx_rental 인덱스를 설정하고 똑같은 쿼리로 데이터를 조회해보았습니다.
CREATE INDEX idx_rental
ON rental (rental_date);
analyze table rental;
SELECT * FROM rental
WHERE rental_date ='2005-05-24 22:53:30';
단순 보조 인덱스를 사용하여 데이터를 조회하였으며 조회 비용은 0.25입니다. 인덱스를 사용하지 않은 조회보다 인덱스를 사용한 조회 비용이 훨씬 싼 것을 알 수 있습니다.
물론, 인덱스를 사용하더라도 여러건의 데이터를 조회한다면 비용은 더 커질 수 있습니다.
SELECT * FROM rental
WHERE rental_date between '2005-05-26 13:06:05' and '2005-05-30 00:00:00';
between을 통해 특정 범위를 조건으로 걸었더니 Index Range Scan으로 실행 계획이 바뀐 것을 확인할 수 있습니다. 기존 1건의 데이터만 조회했던 쿼리에 비해 조회 비용도 상승했습니다.
rental 테이블의 모든 rental_date 값은 아래 SQL 조건보다 크거나 같습니다. 따라서, 모든 데이터가 조회됩니다.
SELECT * FROM rental
WHERE rental_date between '2000-05-24 00:00:00' and '2025-06-01 00:00:00';
WHERE 조건에 인덱스로 설정한 컬럼이 있음에도, Full Table Scan 으로 데이터를 조회했습니다.
이처럼, MySQL은 인덱스를 사용하는 것과 전체 테이블을 조회하는 것을 비교해서 더 나은 방식을 선택하여 실행합니다.
인덱스 사용시 유의사항
1. 카디날리티(cardinality)가 높은 (중복도가 낮은) 열에 인덱스를 설정해야 합니다.
SQL에서 카디널리티라는 용어는 데이터베이스 테이블의 특정 열에 포함된 데이터 값의 고유성을 나타냅니다. 인덱스는 중복도가 높은 컬럼에 설정할 경우, 별다른 성능 향상의 결과를 얻지 못하기 때문에 중복도가 낮은 컬럼에 설정해야합니다.
2. WHERE 절에 자주 사용되는 열에 인덱스를 만들어야 합니다.
WHERE 절에 사용되는 열에 따라 테이블 스캔 방식이 변경됩니다. WHERE 절에 자주 사용되지 않는 열에 인덱스를 설정하였다면 인덱스를 실제로 활용할 일이 적다는 뜻으로, 다른 열에 인덱스를 설정하는 것이 좋습니다.
3. 잘 사용되지 않는 인덱스는 과감히 제거합니다.
인덱스는 테이블 데이터의 약 10% 정도를 차지하는 데이터베이스 개체입니다. 또한, 데이터 변경 작업시 페이지 분할 작업으로 인해 성능에 악영향을 미칠 수 있습니다. 따라서 인덱스를 잘 사용하지 않는다면 과감히 제거하는 것이 좋습니다.
4. INSERT, UPDATE, DELETE 등의 데이터 작업이 빈번한 테이블에서는 인덱스 사용을 고려해야합니다.
데이터 변경 작업(INSERT, UPDATE, DELETE)이 자주 발생한다면 페이지 분할 작업으로 인해 조회 성능을 제외한 다른 작업 성능이 느려지게 됩니다. 이처럼 데이터 변경 작업이 빈번한 테이블이라면 인덱스를 설정하지 않음으로써 조금 느린 조회 속도를 감수하는 것도 하나의 방법이 될 수 있습니다.
