8. 인덱스

인덱스

디스크 읽기 방식

•

성능 튜닝은 어떻게 디스크 IO 를 줄이느냐가 관건

HDD, SSD

•

DB 서버에서는 항상 디스크 장치가 병목

•

HDD 는 기계식 저장 매체

•

SSD 는 전자식 저장 매체

◦

기존 HDD 에서 데이터 저장용 플래터(원판) 을 제거하고 대신 플래시 메모리 장착

◦

그래서 기계적으로 회전시킬 필요가 없어서 빨리 입출력 가능

◦

플래시 메모리는 전원 공급 없어도 데이터 삭제 X

•

초당 처리 횟수

◦

CPU 10억

◦

DRAM 1억

◦

SSD 10만

◦

HDD 200

•

순차 IO 에서는 큰 차이가 없으나 랜덤 IO 에서 큰 차이

•

DB 에서는 랜덤 IO 작업 비중이 대부분

랜덤 IO, 순차 IO

•

디스크에 기록해야 할 위치를 찾기 위해 랜덤 IO 는 N 번, 순차 IO 는 1번

•

MySQL 서버는 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 기능 내장

•

쿼리 튜닝은 랜덤 IO 를 줄이는 것이 목적

•

랜덤 IO 를 줄인다는 것은 쿼리를 처리하는 데에 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것

•

인덱스 레인지 스캔은 랜덤 IO, 풀 테이블 스캔은 순차 IO

B-Tree

•

Balanced Tree

•

컬럼의 원래 값을 변형시키지 않고 인덱싱

•

인덱스 구조체 내에서 항상 정렬된 상태로 유지

•

트리 구조의 최상위에 하나의 루트 노드가 존재, 그 하위에 자식 노드가 붙어 있는 형태

•

루트는 최상위, 리프는 최하위, 나머지는 브랜치 노드

•

인덱스와 실제 데이터가 저장된 데이터는 따로 관리됨

•

인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값

•

InnoDB 에서는 프라이머리 키가 아닌 인덱스로 검색 시 인덱스 -> PK 인덱스 -> 데이터 레코드 순으로 검색된다.

인덱스 레인지 스캔

•

검색해야 할 인덱스의 범위가 있을 때 사용하는 방식

인덱스 풀 스캔

클러스터링 인덱스

•

클러스터링 인덱스는 InnoDB 에서만 지원된다.

•

프라이머리 키에만 적용됨

•

PK 값이 비슷한 레코드끼리 묶어서 저장

•

PK 값에 의해 레코드의 저장 위치가 결정

•

PK 값이 변경되면 레코드의 물리적 저장 위치까지 바뀌어야 함

•

구조 자체는 일반 B Tree 와 비슷

•

다른 점은 리프 노드에 레코드의 모든 칼럼이 같이 저장되어 있음

•

PK 가 없을 경우

not null 옵션의 unique index 중 첫 번째 인덱스를 클러스터링 키로 선택

1번도 없으면 자동으로 유니크 값을 가지도록 증가되는 컬럼을 내부적으로 추가 후 클러스터링 키로 선택

◦

사용자에게 노출 X

◦

쿼리 문장에 명시적으로 사용 불가

◦

혜택 X

◦

가능하다면 PK 를 명시적으로 생성하자

세컨더리 인덱스

•

InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 PK 값을 저장하도록 구현됨

•

예시)

◦

auto_increment PK가 있고 name 컬럼에 index 가 적용된 경우

◦

select * from member where name='eden' 이면 name 인덱스를 검색해서 레코드의 PK 값을 확인하고 PK 인덱스를 검색해서 최종 레코드 가져옴

클러스터링 인덱스 주의 사항

•

PK 키의 크기

◦

모든 세컨더리 인덱스가 PK 를 포함하기때문

•

가능하다면 auto-increment 보다는 업무적인 컬럼으로 생성

유니크 인덱스

•

유니크 인덱스와 세컨더리 인덱스는 구조적으로 차이 X

•

하나의 값을 검색하는 경우, 유니크 인덱스와 세컨더리 인덱스에서 사용되는 실행 계획은 다른데 크게 차이는 없다.

•

새로운 레코드가 insert 되거나 인덱스 컬럼의 값이 변경되는 경우 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요해서 느리다.

•

유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금, 쓰기 할 때는 쓰기 잠금하는 이 과정에서 데드락이 자주 발생

외래키

•

외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스 생성

•

외래키 제약이 제거되지 않으면 자동으로 생성된 인덱스 삭제 불가

대기 상태에 빠지는 경우

•

team

id	name
1	morak
2	sokdak

•

member

id	team_id	name
1	1	eden

# session 1
start transaction;
update team set name='mo-rak' where id=1;
# id = 1 인 레코드에 쓰기 락 

# session 2
start transaction;
update member set team_id=1 where id=1;
# 대기중
# 외래키 제약 조건때문에 해당 team_id 가 유효한지 확인해야하기 때문에 읽으러 감. 그래서 대기중

update member set team_id=2 where id=1;
# query OK
# team_id = 1 레코드에만 걸려있기때문
SQL
복사