[혼자 공부하는 SQL] 6장. 인덱스, 복합 인덱스

인덱스

데이터를 빠르게 찾을 수 있도록 도와주는 도구, 책의 찾아보기와 비슷

 

인덱스의 문제점

필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간이 더 늘어나고, 인덱스를 사용해 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려짐

💡 데이터베이스에 인덱스를 생성해도, 인덱스를 사용해서 검색하는 것이 더 빠를지, 아니면 전체 테이블을 검색하는 것이 더 빠를지는 MySQL이 알아서 판단한다. 만약 인덱스를 사용하지 않는다면, 사용하지도 않는 찾아보기를 만든 것이므로 공간을 낭비한 셈이다.

 

인덱스의 장점과 단점

• 장점
  • 적절한 인덱스를 생성하면, 빠른 응답 속도를 얻을 수 있다.
  • 적은 처리량으로 요청한 결과를 빨리 얻을 수 있으므로, 시스템 성능이 향상된다.
• 단점
  • 인덱스도 공간을 차지하므로 데이터베이스 안에 추가적인 공간이 필요하다.
  • 인덱스를 만드는데에도 생성비용이 발생한다.

 

인덱스의 종류

	클러스터형 인덱스 (clustered index)	보조 인덱스 (secondary index)
특징	- 기본 키로 지정하면 자동생성 - 테이블에 1개만 가능	- 고유 키로 지정하면 자동 생성되며 여러 개를 만들 수 있음 - 인덱스가 별도의 공간에 만들어짐
데이터 정렬	자동정렬	자동정렬 x
비유	국어사전	찾아보기가 있는 일반 책
예시	기본 키	고유 키
검색	루트 페이지 → 중간 또는 리프 페이지를 읽어서 검색	인덱스 페이지의 루트페이지 → 중간 또는 리프 페이지를 읽고 데이터 페이지를 읽어서 검색
속도	클러스터형 인덱스가 조금 더 빠름	보조 인덱스가 조금 더 느림

고유 인덱스 (unique index) : 값이 중복되지 않는 인덱스, 기본 키나 고유 키로 지정하면 값이 중복되지 않아서 고유 인덱스가 자동 생성된다.

고유 키(unique key) : 테이블에서 특정 필드의 값을 만들기 위해 사용, NULL 값 포함 가능

 

인덱스의 내부 작동

클러스형 인덱스와 보조 인덱스는 균형 트리(balanced tree, B-tree)로 만들어진다.

균형 트리(balanced tree, B-tree) : 트리 자료구조의 일종, 이진 트리를 확장해 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리 구조이다. 나무를 거꾸로 표현한 자료구조라고 볼 수 있다.

루프 노드 : 노드의 가장 상위 노드

리프 노드 : 마짐막에 존재하는 노드

중간 노드 : 중간에 끼인 노드

💡 노드는 개념적인 설명에서 나오는 용어이며, MySQL에서는 페이지라고 부른다. 페이지는 최소한의 저장단위로, 16Kbyte(16384byte)의 크기를 가진다. 1개의 데이터를 입력해도 1개 페이지가 필요하다.

균형 트리는 select(데이터 조회) 시 높은 성능을 발휘한다.

 

균형 트리의 페이지 분할

인덱스를 구성하면 데이터 변경 작업 시 성능이 나빠지는데 이는 페이지 분할이라는 작업이 발생하기 때문이다.

페이지 분할 : 새로운 페이지를 준비해서 데이터를 나누는 작업

페이지 분할이 일어나면 MySQL이 느려지고 자주 일어나면 성능이 큰 영향을 미친다.

 

인덱스의 사용

인덱스 생성

CREATE [UNIQUE] INDEX 인덱스_이름
	ON 테이블_이름 (열_이름) [ASC | DESC] -- 기본값 ASC

인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름

⇒ 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거할 수 없다.

💡 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우, 인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 더 좋다. 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성되기 때문이다.

 

인덱스를 효과적으로 사용하는 방법

• 인덱스는 열 단위에 생성된다.
• WHERE 절에서 사용 되는 열에 인덱스를 만들어야 한다.
• WHERE 절에 사용 되더라도 자주 사용해야 가치가 있다.
  • 인덱스를 생성한 열에 주로 변경 작업만 발생한다면 오히려 성능을 나쁘게 할 수 있다.
• 데이터의 중복이 높은 열을 인덱스를 만들어도 큰 효과는 없다.
• 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
  • 조회 시 가장 많이 사용 되는 열에 지정하는 것이 효과적이다.
• 사용하지 않는 인덱스는 제거해야 데이터 입력 시 발생되는 부하도 줄일 수 있다.

인덱스 실습

생성한 인덱스를 실제로 적용시키기 위해 ANALYZE TABLE 문으로 테이블을 분석/처리해야한다.

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

 

select * from member; -- full table scan
select mem_id, mem_name, addr from member; -- full table scan
select mem_id, mem_name, addr
	from member
	where mem_name = '에이핑크'; -- sigle row
select mem_name, mem_number from member where mem_number >= 7; -- index range sacn
select mem_name, mem_number from member where mem_number >= 1; -- full table scan

→ 회원은 1명 이상이므로 인덱스를 사용하지 않고 전체 테이블 검색을 진행한다.

---

복합 인덱스

복합 인덱스(Composite Index) : 데이터베이스에서 여러 개의 열들을 조합하여 인덱스를 생성하는 것

예를 들어, 고객 정보 데이터베이스에서 고객 이름과 주소를 함께 검색하는 경우를 생각해보자.

단일 인덱스로 이름과 주소를 모두 인덱싱한다면, 데이터베이스는 인덱스에서 해당되는 모든 레코드를 검색하고 필터링 하는 데 많은 시간이 소요될 것이다.

하지만, 복합 인덱스를 사용하면 고객 이름과 주소 열을 통합하여 하나의 인덱스로 만들 수 있다.

이렇게 하면 데이터베이스는 해당 인덱스를 사용하여 더 빠르게 필요한 레코드를 검색할 수 있게 된다.

 

복합 인덱스 장점

• 검색 속도 개선
  • 여러 개의 컬럼을 동시에 검색할 수 있어서 검색 속도가 개선된다.
• 데이터 정렬의 효율성 & 인덱스의 용량 절감
  • 여러 개의 컬럼을 함께 사용하여 인덱스를 생성하면 정렬을 할 때 더 효율적으로 할 수 있고 인덱스의 용량이 줄어든다.
• 쿼리 최적화
  • 복합 인덱스는 여러 개의 컬럼을 함께 사용하기 때문에 쿼리가 여러 개의 조건을 가지고 있을 때 최적화 된 실행 계획을 수립할 수 있다.

 

복합 인덱스 컬럼 선택

• WHERE 절에서 AND 조건으로 자주 통합되어 사용하는 컬럼들
• 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들
• ORDER BY에서 자주 사용되는 컬럼들
• 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때

 

주의점

• 인덱스를 생성하는 컬럼의 개수가 많아질수록 인덱스의 성능은 떨어질 수 있다.
• 인덱스 생성 순서를 고려해야 한다.
  • 복합 인덱스의 컬럼 중 선행하는 컬럼(주로 사용되는 컬럼)부터 조건에 지정해서 사용해야 한다.

•