인덱스의 내부 작동.MySQL

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

• 자료구조에 나오는 범용적으로 사용되는 데이터 구조
• 인덱스 표현할 때와 그 외에도 많이 사용

 

---

페이지 분할

• 인덱스 구성시 SELECT 문의 효율성 향상
• 인덱스 구성시 INSERT 문이 일어날 경우 속도 저하되는 단점이 존재합니다.
  • 주어진 공간 이상으로 데이터가 들어가면 페이지 분할이 일어납니다.

 

• 인덱스 없는 테이블의 예시

create table clustertbl -- Cluster Table의 약자
( 
	userid char(8),
	name varchar(10)
);

insert into clustertbl values ('LSG', '이승기');
insert into clustertbl values ('KBS', '김범수');
insert into clustertbl values ('KKH', '김경호');
insert into clustertbl values ('JYP', '조용필');
insert into clustertbl values ('SSK', '성시경');
insert into clustertbl values ('LJB', '임재범');
insert into clustertbl values ('YJS', '윤종신');
insert into clustertbl values ('EJW', '은지원');
insert into clustertbl values ('JKW', '조관우');
insert into clustertbl values ('BBK', '바비킴');

 

---

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

• 클러스터형 인덱스를 구성한 테이블 구조
  • userID를 Primary Key로 지정하면 클러스터형 인덱스로 구성됩니다.

 

---

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

-- 보조 인덱스 테이블
create table secondarytbl -- Cluster Table의 약자
( 
	userid char(8),
	name varchar(10)
);

insert into secondarytbl values ('LSG', '이승기');
insert into secondarytbl values ('KBS', '김범수');
insert into secondarytbl values ('KKH', '김경호');
insert into secondarytbl values ('JYP', '조용필');
insert into secondarytbl values ('SSK', '성시경');
insert into secondarytbl values ('LJB', '임재범');
insert into secondarytbl values ('YJS', '윤종신');
insert into secondarytbl values ('EJW', '은지원');
insert into secondarytbl values ('JKW', '조관우');
insert into secondarytbl values ('BBK', '바비킴');

-- unique key 설정
alter table secondarytbl
	add constraint UK_secondarytbl_userid
		unique (userid);

 

---

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

insert into clustertbl values('FNT', '푸니타');
insert into clustertbl values('KAI', '카아이');

---

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

insert into secondarytbl values('FNT', '푸니타');
insert into secondarytbl values('KAI', '카아이');

 

---

• 클러스터형 인덱스의 특징
  
  • 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체를 다시 정렬
    • 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템부하를 초래합니다.
  • 인덱스 자체의 리프페이지가 곧 데이터
    • 인덱스 자체에 데이터가 포함되어 있습니다.
  • 클러스터형 인덱스는 보조 인덱스보다 검색속도가 더 빠릅니다
    • 데이터의 입력/수정/삭제는 더 느립니다.
  • 클러스터형 인덱스는 성능이 좋지만 테이블에 한 개만 생성이 가능합니다.
    • 어느 열에 클러스터형 인덱스를 생성하는지에 따라 시스템의 성능이 달라집니다.

 

---

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

 

---

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

-- ----------------------------------------
-- 클러스터형 인덱스, 보조 인덱스 모두 존재할 경우
create table mixedtbl
(
	userid char(8) not null,
    name varchar(8) not null,
    addr char(2)
);

insert into mixedtbl values ('LSG', '이승기', '서울');
insert into mixedtbl values ('KBS', '김범수', '경남');
insert into mixedtbl values ('KKH', '김경호', '전남');
insert into mixedtbl values ('JYP', '조용필', '경기');
insert into mixedtbl values ('SSK', '성시경', '서울');
insert into mixedtbl values ('LJB', '임재범', '서울');
insert into mixedtbl values ('YJS', '윤종신', '경남');
insert into mixedtbl values ('EJW', '은지원', '경북');
insert into mixedtbl values ('JKW', '조관우', '경기');
insert into mixedtbl values ('BBK', '바비킴', '서울');

 

---

-- mixedtbl에 primary key 설정
alter table mixedtbl
	add constraint PK_miexdtbl_userid
		primary key (userid);
        
-- 테이블에 보조 인덱스 추가
alter table mixedtbl
	add constraint UK_mixedtbl_name
		unique(name);

 

 

---

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

 

---