인덱스 (2)

Preface

 

이번 장에선 인덱스가 없는 경우, 클러스터형 인덱스를 사용하는 경우, 그리고 보조 인덱스를 사용하는 경우의 시스템 성능을 직접 비교해봤다.

 

단순 읽기 작업을 하는 경우엔 보통 클러스터형 인덱스를, 데이터 수정이 자주 발생하는 경우엔 보조 인덱스를 사용하는 것이 좋다.

 

또, DB에 저장된 대부분의 데이터를 읽는 경우나 데이터 중복도가 높은 경우엔 오히려 인덱스를 사용하지 않는 것이 효울적이다.

 

이 외에도 인덱스를 사용할 때 몇 가지 주의할 점을 알아봤는데, 크게 어려운 부분은 없었다.

 

꼭 기억해야 할 점이라면 인덱스가 생성된 열에는 where절에 함수나 연산을 사용하면 안 된다는 것 정도가 있다.

---

 

4. 인덱스 생성/변경/삭제

 

 

- create index문으로는 primary key로 생성되는 클러스터형 인덱스를 만들 수 없다.

 

 

- 인덱스 생성 방법

create [unique/fulltext/spatial] index 인덱스이름
	on 테이블이름 (열이름);

1) fulltext는 전체 텍스트 인덱스를 만드는데 사용된다.

2) spatial은 점, 선, 면 등의 공간 데이터와 관련된 인덱스를 생성한다.

 

 

- UNIQUE 옵션

1) 고유한 인덱스를 만들 것인지를 결정한다.

→ 동일한 데이터 값이 입력될 수 없다.

2) 보조 인덱스가 생성된다.

※ 데이터가 절대로 중복되지 않을 경우에만 사용해야 한다.

 

 

- 인덱스 제거 방법

drop index 인덱스이름 on 테이블이름;

 

1) 모든 인덱스 삭제 시에는 보조 인덱스를 먼저 제거해야 한다.

2) 인덱스의 활용도가 떨어지면 과감히 삭제해 줄 필요가 있다.

3) 기본 키로 설정된 클러스터형 인덱스의 이름은 항상 PRIMARY로 되어 있다.

4) alter table문으로 기본 키를 제거하면 클러스터형 인덱스도 제거된다.

→ primary key에 설정된 인덱스는 drop index문으로 삭제되지 않고, alter table문으로만 삭제할 수 있다.

    → 먼저 참조하는 외래 키 관계를 제거한 후에 기본 키를 삭제해야 한다.

 

 

- show table status like '테이블이름'의 각 데이터 설명

1) Data_length: 데이터 페이지 또는 클러스터형 인덱스의 크기

2) Index_length: 보조 인덱스의 크기

→ 둘 다 Byte 단위이다.

 

 

- '단순'은 중복을 허용한다는 의미로 '고유(unique)'와 반대다.

 

 

- 단편화 제거 방법

optimize table 테이블이름;
-- InnoDB 엔진의 경우 analyze가 포함되어 있다.

 

 

- 테이블 분석/처리 방법 (생성한 인덱스를 실제로 적용)

analyze table 테이블이름;
-- 생성한 인덱스를 테이블에 실제로 적용하기 위해 필요

 

 

- 선택도가 나쁜 데이터: 데이터의 종류가 적은 열

→ 인덱스를 생성하지 않는 편이 낫다.

 

---

 

5. 인덱스의 성능 비교

 

 

- Execution Plan의 Key Lookup: 인덱스에서 검색된 RID(Row ID, 각 행의 고유번호, 주소값)를 가지고 실제 데이터 페이지의 데이터로 찾아가는 과정

 

 

- 인덱스 힌트(hint)

1) use index(인덱스이름): 인덱스를 강제로 사용하게 함

2) ignore index(인덱스이름): 인덱스를 강제로 사용하지 못하게 함

select * from emp_c ingore(use) index(primary)
	where emp_no < 500000 limit 1000000;

 

 

- 기존에 생성한 보조 인덱스 중 전체 데이터의 대략 15% 이상을 스캔하는 경우엔 MySQL이 인덱스를 사용하지 않고 Full Table Scan을 한다.

 

 

※ 인덱스가 생성된 열에 함수나 연산을 가하게 되면, 인덱스를 사용하지 못할 수도 있다.

→ 인덱스가 생성된 where에 나오는 열 이름에는 아무런 함수나 연산을 가하지 않아야 한다.

 

 

- 데이터의 중복도(Cardinality: 카디널리티): 전체 행에 대한 특정 열의 중복 수치

→ 값이 낮을수록 중복도가 크다.

 

---

 

6. 결론: 인덱스를 생성해야 하는 경우와 그렇지 않은 경우

 

 

- 인덱스는 열 단위에 생성된다.

 

 

- where절에서 사용되는 열에 인덱스를 만들어야 한다.

 

 

- where절에 사용되더라도 자주 사용해야 가치가 있다.

 

 

- 인덱스는 테이블을 정의하는 시점에 어디에 생성할 것인지를 잘 설계한 후에 지정하는 것이 가장 좋다.

 

 

- 데이터의 중복도가 높은 열(카디널리티가 낮은 열)은 인덱스를 만들어도 별 효과가 없다.

 

 

- 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.

 

 

- join에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.

 

 

- insert/update/delete가 얼마나 자주 일어나는지를 고려해야 한다.

 

 

- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.

 

 

- 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다. (데이터 수정이 잦은 경우)

→ unique 키에 not null을 함께 지정하면 클러스터형 인덱스가 생성되는 것에 주의해야 한다.

    → 응용 프로그래밍 화면에서 필수로 입력되도록 하는 방식을 사용하는 것이 좋다.

 

 

- 사용하지 않는 인덱스는 제거하는 것이 시스템 성능에 좋다.