이것이MySQL이다 - 01) 인덱스

인덱스의 개념

 

인덱스(Index)란?

책의 <찾아보기>의 개념과 비슷

데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구

 

 

 

 

인덱스의 장단점

장점

• 검색 속도가 무척 빨라질 수 있음 (항상 그런 것은 아님)
• 쿼리의 부하가 줄어들어 시스템 전체의 성능 향상

 

단점

• 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간 필요
  • 대략 데이터베이스 크기의 10% 정도의 추가 공간 필요
• 처음 인덱스 생성하는데 시간 소요
• 데이터의 변경 작업 (Insert, Update, Delete)이 자주 일어나는 경우 성능이 나빠질 수도 있음

 

 

인덱스의 종류와 자동 생성

 

 

인덱스의 종류

클러스터형 인덱스 (Clustered Index)

• ‘영어 사전’과 같은 책
• 테이블 당 한 개만 지정 가능
• 행 데이터를 인덱스로 지정한 열에 맞춰 자동 정렬

 

보조 인덱스 (Secondary Index)

• 책 뒤에 <찾아보기>가 있는 일반 책
• 테이블당 여러 개도 생성 가능

 

 

 

자동으로 생성되는 인덱스

인덱스 실습

• sqlDB에서 작업 >> 테이블 생성

USE sqldb;
   CREATE TABLE  tbl1  
   (           
	a INT PRIMARY KEY,
	b INT,
	c INT
    );

• 인덱스 상태 확인 :
  • SHOW INDEX FROM tbl1;

 

 

 

인덱스 실습

• Primary Key와 함께 Unique 제약 조건 생성 

CREATE TABLE  tbl2
(
	a INT PRIMARY KEY,
	b INT UNIQUE,
	c INT UNIQUE,
	d INT   
);
SHOW INDEX FROM tbl2;

 

 

 

인덱스 실습

• Primary Key 없이 Unique Key만 지정 

CREATE TABLE  tbl3
(
	a INT UNIQUE,
	b INT UNIQUE,
	c INT UNIQUE,
	d INT   
);

SHOW INDEX FROM tbl3;

 

 

 

 

 

인덱스 실습

• UNIQUE에 클러스터형 인덱스 지정, UNIQUE에 NOT NULL이 포함되면 클러스터형 인덱스로 지정됨 

CREATE TABLE  tbl4
(
	a INT UNIQUE NOT NULL,
	b INT UNIQUE,
	c INT UNIQUE,
	d INT   
);

SHOW INDEX FROM tbl4;

 

 

 

인덱스 실습

UNIQUE에 NOT NULL과 PRIMARY KEY를 모두 지정 

CREATE TABLE  tbl5
(
	a INT UNIQUE NOT NULL,
	b INT UNIQUE,
	c INT UNIQUE,
	d INT PRIMARY KEY  
);

SHOW INDEX FROM tbl5;

 

 

 

인덱스 실습

• 회원 테이블의 열만 정의 

 

 

인덱스 실습

• 데이터 입력 및 확인 

 

인덱스 실습

• userID열의 Primary Key를 제거하고, name열을 Primary Key로 지정  

 

 

인덱스의 특징

• PRIMARY KEY로 지정한 열은 클러스터형 인덱스가 생성
• UNIQUE NOT NULL로 지정한 열은 클러스터형 인덱스 생성
• UNIQUE(또는 UNIQUE NULL)로 지정한 열은 보조 인덱스 생성
• PRIMARY KEY와 UNIQUE NOT NULL이 존재
• PRIMARY KEY와 UNIQUE NOT NULL이 있으면 PRIMARY KEY에 지정한 열에 우선 클러스터형 인덱스 생성
• PRIMARY KEY로 지정한 열로 데이터가 오름차순 정렬

 

 

 

인덱스의 내부 작동

 

 

B-Tree(Balanced Tree, 균형 트리)

자료 구조’에 나오는 범용적으로 사용되는 데이터 구조

인덱스 표현할 때와 그 외에도 많이 사용

 

 

 

페이지 분할

인덱스 구성시 SELECT 문의 효율성 향상

인덱스 구성시 INSERT 문이 일어날 경우 속도 저하되는 단점

• 주어진 공간 이상으로 데이터 들어가면 페이지 분할 일어남

 

 

클러스터형 인덱스와 보조 인덱스의 구조

인덱스 없는 테이블의 예시

 

 

클러스터형 인덱스와 보조 인덱스의 구조

인덱스 없는 테이블의 예시

 

 

 

클러스터형 인덱스와 보조 인덱스의 구조

클러스터형 인덱스 구성한 테이블 구조

• userID를 Primary Key로 지정하면 클러스터형 인덱스로 구성됨

 

클러스터형 인덱스와 보조 인덱스의 구조

클러스터형 인덱스 구성한 테이블 구조

 

보조 인덱스 구성한 테이블 구조

 

 

클러스터형 인덱스와 보조 인덱스의 구조

보조 인덱스 구성한 테이블 구조

 

 

 

 

 

클러스터형 인덱스와 보조 인덱스의 구조

클러스터 인덱스에 새로운 데이터 입력

 

 

보조 인덱스에 새로운 데이터 입력

 

 

 

클러스터형 인덱스와 보조 인덱스의 구조

클러스터형 인덱스의 특징

• 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체 다시 정렬
  • 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스 생성하는 것은 심각한 시스템 부하

• 인덱스 자체의 리프 페이지가 곧 데이터
  • 인덱스 자체에 데이터가 포함되어 있음

• 클러스터형 인덱스는 보조 인덱스보다 검색 속도는 더 빠름
  • 데이터의 입력/수정/삭제는 더 느림

• 클러스터형 인덱스는 성능이 좋지만 테이블에 한 개만 생성 가능
  • 어느 열에 클러스터형 인덱스 생성하는지에 따라 시스템의 성능이 달라짐

 

 

클러스터형 인덱스와 보조 인덱스의 구조

보조 인덱스의 특징

• 보조 인덱스 생성시 별도의 페이지에 인덱스 구성
• 인덱스 자체의 리프 페이지는 데이터가 아니고 데이터가  위치하는 주소 값(RID)
• 클러스터형보다 검색 속도는 더 느림
  • 데이터의 입력/수정/삭제는 덜 느림
• 보조 인덱스는 여러 개 생성할 수 있음
  • 남용할 경우에는 시스템 성능을 떨어뜨리는 결과 발생

 

 

클러스터형 인덱스와 보조 인덱스의 구조

• 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

 

 

 

• 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

 

 

• 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우
  • UNIQUE 제약 조건으로 보조 인덱스 추가

 

 

클러스터형 인덱스와 보조 인덱스의 구조

• 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

 

 

클러스터형 인덱스와 보조 인덱스의 구조

 

클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우

• 보조 인덱스를 검색한 후에 다시 클러스터형 인덱스를 검색해야 하므로 약간의 손해를 볼 수도 있겠지만, 데이터의 삽입 때문에 보조 인덱스를 대폭 재구성하게 되는 큰 부하는 걸리지 않음
• 보조 인덱스와 혼합되어 사용되는 경우 되도록이면 클러스터형 인덱스로 설정할 열은 적은 자릿수의 열을 선택하는 것이 바람직함
• 인덱스를 검색하기 위한 일차 조건
  • WHERE절에 해당 인덱스를 생성한 열의 이름이 나와야 함
  • WHERE절에 해당 인덱스를 생성한 열 이름이 나와도 인덱스를 사용하지 않는 경우도 많음