[우아한테크코스] 인덱스 (Indexes)

실습환경 세팅

• Workbench 설치
• 도커 이미지 다운로드

1

$ docker run -d -p 33306:3306 brainbackdoor/data-subway:0.0.2

• Workbench에서 localhost:33306 (ID: root, PW: masterpw)로 접속

인덱스 생성 및 삭제

인덱스 생성

1

CREATE INDEX `idx_programmer_id` ON `subway`.`programmer` (id);

인덱스 삭제

1

DROP INDEX `idx_programmer_id` ON `subway`.`programmer`;

인덱스가 되는 PK 외의 컬럼만을 WHERE 조건으로 지정하는 검색

• 인덱스를 사용 X
• Full Table Scan을 함

Index Range Scan

인덱스 생성

1

CREATE INDEX `idx_programmer_country_open_source` ON `subway`.`programmer` (country, open_source);

실행계획 확인

• Index Range Scan을 위해서는 인덱스 선두 컬럼이 WHERE에 있어야 함
• e.g. (country, open_source)에서 country가 WHERE에 있어야 함

예시

(country, open_source)에서 country가 WHERE에 없는 경우

• Table Full Scan을 함

1

EXPLAIN SELECT * FROM programmer WHERE open_source = 'Yes';

(country, open_source)에서 country가 WHERE에 있는 경우

• Index Range Scan을 함

1

EXPLAIN SELECT * FROM programmer WHERE open_source = 'Yes' AND country LIKE 'Nigeria';

Index Full Scan

실행계획 확인

• Leaf 블록 전체를 스캔하는 경우

예시

• Index Full Scan을 함

1

EXPLAIN SELECT COUNT(*) FROM programmer;

Index Unique Scan

인덱스 생성

1

CREATE INDEX `idx_programmer_member_id` ON `subway`.`programmer` (member_id);

실행계획 확인

• 인덱스가 존재하는 컬럼에 중복값이 없는 경우

예시

= 조건으로 검색하는 경우

• 데이터를 1건 찾는 순간 더 이상 탐색할 필요 X

1

EXPLAIN SELECT * FROM programmer WHERE member_id = 10;

<, >, <=, >= 조건으로 검색하는 경우

• 범위 검색
• 수직적 검색만으로는 모두 찾을 수 X
• Index Range Scan을 함

1

EXPLAIN SELECT * FROM programmer WHERE member_id < 10;

테이블 액세스 최소화

인덱스 컬럼 추가

• 가장 일반적으로 사용하는 튜닝 기법
• 인덱스는 정렬되므로, WHERE에 해당하는 PK 범위를 줄여 랜덤 액세스 횟수를 줄임

예시

• 쿼리 결과에 해당하는 데이터는 BLAKE 1명인데, 이를 찾기 위해 테이블에 6번 액세스함
• 인덱스 구성을 (deptno, sal)로 변경하면 해결되는 문제지만, 실제 운영 환경에서 인덱스 구성을 변경하는 건 쉽지 X
• 따라서, 기존 인덱스에 (sal)을 추가하여 테이블 랜덤 액세스 횟수를 줄일 수 있음 (인덱스 스캔량을 줄이는 건 X)

1

SELECT * FROM emp WHERE deptno = 30 AND sal >= 2000

Reference

• 우아한테크코스 강의 자료