[혼공학습단 13기] 활동의 일환으로 한빛미디어의 < 혼자 공부하는 SQL >을 읽고 요약 정리한 글입니다.Chap 06. 인덱스
1. 인덱스 개념을 파악하자
인덱스의 개념
인덱스란 데이터를 빠르게 찾을 수 있도록 도와주는 도구
- 인덱스의 장점
- SELECT 문으로 검색하는 속도가 매우 빨라진다.
- 전체 시스템의 성능이 향상된다.
- 인덱스의 단점
- 데이터베이스 안에 추가적인 공간이 필요하다.
- 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
- SELECT가 아닌 데이터 변경 작업이 자주 일어나면 오히려 성능이 나빠질 수도 있다.
인덱스의 종류
자동으로 생성되는 인덱스
- 클러스터형 인덱스는 기본 키로 지정하면 자동으로 생성되고 테이블당 1개만 만들 수 있다.
- 인덱스 정보 확인 시, Key_name에 PRIMARY라고 써 있다면 클러스터형 인덱스.
- Non_unique : 1이면 중복 허용, 0이면 중복 불가
- 보조 인덱스는 고유 키로 지정하면 자동으로 생성되며, 테이블당 여러 개 만들 수 있다.
자동으로 정렬되는 클러스터형 인덱스
- 클러스터형 인덱스가 생성된 열로 데이터가 자동 정렬된다.
- 기본 키를 변경하면 해당 열을 기준으로 다시 정렬된다.
- 추가로 데이터를 입력하면 알아서 기준에 맞게 정렬된다.
정렬되지 않는 보조 인덱스
- 보조 인덱스를 생성해도 데이터의 내용이나 순서는 변경되지 않는다.
※ 고유 인덱스
👉 인덱스의 값이 중복되지 않음. 기본 키나 고유 키로 지정하면 생성된다.
2. 인덱스의 내부 작동
인덱스의 내부 작동 원리
인덱스는 균형 트리의 자료 구조로 구성
균형 트리의 개념
균형트리란? 범용적으로 사용되는 데이터 구조로, 나무를 거꾸로 표현.
노드: 균형 트리 구조에서 데이터가 저장되는 공간- 루프 노드 : 가장 상위 노드
- 리프 노드 : 제일 마지막에 존재하는 노드
- 중간 노드 : 루트 노드와 리프 노드 중간에 끼인 노드
MySQL에서는 페이지라고 부른다.
✅균형 트리로 구성하지 않으면(인덱스가 없으면) 전체 페이지를 검색해야 한다. (전체 테이블 검색. Full Table Scan)
✅균형 트리로 구성하면(인덱스가 있으면) 데이터를 빠르게 검색할 수 있다.
균형 트리의 페이지 분할
페이지 분할 : 새로운 페이지를 준비해서 데이터를 나누는 작업
이로 인해 데이터 변경 작업시 성능에 영향을 준다.
인덱스의 구조
클러스터형 인덱스 구성하기
클러스터형 인덱스를 구성하면 데이터 페이지도 인덱스에 포함된다.
보조 인덱스 구성하기
보조 인덱스를 구성하면 인덱스가 별도의 공간에 만들어진다.
(데이터의 위치는 '페이지 번호 +# 위치'로 기록된다.)
인덱스에서 데이터 검색하기
인덱스 검색을 통해 인덱스를 사용하지 않았을 때보다 빠르게 검색할 수 있다.
(클러스터형 인덱스가 조금 더 빠름)
3. 인덱스의 실제 사용
인덱스 생성과 제거 문법
인덱스 생성 문법
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ ASC | DESC ]- UNIQUE는 중복이 안되는 고유 인덱스를 만드는 것으로, 생략 시 중복 허용.
- ASC / DESC는 각각 인덱스를 오름차순/내림차순으로 만든다.
인덱스 제거 문법
DROP INDEX 인덱스_이름 ON 테이블_이름- 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
[추가 숙제]
인덱스 생성, 제거하는 기본 형식 작성하기
인덱스 생성과 제거 실습
[기본 숙제]
p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기
- SHOW INDEX는 테이블에 생성된 인덱스 정보를 보여준다.
- SHOW TABLE STATUS는 테이블에 생성된 인덱스의 크기를 확인할 수 있다.
단순 보조 인덱스: 중복을 허용하는 보조 인덱스. CREATE INDEX 문 사용.고유 보조 인덱스: 중복을 허용하지 않는 보조 인덱스. CREATE UNIQUE INDEX 문 사용.
- 인덱스를 생성한 후에 ANALYZE TABLE 문을 실행해줘야 실제로 적용된다.
- 고유 인덱스를 생성하면 이후로는 중복된 값이 입력되지 않는다.
- 실행 계획(Execution Plan)에서 인덱스의 사용 여부를 확인할 수 있다.
- 실행 계획 창에 Full Table Scan을 제외한 나머지는 모두 인덱스를 사용했다는 의미이다.
- WHERE 절에 열 이름이 들어있어야 인덱스를 사용한다.
- 인덱스가 있어도 MySQL이 판단해서 사용하지 않을 수 있다.
- WHERE 절의 열에 연산을 하면 인덱스를 사용하지 않는다.
- 클러스터형 인덱스와 보조 인덱스가 섞여 있을 때에는 보조 인덱스를 먼저 제거하는 것이 좋다.
- Primary Key에 설정된 인덱스는 DROP INDEX 문으로 제거되지 않고, ALTER TABLE 문으로만 제거할 수 있다.
- 기본 키를 제거하려면 관련된 외래 키를 먼저 제거해야 한다.
※ 인덱스를 효과적으로 사용하는 방법
1️⃣ 인덱스는 열 단위에 생성된다.
2️⃣ WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
3️⃣ WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
4️⃣ 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
5️⃣ 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
6️⃣ 사용하지 않는 인덱스는 제거한다.
