[MySQL] 인덱스 작성과 삭제

[SYNTAX]
CREATE INDEX
DROP INDEX

 

인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제한다.

표준 SQL에는 CREATE INDEX 명령은 없고, 인덱스 자체가 데이터베이스 제품에 의존하는 선택적인 항목으로 취급된다.

하지만, 대표적인 데이터베이스 제품에는 모두 인덱스 구조가 도입되어 비슷한 관리방법으로 인덱스를 다룰 수 있다.

 

인덱스는 이름을 붙여 관리하는데 인덱스가 데이터베이스 객체가 될지, 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다르다. Oracle이나 DB2 등에서 인덱스는 스키마 객체가 되어 스키마 내 이름이 중복하지 않도록 지정해 관리한다. SQL Server나 MySQL에서 인덱스는 테이블 내의 객체가 되어, 테이블 내에 이름이 중복되지 않도록 지정해 관리한다.

 

먼저 인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정할 필요가 있다. 이때 열은 복수로 지정도 가능하다. 

인덱스를 작성할 때 저장장치에 색인용 데이터가 만들어지는데, 테이블의 크기가 클수록(행이 많을수록) 시간도 많이 걸리고 저장공간도 많이 소비한다.

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

 

인덱스를 삭제할 때는 다른 객체와 동일하게 인덱스 이름만 지정하면 된다. 다만, 테이블 내에 객체로서 작성하는 경우 테이블 이름도 지정해 준다.

인덱스는 테이블에 의존하는 객체로 DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제된다.

[SYNTAX] - 스키마 객체의 경우
DROP INDEX 인덱스명

[SYNTAX] - 테이블 내 객체의 경우
DROP INDEX 인덱스명 ON 테이블명

 

인덱스를 작성해두면 검색이 빨라진다. 작성한 인덱스의 열을 WHERE구로 조건을 지정하여 SELECT 명령으로 검색하면 처리 속도가 향상된다. INSERT 명령의 경우 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리 속도가 늦어진다.

 

인덱스를 사용해 검색하는지 확인하고 싶을 경우 EXPLAIN 명령을 사용한다.

[SYNTAX] EXPLAIN
EXPLAIN SQL 명령

문법은 간단하다. EXPLAIN 뒤에 확인하고 싶은 SQL 명령을 지정하면 된다.

다만, 이 SQL 명령은 실제로 실행되지 않고, 어떤 상태로 실행되는지 데이터베이스가 설명을 해준다.

sample62 테이블의 a 열에는 isample65라는 인덱스가 작성되어 있어 인덱스를 활용해 검색을 한다.

possible_keys라는 곳에 사용될 수 있는 인덱스가 표시되며, key는 사용된 인덱스가 표시된다.

 

WHERE구의 조건을 바꾸어 인덱스를 지정하지 않은 열을 활용해 검색을 하게 되면 possible_keys와 key가 null이 된 것을 확인할 수 있다.

 

SELECT 명령을 실행할 때 인덱스 사용 여부를 선택하는데 이는 데이터베이스 내부의 최적화에 의해 처리되는 부분이다.

내부 처리에서는 명령을 실행하기 앞서 '인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용하자'와 같은 실행계획을 세운다.

EXPLAIN 명령은 이 실행계획을 확인하는 명령이다.

실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 최적화를 통해 판단하게 된다.