혼자 공부하는 SQL - 5주차
- 5주차의 미션은 다음과 같습니다.
- 이번 장에서는 인덱스에 대해 알아보겠습니다.
6.1 인덱스의 개념
- 인덱스는 사전에서 '찾아보기'와 같은 개념으로 데이터를 빠르게 찾을 수 있도록 해주는 도구입니다. 대용량일 경우 더더욱 성능 차이가 날 수 있습니다.
- 인덱스의 장점은 다음과 같습니다.
- select 속도가 매우 빨리짐.- 그 결과 시스템 성능 향상됨.
- 인덱스의 단점은 다음과 같습니다.
- 추가 공간 필요.- 처음 생성시 시간 오래 걸림.
- select 가 아닌 작업이 자주 일어나면 오히려 성능이 나빠짐.
- 인덱스의 종류는 클러스터형 인덱스와 보조 인덱스가 있습니다.
- 인덱스는 테이블의 열 단위에 생성되며 pk 에는 자동으로 인덱스가 생성되는데 이것이 클러스터형 인덱스입니다. 클러스터형은 자동으로 정렬되어 저장됩니다.
- 고유 키로 지정하면 보조 인덱스가 생성되며, 여러 개 생성 가능합니다. 또한 정렬이 되지 않습니다.
6.2 인덱스의 내부 작동
- 인덱스는 모두 내부적으로 균형 트리(B-tree)로 만들어집니다. 균형 트리는 select에서 뛰어난 성능을 발휘하는데, 루트 페이지부터 검색해 페이지 수가 감소됨으로써 성능이 향상됩니다.
- 데이터 변경 작업시 성능이 나빠지는데, 이유는 페이지 분할이라는 작업이 발생하기 때문입니다. 새로운 페이지를 준비해서 나누는 작업으로 속도가 느려지고, 자주 일어나면 성능에 큰 영향을 줍니다.
- 클러스터형 인덱스를 구성하면 데이터 페이지도 인덱스에 포함되는데, 사전의 찾아보기와 같은 개념입니다.
- 보조 인덱스는 일반적인 책과 같이 데이터 페이지는 변경되지 않고, 별도의 찾아보기와 같은 개념입니다.
- 클러스터형 인덱스에서 검색해보면, 가장 먼저 루트 페이지를 읽고, 중간 또는 리프 페이지 주소로 이동하여 검색합니다.
- 보조 인덱스에서 검색하면, 인덱스 페이지의 루트 페이지, 중간 또는 리프 페이지를 읽고 데이터 페이지를 읽어서 검색합니다.
6.3 인덱스의 실제 사용
-- 생성
create [unique] index 인덱스_이름
on 테이블_이름 (열_이름) [asc | desc];
-- 삭제
drop index 인덱스_이름 on 테이블_이름;- 인덱스가 있고 where 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 존재하는데, 대부분의 행을 가져와야 할 경우 차라리 테이블을 차례대로 읽는 것이 효율적이라고 생각될 때 인덱스를 사용하지 않습니다. 또한, where 절에서 열에 연산이 가해지면 인덱스를 사용하지 않습니다.
인덱스를 효과적으로 사용하는 방법
- 인덱스는 열 단위에 생성됩니다. 하나의 열에 하나의 인덱스를 만드는 것이 일반적입니다.
- where 절에서 사용되는 열에 인덱스를 만들어야 합니다.
- where 절에 사용되더라도 자주 사용해야 가치가 있습니다.
- 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없습니다.
- 클러스터형 인덱스는 테이블당 하나만 생성 가능합니다.
- 사용하지 않는 인덱스는 저장 공간만 차지하기 때문에 제거합니다.
기본 미션
- 먼저 member 테이블의 인덱스를 살펴보면, key_name이 PRIMARY로 나오는 것을 확인할 수 있습니다. mem_id 가 PRIMARY KEY로 Column_name에서 확인할 수 있습니다.
- show table status 문을 통해 인덱스의 크기를 살펴보면, Data_length 열에서 16384, 즉 16KB임을 확인할 수 있습니다. 16KB까지 필요는 없지만 1페이지가 최소 단위로 1페이지가 할당된 것을 확인할 수 있습니다.
- 다음으로, 주소(addr)에 중복 허용 단순 보조 인덱스를 생성하고 확인해 보았습니다. Non_unique가 1로 설정되어 고유 보조 인덱스가 아니라는 점을 확인할 수 있습니다.
- 하지만 index_length가 0으로 된 것을 확인할 수 있는데, 이는 생성한 인덱스를 적용시키지 않아서 그렇습니다. analyze table 문으로 테이블을 분석/처리해야 합니다.
- analyze문을 실행한 후에는 index_length가 16384B임을 확인할 수 있습니다.
- 인원수(mem_number)는 중복이 이미 존재해 고유 보조 인덱스를 생성할 수 없어 오류가 발생합니다.
- 회원 이름(mem_name)은 중복허용하지 않기 위해 고유 보조 인덱스를 생성하고, Non_unique가 0임을 확인할 수 있습니다.
- 이미 존재하는 '마마무'의 이름으로 회원가입은 실패합니다.
선택 미션
인덱스 생성 및 제거 기본 형식
-- 생성
create [unique] index 인덱스_이름
on 테이블_이름 (열_이름) [asc | desc];
-- 삭제
drop index 인덱스_이름 on 테이블_이름;
backend studying