MySQL 인덱스 생성 수정 / 삭제

인덱스

: 테이블의 검색속도를 향상시키기 위한 자료구조

: 목차 처럼 필요한 컬럼의 위치를 저장하고 해당 컬럼의 데이터를 빠르게 검색

 

- 장점

: SELECT 절 성능을 향상 (무조건은 아님)

: 쿼리의 부하가 줄어들어 전체적으로 시스템의 성능이 향상됨

 

- 단점

: INSERT, UPDATE, DELETE 성능 하락

 > 레코드가 추가, 삭제, 수정될 경우 인덱스도 동기화 해야하기 때문

: 추가적인 디스크 공간 필요 (테이블 크기의 10%)

: 처음에 인덱스 생성 시 많은 시간이 소요됨

 

 

 

 

 

클러스터형 인덱스

: 키 값에 대한 테이블의 데이터 행을 정렬하는 인덱스

: 테이블 당 한개만 생성 가능

: 행 데이터를 인덱스로 지정한 열에 맞춰 자동으로 정렬

: 테이블의 내용 자체가 순서대로 정렬되어 있어 테이블의 내용과 동일

 

- 생성 조건

: PRIMARY KEY 혹은 UNIQUE NOT NULL에 의해 자동으로 생성됨

 

- 검색 순서

: 루트 페이지 > 리프 페이지 (데이터 페이지)

 

- 특징

: 리프 페이지가 모두 차있을 때 페이지 분할 발생

 

 

 

 

 

보조 인덱스

: 데이터와 인덱스를 각각 다른 위치에 저장

: 인덱스는 해당 데이터의 위치에 대한 포인터를 포함

: 테이블당 여러 개 생성 가능

: 찾아보기에서 먼저 단어를 찾은 후 해당하는 페이지로 이동하여 내용 검색

 

- 생성 조건

: UNIQUE, UNIQUE NULL에 의해 자동 생성

: CREATE INDEX 쿼리로 생성

 

- 검색 순서

: 루트페이지 > 리프페이지 > 데이터 페이지 (Heap Page)

※ HeapPage

: 정렬 기준이 없는 테이블. 입력된 순서대로 저장

 

- 특징

: 별도의 인덱스가 생성되지만 원본 데이터의 변경은 없음

: 리프페이지가 데이터 페이지를 가리키는 포인터를 포함

: 리프페이지가 모두 차있어도 페이지 분할이 일어나지 않음

 

 

 

 

 

 

 

인덱스 생성

CREATE [UNIQUE] INDEX [DB명.].인덱스명

ON [DB명.]테이블명 (컬럼1[,컬럼2, 컬럼3 ...])

: 유티크 인덱스, 단일 인덱스, 복합 인덱스 생성 가능

 

 

 

 

 

 

인덱스 삭제

DROP INDEX [DB명.]인덱스명

 

※ 참고

: 인덱스는 ANALYZE TABLE이나 OPTIMIZE TABLE을 통해 재생성하여 업데이트 할 수 있음

 

 

 

 

 

인덱스 성능 확인

SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';

: 읽은 페이지 수 반환

: 쿼리 실행전과 실행 후 페이지를 얼마나 읽었는지 확인 가능

 

 

 

 

 

인덱스를 사용하기 좋은 테이블

: 규모가 큰 테이블

: 데이터의 변경이 자주 발생하지 않는 컬럼

: JOIN, WHERE, ORDER BY 가 자주 사용되는 컬럼

: 데이터의 중복도가 낮은 컬럼

 

 

 

 

 

 

 

Refference

[MySQL] 인덱스(index), 클러스터/보조인덱스

[Oracle] 오라클 인덱스(Index) 생성 및 삭제 방법 (Create, Drop)

[DB] 인덱스(index) / MySql에서 사용법

효과적인 DB index 설정하기