○ 개요
작성 한 SQL문 내에서, 쿼리에서 사용하는 특정 컬럼에 인덱스가 있음에도 인덱스 스캔을 하지 않는 경우가 있을 것이다.
이러한 경우, SQL문 변경/튜닝을 통해서 성능을 향상 시키는 방법을 간단한 예제를 통해서 확인한다.
○ 인덱스란?
- 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다.
인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다.
고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다.
인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다.
(왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다.)
○ 사례1 : NOT, <>, !=의 부정형 연산은 인덱스 사용이 불가함으로 >= 연산으로 변경하여,name 인덱스를 활용하도록 개선
- 튜닝 전
SELECT code, NAME FROM athlete WHERE NAME IS NOT null |
- 튜닝 후
SELECT code, NAME FROM athlete WHERE NAME >='' |
○ 사례2 : game_date 컬럼에 TO_CHAR 함수를 사용하여 인덱스 사용이 불가. 2000년 1월부터 12월까지 조회하도록 변경
※ 조회 대상이 되는 컬럼에 함수를 사용 할 경우, 해당 컬럼에 인덱스가 있어도 활용이 불가
- 튜닝 전
CREATE INDEX i_game_game_date ON game(game_date); SELECT athlete_code FROM game WHERE TO_CHAR(game_date,'yyyy')='2000' |
- 튜닝 후
SELECT athlete_code FROM game WHERE game_date BETWEEN TO_DATE('200001','yyyymm') AND TO_DATE('200012','yyyymm') |
○ 사례3 : Index가 nation_code, name로 있고, 조회조건을 name으로 검색 할 경우에 인덱스 사용이 불가함으로, 인덱스 생성 순서를 name, nation_code 순서로 변경하여 생성
- 튜닝 전
CREATE INDEX idx01_athlete ON athlete(nation_code, name); SELECT * FROM athlete WHERE NAME LIKE 'Fernandez%' |
- 튜닝 후
CREATE INDEX idx01_athlete ON athlete(name, nation_code); SELECT * FROM athlete WHERE NAME LIKE 'Fernandez%' |
○ 사례4 : 한개의 테이블에 하나의 인덱스만 선택하여 실행 함으로, 단일 인덱스 여러 개의 구조 보다는 multi-column index 생성하여 쿼리 성능 향상
- 튜닝 전
select * from game where stadium_code=‘30115' and nation_code=‘KOR'; create index i_game_1 on game(stadium_code); create index i_game_2 on game(nation_code); |
- 튜닝 후
select * from game where stadium_code=‘30115' and nation_code=‘KOR'; create index i_game_1 on game(stadium_code, nation_code); |
○ 쿼리 튜닝 시 고려사항
- Where절에서 자주 사용되는 컬럼에는 인덱스 추가 고려
- 인덱스를 많이 생성하는 것은 INSERT/UPDATE/DELETE 성능 저하의 원인이 될 수 있음.
- 동일한 값을 가지는 레코드가 적은 컬럼에 인덱스를 설정
- 인덱스 scan이 Full Table scan보다 항상 빠르지는 않다.