SQL 튜닝 (일반적인 SQL 튜닝 기법)

개발자라면 옵티마이저가 실행계획을 생성할 때, 최대한도로 성능을 발휘할 수 있도록 불필요한 로직을 타지 않게 쿼리를 작성해야 한다.

같은 결과를 산출하는 쿼리는 여러 가지 형태로 작성될 수 있으므로, 개발자는 SQL 문장 단에서 최대한 효율적인 문장을 작성해야 한다.

"쿼리를 이렇게 작성하면 좋은 성능을 발휘한다"라는 일반적인 가이드 라인을 알아보자!

 

가급적 WHERE 조건에서는 인덱스 컬럼을 모두 사용한다.

---

인덱스를 만들어 놓더라도 WHERE 조건을 어떻게 명시하느냐에 따라 옵티마이저가 인덱스를 사용할 수도 있고 사용하지 않을 수도 있다.

예를 들어 A와 B라는 컬럼의 인덱스를 만들었는데, WHERE 조건에서 A 컬럼만 사용한다면 인덱스를 타지 않게 된다.

 

예)

CONTRACT 테이블에서 CONTRACT_NO, CONTRACT_REV 컬럼이 CON_NO_IDX 인덱스로 만들어져 있을  때, 아래와 같은 쿼리는 인덱스를 사용하지 않고 FULL SCAN을 하게 된다.

 

SELECT *

FROM CONTRACT

WHERE CONTRACT_REV ='1'

 

아래와 같이 사용하여야 한다.

SELECT *

FROM CONTRACT

WHERE CONTRACT_NO = '900000'

AND CONTRACT_REV = '1'

 

인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자(=)를 사용하라.

---

인덱스 컬럼을 WHERE조건에 모두 명시하였더라도 LIKE와 같은 연산자를 사용하면 인덱스 효율이 떨어진다.

LIKE 외에도 IS NULL, IS NOT NULL, NOT IN 등이 사용되었을 경우에도 마찬가지 현상이 발생한다.

 

인덱스 컬럼은 변형하여 사용하지 않도록 한다.

---

WHERE 조건에 인덱스 컬럼을 사용했고, 동등 연산자를 사용했다 하더라도 인덱스 컬럼에 변형을 가하게 되면 인덱스를 사용하지 못한다.

SELECT *

FROM CONTRACT

WHERE SUBSTR(CONTRACT_NO, 1,1,) = '9'

AND CONTRACT_REV = '1'

 

컬럼에 변형을 가하였을 때

SELECT *

FROM CONTRACT

WHERE CREATOR_ID LIKE 'KKK%'

 

SELECT *

FROM CONTRACT

WHERE SUBSTR(CREATOR_ID, 1, 3) = 'KKK'

 

CREATOR_ID가 UNIQUE 인덱스일 경우, 첫 번째 문장은 LIKE를 사용하여 INDEX ROWID SCAN이 아닌 INDEX RANGE SCAN 방식을 사용하게 된다.

하지만 두 번째는 컬럼자체에 변형을 가했기 때문에 FULL SCAN을 하게 된다.

OR 보다는 AND를 사용해라.

---

옵티마이저의 OR-Expansion 처리는 OR 연산자로 연결된 쿼리를 UNION ALL로 변환하므로 OR 보다는 AND를 사용해야 성능 좋은 쿼리를 작성할 수 있다.

 

그룹핑 쿼리를 사용할 경우 가급적 HAVING 보다는 WHERE 절에서 데이터를 필터링하라.

---

그룹핑 쿼리 처리순서는 WHERE 조건이 먼저 처리되므로 가급적 필터링 할 대상은 WHERE 조건에서 처리할 수 있게 쿼리를 작성하도록 한다.

HAVING 절은 이미 WHERE 절에서 처리된 로우들을 대상으로 조건을 감시하기 때문에 좋은 성능을 발휘하기가 힘들다.

 

DISTINCT는 가급적 사용하지 않는다.

---

DISTINCT는 키워드 내부적으로 정렬 작업을 수반하기 때문에 꼭 필요한 경우가 아니라면 사용하지 않는다.

 

IN, NOT IN 대신 EXISTS 와 NOT EXISTS를 사용하라

---

IN 이나 NOT IN 연산자 보다는 EXISTS 나 NOT EXISTS를 사용하는 것이 더 좋은 성능을 발휘한다. 

 

IN 사용

SELECT A.*

FROM CONTRACT A, CONTRACTOR B

WHERE A.CONTRACT_NO = B.CONTRACT_NO

AND B.CONTRACT_NO ('1111', '2222', '3333');

 

EXISTS 사용

SELECT *

FROM CONTRACT A

WHERE EXISTS ( SELECT 1

                       FROM CONTRACTOR B

                      WHERE A.CONTRACT_NO = B.CONTRACT_NO

                         AND B.CONTRACT_NO IN ('1111', '2222', '3333') );

 

언뜻 보면 아래의 문장이 성능이 좋지 않을 것 같지만,

EXISTS를 사용한 두 번째 쿼리의 비용(2)이 IN을 사용한 첫 번째 쿼리의 비용(3) 보다 더 적다. 실제 테이블에 저장된 데이터 양이 많지 않아 성능 차이는 거의 없다고 볼 수 있지만, 수십, 수백만 건의 데이터가 저장되어 있다면 눈에 띄게 그차이가 드러난다.

=> 생각해보기

SET 연산자 사용시 UNION 대신 UNION ALL을 사용하라.

---

UNION 연산자는 연결된 쿼리에서 동일한 로우는 제거하고 한 로우만 반환하게 된다.

따라서 추가적으로 필터링하는 로직이 숨어 있으므로 UNION ALL 보다는 비용이 높을 수 밖에 없다.

 

(참고서적 : 뇌를 자극하는 오라클 프로그래밍)