1. index란?
- 조회 속도를 향상시키기 위한 데이터 베이스 검색 기술
- 색인이라는 뜻으로 해당 테이블의 조회 결과를 빠르게 하기 위해 사용
- 인덱스가 필요한 이유 : 인덱스를 생성해 줌으로써 조회 속도를 빠르게 할 수 있음
2. index 생성 원리
- index를 테이블의 특정 컬럼에 한 개 이상 주게 되면, index table이 따로 만들어 짐
- 인덱스 컬럼의 로우값과 rowid값이 저장되며, 로우값은 정령된 트리 구조로 저장시켜 두었다가 검색 시 더 빠르게 해당 데이터를 찾는데 도움
(rowid : 테이블을 생성하고 컬럼을 만든 후 데이터를 삽입하면 하나의 row가 생성되며 이 row는 절대적인 주소를 가지게 되는데 이를 rowid라 함) - DML 명령을 사용할때는 원본 table은 물론 index table에도 데이터를 갱신시켜 주어야하기 때문에, update, insert, delete 명령을 쓸 떄 속도가 느려진다는 단점이 있음
3. 반대로 index 생성이 불필요한 경우
- 데이터가 적은(수천건 미만) 경우에는 인데스를 설정하지 않는게 오히려 좋음
- 조회보다 삽입, 수정, 삭제 처리가 많은 테이블
4. DML 구문과 index
- insert : index split 현상으로 부하가 걸림
- detelet : 인덱스에서는 데이터를 사용하지 않으므로 표시하고 지우지 않음
- update : 인덱스에서는 delete를 한 후, 새로운 데이터를 insert 작업 함
5. index 생성 방법
-
uniqe index
create unique index 인덱스명
on 테이블명(컬람); -
non unqiue index
crate index 인덱스명
on 테이블명(칼럼);
코드
-- index
-- 테스트를 위한 테이블 복사
CREATE TABLE EMPLOYEES3
AS
SELECT * FROM EMPLOYEES e ;
SELECT * FROM EMPLOYEES3;
-- index에 활용되는 rowid
SELECT rowid, EMPLOYEE_ID
FROM EMPLOYEES3 e
WHERE EMPLOYEE_ID = 200
;
-- 중복 데이터를 만들어보자
INSERT INTO EMPLOYEES3 (employee_id, FIRST_NAME, LAST_NAME)
values(100, 'Kim','Chen');
SELECT * FROM EMPLOYEES3 e WHERE EMPLOYEE_ID = 100;
-- unique index 생성
CREATE UNIQUE INDEX idx_emp_id
ON EMPLOYEES3(employee_id);
-- Kim Chen 데이터 2건 삭제 후, unique index 걸어주기
DELETE FROM EMPLOYEES3 e
-- SELECT * FROM EMPLOYEES3 e
WHERE first_name = 'Kim'
AND last_name = 'Chen';
-- non-unique index
CREATE INDEX ind_emp_first
ON employees3(first_name);
SELECT * FROM EMPLOYEES3 e
WHERE FIRST_NAME = 'TJ';
INSERT INTO EMPLOYEES3 e
(employee_id, first_name, last_name)
VALUES (500, 'TJ','name');