[MYSQL] INDEX

INDEX란?

저장된 데이터를 가지고 서비스할 때 검색 성능을 높이기위한 기술 

테이블의 동작속도(조회)를 높여주는 자료구조

UPDATE, INSERT, DELETE의 속도는 저하되며 저장 공간(DB의 10%정도) 차지한다는 단점이 있음

왜? 레코드를 추가, 삭제, 수정할 때 생성된 인덱스에도 데이터 동기화를 해주어야해서 오버헤드가 생기기 때문

 

INDEX의 종류?

클러스터형, 보조 인덱스

클러스터형은 테이블당 한개만 생성가능. 우리가 Primary Key로 지정하는 컬럼이 클러스터형 인덱스.

생성과 동시에 오름차순으로 정렬됨.

NOT NULL Unique 제약조건도 클러스터형 인덱스가 될 수 있지만 Primary Key와 동시에 만든다면, Primary Key가 우세함으로 클러스터형 인덱스가 됨

보조 인덱스(비 클러스터형 인덱스)는 검색을 용이하게 하기 위해서 특정 컬럼, 컬럼들의 조합으로 인덱스를 생성하는 것

 

INDEX와 PK의 차이?

INDEX

튜플의 유일성을 보장하지않고 단지 테이블에서 튜플을 보다 빨리 찾기위해 사용되는 것

여러개 생성 가능

UNIQUE INDEX

인덱스가 걸린 컬럼에 중복될 수 없는 유일한 값을 보장하고 NULL값이 입력 가능함 Object의 속성은 index

여러개 생성 가능

PK

중복될 수 없는 유일한 단일값이며 NULL이 불가능 Object의 속성은 Constraint로 제약조건으로 생성됨

생성 시 자동으로 유니크 인덱스가 생성됨

한개만 생성 가능

*제약 조건 : 데이터의 무결성을 위해 설정된 제약

 

INDEX의 내부 동작

- 클러스터형 인덱스

데이터를 INSERT 할 때마다 루트 페이지, 리프 페이지가 재정렬이 됨. 여기서 리트 페이지가 데이터 페이지이기도 함

- 보조 인덱스

데이터 페이지는 그대로 두고 새로운 루트 페이지, 리프 페이지의 인덱스를 구성하고 리프페이지는 데이터 페이지를 참조하는 포인터를 저장함 포인터에는 데이터 페이지의 메모리 주소 + OFFSET이 들어있음

 

INDEX가 실행되지 않는 경우

ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우

WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우

ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우

GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우

ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

 

INDEX 설계방법

무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)

조회시 자주 사용하는 컬럼

고유한 값 위주로 설계

카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)

INDEX 키의 크기는 되도록 작게 설계

PK, JOIN의 연결고리가 되는 컬럼

단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려

UPDATE가 빈번하지 않은 컬럼

JOIN시 자주 사용하는 컬럼

INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

 

INDEX 문법

인덱스 생성

CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...)

인덱스 조회

SHOW INDEX FROM 테이블이름

유니크 인덱스 생성

CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1, 필드이름2, ...)

인덱스 삭제

ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;

인덱스 추가

ALTER TABLE 테이블이름 ADD (UNIQUE)INDEX 인덱스이름(컬럼명1, 컬럼명2...);