데이터베이스 첫걸음 정리 - 29장. 인덱스 작성과 삭제

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

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

 

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

인덱스 작성

인덱스는 CREATE INDEX 명령으로 만듭니다.

인덱스에 이름을 붙여 관리하는데, 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다릅니다.

각 데이터베이스 별 인덱스 관리 Oracle, DB2 → 스키마 객체 SQL Server, MySQL → 테이블 내의 객체

 

인덱스를 작성할 때는 해당 인덱스가 어떤 테이블의 어느 열에 관한 것인지 지정해야 합니다.

이때 열은 복수로도 지정할 수 있습니다. (인덱스의 네임 스페이스가 제품마다 다르다는 것을 주의합시다.)

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

다음은 인덱스 생성 예시입니다.

CREATE INDEX idx_users ON users(no);

인덱스를 작성할때는 저장장치에 색인용 데이터가 만들어집니다.

테이블 크기에 따라 인덱스 작성 시간도 달라지는데, 행이 대량으로 존재하면 시간도 많이 걸리고 저장공간도 많이 소비합니다.

인덱스 삭제

인덱스는 DROP INDEX 명령으로 삭제합니다.

인덱스를 삭제하는 경우 인덱스를 저장하는 방법에 따라 다음과 같이 나뉩니다.

• 인덱스를 스키마 객체로 저장하는 경우

DROP INDEX 인덱스명

• 테이블 내 객체인 경우

DROP INDEX 인덱스명 ON 테이블명

인덱스는 테이블에 의존하는 객체입니다.

DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제됩니다.

인덱스만 삭제하는 경우에는 DROP INDEX를 사용합니다.

그럼 앞에서 미리 작성해 둔 인덱스를 삭제해봅시다.

DROP INDEX idx_users ON users;

인덱스 사용

인덱스를 작성해두면 검색이 빨라지기 때문에 작성한 인덱스의 열을 WHERE 구로 조건을 지정하여 SELECT 명령으로 검색하면 처리속도가 향상됩니다.

단, INSERT 명령을 사용할 경우 인덱스를 최신 상태로 갱신하는 처리가 늘어아기 때문에 처리속도가 떨어집니다.

이제 SELECT 명령을 통해 인덱스를 사용하는 예시를 확인해봅시다.

먼저, 다음과 같이 인덱스를 생성합시다.

CREATE INDEX idx_users ON users(no);

WHERE 구에 no 열에 대한 조건식을 지정한 경우 SELECT 명령은 인덱스를 사용해 빠르게 검색할 수 있습니다.

SELECT * FROM users WHERE no=1;

만약 WHERE 구의 조건식에 인덱스로 지정한 열(현재는 no)이 사용되지 않으면 인덱스가 적용되지 않습니다.

EXPLAIN

실제로 인덱스를 사용해 검색하는지 확인하고 싶다면 EXPLAIN 명령을 통해 확인할 수 있습니다.

EXPLAIN {SQL명령}

EXPLAIN을 사용하려면 뒤이어 확인하고 싶은 SELECT 명령 등의 SQL 명령을 지정하면 됩니다.

단, 이 명령은 실제로는 실행되지 않습니다. 어떤 상태로 실행되는지를 데이터베이스가 설명해줄 뿐입니다.

EXPLAIN은 표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령입니다. 하지만 어떤 데이터베이스 제품이라도 이와 비슷한 명령을 지원합니다.

 

다음은 EXPLAIN 사용예시입니다.

• 인덱스를 사용하여 검색한 경우

EXPLAIN SELECT * FROM users WHERE no=1;

• 인덱스가 사용되지 않은 경우

EXPLAIN SELECT * FROM users WHERE name="kim";

위의 결과를 확인해보면 인덱스를 사용한 경우 possible_keys, key에 인덱스가 표시되고 인덱스를 사용하지 않은 경우 NULL로 나오는 것을 확인할 수 있습니다.

최적화

SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택하게 됩니다.

이는 데이터베이스 내부의 최적화에 의해 처리되는 부분입니다.

내부 처리에서는 SELECT 명령을 실행하기에 앞서 실행계획을 세웁니다.

실행계획에서는 “인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용하자"와 같은 처리가 이루어집니다.

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

실행 계획에서는 인덱스의 유무뿐만 아니라 인덱스의 사용 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단합니다.

이때 판단 기준으로 인덱스의 품질도 고려합니다.

예를 들어 “예", “아니오"라는 값만 가지는 열이 있다면, 해당 열에 인덱스를 지정해도 다음과 같은 이진트리가 되어 좋은 구조를 가지지 못합니다.

위와 같이 예, 아니오로만 되어 있다면 이진 탐색에 의한 효율적인 검색은 기대할 수 없습니다.

데이터의 수가 적으면 적을수록 인덱스의 효율이 떨어지기 때문에 서로 다른 값으로 여러 종류의 데이터가 존재하면 그만큼 효율이 좋아집니다.

이렇게 인덱스의 품질을 고려해 실행계획이 세워집니다.

정리

• 인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제합니다.
• CREATE INDEX 명령으로 인덱스를 생성할 수 있습니다.
• DROP INDEX 명령으로 인덱스를 삭제할 수 있습니다.
• 인덱스를 사용하기 위해서는 인덱스로 지정한 열을 조건에 지정해야 사용할 수 있습니다.
• EXPLAIN 명령으로 해당 검색문에서 인덱스를 사용하고 있는지 확인할 수 있습니다.
• 데이터베이스는 인덱스의 존재유무 이외에도 인덱스의 품질도 고려하여 실행계획을 세워 인덱스를 사용할지 말지 정합니다.