[SQL] 인덱스
인덱스 기본 :
인덱스의 종류, 내부작동, 문법
1. 인덱스란?
인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구다.
1.1. 인덱스 장단점
(1) 인덱스 장점
- 데이터 요청(SELECT) 시 기존보다 빠른 응답 속도 → 컴퓨터 부담 감소 → 전체 시스템 성능 향상
(2) 인덱스의 단점
- 인덱스가 공간 차지 (테이블 크기의 10%)
- 필요 없는 인덱스를 만드는 경우 오히려 공간만 차지
- MySQL이 인덱스를 사용해서 검색할 지 전체 테이블 검색할 지 알아서 판단
- MySQL이 검색 시 인덱스를 사용하지 않는다면, 비효율적인 인덱스
- 처음에 인덱스 생성 시 시간 소요
- 데이터 변경(INSERT, UPDATE, DELETE) 시 오래 걸릴 수 있음
- 데이터 요청보다 변경 작업이 많은 경우 오히려 성능 나빠질 수 있음
1.2. 인덱스 종류
| 구분 | 클러스터형 인덱스 | 보조 인덱스 |
|---|---|---|
| 생성 개수 | 테이블 1개에 1개만 생성 | 테이블 1개에 여러 개 생성 |
| 데이터 순서 | 데이터 삽입 시 기본 키로 지정한 열 기준으로 자동 정렬 | 데이터 삽입해도 데이터 순서 변경 X (별도 공간 차지) |
| 생성 방법 | PRIMARY KEY 제약조건으로 기본 키 지정 |
UNIQUE 제약조건으로 고유 키 지정 CREATE INDEX문으로 고유/단순 인덱스 생성 |
| 검색순서 | 루트 → (중간) → 데이터 | 루트 → (중간) → 리프 → 데이터 |
| 비유 | 사전 그 자체 | 책 뒤에 있는 찾아보기 |
2. 내부 작동
인덱스의 내부 작동 원리를 이해해야 인덱스가 필요한 경우와 불필요한 경우를 선택할 수 있다.
2.1. 기본 개념
(1) 페이지
- MySQL에서 최소한의 저장 단위로 16KB(16384byte) 크기
- 데이터를 1건만 입력해도 1개의 페이지가 필요
- DB의 조회 효율성 = 몇 개의 페이지를 읽었는가?
- 페이지 종류
- 데이터 페이지 : 실제 데이터들이 저장된 페이지
- 인덱스 페이지 : 인덱스 생성 시 생기는 페이지
- 루트 페이지 / 중간 페이지 / 리프 페이지
(2) 균형 트리
- 균형 트리(B-tree) : 자료 구조에 나오는 범용적으로 사용되는 데이터 구조
- 클러스터형 인덱스와 보조 인덱스 모두 내부적으로 균형 트리 형태로 생성됨
- 루트 페이지, 중간 페이지, 리프 페이지 생성
- 루트 페이지는 1개만 존재하며, 검색 시 무조건 루트에서 출발
2.2. 클러스터형 인덱스
(1) 생성
- 데이터 페이지 : 기본 키 기준으로 오름차순 정렬
- 인덱스 페이지
- 리프 페이지 : 데이터 페이지 그 자체
- 루트 페이지 : 각 데이터 페이지마다 인덱스로 지정된 열의 첫 번째 값을 가지고 와서 생성 → 페이지번호 기록
(2) 검색
2.3. 보조 인덱스
(1) 생성
- 데이터 페이지 : 변화 없음
- 인덱스 페이지
- 리프 페이지 : 인덱스로 구성한 열을 정렬해서 생성 → 실제 데이터 위치 기록 (
페이지번호+#위치) - 루트 페이지 : 각 리프 페이지마다 인덱스로 지정된 열의 첫 번째 값을 가지고 와서 생성 → 페이지번호 기록
- 리프 페이지 : 인덱스로 구성한 열을 정렬해서 생성 → 실제 데이터 위치 기록 (
(2) 검색
- 클러스터형 인덱스가 조금 더 빠름
- 클러스터형 인덱스는 3 페이지를 읽고 찾았고, 보조 인덱스는 2 페이지를 읽고 찾았음
2.4. 인덱스와 데이터 변경
균형 트리는 데이터 검색에 효율적이지만, 변경 작업에는 성능이 나빠진다. 변경 작업 시 페이지 분할 작업이 발생하기 때문이다.
(1) 페이지 분할이란
- 페이지 분할 : 새로운 페이지를 준비해서 데이터를 나누는 작업
- 발생 시 MySQL이 느려지고, 성능에 영향을 줌
- 데이터 INSERT 시 인덱스 페이지가 추가될 수 있음
(2) 예시 : QQQ 데이터 INSERT
- QQQ 데이터 하나 삽입 시 3개의 새로운 페이지 할당 & 2개 페이지 분할
- 리프 페이지 : QQQ를 넣을 새로운 페이지 준비 → PPP, QQQ를 새로운 페이지로 분할(+)
- 중간 페이지 : 루트 페이지에 MMM 넣을 자리 없음 → 루트 페이지 2개로 분할(+)
- 루트 페이지 : 기존 루트 페이지가 중간 페이지 됨 → 새로운 루트 페이지 구성(+)
- 즉, 인덱스를 구성하면 데이터 하나 입력해도 많은 작업이 일어날 수 있고, 특히 INSERT 작업이 느려짐
3. 인덱스 문법
인덱스는 클러스터형 인덱스와 보조 인덱스(고유 보조, 단순 보조)가 있다. 인덱스는 키 지정으로 자동 생성하는 방법과 CREATE INDEX문을 통해 생성하는 방법 2가지가 있다.
3.1. 제약조건
(1) 새로운 테이블에 생성
- 클러스터형 인덱스는 기본 키 제약조건으로 자동 생성
- 고유 보조 인덱스는 고유 키 제약조건으로 자동 생성
- 기본 키와 고유 키는 중복 허용하지 않음 (Non_unique = 0)
USE test_db;
CREATE TABLE test_tb (
-- 기본 키 지정 = 클러스터형 인덱스 생성
col1 INT PRIMARY_KEY,
-- 고유 키 지정 = 보조 인덱스 생성
col2 INT UNIQUE,
col3 INT UNIQUE
);
-- 테이블 인덱스 확인
SHOW INDEX FROM test_tb;
(2) 기존 테이블에 생성
- 대용량 테이블에서 기본 키 변경하는 경우 정렬하는 데 시간 오래 걸림
-- 기본 키 제거
ALTER TABLE test_tb DROP PRIMARY KEY;
-- 기존 테이블에 기본 키 지정
ALTER TABLE test_tb
ADD CONSTRAINT
PRIMARY KEY(col1);
-- 기존 테이블에 고유 키 지정
ALTER TABLE test_tb
ADD CONSTRAINT
UNIQUE (col2);
(3) 인덱스 제거
- 보조 인덱스 제거 → (외래 키 관계 제거) → 기본 키 제거
- 여러 인덱스를 제거해야 할 때, 보조 인덱스 먼저 제거하는 것이 좋음
- 클러스터형 인덱스 먼저 제거 시 데이터 재구성으로 시간이 더 오래 걸림
- 기본 키를 다른 테이블이 참조하고 있으면 제거 불가
- table_name(참조한 테이블 명), constraint_name(외래 키 명) 조회해서 제거
-- 고유 키 제거
ALTER TABLE test_tb
DROP INDEX col2;
-- 외래 키 이름 파악(=constraint_name)
SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'test_db';
-- 외래 키 제거
ALTER TABLE 참조한_테이블명
DROP FOREIGN KEY 외래키명;
-- 기본 키 제거
ALTER TABLE test_tb
DROP PRIMARY KEY;
3.2. CREATE INDEX문
(1) 인덱스 생성
- 고유 인덱스 생성 시,
- 기존에 중복 값이 있으면 안 됨
- 새로 입력하는 데이터도 중복될 수 없음
- 절대 중복될 수 없는 열에 인덱스 생성
- 단순 인덱스 생성 시,
- 중복 데이터가 너무 많은데 인덱스 생성하면 성능 오히려 악화됨
-- 고유 인덱스 생성
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC];
-- 단순 인덱스 생성
CREATE INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC];
-- 생성한 인덱스 적용을 위해 테이블 처리
ANALYZE TABLE;
(2) 인덱스 제거
- 자동 생성한 인덱스는
ALTER TABLE로 키를 제거해야 제거됨
-- 인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름;
3.3. 기타
(1) 인덱스 정보 조회
- 테이블 인덱스 정보 조회
- Key_name : PRIMARY면 클러스터형 인덱스, 나머지는 보조 인덱스(열 이름/인덱스 이름)
- Column_name : 인덱스로 지정한 열 이름
- Non_unique : 중복 허용 여부
ANALYZE TABLE;
SHOW INDEX FROM 테이블명;
- 테이블 인덱스 크기 조회
- Data_length : 클러스터형 인덱스(데이터) 크기 Byte 단위로 표기 (최소 16KB 할당)
- Index_length : 보조 인덱스 크기 (없으면 0Byte, 1개라도 있으면 16KB~ 할당)
ANALYZE TABLE;
SHOW TABLE STATUS LIKE '테이블명';
(2) 인덱스 활용 쿼리
- Full Table Scan
- 검색할 때 인덱스를 활용하지 않고 전체 테이블 검색하는 경우
- 아래 조건을 만족하지 않는 경우에는 Full Table Scan 발생
- MySQL의 Execution Plan에서 확인 가능
- 인덱스 사용 조건
- [쿼리] WHERE 절에 인덱스 열이 들어가야 한다.
- [쿼리] WHERE 절에 인덱스 열이 들어갔다고 해도, 해당 열에 연산이 들어가면 안된다.
- [MySQL] 대부분의 데이터를 가져와야 하는 경우면 안 된다.
- [MySQL] 인덱스 검색이 Full Table Scan보다 효율적이어야 한다.
-- Full Table Scan
SELECT id, name
FROM member
WHERE id * 2 > 14;
-- Index Range Scan
SELECT id, name
FROM member
WHERE id >= 14/2;
4. 인덱스 꿀팁
-
하나의 열에 하나의 인덱스를 사용하는 것이 가장 일반적이다.
-
조회가 자주 일어나면서 WHERE절에 사용되는 열에 인덱스를 만들어야 한다.
- 가장 조회가 많이 일어나는 열에 클러스터형 인덱스를 지정한다.
- INSERT가 SELECT 보다 훨씬 자주 발생하면 인덱스가 없는 것이 나을 수도 있다.
-
데이터의 중복이 높은 열(성별, 교통 수단 등)은 인덱스가 소용 없다.
-
사용하지 않는 인덱스는 제거한다.
- SQL 분석을 통해 사용되지 않는 열의 인덱스는 반드시 제거한다.
- MySQL의 Execution Plan을 통해 쿼리 실행 시 인덱스 사용 여부를 확인 가능
- 공간 확보 + INSERT 입력 부하 감소의 효과가 있다.
REFERENCES
- [서적] 혼자 공부하는 SQL (한빛미디어, 2021)
