쿼리최적화 :
SQL작성팁, 최적화된 빠른쿼리를 위한 방법
안녕하세요. 율이맨입니다.
오늘은 쿼리최적화를 위한 SQL작성팁에 대해서 알아보겠습니다.
쿼리를 작성하실 때 쿼리의 최적화에 따라서 그 성능이 큰 차이가 나기도 합니다.
데이터가 많을 수록 그 차이는 더욱 커집니다.
따라서 쿼리를 작성하실 때 기본적으로 최적화를 염두해두고 작성하시는 것이 좋습니다.
일반적으로 쿼리를 작성할 때 최적화를 할 수 있는 몇가지 방법에 대해 알아보겠습니다.
이미 알고계신 분들도 많이 계시지만, 초급분들을 위해 작성해보았습니다.
1. 조건 컬럼에는 별도의 연산을 걸지 않는 것이 좋다.
인덱스가 걸려있는 컬럼을 변형하게 되면 인덱스를 타지 못합니다.
함수, 계산, 표현식이 있다면 인덱스 컬럼이 아닌 반대쪽에 작성하는 것이 좋습니다.
WHERE SUBSTR(EMPNAME,1,5) = 'david' -> WHERE EMPNAME LIKE 'david%' WHERE EMPNAME||DEPTNAME = 'DAVIDSERVICE' -> WHERE EMPNAME = 'DAVID' AND DEPTNAME = 'SERVICE' WHERE SAL + 1000 < 2000 -> WHERE SAL < 1000
이렇게 하면 인덱스가 걸려있는 컬럼을 그대로 사용한다면, 인덱스를 그대로 사용할 수 있기 때문에
더 좋은 성능을 낼 수 있습니다.
2. SELECT 할때는 필요한 컬럼만 가져온다.
SELECT * FROM EMP; SELECT EMPCODE FROM EMP;
많은 필드를 불러오는 것은 아무래도 DB에 더 많은 로드가 생기게 됩니다. 따라서 사용할 필드를 선택해서 가져오는 것이 좋습니다.
3. WHERE 절의 비교컬럼 데이터 타입은 일치하는 것이 좋다
SELECT COUNT(1) FROM EMP WHERE DEPTNO = 1 ; SELECT COUNT(1) FROM EMP WHERE DEPTNO = '1' ;
여기서 DEPTNO 는 컬러타입이 문자라고 가정해보겠습니다.
그러면 첫번째 조건은 TO_NUBMER(DEPTNO) = 1 과 동일하기 때문에 인덱스를 사용하지 못합니다.
따라서 컬럼의 데이터 타입을 일치시켜서 인덱스를 활용해야 합니다.
4. DISTINCT, UNION 과 같은 중복 값을 제거하는 함수를 최소화 한다.
중복값을 제거하기 위한 함수를 사용하면 연산의 시간이 많이 걸립니다.
물론 데이터양이 많을 수록 더욱 그러합니다.
SELECT DISTINCT A.DEPTNAME FROM DEPT A, EMP B WHERE A.DEPTNO = B.DEPTNO SELECT A.DEPTNAME FROM DEPT A WHERE EXISTS (SELECT 1 FROM EMP B WHERE A.DEPTNO = E.DEPTNO)
DISTINCT 를 사용하기 보다는 EXISTS 를 사용하는 방법이 있고, 불가피하게 사용해야 한다면,
테이블의 크기, 데이터량을 최소화 하는 것이 좋습니다.
5. LIKE 사용시 % (와일드카드 문자열) 을 STRING 되도록 앞에 배치 하지 않는다.
SELECT A.DEPTNAME, B.EMPNAME FROM DEPT A, EMP B WHERE A.DEPTNO = B.DEPTNO AND A.DEPTNAME LIKE '%SERVICE%' SELECT A.DEPTNAME, B.EMPNAME FROM DEPT A, EMP B WHERE A.DEPTNO = B.DEPTNO AND A.DEPTNAME LIKE 'SERVICE%'
은 사실 1번과 같은 원리라고 보시면 됩니다.
LIKE '%...' 의 경우에는 FULL SCAN 을 사용하게 됩니다. 인덱스를 타지 않습니다.
따라서 LIKE '%...' 보다는 LIKE '...%' 또는 다른 방법을 사용하는 것이 좋습니다.
LIKE '...%' 는 모든 문자열을 탐색하지 않기 때문에 성능이 좋습니다.
6. 3개 이상의 테이블을 조인할 때는 크기가 가장 작은 테이블을 먼저 배치한다.
남은 테이블은 작은 순서로 배치한다.
물론 항상 통용되는 것은 아니며, PLAN 을 통해 가장 효율적인 순서를 찾아
선택하는 것이 좋습니다.
7. 조인되는 건수가 작다면 일반적인 조인보다는 스칼라서브쿼리 사용도 고려한다.
스칼라서브쿼리는 함수와 같이 레코드당 하나의 값을 리턴하는 것입니다.
건수가 작다면 효과가 있지만, 반대라면 오히려 성능이 저하되고 CPU사용율을 높게 만드니, 주의해야합니다.
8. 그외
1) 인덱스 컬럼을 WHERE 절에서 사용 시 OR 대신 UNON ALL 을 사용한다.
2) 공통 쿼리는 WITH문을 사용하여 통합하여 사용한다.
3) 인덱스 힌트를 이용한다.
이상 기본적으로 쿼리를 작성할 때 최적화하기 위한 방법에 대해 알아보았습니다.
이 방법외에도 여러가지 방법이 물론 있습니다.
어떤 DB 를 사용하는지, 어떤 SQL을 사용하는지에 따라서도 다양합니다.
그런데 위에 언급된 내용은 쿼리를 작성할 때 습관으로 익히는 것이 좋습니다.
기본적인 최적화된 쿼리작성과 작성된 쿼리에 필요한 최적화된 방법을 부합시킨다면
더욱 효율적일 것입니다.
이상입니다.
♥공감과 댓글, 그리고 구독은 큰 힘이 됩니다.
