DB객체) INDEX 객체(장단점, 실행계획, 사용 시 주의점)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

INDEX

- 색인

- SQL 명령어 처리 속도 향상을 위해 특정 테이블, 특정 컬럼에 대해 생성하는 오라클 객체

- key-value 형태로 관리 (key에는 컬럼 값, value에는 레코드 주소)

- 테이블 데이터와 별도로 색인 데이터 관리 (별도의 저장 공간 필요)

 

INDEX 장점

① 검색 속도가 빨라짐

② 시스템 부하가 줄어들고, 전체적인 성능 향상

 

INDEX 단점

① 별도의 저장 공간 필요, 인덱스 생성/수정/삭제에 별도의 시간 소요

② 테이블 데이터 성격 상 insert/update/delete가 잦은 경우, index에 의한 성능 저하가 있을 수 있음

※ 테이블이 insert되면 인덱스 객체 내에서도 순차적으로 정렬이 필요하고,

테이블이 update되면 인덱스 객체 내에서도 update된 컬럼의 주소값을 찾아 수정해야 하며,

테이블이 delete되면 인덱스 객체 내에서도 delete된 컬럼의 주소값을 찾아 삭제해야 하므로 시간이 소요됨

 

어떤 컬럼에 대해 인덱스 생성을 해야 하는가?

① 선택도(selectivity)가 좋은 컬럼 (중복 값이 적고 고유 값을 많이 가진 데이터)

      좋음 : 사번(pk), 주민번호(uq), email(uq) > name → pk/uq컬럼은 자동으로 인덱스 생성

      보통 : 부서코드, 직급코드

      나쁨 : 퇴사여부, 성별, 결혼여부

② where절 / join절에 자주 사용되는 컬럼

③ 테이블 데이터가 많은 경우, 인덱스 사용 필수!

 

인덱스 사용을 자제해야 할 컬럼

① 선택도가 나쁜 컬럼

② null 값이 너무 많은 컬럼

③ 테이블 당 많은 인덱스를 생성하면 성능 저하 가능성이 있음

---

사용자가 갖고 있는 인덱스 조회

select * from user_indexes;

별도로 설정하지 않았지만, index 객체를 갖고 있는 게 확인 되고 모두 pk/uq컬럼입니다.

 

컬럼명을 확인하고 싶다면 user_ind_columns에서 조회!

select * from user_ind_columns;

---

실행 계획을 통한 성능비교

※ 실행 계획이란?

SQL을 실행 했을 때, 어떤 절차로 테이블을 조회/필터링 처리를 할 지에 대한 계획 (F10)

 

인덱스 처리가 안된 컬럼 (job_code)

select * from employee where job_code = 'J1';

실행

실행계획

job_code = 'J1'을 필터링 하기 위해서 테이블을 'FULL' 스캔하여 즉, 24행을 모두 확인하여 찾았다는 것 입니다.

cardinality는 찾아낸 행을 의미, cost는 시간, 메모리, 효율성을 고려한 논리적인 비용을 의미합니다.

 

인덱스 처리가 된 컬럼 (emp_id)

select * from employee where emp_id = '210';

실행

job_code는 employee에 가서 full scan 하였지만,

 

이번에는 INDEX에 먼저 가서(화살표) UNIQUE SCAN 방식을 이용하였고 (cost=0)

INDEX에 가서 레코드의 주소 값 (ROWID)을 이용하여 employee에서 찾아냈습니다.

엄청난 성능 차이를 보여주고 있습니다. (무려 3배!)

 

인덱스 생성

-- 인덱스 생성
create index idx_employee_emp_name on employee(emp_name);

-- 생성 확인하기
select * from user_indexes where table_name = 'EMPLOYEE';

UNIQUENESS 컬럼을 보면, PK/UQ는 중복이 없기 때문에 UNIQUE 스캔 방식을 사용하여 가장 빠르게 검색합니다.

하지만 emp_name은 pk/uq 컬럼이 아니므로, NONUNIQUE 스캔 방식을 사용하게 되어 UNIQUE 스캔 방식 보다는 다소 성능이 떨어집니다. (RANGE SCAN, SKIP SCAN)

 

emp_name 실행계획 확인

select * from employee where emp_name = '송종기';

실행

실행계획

이번엔 중복 값이 있을 수 있는 가능성을 두고 검사를 하는 RANGE SCAN을 이용하여 컬럼 값을 찾아냈습니다.

UNIQUE 스캔 보다 cost가 더 든 것이 확인 됩니다. 

---

인덱스 사용 시 주의점

① 인덱스 컬럼에 변형이 가해진 경우 인덱스 처리 X (있는 값 그대로 해야됨)

select * from employee where substr(emp_no, 8, 1) = '1';    -- emp_no 인덱스 컬럼

 

② is null 비교하면 인덱스 처리 X

select * from employee where emp_name is not null;  -- emp_name 인덱스 컬럼

 

③ not 비교하면 인덱스 처리 X

select * from employee where emp_name != '송종기';

 

④ 인덱스 컬럼과 비교하는 값의 타입이 다른 경우 인덱스 처리 X

select * from employee where emp_id = 201;	-- emp_id는 varchar!

 

⑤ 오라클 내부 cost_based_optimizer의 실행 계획에 의해 변경된 경우 (힌트로 설정 변경)