데이터베이스 성능 튜닝을 위한 가장 기본적인 인덱스 활용방법

데이터베이스 성능 튜닝하기 

안녕하세요! 이번 시간에는 데이터베이스와 쿼리와 관련된 성능 튜닝에 대해 알아보겠습니다.

Front-end 에서 요청된 내용을 back-end 에서 비지니스 로직과 쿼리로 처리하고 요청에 대한 응답을 빠르게 처리해줘야 사용자가 사용하는 화면에서 빠르게 볼 수 있습니다.

비지니스 로직도 중요하지만 데이터를 불러오는 쿼리에서 응답시간이 오래 걸린다면 아무리 비지니스 로직에서 쓰레드와 좋은 알고리즘을 사용한다고 해도 그 프로그램은 빠를 수 없습니다.

데이터베이스 호출과 관련된 문제점은 데이터베이스의 인덱스를 잘 설정하고 쿼리튜닝을 적절하게 하여 성능을 높일 수 있습니다.

데이터베이스 성능 튜닝을 위한 첫번째는 인덱스 튜닝 입니다.

인덱스란 무엇일까?

인덱스는 책에 있는 목차와 같은 형태로 되어 있다고 볼 수 있습니다.

해당 인덱스 컬럼 내용에 따라 정렬되어 있고, 실제 DB의 ROWID 정보가 저장되어 있습니다.

그럼, 인덱스를 무수히 많이 만들어 놓으면 될까?

아닙니다. 지나치게 크거나 많은 인덱스를 설정하면 오히려 성능 저하를 유발할 수 있습니다.

왜냐하면 한 테이블을 조회할 때 사용할 수 있는 인덱스는 한가지 뿐입니다.

효율적인 인덱스 사용에 따라 조회 속도가 천차만별의 차이를 만들어 낼 수 있습니다.

인덱스 사용시 주의점은 어떤 것들이 있을까?

1. 인덱스 컬럼을 가공하면 안됩니다.

EMPLOYEE 테이블의 EMP_NAME 에 인덱스가 잡혀있다고 가정시
WHERE SUBSTR(EMP_NAME, 1, 1) = ‘김’ (X)
WHERE EMP_NAME LIKE ‘김%’ (o)

인덱스 컬럼끼리 결합연산을 사용해도 안됩니다.

EMPLOYEE 테이블에서 REG_DATE 에 인덱스가 잡혀있다고 가정시
WHERE TO_CHAR(REG_DATE, ‘YYYY-MM-DD’) = ‘2015-03-01’ (X)
WHERE REG_DATE BETWEEN TO_DATE(‘2015-03-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) AND TO_DATE(‘2015-03-01 23:59:59’, ‘YYYY-MM-DD HH24:MI:SS’) (O)

오라클에서는 Function 인덱스를 잡을 수는 있지만 잘 사용하지 않습니다.

하지만 성능 향상을 위해서 사용할 수도 있습니다.

CREATE INDEX IX_EMPLOYEE_TEST ON EMPLOYEE_1 (TO_CHAR(REG_DATE, ‘YYYY-MM-DD’))

인덱스 컬럼 순서에도 항상 유의해야 합니다.

● 인덱스 컬럼이 A, B, C 라고 설정되어 있다면?

조회조건은 반드시 A부터 들어가야 합니다.

조회조건 : A, B, C 모두 포함 ( Best )

조회조건 : A, B 만 포함 ( OK )

조회조건 : A 만 사용 ( 인덱스 효율은 떨어지지만 사용은 가능 )

조회조건 : B 또는 C 만 사용 ( Index Full Scan 발생 )

● LIKE 연산 시 반드시 글자 앞 부분은 시작되어야 합니다.

WHERE TITLE LIKE '테스트%' - (O)

WHERE TITLE LKE '%테스트%' - (X)

WHERE TITLE LIKE '%테스트' - (X)

● 인덱스에서는 NULL을 사용할 수 없습니다.

WHERE TITLE IS NULL (X)

WHERE TITLE IS NOT NULL (X)

● 인덱스에서는 부정 연산자 사용 금지 입니다.

WHERE TITLE != 'TEST' (X)

※ 인덱스 생성 팁

SELECT DEPT_LOC
FROM DEPARTMENT
WHERE DEPT_ID = ?
1. CREATE INDEX IX_DEPARTMENT ON DEPARTMENT (DEPT_ID);
2. CREATE INDEX IX_DEPARTMENT ON DEPARTMENT (DEPT_ID, DEPT_LOC);

2번의 방식으로 인덱스를 생성하는 것이 좋습니다.

1번 방식으로 인덱스를 잡을 경우, 인덱스에서 조건에 해당하는 ROWID를 찾아내서, 실제 테이블을 한번 더 검색해 DEPT_LOC 컬럼 값을 읽어들입니다.

즉 인덱스 검색 -> 실 테이블 검색의 2단계 작업이 수행됩니다.

2번의 방식으로 인덱스를 잡을 경우, 인덱스 테이블만 보고 연산이 종료됩니다.

실제로 1단계만 실행되고 작업이 종료되죠.

인덱스의 개념과 생성 시 주의점, 인덱스 생성 팁에 대해 알아보았습니다.

다음 시간에는 서브쿼리 사용 시 주의할 점과 쿼리 튜닝에 대해 알아보겠습니다.