인덱스(색인)은 데이터베이스 객체 중 하나이다.
테이블에는 인덱스를 작성할 수 있다.
1. 인덱스란?
인덱스는 테이블의 행에 붙여진 색인 또는 목차라고 할 수 있다.
인덱스의 역할은 검색속도의 향상이다.
여기서 '검색(탐색)'이란 SELECT 명령에 WHERE 구로 조건을 지정하고 그에 일치하는 행을 찾는 일련의 과정이다.
테이블에 인덱스가 지정되어 있으면 효율적으로 검색할 수 있으므로 WHERE로 조건이 지정된 SELECT 명령의 처리 속도가 향상된다.
데이터베이스의 인덱스에는 검색 시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.
인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다.
하지만 인덱스만으로는 아무런 의미가 없다!
인덱스는 테이블에 의존하는 객체이다. 그러므로 대부분의 데이터베이스에서 테이블을 삭제하면 인덱스도 같이 삭제된다!
2. 인덱스에 쓰이는 검색 알고리즘 : binary search(이진 탐색)
- full table scan(풀 테이블 스캔)
- 인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔 사용
- 테이블에 저장된 모든 값을 처음부터 차례로 조사하는 방법
- binary search(이진 탐색)
- 차례로 나열된 집합에 대해 유효한 검색 방법
- 처음부터 순서대로 조사하는 것이 아니고 집합을 반으로 나누어 조사하는 검색 방법 => 빠르다
- 이진 탐색을 사용하려면 데이터가 미리 정렬되어 있어야한다.
이를 위해 인덱스용 데이터는binary tree(이진 트리)라는 데이터 구조로 관리된다.
(binary search와 binary tree에 대해 더 알고싶다면 구글링 ㅎㅎ)
3. 인덱스 생성
CREATE INDEX idx_name ON table_name(col_name);- CREATE INDEX 명령으로 생성
- 인덱스에 이름을 붙여 관리
- 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다르다.
- 해당 인덱스가 어느 테이블의 어느 열에 관련된 것인지 지정
- 열은 복수로도 지정 가능
- 인덱스 작성시 저장장치에 색인용 데이터가 만들어진다.
- 테이블 크기에 따라 인덱스 작성시간도 달라진다.
- 행이 대량으로 존재하면 시간도 많이 걸리고 저장공간도 많이 소비.
4. 인덱스 삭제
DROP INDEX idx_name; // 스키마 객체의 경우
DROP INDEX idx_name ON table_name; // 테이블 내 객체의 경우- DROP INDEX 명령으로 삭제
- 인덱스는 테이블에 의존하는 객체
- DROP TABLE로 테이블 삭제시 테이블에 작성된 인덱스도 자동 삭제
5. EXPLAIN 명령
인덱스 작성을 통해 쿼리의 성능 향상을 기대할 수 있다.
이때 실제로 인덱스를 사용해 검색하는지 확인하려면 EXPLAIN 명령을 사용한다.
EXPLAIN SELECT * FROM test_table WHERE a='blahblah';- EXPLAIN + 확인하고 싶은 sql 명령
- 이 sql 명령은 실제로는 실행되지 않는다.
- 어떤 상태로 실행되는지를 데이터베이스가 설명해줄 뿐이다.
- possible_keys : 사용될 수 있는 인덱스 / key : 사용된 인덱스
6. 인덱스를 사용하면 검색 속도가 빨라진다
인덱스를 만들어두면 검색이 빨라진다.
작성한 인덱스의 열을 WHERE구로 조건을 지정하여 SELECT 명령으로 검색하면 처리속도가 향상된다. (모든 SELECT 명령에 적용되는 만능 인덱스는 작성할 수 없다)
하지만, INSERT 명령 실행시 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리속도가 조금 떨어진다.
만약 다음과 같은 명령으로 인덱스를 작성했을 경우
CREATE INDEX test_idx ON test_table(a);SELECT 명령에서 WHERE구에 a열에 대한 조건식 지정했을 때 인덱스를 사용해 빠르게 검색할 수 있다.
SELECT * FROM test_table WHERE a='blahblah';그러나 WHERE구의 조건식에 a열이 전혀 사용되지 않으면 SELECT 명령은 test_idx라는 인덱스를 사용할 수 없다.
7. 최적화
SELECT 명령을 실행할 때 데이터베이스는 인덱스의 사용 여부를 선택한다.
이는 데이터베이스 내부의 최적화에 의해 처리되는 부분이다.
데이터베이스는 내부 처리과정에서 SELECT 명령을 실행하기에 앞서 실행계획을 세운다.
실행계획에서는 '인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용하자'와 같은 처리가 이루어진다. EXPLAIN 명령은 이 실행계획을 확인하는 명령!
실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다.
이때 판단 기준으로 인덱스의 품질도 고려한다.
ex) '예' or '아니오'만 가지는 열의 경우 해당 열에 인덱스를 지정해도 좋은 구조 안나온다 -> 단순한 리스트와 별차이 x -> 이진 탐색에 의한 효율화 딱히
데이터의 종류가 적으면 적을수록 인덱스의 효율도 떨어진다.
반대로 서로 다른 값으로 여러 종류의 데이터가 존재하면 그만큼 효율은 좋아진다.
=> 인덱스의 품질을 고려해 실행계획이 세워진다!
