29강. 인덱스 작성과 삭제

여기서는 실제로 테이블에 인덱스를 작성하는 방법에 대해서 알아본다.

인덱스 작성, 삭제

CREATE INDEX
DROP INDEX

1. 인덱스 작성

CREATE INDEX

CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

인덱스 작성하기

CREATE INDEX isample65 ON sample62(no);

DROP INDEX(스키마 객체의 경우)

DROP INDEX 인덱스명

DROP INDEX(테이블 내 객체의 경우)

DROP INDEX 인덱스명 ON 테이블명

인덱스 삭제하기

DROP INDEX isample65 ON sample62;

CREATE INDEX isample65 ON sample62(a);

WHERE 구에 a 열에 대한 조건식을 지정한 경우 SELECT 명령은 인덱스를 사용해 빠르게 검색할 수 있다.
예를 들면 다음과 같은 SELECT 명령이 된다. 그러나 WHERE 구의 조건식에 a 열이 전혀 사용되지 않으면 SELECT 명령은 isample62라는 인덱스를 사용할 수 없다.

SELECT * FROM sample62 WHERE a = 'a';

EXPLAIN

EXPLAIN SQL명령

EXPLAIN 명령의 문법은 간단하다. EXPLAIN에 뒤이어 확인하고 싶은 SELECT 명령 등의 SQL 명령을 지정하면 된다. 다만 이 SQL 명령은 실제로는 실행되지 않는다.
어떤 상태로 실행되는지를 데이터베이스가 설명해줄 뿐이다.
- MySQL의 경우 상황에 따라 다르지만 필요한 정보를 얻기 위해 SQL 명령의 일부분을 실제로 실행하는 경우도 있다.
EXPLAIN은 표준 SQL에는 존재하지 않는, 데이터베이스 제품 의존형 명령이다. 하지만 어떤 데이터베이스 제품이라도 이와 비슷한 명령을 지원한다.

EXPALIN으로 인덱스 사용 확인하기 1 (MySQL)

EXPLAIN SELECT * FROM sample62 WHERE a = 'a';

sample62의 a 열에는 isample65이라는 인덱스가 작성되어 있다.
EXPLAIN의 뒤를 잇는 SELECT 명령은 a 열의 값을 참조해 검색하므로 isample65을 사용해 검색한다(possible_keys 라는 곳에 사용될 수 있는 인덱스가 표시되며, key는 사용된 인덱스가 표시된다).

이때 WHERE 조건을 바꾸면 어떻게 변하는지 알아본다. a 열을 사용하지 않도록 조건을 변경하면 인덱스를 사용할 수 없을 것이다.

EXPLAIN으로 인덱스 사용 확인하기 2 (MySQL)

EXPLAIN SELECT * FROM sample62 WHERE no > 10;

SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택한다는 것을 알았다.
이는 데이터베이스 내부의 최적화에 의해 처리되는 부분이다.
내부 처리에서는 SELECT 명령을 실행하기에 앞서 실행계획을 세운다.
실행계획에는 '인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용하자'와 같은 처리가 이루어진다.
EXPLAIN 명령은 이 실행계획을 확인하는 명령이다.
실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다.
이때 판단 기준으로 인덱스의 품질도 고려한다.
예를 들어 '예' 또는 '아니오'라는 값만 가지는 열이 있다면, 해당 열에 인덱스를 지정해도 다음과 같은 이진트리가 되어 좋은 구조를 가지지 못한다.