index?
책의 뒷부분을 보면 찾아보기가 있다. 단어와 쪽 수가 있는데 원하는 단어가 어디에 있는지 빠르게 찾을 수 있도록 도와준다.
DB의 index도 같은 역할을 한다.
처음부터 끝까지 찾을 경우 최악의 경우에 마지막까지 뒤져야 찾을 수 있다.
장점
- 검색 속도를 향상 시킨다. 그러나 항상 그런건 아닐 수 있다.
단점
- DB 크기의 약 10% 정도되는 추가 공간이 필요하다.
- 인덱스 생성 시 시간이 걸린다.
- insert, update, delete같은 데이터 변경 쿼리가 잦은 경우 paging이 빈번해져 성능이 악화될 수 있다.
index 종류와 자동 생성
인덱스 종류
클러스터형 인덱스 clustered index
- 순서대로 정렬되어 있는 영어 사전과 유사함
- 테이블당 1개만 생성 가능
- 인덱스로 지정된 열에 맞춰 자동 정렬함
- 예 : id에 pk를 걸면 id가 오름차순으로 정렬됨
보조 인덱스 secondary index
- 책 뒤에 찾아보기가 있는 것과 유사함
- 테이블당 여러 개 생성 가능
DB 튜닝?
1. 응답 속도 향상 : 서버 작업량이 늘어날 수 있지만 속도를 향상시킴
2. 서버 부하 감소 : 응답 속도보다 서버 작업량을 줄임
자동 생성되는 인덱스
테이블의 특정 열에 primary key를 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성된다.
PK, Unique 제약조건을 사용하면 자동으로 인덱스가 생성된다.
unique 제약 조건을 설정하면 보조 인덱스가 생성된다.
unique + not null 제약 조건이 설정되면 클러스터형 인덱스가 생성된다.
a 컬럼에 PK, b 컬럼에 unique + not null 제약 조건을 설정하면 a 컬럼에 클러스터형 인덱스가 생성되고 b 컬럼에는 보조 인덱스가 설정된다.
FK를 설정하면 외래키 인덱스가 자동 설정된다.
row가 매우 많은 상태에서 PK, unique + not null로 클러스터형 인덱스를 생성하려고 하면 오름차순(ASC) 정렬하기 때문에 서버 부하가 심해진다.
인덱스의 내부 작동
B-Tree(Balanced Tree, 균형 트리)
루트 노드(페이지) : 최상위 노드(페이지)
리프 노드(페이지) : 최하단에 있는 노드(페이지)
MySQL이 B-Tree를 사용할 때 노드에 해당하는 것이 페이지다
페이지 : 16KB 크기의 최소한의 저장 단위
인덱스를 구현할 때 B-Tree 구조를 사용한다.
B-Tree 구조는 select 시 성능이 좋다.
처음부터 끝까지 찾는 full table search(scan)보다 찾고자 하는 값을 페이지 이동하면서 빠르게 접근하기 때문에 성능이 좋다.
페이지 분할
select 시에는 성능이 좋아질 수 있으나, insert 시에는 페이지 분할이 발생해 성능이 느려진다. insert 하려는 값이 어디에 위치해야할지 찾아야 하고 페이지 내에서의 정렬이나 페이지 내 공간이 없으면 페이지 분할이 발생하기 때문이다.
select 시에는 성능이 좋아질 수 있음
insert, update, delete 시에는 성능이 안좋아짐
클러스터형 인덱스와 보조 인덱스의 구조
클러스터형 인덱스
클러스터형 인덱스를 구성하기 위해 row를 정렬한 뒤 루트 페이지를 만든다.
리프 레벨이 데이터 페이지다.
인덱스 페이지(루트 페이지 + 리프 페이지)
보조 인덱스
인덱스를 설정해도 row를 정렬하지 않는다.
데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성한다.
인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬한다. 리프 페이지에 데이터 위치 포인터를 생성한다.
데이터 위치 포인터 : 주소값(페이지 번호 + #오프셋). 데이터가 위치한 고유값
데이터 페이지를 정렬하지 않고 데이터 페이지 뒤에 삽입한다. 인덱스의 리프 페이지도 분할되지 않는다. insert 시 성능 저하가 덜하다.
인덱스 페이지(루트 페이지 + 리프 페이지) + 데이터 페이지(Heap 영역)
검색 시 클러스터형 인덱스가 보조 인덱스보다 빠르다.
데이터 찾는 순서
클러스터형 인덱스
루트 페이지에서 데이터 페이지로 이동하고 그 안에서 데이터를 찾음
2번만에 찾음
보조 인덱스
루트 페이지에서 리프 페이지로 이동하고 거기서 데이터 페이지 번호와 오프셋을 통해 데이터 페이지에서 데이터를 찾음
3번만에 찾음
범위로 찾는 경우
- 클러스터형 인덱스는 정렬된 상태라 빠름.
- 정렬되지 않아서 데이터 페이지에서 필요한 데이터를 하나하나 찾아야 하기 때문에 덜 빠름.
클러스터형 인덱스와 보조 인덱스를 함께 사용하는 경우
구성
id가 PK이고 name이 unique라고 가정.
보조 인덱스(루트 페이지, 리프 페이지(포인터가 아닌 클러스터형 인덱스 값)) + 클러스터형 인덱스(루트 페이지 + 데이터 페이지)
보조 인덱스 열을 찾기 위해 보조 인덱스를 먼저 타게 된다. 루트 페이지 -> 리프 페이지 순서로 검색한다. 보조 인덱스에서 값을 찾으면 보조 인덱스의 리프 페이지에서는 클러스터형 인덱스의 값을 가지고 클러스터형 인덱스를 루트 페이지 -> 데이터 페이지 순서로 검색한다.
원래 보조 인덱스의 리프 페이지에는 데이터 포인터가 있지만 클러스터형 인덱스와 보조 인덱스가 혼합된 경우 클러스터형 인덱스 값이 저장된다.
이유
- 위와 같은 상황이면 데이터가 insert되면 클러스터형 인덱스에서 페이지 분할이 발생하게 된다.
- 하지만 보조 인덱스의 리프 페이지에 데이터 포인터가 들어가게 되면 클러스터형 인덱스의 페이지 분할 및 정렬을 하면서 주소가 바뀌게 되고 + 보조 인덱스의 리프 페이지에 있는 주소까지 싹 바꿔야 한다.
- 결국 부하를 클러스터형 인덱스에만 발생시킬 것인지 아니면 클러스터형 + 보조 인덱스 두 곳에 발생시킬지 중 전자를 택한 것이다.
- 검색할 때 보조 인덱스 -> 클러스터형 인덱스를 타서 성능상 약간의 손해가 있긴 하지만 데이터 재구성보다 낫다.
인덱스를 생성해야 하는 경우, 그렇지 않은 경우
- select가 빈번하고 insert, update, delete가 적은 경우, where 절에 사용되는 열
- join에 자주 사용되는 열
- order by 절에 사용되는 열은 클러스터형 인덱스가 유리하다. 데이터 페이지가 자동 정렬됐기 때문에.
- 대용량 데이터가 자주 입력되는 경우 클러스터형 인덱스의 경우 빈번한 페이징이 일어나기 때문에 부하가 생긴다. unique만 설정하는 게 좋을 수 있다.
- 데이터 중복도가 높은 열은 효과가 없다. 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫다
optimize table, analyze table 구문으로 인덱스를 재구성해서 조각화를 최소화하면 시스템 성능을 좋게 유지할 수 있다.
출처 이것이 MySQL이다
