인덱스
인덱스는 데이터베이스 객체 중 하나로 색인이라고도 불린다. 테이블에 인덱스를 붙이면 검색속도가 향상된다. 여기서 ‘검색’이란 SELECT 명령에 WHERE 로 조건을 지정하고 그에 일치하는 행을 찾는 일련의 과정을 말한다. 테이블에 인덱스가 지정되어 있으면 효율적으로 검색할 수 있어 SELECT 명령의 처리 속도가 향상된다.
인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다. 데이터가 없는 인덱스는 의미가 없으므로 인덱스는 테이블에 의존하는 객체라는 것을 알 수 있다.
검색에 사용하는 알고리즘
그렇다면 왜 인덱스를 사용했을 때 검색 속도가 향상될까?
대량의 데이터를 효율적으로 탐색하는 방법엔 여러 가지가 있고 그 중 대표적으로 이진 검색(binary search) 라는 검색 알고리즘이 있다. 그 다음으론 해시가 유명하다. 이진 탐색에서 검색하기 쉬운 구조로 한 것이 이진 트리(binary tree)라는 데이터 구조이다.
풀 테이블 스캔
인덱스가 지정되지 않은 테이블을 검색할 땐 풀 테이블 스캔이라는 검색 방법을 사용한다. 테이블에 저장된 모든 값을 처음부터 차례로 조사해 나가는 방법이다. 예를 들어, 행이 1000건 있다면 값을 최대 1000번 비교한다.
이진 탐색
이진 탐색은 차례로 나열된 집합에 대해 유효한 검색 방법이다. 집합을 반으로 나누어 조사를 진행한다. 대량의 데이터를 검색할 때 유리하다.
데이터가 미리 정렬되어 있어야 하기 때문에 일반적으로 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 만들어진다. 이 때, 이진 트리라는 데이터 구조로 작성된다.
출처 : 네이버 부스트 코스
유일성
이진 트리는 집합 내에 중복하는 값을 가질 수 없다.
인덱스 작성과 삭제
CREATE INDEX DROP INDEX
인덱스는 다른 데이터베이스 객체와 같이 DDL을 사용해서 작성하거나 삭제한다.
인덱스 작성
CREATE INDEX 인덱스명 ON 테이블명(열명1, 열명2, …)
인덱스를 작성할 땐 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정해야 한다.
1
CREATE INDEX itest ON test(no);
위의 예시는 test테이블의 no열에 itest라는 인덱스를 지정한다. 인덱스를 작성하면 인덱스용 데이터가 만들어지고 테이블 크기에 따라 인덱스 작성시간도 달라진다. 행이 대량으로 존재하면 시간과 저장공간을 많이 소비한다.
인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명
1
DROP INDEX itest ON test;
다른 객체의 삭제와 동일하게 인덱스 이름만 지정해도 된다. 단, 테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정한다. 인덱스는 테이블에 의존하기 때문에 테이블을 삭제하면 테이블에 작성된 인덱스도 삭제된다.
1
SELECT * FROM test WHERE no = 8;
위와 같이 SELECT 명령에서 인덱스를 사용해 빠르게 검색할 수 있다.
EXPLAIN
EXPLAIN SQL명령
EXPLAIN을 사용하면 실제 인덱스를 사용해 검색하는 지 확인할 수 있다. EXPLAIN 뒤의 SQL명령이 어떤 상태로 실행되는지 데이터베이스가 설명해준다.
1
EXPLAIN SELECT * FROM test WHERE no=8;
possible_keys엔 사용할 수 있는 인덱스가, key는 사용된 인덱스가 표시된다.